MyBatis SQL 生成方法 增删改查

此类根据JAVA实体BEAN生成MYBATIS的接口SQL（mapper）

package com.sicdt.sicsign.bill.service.hessian;

import java.lang.reflect.Field;

import com.sicdt.sicsign.bill.service.entity.InvoiceEntity;

public class MyBatisSqlUtils {
    public static void main(String[] args) {
        String tableName = "T_INVOICE";
        Class<?> clazz = InvoiceEntity.class;
        
        System.out.println("主键查询SQL：======================================");
        String idSql = MyBatisSqlUtils.selectSQLById(clazz, tableName);
        System.out.println("主键查询SQL：    " + idSql);
        String idSqlHump = MyBatisSqlUtils.selectSQLByIdForHump(clazz, tableName);
        System.out.println("主键查询SQL：    " + idSqlHump);
        
        System.out.println("主键集合查询SQL：======================================");
        String idsSql = MyBatisSqlUtils.selectSQLByIds(clazz, tableName);
        System.out.println("主键集合查询SQL：    " + idsSql);
        String idsSqlHump = MyBatisSqlUtils.selectSQLByIdsForHump(clazz, tableName);
        System.out.println("主键集合查询SQL：    " + idsSqlHump);
        
        System.out.println("查询SQL：======================================");
        String selectSql = MyBatisSqlUtils.selectSQL(clazz,tableName);
        System.out.println("查询SQL：    " + selectSql);
        String selectSqlHump = MyBatisSqlUtils.selectSQLForHump(clazz,tableName);
        System.out.println("查询SQL：    " + selectSqlHump);
        
        System.out.println("插入SQL：======================================");
        String insertSql = MyBatisSqlUtils.insertSql(clazz, tableName);
        System.out.println("插入SQL：    "+insertSql);
        String insertSqlHump = MyBatisSqlUtils.insertSqlForHump(clazz, tableName);
        System.out.println("插入SQL：    "+insertSqlHump);
        
        System.out.println("删除SQL：======================================");
        String deleteSql = MyBatisSqlUtils.deleteSqlById(clazz, tableName);
        System.out.println("删除SQL：    "+deleteSql);
        String deleteSqlHump = MyBatisSqlUtils.deleteSqlByIdForHump(clazz, tableName);
        System.out.println("删除SQL：    "+deleteSqlHump);
        
        System.out.println("修改SQL：======================================");
        String updateSql = MyBatisSqlUtils.updateSql(clazz, tableName);
        System.out.println("修改SQL：    "+updateSql);
        String updateSqlHump = MyBatisSqlUtils.updateSqlForHump(clazz, tableName);
        System.out.println("修改SQL：    "+updateSqlHump);
        
        System.out.println("批量插入SQL：======================================");
        String batchInsertSql = MyBatisSqlUtils.batchInsertSql(clazz, tableName);
        System.out.println("批量插入SQL："+batchInsertSql);
        String batchInsertSqlHump = MyBatisSqlUtils.batchInsertSqlForHump(clazz, tableName);
        System.out.println("批量插入SQL："+batchInsertSqlHump);
        
        System.out.println("批量更新SQL：======================================");
        String batchUpdateSql = MyBatisSqlUtils.batchUpdateSql(clazz, tableName);
        System.out.println("批量更新SQL："+batchUpdateSql);
        String batchUpdateSqlHump = MyBatisSqlUtils.batchUpdateSqlForHump(clazz, tableName);
        System.out.println("批量更新SQL："+batchUpdateSqlHump);
        
        System.out.println("批量删除SQL（主键）：======================================");
        String batchDeleteSqlByIds = MyBatisSqlUtils.batchDeleteSqlByIds(clazz, tableName);
        System.out.println("批量删除SQL（主键）："+batchDeleteSqlByIds);
        String batchDeleteSqlHumpByIds = MyBatisSqlUtils.batchDeleteSqlByIdsForHump(clazz, tableName);
        System.out.println("批量删除SQL（主键）："+batchDeleteSqlHumpByIds);
        
        System.out.println("批量删除SQL（实体）：======================================");
        String batchDeleteSql = MyBatisSqlUtils.batchDeleteSql(clazz, tableName);
        System.out.println("批量删除SQL（实体）："+batchDeleteSql);
        String batchDeleteSqlHump = MyBatisSqlUtils.batchDeleteSqlForHump(clazz, tableName);
        System.out.println("批量删除SQL（实体）："+batchDeleteSqlHump);
    }
    
    /**
     * <br>描 述: 生成查询SQL（实体属性与数据库一致）主键查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQLById(Class<?> clazz,String tableName){
        return selectSQLById(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成查询SQL（实体属性为驼峰）主键查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQLByIdForHump(Class<?> clazz,String tableName){
        return selectSQLById(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成查询SQL（实体属性与数据库一致）主键集合查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQLByIds(Class<?> clazz,String tableName){
        return selectSQLByIds(clazz, tableName, false);
    }
    
    
    /**
     * <br>描 述: 生成查询SQL（实体属性为驼峰）主键集合查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQLByIdsForHump(Class<?> clazz,String tableName){
        return selectSQLByIds(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成查询SQL（实体属性与数据库一致）条件查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQL(Class<?> clazz,String tableName){
        return selectSQL(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成查询SQL（实体属性为驼峰）条件查询
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String selectSQLForHump(Class<?> clazz,String tableName){
        return selectSQL(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成插入SQL（实体属性与数据库一致）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String  insertSql(Class<?> clazz,String tableName){
        return insertSql(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成插入SQL（实体属性为驼峰）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String  insertSqlForHump(Class<?> clazz,String tableName){
        return insertSql(clazz, tableName, true);
    }

    /**
     * <br>描 述: 生成更新Sql（实体属性与数据库一致）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String updateSql(Class<?> clazz,String tableName){
        return updateSql(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成更新SQL（实体属性为驼峰）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String updateSqlForHump(Class<?> clazz,String tableName){
        return updateSql(clazz, tableName, true);
    }
    
    
    /**
     * <br>描 述: 生成删除SQL（实体属性与数据库一致）主键删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String deleteSqlById(Class<?> clazz,String tableName){
        return deleteSqlById(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成删除SQL（实体属性为驼峰）主键删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String deleteSqlByIdForHump(Class<?> clazz,String tableName){
        return deleteSqlById(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成删除SQL（实体属性与数据库一致）实体删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String deleteSql(Class<?> clazz,String tableName){
        return deleteSqlById(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成删除SQL（实体属性为驼峰）实体删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String deleteSqlForHump(Class<?> clazz,String tableName){
        return deleteSqlById(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成批量插入SQL（实体属性与数据库一致）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchInsertSql(Class<?> clazz,String tableName){
        return batchInsertSql(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成批量插入SQL（实体属性为驼峰）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchInsertSqlForHump(Class<?> clazz,String tableName){
        return batchInsertSql(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成批量更新SQL（实体属性与数据库一致）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchUpdateSql(Class<?> clazz,String tableName){
        return batchUpdateSql(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成批量更新SQL（实体属性为驼峰）
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchUpdateSqlForHump(Class<?> clazz,String tableName){
        return batchUpdateSql(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成批量删除SQL（实体属性与数据库一致）主键删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchDeleteSqlByIds(Class<?> clazz,String tableName){
        return batchDeleteSqlByIds(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成批量删除SQL（实体属性为驼峰）实体删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchDeleteSqlByIdsForHump(Class<?> clazz,String tableName){
        return batchDeleteSqlByIds(clazz, tableName, true);
    }
    
    /**
     * <br>描 述: 生成批量删除SQL（实体属性与数据库一致）主键删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchDeleteSql(Class<?> clazz,String tableName){
        return batchDeleteSql(clazz, tableName, false);
    }
    
    /**
     * <br>描 述: 生成批量删除SQL（实体属性为驼峰）实体删除
     * <br>作 者: shizhenwei 
     * @param clazz Object.class
     * @param tableName 表名称
     * @return
     */
    public static String batchDeleteSqlForHump(Class<?> clazz,String tableName){
        return batchDeleteSql(clazz, tableName, true);
    }
    
    
    /*
     * 生成查询SQL 主键查询
     */
    private static String selectSQLById(Class<?> clazz,String tableName,boolean isHump){
        String select = "@Select("SELECT";
        Field[] fields = clazz.getDeclaredFields();
        select += getSelectColumns(fields,isHump)
                +" FROM "+tableName
                +" WHERE "+getSelectColumn(fields[0],isHump)+" = "+getValueColumn(fields[0])+"")";
        return select;
    }
    
    /*
     * 生成查询SQL主键集合查询
     */
    private static String selectSQLByIds(Class<?> clazz,String tableName,boolean isHump){
        String select = "@Select("<script>""
                    +"
+"SELECT";
        Field[] fields = clazz.getDeclaredFields();
        select += getSelectColumns(fields,isHump)
                +" FROM "+tableName
                +" WHERE "+getSelectColumn(fields[0],isHump)+" IN ""
                +"
+"<foreach collection='primaryKeys' item='"+fields[0].getName()+"' index='index' open='(' close=')' separator=','>""
                +"
+""+getValueColumn(fields[0])+"""
                +"
+"</foreach>""
                +"
+"</script>")";
        return select;
    }
    
    /*
     * 生成查询SQL 条件查询
     */
    private static String selectSQL(Class<?> clazz,String tableName,boolean isHump){
        //接口引用加" 生成双引号
        String select = "@Select("<script>""
                +"
+"SELECT";
        Field[] fields = clazz.getDeclaredFields();
        select += getSelectColumns(fields,isHump)
                +" FROM "+tableName+"""
                +"
+"<trim prefix='WHERE' prefixOverrides='and|or'>"";
        for(Field field : fields){
            if(isHump){
                select += "
+"<if test='"+field.getName()+" != null'> AND "+getSelectColumn(field,isHump)+" = "+getValueColumn(field)+"</if>"";
            }else{
                select += "
+"<if test='"+field.getName()+" != null'> AND "+field.getName()+" = "+getValueColumn(field)+"</if>"";
            }
        }
        select += "
+"</trim>""
                +"
+"</script>")";
        return select;
    }
    
    
    /*
     * 生成插入SQL
     */
    private static String  insertSql(Class<?> clazz,String tableName,boolean isHump){
        String insert = "@Insert("INSERT INTO "+tableName;
        Field[] fields = clazz.getDeclaredFields();
        insert += " ("+getInsertColumns(fields,isHump)+")"
                +" VALUES ("+getValueColumns(fields,false)+")"
                +"")";
        return insert;
    }
    
    /*
     * 生成更新SQL
     */
    private static String updateSql(Class<?> clazz,String tableName,boolean isHump){
        Field[] fields = clazz.getDeclaredFields();
        String idColumn;
        if(isHump){
            idColumn = getSelectColumn(fields[0],isHump);
        }else{
            idColumn = fields[0].getName();
        }
        String idVal = getValueColumn(fields[0]);
        String update = "@Update("<script>""
                        +"
+"UPDATE "+tableName+"""
                        +"
+"<trim prefix='set' suffixOverrides=',' suffix='WHERE "+idColumn+"="+idVal+"'>"";
        for(int i=1; i<fields.length; i++){
            Field field = fields[i];
            if(isHump){
                update += "
+"<if test='"+field.getName()+"!=null'>"+getSelectColumn(field,isHump)+"="+getValueColumn(field)+",</if>"";
            }else{
                update += "
+"<if test='"+field.getName()+"!=null'>"+field.getName()+"="+getValueColumn(field)+",</if>"";
            }
        }
        update += "
+"</trim>""
                +"
+"</script>")";
        return update;
    }
    
    /*
     * 生成删除SQL
     */
    private static String deleteSqlById(Class<?> clazz,String tableName,boolean isHump){
        String delete = "@Delete("DELETE FROM "+tableName+" WHERE ";
        Field field = clazz.getDeclaredFields()[0];
        String column = getSelectColumn(field,isHump);
        delete += column+" = "+getValueColumn(field)+"")";
        return delete;
    }
    
    
    /*
     * 生成批量插入SQL
     */
    private static String  batchInsertSql(Class<?> clazz,String tableName,boolean isHump){
        String insert = "@Insert("<script>""
                +"
+"INSERT INTO "+tableName+""";
        Field[] fields = clazz.getDeclaredFields();
        insert += "
+"("+getInsertColumns(fields,isHump)+")""
                +"
+"VALUES ""
                +"
+"<foreach collection='list' item='item' index='index' separator=','>""
                +"
+"("+getValueColumns(fields,true)+")""
                +"
+"</foreach>""
                +"
+"</script>")";
        return insert;
    }
    
    /*
     * 生成批量更新SQL
     */
    private static String batchUpdateSql(Class<?> clazz,String tableName,boolean isHump){
        Field[] fields = clazz.getDeclaredFields();
        String idColumn;
        if(isHump){
            idColumn = getSelectColumn(fields[0],isHump);
        }else{
            idColumn = fields[0].getName();
        }
        String idVal = getValueColumn(fields[0],true);
        String update = "@Update("<script>""
                        +"
+"<foreach collection='list' item='item' index='index' open='' close='' separator=';'>""
                        +"
+"UPDATE "+tableName+"""
                        +"
+"<trim prefix='set' suffixOverrides=',' suffix='WHERE "+idColumn+"="+idVal+"'>"";
        for(int i=1; i<fields.length; i++){
            Field field = fields[i];
            if(isHump){
                update += "
+"<if test='item."+field.getName()+"!=null'>"+getSelectColumn(field,isHump)+"="+getValueColumn(field,true)+",</if>"";
            }else{
                update += "
+"<if test='item."+field.getName()+"!=null'>"+field.getName()+"="+getValueColumn(field,true)+",</if>"";
            }
        }
        update += "
+"</trim>""
                +"
+"</foreach>""
                +"
+"</script>")";

        return update;
    }
    
    /*
     * 生成批量删除SQL 主键删除
     */
    private static String batchDeleteSqlByIds(Class<?> clazz,String tableName,boolean isHump){
        String delete = "@Delete("<script>""
                +"
+"DELETE FROM "+tableName+" WHERE ";
        Field field = clazz.getDeclaredFields()[0];
        String column = getSelectColumn(field,isHump);
        delete += column+" IN ""
                +"
+"<foreach collection='primaryKeys' item='"+field.getName()+"' index='index' open='(' close=')' separator=','>""
                +"
+""+getValueColumn(field)+"""
                +"
+"</foreach>""
                +"
+"</script>")";
        return delete;
    }
    
    /*
     * 生成批量删除SQL 实体删除
     */
    private static String batchDeleteSql(Class<?> clazz,String tableName,boolean isHump){
        String delete = "@Delete("<script>""
                +"
+"DELETE FROM "+tableName+" WHERE ";
        Field field = clazz.getDeclaredFields()[0];
        String column = getSelectColumn(field,isHump);
        delete += column+" in ""
                +"
+"<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>""
                +"
+""+getValueColumn(field,true)+"""
                +"
+"</foreach>""
                +"
+"</script>")";
        return delete;
    }
    
    /*
     * 获SELECT取列
     */
    private static String getSelectColumns(Field[] fields,boolean isHump){
        String  columns = "";
        if(isHump){
            for(Field field : fields){
                columns += " "+getSelectColumn(field, isHump) +" AS "+field.getName()+",";
            }
        }else{
            for(Field field : fields){
                columns+=" "+getSelectColumn(field, isHump)+" AS "+field.getName()+",";
            }
        }
        return columns.substring(0,columns.length()-1);
    }
    
    /*
     * 获INSERT列
     */
    private static String getInsertColumns(Field[] fields,boolean isHump){
        String  columns = "";
        if(isHump){
            for(Field field : fields){
                columns += " "+getSelectColumn(field, isHump)+",";
            }
        }else{
            for(Field field : fields){
                columns+=" "+getSelectColumn(field, isHump)+",";
            }
        }
        return columns.substring(0,columns.length()-1).toUpperCase();
    }
    
    /*
     * 获insert取列
     */
    private static String getValueColumns(Field[] fields,boolean isBatch){
        String columns = "";
        for(Field field : fields){
            columns+=" "+getValueColumn(field,isBatch)+",";
        }
        return columns.substring(0, columns.length()-1);
    }
    
    
    /*
     * 获取单列
     */
    private static String getSelectColumn(Field field,boolean isHump){
        String fieldName = field.getName();
        String column = "";
        if(isHump){
            for(int i=0; i<fieldName.length(); i++){
                char ch = fieldName.charAt(i);
                if(Character.isUpperCase(ch)){
                    column += "_"+ch;
                }else{
                    column += ch;
                }
            }
        }else{
            column = fieldName;
        }
        return column.toUpperCase();
    }
    
    /*
     * 获取单值列
     */
    private static String getValueColumn(Field field,boolean isBatch){
        if(isBatch){
            return "#{item."+field.getName()+"}";
        }else{
            return "#{"+field.getName()+"}";
        }
    }
    
    /*
     * 获取单值列
     */
    private static String getValueColumn(Field field){
        return getValueColumn(field,false);
    }
}