Shulou(Shulou.com)06/02 Report--

Most people do not understand the knowledge points of this article "MYSQL inserts data to check whether the field values are duplicates or not", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at the article "MYSQL inserts data when checking whether the field values are duplicated."

Project requirements

There is a parking space information table of u_ps, corresponding to the parking space id ps_id of each district is self-increasing, each parking space is corresponding to the parking space number num, the parking space id is self-growing, and the parking space number can be manually added and set according to the situation of the district, but it cannot be repeated, and the parking space number cannot be repeated. This requires the code to verify whether the new num exists in the u_ps table when the parking information is added, throws an exception if it exists, and inserts if it does not exist.

Crux of the problem

Parking num can be repeated in different residential community_id, if separate design tables set unique constraints is feasible, but our need is to put all residential parking information in the same table, which involves the need to judge whether it is the same residential id when inserting a piece of data, num can not be repeated in the case of the same community_id.

1. Set constraints on the fields when the tables are separated

Our main key is parking id ps_id, our idea is to insert a piece of data, first query the parking information table, traverse whether the num in the new data exists, or whether the new num is empty in the table, throw an exception, otherwise perform the insert operation. This is code logic.

The most direct way in sql is to set a unique index (this does not conflict with the primary key)

Check num

Click on the index

This sets the num field to a unique index

Set the insert statement in the project xml file

The insert ignore into statement determines whether there are duplicate field values in the case of calling the primary key (unique index) field. If so, it ignores this operation and throws an exception (try {} catch () {}). If not, the insert operation is performed.

2. All operations are in the same table

We use code logic to implement without modifying the sql

The sql statement is the most basic insert statement:

Code logic:

Parse the code:

New a new Ps (parking information class) bean object, put the community_id and num in the data we inserted into the listps object, perform the sqlSession query operation on listps and put it into the list collection, and then check the two fields in the query result set. If it is empty, it means that there is no value for this field in the table, then insert it. If it is not empty, an exception is thrown and an exception ServiceException e is thrown again in catch. Here, set catch twice. ServiceException e is to return to the front end an exception with a duplicate parking number. If it is not set, the front end will only receive an exception message.

StringUtilMycc is the utility class we set up, and the isEmpty method under this class is a null operation.

Note: when judging null, you also need to ensure that the value of the field passed cannot be null. If you only judge whether the data in the table is empty, the final test result commands the null pointer!

This is the isEmpty method under my utility class:

Note: framework changes, configuration issues and other differences can not be synchronized, only as a reference

The above is about the content of this article on "what is the method to check whether the field values are duplicated when MYSQL inserts data". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

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.