BaseDao.util(虎大将军)

  1 package logistics.util;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.ResultSetMetaData;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.ArrayList;
 10 import java.util.HashMap;
 11 import java.util.List;
 12 import java.util.Map;
 13 
 14 import javax.sql.DataSource;
 15 
 16 import org.slf4j.Logger;
 17 import org.slf4j.LoggerFactory;
 18 import org.springframework.beans.factory.annotation.Autowired;
 19 import org.springframework.stereotype.Repository;
 20 
 21 /**
 22  * @author �½�
 23  * @������dao������
 24  * 2018-10-25����2:54:57
 25  */
 26 @Repository
 27 public class BaseDao {
 28     private final static Logger logger = LoggerFactory.getLogger(BaseDao.class);
 29     @Autowired
 30     private DataSource dataSource;
 31     //��ȡ���ݿ�����
 32     public Connection getConnection() {
 33         try {
 34             return dataSource.getConnection();
 35         } catch (SQLException e) {
 36             // TODO Auto-generated catch block
 37             e.printStackTrace();
 38         }
 39         return null;
 40     }
 41     //��ѯͨ�÷���
 42     public List<Map<String,Object>> executeQuery(String sql,Object...params) {
 43         List<Map<String,Object>> objList= new ArrayList<Map<String,Object>>();
 44         Connection conn = getConnection();
 45         PreparedStatement ps=null;
 46         ResultSet rs=null;
 47         try {
 48              ps = conn.prepareStatement(sql);
 49              ps = setParams(ps, params);
 50              //ִ��sql
 51               rs = ps.executeQuery();
 52               //��ȡ��Ԫ������Ϣ
 53               ResultSetMetaData metaData = rs.getMetaData();
 54               //���������
 55               while(rs.next()){
 56                   Map<String,Object> row= new HashMap<String, Object>();
 57                   //metaData.getColumnCount()��ȡ����
 58                   for (int i = 0; i < metaData.getColumnCount(); i++) {
 59                       row.put(metaData.getColumnLabel(i+1), rs.getObject(i+1));
 60                   }
 61                   objList.add(row);
 62               }
 63         } catch (SQLException e) {
 64             e.printStackTrace();
 65         }finally {
 66             try {
 67                 conn.close();
 68             } catch (SQLException e) {
 69                 // TODO Auto-generated catch block
 70                 e.printStackTrace();
 71             }
 72         }
 73         return objList;
 74     }
 75     //��ɾ��ͨ�÷���
 76     public boolean executeUpdate(String sql,Object...params) {
 77         Connection conn = getConnection();
 78         PreparedStatement ps=null;
 79         try {
 80              ps = conn.prepareStatement(sql);
 81              ps = setParams(ps, params);
 82              //ִ��sql
 83               int ret = ps.executeUpdate();
 84               if(ret>0){
 85                   logger.info("�����ɹ�");
 86                   return true;       
 87               }else
 88               {
 89                   logger.info("����ʧ��");
 90                   return false;
 91               }
 92         } catch (SQLException e) {
 93             e.printStackTrace();
 94         }finally {
 95             try {
 96                 conn.close();
 97             } catch (SQLException e) {
 98                 // TODO Auto-generated catch block
 99                 e.printStackTrace();
100             }
101         }
102         return false;
103     }
104     
105     //订单返回
106     public Integer executeUpdateGetPK(String sql,Object...params) {
107         Connection conn = getConnection();
108         PreparedStatement ps=null;
109         try {
110              ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
111              ps = setParams(ps, params);
112              //ִ��sql
113               int ret = ps.executeUpdate();
114               ResultSet set1 = ps.getGeneratedKeys();
115               while(set1.next()){
116                     return set1.getInt(1);
117                }
118               
119         } catch (SQLException e) {
120             e.printStackTrace();
121         }finally {
122             try {
123                 conn.close();
124             } catch (SQLException e) {
125                 // TODO Auto-generated catch block
126                 e.printStackTrace();
127             }
128         }
129         return null;
130     }
131     //��ֵͨ�÷���
132     public  PreparedStatement setParams(PreparedStatement ps,Object...params) {
133             if(params!=null){
134                 for (int i = 0; i < params.length; i++) {
135                     try {
136                         ps.setObject(i+1, params[i]);
137                     } catch (SQLException e) {
138                         e.printStackTrace();
139                     }        
140                 }
141             }
142             return ps;
143     }
144 }