package edu.wtbu.helper;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
//操作MySql数据库辅助类
public class MySqlHelper {
// 初始化声明变量
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
// 获取连接
public static Connection getConnection() {
String driver = "com.mysql.cj.jdbc.Driver";
try {
Class.forName(driver);// 注册驱动程序
// 初始化连接参数
String url = "jdbc:mysql://localhost:3306/session1?serverTimezone=GMT%2B8";
String userName = "root";
String password = "123456";
// 建立连接
conn = DriverManager.getConnection(url, userName, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// 关闭连接
public static void close() {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 查询的方法
public static List<HashMap<String, Object>> executeQuery(String sql, Object[] paras) {
// 实例化空的List<HashMap>对象
List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();// 特殊
try {
// 获取Connection对象
conn = getConnection();
// 创建PreparedStatement对象
pstmt = conn.prepareStatement(sql);
if (paras != null) {
// pstmt对象赋值
for (int i = 0; i < paras.length; i++) {
String className = paras[i].getClass().getName();// java.lang.String/java.lang.Integer
// String、Integer
if (className.contains("String")) {
pstmt.setString(i + 1, paras[i].toString());// 下标从1开始,特殊
}
if (className.contains("Integer")) {
pstmt.setInt(i + 1, Integer.parseInt(paras[i].toString()));// 下标从1开始,特殊
}
}
}
// 执行查询
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();// rs对象的列的对象
// 结果处理
while (rs.next()) {
HashMap<String, Object> map = new HashMap<String, Object>();// Rs中的一行
for (int i = 0; i < rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i + 1), rs.getObject(i + 1));
}
list.add(map);// 将map添加到list集合中
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();// 关闭连接
}
return list;
}
// 增删改的方法
public static int executeUpdate(String sql, Object[] paras) {
int result = 0;
conn = getConnection();
try {
pstmt = conn.prepareStatement(sql);
// 将参数化的sql语句转换可执行sql(给sql赋值)
if (paras != null) {
// pstmt对象赋值
for (int i = 0; i < paras.length; i++) {
String className = paras[i].getClass().getName();
// String、Integer
if (className.contains("String")) {// java.lang.String
pstmt.setString(i + 1, paras[i].toString());// 下标从1开始,特殊
}
if (className.contains("Integer")) {
pstmt.setInt(i + 1, Integer.parseInt(paras[i].toString()));// 下标从1开始,特殊
}
}
}
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();// 关闭连接
}
return result;
}
}