package com.aliyun.task.monitor.util;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
/**
* @Auther: liyue
* @Date: 2020/6/19 15:48
* @Description:
*/
public class JdbcUtil {
/**
* 获取连接
*
* @param propertiesPath 为resources下相对路径
* @return
*/
public static Connection getConn(String propertiesPath) {
Connection conn = null;
try {
Properties properties = PropertiesLoaderUtils.loadAllProperties(propertiesPath);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 获取连接池
*
* @param propertiesPath 为resources下相对路径
* @return
*/
public static DataSource getDataSourceInstance(String propertiesPath) {
DataSource dataSource = null;
try {
Properties properties = PropertiesLoaderUtils.loadAllProperties(propertiesPath);
//创建dataSource对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
return dataSource;
}
/**
* 获取建表语句
*
* @param conn
* @param table
* @return
*/
public static String showCreateTableSql(Connection conn, String table) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
long t1 = System.currentTimeMillis();
String sql = "SHOW CREATE TABLE " + table;
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
String str = null;
while (rs.next()) {
String string = rs.getString(2);
if (string != null)
str = string;
}
return str;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 查询返回封装对象列表
*
* @param conn
* @param sql
* @param T
* @param params
* @param <T>
* @return
*/
public static <T> List<T> select(Connection conn, String sql, Class<T> T, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
List<T> list = new LinkedList<>();
rs = ps.executeQuery();
while (rs.next()) {
T t = rsToObject(rs, T);
list.add(t);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 查询返回一个对象
*
* @param conn
* @param sql
* @param T
* @param params
* @param <T>
* @return
*/
public static <T> T selectOne(Connection conn, String sql, Class<T> T, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
T t = null;
while (rs.next()) {
t = rsToObject(rs, T);
break;
}
long t2 = System.currentTimeMillis();
return t;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 查询返回第一行第一列数值
*
* @param conn
* @param sql
* @param params
* @return
*/
public static String selectOneField(Connection conn, String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
String result = null;
while (rs.next()) {
result = rs.getString(1);
break;
}
return result;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 执行SQL,返回影响行数
*
* @param conn
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static int execute(Connection conn, String sql, Object... params) throws SQLException {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
return ps.executeUpdate();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 统计数量
*
* @param conn
* @param sql
* @param params
* @return
*/
public static int count(Connection conn, String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
int result = 0;
while (rs.next()) {
result = rs.getInt(1);
}
long t2 = System.currentTimeMillis();
return result;
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 返回查询结果第一列
*
* @param conn
* @param sql
* @param params
* @return
*/
public static List<String> selectListOneField(Connection conn, String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
List<String> result = new LinkedList<>();
while (rs.next()) {
result.add(rs.getString(1));
}
return result;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 封装对象
*
* @param rs
* @param clazz
* @param <T>
* @return
*/
private static <T> T rsToObject(ResultSet rs, Class<T> clazz) {
try {
if (null == rs) {
return null;
}
List<String> names = getObjectAttributeNames(clazz);
StringBuffer json = new StringBuffer();
json.append("{");
for (String name : names) {
json.append("\"").append(name);
String value = null;
if (StringUtils.isBlank(value)) {
String newName = bigLetterToSmallAndBeforeAdd_(name);
try {
value = rs.getString(newName);
} catch (Exception e) {
value = "";
}
}
if (StringUtils.isBlank(value)) {
try {
value = rs.getString(name);
} catch (Exception e) {
}
}
json.append("\":\"").append(value).append("\"").append(",");
}
json.delete(json.length() - 1, json.length());
json.append("}");
T t = JsonUtils.jsonToPojo(json.toString().replaceAll("null", "").replaceAll("\\\r\\\n", ""), clazz);
return t;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
/**
* 大写字母转小写并在该字母前面加_
*
* @param name
* @return
*/
private static String bigLetterToSmallAndBeforeAdd_(String name) {
StringBuffer stringBuffer = new StringBuffer();
char[] chars = name.toCharArray();
for (char ch : chars) {
boolean digit = Character.isUpperCase(ch);
if (digit) {
String str = String.valueOf(ch);
String lstr = str.toLowerCase();
stringBuffer.append("_");
stringBuffer.append(lstr);
} else {
stringBuffer.append(ch);
}
}
return stringBuffer.toString();
}
/**
* 获取对象的所有属性值
*
* @param clazzT
* @param <T>
* @return
*/
public static <T> List<String> getObjectAttributeNames(Class<T> clazzT) {
List<String> list = new LinkedList<>();
try {
//根据类名获得其对应的Class对象 写上你想要的类名就是了 注意是全名 如果有包的话要加上 比如java.Lang.String
Class clazz = Class.forName(clazzT.getName());
//根据Class对象获得属性 私有的也可以获得
Field[] fields = clazz.getDeclaredFields();
for (Field f : fields) {
list.add(f.getName());
// 类型
// f.getType();
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
依赖
org.apache.commons.lang3
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.1</version>
</dependency>
org.springframework.core
org.apache.commons.dbcp2
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
JsonUtils