• 封装JDBC，实现简单ORM框架

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

    代码仓库：malred/IFullORM

    1. 原生JDBC+sql构建器

    第一步： 封装jdbc

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

    JDBCUtils 
    1. package org.malred.utils;
    2.  
    3.  
    4. import java.sql.*;
    5.  
    6. public class JDBCUtils {
    7.     static String url;
    8.     // defaultUrl+dbName -> url
    9.     static String defaultUrl = "jdbc:mysql://localhost:3306/";
    10.     static String dbName;
    11.     static String driverName;
    12.     static String defaultDriverName = "com.mysql.cj.jdbc.Driver";
    13.     static String user;
    14.     static String password;
    15.  
    16.     public static void setDataSource(String url, String driverName, String user, String password) {
    17.         JDBCUtils.url = url;
    18.         JDBCUtils.driverName = driverName;
    19.         JDBCUtils.user = user;
    20.         JDBCUtils.password = password;
    21.     }
    22.  
    23.     public static void setDataSource(String dbName, String user, String password) {
    24.         JDBCUtils.url = defaultUrl + dbName;
    25.         JDBCUtils.driverName = defaultDriverName;
    26.         JDBCUtils.user = user;
    27.         JDBCUtils.password = password;
    28.     }
    29.  
    30.     public static void setUrl(String url) {
    31.         JDBCUtils.url = url;
    32.     }
    33.  
    34.     public static void setDriverName(String driverName) {
    35.         JDBCUtils.driverName = driverName;
    36.     }
    37.  
    38.     public static void setUser(String user) {
    39.         JDBCUtils.user = user;
    40.     }
    41.  
    42.     public static void setPassword(String password) {
    43.         JDBCUtils.password = password;
    44.     }
    45.  
    46.     public static Connection getConn() {
    47.         try {
    48.             // 四要素 -> 让用户传
    49. //            String url = "jdbc:mysql://localhost:3306/test";
    50. //            String user = "root";
    51. //            String password = "root";
    52. //            String driverName = "com.mysql.cj.jdbc.Driver";
    53.             //实例化驱动
    54.             Class.forName(driverName);
    55.             //获取连接
    56.             Connection conn = DriverManager.getConnection(url, user, password);
    57.             return conn;
    58.         } catch (Exception e) {
    59.             e.printStackTrace();
    60.             return null;
    61.         }
    62.     }
    63.  
    64.     public static void close(Connection conn, PreparedStatement ps) {
    65.         try {
    66.             if (conn != null) conn.close();
    67.             if (ps != null) ps.close();
    68.         } catch (SQLException e) {
    69.             e.printStackTrace();
    70.         }
    71.     }
    72.  
    73.     public static void close(Connection conn) {
    74.         try {
    75.             if (conn != null) conn.close();
    76.         } catch (SQLException e) {
    77.             e.printStackTrace();
    78.         }
    79.     }
    80.  
    81.     public static void close(PreparedStatement ps) {
    82.         try {
    83.             if (ps != null) ps.close();
    84.         } catch (SQLException e) {
    85.             e.printStackTrace();
    86.         }
    87.     }
    88.  
    89.     public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
    90.         try {
    91.             if (conn != null) conn.close();
    92.             if (ps != null) ps.close();
    93.             if (rs != null) rs.close();
    94.         } catch (Exception e) {
    95.             e.printStackTrace();
    96.         }
    97.     }
    98. }
    Operate

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

    1. public class Operate {
    2.     //通用的更新数据库的方法：insert,update,delete 语句时
    3.     public static int update(String sql) throws SQLException {
    4.         //1、获取连接
    5.         Connection conn = JDBCUtils.getConn();
    6.         //2、获取 Statement 对象，这个对象是用来给服务器传 sql 并执行 sql
    7.         Statement st = conn.createStatement();
    8.         //3、执行 sql
    9.         int len = st.executeUpdate(sql);
    10.         //4、释放资源
    11.         JDBCUtils.close(conn, (PreparedStatement) st);
    12.         return len;
    13.     }
    14.  
    15.     // 通用的更新数据库的方法：insert,update,delete 语句，允许 sql 带?
    16.     public static int update(String sql, Object... args) throws SQLException {
    17.         Connection conn = JDBCUtils.getConn();
    18.         int len = update(conn, sql, args);
    19.         JDBCUtils.close(conn);
    20.         return len;
    21.     }
    22.  
    23.     // 通用的更新数据库的方法：insert,update,delete 语句，允许 sql 带?
    24.     public static int update(Connection conn, String sql, Object... args) throws SQLException {
    25.         //2、获取 PreparedStatement 对象，这个对象是用来 sql 进行预编译
    26.         PreparedStatement pst = conn.prepareStatement(sql);
    27.         //3、设置 sql 中的?
    28.         if (args != null && args.length > 0) {
    29.             //数组的下标是从 0 开始，？的编号是 1 开始
    30.             for (int i = 0; i < args.length; i++) {
    31.                 pst.setObject(i + 1, args[i]);
    32.             }
    33.         }
    34.         //4、执行 sql
    35.         int len = pst.executeUpdate();
    36.         //5、释放资源
    37.         JDBCUtils.close(pst);
    38.         return len;
    39.     }
    40.  
    41.     //通用的查询方法之一：查询一行，即一个对象
    42.  
    43.     /**
    44.      * 执行查询操作的 SQL 语句，SQL 可以带参数(?)
    45.      *
    46.      * @param clazz Class 查询的结果需要封装的实体的 Class 类型，例如：学生 Student，商品 Goods,订单 Order
    47.      * @param sql   String 执行查询操作的 SQL 语句
    48.      * @param args  Object... 对应的每个?设置的值，顺序要与?对应
    49.      * @return T 封装了查询结果的实体
    50.      * @throws Exception
    51.      */
    52.     public static <T> T get(Class<T> clazz, String sql, Object... args) throws Exception {
    53.         //1、注册驱动
    54.         //2、获取连接
    55.         Connection conn = JDBCUtils.getConn();
    56.         //3、对 sql 进行预编译
    57.         PreparedStatement pst = conn.prepareStatement(sql);
    58.         //4、设置？
    59.         if (args != null && args.length > 0) {
    60.             //数组的下标是从 0 开始，？的编号是 1 开始
    61.             for (int i = 0; i < args.length; i++) {
    62.                 pst.setObject(i + 1, args[i]);
    63.             }
    64.         }
    65.         //5、查询
    66.         ResultSet rs = pst.executeQuery();
    67.         //获取查询的结果集的元数据信息
    68.         ResultSetMetaData rsmd = rs.getMetaData();
    69.         //这是查询的结果集中，一共有几列
    70.         int count = rsmd.getColumnCount();
    71.         T t = clazz.newInstance();//要求这个 Javabean 类型必须有无参构造
    72.         while (rs.next()) {
    73.             /*
    74.              * 问题？
    75.              * （1）sql 语句中查询了几列，每一列是什么属性
    76.              * （2）怎么把这个值设置到 Javabean 的属性中
    77.              */
    78.             //循环每一行有几列
    79.             for (int i = 0; i < count; i++) {
    80.                 //第几列的名称
    81.                 // String columnName = rsmd.getColumnName(i+1);
    82.                 //如果 sql 中没有取别名，那么就是列名，如果有别名，返回的是别名
    83.                 String fieldName = rsmd.getColumnLabel(i + 1);
    84.                 //该列的值
    85.                 // Object value = rs.getObject(columnName);
    86.                 Object value = rs.getObject(fieldName);
    87.                 //设置 obj 对象的某个属性中
    88.                 Field field = clazz.getDeclaredField(fieldName);//JavaBean 的属性名
    89.                 field.setAccessible(true);
    90.                 field.set(t, value);
    91.             }
    92.         }
    93.         //5、释放资源
    94.         JDBCUtils.close(conn, pst, rs);
    95.         return t;
    96.     }
    97.  
    98.     //通用的查询方法之二：查询多行，即多个对象
    99.     //Class<T> clazz：用来创建实例对象，获取对象的属性，并设置属性值
    100.  
    101.     /**
    102.      * 执行查询操作的 SQL 语句，SQL 可以带参数(?)
    103.      *
    104.      * @param clazz Class 查询的结果需要封装的实体的 Class 类型，例如：学生 Student，商品 Goods,订单 Order
    105.      * @param sql   String 执行查询操作的 SQL 语句
    106.      * @param args  Object... 对应的每个?设置的值，顺序要与?对应
    107.      * @return ArrayList 封装了查询结果的集合
    108.      * @throws Exception
    109.      */
    110.     public static <T> ArrayList<T> getList(Class<T> clazz, String sql, Object... args) throws Exception {
    111.         //1、注册驱动，不用了
    112.         //2、获取连接
    113.         Connection conn = JDBCUtils.getConn();
    114.         //3、对 sql 进行预编译
    115.         PreparedStatement pst = conn.prepareStatement(sql);
    116.         //4、对？进行设置值
    117.         if (args != null && args.length > 0) {
    118.             for (int i = 0; i < args.length; i++) {
    119.                 pst.setObject(i + 1, args[i]);
    120.             }
    121.         }
    122.         //5、执行 sql
    123.         ResultSet rs = pst.executeQuery();
    124.         //获取结果集的元数据
    125.         ResultSetMetaData metaData = rs.getMetaData();
    126.         //获取结果中总列数
    127.         int count = metaData.getColumnCount();
    128.         //创建集合对象
    129.         ArrayList<T> list = new ArrayList<T>();
    130.         while (rs.next()) {//遍历的行
    131.             //1、每一行是一个对象
    132.             T obj = clazz.newInstance();
    133.             //2、每一行有很多列
    134.             //for 的作用是为 obj 对象的每一个属性设置值
    135.             for (int i = 0; i < count; i++) {
    136.                 //(1)每一列的名称
    137.                 String fieldName = metaData.getColumnLabel(i + 1);//获取第几列的名称，如果有别名获取别名，如果没有别名获取列名
    138.                 //(2)每一列的值
    139.                 Object value = rs.getObject(i + 1);//获取第几列的值
    140.                 //(3)获取属性对象
    141.                 Field field = clazz.getDeclaredField(fieldName);
    142.                 //(4)设置可见性
    143.                 field.setAccessible(true);
    144.                 //(5)设置属性值
    145.                 field.set(obj, value);
    146.             }
    147.             //3、把 obj 对象放到集合中
    148.             list.add(obj);
    149.         }
    150.         //6、释放资源
    151.         JDBCUtils.close(conn, pst, rs);
    152.         //7、返回结果
    153.         return list;
    154.     }
    155.  
    156.     //通用的查询方法之三：查询单个值
    157.     //单值：select max(salary) from employee; 一行一列
    158.     //select count(*) from t_goods; 一共几件商品
    159.     public static Object getValue(String sql, Object... args) throws Exception {
    160.         //2、获取连接
    161.         Connection conn = JDBCUtils.getConn();
    162.         //3、对 sql 进行预编译
    163.         PreparedStatement pst = conn.prepareStatement(sql);
    164.         //4、对？进行设置值
    165.         if (args != null && args.length > 0) {
    166.             for (int i = 0; i < args.length; i++) {
    167.                 pst.setObject(i + 1, args[i]);
    168.             }
    169.         }
    170.         //5、执行 sql
    171.         ResultSet rs = pst.executeQuery();
    172.         Object value = null;
    173.         if (rs.next()) {//一行
    174.             value = rs.getObject(1);//一列
    175.         }
    176.         //6、释放资源
    177.         JDBCUtils.close(conn, pst, rs);
    178.         return value;
    179.     }
    180. }

    第二步 sql构建器 SqlBuilder 类

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

    类的一些字段

    1. public class SqlBuilder {
    2.     String sql;
    3.     String tbName;// 表名
    4.     String joinTb;// 连接的表的名称
    5.  
    6.     private SqlBuilder() {
    7.     }
    8.  
    9.     public static SqlBuilder build() {
    10.         return new SqlBuilder();
    11.     }
    12.  
    13.     public String sql() {
    14.         return this.sql;
    15.     }
    16.  
    17.     // 自定义基础sql
    18.     public SqlBuilder base(String U_sql) {
    19.         this.sql = U_sql;
    20.         return this;
    21.     }
    22.  
    23.     public SqlBuilder tbName(String tbName) {
    24.         this.tbName = tbName;
    25.         return this;
    26.     }
    27. }

    select

    select可以拆分为 'select x,x,x from tbName ' + 'join joinTb' + ' on tbName.x=joinTb.x ' + 'where tbName.x=?'
    为？的部分由用户调用原生接口时传入参数，jdbc填充，tbName和joinTb也需要用户传入，传入后在构建器内部保存这两个值，x是用户构建sql时要传入的，代表参数（字段）的名称，会写死在sql里 
    1.     public SqlBuilder select(String tbName, String... columns) {
    2.         this.sql = "select ";
    3.         for (int i = 0; i < columns.length; i++) {
    4.             if (i == columns.length - 1) {
    5.                 this.sql += columns[i] + " ";
    6.                 break;
    7.             }
    8.             this.sql += columns[i] + ", ";
    9.         }
    10.         this.sql += " from " + tbName;
    11.         return this;
    12.     }
    13.  
    14.     public SqlBuilder select(String[] columns) {
    15.         this.sql = "select ";
    16.         for (int i = 0; i < columns.length; i++) {
    17.             if (i == columns.length - 1) {
    18.                 this.sql += columns[i] + " ";
    19.                 break;
    20.             }
    21.             this.sql += columns[i] + ", ";
    22.         }
    23.         this.sql += " from " + tbName;
    24.         return this;
    25.     }
    26.  
    27.     public SqlBuilder select(String tbName) {
    28.         this.sql = "select * from " + tbName;
    29.         return this;
    30.     }
    31.  
    32.     public SqlBuilder select() {
    33.         this.sql = "select * from " + tbName;
    34.         return this;
    35.     }
    36.  
    37.     public SqlBuilder join(SqlJoinType type, String joinTb) {
    38.         this.joinTb = joinTb;
    39.         sql += " " + Common.JOIN_TYPE[type.ordinal()] + " join " + joinTb;
    40.         return this;
    41.     }
    42.  
    43.     public SqlBuilder on(String in_column, SqlCompareIdentity identity, String out_column) {
    44.         sql += " on " + joinTb + "." + in_column +
    45.                 Common.Compares[identity.ordinal()]
    46.                 + tbName + "." + out_column;
    47.         return this;
    48.     }
    49.  
    50.  
    51.     public SqlBuilder count(String tbName) {
    52.         this.sql = "select count(*) from " + tbName;
    53.         return this;
    54.     }
    55.  
    56.     public SqlBuilder count() {
    57.         this.sql = "select count(*) from " + tbName;
    58.         return this;
    59.     }
    60.  
    61.     public SqlBuilder where(String column, SqlCompareIdentity join) {
    62.         if (!sql.contains("where")) {
    63.             this.sql += " where " + column + Common.Compares[join.ordinal()] + " ? ";
    64.             return this;
    65.         }
    66.         this.sql += " and " + column + Common.Compares[join.ordinal()] + "? ";
    67.         return this;
    68.     }

    update

    update可以拆分为 'update tbName set x=?,x=?' + 'where x=?' 
    1.     public SqlBuilder update(String tbName, String... columns) {
    2.         this.sql = "update " + tbName + " set ";
    3.         for (int i = 0; i < columns.length; i++) {
    4.             if (i == columns.length - 1) {
    5.                 this.sql += columns[i] + "=? ";
    6.                 break;
    7.             }
    8.             this.sql += columns[i] + "=?,";
    9.         }
    10.         return this;
    11.     }
    12.  
    13.     public SqlBuilder update(String[] columns) {
    14.         this.sql = "update " + tbName + " set ";
    15.         for (int i = 0; i < columns.length; i++) {
    16.             if (i == columns.length - 1) {
    17.                 this.sql += columns[i] + "=? ";
    18.                 break;
    19.             }
    20.             this.sql += columns[i] + "=?,";
    21.         }
    22.         return this;
    23.     }

    delete

    delete => 'delete from tbName' + 'where x= ?' 
    1.     public SqlBuilder delete(String tbName) {
    2.         sql = "delete from " + tbName;
    3.         return this;
    4.     }
    5.  
    6.     public SqlBuilder delete() {
    7.         sql = "delete from " + tbName;
    8.         return this;
    9.     }

    insert

    insert => 'insert into tbName(x,x,x) values (?,?,?), (?,?,?)' 
    1.     public SqlBuilder insert(String tbName, String... params) {
    2.         sql = "insert into " + tbName;
    3.         sql += "(";
    4.         for (int i = 0; i < params.length; i++) {
    5.             if (i == params.length - 1) {
    6.                 sql += params[i] + ") ";
    7.                 break;
    8.             }
    9.             sql += params[i] + ",";
    10.         }
    11.         sql += "values (";
    12.         for (int i = 0; i < params.length; i++) {
    13.             if (i == params.length - 1) {
    14.                 sql += "?)";
    15.                 break;
    16.             }
    17.             sql += "?,";
    18.         }
    19.         return this;
    20.     }
    21.  
    22.     public SqlBuilder insert(String[] params) {
    23.         sql = "insert into " + tbName;
    24.         sql += "(";
    25.         for (int i = 0; i < params.length; i++) {
    26.             if (i == params.length - 1) {
    27.                 sql += params[i] + ") ";
    28.                 break;
    29.             }
    30.             sql += params[i] + ",";
    31.         }
    32.         sql += "values (";
    33.         for (int i = 0; i < params.length; i++) {
    34.             if (i == params.length - 1) {
    35.                 sql += "?)";
    36.                 break;
    37.             }
    38.             sql += "?,";
    39.         }
    40.         return this;
    41.     }

    测试sqlBuilder

    1. public class t {
    2.     @Before
    3.     public void before() {
    4.         // 设置数据库属性
    5.         JDBCUtils
    6.                 .setDataSource("jdbc:mysql://localhost:3306/test",
    7.                         "com.mysql.cj.jdbc.Driver", "root", "root");
    8.     }
    9.  
    10.     // sqlbuilder+jdbc封装
    11.     @Test
    12.     public void testSelectBuild() {
    13.         String sql = SqlBuilder.build()
    14.                 .select("tb_user")
    15.                 .where("id", SqlCompareIdentity.NE)
    16.                 .sql();
    17.         System.out.println(sql);
    18.  
    19.         String sql_cols = SqlBuilder.build()
    20.                 .select("tb_user", "username", "gender", "addr")
    21.                 .where("id", SqlCompareIdentity.NE)
    22.                 .sql();
    23.         System.out.println(sql_cols);
    24.  
    25.         String sql_cols_option2 = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).where("password", SqlCompareIdentity.NE).sql();
    26.         System.out.println(sql_cols_option2);
    27.  
    28.         String sql_count = SqlBuilder.build().count("tb_user").where("id", SqlCompareIdentity.GT).sql();
    29.         System.out.println(sql_count);
    30.     }
    31.  
    32.     @Test
    33.     public void testSelectBuildTbName() {
    34.         // 直接设置build的tbName,然后使用不需要tbName的方法来构建
    35.         String sql1 = SqlBuilder.build().tbName("tb_user").select().where("id", SqlCompareIdentity.NE).sql();
    36.         System.out.println(sql1);
    37.  
    38.         String sql_cols1 = SqlBuilder.build().tbName("tb_user").select(new String[]{"username", "gender", "addr"}).where("id", SqlCompareIdentity.NE).sql();
    39.         System.out.println(sql_cols1);
    40.  
    41.         String sql_cols_option21 = SqlBuilder.build().tbName("tb_user").select(new String[]{"username", "gender", "addr"}).where("id", SqlCompareIdentity.NE).where("password", SqlCompareIdentity.NE).sql();
    42.         System.out.println(sql_cols_option21);
    43.  
    44.         String sql_count1 = SqlBuilder.build().tbName("tb_user").count().where("id", SqlCompareIdentity.GT).sql();
    45.         System.out.println(sql_count1);
    46.  
    47.         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();
    48.         System.out.println(sql_join);
    49.     }
    50.  
    51.     @Test
    52.     public void testUpdateBuild() {
    53.         String sql = SqlBuilder.build()
    54.                 .update("tb_user", "password", "username")
    55.                 .where("id", SqlCompareIdentity.EQ)
    56.                 .sql();
    57.         System.out.println(sql);
    58.  
    59.         String sql_no_tbname = SqlBuilder.build().tbName("tb_user").update(new String[]{"password", "username"}).where("id", SqlCompareIdentity.EQ).sql();
    60.         System.out.println(sql_no_tbname);
    61.     }
    62.  
    63.     @Test
    64.     public void testInsertBuild() {
    65.         String sql = SqlBuilder.build()
    66.                 .insert("tb_user", "username", "password", "addr", "gender")
    67.                 .sql();
    68.         System.out.println(sql);
    69.     }
    70.  
    71.     @Test
    72.     public void testInsertBuildTbName() {
    73.         String sql = SqlBuilder.build()
    74.                 .tbName("tb_user")
    75.                 .insert(new String[]{"username", "password", "addr", "gender"}).sql();
    76.         System.out.println(sql);
    77.     }
    78.  
    79.     @Test
    80.     public void testDeleteBuild() {
    81.         String sql_tb = SqlBuilder.build()
    82.                 .tbName("tb_user").delete()
    83.                 .where("id", SqlCompareIdentity.EQ)
    84.                 .sql();
    85.         System.out.println(sql_tb);
    86.  
    87.         String sql = SqlBuilder.build().delete("tb_user")
    88.                 .where("id", SqlCompareIdentity.EQ).sql();
    89.         System.out.println(sql);
    90.     }
    91. }

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

    1.     @Before
    2.     public void before() {
    3.         // 设置数据库属性
    4.         JDBCUtils
    5.                 .setDataSource("jdbc:mysql://localhost:3306/test",
    6.                         "com.mysql.cj.jdbc.Driver", "root", "root");
    7.     }
    8.     
    9.     @Test
    10.     public void testSelectMulti() throws Exception {
    11.         ArrayList<TbUser> list;
    12.  
    13.         String sql = SqlBuilder.build().select("tb_user").where("id", SqlCompareIdentity.NE).sql();
    14.         list = Operate.getList(TbUser.class, sql, 1);
    15.         System.out.println(list);
    16.  
    17.         String sql_cols = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).sql();
    18.         list = Operate.getList(TbUser.class, sql_cols, 1);
    19.         System.out.println(list);
    20.  
    21.         String sql_cols_option2 = SqlBuilder.build().select("tb_user", "username", "gender", "addr").where("id", SqlCompareIdentity.NE).where("gender", SqlCompareIdentity.NE).sql();
    22.         list = Operate.getList(TbUser.class, sql_cols_option2, 1, "男");
    23.         System.out.println(list);
    24.  
    25.         String sql_count = SqlBuilder.build().count("tb_user").where("id", SqlCompareIdentity.GT).sql();
    26.         Long cnt = (Long) Operate.getValue(sql_count, 3);
    27.         System.out.println(cnt);
    28.     }
    29.  
    30.     @Test
    31.     public void testUpdate() throws SQLException {
    32.         int cnt = 0;
    33.         String sql = SqlBuilder.build()
    34.                 .update("tb_user", "password", "username")
    35.                 .where("id", SqlCompareIdentity.EQ)
    36.                 .sql();
    37.         cnt = Operate.update(sql, "O", "t50", "13");
    38.         System.out.println("影响了" + cnt + "条数据");
    39.  
    40.         String sql_no_tbname = SqlBuilder.build()
    41.                 .tbName("tb_user")
    42.                 .update(new String[]{"password", "username"})
    43.                 .where("id", SqlCompareIdentity.EQ)
    44.                 .sql();
    45.         cnt = Operate.update(sql_no_tbname, "Obu", "t50123", "13");
    46.         System.out.println("影响了" + cnt + "条数据");
    47.     }
    48.     
    49.     @Test
    50.     public void testInsert() throws SQLException {
    51.         int count = 0;
    52.         String sql = SqlBuilder.build()
    53.                 .insert("tb_user", "username", "password", "addr", "gender")
    54.                 .sql();
    55.         count = Operate.update(sql, "name", "pass", "cn", "男");
    56.         System.out.println("影响了" + count + "条数据");
    57.     }
    58.  
    59.     @Test
    60.     public void testDelete() throws SQLException {
    61.         int cnt = 0;
    62.         String sql_tb = SqlBuilder.build()
    63.                 .tbName("tb_user")
    64.                 .delete()
    65.                 .where("id", SqlCompareIdentity.EQ).sql();
    66.         cnt = Operate.update(sql_tb, 219);
    67.         System.out.println("影响了" + cnt + "条数据");
    68.     }

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

    SqlCompareIdentity 
    1. public enum SqlCompareIdentity {
    2.     GT,// >
    3.     GE,// >=
    4.     LT, // <
    5.     LE,// <=
    6.     EQ, // =
    7.     NE, // !=
    8. }
    Common 
    1. public class Common {
    2.     public static final String[] Compares = new String[]{
    3.             ">", ">=", "<", "<=", "=", "!=", "like", "in"
    4.     };
    5.     // baseCRUD方法的名称
    6.     public static final String[] JOIN_TYPE = new String[]{
    7.             "left","right","full","inner","left outer","right outer"
    8.     };
    9. }
    SqlJoinType 
    1. public enum SqlJoinType {
    2.     // "left","right","full","inner","left outer","right outer"
    3.     LEFT,
    4.     RIGHT,
    5.     FULL,
    6.     INNER,
    7.     LO,
    8.     RO
    9. }

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

    思路：BaseCRUDRepository里定义基本curd方法，select方法的返回值由传入的泛型决定，因为方法名是死的，所以可以通过方法名来判断是哪个方法，我们可以在动态代理里根据不同方法提供不同的实现，用户只需要创建接口继承BaseCRUDRepository，就可以使用这些方法
    Repository是一个注解，主要是指定数据库表名用的 
    1. package org.malred.annotations;
    2.  
    3. import java.lang.annotation.ElementType;
    4. import java.lang.annotation.Retention;
    5. import java.lang.annotation.RetentionPolicy;
    6. import java.lang.annotation.Target;
    7.  
    8. @Target(ElementType.TYPE)
    9. @Retention(RetentionPolicy.RUNTIME)
    10. public @interface Repository {
    11.     String value(); // 表名
    12. }
    Operate
    这里的type1是执行jdbc方法需要的实体类类型，本来想通过泛型拿到，但是好像java不能反射获取类的泛型 
    1.     // 定义在Operate类里
    2.     public static <T> T getMapper(Class<?> mapperClass, Class<?> type1) {
    3.         // 使用JDK动态代理为Dao接口生成代理对象,并返回
    4.         Object proxyInstance = Proxy.newProxyInstance(Operate.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
    5.             @Override
    6.             public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    7.                 // 拿到表名
    8.                 Repository annotation = mapperClass.getAnnotation(Repository.class);
    9.                 String tbName = annotation.value();
    10.                 // 拼装sql
    11. //                        String sql = (String) Common.DefaultCRUDSql.get(method.getName());
    12. //                        System.out.println(sql);
    13.                 String sql = "";
    14.                 // 默认CRUD接口的代理方法
    15.                 switch (method.getName()) {
    16.                     case "findAll": {
    17.                         sql = SqlBuilder.build().tbName(tbName).select().sql();
    18.                         System.out.println("执行findAll方法");
    19.                         System.out.println("当前执行的sql语句: " + sql);
    20.                         return Operate.getList(type1, sql);
    21.                     }
    22.                     case "findById": {
    23.                         sql = SqlBuilder.build().tbName(tbName).select().where("id", SqlCompareIdentity.EQ).sql();
    24.                         System.out.println("执行findById方法");
    25.                         System.out.println("当前执行的sql语句: " + sql);
    26.                         return Operate.get(type1, sql, args);
    27.                     }
    28.                     case "update": {
    29.                         ParseClazz parseClazz = parseObjectArgs(args);
    30.  
    31.                         String[] paramNames = new String[parseClazz.params.keySet().size()];
    32.                         for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
    33.                             paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
    34.                         }
    35.                         sql = SqlBuilder.build().update(tbName, paramNames).where(parseClazz.idName, SqlCompareIdentity.EQ).sql();
    36.  
    37.                         System.out.println("执行update方法");
    38.                         System.out.println("当前执行的sql语句: " + sql);
    39.  
    40.                         String[] paramVals = new String[parseClazz.params.values().size() + 1];
    41.                         for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
    42.                             paramVals[i] = parseClazz.params.values().toArray()[i].toString();
    43. //                                    System.out.println(paramVals[i]);
    44.                         }
    45.                         paramVals[paramVals.length - 1] = parseClazz.idVal.toString();
    46.                         return Operate.update(sql, paramVals);
    47.                     }
    48.                     case "insert": {
    49.                         ParseClazz parseClazz = parseObjectArgs(args);
    50.                         String[] paramNames = new String[parseClazz.params.keySet().size()];
    51.                         for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
    52.                             paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
    53.                         }
    54.  
    55.                         sql = SqlBuilder.build().tbName(tbName).insert(paramNames).sql();
    56.                         System.out.println("执行insert方法");
    57.                         System.out.println("当前执行的sql语句: " + sql);
    58.  
    59.                         String[] paramVals = new String[parseClazz.params.values().size()];
    60.                         for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
    61.                             paramVals[i] = parseClazz.params.values().toArray()[i].toString();
    62. //                                    System.out.println(paramVals[i]);
    63.                         }
    64.                         return update(sql, paramVals);
    65.                     }
    66.                     case "delete": {
    67.                         sql = SqlBuilder.build().tbName(tbName).delete().where("id", SqlCompareIdentity.EQ).sql();
    68.                         System.out.println("执行delete方法");
    69.                         System.out.println("当前执行的sql语句: " + sql);
    70.                         return update(sql, args[0]);
    71.                     }
    72.                 }
    73.                 return null;
    74.             }
    75.         });
    76.         return (T) proxyInstance;
    77.     }

    测试

    1.     // 基本CRUD接口的代理实现测试
    2.     @Test
    3.     public void testSelectProxy() {
    4.         UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    5. //        UserRepository mapper = Operate.getMapper(UserRepository.class);
    6.         List<TbUser> all = mapper.findAll();
    7.         System.out.println(all);
    8.  
    9.         TbUser one = mapper.findById(1);
    10.         System.out.println(one);
    11.     }
    12.  
    13.     @Test
    14.     public void testUpdateProxy() {
    15.         int cnt = 0;
    16. //        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    17.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    18.  
    19.         cnt = mapper.update(new TbUser(212, "ema1n", "s1sap", "女", null));
    20.         System.out.println("影响了" + cnt + "条数据");
    21.  
    22. //        cnt = mapper.update(new TbUser(211, "name", "pass", null, null));
    23. //        System.out.println("影响了" + cnt + "条数据");
    24.     }
    25.  
    26.     @Test
    27.     public void testInsertProxy() {
    28.         int cnt = 0;
    29. //        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    30.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    31.  
    32.         cnt = mapper.insert(new TbUser(0, "eman", "ssap", null, null));
    33.         System.out.println("影响了" + cnt + "条数据");
    34.     }
    35.  
    36.     @Test
    37.     public void testDeleteProxy() {
    38.         int cnt = 0;
    39.         UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    40.  
    41.         cnt = mapper.delete(223);
    42.         System.out.println("影响了" + cnt + "条数据");
    43.     }

    3. 注解+接口方法

    思路，依然是走动态代理，只是sql的获取不再是框架构建，而是用户在注解中定义，框架只是把值填充，这里我们需要注意，如果是第二种方法和注解的混用，会有一个类型问题
    这个问题就是，如果传入了type1，那么所有被代理的方法如果都用type1作为调用jdbc时传入的实体类型，就无法使用其他类型（无法查询并封装到其他类型），而我们的解决方法是，注解的方法就根据注解方法的返回值来传递实体类型，base方法就用type1 
    1.     public static <T> T getMapper(Class<?> mapperClass, Class<?> type1) {
    2.         // 使用JDK动态代理为Dao接口生成代理对象,并返回
    3.         Object proxyInstance = Proxy.newProxyInstance(Operate.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
    4.             @Override
    5.             public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    6.                 // 用户定义的方法的返回类型
    7.                 Class<?> type = null;
    8.                 //获取方法的返回值类型
    9.                 Type genericReturnType = method.getGenericReturnType();
    10.                 if (!method.getName().equals("findAll") && !method.getName().equals("findById")) {
    11.                     if (genericReturnType instanceof ParameterizedType) {
    12.                         Type[] actualTypeArguments = ((ParameterizedType) genericReturnType).getActualTypeArguments();
    13.                         for (Type parameterType : actualTypeArguments) {
    14.                             System.out.println(parameterType);
    15.                             type = (Class<?>) parameterType;
    16.                         }
    17.                     } else {
    18.                         type = (Class<?>) genericReturnType;
    19.                     }
    20.                 }
    21.                 // 拿到表名
    22.                 Repository annotation = mapperClass.getAnnotation(Repository.class);
    23.                 String tbName = annotation.value();
    24.                 // 拼装sql
    25. //                        String sql = (String) Common.DefaultCRUDSql.get(method.getName());
    26. //                        System.out.println(sql);
    27.                 String sql = "";
    28.                 // 默认CRUD接口的代理方法
    29.                 switch (method.getName()) {
    30.                     case "findAll": {
    31.                         sql = SqlBuilder.build().tbName(tbName).select().sql();
    32.                         System.out.println("执行findAll方法");
    33.                         System.out.println("当前执行的sql语句: " + sql);
    34.                         return Operate.getList(type1, sql);
    35.                     }
    36.                     case "findById": {
    37.                         sql = SqlBuilder.build().tbName(tbName).select().where("id", SqlCompareIdentity.EQ).sql();
    38.                         System.out.println("执行findById方法");
    39.                         System.out.println("当前执行的sql语句: " + sql);
    40.                         return Operate.get(type1, sql, args);
    41.                     }
    42.                     case "update": {
    43.                         ParseClazz parseClazz = parseObjectArgs(args);
    44.  
    45.                         String[] paramNames = new String[parseClazz.params.keySet().size()];
    46.                         for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
    47.                             paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
    48.                         }
    49.                         sql = SqlBuilder.build().update(tbName, paramNames).where(parseClazz.idName, SqlCompareIdentity.EQ).sql();
    50.  
    51.                         System.out.println("执行update方法");
    52.                         System.out.println("当前执行的sql语句: " + sql);
    53.  
    54.                         String[] paramVals = new String[parseClazz.params.values().size() + 1];
    55.                         for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
    56.                             paramVals[i] = parseClazz.params.values().toArray()[i].toString();
    57. //                                    System.out.println(paramVals[i]);
    58.                         }
    59.                         paramVals[paramVals.length - 1] = parseClazz.idVal.toString();
    60.                         return Operate.update(sql, paramVals);
    61.                     }
    62.                     case "insert": {
    63.                         ParseClazz parseClazz = parseObjectArgs(args);
    64.                         String[] paramNames = new String[parseClazz.params.keySet().size()];
    65.                         for (int i = 0; i < parseClazz.params.keySet().toArray().length; i++) {
    66.                             paramNames[i] = parseClazz.params.keySet().toArray()[i].toString();
    67.                         }
    68.  
    69.                         sql = SqlBuilder.build().tbName(tbName).insert(paramNames).sql();
    70.                         System.out.println("执行insert方法");
    71.                         System.out.println("当前执行的sql语句: " + sql);
    72.  
    73.                         String[] paramVals = new String[parseClazz.params.values().size()];
    74.                         for (int i = 0; i < parseClazz.params.values().toArray().length; i++) {
    75.                             paramVals[i] = parseClazz.params.values().toArray()[i].toString();
    76. //                                    System.out.println(paramVals[i]);
    77.                         }
    78.                         return update(sql, paramVals);
    79.                     }
    80.                     case "delete": {
    81.                         sql = SqlBuilder.build().tbName(tbName).delete().where("id", SqlCompareIdentity.EQ).sql();
    82.                         System.out.println("执行delete方法");
    83.                         System.out.println("当前执行的sql语句: " + sql);
    84.                         return update(sql, args[0]);
    85.                     }
    86.                 }
    87.                 // 如果都不是上面的,就是用户自己定义的
    88.                 if (method.isAnnotationPresent(Select.class)) {
    89.                     Select selectAnno = method.getAnnotation(Select.class);
    90.                     sql = selectAnno.value();
    91.                     // 判断是查询单个还是多个(返回值类型是List之类的吗)
    92.                     // 这里只是简单判断一下
    93. //                            Type genericReturnType = method.getGenericReturnType();
    94.                     // 判断是否进行了泛型类型参数化(是否有泛型)
    95.                     if (genericReturnType instanceof ParameterizedType) {
    96. //                            if (x instanceof Collection< ? >){
    97. //                            }
    98. //                            if (x instanceof Map<?,?>){
    99. //                            }
    100.                         return Operate.getList(type, sql, args);
    101.                     }
    102.                     return Operate.get(type, sql, args);
    103.                 }
    104.                 if (method.isAnnotationPresent(Update.class)) {
    105.                     Update anno = method.getAnnotation(Update.class);
    106.                     sql = anno.value();
    107.                     return update(sql, args);
    108.                 }
    109.                 if (method.isAnnotationPresent(Delete.class)) {
    110.                     Delete anno = method.getAnnotation(Delete.class);
    111.                     sql = anno.value();
    112.                     return update(sql, args);
    113.                 }
    114.                 if (method.isAnnotationPresent(Insert.class)) {
    115.                     Insert anno = method.getAnnotation(Insert.class);
    116.                     sql = anno.value();
    117.                     return update(sql, args);
    118.                 }
    119.                 // 返回值
    120.                 return null;
    121.             }
    122.         });
    123.         return (T) proxyInstance;
    124.     }

    测试

    1.     // 用户定义的注解的代理实现测试
    2.     @Test
    3.     public void testSelectAnnotation() {
    4.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    5. //        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    6.  
    7.         TbUser user = mapper.selectOneByUsername("张三");
    8.         System.out.println(user);
    9.  
    10.         List<TbUser> tbUsers = mapper.selectOneByNEPassword("456");
    11.         for (TbUser tbUser : tbUsers) {
    12.             System.out.println(tbUser);
    13.         }
    14.  
    15.         // 和因为在代理时写死传入的返回值类型,这里只能重新创建代理
    16.         // 根据接口方法的返回值获取泛型类型,动态判断返回类型
    17. //        UserRepository puMapper =
    18. //                Operate.getMapper(UserRepository.class, ProductAndUser.class);
    19. //        UserRepository puMapper = Operate.getMapper(UserRepository.class);
    20.         List<ProductAndUser> userAndProductJoin = mapper.findUserAndProductJoin(7);
    21.         System.out.println(userAndProductJoin);
    22.     }
    23.  
    24.     @Test
    25.     public void testUpdateAnnotation() {
    26. //        UserRepository mapper =
    27. //                Operate.getMapper(UserRepository.class, TbUser.class);
    28.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    29.         int cnt = mapper.uptUser("哇哈哈1", 14);
    30.         System.out.println("影响了" + cnt + "条数据");
    31.     }
    32.  
    33.     @Test
    34.     public void testDeleteAnnotation() {
    35. //        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    36.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    37.         int cnt = mapper.delUser(218, "tpass");
    38.         System.out.println("影响了" + cnt + "条数据");
    39.     }
    40.  
    41.     @Test
    42.     public void testInsertAnnotation() {
    43. //        UserRepository mapper = Operate.getMapper(UserRepository.class, TbUser.class);
    44.         UserRepository mapper = Operate.getMapper(UserRepository.class);
    45.         int cnt = mapper.addUser("tttt", "tpass", "tttt2", "tpass2");
    46.         System.out.println("影响了" + cnt + "条数据");
    47.     }

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

    思路：实体类加上注解，指定表名，Operator的static里扫描注解并将key-表名：val-扫到的类注入到一个map，代理方法里通过Repository里的表名和map里的匹配，拿到实体类，拼接字符串selectByxxx，放入list，当执行方法，进入代理，就判断是否在list中包含，然后实现代理逻辑
  • 相关阅读:
    [模拟][模电][面试][运放]仪表放大器
    java写一个用于生成雪花id的工具类
    【UNR #6 A】面基之路（最短路）
    Lagent & AgentLego 智能体应用搭建——笔记
    即时编译器JIT
    优化|优化处理可再生希尔伯特核空间的非参数回归中的协变量偏移
    JWT一篇通
    【算法|贪心算法系列No.5】leetcode409. 最长回文串
    js中各种数据类型检测与判定
    美创科技勒索病毒“零信任”防护和数据安全治理体系的探索实践
  • 原文地址：https://blog.csdn.net/m0_60707623/article/details/133392772