自定义JdbcTemplet,自定义JDBC框架,实现基本功能.包括连接池

本文运用到的技术和思想有:反射, 动态代理, 策略模式等

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", "鸣人");
    }
}