The method for assigning values to SQL fields unrelated to the business in the CRUD operation, crudsql

The method for assigning values to SQL fields unrelated to the business in the CRUD operation, crudsql

Improving efficiency has always been an eternal topic. One of the topics in programming can also mention efficiency, that is, focusing on one thing and separating other closely related tasks. Here we will share with you the Common Data Processing scenarios we encountered during CRUD:

• All database table fields are designed to be non-empty. Even if this field can be left blank for business purposes, the reason for designing all database table fields as non-empty is that it has advantages and disadvantages, we think the advantage is greater than the disadvantage, so we chose it.

Advantages:

1. When obtaining a value, you do not need to judge whether the field is null and can directly use it for logical operations.

2. mysql DBA recommends this solution, which may be conducive to performance. I have not verified it here.

Disadvantages:

1. The business meaning is not null clear. For example, if the default value of the int field is 0 or 0, the null semantics is not clear.

2. When Using ORM to insert data, you need to handle the problem that the non-empty field value is null.

• Assign values to system fields, such as the creator, creator id, creation time, creator, creator id, and editing time, which must be assigned to the Model before the database is actually inserted. These System fields are not closely related to specific services. They only indicate when the data is processed by the person. When these non-business-related codes flood the code, it seems redundant, and this type of code is redundant. The final result is a large proportion of non-key code.

We do not need to solve the above problem about the semantics of default values and null, because we think that the advantages brought by default values are much greater than the troubles caused by empty fields. Let's look at how the default values and system fields are generally handled:

• When operating the ORM, manually assign the default value to all the fields that can be empty in the model, and assign the value of int to 0.

• When designing a database, add non-empty fields with default values to allow the database to process these fields with no inserted values. If mybatis is used, the insert operations mentioned in mapper include insert, insertSelective: The insertSelective is used to process non-empty fields. That is, the inserted model maintains a null value for fields that do not need to be assigned a value, the SQL statement generated when the database is inserted does not contain these fields, so that the default value of the database can be used. If it happens that the database structure was not designed with a default value and cannot be changed, it would be worse. In this case, the manual assignment is returned, and the following code may appear: compile a function to parse each field through reflection. If it is null, change it to the default value:

public static <T> void emptyNullValue(final T model) {Class<?> tClass = model.getClass();List<Field> fields = Arrays.asList(tClass.getDeclaredFields());for (Field field : fields) {Type t = field.getType();field.setAccessible(true);try {if (t == String.class && field.get(model) == null) {field.set(model, "");} else if (t == BigDecimal.class && field.get(model) == null) {field.set(model, new BigDecimal(0));} else if (t == Long.class && field.get(model) == null) {field.set(model, new Long(0));} else if (t == Integer.class && field.get(model) == null) {field.set(model, new Integer(0));} else if (t == Date.class && field.get(model) == null) {field.set(model, TimeHelper.LocalDateTimeToDate(java.time.LocalDateTime.of(1990, 1, 1, 0, 0, 0, 0)));}} catch (IllegalAccessException e) {e.printStackTrace();}}} 

Then, call the function before the Code calls insert to solve the problem:

ModelHelper.emptyNullValue(request); 

How to process system fields? When creating and Editing data, you need to obtain the current user, and then update the Creator information and the edited user information based on the logic, we specially compile a reflection function to process system fields:

Note: The following system fields are identified by system conventions. For example, if creator is set as the creator, data compatibility can be performed based on different situations. if the system is well designed, generally, the style of all tables in a system should be the same.

public static <T> void buildCreateAndModify(T model,ModifyModel modifyModel,boolean isCreate){Class<?> tClass = model.getClass();List<Field> fields = Arrays.asList(tClass.getDeclaredFields());for (Field field : fields) {Type t = field.getType();field.setAccessible(true);try {if(isCreate){if (field.getName().equals(modifyModel.getcId())) {field.set(model, modifyModel.getUserId());}if (field.getName().equals(modifyModel.getcName())) {field.set(model, modifyModel.getUserName());}if (field.getName().equals(modifyModel.getcTime())) {field.set(model, new Date());}}if (field.getName().equals(modifyModel.getmId())) {field.set(model, modifyModel.getUserId());}if (field.getName().equals(modifyModel.getmName())) {field.set(model, modifyModel.getUserName());}if (field.getName().equals(modifyModel.getmTime())) {field.set(model, new Date());}} catch (IllegalAccessException e) {e.printStackTrace();}}}

Finally, before data processing, assign values to system fields by calling functions based on creation or editing. Such code is mixed in Business Code.

ModifyModel modifyModel = new ModifyModel();modifyModel.setUserId(getCurrentEmployee().getId());modifyModel.setUserName(getCurrentEmployee().getName());if (request.getId() == 0) {ModelHelper.buildCreateAndModify(request, modifyModel, true);deptService.insert(request);} else {ModelHelper.buildCreateAndModify(request, modifyModel, false);deptService.updateByPrimaryKey(request);}

We can use parameter injection to solve the problem. The concept of parameter injection is that after spring mvc receives the parameters in the foreground request, it further processes the received parameters to achieve the expected results. Let's create

ManageModelConfigMethodArgumentResolver, which needs to implement HandlerMethodArgumentResolver. This interface looks simple and contains two core methods:

• Determine whether a parameter needs to be injected. It is generally implemented by determining whether there are special annotations on the parameter. You can also add another parameter to determine whether it can be adjusted based on specific services, here, I only use special comments to determine whether parameter injection is required.

@Overridepublic boolean supportsParameter(MethodParameter parameter) {return parameter.hasParameterAnnotation(ManageModelConfig.class);}

• Parameter injection, which provides an extension entry, giving us the opportunity to further process received parameters.

@Overridepublic Object resolveArgument(MethodParameter parameter, ModelAndViewContainer mavContainer,NativeWebRequest webRequest, WebDataBinderFactory binderFactory) throws Exception {Object manageModel =getRequestResponseBodyMethodProcessor().resolveArgument(parameter, mavContainer, webRequest, binderFactory);ServletRequest servletRequest = webRequest.getNativeRequest(ServletRequest.class);Employee currentUser = (Employee) servletRequest.getAttribute(DEFAULT_ATTRIBUTE_GET_USER_FROM_REQUEST);if (null == currentUser){return manageModel;}ManageModelConfig parameterAnnotation = parameter.getParameterAnnotation(ManageModelConfig.class);ModelHelper.setDefaultAndSystemFieldsValue(manageModel, currentUser,parameterAnnotation.isSetDefaultFieldsValue());return manageModel;}

This function has several core Logics:

• Get the parameter object because we are processing the parameters of the ajax request. The simplest injection method is to get the actual parameters to process the default fields and system values through reflection. Ajax requests are slightly different from data binding submitted by form post. For details, refer to the parameter injection for dynamic search on the list page shared in the previous article (Dynamic conditional search on the list page ). You can use the following two objects to obtain the current request parameter object:

• RequestMappingHandlerAdapter

• RequestResponseBodyMethodProcessor

private RequestMappingHandlerAdapter requestMappingHandlerAdapter=null;private RequestResponseBodyMethodProcessor requestResponseBodyMethodProcessor = null;private RequestResponseBodyMethodProcessor getRequestResponseBodyMethodProcessor() {if(null==requestMappingHandlerAdapter){requestMappingHandlerAdapter=new RequestMappingHandlerAdapter();}if (null==requestResponseBodyMethodProcessor) {List<HttpMessageConverter<?>> messageConverters = requestMappingHandlerAdapter.getMessageConverters();messageConverters.add(new MappingJackson2HttpMessageConverter());requestResponseBodyMethodProcessor = new RequestResponseBodyMethodProcessor(messageConverters);}return requestResponseBodyMethodProcessor;} 

The following code can be used to retrieve the parameter object. In fact, spring mvc can re-parse the parameter.

Object manageModel =getRequestResponseBodyMethodProcessor().resolveArgument(parameter, mavContainer, webRequest, binderFactory); 

• How to obtain the current user? After successfully logging on to the system, we store the information of the current user in the request. Then, we can obtain the current user in the function, or use other methods, such as ThreadLocal and cache.

ServletRequest servletRequest = webRequest.getNativeRequest(ServletRequest.class);Employee currentUser = (Employee) servletRequest.getAttribute(DEFAULT_ATTRIBUTE_GET_USER_FROM_REQUEST); 

• Call the processing function to solve the default field and assign values to the system. You can decide whether to process the default field value based on the configuration.

ManageModelConfig parameterAnnotation = parameter.getParameterAnnotation(ManageModelConfig.class);ModelHelper.setDefaultAndSystemFieldsValue(manageModel, currentUser,parameterAnnotation.isSetDefaultFieldsValue());

Finally, start our parameter injection logic. Here we select to configure in xml:

<mvc:annotation-driven content-negotiation-manager="contentNegotiationManager"><mvc:argument-resolvers><bean class="cn.wanmei.party.management.common.mvc.method.annotation.ManageModelConfigMethodArgumentResolver"/></mvc:argument-resolvers></mvc:annotation-driven> 

Look at the call in action: you only need to add annotation @ ManageModelConfig before the parameter. If you need to handle the default value, set the option to true to enable the default value, the following implementation does not see any business-independent code.

@RequestMapping(value = "/addOrUpdateUser")@ResponseBodypublic Map<String, Object> addOrUpdateUser(@ManageModelConfig(isSetDefaultFieldsValue=true) EmployeeDto request) {Map<String, Object> ret = new HashMap<>();ValidateUtil.ValidateResult result= new ValidateUtil().ValidateModel(request);boolean isCreate=request.getId() == 0;try {if (isCreate){employeeService.insert(request);}else{employeeService.updateByPrimaryKey(request);}ret.put("data", "ok");}catch (Exception e){ret.put("err", e.getMessage());}return ret;}

HandlerMethodArgumentResolver can be customized to capture ajax request parameters. The reflection mechanism is used to dynamically assign values to system fields and fields that need to handle default values, avoiding manual intervention and streamlining the code, clean logic and unified troubleshooting. Note that these implementations are designed in combination with the current system. For example, if the id field is greater than 0, it indicates an update operation. If it is null or equal to or less than 0, it indicates creation, the system field is also a convention name.