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());
}
}
07-15
1032
1032
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
