package com.lb.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
public class DBManagerV1 {
private String userName;
private String password;
private String url;
private String driver;
public DBManagerV1(String userName, String password, String url, String driver) {
this.userName = userName;
this.password = password;
this.url = url;
this.driver = driver;
}
public Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userName, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void close(ResultSet rs, Statement statement, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行一条sql ( 没有使用预编译, 有sql 注入风险)
* @param sql
*/
public void execute(String sql) {
Connection conn = null;
Statement statement = null;
conn = getConnection();
try {
statement = conn.createStatement();
statement.execute(sql);
} catch (SQLException e) {
System.out.println(sql);
System.out.println("出错了");
e.printStackTrace();
if (conn != null) {
// 事务回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(null, statement, conn);
}
}
/**
* 批量执行多条 sqls, 一条失败全部回滚 ( 没有使用预编译, 有sql 注入风险)
*/
public void executesBantch(List<String> sqls) {
Connection conn = null;
Statement statement = null;
conn = getConnection();
// 禁用自动提交
try {
statement = conn.createStatement();
conn.setAutoCommit(false);
for (String sql : sqls) {
statement.addBatch(sql);
}
statement.executeBatch();
// 提交事务
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
for (String sql : sqls) {
System.out.println(sql);
}
System.out.println("出错了");
e.printStackTrace();
if (conn != null) {
// 事务回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(null, statement, conn);
}
}
/**
* @param querySql 查询语句
* @return Map<String, Object> </br>
*
* key : 列名</br>
* value : 列值
*
*/
public List<Map<String, Object>> query(String querySql) {
Connection conn = null;
PreparedStatement ps = null;
conn = getConnection();
try {
ps = conn.prepareStatement(querySql);
ResultSet rs = ps.executeQuery();
List<Map<String, Object>> objMapList = new LinkedList<>();
while (rs.next()) {
Map<String, Object> objMap = new HashMap<String, Object>();
ResultSetMetaData rsm = rs.getMetaData(); //获得列集
int colCount = rsm.getColumnCount(); //获得列的个数
for (int i = 0; i < colCount; i++) {
String colName = rsm.getColumnName( i + 1 );
Object colValue = rs.getObject(colName);
objMap.put(colName, colValue);
}
objMapList.add(objMap);
}
return objMapList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 预编译的批量执行 (推荐)
*
* @param paramsList 参数列表
* @param sqlTemplate sql 模板
* @throws RuntimeException
*/
public void bantchExecute(List<Object[]> paramsList, String sqlTemplate) throws RuntimeException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement(sqlTemplate);
for (Object[] params : paramsList) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ps.addBatch();
}
ps.executeBatch();
// 提交事务
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
if (conn != null) {
// 事务回滚
try {
conn.rollback();
throw new RuntimeException();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(null, ps, conn);
}
}
/**
* 推荐使用, 安全高效
* @param sqlMaps</br>
* List<Object[]> paramList
* String sqlTemplate
* @throws RuntimeException
*/
public void bantchExecute(LinkedHashMap<List<Object[]>, String> sqlMaps) throws RuntimeException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
for (Entry<List<Object[]>, String> sqlMap : sqlMaps.entrySet()) {
conn.setAutoCommit(false);
List<Object[]> paramList = sqlMap.getKey();
String sqlTemplate = sqlMap.getValue();
ps = conn.prepareStatement(sqlTemplate);
for (Object[] params : paramList) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ps.addBatch();
}
ps.executeBatch();
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
if (conn != null) {
// 事务回滚
try {
conn.rollback();
throw new RuntimeException();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(null, ps, conn);
}
}
}
可能是史上最强的 jdbc DBManager
最新推荐文章于 2024-04-24 10:05:40 发布