一个用来操作数据库的常用工具类.
提供批量操作,生成建表,插入语句等
操作示例:
// 1.获取连接
DataBaseUtil jdbc = new DataBaseUtil();
jdbc.getConn();
// Connection conn = new DataBaseUtil().getConn(); // 2. 插入一条数据,返回主键(自增的情况下)
// 如果不要返回主键,用executePstamt就行了
Object pk1 = jdbc.insertReturnPK("insert into t_employee (name,age) values (?,?);", Arrays.asList("Java", 16));
Object pk2 = jdbc.insertReturnPK("insert t_employee (name)", "Java"); // 3.查询一条数据:返回一个map
Map selectOne1 = jdbc.selectOne("select * from t_employee where name = ?;", "Jenny");
Map selectOne2 = jdbc.selectOne("select * from t_employee where id =?;", Arrays.asList(1)); // 4.查询多条数据
List<Map> selectMore1 = jdbc.selectMore("select *from t_employee;", null);
List<Map> selectMore2 = jdbc.selectMore("select id,name,age from t_employee where age > ?;", Arrays.asList(18)); // 5.执行sql
jdbc.executePstamt("update t_employee set name = ?,age = ? where is = ? ;", Arrays.asList("Danny", 33, 2));
jdbc.executePstamt("insert into t_employee (name,age) values (?,?);", Arrays.asList("Liming", 21)); // 6.批量插入/更新(BLUK模式)
// 你的List中有多少个子list,就会插入多少条数据
List<List> allData = new ArrayList<>();
allData.add(Arrays.asList("Python", 17));
allData.add(Arrays.asList("Nodejs", 23));
jdbc.batchExecutePstamt("insert into t_employee (name,age) values (?,?)", allData); // 7.查询一个表的列名集合
List<String> cloumns = jdbc.selectColumns("t_employee"); // 8.验证一个表是否存在
boolean exist = jdbc.validateTableExist("t_employee"); // 9.获取插入语句:这里的第三个布尔变量,是决定是否使用去重复(IGNORE)插入
String insertSql = jdbc.getInsertSql("t_employee", Arrays.asList("name", "age"), true); // 10.获取建表语句
Map<String, String> fields = new LinkedHashMap<>();
fields.put("name", "varchar(64)");
fields.put("age", "int(32)");
String createTableSql1 = jdbc.createTableSql("t_employee", fields, "InnoDB");
String createTableSql2 = jdbc.createTableSql("t_employee", Arrays.asList("name", "age"), "InnoDB", "varchar(64)");
方法的返回值,传入参数类型,具体看注释.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.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 //
////////////////////////////////////////////////////////////////////////////////// /**
* @author lnexin@aliyun.com
*/
public class DataBaseUtil {
// 数据库配置文件地址
private static final String CONFIG_PATH = "config.properties"; private static final String DB_DRIVER;
private static final String DB_URL;
private static final String DB_USERNAME;
private static final String DB_PASSWORD; private Connection connection;
private PreparedStatement pstmt;
private ResultSet resultSet; static {
Properties p = new Properties();
try {
// 两种方式任选其一
// InputStream u = DataBaseUtil.class.getResourceAsStream("config.properties");
// p.load(u);
p.load(new FileInputStream(new File(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");
} public DataBaseUtil() {
} /**
* 使用配置文件中的连接信息获取连接
*/
public Connection 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;
} /////////////////////////////////////////////////////////////////////////////////////
/**
* 使用非配置文件的连接信息
*
* @param driver
* 数据路驱动
* @param url
* 数据库连接
* @param username
* 数据库用户名
* @param password
* 数据库密码
* @return Connection
*/
public Connection 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;
}
////////////////////////////////////////////////////////////////////////////////////// /**
* 插入
*
* @param sql
* prepareStatement格式的插入语句
* @param param
* 需要插入的单个数值
* @return 自增的情况下返回插入的主键
* @throws SQLException
*/
public Object insertReturnPK(String sql, Object param) throws SQLException {
return insertReturnPK(sql, Arrays.asList(param));
} /**
*
* @param sql
* prepareStatement格式的insert语句
* @param params
* 需要插入的多个参数,以list格式
* @return 返回插入语句的自增主键
* @throws SQLException
*/
public Object insertReturnPK(String sql, List params) throws SQLException {
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();
return pk;
} /**
* 执行某一条语句
*
* @param sql
* prepareStatement格式的sql语句
* @param param
* 填充的参数
* @return 返回是否执行成功
* @throws SQLException
*/
public boolean executePstamt(String sql, Object param) throws SQLException {
return executePstamt(sql, Arrays.asList(param));
} /**
* 执行某一条语句
*
* @param sql
* prepareStatement格式的sql语句
* @param param
* 填充的参数列表
* @return 返回是否执行成功
* @throws SQLException
*/
public boolean executePstamt(String sql, List params) throws SQLException {
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;
} /**
* 批量执行
*
* @param sql
* prepareStatement格式的sql语句
* @param lists
* 需要插入的数据列表,注:List<List> 格式,子list中为一条语句所填充的参数列表
* @return 返回是否执行成功
* @throws SQLException
*/
public boolean batchExecutePstamt(String sql, List<List> lists) throws SQLException {
connection.setAutoCommit(false);
boolean flag = false;
int resultNum = 0;
pstmt = connection.prepareStatement(sql);
if (lists != null && !lists.isEmpty()) {
for (List<Object> cList : 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;
return flag;
} /**
* 查询一条,返回结果
*
* @param sql
* prepareStatement格式语句
* @param param
* 不可为空
* @return 一个map集合
* @throws SQLException
*/
public Map selectOne(String sql, String param) throws SQLException {
return selectOne(sql, Arrays.asList(param));
} /**
* 查询一条,返回结果
*
* @param sql
* prepareStatement格式语句
* @param param
* 需要的参数列表,可为空
* @return 一个map集合
* @throws SQLException
*/
public Map selectOne(String sql, List params) throws SQLException {
Map map = new LinkedHashMap();
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();
return map;
} /**
* 查询多个结果的返回集
*
* @param sql
* prepareStatement格式语句
* @param params
* 需要填充的参数列表,可为空
* @return 一个list<map>的结果集
* @throws SQLException
*/
public List<Map> selectMore(String sql, List params) throws SQLException {
List<Map> list = new ArrayList<Map>();
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 = new LinkedHashMap();
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();
return list;
} /**
* 查询列名
*
* @param tanleN
* 表名称
* @return 列名称列表
* @throws SQLException
*/
public List<String> selectColumns(String tanleN) throws SQLException {
DatabaseMetaData data;
List<String> 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();
return result;
} /**
* 获取插入语句
*
* @param tableName
* @param fields
* @param isIGNORE
* 是否去重复
* @return
*/
public static String getInsertSql(String tableName, List<String> fields, boolean isIGNORE) {
StringBuffer sb = new StringBuffer();
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(";");
return sb.toString();
} /**
* 获取建表语句
*
* @param tableName
* @param fields
* Map<String,String> 结构为[字段名,字段数据类型],如:[{name=varchar(64),lastname=varchar(64)}]
* @param engine
* :InnoDB/MyISAM
* @return create sql
*/
public static String createTableSql(String tableName, Map<String, String> fields, String engine) {
StringBuffer sb = new StringBuffer();
sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");
int sign = 0;
for (Entry<String, String> entry : 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(";");
return sb.toString();
} /**
* 获取建表语句
*
* @param tableName
* @param fields
* 一个list<string> 的字段列表
* @param engine
* 数据库引擎类型
* @param defaultFieldType
* 默认数据字段的类型,如:varchar(64)
* @return
*/
public static String createTableSql(String tableName, List<String> fields, String engine, String defaultFieldType) {
StringBuffer sb = new StringBuffer();
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(";");
return sb.toString();
} /**
* 验证表是否存在
*
* @param tableName
* 表名称
* @return true/false
*/
public boolean validateTableExist(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();
}
return flag;
} /*
* 释放链接
*/
public void releaseConn() {
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 void closeResultSet() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} private void closePstmat() {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}