手写持久层框架------无需写sql语句即可完成对单表的CRUD操作。

标签：tableField String CRUD field 单表 sql null name

目的：巩固知识点

技术栈：java+mysql+反射+自定义注解+泛型+jdbc
持久层框架：与数据库交互的一层成为持久层。完成orm操作
o(Object对象) r:(relative关系) m:(mapping映射)。实体类---数据库表属性---表的字段实体类对象---- 一条记录集合----表中多条记录
手撕持久层框架：自己编写持久层和框架，可以完成无需写sql语句即可完成对单表的CRUD操作。

1.1创建一个maven的java工程

1.2 引入依赖的jar

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>

1.3 创建数据源的属性文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai
username=账号
password=密码

1.4 创建DbUtil工具类

package com.syh.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class Dbutil {
    private static final DataSource dataSource;
//    private static String driverClassName;
//    private static String url;
//    private static String username;
//    private static String password;
    static {
        try {
            //该类用于读取属性文件
            Properties properties = new Properties();
            //加载属性文件
            InputStream inputStream = ClassLoader.getSystemResourceAsStream("db.properties");
            properties.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }
        catch (Exception e) {
            throw new RuntimeException("无法读取db.properties");
        }
    }
    //获取连接数据库对象:思考:如果我们把驱动名和url和密码硬编码放在配置文件中,那么我们就不需要在代码中写死这些内容了,只需要在配置文件中读取即可
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        }
        catch (Exception e) {
            throw new RuntimeException("1111");
        }
    }

    public static void closeAll(Connection connection, PreparedStatement ps,ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        catch (Exception e) {
            throw new RuntimeException("无法关闭数据库连接");
        }
    }
}

1.5 定义注解

//属性的注解
@Target(ElementType.FIELD)
//运行时执行
@Retention(RetentionPolicy.RUNTIME)
public @interface TableField {
    String name();
}


//主键的注解
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableFieldKey {
    String value() default "id";
}


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

1.6 单表的添加

public class BaseDao<T> {
    private Class<T> clazz;

    public BaseDao() {
        //this表示子类的dao对象
        Class<? extends BaseDao> aClass = this.getClass();
        //获取当前子类的父类的的反射类
        ParameterizedType genericSuperclass = (ParameterizedType) aClass.getGenericSuperclass();

        //获取反射类中的泛型类型
        Type actualTypeArgument = genericSuperclass.getActualTypeArguments()[0];
        clazz = (Class) actualTypeArgument;
    }

    //添加功能
    public int insert(T t) {
        StringBuilder sql = new StringBuilder("insert into ");
        //根据对象获取该对象的反射类Class
        Class<?> aClass = t.getClass();
        //获取反射类上的注解对象
        TableName annotation = aClass.getAnnotation(TableName.class);
        //表名
        String tableName = "";
        if (annotation != null) {
            //获取注解的属性
            tableName = annotation.value();
        } else {
            //获取实体名
            tableName = aClass.getSimpleName();
        }
        sql.append(tableName);
        //获取所有列名
        Field[] fields = aClass.getDeclaredFields();
        ArrayList<String> columns = new ArrayList<String>();
        //获取所有值
        ArrayList<Object> values = new ArrayList<Object>();
        for (Field field : fields) {
            TableField tableField = field.getAnnotation(TableField.class);
            TableFieldKey tableFieldKey = field.getAnnotation(TableFieldKey.class);
            String name = "";
            if (tableFieldKey != null) {
                continue;
            }
            if (tableField != null) {
                name = tableField.name();
            } else {
                name = field.getName();
            }
            Object v = null;
            field.setAccessible(true);
            try {
                v = field.get(t);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            values.add("'" + v + "'");
            columns.add(name);
        }


        String columName = columns.toString().replace("[", "(").replace("]", ")");
        String valueNames = values.toString().replace("[", "(").replace("]", ")");
        sql.append(columName);
        sql.append(" values ");
        sql.append(valueNames);
        System.out.println(sql);

        int i = 0;

        try {
            Connection connection = Dbutil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return i;
    }
}

1.7 单表的修改

public int update(T t) {
        StringBuffer sql = new StringBuffer("update ");
        //获取表名
        Class<?> aClass = t.getClass();
        String tableName = aClass.getSimpleName();
        TableName annotation = aClass.getAnnotation(TableName.class);
        if (annotation != null) {
            //获取注解的表名
            tableName = annotation.value();
        }
        sql.append(tableName + " set ");
        String where = " where ";
        //获取所有列对象
        Field[] fields = aClass.getDeclaredFields();
        for (Field field : fields) {
            //获取属性名
            String name = field.getName();
            TableField tableField = field.getAnnotation(TableField.class);
            // 尝试获取字段上的TableFieldKay注解，用于后续判断是否需要拼接SQL的where条件
            //获取主键
            TableFieldKey tableFieldKey = field.getAnnotation(TableFieldKey.class);
            // 设置字段可访问，以突破访问权限限制，获取字段值
            field.setAccessible(true);
            Object value = null;
            try {
                // 尝试获取字段的值
                value = field.get(t);
            } catch (IllegalAccessException e) {
                // 当访问字段失败时，打印异常堆栈跟踪，但并未对异常进行进一步处理
                e.printStackTrace();
            }
            // 如果字段上存在TableFieldKay注解，则拼接SQL的where条件
            if (tableFieldKey != null) {
                // 从注解中获取字段在数据库表中的名称
                String tableFieldKayName = tableFieldKey.value();
                // 拼接where条件，此处应注意SQL注入的风险
                where += tableFieldKayName + "='" + value + "'";
                continue;
            }
            if (tableField != null) {
                //获取注解的属性名
                name = tableField.name();
            }
            sql.append(name + "='" + value + "',");
        }
        try {

            sql.deleteCharAt(sql.length() - 1).append(where);
            System.out.println(sql);
            Connection connection = Dbutil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

1.8 单表的删除

//删除
    public int delete(Object id) {
        StringBuffer sql = new StringBuffer("delete from ");
        //获取表名
        String tableName = clazz.getSimpleName();
        //获取注解的表名
        TableName annotation = clazz.getAnnotation(TableName.class);
        if (annotation != null) {
            tableName = annotation.value();
        }
        sql.append(tableName + " where ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            //获取where条件注解
            TableFieldKey tableField = field.getAnnotation(TableFieldKey.class);
            //允许获得私有属性的值
            field.setAccessible(true);
            if (tableField != null) {
                String tableFieldKay = tableField.value();
                sql.append(tableFieldKay + "='" + id + "'");
            }
        }
        try {
            System.out.println(sql);
            Connection conn = Dbutil.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

1.9 根据id查询

public T selectById(Object id) {
        StringBuffer sql = new StringBuffer("select * from ");
        //获取表名
        String tableName = clazz.getSimpleName();
        TableName annotation = clazz.getAnnotation(TableName.class);
        if (annotation != null) {
            tableName = annotation.value();
        }
        sql.append(tableName + " where ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            TableFieldKey tableField = field.getAnnotation(TableFieldKey.class);
            if (tableField != null) {
                String tableFieldKay = tableField.value();
                sql.append(tableFieldKay + "='" + id + "'");
            }
        }
        System.out.println(sql);
        Connection conn = Dbutil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    TableField tableField = field.getAnnotation(TableField.class);
                    TableFieldKey tableId = field.getAnnotation(TableFieldKey.class);
                    //获取属性名
                    String name = field.getName();
                    if (tableField != null) {
                        name = tableField.name();
                    }
                    if (tableId != null) {
                        name = tableId.value();
                    }
                    Object object = rs.getObject(name);
                    field.set(t, object);
                }
                System.out.println(t);
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

1.10 查询所有

public List<T> selectAll() {
        List<T> list=new ArrayList<>();
        StringBuilder sql = new StringBuilder("select * from ");
        //获取表名
        String tableName = clazz.getSimpleName();
        TableName annotation = clazz.getAnnotation(TableName.class);
        if (annotation != null) {
            tableName = annotation.value();
        }
        sql.append(tableName);
        Connection conn = Dbutil.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();

            Field[] fields = clazz.getDeclaredFields();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    TableField tableField = field.getAnnotation(TableField.class);
                    TableFieldKey tableId = field.getAnnotation(TableFieldKey.class);
                    String name = field.getName();
                    if (tableField != null) {
                        name = tableField.name();
                    }
                    if (tableId != null) {
                        name = tableId.value();
                    }
                    Object object = rs.getObject(name);
                    field.set(t, object);
                }
                list.add(t);
            }
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

2. 测试

可姨进行相关测试，自己测试

标签：tableField,String,CRUD,field,单表,sql,null,name
From： https://blog.csdn.net/m0_72156649/article/details/140315150

手写持久层框架------无需写sql语句即可完成对单表的CRUD操作。

1.1创建一个maven的java工程

1.2 引入依赖的jar

1.3 创建数据源的属性文件

1.4 创建DbUtil工具类

1.5 定义注解

1.6 单表的添加

1.7 单表的修改

1.8 单表的删除

1.9 根据id查询

1.10 查询所有

2. 测试

相关文章

赞助商

阅读排行