In the previous article, abstraction of the database layer was implemented. As some readers have mentioned, HashMap is used as an input parameter, which does not comply with ORM standards. This is true, but the previous article abstracted the basic query and made it a hashMap as the input parameter. It only required the business query layer to implement the query of type Object during inheritance. Take a look at the schema diagram for database layer abstraction
This part, I will realize the business part of the database query as an example, the database layer further encapsulation.
A simple business example
Let’s use the above abstract query as an example of an organizational architecture scenario that exists in every system.
Build table
First, create the organization table SYS_organization
CREATE TABLE IF NOT EXISTS sys_organization (id bigint PRIMARY KEY DEFAULT random_id(), Org_name varchar(64) NOT NULL, org_type varchar(64) NOT NULL DEFAULT 'unit', Father_org_id bigint NOT NULL DEFAULT '0', -- Parent organization id father_org_name varchar(64) NOT NULL DEFAULT '', Social_code VARCHAR (64) NOT NULL DEFAULT... ...). ;Copy the codeStart defining the business layer base code
Using automatic code generation, the corresponding Model is generated as follows:
Public class SysOrganization {private Long id; Private String orgName; Private String orgType; Private Long fatherOrgId; // Parent organization id private String fatherOrgName; Private String socialCode; // Unified Social Taxpayer Identification number... . }Copy the codeThen we need to define the table, the query that inherits the base query, and the SysOrganizationQuery that inherits PostgreSQLBaseQuery.
public class SysOrganizationQuery extends PostgreSQLBaseQuery<SysOrganization> {
public SysOrganizationQuery(a) {
super(a);this.primaryKey = "id"; }... . }Copy the codeThen we can think about, what are the basic operations for a table?
- Query a single row of data by condition -> returns a single object
- Query paging multi-row data by condition -> return multiple objects
- Query all data by criteria
- Query total number by condition
- Conditional renewal
- Press the primary key to update
- A single new
- A batch of new
- Other basic methods of architecture adjustment according to the overall requirements of the project (this is precisely what the existing framework cannot achieve and do uniformly. When dealing with the scenario of permission control of row-level data in data security, if the code is not automatically generated uniformly in the database layer, the result will be a lot of special judgment)
Then we according to the above operations, to improve the SysOrganizationQuery, perfect, you can use the basic query we defined before.
public class SysOrganizationQuery extends PostgreSQLBaseQuery<SysOrganization> {
public SysOrganizationQuery(a) {
super(a);this.primaryKey = "id";
}
public Long count(SysOrganization sysOrganization) {
return this.countModelBySimpleAnd(Json.toMap(Json.toJson(sysOrganization)));
}
public List<SysOrganization> page(SysOrganization sysOrganization, Integer page, Integer size) {
return this.findListModelBySimpleAnd(Json.toMap(Json.toJson(sysOrganization)), page, size);
}
public Integer updateByCondition(SysOrganization condition, SysOrganization value) {
return this.update(Json.toMap(Json.toJson(condition)), Json.toMap(Json.toJson(value)));
}
public List<SysOrganization> findByCondition(SysOrganization condition) {
return this.findListModelBySimpleAnd(Json.toMap(Json.toJson(condition))); }}Copy the codeWell, the code for the business query is complete. As you may notice, the business Query can also be generated automatically using string substitution because the code is in the same form except for the object names.
Now, we need to blend in the abstract into the Spring projects, so you need to give SysOrganization creating data warehouse objects, so we create a SysOrganizationRepository is as follows:
public interface SysOrganizationRepository {
default SysOrganization findSysOrganizationById(Long id) {
SysOrganizationQuery query = new SysOrganizationQuery();
return query.findModelById(id);
}
default SysOrganization findSysOrganizationByCondition(SysOrganization condition) {
String json = Json.toJson(condition);
Map<String, Object> andCondition = Json.toMap(json);
SysOrganizationQuery query = new SysOrganizationQuery();
return query.findModelBySimpleAnd(andCondition);
}
default Long save(SysOrganization sysOrganization) {
SysOrganizationQuery query = new SysOrganizationQuery();
return query.insert(Json.toMap(Json.toJson(sysOrganization)));
}
default Integer saveAll(List<SysOrganization> sysOrganizations) {
List<Map<String, Object>> sysOrganizationMaps = new ArrayList<>();
for (SysOrganization sysOrganization : sysOrganizations) {
sysOrganizationMaps.add(Json.toMap(Json.toJson(sysOrganization)));
}
SysOrganizationQuery query = new SysOrganizationQuery();
return query.batchInsert(sysOrganizationMaps);
}
default List<SysOrganization> findAll(a) {
SysOrganizationQuery query = new SysOrganizationQuery();
List<Map<String, Object>> tmp = query.findAll();
List<SysOrganization> ans = new ArrayList<>();
for (Map<String, Object> x : tmp) {
ans.add(Json.toObject(Json.toJson(x), SysOrganization.class));
}
return ans;
}
default Integer update(SysOrganization condition, SysOrganization value) {
SysOrganizationQuery query = new SysOrganizationQuery();
return query.updateByCondition(condition, value);
}
default Long count(SysOrganization sysOrganization) {
SysOrganizationQuery sysOrganizationQuery = new SysOrganizationQuery();
return sysOrganizationQuery.count(sysOrganization);
}
default List<SysOrganization> page(SysOrganization sysOrganization, Integer page, Integer size) {
SysOrganizationQuery sysOrganizationQuery = new SysOrganizationQuery();
return sysOrganizationQuery.page(sysOrganization, page, size);
}
default Integer updateById(Long id, SysOrganization value) {
SysOrganization condition = new SysOrganization();
condition.setId(id);
SysOrganizationQuery query = new SysOrganizationQuery();
returnquery.updateByCondition(condition, value); }}Copy the codeYou can see if all the basic operations we mentioned above for a table can be done by calling a query class. As you may have noticed, all of the classes mentioned above can be generated automatically and, in the case of calls from the underlying service layer, also from objects. There is no case where hashMap is used as an input parameter.
Finally, if you want to implement specific queries, you just need to implement the interface repository as follows:
@Repository
public class SysOrganizationRepositoryImpl implements SysOrganizationRepository {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
}
Copy the codeFirst, you need to read and parse the SQL that created the table. I’m using PGSQL here, so I’m parsing for its syntax as well. Table objects and field objects need to be defined as follows:
static class Field {
String name;
String type;
String desc;
public Field(String name, String type, String desc) {
this.name = name;
this.type = type;
this.desc = desc; }}static class Table {
String name;
List<Field> fields;
String desc;
public Table(String name, List<Field> fields, String desc) {
this.name = name;
this.fields = new ArrayList<>();
this.fields.addAll(fields);
this.desc = desc; }}Copy the code
The parse code is as follows:
public static List<Table> getTableStrs(String fileName) {
List<Table> tables = new ArrayList<>();
try {
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(fileName)));
String line = "";
String tableName = "";
List<Field> fieldList = new ArrayList<>();
String tableDesc = "";
String tmpDesc = "";
while((line = bufferedReader.readLine()) ! =null) {
if (line.toUpperCase().startsWith("CREATE TABLE IF NOT EXISTS")) {
tableName = line.replace("CREATE TABLE IF NOT EXISTS"."").trim();
tableDesc = tmpDesc;
if (tableName.length() >= 32) {
System.out.println(tableName + "Table length > 32");
return new ArrayList<>();
}
fieldList = new ArrayList<>();
}
if (line.startsWith(* Table description:)) {
tmpDesc = line.replace(* Table description:."").trim();
}
if (line.startsWith("--")) {
tmpDesc = line.replace("--"."").trim();
}
if (line.startsWith("") || line.startsWith("\t")) {
line = line.trim();
String[] a = line.split("");
int cnt = 0;
if (a.length >= 3) {
String name = "";
String type = "";
String desc = "";
for (String b : a) {
b = b.trim();
if (StringUtils.hasText(b)) {
b = b.toLowerCase();
if (cnt == 0) {
name = b;
cnt += 1;
}
if (b.contains("bigint") || b.contains("int8")) {
type = "int8";
}
if (b.contains("int4") || b.contains("integer")) {
type = "int4";
}
if (b.contains("varchar") || b.contains("text")) {
type = "varchar";
}
if (b.contains("timestamp")) {
type = "timestamp";
}
if (b.contains("date")) {
type = "date";
}
if (b.contains("uuid")) {
type = "uuid";
}
if (b.contains("boolean") || b.contains("bool")) {
type = "boolean";
}
if (b.contains("float4")) {
type = "float4"; }}if (b.contains("--")) {
b = b.trim();
desc = b.replace("--".""); }}if (StringUtils.hasText(name) && StringUtils.hasText(type)) {
if (name.length() >= 32) {
System.out.println(name + "Field length greater than 32");
return new ArrayList<>();
}
fieldList.add(new Field(name, type, desc));
}
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return tables;
}
Copy the codeIn general, the length of a field should not be greater than 32 to accommodate scenarios where some databases have a length limit on column names. It’s too long and the readability gets worse, so there’s a special rule here.
With parsed code, we just need to implement the generated code. There are frameworks for this, but I prefer to implement it myself and iterate when things go wrong.
- To generate the Model
public static String getModelStr(Table table) {
String name = StringFormatUtils.snake(table.name, true);
String desc = "/ /" + table.desc + "\n";
StringBuilder ans = new StringBuilder().append(String.format("public class %s {\n", name));
List<String> types = new ArrayList<>();
for (Field field : table.fields) {
ans.append(String.format(" private %s %s; //%s \n", sqlTypeExchange2Java(field.type),
StringFormatUtils.snake(field.name, false),
field.desc));
types.add(sqlTypeExchange2Java(field.type));
}
for (Field field : table.fields) {
ans.append(String.format(" public %s get%s() {\n" +
" return %s; \n" +
" }\n", sqlTypeExchange2Java(field.type),
StringFormatUtils.snake(field.name, true),
StringFormatUtils.snake(field.name, false)));
ans.append(String.format(" public void set%s(%s %s) {\n" +
" this.%s = %s; \n" +
" }\n", StringFormatUtils.snake(field.name, true),
sqlTypeExchange2Java(field.type),
StringFormatUtils.snake(field.name, false),
StringFormatUtils.snake(field.name, false),
StringFormatUtils.snake(field.name, false)));
}
String importStr = importStr(types);
return importStr + "\n" + desc + ans.toString() + "\n" + "}";
}
Copy the code- To generate the Query
- Generate the Repository
- Generate RepositoryImpl
To generate these three, you just need to cut out the keywords and replace them with the size hump of the table name.
conclusion
This completes the abstraction of the entire database layer. At the same time, a large number of basic methods are automatically generated. When the data table is created, the above basic business code can be automatically generated by executing a command.
Of course, in order to accommodate more complex situations, the database layer abstraction is still small and needs to be iterated.