package Part;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import org.apache.commons.beanutils.BeanUtils;
import Part.ReflectionUtils;
/*
* 与JDBCTools不同点:少了很多注释,少了很多落后的方法(Statement),更新和查询操作使用了连接池技术,优化了速度。
* 同时因为连接池技术,不用频繁关闭连接,但仍然提供了关闭连接的方法
*/
public class JDBCTool {
//开始事务
public static void begin(Connection connection) throws SQLException {
connection.setAutoCommit(false);
}
//提交事务
public static void commit(Connection connection) throws SQLException {
connection.commit();
}
//回滚事务
public static void rollback(Connection connection) throws SQLException {
connection.rollback();
}
//获取连接方法(从连接池里获取连接)
public static Connection getConnection() throws Exception {
//读取配置文件
Properties properties=new Properties();
InputStream in=JDBCTools.class.getClassLoader().getResourceAsStream("dbcp.properties");
properties.load(in);
//配置文件传入连接池
DataSource dataSource=BasicDataSourceFactory.createDataSource(properties);
return dataSource.getConnection();
}
//关闭连接方法
//关闭俩个对象方法
public static void release(Connection connection,Statement statement) throws SQLException {
if(connection!=null) {
connection.close();
}
if(statement!=null) {
statement.close();
}
}
//关闭三个对象方法
public static void release(Connection connection,Statement statement,ResultSet resultset) throws SQLException {
if(connection!=null) {
connection.close();
}
if(statement!=null) {
statement.close();
}
if(resultset!=null) {
resultset.close();
}
}
//执行sql插入、删除或更新的通用方法(利用PreparedStatement)
public static void Update(String sql,Object ... obj) throws Exception {//Object ... agrs表示一个参数列表(Object数组),
//可以为任意类型,任意个数
Connection connection=getConnection();
PreparedStatement preparedstatement=connection.prepareStatement(sql);
for(int i=0;i<obj.length;i++) {
preparedstatement.setObject(i+1,obj[i]);
}
preparedstatement.executeUpdate();
}
//执行sql查询的通用方法(利用PreparedStatement)
public static ResultSet Select(String sql,Object ...obj) throws Exception {
Connection connection=getConnection();
PreparedStatement preparedstatement=connection.prepareStatement(sql);
for(int i=0;i<obj.length;i++) {
preparedstatement.setObject(i+1,obj[i]);
}
return preparedstatement.executeQuery();
}
//查询一条记录
//传入SQL查询语句和类,返回一个与其一一对应属性的类
//即返回一个记录类
public static <T> T get(Class<T> clazz,String sql,Object ...obj) throws Exception {
T Record=null;
ResultSet resultset=Select(sql,obj);
if(resultset.next()) { //如果查询结果不为空
Record=clazz.newInstance(); //实例化对象
ResultSetMetaData resultsetmetadata=resultset.getMetaData();
Map<String,Object>map=new HashMap<>();
for(int i=0;i<resultsetmetadata.getColumnCount();i++) {
map.put(resultsetmetadata.getColumnName(i+1),resultset.getObject(i+1));
}
for(Map.Entry<String,Object> entry:map.entrySet()) { //取出map里每条记录entry(Key-Value)
ReflectionUtils.setFieldValue(Record, entry.getKey(),entry.getValue());
}
}
return Record;
}
//查询多条记录,返回记录对象的列表
public static <T> List<T> getList(Class<T> clazz,String sql,Object ...obj) throws Exception{
ResultSet resultset=Select(sql,obj);
List<T> list=ListMap_To_List(clazz,Result_To_ListMap(resultset));
return list;
}
//传入ResultSet得到一个记录对象列表(List<Map<String,Object>>类型,Map的键为列名,值为列值)
public static List<Map<String, Object>> Result_To_ListMap(ResultSet resultset)throws SQLException {
List<Map<String,Object>> list=null;
ResultSetMetaData resultsetmetadata=resultset.getMetaData();
int columnCount=resultsetmetadata.getColumnCount();
ArrayList<String>columnName=new ArrayList<String>();
for(int i=0;i<columnCount;i++) {
columnName.add(resultsetmetadata.getColumnName(i+1));
}
list=new ArrayList<>();
while(resultset.next()) {
Map<String,Object>map=new HashMap<>();
for(int i=0;i<columnCount;i++) {
map.put(columnName.get(i),resultset.getObject(i+1));
}
list.add(map);
}
return list;
}
//传入一个ListMap和记录对象类型获得一个对象列表List
public static <T> List<T> ListMap_To_List(Class<T> clazz, List<Map<String, Object>> listMap)
throws InstantiationException, IllegalAccessException, InvocationTargetException {
List<T> list=null;
T Record=null;
if(listMap.size()>0) {
list=new ArrayList<>();
for(Map<String,Object>map:listMap) { //取出listMap里每个map对象
Record=clazz.newInstance();
for(Map.Entry<String,Object>entry:map.entrySet()) {//取出map里的每个记录(entry:Key-Value)对象
ReflectionUtils.setFieldValue(Record,entry.getKey(),entry.getValue());
}
list.add(Record);
}
}
return list;
}
//返回某条记录的某一字段的值或一个统计的值(一共有多少条记录等)
public static <E> E getForValue(String sql,Object ...obj) throws Exception {
ResultSet resultset=Select(sql,obj);
resultset.next();
return (E) resultset.getObject(1);
}
//批量处理SQL语句
public static void BatchSQL(String sql,int count,Object ...obj) throws Exception{
Connection connection=getConnection();
try {
begin(connection);
PreparedStatement preparedstatement=connection.prepareStatement(sql);
for(int i=0;i<obj.length;i++) {
preparedstatement.setObject(i+1,obj[i]);
}
for(int i=0;i<count;i++) {
preparedstatement.addBatch(); //积攒sql语句
}
preparedstatement.executeBatch(); //发送sql语句
preparedstatement.clearBatch(); //清空sql语句
commit(connection);
}catch(Exception e) {
rollback(connection);
e.printStackTrace();
}
}
}