1.数据库工具类
public class DBUtil {
private static String url;
private static String username;
private static String password;
private static ThreadLocal<Connection> local;
static{
Properties properties = new Properties();
try {
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
String driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
local = new ThreadLocal<>();
}
2.获取连接对象
public static Connection getConnection() throws SQLException {
Connection connection = local.get();//获取当前线程的Connection对象
if(connection == null){
connection = DriverManager.getConnection(url,username,password);
local.set(connection);//将Connection对象添加到local中
}
return connection;
}
3.关闭资源
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
if(connection.getAutoCommit()){
connection.close();
local.set(null);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
4.事务的关闭与开启
/**
* 开启事务
*/
public static void startTransaction() throws SQLException {
Connection connection = getConnection();
connection.setAutoCommit(false);
}
/**
* 提交事务
*/
public static void commit() throws SQLException {
Connection connection = local.get();
if(connection != null){
connection.commit();
local.set(null);
}
}
public static void rollback() throws SQLException {
Connection connection = local.get();
if(connection != null){
connection.rollback();
local.set(null);
}
}
5.更新数据(添加、删除、修改)
public static int commonUpdate(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
int num = statement.executeUpdate();
return num;
}finally {
close(connection,statement,null);
}
}
6.添加数据 - 主键回填
public static int commonInsert(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
paramHandler(statement,params);
statement.executeUpdate();
resultSet = statement.getGeneratedKeys();
int primaryKey = 0;
if(resultSet.next()){
primaryKey = resultSet.getInt(1);
}
return primaryKey;
}finally {
close(connection,statement,resultSet);
}
}
7.查询数据
/**
* 查询多个数据
*/
public static <T> List<T> commonQueryList(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
List<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
list.add(t);
}
return list;
} finally {
DBUtil.close(connection,statement,resultSet);
}
}
/**
* 查询单个数据
*/
public static <T> T commonQueryObj(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
if(resultSet.next()){
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
return t;
}
} finally {
DBUtil.close(connection,statement,resultSet);
}
return null;
}
8.处理statement对象参数数据的处理器
private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
statement.setObject(i+1,params[i]);
}
}
9.获取当前类及其父类的属性对象
private static Field getField(Class<?> clazz,String name){
for(Class<?> c = clazz;c != null;c = c.getSuperclass()){
try {
Field field = c.getDeclaredField(name);
return field;
} catch (NoSuchFieldException e) {
} catch (SecurityException e) {
}
}
return null;
}
10.设置对象中的属性
private static void setField(Object obj,String name,Object value){
Field field = getField(obj.getClass(), name);
if(field != null){
field.setAccessible(true);
try {
field.set(obj, value);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}