DBHelper数据库操作

1、给予一些特殊的需求原因，我们可能会直接通过sql来操作数据库，这里提供了一个工具类DBHelper.java来实现基本的增帏渍栉啻删改查package com.yj.utils.dbHelper;import com.yj.utils.properties.PathUtil;import com.yj.utils.properties.PropertiesUtil;import javax.servlet.jsp.jstl.sql.Result;import javax.servlet.jsp.jstl.sql.ResultSupport;import java.sql.*;import java.util.List;import java.util.logging.Level;import java.util.logging.Logger;public class DBHelper {private String sql; //要传入的sql语句public void setSql(String sql) {this.sql = sql;}private List sqlValues; //sql语句的参数public void setSqlValues(List sqlValues) {this.sqlValues = sqlValues;}private List<List> sqlValue; //sql语句的参数public void setSqlValue(List<List> sqlValues) {this.sqlValue = sqlValues;}public Connection con; //连接对象/*public void setCon(Connection con) {this.con = con;}*/public DBHelper(){this.con=getConnection(); //给Connection的对象赋初值}/*** 获取数据库连接* @return*/private Connection getConnection(){String driver_class=null;String driver_url=null;String database_user=null;String database_password=null;try {PropertiesUtil propertiesUtil = new PropertiesUtil(PathUtil.getClassPath() + "/config/jdbc.properties");driver_class="com.mysql.jdbc.Driver"; //获取数据库配置文件driver_url="jdbc:mysql://127.0.0.1:3306/lsd_wx_navigation";database_user=propertiesUtil.getString("jdbc_username");database_password=propertiesUtil.getString("jdbc_password");Class.forName(driver_class);con=DriverManager.getConnection(driver_url,database_user,database_password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return con;}/*** 关闭数据库* @param con* @param pst* @param rst*/private void closeAll(Connection con,PreparedStatement pst,ResultSet rst){if(rst!=null){try {rst.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(pst!=null){try {pst.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(con!=null){try {con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/*** 关闭数据库* @param con* @param pst* @param rst*/private void closeAll(Connection con,Statement pst,ResultSet rst){if(rst!=null){try {rst.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(pst!=null){try {pst.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(con!=null){try {con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/*** 查找* @param* @param* @return*/public Result executeQuery(){Result result=null;ResultSet rst=null;PreparedStatement pst=null;try {pst=con.prepareStatement(sql);if(sqlValues!=null&&sqlValues.size()>0){ //当sql语句中存在占位符时setSqlValues(pst,sqlValues);}rst=pst.executeQuery();result= ResultSupport.toResult(rst); //一定要在关闭数据库之前完成转换} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{this.closeAll(con, pst, rst);}return result;}/*** 增删改* @return*/public int executeUpdate(){int result=-1;PreparedStatement pst=null;try {pst=con.prepareStatement(sql);if(sqlValues!=null&&sqlValues.size()>0){ //当sql语句中存在占位符时setSqlValues(pst,sqlValues);}result=pst.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{this.closeAll(con, pst, null);}return result;}/*** 使用PreparedStatement加批量的方法* @return*/public int[] executeUpdateMore(){int[] result=null;try{PreparedStatement prest =con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);for(List sqlValueString : sqlValue){for(int i=0;i<sqlValueString.size();i++){try {prest.setObject(i+1,sqlValueString.get(i));} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}prest.addBatch();}result = prest.executeBatch();// con.commit();this.closeAll(con, prest, null);} catch (SQLException ex){Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null,ex);}return result;}/*** 使用PreparedStatement加批量的方法,strvalue:* "INSERT INTOadlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3','localhost','20081009',8,'23123')"* @return* @throws SQLException*/public int[] executeUpdateMoreNotAuto() throws SQLException{int[] result =null;con.setAutoCommit(false);Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);String[] SqlString= null;for(String strvalue : SqlString){stmt.execute(strvalue);}con.commit();return result;}/*** 使用PreparedStatement加批量的方法,strvalue:* "INSERT INTOadlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3','localhost','20081009',8,'23123')"* @return* @throws SQLException*/public int[] executeMoreNotAuto() throws SQLException{//保存当前自动提交模式Boolean booleanautoCommit=false;String[] SqlString= null;int[] result= null;try{booleanautoCommit=con.getAutoCommit();//关闭自动提交con.setAutoCommit(false);Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//使用Statement同时收集多条sql语句/*stmt.addBatch(insert_sql1);stmt.addBatch(insert_sql2);stmt.addBatch(update_sql3);*/for(String strvalue : SqlString){stmt.addBatch(strvalue);}//同时提交所有的sql语句stmt.executeBatch();//提交修改con.commit();con.setAutoCommit(booleanautoCommit);this.closeAll(con, stmt, null);}catch(Exception e){e.printStackTrace();con.rollback(); //设定setAutoCommit(false)没有在catch中进行Connection的rollBack操作，操作的表就会被锁住，造成数据库死锁}return result;}/*** 给sql语句中的占位符赋值* @param pst* @param sqlValues*/private void setSqlValues(PreparedStatement pst,List sqlValues){for(int i=0;i<sqlValues.size();i++){try {pst.setObject(i+1,sqlValues.get(i));} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

2、这里进行junit测试@Testpublic void query(){//这里需要注意的是：不能直接调用dbHelper.executeQuery()等方法，因为在executeQuery()中的finally中已经将连接关闭，我们将取不到set中的值DBHelper dbHelper = new DBHelper();String sql = "select * from message_record";ResultSet resultSet = null;List<MessageRecord> list = new ArrayList<>();PreparedStatement st = null;try {try {st = dbHelper.con.prepareStatement(sql);resultSet = st.executeQuery();} catch (SQLException e) {e.printStackTrace();}list = new ArrayList<>();while (resultSet.next()) {MessageRecord messageRecord = new MessageRecord();long aLong = resultSet.getLong(1);String mesType = resultSet.getString(2);messageRecord.setMsgId(aLong);messageRecord.setMsgType(mesType);list.add(messageRecord);}} catch (SQLException e) {e.printStackTrace();} finally {if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}}System.out.println("list==============" + gson.toJson(list));}@Testpublic void update() {DBHelper dbHelper = new DBHelper();String sql = "update message_record set msg_type =? where msg_id =?";ResultSet resultSet = null;List list = new ArrayList<>();list.add("oooooooooo");list.add(3518097887765504l);List list1 = new ArrayList<>();list1.add("9999999");list1.add(3518099497722880l);List<List> sqlValues = new ArrayList<>();sqlValues.add(list);sqlValues.add(list1);dbHelper.setSql(sql);dbHelper.setSqlValue(sqlValues);int[] ints = dbHelper.executeUpdateMore();System.out.println("ints===" + ints);int flag = 0;if (null != ints && 0 < ints.length) {for (int anInt : ints) {flag+=anInt;}}System.out.println("flag================" + flag);}