News:三分天注定,七分靠打拼,爱拼才会赢!致力打造专业IT博客。如果你对本博客有任何意见或建议请联系作者,邮箱:blog@caokuan.cn

JPA 根据 Entity 生成 SQL insert 语句工具类

逝水无痕 576 0 条

在使用 Spring JPA 开发的过程中,由于 JPA 框架的原因,在保存数据时如果存在实体引用,就会发查询 SQL 来验证引用实体的正确性,这就损失了不少的效率。其实在大多数情况下,引用的实体都是正常的,所以我们可以通过直接执行 insert 语句的方式来提高保存效率。

sql.jpg

package cn.caokuan.utils;

import cn.caokuan.common.entity.BaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.annotations.Formula;

import javax.persistence.*;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * 说明:生成SQL语句
 * @author caokk
 */
public class SQLUtils {
    
    private static final String SQL_DATE_FORMAT = "dd-mm-yyyy hh24:mi:ss";
    private static final String DATE_FORMAT = "dd-MM-yyyy HH:mm:ss";

    /**
     * 获取表名
     */
    public static <T extends BaseEntity> String getTableName(Class<?> entityClass) {
        String tableName = null;
        Table table = entityClass.getAnnotation(Table.class);
        if (table != null) {
            tableName = table.name();
        }
        if (StringUtils.isBlank(tableName)) {
            Entity entity = entityClass.getAnnotation(Entity.class);
            if (entity != null) {
                tableName = entity.name();
            }
        }
        if (StringUtils.isBlank(tableName)) {
            tableName = entityClass.getSimpleName();
        }
        return tableName;
    }

    /**
     * 生成insert语句
     * 
     * t 表示实体对象
     */
    public static <T extends BaseEntity> String generateInsertSQL(T t) {
        Class<?> entityClass = t.getClass();
        String tableName = getTableName(entityClass);
        StringBuilder sql = new StringBuilder(256);
        StringBuilder values = new StringBuilder(256);
        sql.append("INSERT INTO ").append(tableName).append(" (ID, DR, TS, CREATOR, CREATION_TIME, MODIFIER, MODIFIED_TIME");
        values.append("VALUES ('").append(t.getId()).append("', ").append(t.getDr()).append(", ");
        SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
        values.append(t.getTs() == null ? null : dateString(t.getTs(), format)).append(", ");
        values.append(t.getCreator() == null ? null : "'" + t.getCreator() + "'").append(", ");
        values.append(t.getCreationTime() == null ? null : dateString(t.getCreationTime(), format)).append(", ");
        values.append(t.getModifier() == null ? null : "'" + t.getModifier() + "'").append(", ");;
        values.append(t.getModifiedTime() == null ? null : dateString(t.getModifiedTime(), format));
        Field[] fields = entityClass.getDeclaredFields();
        for (Field field : fields) {
            String columnName = getColumnName(field);
            if (columnName != null) {
                Object value = null;
                try {
                    field.setAccessible(true);
                    value = field.get(t);
                } catch (IllegalArgumentException | IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (value != null) {
                    boolean ist = true;
                    if (value instanceof Date) {
                        values.append(", ").append(dateString((Date) value, format));
                    } else if (value instanceof String || value instanceof Character) {
                        values.append(", '").append(value).append("'");
                    } else if (value instanceof BaseEntity) {
                        String id = ((BaseEntity) value).getId();
                        if (StringUtils.isNotBlank(id)) {
                            values.append(", '").append(id).append("'");
                        } else {
                            ist = false;
                        }
                    } else {
                        values.append(", ").append(value);
                    }
                    if (ist) {
                        sql.append(", ").append(columnName.toUpperCase());
                    }
                }
            }
        }
        sql.append(") ").append(values).append(")");
        return sql.toString();
    }

    private static String dateString(Date date, DateFormat format) {
        return "to_date('" + format.format(date) + "', '" + SQL_DATE_FORMAT + "')";
    }

    private static String timestampString(Date date, DateFormat format) {
        return "to_timestamp('" + format.format(date) + "', '" + SQL_DATE_FORMAT + "')";
    }
    
    /**
     * 获取表字段名
     * 
     * @param field
     * @return
     */
    private static String getColumnName(Field field) {
        String columnName = null;
        Column column = field.getAnnotation(Column.class);
        if (column != null) {
            columnName = column.name();
        }
        if (StringUtils.isBlank(columnName)) {
            JoinColumn jcolumn = field.getAnnotation(JoinColumn.class);
            if (jcolumn != null) {
                columnName = jcolumn.name();
            }
        }
        if (StringUtils.isBlank(columnName)) {
            Transient transients = field.getAnnotation(Transient.class);
            Formula formula = field.getAnnotation(Formula.class);
            if (transients != null || formula != null) {
                return columnName;
            }
            if (!Collection.class.isAssignableFrom(field.getType())) {
                columnName = field.getName();
            }
        }
        return columnName;
    }
}

工具类包含两个方法:

  • getTableName:获取实体的表名
  • generateInsertSQL:生成 insert 语句
发表我的评论
icon_mrgreen.gificon_neutral.gificon_twisted.gificon_arrow.gificon_eek.gificon_smile.gificon_confused.gificon_cool.gificon_evil.gificon_biggrin.gificon_idea.gificon_redface.gificon_razz.gificon_rolleyes.gificon_wink.gificon_cry.gificon_surprised.gificon_lol.gificon_mad.gificon_sad.gificon_exclaim.gificon_question.gif

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址