java的jdbc连接辅助类

 1     @Override
 2     public void insert(String name, String type) {
 3         final String sql = "insert into service_data(serviceName,serviceType,status,serviceTime) values(?,?,?,?)";
 4         final Object[] objArr = {name,type,"新创建",null};
 5         ConnectionUtil.startAction(new ConnectionUtil() {
 6             @Override
 7             public void resetAction() throws SQLException {
 8                 preparedStatement(BaseConnection.getConnection(), sql, objArr).executeUpdate();
 9             }
10         });
11     }

其中BaseConnection是获取到的数据库连接.

以上是属于dao层的插入代码,来插入数据库,方法里有自动关闭连接的方法.预编译的位数就对应传入的Object数组.

 

 1     @SuppressWarnings("unchecked")
 2     public List<ServiceData> listSelect(String serviceName, int pageCount,
 3             int pagingCout) {
 4         final String sql = "select * from service_data  where serviceName like  ? limit ?,?";
 5         final Object[] objArr = { "%" + serviceName + "%",
 6                 Integer.valueOf(pagingCout), Integer.valueOf(pagingCout) };
 7         return ConnectionUtil.startAction(new ConnectionUtil() {
 8             @Override
 9             public void resetAction() throws SQLException {
10                 preparedStatement(BaseConnection.getConnection(), sql, objArr)
11                         .executeQuery();// 预编译，并设置值,并且开始查询
12                 ResultSet resultSet = getResultSet(); // 拿到结果集合
13                 setReturnArray(new ArrayList<ServiceData>());
14                 while (resultSet.next()) {
15                     add(new ServiceData(getInt(1), getInt(2), getString(3),
16                             getString(4), getString(5), getTime(6), getInt(7)));
17                 }
18             }
19         });
20     }

 

以上则是封装到Array中再关闭.

不足之处,请自行改正,封装的本质,等同于偷懒. 

基础代码

  1 package cn.gzsxt.service.util;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.sql.Time;
  8 import java.util.ArrayList;
  9 
 10 import cn.gzsxt.util.BaseConnection;
 11 
 12 public abstract class ConnectionUtil {
 13 
 14     public static ArrayList startAction(ConnectionUtil c) {
 15         try {
 16             c.resetAction();
 17         } catch (SQLException e) {
 18             e.printStackTrace();
 19         } finally {
 20             c.close();
 21         }
 22         return c.returnArray;
 23     }
 24     private Connection connection = BaseConnection.getConnection();
 25     private PreparedStatement prepareStatement = null;
 26     private ResultSet resultSet = null;
 27 
 28     public ArrayList returnArray = null;
 29 
 30     public void add(Object obj){
 31         returnArray.add(obj);
 32     }
 33 
 34     private void close() {
 35         if (resultSet != null)
 36             try {
 37                 resultSet.close();
 38             } catch (SQLException e) {
 39                 e.printStackTrace();
 40             }
 41         if (prepareStatement != null)
 42             try {
 43                 prepareStatement.close();
 44             } catch (SQLException e) {
 45                 e.printStackTrace();
 46             }
 47         if (connection != null)
 48             try {
 49                 connection.close();
 50             } catch (SQLException e) {
 51                 e.printStackTrace();
 52             }
 53     }
 54 
 55     public void executeQuery() throws SQLException {
 56         resultSet = prepareStatement.executeQuery();
 57     }
 58 
 59     public int executeUpdate() throws SQLException {
 60         return prepareStatement.executeUpdate();
 61     }
 62 
 63     public int getInt(int num) throws SQLException {
 64         return resultSet != null ? resultSet.getInt(num) : -1;
 65     }
 66 
 67     public Object getObject(int num) throws SQLException {
 68         return resultSet != null ? resultSet.getObject(num) : null;
 69     }
 70 
 71     public ResultSet getResultSet() {
 72         return resultSet;
 73     }
 74 
 75     public ArrayList getReturnArray() {
 76         return returnArray;
 77     }
 78 
 79     public String getString(int num) throws SQLException {
 80         return resultSet != null ? resultSet.getString(num) : null;
 81     }
 82 
 83     public Time getTime(int num) throws SQLException {
 84         return resultSet != null ? resultSet.getTime(num) : null;
 85     }
 86 
 87     
 88     public ConnectionUtil preparedStatement(Connection connection, String sql,
 89             Object[] objArr) {
 90         if (connection != null) {
 91             this.connection = connection;
 92             try {
 93                 this.prepareStatement = connection.prepareStatement(sql);
 94                 if (objArr != null) {
 95                     for (int i = 0; i < objArr.length; i++) {
 96                         prepareStatement.setObject(i + 1, objArr[i]);
 97                     }
 98                 }
 99             } catch (SQLException e) {
100                 throw new AssertionError("预编译失败，设置值错误-->" + e);
101             }
102 
103         } else {
104             throw new AssertionError("传递的数据库连接为空");
105         }
106         return this;
107     }
108     public abstract void resetAction() throws SQLException;
109 
110     public void setReturnArray(ArrayList returnArray) {
111         this.returnArray = returnArray;
112     }
113 
114 }

 

转载于:https://www.cnblogs.com/jxlys/p/8533189.html