import com.alibaba.druid.pool.DruidDataSource; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.sql.*; import java.util.*; public class DBUtils { public static String DRIVER_CLASS; public static String URL; public static String USER; public static String PASSWORD; public static int INIT_SIZE; public static int MAX_SIZE; public static long MAX_WAIT; public static DruidDataSource dataSource; static { try { InputStream is = DBUtils.class.getResourceAsStream("/jdbc.properties"); Properties prop = new Properties(); prop.load(is); DRIVER_CLASS = prop.getProperty("jdbc.driver"); URL = prop.getProperty("jdbc.url"); USER = prop.getProperty("jdbc.user"); PASSWORD = prop.getProperty("jdbc.password"); String initSize = prop.getProperty("jdbc.initSize"); String maxActive = prop.getProperty("jdbc.maxActive"); String maxWait = prop.getProperty("jdbc.maxWait"); INIT_SIZE = initSize == null ? 5 : Integer.parseInt(initSize); MAX_SIZE = maxActive == null ? 10 : Integer.parseInt(maxActive); MAX_WAIT = maxWait == null ? 5000L : Long.parseLong(maxWait); dataSource = new DruidDataSource(); dataSource.setDriverClassName(DRIVER_CLASS); dataSource.setUrl(URL); dataSource.setUsername(USER); dataSource.setPassword(PASSWORD); dataSource.setInitialSize(INIT_SIZE); dataSource.setMaxActive(MAX_SIZE); dataSource.setMaxWait(MAX_WAIT); dataSource.init(); } catch (SQLException | IOException e) { e.printStackTrace(); } } public static Connection getConn() { try { return dataSource.getConnection(); } catch (SQLException throwables) { throwables.printStackTrace(); } return null; } public static void close(ResultSet rs, Statement stat, Connection conn) { try { if (Objects.nonNull(rs)) { rs.close(); } if (Objects.nonNull(stat)) { stat.close(); } if (Objects.nonNull(conn)) { conn.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } public static List<Map<String, Object>> queryList(String sql, Object... params) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = getConn(); ps = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } rs = ps.executeQuery(); return queryToMapList(rs); } catch (SQLException e) { e.printStackTrace(); } finally { close(rs, ps, conn); } return null; } private static List<Map<String, Object>> queryToMapList(ResultSet rs) throws SQLException { List<Map<String, Object>> list = new ArrayList<>(); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); while (rs.next()) { Map<String, Object> line = new HashMap<>(); for (int i = 1; i <= columnCount; i++) { String key = rsmd.getColumnLabel(i); Object value = rs.getObject(key); line.put(key, value); } list.add(line); } return list; } public static Map<String, Object> queryOne(String sql, Object... params) { List<Map<String, Object>> maps = queryList(sql, params); if (maps.isEmpty()) { return null; } else { return maps.get(0); } } private static boolean exeUpdate(String sql, Object... params) { Connection conn = null; PreparedStatement ps = null; try { conn = getConn(); try { ps = conn.prepareStatement(sql); } catch (SQLException e) { e.printStackTrace(); } for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } return ps.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); } finally { close(null, ps, conn); } return false; } private static boolean exeUpdate(Connection conn, String sql, Object... params) throws SQLException { PreparedStatement ps = null; try { conn = getConn(); ps = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } return ps.executeUpdate() > 0; } finally { close(null, ps, null); } } public static boolean insert(String sql, Object... params) { return exeUpdate(sql, params); } public static boolean insert(Connection conn, String sql, Object... params) throws SQLException { return exeUpdate(conn, sql, params); } public static boolean delete(String sql, Object... params) { return exeUpdate(sql, params); } public static boolean delete(Connection conn, String sql, Object... params) throws SQLException { return exeUpdate(conn, sql, params); } public static boolean update(String sql, Object... params) { return exeUpdate(sql, params); } public static boolean update(Connection conn, String sql, Object... params) throws SQLException { return exeUpdate(conn, sql, params); } public static Long insertGetKey(Connection conn, String sql, Object... params) throws SQLException { PreparedStatement ps = null; ResultSet rs = null; try { conn = getConn(); ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } if (ps.executeUpdate() > 0) { rs = ps.getGeneratedKeys(); if (rs.next()) { return rs.getLong(1); } } } finally { close(rs, ps, null); } return -1L; } public static Long insertGetKey(String sql, Object... params) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = getConn(); try { ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); } catch (SQLException e) { e.printStackTrace(); } for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } if (ps.executeUpdate() > 0) { rs = ps.getGeneratedKeys(); if (rs.next()) { return rs.getLong(1); } } } catch (SQLException e) { e.printStackTrace(); } finally { close(rs, ps, conn); } return -1L; } public static <T> T queryOne(Class<T> t, String sql, Object... params) { Map<String, Object> map = queryOne(sql, params); return mapToBean(t, map); } public static <T> List<T> queryList(Class<T> t, String sql, Object... params) { List<T> list1 = new ArrayList<>(); List<Map<String, Object>> list = queryList(sql, params); list.forEach(map -> { T obj = mapToBean(t, map); list1.add(obj); }); return list1; } private static <T> T mapToBean(Class<T> t, Map<String, Object> map) { if (Objects.isNull(map)) { return null; } T obj = null; try { obj = t.newInstance(); Field[] fields = t.getDeclaredFields(); for (Field f : fields) { String key = f.getName(); Object val = map.get(key); if (Objects.nonNull(val)) { f.setAccessible(true); f.set(obj, val); } } } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } return obj; } /** * 执行更新操作 * * @param t * @param <T> * @return * @throws IllegalAccessException * @throws SQLException */ public static <T> boolean update(T t) throws IllegalAccessException, SQLException { //获取类名 即表名 String className = t.getClass().getSimpleName().toLowerCase(); ArrayList<String> list = new ArrayList<>(); Object id = null; Object key = null; Object value = null; for (Field d : t.getClass().getDeclaredFields()) { d.setAccessible(true); key = d.getName(); //当前当前字段是否为id字段 if (!Objects.equals(key, "id")) { value = d.get(t); //如果不是id字段,继续判断该字段的值是否为空 if (!Objects.isNull(value)) { //如果当前值不为空,则将字段和值拼接后传入集合中 list.add(key + "='" + value + "',"); } } else { //获取id的值 id = d.get(t); } } String sql = "update " + className + " set "; for (String s : list) { sql += s; } //去掉最后一个, sql = sql.substring(0, sql.length() - 1) + " where id=?"; return DBUtils.update(sql, id); } public static <T> boolean update(Connection conn, T t) throws IllegalAccessException, SQLException { //获取类名 即表名 String className = t.getClass().getSimpleName().toLowerCase(); ArrayList<String> list = new ArrayList<>(); Object id = null; Object key = null; Object value = null; for (Field d : t.getClass().getDeclaredFields()) { d.setAccessible(true); key = d.getName(); //当前当前字段是否为id字段 if (!Objects.equals(key, "id")) { value = d.get(t); //如果不是id字段,继续判断该字段的值是否为空 if (!Objects.isNull(value)) { //如果当前值不为空,则将字段和值拼接后传入集合中 list.add(key + "='" + value + "',"); } } else { //获取id的值 id = d.get(t); } } String sql = "update " + className + " set "; for (String s : list) { sql += s; } //去掉最后一个, sql = sql.substring(0, sql.length() - 1) + " where id=?"; return DBUtils.update(conn, sql, id); } /** * 执行插入操作 * * @param obj * @param <T> * @return * @throws IllegalAccessException * @throws SQLException */ public static <T> boolean insert(T obj) throws IllegalAccessException, SQLException { Class<?> t = obj.getClass(); List<Object> list = new ArrayList<>(); String sql = ""; sql += "insert into " + t.getSimpleName().toLowerCase() + "("; Field[] fields = t.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); Object o = fields[i].get(obj); if (o != null) { if (!Objects.equals(fields[i].getName(), "id")) { sql += (fields[i].getName() + ","); } list.add(o); } } sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; for (int i = 0; i < list.size(); i++) { sql += "?,"; } sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; return insert(sql, list.toArray()); } public static <T> Long insertGetKey(T obj) throws IllegalAccessException, SQLException { Class<?> t = obj.getClass(); List<Object> list = new ArrayList<>(); String sql = ""; sql += "insert into " + t.getSimpleName().toLowerCase() + "("; Field[] fields = t.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); Object o = fields[i].get(obj); if (o != null) { if (!Objects.equals(fields[i].getName(), "id")) { sql += (fields[i].getName() + ","); } list.add(o); } } sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; for (int i = 0; i < list.size(); i++) { sql += "?,"; } sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; return insertGetKey(sql, list.toArray()); } /** * 执行插入操作 * * @param obj * @param <T> * @return * @throws IllegalAccessException * @throws SQLException */ public static <T> boolean insert(Connection conn, T obj) throws IllegalAccessException, SQLException { Class<?> t = obj.getClass(); List<Object> list = new ArrayList<>(); String sql = ""; sql += "insert into " + t.getSimpleName().toLowerCase() + "("; Field[] fields = t.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); Object o = fields[i].get(obj); if (o != null) { if (!Objects.equals(fields[i].getName(), "id")) { sql += (fields[i].getName() + ","); } list.add(o); } } sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; for (int i = 0; i < list.size(); i++) { sql += "?,"; } sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; return insert(conn, sql, list.toArray()); } public static <T> Long insertGetKey(Connection conn, T obj) throws IllegalAccessException, SQLException { Class<?> t = obj.getClass(); List<Object> list = new ArrayList<>(); String sql = ""; sql += "insert into " + t.getSimpleName().toLowerCase() + "("; Field[] fields = t.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { fields[i].setAccessible(true); Object o = fields[i].get(obj); if (o != null) { if (!Objects.equals(fields[i].getName(), "id")) { sql += (fields[i].getName() + ","); } list.add(o); } } sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; for (int i = 0; i < list.size(); i++) { sql += "?,"; } sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; return insertGetKey(conn, sql, list.toArray()); } public static <T> List<T> queryList(Class<T> t, T t1) throws IllegalAccessException, SQLException { //获取类名 即表名 String className = t.getSimpleName().toLowerCase(); ArrayList<String> list = new ArrayList<>(); Object id = null; Object key = null; Object value = null; int count = 0; for (Field d : t.getDeclaredFields()) { d.setAccessible(true); key = d.getName(); value = d.get(t1); //如果不是id字段,继续判断该字段的值是否为空 if (!Objects.isNull(value)) { count++; //如果当前值不为空,则将字段和值拼接后传入集合中 list.add(key + "='" + value + "' and "); } } StringBuilder sql = new StringBuilder("select * from " + className + " where "); if (count > 0) { for (String s : list) { sql.append(s); } //去掉最后一个, sql = new StringBuilder(sql.substring(0, sql.length() - 5)); } else { sql = new StringBuilder(sql.substring(0, sql.length() - 6)); } return queryList(t, sql.toString()); } }
09-04
01-02