Java反射实体组装SQL

之前在LIS.Core定义了实体特性，在LIS.Model给实体类加了表特性，属性特性，外键特性等。ORM要实现增删改查和查带外键的父表信息就需要解析Model的特性和实体信息组装SQL来供数据库驱动实现增删改查功能。

实现实体得到SQL的工具类，先实现一个得到查询SQL示例

package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.*;
import LIS.Core.Dto.*;
import LIS.Core.Dto.ParamDto;
import java.util.*;
import java.util.List;
import LIS.DAL.ORM.Common.TableInfo;
import LIS.DAL.ORM.DBUtility.IDbFactory;

import java.lang.annotation.Annotation;
import java.lang.reflect.*;
import LIS.DAL.ORM.Common.ColumnInfo;
import LIS.DAL.ORM.Common.TableInfo;
import LIS.Core.Util.ReflectUtil;
import LIS.Core.CustomAttributes.TableAttribute;

//通过实体类型得到SQL的工具类
public class ModelToSqlUtil {
    //通过实体对象得到表信息
    public static TableInfo GetTypeInfo(Object model) {
        if (model == null) {
            return null;
        }
        try {
            //返回的对象
            TableInfo tableInfo = new TableInfo();
            Class c = model.getClass();
            System.out.println("111");
            //得到表特性
            Annotation[] annoList = c.getAnnotations();
            if (annoList != null && annoList.length > 0) {
                for (int i = 0; i < annoList.length; i++) {
                    //表特性
                    if (annoList[i] instanceof TableAttribute) {
                        tableInfo.TableInfo = (TableAttribute) annoList[i];
                    }
                    //唯一特性
                    else {
                        tableInfo.UniqueList.add((UniqueAttribute) annoList[i]);
                    }
                }
            }
            //得到列信息
            Field[] declaredFields = c.getDeclaredFields();
            for (int i = 0; i < declaredFields.length; i++) {
                LIS.DAL.ORM.Common.ColumnInfo col = new LIS.DAL.ORM.Common.ColumnInfo();
                FrekeyAttribute fk = declaredFields[i].getAnnotation(FrekeyAttribute.class);
                col.FkInfo = fk;
                col.Name = declaredFields[i].getName();
                col.ColType = declaredFields[i].getType();
                col.Value = declaredFields[i].get(model);
                tableInfo.ColList.add(col);
            }

            return tableInfo;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //通过表信息和参数列表组装查询SQL
    //factory:驱动工厂
    //tableInfo:表信息
    //para:参数
    //operList:参数对应的操作符列表
    //linkList:多个参数之间连接的操作符 and or这种
    //orderBySql:Order By的SQL语句
    //IsFk:是否组装外键查询的SQL
    public static String GetSelectSqlByTableInfo(IDbFactory factory,TableInfo tableInfo, List<ParamDto> para,List<String> operList,List<String> linkList, String orderBySql,Boolean IsFk)
    {
        StringBuilder sb=new StringBuilder();
        sb.append("select ");
        //是否含有序号列
        boolean HasSequence=false;
        boolean HasSeqNum=false;
        //组装查询列
        for(int i=0;i<tableInfo.ColList.size();i++)
        {
            //存列名
            String columnName = tableInfo.ColList.get(i).Name;
            if(columnName=="Sequence")
            {
                HasSequence=true;
            }
            else if(columnName=="SeqNum")
            {
                HasSeqNum=true;
            }
            //组装查询列
            if(i==0)
            {
                sb.append(factory.DealPropertyName(columnName));
            }
            else
            {
                sb.append(","+factory.DealPropertyName(columnName));
            }
            //组装外键查询信息
            if(IsFk==true&&tableInfo.ColList.get(i).FkInfo!=null) {
                FrekeyAttribute fkAttr=tableInfo.ColList.get(i).FkInfo;
                String refTableName=factory.DealTableName(GetTableName(ReflectUtil.GetType("LIS.Model.Entity." + fkAttr.Name(), "LIS.Model")));
                sb.append("," + "(select " + factory.DealPropertyName(fkAttr.AssociaField()) + " from " + refTableName + " where ti." + factory.DealPropertyName(columnName) + "=" + refTableName + "." + factory.DealPropertyName(fkAttr.RefColumnName()) + ") as " + factory.DealPropertyName(columnName + "_" + fkAttr.Name() + "_" + fkAttr.AssociaField()));
                //如果有拉取字段1，查询拉取字段
                if (fkAttr.AssociaField1() != "")
                {
                    sb.append("," + "(select " + factory.DealPropertyName(fkAttr.AssociaField1()) + " from " + refTableName + " where ti." + factory.DealPropertyName(columnName) + "=" + refTableName + "." + factory.DealPropertyName(fkAttr.RefColumnName()) + ") as " + factory.DealPropertyName(columnName + "_" + fkAttr.Name() + "_" + fkAttr.AssociaField1()));
                }
                //如果有拉取字段2，查询拉取字段
                if (fkAttr.AssociaField2() != "")
                {
                    sb.append("," + "(select " + factory.DealPropertyName(fkAttr.AssociaField2()) + " from " + refTableName + " where ti." + factory.DealPropertyName(columnName) + "=" + refTableName + "." + factory.DealPropertyName(fkAttr.RefColumnName()) + ") as " + factory.DealPropertyName(columnName + "_" + fkAttr.Name() + "_" + fkAttr.AssociaField2()));
                }
            }
        }
        sb.append(" from " + factory.DealTableName(tableInfo.TableInfo.Name()) + " ti ");

        //组装查询参数
        if(para!=null&&para.size()>0)
        {
            sb.append(" where ");
            for(int i=0;i<para.size();i++)
            {
                String oper="=";
                if(operList!=null&&operList.size()>i)
                {
                    oper=operList.get(i);
                }
                String link="and";
                if(operList!=null&&operList.size()>i-1)
                {
                    link=operList.get(i-1);
                }
                if(i==0) {
                    sb.append(factory.DealPropertyName(para.get(i).Key.toString()) + oper + factory.DealSqlPara(para.get(i).Key.toString()));
                }
                else
                {
                    sb.append(" "+link+" "+factory.DealPropertyName(para.get(i).Key.toString()) + oper + factory.DealSqlPara(para.get(i).Key.toString()));
                }
            }
        }

        //存组装的排序sql
        String strSort = "";
        //如果传入了排序字段，组装排序语句
        if (orderBySql != null && orderBySql.length() > 0)
        {
            //用来存处理的排序串
            String dealStr = "";
            //先分割多个排序条件
            String[] strList = orderBySql.split(",");
            for (int m = 0; m < strList.length; m++)
            {
                //分开多个排序条件
                if (m > 0)
                {
                    dealStr += ",";
                }
                //分开字段名称和升降序
                String[] strSubList = strList[m].split(" ");
                //处理字段名称
                dealStr += factory.DealPropertyName(strSubList[0]);
                //组装处理的串
                for (int n = 1; n < strSubList.length; n++)
                {
                    dealStr += " " + strSubList[n];
                }
            }
            //组装排序串
            strSort = " Order By " + dealStr;
        }
        else
        {
            if (HasSequence)
            {
                strSort = " Order By " + factory.DealPropertyName("Sequence") + " ASC";
            }
            else if (HasSeqNum)
            {
                strSort = " Order By " + factory.DealPropertyName("SeqNum") + " ASC";
            }
            else
            {
                strSort = "";
            }
        }
        sb.append(strSort);
        return sb.toString();
    }

    //通过实体类型得到实体表名称
    private static String GetTableName(Class c)
    {
        //存表名
        String strTableName = "";
        //得到表特性
        TableAttribute tableInfo = null;
        //得到表特性
        Annotation[] annoList = c.getAnnotations();
        if (annoList != null && annoList.length > 0) {
            for (int i = 0; i < annoList.length; i++) {
                //表特性
                if (annoList[i] instanceof TableAttribute) {
                    tableInfo = (TableAttribute) annoList[i];
                    strTableName=tableInfo.Name();
                    break;
                }
            }
        }
        return strTableName;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214

定义的表信息实体
解析的列信息

package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.FrekeyAttribute;
import java.lang.reflect.Type;

//列信息
public class ColumnInfo {
    //存外键信息
    public FrekeyAttribute FkInfo=null;

    //存列名
    public String Name;

    //存列结果
    public Object Value;

    //存列的类型
    public Type ColType;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

解析的id信息

package LIS.DAL.ORM.Common;

//存主键信息
public class IdInfo {
    /// 

    /// 键值ID
    /// 
    public String Key;

    /// 

    /// 键值数据
    /// 
    public Object Value;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

解析的表信息

package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.*;
import java.util.*;
import LIS.DAL.ORM.Common.ColumnInfo;

//存解析的表新
public class TableInfo {
    //存唯一的特性
    public List<UniqueAttribute> UniqueList=new ArrayList<UniqueAttribute>();

    //表特性
    public TableAttribute TableInfo=null;

    //主键信息
    public LIS.DAL.ORM.Common.IdInfo ID=null;

    //存列信息
    public List<ColumnInfo> ColList=new ArrayList<ColumnInfo>();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

测试

控制台测试

package com.company;
//import org.apache.commons.configuration.ConfigurationException;
//import org.apache.commons.configuration.PropertiesConfiguration;
import LIS.Model.Entity.SYSForm;
import LIS.Model.Entity.SYSUser;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import java.io.*;
import java.net.URL;

public class Main {

    public static void main(String[] args) {
        //用容器的配置xml初始化容器
        LIS.Core.Context.ObjectContainer.InitIoc();
        //ORM通过容器取数据库驱动工厂
        LIS.DAL.ORM.EntityManager.EntityManagerImpl orm=new LIS.DAL.ORM.EntityManager.EntityManagerImpl();
        //执行查询测试
        orm.DBSelectTest();
        //测试通过实体得到SQL语句
        orm.InsertSqlTest(new SYSForm());
        orm.InsertSqlTest(new SYSUser());
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

测试查询外键的sql

select "RowID","Code","CName","SysCode","Sequence","Active","Url","Path","Remark","FormHelp","HelpWidth","HelpHeight","HelpDoc","SystemSubDR",(select "RowID" from dbo.SYS_SystemSub where ti."SystemSubDR"=dbo.SYS_SystemSub."RowID") as "SystemSubDR_SYSSystemSub_RowID",(select "" from dbo.SYS_SystemSub where ti."SystemSubDR"=dbo.SYS_SystemSub."RowID") as "SystemSubDR_SYSSystemSub_",(select "" from dbo.SYS_SystemSub where ti."SystemSubDR"=dbo.SYS_SystemSub."RowID") as "SystemSubDR_SYSSystemSub_" from dbo.SYS_Form ti  Order By "Sequence" ASC

select "RowID","Code","CName","Password","HospitalDR",(select "CName" from dbo.BT_Hospital where ti."HospitalDR"=dbo.BT_Hospital."RowID") as "HospitalDR_BTHospital_CName",(select "" from dbo.BT_Hospital where ti."HospitalDR"=dbo.BT_Hospital."RowID") as "HospitalDR_BTHospital_",(select "" from dbo.BT_Hospital where ti."HospitalDR"=dbo.BT_Hospital."RowID") as "HospitalDR_BTHospital_","LanguageDR",(select "CName" from dbo.SYS_Language where ti."LanguageDR"=dbo.SYS_Language."RowID") as "LanguageDR_SYSLanguage_CName",(select "" from dbo.SYS_Language where ti."LanguageDR"=dbo.SYS_Language."RowID") as "LanguageDR_SYSLanguage_",(select "" from dbo.SYS_Language where ti."LanguageDR"=dbo.SYS_Language."RowID") as "LanguageDR_SYSLanguage_","SecurityType","PIN","Remark","Sequence","Active","PositionTitleDR",(select "CName" from dbo.BT_PositionTitle where ti."PositionTitleDR"=dbo.BT_PositionTitle."RowID") as "PositionTitleDR_BTPositionTitle_CName",(select "" from dbo.BT_PositionTitle where ti."PositionTitleDR"=dbo.BT_PositionTitle."RowID") as "PositionTitleDR_BTPositionTitle_",(select "" from dbo.BT_PositionTitle where ti."PositionTitleDR"=dbo.BT_PositionTitle."RowID") as "PositionTitleDR_BTPositionTitle_","UserType","UKeyNo","SignImage","Theme","MenuModel","PermissionDR",(select "CName" from dbo.SYS_Permission where ti."PermissionDR"=dbo.SYS_Permission."RowID") as "PermissionDR_SYSPermission_CName",(select "" from dbo.SYS_Permission where ti."PermissionDR"=dbo.SYS_Permission."RowID") as "PermissionDR_SYSPermission_",(select "" from dbo.SYS_Permission where ti."PermissionDR"=dbo.SYS_Permission."RowID") as "PermissionDR_SYSPermission_" from dbo.SYS_User ti  Order By "Sequence" ASC

1
2
3
4

支持SQL组装的基础雏形完成了，Maven的依赖实在是太无语了，一会儿IDE提示代码不行的时候Maven编译又可以。一会儿IDE提示可以的时候Maven有编译不行，摸不着规律，工程换Jboss工程了，工程引用浪费大量时间，弃用Maven工程后引用依赖稳定了