mysql的util_Mysqlutil.JDBCutil.Dtabaseutil数据库操作工具类[批量操作]

importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.sql.Connection;importjava.sql.DatabaseMetaData;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.Arrays;importjava.util.LinkedHashMap;importjava.util.List;importjava.util.Map;importjava.util.Map.Entry;importjava.util.Properties;//

//注意jar包版本,新版(应该是6.0以上)的driverClass格式不一样//

//old :com.mysql.jdbc.Driver//

//6.0+:com.mysql.cj.jdbc.Driver//

//url连接中需要带上:serverTimezone=UTC//

//中国地区请使用:serverTimezone=Asia/Shanghai//

//

//

//jdbc_driver=com.mysqljdbc.Driver//

//jdbc_url=jdbc:mysql://localhost:3306/log?characterEncoding=utf-8//

//jdbc_username=root//

//jdbc_password=admin//

//

/***@authorlnexin@aliyun.com*/

public classDataBaseUtil {//数据库配置文件地址

private static final String CONFIG_PATH = "config.properties";private static finalString DB_DRIVER;private static finalString DB_URL;private static finalString DB_USERNAME;private static finalString DB_PASSWORD;privateConnection connection;privatePreparedStatement pstmt;privateResultSet resultSet;static{

Properties p= newProperties();try{//两种方式任选其一//InputStream u = DataBaseUtil.class.getResourceAsStream("config.properties");//p.load(u);

p.load(new FileInputStream(newFile(CONFIG_PATH)));

}catch(FileNotFoundException e) {

System.err.println("Not Database configuration files !");

e.printStackTrace();

}catch(IOException e) {

System.err.println("Read configuration file failure!");

e.printStackTrace();

}

DB_DRIVER= p.getProperty("jdbc_driver");

DB_URL= p.getProperty("jdbc_url");

DB_USERNAME= p.getProperty("jdbc_username");

DB_PASSWORD= p.getProperty("jdbc_password");

}publicDataBaseUtil() {

}/*** 使用配置文件中的连接信息获取连接*/

publicConnection getConn() {try{

Class.forName(DB_DRIVER);

connection=DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

}catch(SQLException e) {

System.err.println("Get connection failure！" +e);

}catch(ClassNotFoundException e) {

System.err.println("Load DB Driver failure!" +e);

}return connection == null ? connection : null;

}/

/*** 使用非配置文件的连接信息

*

*@paramdriver

* 数据路驱动

*@paramurl

* 数据库连接

*@paramusername

* 数据库用户名

*@parampassword

* 数据库密码

*@returnConnection*/

publicConnection getConn(String driver, String url, String username, String password) {try{

Class.forName(driver);

connection=DriverManager.getConnection(url, username, password);

}catch(SQLException e) {

System.err.println("Get connection failure！" +e);

releaseConn();

}catch(ClassNotFoundException e) {

System.err.println("Load DB Driver failure!" +e);

}return connection == null ? connection : null;

}//

/*** 插入

*

*@paramsql

* prepareStatement格式的插入语句

*@paramparam

* 需要插入的单个数值

*@return自增的情况下返回插入的主键

*@throwsSQLException*/

public Object insertReturnPK(String sql, Object param) throwsSQLException {returninsertReturnPK(sql, Arrays.asList(param));

}/***

*@paramsql

* prepareStatement格式的insert语句

*@paramparams

* 需要插入的多个参数,以list格式

*@return返回插入语句的自增主键

*@throwsSQLException*/

public Object insertReturnPK(String sql, List params) throwsSQLException {

pstmt=connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);int index = 1;if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {

pstmt.setObject(index++, params.get(i));

}

}

pstmt.executeUpdate();

resultSet=pstmt.getGeneratedKeys();

Object pk= null;if(resultSet.next()) {

pk= resultSet.getObject(1);

}

closePstmat();

resultSet.close();returnpk;

}/*** 执行某一条语句

*

*@paramsql

* prepareStatement格式的sql语句

*@paramparam

* 填充的参数

*@return返回是否执行成功

*@throwsSQLException*/

public boolean executePstamt(String sql, Object param) throwsSQLException {returnexecutePstamt(sql, Arrays.asList(param));

}/*** 执行某一条语句

*

*@paramsql

* prepareStatement格式的sql语句

*@paramparam

* 填充的参数列表

*@return返回是否执行成功

*@throwsSQLException*/

public boolean executePstamt(String sql, List params) throwsSQLException {int result = -1;

pstmt=connection.prepareStatement(sql);int index = 1;if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {

pstmt.setObject(index++, params.get(i));

}

}

result=pstmt.executeUpdate();

closePstmat();return result > 0 ? true : false;

}/*** 批量执行

*

*@paramsql

* prepareStatement格式的sql语句

*@paramlists

* 需要插入的数据列表,注:List 格式,子list中为一条语句所填充的参数列表

*@return返回是否执行成功

*@throwsSQLException*/

public boolean batchExecutePstamt(String sql, List lists) throwsSQLException {

connection.setAutoCommit(false);boolean flag = false;int resultNum = 0;

pstmt=connection.prepareStatement(sql);if (lists != null && !lists.isEmpty()) {for (ListcList : lists) {if (cList == null || cList.isEmpty()) continue;for (int i = 0; i < cList.size(); i++) {

pstmt.setObject(i+ 1, cList.get(i));

}

pstmt.addBatch();

}int[] resNum =pstmt.executeBatch();

connection.commit();

resultNum+=resNum.length;

}

closePstmat();

flag= resultNum > 0 ? true : false;returnflag;

}/*** 查询一条,返回结果

*

*@paramsql

* prepareStatement格式语句

*@paramparam

* 不可为空

*@return一个map集合

*@throwsSQLException*/

public Map selectOne(String sql, String param) throwsSQLException {returnselectOne(sql, Arrays.asList(param));

}/*** 查询一条,返回结果

*

*@paramsql

* prepareStatement格式语句

*@paramparam

* 需要的参数列表,可为空

*@return一个map集合

*@throwsSQLException*/

public Map selectOne(String sql, List params) throwsSQLException {

Map map= newLinkedHashMap();int index = 1;

pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {

pstmt.setObject(index++, params.get(i));

}

}

resultSet=pstmt.executeQuery();

ResultSetMetaData metaData=resultSet.getMetaData();int col_len =metaData.getColumnCount();while(resultSet.next()) {for (int i = 0; i < col_len; i++) {

String cols_name= metaData.getColumnName(i + 1);

Object cols_value=resultSet.getObject(cols_name);if (cols_value == null) {

cols_value= "";

}

map.put(cols_name, cols_value);

}

}

closeResultSet();

closePstmat();returnmap;

}/*** 查询多个结果的返回集

*

*@paramsql

* prepareStatement格式语句

*@paramparams

* 需要填充的参数列表,可为空

*@return一个list的结果集

*@throwsSQLException*/

public List selectMore(String sql, List params) throwsSQLException {

List list = new ArrayList();int index = 1;

pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {

pstmt.setObject(index++, params.get(i));

}

}

resultSet=pstmt.executeQuery();

ResultSetMetaData metaData=resultSet.getMetaData();int cols_len =metaData.getColumnCount();while(resultSet.next()) {

Map map= newLinkedHashMap();for (int i = 0; i < cols_len; i++) {

String cols_name= metaData.getColumnName(i + 1);

Object cols_value=resultSet.getObject(cols_name);if (cols_value == null) {

cols_value= "";

}

map.put(cols_name, cols_value);

}

list.add(map);

}

closeResultSet();

closePstmat();returnlist;

}/*** 查询列名

*

*@paramtanleN

* 表名称

*@return列名称列表

*@throwsSQLException*/

public List selectColumns(String tanleN) throwsSQLException {

DatabaseMetaData data;

List result = new ArrayList<>();

data=connection.getMetaData();

ResultSet columns= data.getColumns(null, null, tanleN, null);while(columns.next()) {

result.add(columns.getString("COLUMN_NAME"));

}

columns.close();returnresult;

}/*** 获取插入语句

*

*@paramtableName

*@paramfields

*@paramisIGNORE

* 是否去重复

*@return

*/

public static String getInsertSql(String tableName, List fields, booleanisIGNORE) {

StringBuffer sb= newStringBuffer();if(isIGNORE) {

sb.append("INSERT IGNORE INTO `").append(tableName).append("` ");

}else{

sb.append("INSERT INTO `").append(tableName).append("` ");

}

StringBuffer field= new StringBuffer("(");

StringBuffer value= new StringBuffer("(");int sign = 0;for(String f : fields) {if (sign > 0) {

field.append(",");

value.append(",");

}

field.append("`").append(f).append("`");

value.append("?");

sign++;

}

field.append(")");

value.append(")");

sb.append(field).append(" values ").append(value).append(";");returnsb.toString();

}/*** 获取建表语句

*

*@paramtableName

*@paramfields

* Map 结构为[字段名,字段数据类型],如:[{name=varchar(64),lastname=varchar(64)}]

*@paramengine

* :InnoDB/MyISAM

*@returncreate sql*/

public static String createTableSql(String tableName, Mapfields, String engine) {

StringBuffer sb= newStringBuffer();

sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");int sign = 0;for (Entryentry : fields.entrySet()) {

String field=entry.getKey();

String type=entry.getValue();if (sign > 0) sb.append(",");

sb.append("`").append(field).append("` ");

sb.append(type).append(" null default null");

}

sb.append(")");

sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");returnsb.toString();

}/*** 获取建表语句

*

*@paramtableName

*@paramfields

* 一个list 的字段列表

*@paramengine

* 数据库引擎类型

*@paramdefaultFieldType

* 默认数据字段的类型,如:varchar(64)

*@return

*/

public static String createTableSql(String tableName, Listfields, String engine, String defaultFieldType) {

StringBuffer sb= newStringBuffer();

sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");int sign = 0;for(String field : fields) {if (sign > 0) sb.append(",");

sb.append("`").append(field).append("` ");

sb.append(defaultFieldType).append(" null default null");

}

sb.append(")");

sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");returnsb.toString();

}/*** 验证表是否存在

*

*@paramtableName

* 表名称

*@returntrue/false*/

public booleanvalidateTableExist(String tableName) {boolean flag = false;try{

DatabaseMetaData meta=connection.getMetaData();

String type[]= { "TABLE"};

ResultSet rs= meta.getTables(null, null, tableName, type);

flag=rs.next();

}catch(SQLException e) {

System.err.println("ERROR! validateTableExist failure! msg:[" + e + "]");

e.printStackTrace();

}returnflag;

}/** 释放链接*/

public voidreleaseConn() {if (resultSet != null) {try{

resultSet.close();

}catch(SQLException e) {

e.printStackTrace();

}

}if (pstmt != null) {try{

pstmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

}if (connection != null) {try{

connection.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}private voidcloseResultSet() {if (resultSet != null) {try{

resultSet.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}private voidclosePstmat() {if (pstmt != null) {try{

pstmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}

}