由于mysql版本问题, 8.0的配置文件变了
driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/studentmanage? characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username = root
password = 123456
package MySqlHelper;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.sql.*;
public class SqlHelper {
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
//连接数据库的参数
private static String url = null;
private static String userName =null;
private static String driver =null;
private static String passwd =null;
private static CallableStatement cs = null;
private static Properties pp = null;
private static InputStream fis = null;
public static CallableStatement getCs() {
return cs;
}
public static Connection getCt() {
return conn;
}
public static PreparedStatement getPs() {
return ps;
}
public static ResultSet getRs() {
return rs;
}
static {
try {
pp = new Properties();
fis = SqlHelper.class.getClassLoader().getResourceAsStream("dbinfo.properties");//配置文件
pp.load(fis);
url = pp.getProperty("url");
driver = pp.getProperty("driverClassName");
userName = pp.getProperty("username");
passwd = pp.getProperty("password");
Class.forName(driver);
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
fis.close();
}
catch(IOException e) {e.printStackTrace();}
fis = null;//垃圾回收站上收拾
}
}
public static Connection getConnection() {
try {
conn = DriverManager.getConnection(url, userName,passwd);
}
catch(Exception e) {e.printStackTrace();}
return conn;
}
//*************callPro1存储过程函数1*************
public static CallableStatement callPro1(String sql, String[] parameters) {
try {
conn = getConnection();
cs = conn.prepareCall(sql);
if(parameters!=null) {
for(int i = 0; i < parameters.length; ++i) {
cs.setObject(i + 1, parameters[i]);
}
}
cs.execute();
}
catch(Exception e) {
e.printStackTrace(); throw new RuntimeException(e.getMessage());
} finally {
close(rs,cs, conn);
}
return cs;
}
//*******************callpro2存储过程2************************
public static CallableStatement callPro2(String sql,String[] inparameters, Integer[] outparameters) {
try {
conn = getConnection();
cs = conn.prepareCall(sql);
if(inparameters != null) {
for(int i = 0; i < inparameters.length; ++i) {
cs.setObject(i+1,inparameters[i]);
}
}
//cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
if(outparameters != null) {
for(int i = 0; i < outparameters.length; ++i) {
cs.registerOutParameter(inparameters.length+1+i,outparameters[i]);
}
}
cs.execute();
}
catch(Exception e) {
e.printStackTrace(); throw new RuntimeException(e.getMessage());
}
finally {
}
return cs;
}
public static ArrayList<Object[]> executeQuery(String sql, String[] parameters) {
ArrayList<Object[]> list = null;
try {
conn =getConnection();
ps= conn.prepareStatement(sql);
if(parameters!=null) {
for(int i = 0; i < parameters.length; ++i) {
ps.setString(i+1,parameters[i]);
}
}
rs = ps.executeQuery();
//得到结果集（rs）的结构
ResultSetMetaData rsmd = rs.getMetaData();
list = new ArrayList<Object[]>();
//通过rsmd可以得到该结果集有多少列
int columnNum = rsmd.getColumnCount();
//从rs中取出数据，并且封装到ArrayList中
while (rs.next()) {
Object []objects = new Object[columnNum];
for(int i = 0; i < objects.length; ++i) {
objects[i] = rs.getObject(i + 1);
}
list.add(objects);
}
return list;
}
catch(Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
close(rs, ps, conn);
}
}
public static void executeUpdate2(String[] sql,String[][] parameters) {
try {
conn = getConnection();
conn.setAutoCommit(false);
for(int i = 0; i < sql.length; ++i) {
if(null != parameters[i]) {
ps = conn.prepareStatement(sql[i]);
for(int j = 0; j < parameters[i].length; ++j) {
ps.setString(j + 1, parameters[i][j]);
}
ps.executeUpdate();
}
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e.getMessage());
} finally {
close(rs,ps, conn);
}
}
//先写一个update、delete、insert
//sql格式：update 表名 set 字段名 =？where 字段=？
//parameter神应该是（”abc“,23）
public static void executeUpdate(String sql,String[] parameters) {
try {
conn =getConnection();
ps = conn.prepareStatement(sql);
if(parameters!=null) {
for(int i=0;i<parameters.length;i++) {
ps.setString(i+1,parameters[i]);
}
}
ps.executeUpdate();
} catch(Exception e) {
e.printStackTrace();//开发阶段
//抛出异常
//可以处理，也可以不处理
throw new RuntimeException(e.getMessage());
} finally {
close(rs,ps, conn);
}
}
public static void close(ResultSet rs,Statement ps,Connection ct)
{
//关闭资源(先开后关)
if(rs!=null) {
try {
rs.close();
} catch(SQLException e) {
e.printStackTrace();
}
rs=null;
}
if(ps!=null) {
try {
ps.close();
} catch(SQLException e) {
e.printStackTrace();
}
ps=null;
}
if(null!=ct) {
try {
ct.close();
} catch(SQLException e) {
e.printStackTrace();
}
ct=null;
}
}
public static List<Object> resultSetToList(ResultSet rs) throws java.sql.SQLException {
if (rs == null) return Collections.emptyList();
ResultSet md = (ResultSet) rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
int columnCount = ((ResultSetMetaData) md).getColumnCount(); //返回此 ResultSet 对象中的列数
List<Object> list = new ArrayList<Object>();
Map<Object, Object> rowData = new HashMap<Object, Object>();
while (rs.next()) {
rowData = new HashMap<Object, Object>(columnCount);
for (int i = 1; i <= columnCount; i++) {
rowData.put(((ResultSetMetaData) md).getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
}
}
原文：https://www.cnblogs.com/2aptx4869/p/12943752.html