import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class JDBCUtils {
private static JDBCUtils jdbcUtils = null;
private static String jdbc_driver; //jdbc驱动
private static String jdbc_url; //jdbc连接Url
private static String user_name; //jdbc连接用户名
private static String user_password; //jdbc连接密码
private static String batch_size; //批量提交数
private JDBCUtils() { }
/**
* 创建JDBC工具类实例
* @return
*/
public static synchronized JDBCUtils getInstance(){
if(jdbcUtils == null){
jdbcUtils = new JDBCUtils();
}
return jdbcUtils;
}
/**
* 获取 数据库连接
* @return
*/
public Connection getConnection(){
try {
Class.forName(jdbc_driver);
Connection conn = DriverManager.getConnection(jdbc_url, user_name, user_password);
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭数据库相关连接
* @param connection
*/
public void close(ResultSet rs, Statement st, Connection conn) {
try {
if(rs != null)rs.close();rs=null;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (st != null) st.close();st=null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();conn=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 关闭数据库相关连接
* @param connection
*/
private void close(PreparedStatement pstmt, Connection conn) {
try {
if(pstmt != null)pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 增加单条数据
* @param sql sql语句
* @param values 参数值
* @return 是否增加成功
* @throws SQLException
*/
public boolean saveOrUpdate(String sql,Object ... values) throws SQLException{
Connection conn = getConnection(); //获取数据库连接
PreparedStatement pstmt = null;
try {
conn.setAutoCommit(false); //设置手动提交事务
pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
//赋值
for (int i = 0; i < values.length; i++) {
pstmt.setObject(i+1, values[i]);
}
pstmt.execute(); //执行操作
conn.commit(); //提交事务
close(pstmt,conn); //关闭相关连接
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(pstmt,conn); //关闭相关连接
}
return true;
}
/**
* 删除
* @param sql
* @return
*/
public boolean batchDelete(String sql){
Connection conn = getConnection(); //获取数据库连接
PreparedStatement pstmt = null;
try {
conn.setAutoCommit(false); //设置手动提交事务
pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
pstmt.execute(); //执行操作
conn.commit(); //提交事务
close(pstmt,conn); //关闭相关连接
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(pstmt,conn); //关闭相关连接
}
return true;
}
/**
* 批量增加与修改
* @param sql insert or update 语句
* @param params 参数集合
* @return
* @throws SQLException
*/
public boolean batchSaveOrUpdate(String sql,List<Object[]> paramList) {
int count = Integer.parseInt(batch_size)-1;
Connection conn = getConnection(); //获取数据库连接
PreparedStatement pstmt = null;
try {
conn.setAutoCommit(false); //设置手动提交事务
pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象
//赋值
for (int i = 0; i < paramList.size(); i++) {
Object[] values = paramList.get(i);
for (int j = 0; j < values.length ; j++) {
pstmt.setObject(j+1, values[j]);
}
pstmt.addBatch();
//批量数等于 batch_size 时 提交数据
if(i != 0 && (i%count == 0)){
int ids[] = pstmt.executeBatch(); //执行操作
if(ids.length == count+1 ){
conn.commit(); //提交事务
}else{
conn.rollback(); //事务回滚
}
pstmt.clearBatch();
}
}
int ids[] = pstmt.executeBatch(); //执行操作
if(ids.length == paramList.size()%(count+1) ){
conn.commit(); //提交事务
}else{
conn.rollback(); //事务回滚
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(pstmt,conn); //关闭相关连接
}
return true;
}
public static void main(String[] args) throws SQLException {
// JDBCUtils utils = JDBCUtils.getInstance();
//
//
// String sql = "insert into tbl_yitiansystem_systemlog (id,message) values(?,?);";
// List paramList = new ArrayList();
// for (int i = 0; i < 10; i++) {
// String [] param = new String[]{i+"",i+""};
// paramList.add(param);
// }
//
// boolean t = utils.batchSaveOrUpdate(sql, paramList);
// System.out.println(t);
//
}
}