J2EE课程结课作业

package edu.njust.utils; //import com.sun.istack.internal.Nullable; import java.sql.*; import java.util.*; import java.util.regex.Pattern; /** * 数据库JDBC连接工具类 * Created by yuandl on 2016-12-16. */ public class DBUtil { /** * 执行数据库插入操作 * * @param valueMap 插入数据表中key为列名和value为列对应的值的Map对象 * @param tableName 要插入的数据库的表名 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException { /**获取数据库插入的Map的键值对的值**/ Set<String> keySet = valueMap.keySet(); Iterator<String> iterator = keySet.iterator(); /**要插入的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); /**要插入的字段值，其实就是？**/ StringBuilder unknownMarkSql = new StringBuilder(); Object[] bindArgs = new Object[valueMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); columnSql.append(i == 0 ? "" : ","); columnSql.append(key); unknownMarkSql.append(i == 0 ? "" : ","); unknownMarkSql.append("?"); bindArgs[i] = valueMap.get(key); i++; } /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO "); sql.append(tableName); sql.append(" ("); sql.append(columnSql); sql.append(" ) VALUES ("); sql.append(unknownMarkSql); sql.append(" )"); return executeUpdate(sql.toString(), bindArgs); } /** * 执行数据库插入操作 * * @param datas 插入数据表中key为列名和value为列对应的值的Map对象的List集合 * @param tableName 要插入的数据库的表名 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int insertAll(String tableName, List<Map<String, Object>> datas) throws SQLException { /**影响的行数**/ int affectRowCount = -1; Connection connection = null; PreparedStatement preparedStatement = null; try { /**从数据库连接池中获取数据库连接**/ connection = DBConnectionPool.getInstance().getConnection(); Map<String, Object> valueMap = datas.get(0); /**获取数据库插入的Map的键值对的值**/ Set<String> keySet = valueMap.keySet(); Iterator<String> iterator = keySet.iterator(); /**要插入的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); /**要插入的字段值，其实就是？**/ StringBuilder unknownMarkSql = new StringBuilder(); Object[] keys = new Object[valueMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); keys[i] = key; columnSql.append(i == 0 ? "" : ","); columnSql.append(key); unknownMarkSql.append(i == 0 ? "" : ","); unknownMarkSql.append("?"); i++; } /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO "); sql.append(tableName); sql.append(" ("); sql.append(columnSql); sql.append(" ) VALUES ("); sql.append(unknownMarkSql); sql.append(" )"); /**执行SQL预编译**/ preparedStatement = connection.prepareStatement(sql.toString()); /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ connection.setAutoCommit(false); System.out.println(sql.toString()); for (int j = 0; j < datas.size(); j++) { for (int k = 0; k < keys.length; k++) { preparedStatement.setObject(k + 1, datas.get(j).get(keys[k])); } preparedStatement.addBatch(); } int[] arr = preparedStatement.executeBatch(); connection.commit(); affectRowCount = arr.length; System.out.println("成功了插入了" + affectRowCount + "行"); System.out.println(); } catch (Exception e) { if (connection != null) { connection.rollback(); } e.printStackTrace(); throw e; } finally { if (preparedStatement != null) { preparedStatement.close(); } if (connection != null) { connection.close(); } } return affectRowCount; } /** * 执行更新操作 * * @param tableName 表名 * @param valueMap 要更改的值 * @param whereMap 条件 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap) throws SQLException { /**获取数据库插入的Map的键值对的值**/ Set<String> keySet = valueMap.keySet(); Iterator<String> iterator = keySet.iterator(); /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("UPDATE "); sql.append(tableName); sql.append(" SET "); /**要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); int i = 0; List<Object> objects = new ArrayList<>(); while (iterator.hasNext()) { String key = iterator.next(); columnSql.append(i == 0 ? "" : ","); columnSql.append(key + " = ? "); objects.add(valueMap.get(key)); i++; } sql.append(columnSql); /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder whereSql = new StringBuilder(); int j = 0; if (whereMap != null && whereMap.size() > 0) { whereSql.append(" WHERE "); iterator = whereMap.keySet().iterator(); while (iterator.hasNext()) { String key = iterator.next(); whereSql.append(j == 0 ? "" : " AND "); whereSql.append(key + " = ? "); objects.add(whereMap.get(key)); j++; } sql.append(whereSql); } return executeUpdate(sql.toString(), objects.toArray()); } /** * 执行删除操作 * * @param tableName 要删除的表名 * @param whereMap 删除的条件 * @return 影响的行数 * @throws SQLException SQL执行异常 */ public static int delete(String tableName, Map<String, Object> whereMap) throws SQLException { /**准备删除的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("DELETE FROM "); sql.append(tableName); /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder whereSql = new StringBuilder(); Object[] bindArgs = null; if (whereMap != null && whereMap.size() > 0) { bindArgs = new Object[whereMap.size()]; whereSql.append(" WHERE "); /**获取数据库插入的Map的键值对的值**/ Set<String> keySet = whereMap.keySet(); Iterator<String> iterator = keySet.iterator(); int i = 0; while (iterator.hasNext()) { String key = iterator.next(); whereSql.append(i == 0 ? "" : " AND "); whereSql.append(key + " = ? "); bindArgs[i] = whereMap.get(key); i++; } sql.append(whereSql); } ret