封装JDBCUtil

发布于 2021-08-19  62 次阅读


JDBCUtils如下:

package com.apesblog.jdbcutil1.util;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import java.util.Set;

import com.apesblog.jdbcutil1.bean.Order;

public class JDBCUtils {

    private static Connection getConnection() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        // 2.加载驱动
        Class.forName(driverClass);

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    private static void closeResource(Connection conn, Statement ps) {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void closeResource(Connection conn, Statement ps, ResultSet rs) {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void update(String sql, Object... args) {// sql中占位符的个数与可变形参的长度相同!
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            // 2.预编译sql语句,返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);// 小心参数声明错误!!
            }
            // 4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.资源的关闭
            JDBCUtils.closeResource(conn, ps);

        }
    }

    public static <T> List<T> query(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            // 获取结果集的元数据 :ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            // 创建集合对象
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()) {
                T t = clazz.newInstance();
                // 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object columValue = rs.getObject(i + 1);

                    // 获取每个列的列名
                    // String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    // 给t对象指定的columnName属性,赋值为columValue:通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);

        }

        return null;
    }

    public static void updateDomain(String sql, Object object) {
        Connection conn = null;
        PreparedStatement ps = null;
        Class<? extends Object> clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        HashMap<Integer, String> hashMap = new HashMap<Integer, String>();
        for (int i = 0; i < fields.length; i++) {
            String lowerCase = fields[i].getName().toLowerCase();
            int indexOf = sql.indexOf(lowerCase);
            if (indexOf == -1)
                continue;
            hashMap.put(indexOf, fields[i].getName());
        }

        try {
            // 1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            // 2.预编译sql语句,返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            // 3.填充占位符
            int size = hashMap.size();
            for (int i = 0; i < size; i++) {// --for() begin
                Integer minKey = (Integer) getMinKey(hashMap);
                String string = hashMap.get(minKey);
                Field field = clazz.getDeclaredField(string);
                field.setAccessible(true);
                Object value = field.get(object);
                if (value == null)
                    continue;
                ps.setObject(i + 1, value);// 小心参数声明错误!!
                hashMap.remove(minKey);
            }

            // 4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.资源的关闭
            JDBCUtils.closeResource(conn, ps);

        }
    }

    public static <T> List<T> queryDomain(Class<T> clazz, String sql, Object object) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Class<? extends Object> objclazz = object.getClass();
        Field[] fields = objclazz.getDeclaredFields();
        HashMap<Integer, String> hashMap = new HashMap<Integer, String>();
        for (int i = 0; i < fields.length; i++) {
            String lowerCase = fields[i].getName().toLowerCase();
            int indexOf;
            if (sql.indexOf("from") == -1) {
                indexOf = sql.substring(sql.indexOf("FROM")).indexOf(lowerCase);
            } else {
                indexOf = sql.substring(sql.indexOf("from")).indexOf(lowerCase);
            }
            if (indexOf == -1)
                continue;
            hashMap.put(indexOf, fields[i].getName());
        }
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);
            int size = hashMap.size();
            for (int i = 0; i < size; i++) {// --for() begin
                Integer minKey = (Integer) getMinKey(hashMap);
                String string = hashMap.get(minKey);
                Field field = clazz.getDeclaredField(string);
                field.setAccessible(true);
                Object value = field.get(object);
                if (value == null)
                    continue;
                ps.setObject(i + 1, value);// 小心参数声明错误!!
                hashMap.remove(minKey);
            }

            rs = ps.executeQuery();
            // 获取结果集的元数据 :ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            // 创建集合对象
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()) {
                T t = clazz.newInstance();
                // 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object columValue = rs.getObject(i + 1);

                    // 获取每个列的列名
                    // String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    // 给t对象指定的columnName属性,赋值为columValue:通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);

        }

        return null;
    }

    private static Object getMinKey(HashMap<Integer, String> hashMap) {
        if (hashMap == null)
            return null;
        Set<Integer> set = hashMap.keySet();
        Object[] obj = set.toArray();
        Arrays.sort(obj);
        return obj[0];

    }
}

测试类如下:

package com.apesblog.jdbcutil1.util;

import java.util.List;

import org.junit.Test;
import org.junit.internal.requests.OrderingRequest;

import com.apesblog.jdbcutil1.bean.Customer;
import com.apesblog.jdbcutil1.bean.Order;

public class Test1 {
    @Test
    public void testUpdateDoamin() {
        String sql = "delete from customers where id = ?";
        Customer customer = new Customer();
        customer.setId(4);
        JDBCUtils.updateDomain(sql, customer);

        sql = "update `order` set ordername = ? where orderid = ?";
        Order order = new Order();
        order.setOrderName("zzzz");
        order.setOrderId(1);
        JDBCUtils.updateDomain(sql, order);

    }

    @Test
    public void testQueryDomain() {

        String sql = "select id,name,email from customers where id < ?";
        Customer customer = new Customer();
        customer.setId(12);
        List<Customer> list = JDBCUtils.queryDomain(Customer.class, sql, customer);
        list.forEach(System.out::println);

        String sql1 = "select orderid orderId,ordername orderName from `order`";
        List<Order> orderList = JDBCUtils.queryDomain(Order.class, sql1, new Order());
        orderList.forEach(System.out::println);
    }

    @Test
    public void testUpdate() {
//      String sql = "delete from customers where id = ?";
//      update(sql,3);

        String sql = "update `order` set ordername = ? where orderid = ?";
        JDBCUtils.update(sql, "zys", "2");

    }

    @Test
    public void testQuery() {

        String sql = "select id,name,email from customers where id < ?";
        List<Customer> list = JDBCUtils.query(Customer.class, sql, 12);
        list.forEach(System.out::println);

        String sql1 = "select orderid orderId,ordername orderName from `order`";
        List<Order> orderList = JDBCUtils.query(Order.class, sql1);
        orderList.forEach(System.out::println);
    }

}

总结:

1.0

增删改没有结果集,封装成一个方法,但是传的是具体的值,不是domain,公司框架传的是domain,那是如何获取值得呢?

查询操作因为需要返回不同的domainList,所以需要传入domain.class

2.0

新增updateDomain通过反射获取属性

3.0

新增queryDomain,完成告一段落