java 根据实体对象生成 增删改的SQL语句 ModelToSQL

原文链接

在参考原文链接的代码后,发现有一处小错误,就是update 的set 两个条件之间差 逗号,于是乎自己小小的修改了下,并且加了部分代码的注释,最终代码如下:

package com.xxx.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.List;
import java.util.Vector;

import org.apache.commons.lang3.reflect.FieldUtils;

public class ModelToSQL {
    private Object target;

    private String idName;

    private Object idValue;

    private SqlType currentType;

    public enum SqlType {
        INSERT, UPDATE, DELETE
    }

    public ModelToSQL(SqlType sqlType, Object target) {
        this.target = target;
        switch (sqlType) {
        case INSERT:
            currentType = SqlType.INSERT;
            createInsert();
            break;
        case UPDATE:
            currentType = SqlType.UPDATE;
            createUpdate();
            break;
        case DELETE:
            currentType = SqlType.DELETE;
            createDelete();
            break;
        }
    }

    public ModelToSQL(Class<?> target) {
        String tableName = getTableNameForClass(target);
        getFields(target);

        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("DELETE FROM ").append(tableName).append(" WHERE ");
        for (Field field : fields) {
            if (!Modifier.isStatic(field.getModifiers())) {
                ID id = field.getAnnotation(ID.class);
                if (null != id) {
                    sqlBuffer.append(field.getName()).append("=?");
                }
            }
        }
        this.sqlBuffer = sqlBuffer.toString();
    }

    /**
     * 创建跟删除
     */
    private void createDelete() {
        String tableName = getTableName();
        getFields(target.getClass());
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("DELETE FROM ").append(tableName).append(" WHERE ");
        for (Field field : fields) {
            if (!Modifier.isStatic(field.getModifiers())) {
                ID id = field.getAnnotation(ID.class);
                if (null != id) {
                    sqlBuffer.append(field.getName()).append(" = ? ");
                    param.add(readField(field));
                }
            }
        }
        System.err.println("delete:\t"+sqlBuffer.toString());
        this.sqlBuffer = sqlBuffer.toString();
    }

    protected Object readField(Field field) {
        try {
            return FieldUtils.readField(field, target, true);
        } catch (Exception e) {
            throw new RuntimeException(currentType.name(), e);
        }
    }

    /**
     * 创建更新语句
     */
    private void createUpdate() {
        String tableName = getTableName();
        getFields(target.getClass());
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("UPDATE ").append(tableName).append(" SET ");

        for (Field field : fields) {
            if (!Modifier.isStatic(field.getModifiers())) {
                ID id = field.getAnnotation(ID.class);
                if (id == null) {
                    sqlBuffer.append(field.getName()).append("=? , ");
                    param.add(readField(field));
                } else {
                    idName = field.getName();
                    idValue = readField(field);
                }
            }
        }
        sqlBuffer.replace(sqlBuffer.length()-2, sqlBuffer.length()-1, " ");
        if (idName == null) {
            throw new RuntimeException("not found of " + target.getClass() + "'s ID");
        }
        sqlBuffer.append(" WHERE ").append(idName).append("=?");
        param.add(idValue);
System.err.println("update:\t"+sqlBuffer.toString());
        this.sqlBuffer = sqlBuffer.toString();
    }

    /**
     * 根据注解获取表名
     */
    private String getTableName() {
        String tableName = null;
        Class<?> clazz = target.getClass();
        tableName = getTableNameForClass(clazz);
        return tableName;
    }

    private String getTableNameForClass(Class<?> clazz) {
        String tableName;
        Table table = clazz.getAnnotation(Table.class);
        if (null != table) {
            tableName = table.name();
            if ("".equalsIgnoreCase(tableName)) {
                tableName = clazz.getSimpleName();
            }
        } else {
            tableName = clazz.getSimpleName();
        }
        return tableName;
    }

    /**
     * 创建插入语句
     */
    private void createInsert() {
        String tableName = getTableName();
        getFields(target.getClass());
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("INSERT INTO ").append(tableName).append("(");

        for (Field field : fields) {
            if (!Modifier.isStatic(field.getModifiers())) {
                ID id = field.getAnnotation(ID.class);
                if (id == null) {
                    sqlBuffer.append(field.getName()).append(",");
                    param.add(readField(field));
                }
            }
        }
        int length = sqlBuffer.length();
        sqlBuffer.delete(length - 1, length).append(")values(");
        int size = param.size();
        for (int x = 0; x < size; x++) {
            if (x != 0) {
                sqlBuffer.append(",");
            }
            sqlBuffer.append("?");
        }
        sqlBuffer.append(")");
System.err.println("insert:\t"+sqlBuffer.toString());
        this.sqlBuffer = sqlBuffer.toString();
    }

    private List<Object> param = new Vector<Object>();

    private String sqlBuffer;

    public List<Object> getParam() {
        return param;
    }

    public String getSqlBuffer() {
        return sqlBuffer;
    }

    public String getIdName() {
        return idName;
    }

    public Object getIdValue() {
        return idValue;
    }

    List<Field> fields = new Vector<Field>();

    protected void getFields(Class<?> clazz) {
        if (Object.class.equals(clazz)) {
            return;
        }
        Field[] fieldArray = clazz.getDeclaredFields();
        for (Field file : fieldArray) {
            fields.add(file);
        }
        getFields(clazz.getSuperclass());
    }

    //创建注解,标识该model的table名
    @java.lang.annotation.Target(value = { java.lang.annotation.ElementType.TYPE })
    @java.lang.annotation.Retention(value = java.lang.annotation.RetentionPolicy.RUNTIME)
    public @interface Table {
        String name() default "";
    }

    //创建注解,标识该model的id字段
    @java.lang.annotation.Target(value = { java.lang.annotation.ElementType.FIELD })
    @java.lang.annotation.Retention(value = java.lang.annotation.RetentionPolicy.RUNTIME)
    public @interface ID {
    }
}

model

package test.xxx.utils;

@Table(name="Student")
public class TestModel {

    @ID
    private int sid;
    private  String name;
    private int age ;
    public int getSid() {
        return sid;
    }
    public void setSid(int sid) {
        this.sid = sid;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

}

测试

public class Test {
    public static void main(String[] args) {
        new ModelToSQL(SqlType.INSERT, new TestModel());        
        new ModelToSQL(SqlType.UPDATE, new TestModel());        
        new ModelToSQL(SqlType.DELETE, new TestModel());
    }



}

生成的sql语句

insert: INSERT INTO Student(name,age)values(?,?)
update: UPDATE Student SET name=? , age=?    WHERE sid=?
delete: DELETE FROM Student WHERE sid = ? 

用到的jar包的maven地址如下

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.0</version>
</dependency>