自定义JDBCTemplate
本文运用反射, 动态代理, 策略模式等方式实现一个自定义的 jdbcTemplate ，加深对其他的理解。
package com.zuoyueer.util;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;
/**
* @author Zuoyueer
* Date: 2019/11/4
* Time: 16:39
* @projectName JDBC
* @description: 获取数据库连接, 释放资源, 执行sql的update和query操作
*/
public class JdbcUtil {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
//读取配置文件中数据库的连接数据
InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//加载数据库驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @return 获取连接对象
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 增删改操作
*
* @param sql 要执行的sql语句
* @param objects 参数
* @throws SQLException
*/
public static void update(String sql, Object... objects) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
preparedStatement.executeUpdate();
} finally {
release(connection, preparedStatement, null);
}
}
/**
* @param sql 执行的sql语句
* @param resultSetHandler 对查询的结果集的处理方案
* @param objects sql的参数
* @return 返回查询结果
* @throws SQLException
*/
public static <T> List<T> query(String sql, ResultSetHandler<T> resultSetHandler, Object... objects) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
resultSet = preparedStatement.executeQuery();
/*
* 查询的返回结果不知道如何处理,使用策略模式
* 对用户提供一个结果集处理的接口,用户实现该接口,
* 那么query方法就知道如何处理了
* */
return resultSetHandler.handler(resultSet);
} finally {
release(connection, preparedStatement, resultSet);
}
}
/**
* 查询一条记录,封装成对象
*
* @param sql
* @param resultSetHandler
* @param objects
* @param <T>
* @return 返回查询的对象
* @throws SQLException
*/
public static <T> T queryForObject(String sql, ResultSetHandler<T> resultSetHandler, Object... objects) throws SQLException {
List<T> results = query(sql, resultSetHandler, objects);
return results != null ? results.iterator().next() : null;
}
/**
* 释放资源
*
* @param connection
* @param statement
* @param resultSet
*/
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.zuoyueer.util;
import java.sql.ResultSet;
import java.util.List;
/**
* @author Zuoyueer
* Date: 2019/11/4
* Time: 17:17
* @projectName JDBC
* @description: 策略模式的接口
*/
public interface ResultSetHandler<T> {
/**
* 结果集的处理办法
*
* @param resultSet 查询到的结果集
* @return 查询结果
*/
List<T> handler(ResultSet resultSet);
}
package com.zuoyueer.util;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/**
* @author Zuoyueer
* Date: 2019/11/4
* Time: 18:22
* @projectName JDBC
* @description: TODO
*/
public class BeanListHandler<T> implements ResultSetHandler<T> {
private Class<T> clazz;
public BeanListHandler(Class<T> clazz) {
this.clazz = clazz;
}
/**
* 结果集的处理办法
*
* @param resultSet 查询到的结果集
* @return 查询结果
*/
@Override
public List<T> handler(ResultSet resultSet) {
try {
T bean;
List<T> list = new ArrayList<>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
bean = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
String ColumnName = metaData.getColumnName(i + 1);
Object columnData = resultSet.getObject(ColumnName);
Field field = bean.getClass().getDeclaredField(ColumnName);
field.setAccessible(true);
field.set(bean, columnData);
}
list.add(bean);
}
return list.size() > 0 ? list : null;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
}
package com.zuoyueer.util;
import com.zuoyueer.bean.User;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;
/**
* @author Zuoyueer
* Date: 2019/11/4
* Time: 19:33
* @projectName JDBC
* @description: TODO
*/
public class Client {
@Test
public void method6() {
String sql = "select * from user";
List<User> list = null;
try {
list = JdbcUtil.query(sql, new BeanListHandler<>(User.class));
} catch (SQLException e) {
e.printStackTrace();
}
list.forEach(System.out::println);
}
/**
* 查询结果封装到javaBean中
* @throws SQLException
*/
@Test
public void method5() throws SQLException {
String sql = "select * from user where nickname=?";
User user = JdbcUtil.queryForObject(sql, new BeanListHandler<>(User.class), "佐助");
System.out.println(user);
}
/**
* 修改
*
* @throws SQLException
*/
@Test
public void method3() throws SQLException {
String sql = "update user set password=? where nickname=?";
JdbcUtil.update(sql, "111111", "佐助");
}
/**
* 删除
*
* @throws SQLException
*/
@Test
public void method2() throws SQLException {
String sql = "delete from user where username=?";
JdbcUtil.update(sql, "mr");
}
/**
* 增加
*
* @throws SQLException
*/
@Test
public void method() throws SQLException {
String sql = "insert into user values(null,?,?,?)";
JdbcUtil.update(sql, "mr", "33333", "鸣人");
}
}
