标签：手工 String 框架 class ORM user sql public append

手工实现一个ORM框架

目的

温故而知新

了解ORM框架实现的基本原理

与面试官吹B

本项目所需前置知识

注解@interface
泛型
JDBC

相关知识简介

认识JDBC

JDBC是什么？

Java DateBase connectivity：Java数据库连接，Java语言连接数据库
JDBC本质：利用Java语言连接数据库、操作数据库的一整套接口规范

为什么存在JDBC？

屏蔽底层的技术实现细节，让程序员专注于业务代码。说人话就是：连接mysql数据库时就用mysql厂商提供的jdbc实现；而连接oracle数据库时就用oracle厂商提供的jdbc实现。基于同一套接口规范调用不同厂商实现的jdbc，来实现连接不同的数据库。

举个不太恰当的例子：人饿了需要吃饭，吃饭一般步骤可以归纳为以下几步：1.排队 2.点餐 3.付款 4.吃饭，基于这4步（规范），我们便可以轻车熟路的填饱肚子：想吃汉堡就去肯德基、麦当劳；想吃拉面就去兰州拉面；想吃火锅就去火锅店。而不必担心吃汉堡需不需要带叉子，吃拉面需不需要带筷子，吃大饼需不需要带手套。

如何使用JDBC操作数据库

数据库连接通用方法

public class DBUtil {
    private static String URL = "jdbc:mysql://node1:3306/demo";
    private static String USERNAME = "user";
    private static String PASSWORD= "Password@123";

    private DBUtil() {
        throw new RuntimeException("can not instance.");
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            System.out.printf("获取数据库连接异常:%s",e);
        }
        return connection;
    }

    public static void closeConnection(Connection c, Statement s) {
        try {
            if (s != null) {
                s.close();
            }
            if (c != null) {
                c.close();
            }
        } catch (SQLException e) {
            System.out.printf("关闭数据库连接异常:%s",e);
        }
    }

    public static void closeConnection(Connection c, Statement s, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (c != null) {
                c.close();
            }
        } catch (SQLException e) {
            System.out.printf("关闭数据库连接异常:%s",e);
        }
    }
}

获取数据库连接

public class DBUtilTest {

    @Test
    public void getConnection() {
        Connection connection = DBUtil.getConnection();
        Assert.assertNotNull(connection);
    }
}

通过数据库连接查询表数据

public class DBUtilTest {

    @Test
    public void queryTableRow() throws SQLException {
        String sql = "select * from user where id=?";

        Connection connection = DBUtil.getConnection();
        PreparedStatement pst = connection.prepareStatement(sql);
        // 查询ID为1的用户数据
        pst.setObject(1, 1L);

        ResultSet rs = pst.executeQuery();
        Assert.assertNotNull(rs);
        while (rs.next()) {
            // 用户表中的每一行数据存在7列
            for (int i = 1; i <= 7; i++) {
                String columnName = rs.getMetaData().getColumnName(i);
                Object columnValue = rs.getObject(i);
                System.out.printf("column: %s, value:%s\n", columnName, columnValue);
            }
        }
    }
}

封装一个DAO对象

我们知道在Java中一切皆对象，同样的，在对数据源的访问操作，自然应该进行抽象和封装。基于此，DAO（Data Access Object）便诞生了。

首先创建一个实体对象，与数据库中的行对应

public class User {

    /**
     * 自增主键
     */
    private Long id;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;
    /**
     * 盐
     */
    private String salt;
    /**
     * 身份证
     */
    private String identityCard;
    /**
     * 创建时间
     */
    private LocalDateTime createTime;
    /**
     * 更新时间
     */
    private LocalDateTime updateTime;

    // getter、setter...
}

其次创建一个DAO对象，用于操作数据库

public class UserDao {

    public User queryUserById(Long id) throws SQLException {
        String sql = "select * from user where id=?";
        Connection connection = DBUtil.getConnection();
        PreparedStatement pst = connection.prepareStatement(sql);
        pst.setObject(1, id);
        ResultSet rs = pst.executeQuery();

        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong(1));
            user.setUsername(rs.getString(2));
            user.setPassword(rs.getString(3));
            user.setSalt(rs.getString(4));
            user.setIdentityCard(rs.getString(5));
            user.setCreateTime(rs.getObject(6, LocalDateTime.class));
            user.setUpdateTime(rs.getObject(7, LocalDateTime.class));
            return user;
        }
        return null;
    }
    
    // create、update、delete...
}

最后，编写单元测试

public class UserDaoTest {

    @Test
    public void queryUserById() throws SQLException {
        UserDao userDao = new UserDao();
        User user = userDao.queryUserById(1L);
        Assert.assertNotNull(user);
    }
}

至此，一个简单的DAO对象便封装完成了。

认识泛型

泛型是什么？

泛型：就是指在类定义时不会设置类中的属性或方法参数的具体类型，而是在类使用时（创建对象）再进行类型的定义。会在编译期检查类型是否错误。

如何使用泛型

创建泛型类

public class BaseDao<T> {
    private T t;

    public T getT() {
        return t;
    }

    public void setT(T t) {
        this.t = t;
    }
}

编写测试用例

public class BaseDaoTest {

    @Test
    public void setGetT() {
        BaseDao<Integer> baseDao = new BaseDao<>();
        baseDao.setT(123456);
        Assert.assertEquals(123456, (int) baseDao.getT());

        BaseDao<User> baseDao2 = new BaseDao<>();
        User user = new User();
        user.setId(1L);
        user.setUsername("admin");
        baseDao2.setT(user);
        Assert.assertEquals(1L, (long) baseDao2.getT().getId());
        Assert.assertEquals("admin", baseDao2.getT().getUsername());
    }
}

创建泛型方法

public class BaseDao {
    
    public <T> T get(T t) {
        return t;
    }
}

编写测试用例

public class BaseDaoTest {

    @Test
    public void setGet() {
        BaseDao baseDao = new BaseDao();

        Integer i = baseDao.get(123456);
        Assert.assertEquals(123456, (int) i);

        String s = baseDao.get("123456");
        Assert.assertEquals("123456", s);

        User user = new User();
        user.setId(1L);
        user.setUsername("admin");
        User u = baseDao.get(user);

        Assert.assertEquals(1L, (long) u.getId());
        Assert.assertEquals("admin", u.getUsername());
    }
}

泛型可以做什么

编译期进行强制类型检查，避免强制类型转换产生的不必要错误，增加程序的健壮性。下面看个例子：

泛型情况下从List集合中获取数据

public class GenericTest {

    @Test
    public void queryFromList() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId((long) i);
            user.setUsername(String.format("test%d", i));
            users.add(user);
        }

        for (User user : users) {
            System.out.printf("id: %d, username:%s\n", user.getId(), user.getUsername());
        }
    }
}

非泛型情况下从List集合中获取数据

public class GenericTest {

    @Test
    public void queryFromList() {

        List users = new ArrayList();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId((long) i);
            user.setUsername(String.format("test%d", i));
            users.add(user);
        }

        for (Object user : users) {
            // User u = (User) user; 错误的强制类型转换并不会在编译器出现任何的提示，只有在程序的运行过程中才会出现java.lang.ClassCastException
            // 下面这个错误的类型转换，可能是Ctrl-c、Ctrl-v程序员最常见的错误
            USer u = (USer) user;
            System.out.printf("id: %d, username:%s\n", u.getId(), u.getUsername());
        }
    }
}

在父类中获取子类的泛型信息

// 1.获取泛型信息的接口
public interface GenericUtil<T> {

    /**
     * 获取泛型信息
     *
     * @return T 对应的具体class信息
     */
    @SuppressWarnings("unchecked")
    default Class<T> getGeneric() {
        Type genericSuperclass = this.getClass().getGenericSuperclass();

        if (genericSuperclass instanceof ParameterizedTypeImpl
                && ((ParameterizedType) genericSuperclass).getRawType().equals(BaseDao.class)) {
            return (Class<T>) ((ParameterizedType) genericSuperclass)
                    .getActualTypeArguments()[0];
        }
        throw new RuntimeException("子类必须继承" + BaseDao.class);
    }
}

// 2.实现GenericUtil接口的基类
public class BaseDao<T> implements GenericUtil<T> {

    public Class<T> genericInfo() {
        return getGeneric();
    }
}

// 3.继承BaseDao的子类
public class UserDao extends BaseDao<User> {
    // some other method...
}

获取子类泛型信息测试用例

public class UserDaoTest {

    @Test
    public void genericInfo() {
        UserDao userDao = new UserDao();
        Class<User> genericInfo = userDao.genericInfo();
        Assert.assertEquals(User.class, genericInfo);
    }
}

通过3、4可以看出泛型的一个应用场景：父类获取子类中的泛型信息，通过解析获取到的泛型信息，可以实现通用的CURD方法。

由此，可以看出泛型在编码过程中对程序员而言是很有帮助的：它能在一定程度上提高我们的工(mo)作(yu)效率。但泛型的作用远不止于此。下文将会结合注解，编写一个基本的BaseDao，来实现一个ORM框架的基本功能。

认识注解

注解是什么？

一种代码级别的注释。可以在源代码级、class文件或者运行时中出现。

如何使用注解

创建一个自定义注解

@Target(ElementType.FIELD) // 标识注解只能出现在类的属性上
@Retention(RetentionPolicy.RUNTIME) // 标识注解可以在程序运行时被获取到
public @interface Column {
    String value();
}

使用注解

public class User {

    /**
     * 自增主键
     */
    private Long id;
    /**
     * 用户名
     */
    @Column("username")
    private String username;
    /**
     * 密码
     */
    @Column("password")
    private String password;
    /**
     * 盐
     */
    @Column("salt")
    private String salt;
    /**
     * 身份证
     */
    @Column("identity_card")
    private String identityCard;
    /**
     * 创建时间
     */
    @Column("create_time")
    private LocalDateTime createTime;
    /**
     * 更新时间
     */
    @Column("update_time")
    private LocalDateTime updateTime;

    // getter、setter...
}

利用反射获取注解信息

public class AnnotationTest {

    @Test
    public void getAnnotationInfo() {

        Class<User> clazz = User.class;
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            Column column = field.getAnnotation(Column.class);
            System.out.println(column);
        }
    }
}

注解可以做什么

拼接SQL语句，下面看个例子：

public class AnnotationTest {

    private static List<Column> getColumnAnnotations(Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();

        List<Column> columns = new ArrayList<>();
        for (Field field : fields) {
            Column column = field.getAnnotation(Column.class);
            if (Objects.nonNull(column)) {
                columns.add(column);
            }
        }
        return columns;
    }

    @Test
    public void generateSQLByAnnotationInfo() {
        List<Column> columnAnnotations = getColumnAnnotations(User.class);
        // 此处我们可以通过自定义一个@Table注解，来获取实体对应的表名称
        String tableName = "user";

        StringBuilder sql = new StringBuilder("select");
        for (Column column : columnAnnotations) {
            sql.append(" ").append(column.value()).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ").append(tableName);

        // generateSQL: select username, password, salt, identity_card, create_time, update_time from user
        System.out.println("generateSQL: " + sql);
    }
}

通过上述例子我们可以看到：注解可以用在实体与表结构之间的映射关系上，通过注解我们可以封装基类，实现通用的CURD方法，更进一步的提高我们的工(mo)作(yu)效率。

从0开始实现一个ORM框架

本章节带领小伙伴们实现基类的封装：利用继承和泛型实现给定实体类的通用CURD方法。

一、自定义注解实现实体与表的映射

与表名称关联的注解

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {

    String value();
}

与主键关联的注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Id {

    String value() default "";
}

与普通列关联的注解
```
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {

    String value();
}
```
通过以上三个注解，我们便可以在对应的实体类中标识与之对应的表名称、表字段和主键信息。

二、创建实体对象关联表结构

创建一个User实体并使用注解映射表结构

@Table("user")
public class User {

    /**
     * 自增主键
     */
    @Id("id")
    private Long id;
    /**
     * 用户名
     */
    @Column("username")
    private String username;
    /**
     * 密码
     */
    @Column("password")
    private String password;
    /**
     * 盐
     */
    @Column("salt")
    private String salt;
    /**
     * 身份证
     */
    @Column("identity_card")
    private String identityCard;
    /**
     * 创建时间
     */
    @Column("create_time")
    private LocalDateTime createTime;
    /**
     * 更新时间
     */
    @Column("update_time")
    private LocalDateTime updateTime;

    // getter、setter...
}

三、创建工具类封装表与实体的对应关系

创建EntityMapper封装表与实体的对应关系

public class EntityMapper {

    private String tableName;

    private String idName;

    private Map<String, String> columnMapper;

    // getter、setter...
}

创建AnnotationUtil解析实体类注解信息

public class AnnotationUtil {

    /**
     * 将给定entity的注解信息，封装到EntityMapper中
     */
    public static EntityMapper parse(Object entity) {
        EntityMapper mapper = new EntityMapper();

        Class<?> clazz = entity.getClass();
        String tableName = parseTableName(clazz);
        String id = parseIdName(clazz);

        Map<String, String> columnMapper = parseColumnName(clazz);
        mapper.setTableName(tableName);
        mapper.setIdName(id);
        mapper.setColumnMapper(columnMapper);

        return mapper;
    }

    private static String parseTableName(Class<?> clazz) {
        Annotation[] clazzAnnotations = clazz.getAnnotations();
        if (clazzAnnotations.length > 0) {
            for (Annotation clazzAnnotation : clazzAnnotations) {
                if (clazzAnnotation instanceof Table) {
                    return ((Table) clazzAnnotation).value();
                }
            }
        }
        return null;
    }

    private static String parseIdName(Class<?> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        if (declaredFields.length > 0) {
            for (Field declaredField : declaredFields) {
                Annotation[] fieldAnnotations = declaredField.getAnnotations();
                if (fieldAnnotations.length > 0) {
                    for (Annotation fieldAnnotation : fieldAnnotations) {
                        if (fieldAnnotation instanceof Id) {
                            return ((Id) fieldAnnotation).value();
                        }
                    }
                }
            }
        }
        return null;
    }

    private static Map<String, String> parseColumnName(Class<?> clazz) {
        Map<String, String> columnMapper = new HashMap<>();

        Field[] declaredFields = clazz.getDeclaredFields();
        if (declaredFields.length > 0) {
            for (Field declaredField : declaredFields) {
                Annotation[] fieldAnnotations = declaredField.getAnnotations();
                if (fieldAnnotations.length > 0) {
                    for (Annotation fieldAnnotation : fieldAnnotations) {
                        if (fieldAnnotation instanceof Column) {
                            String value = ((Column) fieldAnnotation).value();
                            columnMapper.putIfAbsent(declaredField.getName(), value);
                        } else if (fieldAnnotation instanceof Id) {
                            String value = ((Id) fieldAnnotation).value();
                            columnMapper.putIfAbsent(declaredField.getName(), value);
                        }
                    }
                }
            }
        }
        return columnMapper;
    }
}

创建SqlGenerateUtil根据EntityMapper生成SQL语句

public class SqlGenerateUtil {

    public static String generateSelectById(EntityMapper mapper) {
        StringBuilder sql = new StringBuilder("SELECT ");
        Set<Map.Entry<String, String>> entrySet = mapper.getColumnMapper().entrySet();
        for (Map.Entry<String, String> entry : entrySet) {
            sql.append(entry.getValue()).append(", ");
        }
        sql
                .deleteCharAt(sql.length() - 2)
                .append("FROM ").append(mapper.getTableName())
                .append(" ").append("WHERE ")
                .append(mapper.getIdName()).append(" ")
                .append("= ?");
        return sql.toString();
    }

    public static String generateSelectAll(EntityMapper mapper) {
        StringBuilder sql = new StringBuilder("SELECT ");
        Set<Map.Entry<String, String>> entrySet = mapper.getColumnMapper().entrySet();
        for (Map.Entry<String, String> entry : entrySet) {
            sql.append(entry.getValue()).append(", ");
        }
        sql
                .deleteCharAt(sql.length() - 2)
                .append("FROM ").append(mapper.getTableName());
        return sql.toString();
    }

    public static String generateInsert(EntityMapper mapper) {
        StringBuilder sql = new StringBuilder("INSERT INTO ")
                .append(mapper.getTableName()).append(" ").append("( ");

        Set<Map.Entry<String, String>> entrySet = mapper.getColumnMapper().entrySet();
        for (Map.Entry<String, String> entry : entrySet) {
            sql.append(entry.getValue()).append(", ");
        }
        sql.deleteCharAt(sql.length() - 2).append(") ").append("VALUES ( ");
        for (Map.Entry<String, String> entry : entrySet) {
            sql.append("?").append(", ");
        }
        sql.deleteCharAt(sql.length() - 2).append(")");
        return sql.toString();
    }

    public static String generateUpdate(EntityMapper mapper) {
        StringBuilder sql = new StringBuilder("UPDATE ")
                .append(mapper.getTableName()).append(" SET ");

        Set<Map.Entry<String, String>> entrySet = mapper.getColumnMapper().entrySet();
        for (Map.Entry<String, String> entry : entrySet) {
            if (mapper.getIdName().equals(entry.getValue())) {
                continue;
            }
            sql.append(entry.getValue()).append("=?, ");
        }
        sql
                .deleteCharAt(sql.length() - 2)
                .append("WHERE ").append(mapper.getIdName()).append("=?");
        return sql.toString();
    }

    public static String generateDelete(EntityMapper mapper) {
        StringBuilder sql = new StringBuilder("DELETE FROM ")
                .append(mapper.getTableName()).append(" WHERE ")
                .append(mapper.getIdName()).append("=?");
        return sql.toString();
    }
}

编写测试类

public class EntityAnnotationTest {

    @Test
    public void generateSelectAll() {
        User user = new User();
        EntityMapper entityMapper = AnnotationUtil.parse(user);
        String sql = SqlGenerateUtil.generateSelectAll(entityMapper);

        System.out.println(sql);
    }

    @Test
    public void generateSelectById() {
        User user = new User();
        EntityMapper entityMapper = AnnotationUtil.parse(user);
        String sql = SqlGenerateUtil.generateSelectById(entityMapper);

        System.out.println(sql);
    }
    
    // generateInsert、generateUpdate、generateDelete...
}

通过如上步骤，便可以根据一个给定的实体类，实现自动生成CURD通用SQL。

四、创建工具类获取数据库连接

创建DBUtil从外部读取数据库配置，并生成连接

public class DBUtil {

    private static final String DEFAULT_PROPERTIES_PATH = "jdbc/db.properties";
    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;

    static {
        try (InputStream in = DBUtil.class.getClassLoader().getResourceAsStream((DEFAULT_PROPERTIES_PATH))) {
            Properties p = new Properties();
            p.load(in);
            URL = p.getProperty("mysql.url").trim();
            USERNAME = p.getProperty("mysql.username").trim();
            PASSWORD = p.getProperty("mysql.password").trim();
        } catch (IOException e) {
            throw new RuntimeException("[" + DEFAULT_PROPERTIES_PATH + "]读取失败.");
        }
    }

    private DBUtil() {
        throw new RuntimeException("can not instance.");
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    public static void closeConnection(Connection c) throws SQLException {
        if (c != null) {
            c.close();
        }
    }

    public static void closeConnection(Connection c, Statement s) throws SQLException {
        if (s != null) {
            s.close();
        }
        if (c != null) {
            c.close();
        }
    }

    public static void closeConnection(Statement s, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (s != null) {
            s.close();
        }
    }

    public static void closeConnection(Connection c, Statement s, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (s != null) {
            s.close();
        }
        if (c != null) {
            c.close();
        }
    }
}

创建db.properties配置文件

# path: classpath:jdbc/db.properties
mysql.url=jdbc:mysql://127.0.0.1:3306/demo
mysql.username=demo
mysql.password=123456

编写测试用例

public class DBUtilTest {

    @Test
    public void getConnection() throws SQLException {
        Connection connection = DBUtil.getConnection();
        Assert.assertNotNull(connection);
    }
}

如上步骤，我们便可以在代码之外，动态切换数据库配置。

五、创建工具类优化获取数据库连接

创建ConnectionManager，利用ThreadLocal来保证事务

public class ConnectionManager {

    private static final ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<>();

    public static Connection getConnection() {
        return connectionThreadLocal.get();
    }

    public static void setConnection(Connection connection) {
        connectionThreadLocal.set(connection);
    }

    public static void removeConnection() {
        connectionThreadLocal.remove();
    }
}

创建DataSourceUtil控制连接的获取和关闭

public class DataSourceUtil {

    public static Connection getConnection() {
        Connection connection = ConnectionManager.getConnection();
        if (connection == null) {
            try {
                connection = DBUtil.getConnection();
                if (connection == null) {
                    throw new RuntimeException("connect is null.");
                }
                ConnectionManager.setConnection(connection);
                return connection;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return connection;
    }

    public static void closeConnection() {
        try {
            DBUtil.closeConnection(getConnection());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            ConnectionManager.removeConnection();
        }
    }
}

编写测试类，验证同一个方法内多次获取的连接是否相同

public class DataSourceUtilTest {

    @Test
    public void getConnection() {
        Connection connection1 = DataSourceUtil.getConnection();
        Connection connection2 = DataSourceUtil.getConnection();

        Assert.assertEquals(connection2, connection1);
        // todo commit or rollback...
    }
}

六、优化工具类，父类方法获取子类泛型对象

优化GenericUtil接口，根据子类泛型信息实例化对象

public interface GenericUtil<T> {

    // ...

    default T getInstance() {
        Class<T> clazz = getGeneric();

        T t = null;
        Constructor<?> constructor = Arrays.stream(clazz.getConstructors())
                .filter(c -> c.getParameters().length == 0)
                .findFirst()
                .orElseThrow(() -> new RuntimeException(clazz.getSimpleName() + "没有可用的无参构造器"));
        try {
            Object o = constructor.newInstance();
            t = clazz.cast(o);
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return t;
    }
}

编写测试用例

public class GenericUtilTest {

    @Test
    public void getInstance() {
        // UserDao extends BaseDao<User>
        UserDao userDao = new UserDao();
        User instance = userDao.getInstance();
        Assert.assertEquals(User.class, instance.getClass());
    }
}

如上，当我们创建一个XxxDao<T>并继承了BaseDao之后，BaseDao便拥有了实例化T的能力。

七、利用上述工具封装基类

重写BaseDao，实现通用的CURD方法

public class BaseDao<T> implements GenericUtil<T> {

    public T selectById(Long id) {
        T instance = this.getInstance();
        EntityMapper mapper = AnnotationUtil.parse(instance);
        String sql = SqlGenerateUtil.generateSelectById(mapper);

        try {
            PreparedStatement prepareStatement = DataSourceUtil.getConnection().prepareStatement(sql);
            prepareStatement.setObject(1, id);
            ResultSet resultSet = prepareStatement.executeQuery();

            int count = resultSet.getMetaData().getColumnCount();
            Map<String, String> columnMapper = mapper.getColumnMapper();
            while (resultSet.next()) {
                for (int i = 1; i <= count; i++) {
                    String columnName = resultSet.getMetaData().getColumnName(i);
                    for (Map.Entry<String, String> entry : columnMapper.entrySet()) {
                        if (columnName.equals(entry.getValue())) {
                            Field declaredField = instance.getClass().getDeclaredField(entry.getKey());
                            declaredField.setAccessible(true);
                            declaredField.set(instance, resultSet.getObject(i));
                        }
                    }
                }
                return instance;
            }
        } catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<T> selectAll() {
        T instance = this.getInstance();

        EntityMapper mapper = AnnotationUtil.parse(instance);
        String sql = SqlGenerateUtil.generateSelectAll(mapper);
        List<T> list = new ArrayList<>();
        try {
            PreparedStatement prepareStatement = DataSourceUtil.getConnection().prepareStatement(sql);
            ResultSet resultSet = prepareStatement.executeQuery();

            int count = resultSet.getMetaData().getColumnCount();
            Map<String, String> columnMapper = mapper.getColumnMapper();
            while (resultSet.next()) {
                for (int i = 1; i <= count; i++) {
                    String columnName = resultSet.getMetaData().getColumnName(i);
                    for (Map.Entry<String, String> entry : columnMapper.entrySet()) {
                        if (columnName.equals(entry.getValue())) {
                            Field declaredField = instance.getClass().getDeclaredField(entry.getKey());
                            declaredField.setAccessible(true);
                            declaredField.set(instance, resultSet.getObject(i));
                        }
                    }
                }
                list.add(instance);
                instance = this.getInstance();
            }
        } catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int insert(T t) {
        T instance = this.getInstance();
        EntityMapper mapper = AnnotationUtil.parse(instance);
        String sql = SqlGenerateUtil.generateInsert(mapper);

        try {
            PreparedStatement prepareStatement = DataSourceUtil.getConnection().prepareStatement(sql);
            Map<String, String> columnMapper = mapper.getColumnMapper();
            int i = 1;
            for (Map.Entry<String, String> entry : columnMapper.entrySet()) {
                Field declaredField = instance.getClass().getDeclaredField(entry.getKey());
                declaredField.setAccessible(true);
                prepareStatement.setObject(i++, declaredField.get(t));
            }

            return prepareStatement.executeUpdate();
        } catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int update(T t) {
        T instance = this.getInstance();
        EntityMapper mapper = AnnotationUtil.parse(instance);
        String sql = SqlGenerateUtil.generateUpdate(mapper);

        try {
            PreparedStatement prepareStatement = DataSourceUtil.getConnection().prepareStatement(sql);
            Map<String, String> columnMapper = mapper.getColumnMapper();
            int i = 1;
            Object idValue = null;
            for (Map.Entry<String, String> entry : columnMapper.entrySet()) {
                Field declaredField = instance.getClass().getDeclaredField(entry.getKey());
                declaredField.setAccessible(true);
                if (mapper.getIdName().equals(entry.getValue())) {
                    idValue = declaredField.get(t);
                    continue;
                }
                prepareStatement.setObject(i++, declaredField.get(t));
            }
            prepareStatement.setObject(i, idValue);

            return prepareStatement.executeUpdate();
        } catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int delete(Object id) {
        T instance = this.getInstance();
        EntityMapper mapper = AnnotationUtil.parse(instance);
        String sql = SqlGenerateUtil.generateDelete(mapper);

        try {
            PreparedStatement prepareStatement = DataSourceUtil.getConnection().prepareStatement(sql);
            prepareStatement.setObject(1, id);

            return prepareStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

编写测试类

public class BaseDaoTest {

    UserDao userDao = new UserDao();

    @Test
    public void selectById() {
        User user = userDao.selectById(1L);
        Assert.assertNotNull(user);
    }

    @Test
    public void selectAll() {
        List<User> users = userDao.selectAll();
        Assert.assertNotNull(users);
    }

    @Test
    public void insert() {
        User user = new User();
        user.setId(100L);
        user.setUsername("zhangsan");
        user.setPassword("123456");

        int rows = userDao.insert(user);
        Assert.assertTrue(rows > 0);
    }

    @Test
    public void update() {
        User user = new User();
        user.setId(100L);
        user.setUsername("zhangsan123");

        int rows = userDao.update(user);
        Assert.assertTrue(rows > 0);
    }

    @Test
    public void delete() {
        int rows = userDao.delete(100L);
        Assert.assertTrue(rows > 0);
    }
}

至此，我们便完成了一个ORM框架的基本骨架。

附录

项目用到的SQL文件

创建数据库

-- 创建数据库
create database demo character set utf8;
-- 创建用户
create user demo@'%' identified by '123456';
-- 授权
grant all privileges on demo.* to demo@'%';
grant process on *.* to demo@'%';

建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`
(
    `id`            bigint NOT NULL COMMENT '自增主键',
    `username`      varchar(50)  DEFAULT NULL COMMENT '用户名',
    `password`      varbinary(128) DEFAULT NULL COMMENT '密码',
    `salt`          varchar(255) DEFAULT NULL COMMENT '盐',
    `identity_card` varchar(18)  DEFAULT NULL COMMENT '身份证',
    `create_time`   datetime     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`   datetime     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

标签：手工,String,框架,class,ORM,user,sql,public,append
From： https://www.cnblogs.com/wangwz2019/p/17000631.html

手工实现一个ORM小框架

手工实现一个ORM框架

目的

本项目所需前置知识

相关知识简介

认识JDBC

如何使用JDBC操作数据库

认识泛型

如何使用泛型

认识注解

如何使用注解

从0开始实现一个ORM框架

一、自定义注解实现实体与表的映射

二、创建实体对象关联表结构

三、创建工具类封装表与实体的对应关系

四、创建工具类获取数据库连接

五、创建工具类优化获取数据库连接

六、优化工具类，父类方法获取子类泛型对象

七、利用上述工具封装基类

附录

项目用到的SQL文件

相关文章

赞助商

阅读排行