package util;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
public class ConnectionUtil {
//私有化
private ConnectionUtil() {}
private static ConnectionUtil connectionUtil =new ConnectionUtil();
public static ConnectionUtil getInstance() {
return connectionUtil;
}
/**
* 获取连接
* @return
*/
public static Connection getConnection() {
//加载配置文件
InputStream inputStream = ConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
try {
properties.load(inputStream);
} catch (IOException e1) {
// TODO Auto-generated catch block
new JdbcException("配置文件加载流错误", e1);
}
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Connection connection = null;
try {
//加载驱动
Class.forName(driver);
//获取连接
connection = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
new JdbcException("获取连接错误", e);
} catch (ClassNotFoundException e) {
new JdbcException("加载驱动错误", e);
}
return connection;
}
/**
* 关闭资源
* @param connection
* @param preparedStatement
*
* @param resultSet
*/
public static void close(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet) {
try {
if (preparedStatement!=null&& preparedStatement.isClosed()) {
preparedStatement.close();
}
} catch (SQLException e) {
new JdbcException("关闭preparedStatement错误", e);
}
try {
if (connection!=null&&connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
new JdbcException("关闭connection错误", e);
}
try {
if (resultSet!=null&&resultSet.isClosed()) {
resultSet.close();
}
} catch (SQLException e) {
new JdbcException("关闭resultSet错误", e);
}
}
/**
* //增删改 支持事务 需手动关闭资源
* @param sql
* @param args:占位符
* @throws Exception
*/
public static int updateTX(Connection connection, String sql,Object... args) {
//关闭自动提交
try {
connection.setAutoCommit(false);
} catch (SQLException e1) {
new JdbcException("设置自动提交错误", e1);
}
int num =-1;
//预编译sql
PreparedStatement preparedStatement = null;
try {
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
//执行 返回有执行成功 >0
num = preparedStatement.executeUpdate();
} catch (Exception e) {
new JdbcException("预处理错误", e);
}finally{
ConnectionUtil.close(null, preparedStatement, null);
}
if (num>0) {
System.out.println("执行成功");
System.out.println("影响了:"+num+"行数据");
}else {
System.out.println("执行失败");
}
return num;
}
/**
* //增删改 单条执行
* @param sql
* @param args:占位符
* @throws Exception
*/
public static int update(String sql,Object... args) {
int num =-1;
//获取数据库的连接
Connection connection = null;
//预编译sql
PreparedStatement preparedStatement = null;
try {
connection = ConnectionUtil.getConnection();
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
//执行 返回有执行成功 >0
num = preparedStatement.executeUpdate();
} catch (Exception e) {
new JdbcException("预处理错误", e);
}finally
{
//关闭
ConnectionUtil.close(connection,preparedStatement, null);
}
if (num>0) {
System.out.println("执行成功");
System.out.println("影响了:"+num+"行数据");
}else {
System.out.println("执行失败");
}
return num;
}
/**
* search 查询操作
* @param class1
* @param sql
* @param objects
* @return
*/
public static <T> T search(Class<T> class1,String sql,Object...objects ) {
Connection connection =null;
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
connection =ConnectionUtil.getConnection();
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i+1, objects[i]);
}
//获取结果集
resultSet =preparedStatement.executeQuery();
//获取结构集的元数据
ResultSetMetaData data =null;
data =(ResultSetMetaData) resultSet.getMetaData();
//获取结果集列数
int columnCount = data.getColumnCount();
//处理结果集
if (resultSet.next()) {
T t =class1.newInstance();
//获取结果集中各个列的值
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnVal = resultSet.getObject(i+1);
//获取结果集中字段的名字
String colmnLabel = data.getColumnLabel(i+1);
//反射给cust的属性赋值
Field field = class1.getDeclaredField(colmnLabel);
field.setAccessible(true);
field.set(t,columnVal);
}
return t;
}
} catch (Exception e) {
new JdbcException("search错误", e);
}finally{
ConnectionUtil.close(connection,preparedStatement,resultSet);
}
return null;
}
/**
* 查询 支持事务,手动关闭资源
* @param connection
* @param class1
* @param sql
* @param objects
* @return
*/
public static <T> T searchTX(Connection connection ,Class<T> class1,String sql,Object...objects ) {
//关闭自动提交
try {
connection.setAutoCommit(false);
} catch (SQLException e1) {
new JdbcException("自动提交错误", e1);
}
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i+1, objects[i]);
}
//获取结果集
resultSet =preparedStatement.executeQuery();
//获取结构集的元数据
ResultSetMetaData data =null;
data =(ResultSetMetaData) resultSet.getMetaData();
//获取结果集列数
int columnCount = data.getColumnCount();
//处理结果集
if (resultSet.next()) {
T t =class1.newInstance();
//获取结果集中各个列的值
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnVal = resultSet.getObject(i+1);
//获取结果集中字段的名字
String colmnLabel = data.getColumnLabel(i+1);
//反射给cust的属性赋值
Field field = class1.getDeclaredField(colmnLabel);
field.setAccessible(true);
field.set(t,columnVal);
}
return t;
}
} catch (Exception e) {
new JdbcException("查询searchTX错误", e);
}finally{
ConnectionUtil.close(null, preparedStatement, resultSet);
}
return null;
}
/**
* 查询返回多个结果
* @param class1
* @param sql
* @param objects
* @return
*/
public static <T> List<T> searchList(Class<T> class1,String sql,Object...objects ) {
Connection connection =null;
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
connection =ConnectionUtil.getConnection();
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i+1, objects[i]);
}
//获取结果集
resultSet =preparedStatement.executeQuery();
//获取结构集的元数据
ResultSetMetaData data =null;
data =(ResultSetMetaData) resultSet.getMetaData();
//获取结果集列数
int columnCount = data.getColumnCount();
List<T> list = new ArrayList<>();
//处理结果集
while (resultSet.next()) {
T t =class1.newInstance();
//获取结果集中各个列的值,并将此值封装到list
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnVal = resultSet.getObject(i+1);
//获取结果集中字段的名字
String colmnLabel = data.getColumnLabel(i+1);
//反射给cust的属性赋值
Field field = class1.getDeclaredField(colmnLabel);
field.setAccessible(true);
field.set(t,columnVal);
}
list.add(t);
}
return list;
} catch (Exception e) {
new JdbcException("查询返回多个结果错误", e);
}finally{
ConnectionUtil.close(connection,preparedStatement,resultSet);
}
return null;
}
/**
* 查询结果集,支持事务,手动关闭资源
* @param connection
* @param class1
* @param sql
* @param objects
* @return
*/
public static <T> List<T> searchList(Connection connection,Class<T> class1,String sql,Object...objects ) {
//关闭自动提交
try {
connection.setAutoCommit(false);
} catch (SQLException e1) {
new JdbcException("自动提交错误", e1);
}
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i+1, objects[i]);
}
//获取结果集
resultSet =preparedStatement.executeQuery();
//获取结构集的元数据
ResultSetMetaData data =null;
data =(ResultSetMetaData) resultSet.getMetaData();
//获取结果集列数
int columnCount = data.getColumnCount();
List<T> list = new ArrayList<>();
//处理结果集
while (resultSet.next()) {
T t =class1.newInstance();
//获取结果集中各个列的值,并将此值封装到list
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnVal = resultSet.getObject(i+1);
//获取结果集中字段的名字
String colmnLabel = data.getColumnLabel(i+1);
//反射给cust的属性赋值
Field field = class1.getDeclaredField(colmnLabel);
field.setAccessible(true);
field.set(t,columnVal);
}
list.add(t);
}
return list;
} catch (Exception e) {
new JdbcException("查询结果集,支持事务错误", e);
}finally{
ConnectionUtil.close(null, preparedStatement, resultSet);
}
return null;
}
/**
* 自动开始事务
* @param conn
*/
public static void begin(Connection conn)
{
try
{
if ((conn != null) && (!conn.isClosed()))
{
conn.setAutoCommit(false);
}
}
catch (Exception ex)
{
throw new JdbcException("自动提交事务出错",ex);
}
}
/**
* 提交事务
* @param conn
*/
public static void commit(Connection conn)
{
try
{
if ((conn != null) && (!conn.isClosed()))
{
conn.commit();
}
}
catch (Exception ex)
{
throw new JdbcException("提交事务出错", ex);
}
}
/**
* 事务回滚
* @param conn
*/
public static void rollback(Connection conn)
{
try
{
if ((conn != null) && (!conn.isClosed()))
{
conn.rollback();
}
}
catch (Exception ex)
{
throw new JdbcException("事务回滚出错", ex);
}
}
}
/**
* 内部类定义异常
* @author 候东
*
*/
class JdbcException extends RuntimeException
{
public JdbcException()
{
}
public JdbcException(String message)
{
super(message);
}
public JdbcException(String message, Throwable cause)
{
super(message, cause);
}
public JdbcException(Throwable cause)
{
super(cause);
}
}