How easy it is to implement an ORM

Table of Contents

  • How easy it is to implement an ORM
  • The principle of
  • The ORM implementation
    • 1. Use annotations to associate Java beans with database fields
    • 2. Reflection utility classes
    • 3. Simple Model examples
    • 4. Annotation analysis
    • 5. Perform database operations
    • 6. Combine reflection to implement query operations
  • Use dynamic proxy to implement @query@select similar functionality
    • 1. Dynamic proxy
    • 2. The annotation
    • 3. Table design
    • 4. model
    • 5. repository
    • 7. General process
    • 8. Proxy use
    • 9. Put the build agent into the Spring IOC container
    • 10. Invoke method processing

In this article, we use annotations, reflection, dynamic proxies, regex, and Jexl3 expressions to implement an ORM. In most frameworks, we use these things simply, rather than analyzing principles

The principle of

In the ORM framework I use, I can manipulate the storage of data like objects, how to achieve this, we know that the database is aware of SQL statements, but not Java beans! At the same time, when we use ORM, we need to define our beans according to the ORM framework. Why?

This is because the ORM provides us with the ability to convert an object operation into a corresponding SQL statement, such as save(bean), which needs to be converted into an INSERT statement and update(bean) which needs to be converted into the corresponding UPDATE statement

The usual insert statement format is

insert intoTable name (column name)values(value)Copy the code

The update statement for

Update the table namesetThe column name= 值 where id = 值
Copy the code

As the above format shows, if we can derive table names and column names from objects, we can also write a simple ORM framework

The ORM implementation

1. Use annotations to associate Java beans with database fields

The last article mentioned annotations and the way to customize annotations and parse them. With annotations, you can create a simple ORM

To operate on the database, we must know the name of the data table and the names and types of the fields in the table. Just as Hibernate uses annotations to identify the mapping between the model and the database, I use the Java Persistence API

Note that

annotations role Directions for use
@Entity Mark this as an entity Annotation on a class to indicate that the class can be processed by ORM
@Table Marks the table corresponding to the entity Labeled on a class to indicate the database identifier for that class
@Id Mark the field as id Mark a field with an ID
@Column Marks the column information corresponding to this field The mark on a field indicates the corresponding column information, mainly the column name

A field property table is used to store the mapping between fields and columns in a data table in an object

package com.zyndev.tool.fastsql.core;

import lombok.Data;

import javax.persistence.GenerationType;

/**
 * The type Db column info.
 *
 * @authorYu 'nan Chang [email protected] *@version0.0.1 * /
@Data
class DBColumnInfo {

    /** * (Optional) The primary key generation strategy * that the persistence provider must use to * generate the annotated entity primary key. */
    private GenerationType strategy = GenerationType.AUTO;

    private String fieldName;

    /** * The name of the column */
    private String columnName;

    /** * Whether the column is a unique key. */
    private boolean unique;

    /** * Whether the database column is nullable. */
    private boolean nullable = true;

    /** * Whether the column is included in SQL INSERT */
    private boolean insertAble = true;

    /** * Whether the column is included in SQL UPDATE */
    private boolean updatable = true;

    /** * The SQL fragment that is used when * generating the DDL for the column. */
    private String columnDefinition;

    /** * The name of the table that contains the column. * If absent the column is assumed to be in the primary table. */
    private String table;

    /** * (Optional) The column length. (Applies only if a * string-valued column is used.) */
    private int length =  255;

    private boolean id = false;

}
Copy the code

2. Reflection utility classes

Provides some common reflection operations

Reflection allows us to dynamically obtain information about all the member variables of a class, and to value or assign them at the same time

package com.zyndev.tool.fastsql.util;


import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * The type Bean reflection util.
 *
 * @author yunan.zhang [email protected]
 * @version 0.0.3
 * @dateDecember 26, 2017 16:36 */
public class BeanReflectionUtil {

    public static Object getPrivatePropertyValue(Object obj,String propertyName)throws Exception{
        Class cls=obj.getClass();
        Field field=cls.getDeclaredField(propertyName);
        field.setAccessible(true);
        Object retvalue=field.get(obj);
        return retvalue;
    }

    /** * Gets static field value. */
    public static Object getStaticFieldValue(String className, String fieldName) throws Exception {
        Class cls = Class.forName(className);
        Field field = cls.getField(fieldName);
        return field.get(cls);
    }

    /** * Gets field value. */
    public static Object getFieldValue(Object obj, String fieldName) throws Exception {
        Class cls = obj.getClass();
        Field field = cls.getDeclaredField(fieldName);
        field.setAccessible(true);
        return field.get(obj);
    }

    /** * Invoke method object. */
    public Object invokeMethod(Object owner, String methodName, Object[] args) throws Exception {
        Class cls = owner.getClass();
        Class[] argclass = new Class[args.length];
        for (int i = 0, j = argclass.length; i < j; i++) {
            argclass[i] = args[i].getClass();
        }
        @SuppressWarnings("unchecked")
        Method method = cls.getMethod(methodName, argclass);
        return method.invoke(owner, args);
    }

    /** * Invoke static method object. */
    public Object invokeStaticMethod(String className, String methodName, Object[] args) throws Exception {
        Class cls = Class.forName(className);
        Class[] argClass = new Class[args.length];
        for (int i = 0, j = argClass.length; i < j; i++) {
            argClass[i] = args[i].getClass();
        }
        @SuppressWarnings("unchecked")
        Method method = cls.getMethod(methodName, argClass);
        return method.invoke(null, args);
    }

    /** * New instance object. */
    public static Object newInstance(String className) throws Exception {
        Class clazz = Class.forName(className);
        @SuppressWarnings("unchecked")
        java.lang.reflect.Constructor cons = clazz.getConstructor();
        return cons.newInstance();
    }

    /** * New instance object. */
    public static Object newInstance(Class clazz) throws Exception {
        @SuppressWarnings("unchecked")
        java.lang.reflect.Constructor cons = clazz.getConstructor();
        return cons.newInstance();
    }

    /** * Get bean declared fields field [ ]. */
    public static Field[] getBeanDeclaredFields(String className) throws ClassNotFoundException {
        Class clazz = Class.forName(className);
        return clazz.getDeclaredFields();
    }

    /** * Get bean declared methods method [ ]. */
    public static Method[] getBeanDeclaredMethods(String className) throws ClassNotFoundException {
        Class clazz = Class.forName(className);
        return clazz.getMethods();
    }

    /** * Copy fields. */
    public static void copyFields(Object source, Object target) {
        try {
            List<Field> list = new ArrayList<>();
            Field[] sourceFields = getBeanDeclaredFields(source.getClass().getName());
            Field[] targetFields = getBeanDeclaredFields(target.getClass().getName());
            Map<String, Field> map = new HashMap<>(targetFields.length);
            for (Field field : targetFields) {
                map.put(field.getName(), field);
            }
            for (Field field : sourceFields) {
                if(map.get(field.getName()) ! =null) { list.add(field); }}for (Field field : list) {
                Field tg = map.get(field.getName());
                tg.setAccessible(true); tg.set(target, getFieldValue(source, field.getName())); }}catch(Exception e) { e.printStackTrace(); }}}Copy the code

3. Simple Model examples

package com.zyndev.tool.fastsql;

import javax.persistence.*;

/ * * *@authorYu 'nan Chang [email protected] *@date2017/11/30 11:21 PM */
@Data
@Entity
@Table(name = "tb_student")
public class Student {

    @Id
    @Column
    private Integer id;

    @Column
    private String name;

    @Column(updatable = true, insertable = true, nullable = false)
    private Integer age;

}
Copy the code

4. Annotation analysis

Parse the annotations on the object to get the corresponding relationship

package com.zyndev.tool.fastsql.core;


import com.zyndev.tool.fastsql.util.StringUtil;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/** * The type Annotation parser. * <p> * Annotation parser **@author yunan.zhang [email protected]
 * @version 0.0.3
 * @sinceThe 2017-12-26 16:59:07 * /
public class AnnotationParser {

    /** * The relationship between the storage class name and the database table name ** The use of three caches mainly to reduce the number of reflection, improve efficiency */
    private final static Map<String, String> tableNameCache = new HashMap<>(30);

    /** * The relationship between the storage class name and the database column */
    private final static Map<String, String> tableAllColumnNameCache = new HashMap<>(30);

    /** * The relationship between the storage class name and the corresponding database column name */
    private final static Map<String, List<DBColumnInfo>> tableAllDBColumnCache = new HashMap<>(30);

    /** * Gets table name@TableAnnotation, if there is an annotated name, if name is empty, use the class name as the table name * if not@TableReturns null * *@param <E>    the type parameter
     * @param entity the entity
     * @return the table name
     */
    public static <E> String getTableName(E entity) {
        String tableName = tableNameCache.get(entity.getClass().getName());
        if (tableName == null) {
            Table table = entity.getClass().getAnnotation(Table.class);
            if(table ! =null && StringUtil.isNotBlank(table.name())) {
                tableName = table.name();
            } else {
                tableName = entity.getClass().getSimpleName();
            }
            tableNameCache.put(entity.getClass().getName(), tableName);
        }
        return tableName;
    }

    /** * Gets all db column info. */
    public static <E> List<DBColumnInfo> getAllDBColumnInfo(E entity) {
        List<DBColumnInfo> dbColumnInfoList = tableAllDBColumnCache.get(entity.getClass().getName());
        if (dbColumnInfoList == null) {
            dbColumnInfoList = new ArrayList<>();
            DBColumnInfo dbColumnInfo;
            Field[] fields = entity.getClass().getDeclaredFields();
            for (Field field : fields) {
                Column column = field.getAnnotation(Column.class);
                if(column ! =null) {
                    dbColumnInfo = new DBColumnInfo();
                    if (StringUtil.isBlank(column.name())) {
                        dbColumnInfo.setColumnName(field.getName());
                    } else {
                        dbColumnInfo.setColumnName(column.name());
                    }
                    if (null! = field.getAnnotation(Id.class)) { dbColumnInfo.setId(true);
                    }
                    dbColumnInfo.setFieldName(field.getName());
                    dbColumnInfoList.add(dbColumnInfo);
                }
            }
            tableAllDBColumnCache.put(entity.getClass().getName(), dbColumnInfoList);
        }
        return dbColumnInfoList;
    }

    /** * Return column1,column2,column3 **@param <E>    the type parameter
     * @param entity the entity
     * @return string
     */
    public static <E> String getTableAllColumn(E entity) {

        String allColumn = tableAllColumnNameCache.get(entity.getClass().getName());
        if (allColumn == null) {
            List<DBColumnInfo> dbColumnInfoList = getAllDBColumnInfo(entity);
            StringBuilder allColumnsInfo = new StringBuilder();
            int i = 1;
            for (DBColumnInfo dbColumnInfo : dbColumnInfoList) {
                allColumnsInfo.append(dbColumnInfo.getColumnName());
                if(i ! = dbColumnInfoList.size()) { allColumnsInfo.append(",");
                }
                i++;
            }
            allColumn = allColumnsInfo.toString();
            tableAllColumnNameCache.put(entity.getClass().getName(), allColumn);
        }
        returnallColumn; }}Copy the code

5. Perform database operations

The database interaction uses the JdbcTemplate provided by Spring, instead of writing your own DBUtil

6. Combine reflection to implement query operations

Save an Entity

The save operation is relatively simple, and the focus here is on converting the Entity into an INSERT statement

/**
 * Save int.
 * @param entity the entity
 * @return the int
 */
@Override
public int save(Object entity) {
    try {
        String tableName = AnnotationParser.getTableName(entity);
        StringBuilder property = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<Object> propertyValue = new ArrayList<>();
        List<DBColumnInfo> dbColumnInfoList = AnnotationParser.getAllDBColumnInfo(entity);

        for (DBColumnInfo dbColumnInfo : dbColumnInfoList) {
            if(dbColumnInfo.isId() || ! dbColumnInfo.isInsertAble()) {continue;
            }
            / / not null
            Object o = BeanReflectionUtil.getFieldValue(entity, dbColumnInfo.getFieldName());
            if(o ! =null) {
                property.append(",").append(dbColumnInfo.getColumnName());
                value.append(",").append("?");
                propertyValue.add(o);
            }
        }
        String sql = "insert into " + tableName + "(" + property.toString().substring(1) + ") values(" + value.toString().substring(1) + ")";
        return this.getJdbcTemplate().update(sql, propertyValue.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }
    return 0;
}

Copy the code

The update operation

The update operation takes one more step to determine the WHERE statement than the save operation

/**
 * Update int.
 *
 * @param entity     the entity
 * @param ignoreNull the ignore null
 * @param columns    the columns
 * @return the int
 */
@Override
public int update(Object entity, boolean ignoreNull, String... columns) {
    try {
        String tableName = AnnotationParser.getTableName(entity);
        StringBuilder property = new StringBuilder();
        StringBuilder where = new StringBuilder();
        List<Object> propertyValue = new ArrayList<>();
        List<Object> wherePropertyValue = new ArrayList<>();
        List<DBColumnInfo> dbColumnInfos = AnnotationParser.getAllDBColumnInfo(entity);
        for (DBColumnInfo dbColumnInfo : dbColumnInfos) {

            Object o = BeanReflectionUtil.getFieldValue(entity, dbColumnInfo.getFieldName());
            if (dbColumnInfo.isId()) {
                where.append(" and ").append(dbColumnInfo.getColumnName()).append("=? ");
                wherePropertyValue.add(o);
            } else if(ignoreNull || o ! =null) {
                property.append(",").append(dbColumnInfo.getColumnName()).append("=?"); propertyValue.add(o); }}if (wherePropertyValue.isEmpty()) {
            throw new IllegalArgumentException("Update table [" + tableName + "] could not find id, request data:" + entity);
        }

        String sql = "update " + tableName + " set " + property.toString().substring(1) + " where " + where.toString().substring(5);
        propertyValue.addAll(wherePropertyValue);
        return this.getJdbcTemplate().update(sql, propertyValue.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }
    return 0;
}
Copy the code

Delete operation

This is simpler than update

/** * Delete int. * <p> Delete data according to id </p> ** @param entity the entity * @return the int */ @override public int delete(Object entity) { try { String tableName = AnnotationParser.getTableName(entity); StringBuilder where = new StringBuilder(" 1=1 "); List<Object> whereValue = new ArrayList<>(5); List<DBColumnInfo> dbColumnInfos = AnnotationParser.getAllDBColumnInfo(entity); for (DBColumnInfo dbColumnInfo : dbColumnInfos) { if (dbColumnInfo.isId()) { Object o = BeanReflectionUtil.getFieldValue(entity, dbColumnInfo.getFieldName()); if (null ! = o) { whereValue.add(o); } where.append(" and `").append(dbColumnInfo.getColumnName()).append("` = ? "); }} if (whereValue. Size () == 0) {throw new IllegalStateException("delete "+ tableName +" id has no corresponding value, cannot delete "); } String sql = "delete from " + tableName + " where " + where.toString(); return this.getJdbcTemplate().update(sql, whereValue); } catch (Exception e) { e.printStackTrace(); } return 0; }Copy the code

Through the above example, it is easy to implement an ORM, in order to better use, we also need to provide our own solution to write SQL

Use dynamic proxy to implement @query@select similar functionality

1. Dynamic proxy

The JDK based dynamic proxy implementation is used directly here

2. The annotation

@query and @param are not available in the Java Persistence API. Let’s customize these two annotations and define a ReturnGeneratedKey annotation to enable Query to return a primary key

Query.java

package com.zyndev.tool.fastsql.annotation;

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

/** * query operation SQL **@authorYu 'nan Chang [email protected] *@version 0.0.1
 * @since  2017/12/22 17:26
 */
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Query {

    /** * SQL statement */
    String value(a);
}
Copy the code

Param.java

package com.zyndev.tool.fastsql.annotation;

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

/ * * * *@authorYu 'nan Chang [email protected] *@version 0.0.1
 * @since2017/12/22 17:29 * /
@Target( { ElementType.PARAMETER })
@Retention(RetentionPolicy.RUNTIME)
public @interface Param {

    /** * specifies which parameter in the SQL statement this value belongs to, using the named parameter */
    String value(a);
}

Copy the code

ReturnGeneratedKey.java

package com.zyndev.tool.fastsql.annotation;


import java.lang.annotation.*;


/** * returns the primary key **@authorYu 'nan Chang [email protected] *@version0.0.3 * * /
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ReturnGeneratedKey {
}
Copy the code

3. Table design

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(40) DEFAULT NULL,
  `account_name` varchar(40) DEFAULT NULL,
  `nick_name` varchar(23) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  `phone` varchar(16) DEFAULT NULL,
  `register_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
Copy the code

4. model

Here is an example using user.java:

User.java

package com.zyndev.tool.fastsql.repository;

import lombok.*;

import java.io.Serializable;
import java.util.Date;

/ * * *@authorYu 'nan Chang [email protected] *@version 1.0
 * @dateThe 2017-12-27 15:04:13 * /
@Data
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String uid;

    private String accountName;

    private String nickName;

    private String password;

    private String phone;

    private Date registerTime;

    private Date updateTime;

}
Copy the code

5. repository

package com.zyndev.tool.fastsql.repository;

import com.zyndev.tool.fastsql.annotation.Param;
import com.zyndev.tool.fastsql.annotation.Query;
import com.zyndev.tool.fastsql.annotation.ReturnGeneratedKey;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/** ** ** *@version 1.0
 * @authorYu 'nan Chang [email protected] *@date 2017 /12/22 18:13
 */
@Repository
public interface UserRepository {

    @Query("select count(*) from tb_user")
    public Integer getCount(a);

    @Query("delete from tb_user where id = ? 1)"
    public Boolean deleteById(int id);

    @Query("select count(*) from tb_user where password = ? 1)"
    public int getCountByPassword(@Param("password") String password);

    @Query("select uid from tb_user where password = ? 1)"
    public String getUidByPassword(@Param("password") String password);

    @Query("select * from tb_user where id = :id ")
    public User getUserById(@Param("id") Integer id);

    @Query("select * " + " from tb_user " + " where account_name = :accountName ")
    public List<User> getUserByAccountName(@Param("accountName") String accountName);

    @Query("insert into tb_user(id, account_name, password, uid, nick_name, register_time, update_time) " + "values(:id, :user.accountName, :user.password, :user.uid, :user.nickName, :user.registerTime, :user.updateTime )")
    public int saveUser(@Param("id") Integer id, @Param("user") User user);

    @ReturnGeneratedKey
    @Query("insert into tb_user(account_name, password, uid, nick_name, register_time, update_time) " + "values(:user.accountName, :user.password, :user.uid, :user.nickName, :user.registerTime, :user.updateTime )")
    public int saveUser(@Param("user") User user);
}
Copy the code

7. General process

We have done some preparatory work above, including:

  1. Definition of annotations
  2. The design of the table
  3. The design of the model
  4. The design of the Repository

Now, how do we put this all together

General process:

  1. Generate an agent for Repository
  2. Put the build agent into the Spring IOC container
  3. When a proxy method is called, the method’s@Query , @Param.@ReturnGeneratedKeyAnnotation and get the return value of the method
  4. Rewrite the SQL of Query and execute the encapsulation of the SQL return result set based on the return type of the method

8. Proxy use

FacadeProxy.java

Generate a proxy for Repository and call the invoke method when the proxy method executes. The invoke logic is written to StatementParser.java to prevent the class from becoming too powerful

package com.zyndev.tool.fastsql.core;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;

/** * Generate proxy **@authorYu 'nan Chang [email protected] *@version 0.0.1
 * @since  2017 /12/23 上午12:40
 */
@SuppressWarnings("unchecked")
public class FacadeProxy implements InvocationHandler {

    private final static Log logger = LogFactory.getLog(FacadeProxy.class);

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        return StatementParser.invoke(proxy, method, args);
    }

    /** * New mapper proxy t. */
    protected static <T> T newMapperProxy(Class<T> mapperInterface) {
        logger.info("Generate proxy:"+ mapperInterface.getName()); ClassLoader classLoader = mapperInterface.getClassLoader(); Class<? >[] interfaces =new Class[]{mapperInterface};
        FacadeProxy proxy = new FacadeProxy();
        return(T) Proxy.newProxyInstance(classLoader, interfaces, proxy); }}Copy the code

9. Put the build agent into the Spring IOC container

BeanFactoryAware is used here. There will be a separate article about this part, which will not be explained in detail here

package com.zyndev.tool.fastsql.core;

import com.zyndev.tool.fastsql.util.ClassScanner;
import com.zyndev.tool.fastsql.util.StringUtil;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;
import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.context.annotation.ImportBeanDefinitionRegistrar;
import org.springframework.core.type.AnnotationMetadata;
import org.springframework.stereotype.Repository;

import java.io.IOException;
import java.util.Set;

/**
 * The type Fast sql repository registrar.
 *
 * @authorYu 'nan Chang [email protected] *@version 1.0
 * @date2018/2/23 if then * /
public class FastSqlRepositoryRegistrar implements ImportBeanDefinitionRegistrar.BeanFactoryAware {

    private ConfigurableListableBeanFactory beanFactory;

    @Override
    public void registerBeanDefinitions(AnnotationMetadata annotationMetadata, BeanDefinitionRegistry beanDefinitionRegistry) {
        System.out.println("FastSqlRepositoryRegistrar registerBeanDefinitions ");
        String basePackage = "com.sparrow";
        ClassScanner classScanner = newClassScanner(); Set<Class<? >> classSet =null;
        try {
            classSet = classScanner.getPackageAllClasses(basePackage, true);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        for (Class clazz : classSet) {
            if(clazz.getAnnotation(Repository.class) ! =null) { beanFactory.registerSingleton(StringUtil.firstCharToLowerCase(clazz.getSimpleName()), FacadeProxy.newMapperProxy(clazz)); }}}@Override
    public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
        this.beanFactory = (ConfigurableListableBeanFactory) beanFactory; }}Copy the code

10. Invoke method processing

When generating the dynamic proxy earlier, you can see that all invoke logic is handled by StatementParser.java, which is also a heavyweight method

Invoke execution process description:

invoke(Object proxy, Method method, Object[] args)

  1. Gets the return type of the method
  2. Get the ** @query ** annotation of the method to get the one that needs to be executedsqlStatement, can not get the SQL throw exception
  3. Obtain the parameters of the method, and the parameter sequence corresponds to? 1->arg0 ? 2->arg1 …
  4. Get method parameters and parameters on@ParamAnnotate and associate the parameter with the name of the corresponding Param: param1->arg0 password->arg1
  5. To determine whether SQL is SELECT or something else, use re(? i)select([\\s\\S]*?)
  6. Rewrite the SQL
  7. If it is not a SELECT statement, check whether it is@ReturnGeneratedKeyannotations
  8. If there is no@ReturnGeneratedKeyExecute the statement directly and return the corresponding result
  9. If you have a@ReturnGeneratedKeyAnd an INSERT statement returns the generated primary key
  10. If it is a SELECT statement, the SELECT statement is executed and the result set is wrapped based on the return type of the method

About rewriting SQL

@Query("insert into tb_user(id, account_name, password, uid, nick_name, register_time, update_time) values( :id, :user.accountName, :user.password, :user.uid, :user.nickName, :user.registerTime, :user.updateTime )")
    public int saveUser(@Param("id") Integer id, @Param("user") User user); 
Copy the code

Get the SQL first

insert into tb_user(id, account_name, password, uid, nick_name, register_time, update_time)
values(:id, :user.accountName, :user.password, :user.uid, :user.nickName, :user.registerTime, :user.updateTime )
Copy the code

As you can see, this is not standard SQL or JDBC-recognized SQL, so we use re **? \d+(.[A-Za-z]+)? |:[A-Za-z0-9]+(.[A-Za-z]+)? 支那

To extract? 1? 2 :1 :2 :id :user.accountName special flag and replace it with?

You didn’t replace any of them? Then record the corresponding? The value represented by

The SQL after the replacement is

insert into tb_user(id, account_name, password, uid, nick_name, register_time, update_time)
values(? ,? ,? ,? ,? ,? ,?)Copy the code

This SQL can be processed by JDBC and the parameters can be:

:id, :user.accountName, :user.password, :user.uid, :user.nickName, :user.registerTime, :user.updateTime
Copy the code

The id can be obtained directly from the parameter ID, while :user.accountName needs to be reflected from the parameter :user (user) to complete the rewriting of the SQL

Return result set encapsulation can be done by reflection, which can be seen directly in the following code

StatementParser.java

package com.zyndev.tool.fastsql.core;

import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import com.zyndev.tool.fastsql.annotation.Param;
import com.zyndev.tool.fastsql.annotation.Query;
import com.zyndev.tool.fastsql.annotation.ReturnGeneratedKey;
import com.zyndev.tool.fastsql.convert.BeanConvert;
import com.zyndev.tool.fastsql.convert.ListConvert;
import com.zyndev.tool.fastsql.convert.SetConvert;
import com.zyndev.tool.fastsql.util.BeanReflectionUtil;
import com.zyndev.tool.fastsql.util.StringUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import sun.reflect.generics.reflectiveObjects.NotImplementedException;

import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/** * select count(*) from tb_user@authorYu 'nan Chang [email protected] *@version 0.0.1
 * @since 2017 /12/23 下午12:11
 */
class StatementParser {

    private final static Log logger = LogFactory.getLog(StatementParser.class);

    private static PreparedStatementCreator getPreparedStatementCreator(final String sql, final Object[] args, final boolean returnKeys) {
        PreparedStatementCreator creator = new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                if (returnKeys) {
                    ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                } else {
                    ps = con.prepareStatement(sql);
                }

                if(args ! =null) {
                    for (int i = 0; i < args.length; i++) {
                        Object arg = args[i];
                        if (arg instanceof SqlParameterValue) {
                            SqlParameterValue paramValue = (SqlParameterValue) arg;
                            StatementCreatorUtils.setParameterValue(ps, i + 1, paramValue,
                                    paramValue.getValue());
                        } else {
                            StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, arg); }}}returnps; }};return creator;
    }

    /** * the Repository method is parsed to the corresponding SQL and parameters * <p> * the SQL comes from the@QueryThe annotated value * argument comes from the method's argument * <p> * Note that the result set is encapsulated according to the return value * *@paramProxy executes object *@paramMethod Execution method *@param* args parameter@return object
     */
    static Object invoke(Object proxy, Method method, Object[] args) throws Exception {

        JdbcTemplate jdbcTemplate = DataSourceHolder.getInstance().getJdbcTemplate();

        boolean logDebug = logger.isDebugEnabled();

        String methodReturnType = method.getReturnType().getName();
        Query query = method.getAnnotation(Query.class);

        if (null == query || StringUtil.isBlank(query.value())) {
            logger.error(method.toGenericString() + "No Query annotation or SQL is empty");
            throw new IllegalStateException(method.toGenericString() + "No Query annotation or SQL is empty");
        }

        String originSql = query.value().trim();

        System.out.println("sql:" + query.value());
        Map<String, Object> namedParamMap = new HashMap<>();
        Parameter[] parameters = method.getParameters();
        if(args ! =null && args.length > 0) {
            for (int i = 0; i < args.length; ++i) {
                Param param = parameters[i].getAnnotation(Param.class);
                if (null! = param) { namedParamMap.put(param.value(), args[i]); } namedParamMap.put("?" + (i + 1), args[i]); }}if (logDebug) {
            logger.debug("Execute SQL:" + originSql);
        }

        // Check the type of the SQL statement
        boolean isQuery = originSql.trim().matches("(? i)select([\\s\\S]*?) ");
        Object[] params = null;
        // rewrite sql
        if (null! = args && args.length >0) {
            List<String> results = StringUtil.matches(originSql, "\ \? \\d+(\\.[A-Za-z]+)? |:[A-Za-z0-9]+(\\.[A-Za-z]+)?");
            if (results.isEmpty()) {
                params = args;
            } else {
                params = new Object[results.size()];
                for (int i = 0; i < results.size(); ++i) {
                    if (results.get(i).charAt(0) = =':') {
                        originSql = originSql.replaceFirst(results.get(i), "?");
                        // Check whether it is the format of param.param
                        if(! results.get(i).contains(".")) {
                            params[i] = namedParamMap.get(results.get(i).substring(1));
                        } else {
                            String[] paramArgs = results.get(i).split("\ \.");
                            Object param = namedParamMap.get(paramArgs[0].substring(1));
                            params[i] = BeanReflectionUtil.getFieldValue(param, paramArgs[1]);
                        }
                        continue;
                    }
                    int paramIndex = Integer.parseInt(results.get(i).substring(1));
                    originSql = originSql.replaceFirst("\ \"?" + paramIndex, "?");
                    params[i] = namedParamMap.get(results.get(i));
                }
            }
        }


        System.out.println("execute sql:" + originSql);
        System.out.print("params : ");
        if (null! = params) {for (Object o : params) {
                System.out.print(o + ",\t");
            }
        }
        System.out.println("\n");


        /** * if the return value is a primitive type or its wrapper */
        System.out.println(methodReturnType);
        if (isQuery) {
            // Query method
            if ("java.lang.Integer".equals(methodReturnType) || "int".equals(methodReturnType)) {
                return jdbcTemplate.queryForObject(originSql, params, Integer.class);
            } else if ("java.lang.String".equals(methodReturnType)) {
                return jdbcTemplate.queryForObject(originSql, params, String.class);
            } else if ("java.util.List".equals(methodReturnType) || "java.util.Set".equals(methodReturnType)) {
                String typeName = null;
                Type returnType = method.getGenericReturnType();
                if (returnType instanceof ParameterizedType) {
                    Type[] types = ((ParameterizedType) returnType).getActualTypeArguments();
                    if (null == types || types.length > 1) {
                        throw new IllegalArgumentException("When the return value is list, it must indicate the specific type and only one.");
                    }
                    typeName = types[0].getTypeName();
                }
                Object obj = BeanReflectionUtil.newInstance(typeName);
                SqlRowSet rowSet = jdbcTemplate.queryForRowSet(originSql, params);
                if ("java.util.List".equals(methodReturnType)) {
                    return ListConvert.convert(rowSet, obj);
                }
                return SetConvert.convert(rowSet, obj);
            } else if ("java.util.Map".equals(methodReturnType)) {
                throw new NotImplementedException();
            } else {
                SqlRowSet rowSet = jdbcTemplate.queryForRowSet(originSql, params);
                Object obj = BeanReflectionUtil.newInstance(methodReturnType);
                returnBeanConvert.convert(rowSet, obj); }}else {
            // Non-query methods
            // Check if it is an INSERT statement
            ReturnGeneratedKey returnGeneratedKeyAnnotation = method.getAnnotation(ReturnGeneratedKey.class);
            if (returnGeneratedKeyAnnotation == null) {
                int retVal = jdbcTemplate.update(originSql, params);
                if ("java.lang.Integer".equals(methodReturnType) || "int".equals(methodReturnType)) {
                    return retVal;
                } else if ("java.lang.Boolean".equals(methodReturnType)) {
                    return retVal > 0; }}else {
                // Check if it is an INSERT statement
                boolean isInsertSql = originSql.trim().matches("(? i)insert([\\s\\S]*?) ");
                if (isInsertSql) {
                    KeyHolder keyHolder = new GeneratedKeyHolder();
                    PreparedStatementCreator preparedStatementCreator = getPreparedStatementCreator(originSql, params, true);
                    jdbcTemplate.update(preparedStatementCreator, keyHolder);
                    if ("java.lang.Integer".equals(methodReturnType) || "int".equals(methodReturnType)) {
                        return keyHolder.getKey().intValue();
                    } else if ("java.lang.Long".equals(methodReturnType) || "long".equals(methodReturnType)) {
                        return keyHolder.getKey().longValue();
                    }
                    logger.error(method.toGenericString() + "Return primary key ID should be int or long");
                    throw new IllegalArgumentException(method.toGenericString() + "Return primary key ID should be int or long");
                } else {
                    logger.error(method.toGenericString() + "Non-insert statements cannot return GeneratedKey: SQL statements are:" + originSql);
                    throw new IllegalStateException(method.toGenericString() + "Non-insert statements cannot return GeneratedKey: SQL statements are:"+ originSql); }}}return null; }}Copy the code

Thus a simple ORM is implemented. In fact, it is not difficult to implement ORM. The difficulty is to deal with all possible bugs carefully