mysql-connector-java-5.1.49.jar
mysql-connector-java-8.0.30.jar
/**
* 获取数据库连接
* @return Connection
*/
public static Connection getDataSource(String dburl, String username, String password) {
Connection conn = null;
try {
// mysql-connector-java 5 使用的是 com.mysql.jdbc.Driver
//Class.forName("com.mysql.jdbc.Driver");
// 在mysql-connector-java 5以后的版本中(不包括5) 使用的都是com.mysql.cj.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(dburl, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 根据sql获取结果
*
* @param connection 数据库连接
* @param sql sql语句
* @return 查询结果
*/
public static ArrayList<LinkedHashMap<String, String>> getSelectSqlResult(Connection connection, String sql) {
ArrayList<LinkedHashMap<String, String>> result = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
List<String> field = new ArrayList<>();
try {
pstmt = connection.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
// 获取SQL查询结果的列名，下标从1开始
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
field.add(resultSetMetaData.getColumnLabel(i));
}
while (rs.next()) {
LinkedHashMap<String, String> data = new LinkedHashMap<>();
for (String f : field) {
data.put(f, rs.getString(f));
}
result.add(data);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return result;
}
/**
* 获取JSONArray
*
* @param connection 数据库连接
* @param sql sql语句
* @return JSONArray
*/
public static JSONArray getSelectSqlResult(Connection connection, String sql) {
// 数据列表
JSONArray resultArray = new JSONArray();
// 获取列名列表
List<String> fieldList = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet result = null;
try {
pstmt = connection.prepareStatement(sql);
result = pstmt.executeQuery();
// 获取结果集resultSet的结果集元数据metaData
ResultSetMetaData resultSetMetaData = result.getMetaData();
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
// 获取用于打印输出和显示的指定列的建议标题
String columnLabel = resultSetMetaData.getColumnLabel(i);
fieldList.add(columnLabel);
}
// ----------------获取sql查询结果-------------------------------------------
while (result.next()) {
LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
for (String string : fieldList) {
map.put(string, result.getString(string));
}
JSONObject jsonObject = new JSONObject(true);
jsonObject.putAll(map);
resultArray.add(jsonObject);
}
} catch (Exception e) {
// TODO: handle exception
} finally {
try {
if (null != result) {
result.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return resultArray;
}
/**
* delete/insert/update
*
* @param con 数据库连接
* @param update_sql SQL语句
* @return 更新记录条数
*/
public static int updateSql(Connection con, String update_sql) {
int row = 0;
PreparedStatement pstmt = null;
try {
con.setAutoCommit(false);
pstmt = con.prepareStatement(update_sql);
row = pstmt.executeUpdate();
con.commit();
} catch (SQLException e) {
// TODO: handle exception
//sql执行出现异常后回滚
try {
con.rollback();
con.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
con.setAutoCommit(true);
if (null != pstmt) {
pstmt.close();
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
return row;
}
/**
* 执行sql，自动判断是更新还是查询SQL
*
* @param con 数据库连接
* @param sql SQL语句
* @return 返回结果map。map字段解析：status----true/false，sql是否执行成功。type-----select/update，sql执行结果类型，select为查询sql，update为更新sql。row----更新数据行数。data----sql查询结果，ArrayList<LinkedHashMap<String, String>>
*/
public static Map<String, Object> execSql(Connection con, String sql) {
PreparedStatement pstmt = null;
ResultSet rs = null;
Map<String, Object> resultMap = new HashMap<>();
try {
int updateCount = 0;
con.setAutoCommit(false);
pstmt = con.prepareStatement(sql);
// true表示有resultset，false表示有更新数据条数或者没有resultset
boolean currentExec = pstmt.execute();
while (true) {
if (currentExec) {
rs = null;
rs = pstmt.getResultSet();
ArrayList<LinkedHashMap<String, String>> dataList = getDataResultByResultSet(rs);
resultMap.put("type", "select");
resultMap.put("data", dataList);
} else {
updateCount = pstmt.getUpdateCount();
if (updateCount == -1) {
// 没有更多结果了
break;
} else {
resultMap.put("type", "update");
resultMap.put("row", updateCount);
}
}
currentExec = pstmt.getMoreResults();
}
con.commit();
resultMap.put("status", true);
} catch (Exception e) {
resultMap.put("status", false);
//sql执行出现异常后回滚
try {
con.rollback();
con.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
con.setAutoCommit(true);
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return resultMap;
}
/**
* 根据sql获取结果
*
* @param rs ResultSet
* @return 查询结果
*/
public static ArrayList<LinkedHashMap<String, String>> getDataResultByResultSet(ResultSet rs) {
ArrayList<LinkedHashMap<String, String>> result = new ArrayList<>();
List<String> field = new ArrayList<>();
try {
ResultSetMetaData resultSetMetaData = rs.getMetaData();
// 获取SQL查询结果的列名，下标从1开始
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
field.add(resultSetMetaData.getColumnLabel(i));
}
while (rs.next()) {
LinkedHashMap<String, String> data = new LinkedHashMap<>();
for (String f : field) {
data.put(f, rs.getString(f));
}
result.add(data);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
//如果有 BATCH_COUNT 条记录时，则执行批量一次
private static final Integer BATCH_COUNT = 100;
/**
* JDBC 批处理 SQL 语句
* @param connection 数据库连接
* @param sqlList SQL列表
*/
public static void updateBatch(Connection connection, List<String> sqlList) {
Statement statement = null;
try {
connection.setAutoCommit(false);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
int index = 0, count = 0;
for (String sql : sqlList) {
index++;
statement.addBatch(sql);
//如果有 BATCH_COUNT 条记录时，则执行批量一次
if (index % BATCH_COUNT == 0) {
count++;
log.info("每 {} 条批量执行一次", BATCH_COUNT);
//批量执行
int[] row = statement.executeBatch();
connection.commit();
//一把清空
statement.clearBatch();
}
}
// 总条数不是批量数值的整数倍需要再额外的执行一次
if (sqlList.size() % BATCH_COUNT != 0) {
log.info("剩余未执行 {} 条批量执行一次", sqlList.size() - count * BATCH_COUNT);
//批量执行
int[] row = statement.executeBatch();
connection.commit();
//一把清空
statement.clearBatch();
}
long end = System.currentTimeMillis();
log.info("批处理 SQL 语句执行耗时: {} ms", (end - begin));
} catch (Exception e) {
//sql执行出现异常后回滚
try {
connection.rollback();
connection.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);
if (null != statement) {
statement.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}