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 block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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 block
e.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 block
e.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 block
e.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 block
e.printStackTrace();
}
}
}
}
2、这里进行junit测试
@Test
public 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));
}
@Test
public 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);
}