封装JDBC，实现简单ORM框架

本文将封装JDBC的操作，实现简单的ORM框架，提供3种风格的api来给用户使用（1.原生jdbc+SqlBuilder；2.类似jpa和mp的；3.注解+接口方法）

代码仓库：malred/IFullORM

1. 原生JDBC+sql构建器

第一步：封装jdbc

这个框架的重点是将jdbc等操作隐藏到框架内部，从而让用户更轻松地实现sql操作，JDBC的部分就粗略地过一下代码

JDBCUtils

package org.malred.utils;


import java.sql.*;

public class JDBCUtils {
    static String url;
    // defaultUrl+dbName -> url
    static String defaultUrl = "jdbc:mysql://localhost:3306/";
    static String dbName;
    static String driverName;
    static String defaultDriverName = "com.mysql.cj.jdbc.Driver";
    static String user;
    static String password;

    public static void setDataSource(String url, String driverName, String user, String password) {
        JDBCUtils.url = url;
        JDBCUtils.driverName = driverName;
        JDBCUtils.user = user;
        JDBCUtils.password = password;
    }

    public static void setDataSource(String dbName, String user, String password) {
        JDBCUtils.url = defaultUrl + dbName;
        JDBCUtils.driverName = defaultDriverName;
        JDBCUtils.user = user;
        JDBCUtils.password = password;
    }

    public static void setUrl(String url) {
        JDBCUtils.url = url;
    }

    public static void setDriverName(String driverName) {
        JDBCUtils.driverName = driverName;
    }

    public static void setUser(String user) {
        JDBCUtils.user = user;
    }

    public static void setPassword(String password) {
        JDBCUtils.password = password;
    }

    public static Connection getConn() {
        try {
            // 四要素 -> 让用户传
//            String url = "jdbc:mysql://localhost:3306/test";
//            String user = "root";
//            String password = "root";
//            String driverName = "com.mysql.cj.jdbc.Driver";
            //实例化驱动
            Class.forName(driverName);
            //获取连接
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

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

    public static void close(Connection conn) {
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(PreparedStatement ps) {
        try {
            if (ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (conn != null) conn.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Operate

该部分代码来源于：JDBC（3）实现通用的增删改查方法_如何将增删改查方法抽成公共方法-CSDN博客

public class Operate {
    //通用的更新数据库的方法：insert,update,delete 语句时
    public static int update(String sql) throws SQLException {
        //1、获取连接
        Connection conn = JDBCUtils.getConn();
        //2、获取 Statement 对象，这个对象是用来给服务器传 sql 并执行 sql
        Statement st = conn.createStatement();
        //3、执行 sql
        int len = st.executeUpdate(sql);
        //4、释放资源
        JDBCUtils.close(conn, (PreparedStatement) st);
        return len;
    }

    // 通用的更新数据库的方法：insert,update,delete 语句，允许 sql 带?
    public static int update(String sql, Object... args) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        int len = update(conn, sql, args);
        JDBCUtils.close(conn);
        return len;
    }

    // 通用的更新数据库的方法：insert,update,delete 语句，允许 sql 带?
    public static int update(Connection conn, String sql, Object... args) throws SQLException {
        //2、获取 PreparedStatement 对象，这个对象是用来 sql 进行预编译
        PreparedStatement pst = conn.prepareStatement(sql);
        //3、设置 sql 中的?
        if (args != null && args.length > 0) {
            //数组的下标是从 0 开始，？的编号是 1 开始
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
        }
        //4、执行 sql
        int len = pst.executeUpdate();
        //5、释放资源
        JDBCUtils.close(pst);
        return len;
    }

    //通用的查询方法之一：查询一行，即一个对象

    /**
     * 执行查询操作的 SQL 语句，SQL 可以带参数(?)
     *
     * @param clazz Class 查询的结果需要封装的实体的 Class 类型，例如：学生 Student，商品 Goods,订单 Order
     * @param sql   String 执行查询操作的 SQL 语句
     * @param args  Object... 对应的每个?设置的值，顺序要与?对应
     * @return T 封装了查询结果的实体
     * @throws Exception
     */
    public static <T> T get(Class<T> clazz, String sql, Object... args) throws Exception {
        //1、注册驱动
        //2、获取连接
        Connection conn = JDBCUtils.getConn();
        //3、对 sql 进行预编译
        PreparedStatement pst = conn.prepareStatement(sql);
        //4、设置？
        if (args != null && args.length > 0) {
            //数组的下标是从 0 开始，？的编号是 1 开始
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
        }
        //5、查询
        ResultSet rs = pst.executeQuery();
        //获取查询的结果集的元数据信息
        ResultSetMetaData rsmd = rs.getMetaData();
        //这是查询的结果集中，一共有几列
        int count = rsmd.getColumnCount();
        T t = clazz.newInstance();//要求这个 Javabean 类型必须有无参构造
        while (rs.next()) {
            /*
             * 问题？
             * （1）sql 语句中查询了几列，每一列是什么属性
             * （2）怎么把这个值设置到 Javabean 的属性中
             */
            //循环每一行有几列
            for (int i = 0; i < count; i++) {
                //第几列的名称
                // String columnName = rsmd.getColumnName(i+1);
                //如果 sql 中没有取别名，那么就是列名，如果有别名，返回的是别名
                String fieldName = rsmd.getColumnLabel(i + 1);
                //该列的值
                // Object value = rs.getObject(columnName);
                Object value = rs.getObject(fieldName);
                //设置 obj 对象的某个属性中
                Field field = clazz.getDeclaredField(fieldName);//JavaBean 的属性名
                field.setAccessible(true);
                field.set(t, value);
            }
        }
        //5、释放资源
        JDBCUtils.close(conn, pst, rs);
        return t;
    }

    //通用的查询方法之二：查询多行，即多个对象
    //Class<T> clazz：用来创建实例对象，获取对象的属性，并设置属性值

    /**
     * 执行查询操作的 SQL 语句，SQL 可以带参数(?)
     *
     * @param clazz Class 查询的结果需要封装的实体的 Class 类型，例如：学生 Student，商品 Goods,订单 Order
     * @param sql   String 执行查询操作的 SQL 语句
     * @param args  Object... 对应的每个?设置的值，顺序要与?对应
     * @return ArrayList<T> 封装了查询结果的集合
     * @throws Exception
     */
    public static <T> ArrayList<T> getList(Class<T> clazz, String sql, Object... args) throws Exception {
        //1、注册驱动，不用了
        //2、获取连接
        Connection conn = JDBCUtils.getConn();
        //3、对 sql 进行预编译
        PreparedStatement pst = conn.prepareStatement(sql);
        //4、对？进行设置值
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
        }
        //5、执行 sql
        ResultSet rs = pst.executeQuery();
        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取结果中总列数
        int count = metaData.getColumnCount();
        //创建集合对象
        ArrayList<T> list = new ArrayList<T>();
        while (rs.next()) {//遍历的行
            //1、每一行是一个对象
            T obj = clazz.newInstance();
            //2、每一行有很多列
            //for 的作用是为 obj 对象的每一个属性设置值
            for (int i = 0; i < count; i++) {
                //(1)每一列的名称
                String fieldName = metaData.getColumnLabel(i + 1);//获取第几列的名称，如果有别名获取别名，如果没有别名获取列名
                //(2)每一列的值
                Object value = rs.getObject(i + 1);//获取第几列的值
                //(3)获取属性对象
                Field field = clazz.getDeclaredField(fieldName);
                //(4)设置可见性
                field.setAccessible(true);
                //(5)设置属性值
                field.set(obj, value);
            }
            //3、把 obj 对象放到集合中
            list.add(obj);
        }
        //6、释放资源
        JDBCUtils.close(conn, pst, rs);
        //7、返回结果
        return list;
    }

    //通用的查询方法之三：查询单个值
    //单值：select max(salary) from employee; 一行一列
    //select count(*) from t_goods; 一共几件商品
    public static Object getValue(String sql, Object... args) throws Exception {
        //2、获取连接
        Connection conn = JDBCUtils.getConn();
        //3、对 sql 进行预编译
        PreparedStatement pst = conn.prepareStatement(sql);
        //4、对？进行设置值
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
        }
        //5、执行 sql
        ResultSet rs = pst.executeQuery();
        Object value = null;
        if (rs.next()) {//一行
            value = rs.getObject(1);//一列
        }
        //6、释放资源
        JDBCUtils.close(conn, pst, rs);
        return value;
    }
}

第二步 sql构建器 SqlBuilder 类

我们将sql语句拆分为小段，然后定义单独的方法，一个一个地拼接sql语句（链式调用），最后通过一个方法获取类内部存放的sql语句

类的一些字段

public class SqlBuilder {
    String sql;
    String tbName;// 表名
    String joinTb;// 连接的表的名称

    private SqlBuilder() {
    }

    public static SqlBuilder build() {
        return new SqlBuilder();
    }

    public String sql() {
        return this.sql;
    }

    // 自定义基础sql
    public SqlBuilder base(String U_sql) {
        this.sql = U_sql;
        return this;
    }

    public SqlBuilder tbName(String tbName) {
        this.tbName = tbName;
        return this;
    }
}

select

select可以拆分为 'select x,x,x from tbName ' + 'join joinTb' + ' on tbName.x=joinTb.x ' + 'where tbName.x=?'
为？的部分由用户调用原生接口时传入参数，jdbc填充，tbName和joinTb也需要用户传入，传入后在构建器内部保存这两个值，x是用户构建sql时要传入的，代表参数（字段）的名称，会写死在sql里

    public SqlBuilder select(String tbName, String... columns) {
        this.sql = "select ";
        for (int i = 0; i < columns.length; i++) {
            if (i == columns.length - 1) {
                this.sql += columns[i] + " ";
                break;
            }
            this.sql += columns[i] + ", ";
        }
        this.sql += " from " + tbName;
        return this;
    }

    public SqlBuilder select(String[] columns) {
        this.sql = "select ";
        for (int i = 0; i < columns.length; i++) {
            if (i == columns.length - 1) {
                this.sql += columns[i] + " ";
                break;
            }
            this.sql += columns[i] + ", ";
        }
        this.sql += " from " + tbName;
        return this;
    }

    public SqlBuilder select(String tbName) {
        this.sql = "select * from " + tbName;
        return this;
    }

    public SqlBuilder select() {
        this.sql = "select * from " + tbName;
        return this;
    }

    public SqlBuilder join(SqlJoinType type, String joinTb) {
        this.joinTb = joinTb;
        sql += " " + Common.JOIN_TYPE[type.ordinal()] + " join " + joinTb;
        return this;
    }

    public SqlBuilder on(String in_column, SqlCompareIdentity identity, String out_column) {
        sql += " on " + joinTb + "." + in_column +
                Common.Compares[identity.ordinal()]
                + tbName + "." + out_column;
        return this;
    }


    public SqlBuilder count(String tbName) {
        this.sql = "select count(*) from " + tbName;
        return this;
    }

    public SqlBuilder count() {
        this.sql = "select count(*) from " + tbName;
        return this;
    }

    public SqlBuilder where(String column, SqlCompareIdentity join) {
        if (!sql.contains("where")) {
            this.sql += " where " + column + Common.Compares[join.ordinal()] + " ? ";
            return this;
        }
        this.sql += " and " + column + Common.Compares[join.ordinal()] + "? ";
        return this;
    }

update

update可以拆分为 'update tbName set x=?,x=?' + 'where x=?'

    public SqlBuilder update(String tbName, String... columns) {
        this.sql = "update " + tbName + " set ";
        for (int i = 0; i < columns.length; i++) {
            if (i == columns.length - 1) {
                this.sql += columns[i] + "=? ";
                break;
            }
            this.sql += columns[i] + "=?,";
        }
        return this;
    }

    public SqlBuilder update(String[] columns) {
        this.sql = "update " + tbName + " set ";
        for (int i = 0; i < columns.length; i++) {
            if (i == columns.length - 1) {
                this.sql += columns[i] + "=? ";
                break;
            }
            this.sql += columns[i] + "=?,";
        }
        return this;
    }

delete

delete => 'delete from tbName' + 'where x= ?'

    public SqlBuilder delete(String tbName) {
        sql = "delete from " + tbName;
        return this;
    }

    public SqlBuilder delete() {
        sql = "delete from " + tbName;
        return this;
    }

insert

insert => 'insert into tbName(x,x,x) values (?,?,?), (?,?,?)'

    public SqlBuilder insert(String tbName, String... params) {
        sql = "insert into " + tbName;
        sql += "(";
        for (int i = 0; i < params.length; i++) {
            if (i == params.length - 1) {
                sql += params[i] + ") ";
                break;
            }
            sql += params[i] + ",";
        }
        sql += "values (";
        for (int i = 0; i < params.length; i++) {
            if (i == params.length - 1) {
                sql += "?)";
                break;
            }
            sql += "?,";
        }
        return this;
    }

    public SqlBuilder insert(String[] params) {
        sql = "insert into " + tbName;
        sql += "(";
        for (int i = 0; i < params.length; i++) {
            if (i == params.length - 1) {
                sql += params[i] + ") ";
                break;
            }
            sql += params[i] + ",";
        }
        sql += "values (";
        for (int i = 0; i < params.length; i++) {
            if (i == params.length - 1) {
                sql += "?)";
                break;
            }
            sql += "?,";
        }
        return this;
    }

测试sqlBuilder

public class t {
    @Before
    public void before() {
        // 设置数据库属性
        JDBCUtils
                .setDataSource("jdbc:mysql://localhost:3306/test",
                        "com.mysql.cj.jdbc.Driver", "root", "root");
    }

    // sqlbuilder+jdbc封装
    @Test
    public void testSelectBuild() {
        String sql = SqlBuilder.build()
                .select("tb_user")
                .where("id", SqlCompareIdentity.NE)
                .sql();
        System.out.println(sql);

        String sql_cols = SqlBuilder.build()
                .select("tb_user", "username", "gender", "addr")
                .where("id", SqlCompareIdentity.NE)
                .sql();
        System.out.println(sql_cols);

        String sql_cols_option2 = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).where("password", SqlCompareIdentity.NE).sql();
        System.out.println(sql_cols_option2);

        String sql_count = SqlBuilder.build().count("tb_user").where("id", SqlCompareIdentity.GT).sql();
        System.out.println(sql_count);
    }

    @Test
    public void testSelectBuildTbName() {
        // 直接设置build的tbName,然后使用不需要tbName的方法来构建
        String sql1 = SqlBuilder.build().tbName("tb_user").select().where("id", SqlCompareIdentity.NE).sql();
        System.out.println(sql1);

        String sql_cols1 = SqlBuilder.build().tbName("tb_user").select(new String[]{"username", "gender", "addr"}).where("id", SqlCompareIdentity.NE).sql();
        System.out.println(sql_cols1);

        String sql_cols_option21 = SqlBuilder.build().tbName("tb_user").select(new String[]{"username", "gender", "addr"}).where("id", SqlCompareIdentity.NE).where("password", SqlCompareIdentity.NE).sql();
        System.out.println(sql_cols_option21);

        String sql_count1 = SqlBuilder.build().tbName("tb_user").count().where("id", SqlCompareIdentity.GT).sql();
        System.out.println(sql_count1);

        String sql_join = SqlBuilder.build().tbName("tb_user").select().join(SqlJoinType.INNER, "tb_product").on("id", SqlCompareIdentity.EQ, "id").where("tb_user.id", SqlCompareIdentity.EQ).sql();
        System.out.println(sql_join);
    }

    @Test
    public void testUpdateBuild() {
        String sql = SqlBuilder.build()
                .update("tb_user", "password", "username")
                .where("id", SqlCompareIdentity.EQ)
                .sql();
        System.out.println(sql);

        String sql_no_tbname = SqlBuilder.build().tbName("tb_user").update(new String[]{"password", "username"}).where("id", SqlCompareIdentity.EQ).sql();
        System.out.println(sql_no_tbname);
    }

    @Test
    public void testInsertBuild() {
        String sql = SqlBuilder.build()
                .insert("tb_user", "username", "password", "addr", "gender")
                .sql();
        System.out.println(sql);
    }

    @Test
    public void testInsertBuildTbName() {
        String sql = SqlBuilder.build()
                .tbName("tb_user")
                .insert(new String[]{"username", "password", "addr", "gender"}).sql();
        System.out.println(sql);
    }

    @Test
    public void testDeleteBuild() {
        String sql_tb = SqlBuilder.build()
                .tbName("tb_user").delete()
                .where("id", SqlCompareIdentity.EQ)
                .sql();
        System.out.println(sql_tb);

        String sql = SqlBuilder.build().delete("tb_user")
                .where("id", SqlCompareIdentity.EQ).sql();
        System.out.println(sql);
    }
}

第三步使用原生接口+sql构建器执行crud

    @Before
    public void before() {
        // 设置数据库属性
        JDBCUtils
                .setDataSource("jdbc:mysql://localhost:3306/test",
                        "com.mysql.cj.jdbc.Driver", "root", "root");
    }
    
    @Test
    public void testSelectMulti() throws Exception {
        ArrayList<TbUser> list;

        String sql = SqlBuilder.build().select("tb_user").where("id", SqlCompareIdentity.NE).sql();
        list = Operate.getList(TbUser.class, sql, 1);
        System.out.println(list);

        String sql_cols = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).sql();
        list = Operate.getList(TbUser.class, sql_cols, 1);
        System.out.println(list);

        String sql_cols_option2 = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).where("gender", SqlCompareIdentity.NE).sql();
        list = Operate.getList(TbUser.class, sql_cols_option2, 1, "男");
        System.out.println(list);

        String sql_count = SqlBuilder.build().count("tb_user").where("id", SqlCompareIdentity.GT).sql();
        Long cnt = (Long) Operate.getValue(sql_count, 3);
        System.out.println(cnt);
    }

    @Test
    public void testUpdate() throws SQLException {
        int cnt = 0;
        String sql = SqlBuilder.build()
                .update("tb_user", "password", "username")
                .where("id", SqlCompareIdentity.EQ)
                .sql();
        cnt = Operate.update(sql, "O", "t50", "13");
        System.out.println("影响了" + cnt + "条数据");

        String sql_no_tbname = SqlBuilder.build()
                .tbName("tb_user")
                .update(new String[]{"password", "username"})
                .where("id", SqlCompareIdentity.EQ)
                .sql();
        cnt = Operate.update(sql_no_tbname, "Obu", "t50123", "13");
        System.out.println("影响了" + cnt + "条数据");
    }
    
    @Test
    public void testInsert() throws SQLException {
        int count = 0;
        String sql = SqlBuilder.build()
                .insert("tb_user", "username", "password", "addr", "gender")
                .sql();
        count = Operate.update(sql, "name", "pass", "cn", "男");
        System.out.println("影响了" + count + "条数据");
    }

    @Test
    public void testDelete() throws SQLException {
        int cnt = 0;
        String sql_tb = SqlBuilder.build()
                .tbName("tb_user")
                .delete()
                .where("id", SqlCompareIdentity.EQ).sql();
        cnt = Operate.update(sql_tb, 219);
        System.out.println("影响了" + cnt + "条数据");
    }

这里的SqlCompareIdentity是为了方便管理，把一些比较关键字放到枚举里，然后在commons类里定义数组，根据enum的索引从数组里拿比较运算符来拼接

SqlCompareIdentity

public enum SqlCompareIdentity {
    GT,// >
    GE,// >=
    LT, // <
    LE,// <=
    EQ, // =
    NE, // !=
}

Common

public class Common {
    public static final String[] Compares = new String[]{
            ">", ">=", "<", "<=", "=", "!=", "like", "in"
    };
    // baseCRUD方法的名称
    public static final String[] JOIN_TYPE = new String[]{
            "left","right","full","inner","left outer","right outer"
    };
}

SqlJoinType

public enum SqlJoinType {
    // "left","right","full","inner","left outer","right outer"
    LEFT,
    RIGHT,
    FULL,
    INNER,
    LO,
    RO
}

2. 创建BaseCRUDRepository，实现动态代理

思路：BaseCRUDRepository<T>里定义基本curd方法，select方法的返回值由传入的泛型决定，因为方法名是死的，所以可以通过方法名来判断是哪个方法，我们可以在动态代理里根据不同方法提供不同的实现，用户只需要创建接口继承BaseCRUDRepository，就可以使用这些方法
Repository是一个注解，主要是指定数据库表名用的

package org.malred.annotations;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Repository {
    String value(); // 表名
}

Operate
这里的type1是执行jdbc方法需要的实体类类型，本来想通过泛型拿到，但是好像java不能反射获取类的泛型

    // 定义在Operate类里
    public static <T> T getMapper(Class<?> mapperClass, Class<?> type1) {
        // 使用JDK动态代理为Dao接口生成代理对象,并返回
        Object proxyInstance = Proxy.newProxyInstance(Operate.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                // 拿到表名
                Repository annotation = mapperClass.getAnnotation(Repository.class);
                String tbName = annotation.value();
                // 拼装sql
//                        String sql = (String) Common.DefaultCRUDSql.get(method.getName());
//                        System.out.println(sql);
                String sql = "";
                // 默认CRUD接口的代理方法
                switch (method.getName()) {
                    case "findAll": {
                        sql = SqlBuilder.build().tbName(tbName).select().sql();
                        System.out.println("执行findAll方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return Operate.getList(type1, sql);
                    }
                    case "findById": {
                        sql = SqlBuilder.build().tbName(tbName).select().where("id", SqlCompareIdentity.EQ).sql();
                        System.out.println("执行findById方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return Operate.get(type1, sql, args);
                    }
                    case "update": {
                        ParseClazz parseClazz = parseObjectArgs(args);

                        String[] paramNames = new String[parseClazz.params.keySet().size()];
                        for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
                            paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
                        }
                        sql = SqlBuilder.build().update(tbName, paramNames).where(parseClazz.idName, SqlCompareIdentity.EQ).sql();

                        System.out.println("执行update方法");
                        System.out.println("当前执行的sql语句: " + sql);

                        String[] paramVals = new String[parseClazz.params.values().size() + 1];
                        for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
                            paramVals[i] = parseClazz.params.values().toArray()[i].toString();
//                                    System.out.println(paramVals[i]);
                        }
                        paramVals[paramVals.length - 1] = parseClazz.idVal.toString();
                        return Operate.update(sql, paramVals);
                    }
                    case "insert": {
                        ParseClazz parseClazz = parseObjectArgs(args);
                        String[] paramNames = new String[parseClazz.params.keySet().size()];
                        for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
                            paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
                        }

                        sql = SqlBuilder.build().tbName(tbName).insert(paramNames).sql();
                        System.out.println("执行insert方法");
                        System.out.println("当前执行的sql语句: " + sql);

                        String[] paramVals = new String[parseClazz.params.values().size()];
                        for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
                            paramVals[i] = parseClazz.params.values().toArray()[i].toString();
//                                    System.out.println(paramVals[i]);
                        }
                        return update(sql, paramVals);
                    }
                    case "delete": {
                        sql = SqlBuilder.build().tbName(tbName).delete().where("id", SqlCompareIdentity.EQ).sql();
                        System.out.println("执行delete方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return update(sql, args[0]);
                    }
                }
                return null;
            }
        });
        return (T) proxyInstance;
    }

测试

    // 基本CRUD接口的代理实现测试
    @Test
    public void testSelectProxy() {
        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
//        UserRepository mapper = Operate.getMapper(UserRepository.class);
        List<TbUser> all = mapper.findAll();
        System.out.println(all);

        TbUser one = mapper.findById(1);
        System.out.println(one);
    }

    @Test
    public void testUpdateProxy() {
        int cnt = 0;
//        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
        UserRepository mapper = Operate.getMapper(UserRepository.class);

        cnt = mapper.update(new TbUser(212, "ema1n", "s1sap", "女", null));
        System.out.println("影响了" + cnt + "条数据");

//        cnt = mapper.update(new TbUser(211, "name", "pass", null, null));
//        System.out.println("影响了" + cnt + "条数据");
    }

    @Test
    public void testInsertProxy() {
        int cnt = 0;
//        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
        UserRepository mapper = Operate.getMapper(UserRepository.class);

        cnt = mapper.insert(new TbUser(0, "eman", "ssap", null, null));
        System.out.println("影响了" + cnt + "条数据");
    }

    @Test
    public void testDeleteProxy() {
        int cnt = 0;
        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);

        cnt = mapper.delete(223);
        System.out.println("影响了" + cnt + "条数据");
    }

3. 注解+接口方法

思路，依然是走动态代理，只是sql的获取不再是框架构建，而是用户在注解中定义，框架只是把值填充，这里我们需要注意，如果是第二种方法和注解的混用，会有一个类型问题
这个问题就是，如果传入了type1，那么所有被代理的方法如果都用type1作为调用jdbc时传入的实体类型，就无法使用其他类型（无法查询并封装到其他类型），而我们的解决方法是，注解的方法就根据注解方法的返回值来传递实体类型，base方法就用type1

    public static <T> T getMapper(Class<?> mapperClass, Class<?> type1) {
        // 使用JDK动态代理为Dao接口生成代理对象,并返回
        Object proxyInstance = Proxy.newProxyInstance(Operate.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                // 用户定义的方法的返回类型
                Class<?> type = null;
                //获取方法的返回值类型
                Type genericReturnType = method.getGenericReturnType();
                if (!method.getName().equals("findAll") && !method.getName().equals("findById")) {
                    if (genericReturnType instanceof ParameterizedType) {
                        Type[] actualTypeArguments = ((ParameterizedType) genericReturnType).getActualTypeArguments();
                        for (Type parameterType : actualTypeArguments) {
                            System.out.println(parameterType);
                            type = (Class<?>) parameterType;
                        }
                    } else {
                        type = (Class<?>) genericReturnType;
                    }
                }
                // 拿到表名
                Repository annotation = mapperClass.getAnnotation(Repository.class);
                String tbName = annotation.value();
                // 拼装sql
//                        String sql = (String) Common.DefaultCRUDSql.get(method.getName());
//                        System.out.println(sql);
                String sql = "";
                // 默认CRUD接口的代理方法
                switch (method.getName()) {
                    case "findAll": {
                        sql = SqlBuilder.build().tbName(tbName).select().sql();
                        System.out.println("执行findAll方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return Operate.getList(type1, sql);
                    }
                    case "findById": {
                        sql = SqlBuilder.build().tbName(tbName).select().where("id", SqlCompareIdentity.EQ).sql();
                        System.out.println("执行findById方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return Operate.get(type1, sql, args);
                    }
                    case "update": {
                        ParseClazz parseClazz = parseObjectArgs(args);

                        String[] paramNames = new String[parseClazz.params.keySet().size()];
                        for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
                            paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
                        }
                        sql = SqlBuilder.build().update(tbName, paramNames).where(parseClazz.idName, SqlCompareIdentity.EQ).sql();

                        System.out.println("执行update方法");
                        System.out.println("当前执行的sql语句: " + sql);

                        String[] paramVals = new String[parseClazz.params.values().size() + 1];
                        for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
                            paramVals[i] = parseClazz.params.values().toArray()[i].toString();
//                                    System.out.println(paramVals[i]);
                        }
                        paramVals[paramVals.length - 1] = parseClazz.idVal.toString();
                        return Operate.update(sql, paramVals);
                    }
                    case "insert": {
                        ParseClazz parseClazz = parseObjectArgs(args);
                        String[] paramNames = new String[parseClazz.params.keySet().size()];
                        for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
                            paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
                        }

                        sql = SqlBuilder.build().tbName(tbName).insert(paramNames).sql();
                        System.out.println("执行insert方法");
                        System.out.println("当前执行的sql语句: " + sql);

                        String[] paramVals = new String[parseClazz.params.values().size()];
                        for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
                            paramVals[i] = parseClazz.params.values().toArray()[i].toString();
//                                    System.out.println(paramVals[i]);
                        }
                        return update(sql, paramVals);
                    }
                    case "delete": {
                        sql = SqlBuilder.build().tbName(tbName).delete().where("id", SqlCompareIdentity.EQ).sql();
                        System.out.println("执行delete方法");
                        System.out.println("当前执行的sql语句: " + sql);
                        return update(sql, args[0]);
                    }
                }
                // 如果都不是上面的,就是用户自己定义的
                if (method.isAnnotationPresent(Select.class)) {
                    Select selectAnno = method.getAnnotation(Select.class);
                    sql = selectAnno.value();
                    // 判断是查询单个还是多个(返回值类型是List之类的吗)
                    // 这里只是简单判断一下
//                            Type genericReturnType = method.getGenericReturnType();
                    // 判断是否进行了泛型类型参数化(是否有泛型)
                    if (genericReturnType instanceof ParameterizedType) {
//                            if (x instanceof Collection< ? >){
//                            }
//                            if (x instanceof Map<?,?>){
//                            }
                        return Operate.getList(type, sql, args);
                    }
                    return Operate.get(type, sql, args);
                }
                if (method.isAnnotationPresent(Update.class)) {
                    Update anno = method.getAnnotation(Update.class);
                    sql = anno.value();
                    return update(sql, args);
                }
                if (method.isAnnotationPresent(Delete.class)) {
                    Delete anno = method.getAnnotation(Delete.class);
                    sql = anno.value();
                    return update(sql, args);
                }
                if (method.isAnnotationPresent(Insert.class)) {
                    Insert anno = method.getAnnotation(Insert.class);
                    sql = anno.value();
                    return update(sql, args);
                }
                // 返回值
                return null;
            }
        });
        return (T) proxyInstance;
    }

测试

    // 用户定义的注解的代理实现测试
    @Test
    public void testSelectAnnotation() {
        UserRepository mapper = Operate.getMapper(UserRepository.class);
//        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);

        TbUser user = mapper.selectOneByUsername("张三");
        System.out.println(user);

        List<TbUser> tbUsers = mapper.selectOneByNEPassword("456");
        for (TbUser tbUser : tbUsers) {
            System.out.println(tbUser);
        }

        // 和因为在代理时写死传入的返回值类型,这里只能重新创建代理
        // 根据接口方法的返回值获取泛型类型,动态判断返回类型
//        UserRepository puMapper =
//                Operate.getMapper(UserRepository.class, ProductAndUser.class);
//        UserRepository puMapper = Operate.getMapper(UserRepository.class);
        List<ProductAndUser> userAndProductJoin = mapper.findUserAndProductJoin(7);
        System.out.println(userAndProductJoin);
    }

    @Test
    public void testUpdateAnnotation() {
//        UserRepository mapper =
//                Operate.getMapper(UserRepository.class, TbUser.class);
        UserRepository mapper = Operate.getMapper(UserRepository.class);
        int cnt = mapper.uptUser("哇哈哈1", 14);
        System.out.println("影响了" + cnt + "条数据");
    }

    @Test
    public void testDeleteAnnotation() {
//        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
        UserRepository mapper = Operate.getMapper(UserRepository.class);
        int cnt = mapper.delUser(218, "tpass");
        System.out.println("影响了" + cnt + "条数据");
    }

    @Test
    public void testInsertAnnotation() {
//        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
        UserRepository mapper = Operate.getMapper(UserRepository.class);
        int cnt = mapper.addUser("tttt", "tpass", "tttt2", "tpass2");
        System.out.println("影响了" + cnt + "条数据");
    }

下一步：接口代理的selectBy实体类属性名方法

思路：实体类加上注解，指定表名，Operator的static里扫描注解并将key-表名：val-扫到的类注入到一个map，代理方法里通过Repository里的表名和map里的匹配，拿到实体类，拼接字符串selectByxxx，放入list，当执行方法，进入代理，就判断是否在list中包含，然后实现代理逻辑

封装JDBC，实现简单ORM框架

1. 原生JDBC+sql构建器

第一步：封装jdbc

第二步 sql构建器 SqlBuilder 类

类的一些字段

select

update

delete

insert

测试sqlBuilder

第三步使用原生接口+sql构建器执行crud

这里的SqlCompareIdentity是为了方便管理，把一些比较关键字放到枚举里，然后在commons类里定义数组，根据enum的索引从数组里拿比较运算符来拼接

2. 创建BaseCRUDRepository，实现动态代理

测试

3. 注解+接口方法

测试

下一步：接口代理的selectBy实体类属性名方法

相关文章

基于微信小程序的社区团购小程序设计与实现(源码+lw+部署文档+讲解等)

LeetCode算法二叉树—116. 填充每个节点的下一个右侧节点指针

内网穿透--cpolar

秦时明月卡牌版攻略，最强的推图阵容推荐

Unity Urp无线延申的网格效果

MATLAB 多信号显示方案

深信服云桌面用户忘记密码后的处理

docker安装常用的服务脚本汇总

封装JDBC，实现简单ORM框架

1. 原生JDBC+sql构建器

第一步： 封装jdbc

第二步 sql构建器 SqlBuilder 类

类的一些字段

select

update

delete

insert

测试sqlBuilder

第三步 使用原生接口+sql构建器执行crud

这里的SqlCompareIdentity是为了方便管理，把一些比较关键字放到枚举里，然后在commons类里定义数组，根据enum的索引从数组里拿比较运算符来拼接

2. 创建BaseCRUDRepository，实现动态代理

测试

3. 注解+接口方法

测试

下一步： 接口代理的selectBy实体类属性名方法

相关文章

第一步：封装jdbc

第三步使用原生接口+sql构建器执行crud

下一步：接口代理的selectBy实体类属性名方法