Shulou(Shulou.com)05/31 Report--

This article mainly introduces the method of "improving the assignment of the SQL field of CRUD operation". In the daily operation, I believe that many people have doubts about the method of improving the assignment of SQL field of CRUD operation. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubt of "improving the method of SQL field assignment of CRUD operation". Next, please follow the editor to study!

Improving efficiency has always been an eternal topic, and one thing that can be mentioned in programming is to focus on one thing and separate other things that are not closely related to it. Here are some common data processing scenarios we encounter when doing CRUD:

Database table fields are all designed to be non-empty, even if this field can be empty in business. The reason why database table fields are all designed as non-empty is that there are both advantages and disadvantages. We think the advantages outweigh the disadvantages, so we chose it.

Advantages:

1. When getting the value, you don't have to judge whether the field is null or not, it can be used for logical operation directly.

2.mysql DBA recommends this solution, which may be good for performance, which I have not verified here.

Disadvantages:

1. The business meaning is not as clear as null. For example, if the default value of int field is set to 0, null is not as clear as it is.

two。 When inserting data using ORM, you need to deal with the problem that the non-empty field value is null.

System field assignments, such as creator, creator id, creation time, editor, editor id, editing time, etc., all need to be assigned to Model before actually inserting into the database. These system fields generally do not have much to do with specific business, but only to mark when and when the data is processed by whom. When these non-business-related codes are filled with code, it appears to be a bit redundant. Moreover, more such codes will show redundancy, and the final result is a large proportion of non-critical codes.

The above semantic problems about default values and null do not need to be solved, because we think that the advantages of default values far outweigh the troubles caused by nullable fields. Let's take a look at how to deal with default values and system fields in general:

When operating ORM, all nullable fields of the model are manually assigned to default values, int is assigned to 0, and so on.

When designing the database, add the default values to the non-empty fields and let the database handle the fields with no inserted values. If you use mybatis, there are two insertions mentioned in mapper: insert,insertSelective, and the latter insertSelective deals with non-empty fields, that is, the inserted model keeps null values for fields that do not need to be assigned, and the sql statement generated by the database does not contain these fields. In this way, you can take advantage of the default values in the database. If the structure of the database is designed without the default value and cannot be changed, it will be worse. If we go back to the above manual assignment, there may be code similar to the following: write a function to parse each field through reflection, and change it to the default value if it is null:

Public static void emptyNullValue (final T model) {Class tClass = model.getClass (); List 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 deal with system fields? when you create editing data, you need to get the current user, and then update the creator information and editor information according to the logic. We specially write a function of reflection mechanism to deal with system fields:

Note: the identification of the following system fields is realized by the system convention, such as the creator agreement as the creator, etc., which can be compatible with data according to different situations. if the system is well designed, the style of all tables under a system should be the same.

Public static void buildCreateAndModify (T model,ModifyModel modifyModel,boolean isCreate) {Class tClass = model.getClass (); List 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 the data processing, according to the creation or editing to call the function to assign values to the system field, this kind of code is mixed in the 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 solve this problem by parameter injection. The idea of parameter injection is that after the spring mvc receives the parameters requested by the foreground, it further processes the received parameters to achieve the desired results. Let's create

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

To determine whether it is a parameter that needs to be injected, it is generally achieved by judging whether there is a special note on the parameter, or you can add another parameter, which can be adjusted according to the specific business. Here, I only judge whether parameter injection is required based on whether there are special comments.

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

Parameter injection, which provides an extension entry that gives us the opportunity to further process the 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 logic:

Get the parameter object because we are dealing with the parameters requested by ajax, and the easiest way to inject them is to get the actual parameters to process the default fields and system values through reflection. The ajax request is slightly different from the data binding submitted by the form form post, as you can refer to the parameter injection of the dynamic search on the list page shared in the previous article (dynamic conditional search on the list page). Get the current request parameter object, which can be accomplished with the cooperation of the following two objects:

RequestMappingHandlerAdapter

RequestResponseBodyMethodProcessor

Private RequestMappingHandlerAdapter requestMappingHandlerAdapter=null;private RequestResponseBodyMethodProcessor requestResponseBodyMethodProcessor = null;private RequestResponseBodyMethodProcessor getRequestResponseBodyMethodProcessor () {if (null==requestMappingHandlerAdapter) {requestMappingHandlerAdapter=new RequestMappingHandlerAdapter ();} if (null==requestResponseBodyMethodProcessor) {List

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.