一个用来操作数据库的常用工具类.
提供批量操作,生成建表,插入语句等
操作示例:
// 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(); } } } }