Entity properties were previously defined in LIS.Core, and table properties, attribute properties, foreign key properties, etc. were added to the entity class in LIS.Model. In order for ORM to implement add, delete, modify, and query parent table information with foreign keys, it needs to parse the Model’s characteristics and entity information to assemble SQL for the database driver to implement the add, delete, modify, and query function.
To implement the tool class for obtaining SQL from entities, first implement an example to obtain query 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;
//Get the SQL tool class through the entity type
public class ModelToSqlUtil {<!-- -->
//Get table information through entity object
public static TableInfo GetTypeInfo(Object model) {<!-- -->
if (model == null) {<!-- -->
return null;
}
try {<!-- -->
//returned object
TableInfo tableInfo = new TableInfo();
Class c = model.getClass();
System.out.println("111");
//Get table properties
Annotation[] annoList = c.getAnnotations();
if (annoList != null & amp; & amp; annoList.length > 0) {<!-- -->
for (int i = 0; i < annoList.length; i + + ) {<!-- -->
//table properties
if (annoList[i] instanceof TableAttribute) {<!-- -->
tableInfo.TableInfo = (TableAttribute) annoList[i];
}
// unique feature
else {<!-- -->
tableInfo.UniqueList.add((UniqueAttribute) annoList[i]);
}
}
}
//Get column information
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;
}
//Assemble query SQL through table information and parameter list
//factory: driver factory
//tableInfo: table information
//para:parameter
//operList: Operator list corresponding to the parameter
//linkList: operator for connecting multiple parameters and or this kind
//orderBySql:Order By SQL statement
//IsFk: Whether to assemble the SQL of foreign key query
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 ");
//Whether it contains a serial number column
boolean HasSequence=false;
boolean HasSeqNum=false;
//Assemble query column
for(int i=0;i<tableInfo.ColList.size();i + + )
{<!-- -->
//Save column name
String columnName = tableInfo.ColList.get(i).Name;
if(columnName=="Sequence")
{<!-- -->
HasSequence=true;
}
else if(columnName=="SeqNum")
{<!-- -->
HasSeqNum=true;
}
//Assemble query column
if(i==0)
{<!-- -->
sb.append(factory.DealPropertyName(columnName));
}
else
{<!-- -->
sb.append("," + factory.DealPropertyName(columnName));
}
//Assemble foreign key query information
if(IsFk==true & amp; & amp;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()));
//If there is pull field 1, query the pull field
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()));
}
//If there is pull field 2, query the pull field
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 ");
//Assemble query parameters
if(para!=null & amp; & amp;para.size()>0)
{<!-- -->
sb.append(" where ");
for(int i=0;i<para.size();i + + )
{<!-- -->
String oper="=";
if(operList!=null & amp; & amp;operList.size()>i)
{<!-- -->
oper=operList.get(i);
}
String link="and";
if(operList!=null & amp; & amp;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())) ;
}
}
}
//Save assembled sort sql
String strSort = "";
//If the sort field is passed in, assemble the sort statement
if (orderBySql != null & amp; & amp; orderBySql.length() > 0)
{<!-- -->
//Used to store the sorted string for processing
String dealStr = "";
//First split into multiple sorting conditions
String[] strList = orderBySql.split(",");
for (int m = 0; m < strList.length; m + + )
{<!-- -->
//Separate multiple sorting conditions
if (m > 0)
{<!-- -->
dealStr + = ",";
}
//Separate field names and ascending and descending order
String[] strSubList = strList[m].split(" ");
//Process field names
dealStr + = factory.DealPropertyName(strSubList[0]);
//Assemble the processed string
for (int n = 1; n < strSubList.length; n + + )
{<!-- -->
dealStr + = " " + strSubList[n];
}
}
//Assemble sort string
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();
}
//Get the entity table name through the entity type
private static String GetTableName(Class c)
{<!-- -->
//save table name
String strTableName = "";
//Get table properties
TableAttribute tableInfo = null;
//Get table properties
Annotation[] annoList = c.getAnnotations();
if (annoList != null & amp; & amp; annoList.length > 0) {<!-- -->
for (int i = 0; i < annoList.length; i + + ) {<!-- -->
//table properties
if (annoList[i] instanceof TableAttribute) {<!-- -->
tableInfo = (TableAttribute) annoList[i];
strTableName=tableInfo.Name();
break;
}
}
}
return strTableName;
}
}
Defined table information entities
Parsed column information
package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.FrekeyAttribute;
import java.lang.reflect.Type;
//Column information
public class ColumnInfo {<!-- -->
//Save foreign key information
public FrekeyAttribute FkInfo=null;
//Save column name
public String Name;
//Save column results
public Object Value;
//Type of storage column
public Type ColType;
}
Parsed id information
package LIS.DAL.ORM.Common;
//Save primary key information
public class IdInfo {<!-- -->
/// <summary>
///Key value ID
/// </summary>
public String Key;
/// <summary>
///Key value data
/// </summary>
public Object Value;
}
Parsed table information
package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.*;
import java.util.*;
import LIS.DAL.ORM.Common.ColumnInfo;
//Save the parsed table new
public class TableInfo {<!-- -->
//Save unique characteristics
public List<UniqueAttribute> UniqueList=new ArrayList<UniqueAttribute>();
//table properties
public TableAttribute TableInfo=null;
//Primary key information
public LIS.DAL.ORM.Common.IdInfo ID=null;
//Save column information
public List<ColumnInfo> ColList=new ArrayList<ColumnInfo>();
}
test
Console test
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) {<!-- -->
//Initialize the container with the container's configuration xml
LIS.Core.Context.ObjectContainer.InitIoc();
//ORM gets the database driver factory through the container
LIS.DAL.ORM.EntityManager.EntityManagerImpl orm=new LIS.DAL.ORM.EntityManager.EntityManagerImpl();
//Execute query test
orm.DBSelectTest();
//Test to get the SQL statement through the entity
orm.InsertSqlTest(new SYSForm());
orm.InsertSqlTest(new SYSUser());
}
}
Test sql for querying foreign keys
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
The basic prototype to support SQL assembly is completed. The dependency on Maven is really dumb. After a while, when the IDE prompts that the code is not working, Maven can compile it. After a while, when the IDE prompts that it is OK, Maven fails to compile, and I can’t figure out the pattern. The project is changed to Jboss project, and project references waste a lot of time. After the Maven project is abandoned, the reference dependencies are stable.