dbinfo.properties mysql_java 8.0Mysql 助手类

由于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

Sqlhelper

package edu.rjxy.xjdx.emps.common.utils;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Method;

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 executeQuery(Class type, String sql, String[] parameters) {

ArrayList 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();

//通过rsmd可以得到该结果集有多少列

int columnNum = rsmd.getColumnCount();

//获取字段名

String[] clounmNames = new String[columnNum];

for (int i = 0; i < columnNum; i++) {

clounmNames[i] = rsmd.getColumnLabel(i+1);

}

Method[] methods = type.getDeclaredMethods();

//从rs中取出数据，并且封装到ArrayList中

while (rs.next()) {

Object obj = type.getDeclaredConstructor().newInstance();

for (int i = 0; i < columnNum; i++) {

String clounmName = clounmNames[i];

Object clounmValue = rs.getObject(i + 1);

String setterName = "set"+clounmName.substring(0,1).toUpperCase()+clounmName.substring(1);// setName

Method setter = null;

for (int j = 0; j < methods.length; j++) {

if (methods[j].getName().equals(setterName)){

setter = methods[j];

break;

}

}

if(setter!=null ){

// System.out.println(""+setter + ":"+clounmValue.getClass().getName() );

if(setter.getParameterTypes()[0].getName().equals("boolean") || setter.getParameterTypes()[0].getName().equals("java.lang.Boolean")){

clounmValue = ((Integer)clounmValue==0)?false:true;

}

boolean isBasic = false;

String parameter0Name = setter.getParameterTypes()[0].getName();

String[] allBasic =new String[]{"byte","short","int","long","float","double","char","boolean"};

for (String each:

allBasic ) {

if(parameter0Name.equals(each)){

isBasic= true;

break;

}

}

if( !( clounmValue==null && isBasic ) ){

setter.invoke(obj,clounmValue);

}

}

//获取字段值

}

list.add((T)obj);

}

return list;

}

catch(Exception e) {

e.printStackTrace();

throw new RuntimeException(e.getMessage());

} finally {

close(rs, ps, conn);

}

}

public static int queryCount(String sql, String[] parameters) {

ArrayList 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();

if (!rs.next()) return 0;

return Integer.parseInt(rs.getObject(1).toString());

}

catch(Exception e) {

e.printStackTrace();

throw new RuntimeException(e.getMessage());

} finally {

close(rs, ps, conn);

}

}

public static ArrayList executeQuery(String sql, String[] parameters) {

ArrayList 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();

//通过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

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 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 list = new ArrayList();

Map rowData = new HashMap();

while (rs.next()) {

rowData = new HashMap(columnCount);

for (int i = 1; i <= columnCount; i++) {

rowData.put(((ResultSetMetaData) md).getColumnName(i), rs.getObject(i));

}

list.add(rowData);

}

return list;

}

}