package cn.it58.util;
import cn.it58.pojo.Student;
import org.junit.jupiter.api.Test;
import java.lang.reflect.Field;
import java.sql.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;
/**
* @author geQiang
* @version 1.0
* @date 2019/5/23
* @description cn.it58.util
*/
public class DBUtil {
private String url;
private String username;
private String password;
//执行数据库配置文件读取
{
Properties properties = new Properties();
InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(in);
url = properties.getProperty("url");
//System.out.println(url);
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获得数据库连接
*
* @return
*/
public Connection getConnection() {
try {
Connection ct = DriverManager.getConnection(url, username, password);
//关闭事务的自动提交
ct.setAutoCommit(false);
//设数据库事务的隔离级别 8 SERIALIZABLE
ct.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
return ct;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭数据库连接，有顺序要求
*/
public void close(Connection ct, PreparedStatement st, ResultSet rt) {
if (rt != null) {
try {
rt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ct != null) {
try {
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获得数据库的表名称
*
* @return
*/
public List<String> getAllTableNames() {
List<String> list = new CopyOnWriteArrayList<>();
String sql = " show tables ";
Connection ct = getConnection();
PreparedStatement st = null;
ResultSet rt = null;
try {
st = ct.prepareStatement(sql);
rt = st.executeQuery();
while (rt.next()) {
String tableName = rt.getString(1);
list.add(tableName);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(ct, st, rt);
}
return list;
}
@Test
public void testGetAllTableNames() {
for (String tableName : getAllTableNames()) {
System.out.println(tableName);
}
}
/**
* 获得指定表名的字段名称和数据类型
*
* @param tableName
* @return
*/
public Map<String, String> getTableMetaData(String tableName) {
Connection ct = getConnection();
Map<String, String> map = new ConcurrentHashMap<>();
ResultSet rt = null;
try {
DatabaseMetaData metaData = ct.getMetaData();
rt = metaData.getColumns(ct.getCatalog(), "%", tableName, "%");
System.out.println(ct.getCatalog());
String columnName = "";
String columnType = "";
//map是无序的，所以设置游标顺序无用
rt.setFetchDirection(ResultSet.FETCH_REVERSE);
while (rt.next()) {
map.put(rt.getString("COLUMN_NAME"), rt.getString("TYPE_NAME"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(ct, null, rt);
}
return map;
}
@Test
public void testGetTableMetaData() {
System.out.println(getTableMetaData("account"));
}
/**
* 通用的查询对象集合的方法
*
* @param tClass 何种数据类型
* @param sql sql语句
* @param args sql参数集合
* @return 存放根据sql语句查询出对象的集合
*/
public List query(Class tClass, String sql, List args) {
//存放查询记录的集合
List list = new CopyOnWriteArrayList<>();
//如果sql是null，直接返回空集合
if (sql == null) {
return list;
}
//打印sql语句
System.out.println(sql);
//获得连接对象
Connection ct = getConnection();
//定义sql预处理对象
PreparedStatement st = null;
//定义结果集对象
ResultSet rt = null;
try {
//预处理sql语句
st = ct.prepareStatement(sql);
//添加参数，先判断，避免空指针
if (args != null) {
for (int i = 0; i < args.size(); i++) {
//变量sql参数集合，添加sql参数
st.setObject((i + 1), args.get(i));
}
}
//执行查询操作
rt = st.executeQuery();
//获得结果集数据描述对象metaData
ResultSetMetaData metaData = rt.getMetaData();
//定义存放字段和字段值的map
Map<String, Object> map = new ConcurrentHashMap<>();
//获得当前类的所有属性集合
Field[] fields = tClass.getDeclaredFields();
//遍历结果集
while (rt.next()) {
//当前类的对象
Object object = tClass.newInstance();
//遍历结果集数据描述对象
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获得列名
String cloumnName = metaData.getColumnName(i + 1);
//获得列名对应的值
Object value = rt.getObject(cloumnName);
//把列名和列的值存放到map中
map.put(cloumnName, value);
}
//遍历map中的Entry实例
for (Map.Entry<String, Object> entry : map.entrySet()) {
//获得实例的key值
String fieldName = entry.getKey();
//获得实例的value值
Object value = entry.getValue();
//遍历当前类的属性，属性和字段名必须一致，否则无法把字段和属性进行匹配
for (Field field : fields) {
//获得属性名
String name = field.getName();
//把属性名和字段名转小写，进行比对，如果相同，就把字段值赋值给属性
if (fieldName.toLowerCase().equals(name.toLowerCase())) {
//修改属性的可见性private-->public
field.setAccessible(true);
//当前类的对象object的name属性赋值为value
field.set(object, value);
}
}
}
list.add(object);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(ct, st, rt);
}
return list;
}
@Test
public void testQuery() {
String sql = " select s.*,c.caption from student s,clazz c where s.classId = c.id and s.id < ? ";
List params = new CopyOnWriteArrayList<>();
params.add(100);
List list = query(Student.class, sql, params);
for (Object obj : list) {
System.out.println(obj);
}
}
/**
* 通用的查询总数的方法
*
* @param sql
* @param args
* @return
*/
public int querySum(String sql, List args) {
//如果sql是null，直接返回空集合
if (sql == null) {
return 0;
}
//打印sql语句
System.out.println(sql);
//获得连接对象
Connection ct = getConnection();
//定义sql预处理对象
PreparedStatement st = null;
//定义结果集对象
ResultSet rt = null;
try {
st = ct.prepareStatement(sql);
if (args != null) {
for (int i = 0, size = args.size(); i < size; i++) {
st.setObject((i + 1), args.get(i));
}
}
rt = st.executeQuery();
if (rt.next()) {
return rt.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(ct, st, rt);
}
return 0;
}
@Test
public void testQuerySum() {
String sql = " select count(1) from student where studentName like ? ";
List params = Arrays.asList("王%");
System.out.println(querySum(sql, params));
}
/**
* 通用的增、删、改操作
*
* @param sql
* @param args
* @param operation
* @return
*/
public Long update(String sql, List args, Operation operation) {
if (sql == null) {
return 0L;
}
//打印sql语句
System.out.println(sql);
//获得连接对象
Connection ct = getConnection();
//定义sql预处理对象
PreparedStatement st = null;
//定义结果集对象
ResultSet rt = null;
try {
if (operation == Operation.INSERT) {
st = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
} else {
st = ct.prepareStatement(sql);
}
if (args != null) {
for (int i = 0, size = args.size(); i < size; i++) {
st.setObject((i + 1), args.get(i));
}
}
long num = st.executeUpdate();
ct.commit();
if (operation == Operation.INSERT) {
rt = st.getGeneratedKeys();
if (rt.next()) {
long id = rt.getLong(1);
return id;
}
} else {
return num;
}
} catch (SQLException e) {
e.printStackTrace();
try {
ct.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
close(ct, st, rt);
}
return 0L;
}
@Test
public void add() {
String sql = " insert into student (studentName,gender,classId,birthday) values(?,?,?,?) ; ";
List args = Arrays.asList("root123", "男", 3, "1998-12-12 00:00:00");
//49727
System.out.println(update(sql, args, Operation.INSERT));
/*
mysql> select * from student where id = 49727;
+-------+-------------+--------+---------+---------------------+
| id | studentName | gender | classId | birthday |
+-------+-------------+--------+---------+---------------------+
| 49727 | root123 | 男 | 3 | 1998-12-12 00:00:00 |
+-------+-------------+--------+---------+---------------------+
*/
}
@Test
public void up() {
String sql = " update student set studentName=?,birthday=? where id =? ";
List args = Arrays.asList("root1234", "1997-10-12 00:00:00", 49727);
//1
System.out.println(update(sql, args, Operation.UPDATE));
/*
mysql> select * from student where id = 49727;
+-------+-------------+--------+---------+----------------------+
| id | studentName | gender | classId | birthday |
+-------+-------------+--------+---------+----------------------+
| 49727 | root1234 | 男 | 3 | 1997-10-12 00:00:00 |
+-------+-------------+--------+---------+----------------------+
*/
}
}
