Set Correct Primary Key
Set Correct Primary Key
Primary key is an extremely important column. We believe that the primary key must be generated by the backend, not passed from the frontend. In a scenario where there is a form and multiple record items, if we want to modify record items, the frontend often needs to pass record item information. If an id exists, the id will be passed to the backend. But if the frontend passes an incorrect id, ordinary savable will cause the backend to misuse the current id, which will lead to a series of problems such as index fragmentation or disorder. So how should we handle this situation?
SaveEntitySetPrimaryKeyGenerator
For eq version 3.1.41+, when using savable, you can set the correct primary key for child items to ensure their accuracy.
For the case of one-to-many save custom collection savable example, we modify
@PostMapping("/update2")
@Transactional(rollbackFor = Exception.class)
@EasyQueryTrack
public Object update2(@RequestBody BankUpdateRequest request) {
SaveBank saveBank = easyEntityQuery.queryable(SaveBank.class)
.include(save_bank -> save_bank.saveBankCards())
.whereById(request.getId()).singleNotNull();
saveBank.setName(request.getName());
saveBank.setAddress(request.getAddress());
ArrayList<SaveBankCard> requestBankCards = new ArrayList<>();
for (BankUpdateRequest.InternalSaveBankCards saveBankCard : request.getSaveBankCards()) {
SaveBankCard bankCard = new SaveBankCard();
bankCard.setId(saveBankCard.getId());//Blindly using the id passed by the frontend will bring great risks
bankCard.setType(saveBankCard.getType());
bankCard.setCode(saveBankCard.getCode());
requestBankCards.add(bankCard);
}
saveBank.setSaveBankCards(requestBankCards);
easyEntityQuery.savable(saveBank).executeCommand();
return "ok";
}When we loop through the request.getSaveBankCards() objects, since this object is passed from the frontend, we cannot ensure that the frontend will not pass us random ids. So when the id does not exist in the database, that object is considered for insertion, but we need to assign the backend's value to the id of that object, rather than using the id passed from the frontend. Then we have the following two solutions:
- Use
PrimaryKeyGenerator - Use
SaveEntitySetPrimaryKeyGenerator
So specifically how should we use it?
MySaveEntitySetPrimaryKeyGenerator
First, we implement our interface SaveEntitySetPrimaryKeyGenerator and then replace the framework behavior
public class MySaveEntitySetPrimaryKeyGenerator implements SaveEntitySetPrimaryKeyGenerator {
@Override
public void setPrimaryKey(Object entity, ColumnMetadata columnMetadata) {
String id = UUID.randomUUID().toString().replaceAll("-", "");
columnMetadata.getSetterCaller().call(entity, id);
}
}Modify the Original Interface
@PostMapping("/update3")
@Transactional(rollbackFor = Exception.class)
@EasyQueryTrack
public Object update3(@RequestBody BankUpdateRequest request) {
SaveBank saveBank = easyEntityQuery.queryable(SaveBank.class)
.include(save_bank -> save_bank.saveBankCards())
.whereById(request.getId()).singleNotNull();
saveBank.setName(request.getName());
saveBank.setAddress(request.getAddress());
ArrayList<SaveBankCard> requestBankCards = new ArrayList<>();
for (BankUpdateRequest.InternalSaveBankCards saveBankCard : request.getSaveBankCards()) {
SaveBankCard bankCard = new SaveBankCard();
bankCard.setId(saveBankCard.getId());
bankCard.setType(saveBankCard.getType());
bankCard.setCode(saveBankCard.getCode());
//Will verify if the id from saveBankCard.getId() is in the current tracking context. If not and it needs to be inserted, it means this id should be replaced
easyEntityQuery.saveEntitySetPrimaryKey(bankCard);
requestBankCards.add(bankCard);
}
saveBank.setSaveBankCards(requestBankCards);
easyEntityQuery.savable(saveBank).executeCommand();
return "ok";
}This way, even if we use snowflake id, we don't have to worry about the frontend uploading ids like 1 or 2 for one-to-many child items, which would break the snowflake id's monotonicity and cause database index fragmentation.
Specific Scenario Description
First, this is our insert operation
@PostMapping("/create")
@Transactional(rollbackFor = Exception.class)
@EasyQueryTrack
public Object create() {
SaveBank saveBank = new SaveBank();
saveBank.setId("1");
saveBank.setName("ICBC");
saveBank.setAddress("City Plaza No. 1");
ArrayList<SaveBankCard> saveBankCards = new ArrayList<>();
saveBank.setSaveBankCards(saveBankCards);
SaveBankCard card1 = new SaveBankCard();
card1.setId("2");
card1.setType("Savings Card");
card1.setCode("123");
saveBankCards.add(card1);
SaveBankCard card2 = new SaveBankCard();
card1.setId("3");
card2.setType("Credit Card");
card2.setCode("456");
saveBankCards.add(card2);
easyEntityQuery.savable(saveBank).executeCommand();
return "ok";
}Request JSON Data
{
"id": "1",
"name": "ICBC",
"address": "City Plaza No. 1",
"saveBankCards": [
{
"id": "2",
"type": "Savings Card",
"code": "123"
},
{
"id": "3",
"type": "Credit Card",
"code": "456"
}
]
}When we need to modify, we might delete 2, and then add a new one
{
"id": "1",
"name": "ICBC",
"address": "City Plaza No. 1",
"saveBankCards": [
// {
// "id": "2",
// "type": "Savings Card",
// "code": "123"
// },
{
"id": "3",
"type": "Credit Card",
"code": "456"
},
{
"id": "4",
"type": "Debit Card",
"code": "789"
}
]
}At this time, we save again through the save method
SaveBank saveBank = easyEntityQuery.queryable(SaveBank.class)
.include(save_bank -> save_bank.saveBankCards())
.whereById(request.getId()).singleNotNull();
saveBank.setName(request.getName());
saveBank.setAddress(request.getAddress());
ArrayList<SaveBankCard> requestBankCards = new ArrayList<>();
for (BankUpdateRequest.InternalSaveBankCards saveBankCard : request.getSaveBankCards()) {
SaveBankCard bankCard = new SaveBankCard();
bankCard.setId(saveBankCard.getId());//Blindly using the id passed by the frontend will bring great risks
bankCard.setType(saveBankCard.getType());
bankCard.setCode(saveBankCard.getCode());
requestBankCards.add(bankCard);
}
saveBank.setSaveBankCards(requestBankCards);We will see that we construct a new object collection from the request data. bankCard.setId(saveBankCard.getId()); this line will make the frontend pass id. If the id already exists in the database, modify it. If it doesn't exist, insert it. So now the frontend passed a 4. Obviously we only had 2 and 3 before, so this 4 will be inserted by us. But what if I call the api interface through postman and pass a 99999? Then the program will also insert 99999. This is the result of all ORMs blindly trusting frontend ids for save. This result is fatal because all your subsequent ids might be between 3-99999, causing the id to no longer be monotonic and the index to no longer be monotonic. This will make inserts relatively slow and cause performance problems. The correct approach should be that the backend handles it. If this requested id is not in the database, the backend should generate a new id independently. This way we can prevent the problems we mentioned before. But how to implement this function? If users manually handle it, it is very complex. So eq thoughtfully implemented a function for everyone to set the correct key.
Modify the original code as follows
SaveBank saveBank = easyEntityQuery.queryable(SaveBank.class)
.include(save_bank -> save_bank.saveBankCards())
.whereById(request.getId()).singleNotNull();
saveBank.setName(request.getName());
saveBank.setAddress(request.getAddress());
ArrayList<SaveBankCard> requestBankCards = new ArrayList<>();
for (BankUpdateRequest.InternalSaveBankCards saveBankCard : request.getSaveBankCards()) {
SaveBankCard bankCard = new SaveBankCard();
bankCard.setId(saveBankCard.getId());
bankCard.setType(saveBankCard.getType());
bankCard.setCode(saveBankCard.getCode());
//Will verify if the id from saveBankCard.getId() is in the current tracking context. If not and it needs to be inserted, it means this id should be replaced
easyEntityQuery.saveEntitySetPrimaryKey(bankCard);
requestBankCards.add(bankCard);
}
saveBank.setSaveBankCards(requestBankCards);
easyEntityQuery.savable(saveBank).executeCommand();By adding easyEntityQuery.saveEntitySetPrimaryKey(bankCard); this line of code, the framework can process all keys of the current object through the context. If the current object is not tracked in the context, it will reassign its id. So what should users do if they want to handle this situation themselves?
Modify the above code key line as follows
// easyEntityQuery.saveEntitySetPrimaryKey(bankCard);
EntityState trackEntityState = easyEntityQuery.getTrackEntityState(bankCard);
if(trackEntityState==null){
bankCard.setId(UUID.randomUUID().toString());
}Because for a highly abstracted business system, the whole is abstracted. If you really encounter something that cannot be abstracted, then manually handle it through the above method.
Can It Be Set to Null
Of course, smart friends might ask, can I set it to null? This way the interceptor can automatically handle the id, and the primary key generator can also automatically handle the id.
Very good. For simple one-to-many business table systems, we can do this. Like the above SaveBankCard object, if your id doesn't exist in the database, using null directly is no problem.
But if your object is a multi-level nested structure, then this might not be a good method.
a:{
id:'',
name:'',
b:[ ]
}
b:{
id:'',
name:'',
aid:'',
c:[ ]
}
c:{
id:'',
name:'',
aid:'',
bid:''
}When we process a.id, if your association relationship is set to a and b:a.id=b.aid, b and c:b.id=c.bid, then b's aid and c's bid will be automatically assigned. But for redundant fields, c.aid will not be automatically assigned and will always be null because c.aid doesn't appear in the association relationship,
Unless the user sets b and c:b.aid=c.aid&&b.id=c.bid
So whether to use null or use backend-created values is a question that depends on the different levels of project standardization and customization. For highly engineered projects, there is definitely a high-level abstraction concept. Personally, I think to reduce cognitive burden, it is recommended to directly assign all code blocks and construct the corresponding collection objects.