Problems encountered in using mybatis + postgreSql

Problem 1: The field name does not exist

The background is: there is a problem when the insert unit test of mapper is executed. There is a problem when the following statement is executed:

 Test entity = new Test();
entity.setName(new RandomString().nextString());
entity.setFatherTestId(new RandomString().nextString());

Integer insNum = testMapper.insert(entity);

Here, the insert method that comes with mapper is used to insert data into the database.

After inserting with mapper, the following error was reported: The field name does not exist.

But go to the data table to check, there is actually the field fatherTestId .

Just wondering 为什么后台会找不到这个字段 ?

---

And carefully look at the red error, the background says fatherid this field cannot be found.
Here I found the clue. The table in the database is defined as fatherTestId, and the letters i and T are capitalized.

Guess it's due to database field capitalization issues .

After going to Google, I found the answer:

Reason: PostgreSQL is case-sensitive for table and field names . When executing SQL, no matter whether the field in your SQL is uppercase or lowercase, it will finally be converted to lowercase by default for execution.

solve:

1. Change the database field names to lowercase. Or split with underscore _.
It works fine after changing to underscore later

2. When writing sql statements, add double quotes to the table name or field name

When defining SQL, add double quotes to the table name and field name to prevent uppercase fields from being converted to lowercase for execution.

Because in PGSQL, one of the double-quote erasure and uppercase-to-lowercase SQL statement is executed by default , and the double-quote erasure has a higher priority . Therefore, when the uppercase field is added with double-quotes, only the double-quote erasure is performed. processed without being converted to lowercase.

For example, add double quotes.

---

Finally, because I use the insert function that comes with mapper to insert data , I didn't write the sql statement myself.
So in the end, the first solution was adopted.

Question 2: The char(32) type field, the queried data has spaces

 Integer insNum = testMapper.insert(entity);
Assert.isTrue(insNum.equals(1), "insert success");

String id = entity.getId();
Test getEntity = testMapper.selectById(id);;
Assert.isTrue(getEntity.getName().equals(entity.getName()), "name not equal");

During the background unit test, an assertion is made: the inserted data is the same as the queried data.

That is to say, insert a piece of data into the database, and then query it, and judge that the attributes of the two data are the same.

But the unit tests never pass .

In the code breakpoint test, it is found that the data queried from the database has spaces . As shown below.

The guess is because postgresql will automatically supplement the data length with spaces to 32 bits when saving.

As a result, when the data is queried with the selectById method that comes with testMapper, there will be spaces.

If this is the case, after querying the data with mapper, you need to use the trim() function to remove the spaces one by one.

At the same time, I found a suggestion to use postgreSql, which also mentioned some disadvantages of using char(n).
https://juejin.cn/post/6844903917478739981#heading-41

---

Also mentioned not to use capital letters for table names

Basic use of mybatis + spring boot

Take the new example as an example:

Controller layer: similar to the previous use, all call the service layer directly

 @Autowired
  DictionaryService dictionaryService;

  @ApiOperation(value = "新建字典")
  @PostMapping("")
  public Dictionary addDictionary(@RequestBody Dictionary dictionary) {
   return dictionaryService.saveDictionary(dictionary);
  }

Serivce layer:

 @Service
public class DictionaryServiceImpl extends ServiceImpl<DictionaryMapper, Dictionary> implements DictionaryService {

  @Autowired
  DictionaryMapper dictionaryMapper;

  @Override
  public Dictionary saveDictionary(Dictionary dictionary) {
    Integer insNum = dictionaryMapper.insert(dictionary);
    Assert.isTrue(insNum.equals(1), "insert success");

    String id = dictionary.getId();
    Dictionary getDictionary = dictionaryMapper.selectById(id);
    return  getDictionary;
  }

When using the built-in insert function of Mapper, the insert function returns: the number of affected data bars after inserting the data. Instead of jpa, it returns the inserted entity.

So, how can I make it return the inserted entity?

During unit testing, it was found that

Before executing the insert function, the id of the entity is null. After executing the insert function, the id of the entity has a value.

So it can be guessed that after executing the insert function, the value of the database will be synchronized to the entity. That is, we get the id

Even if we are not at ease, since we have obtained the value of id. We can also use the selectById function to query the database again to make sure it is the value stored by the database.

 String id = dictionary.getId();
Dictionary getDictionary = dictionaryMapper.selectById(id);

The overall unit test is as follows:

 @Autowired
  DictionaryMapper dictionaryMapper;

  Dictionary getDictionary() {
    Dictionary dictionary = new Dictionary();
    dictionary.setKey(new RandomString().nextString());
    dictionary.setRemarks(new RandomString().nextString());
    dictionary.setValue(new RandomString().nextString());
    dictionary.setName(new RandomString().nextString());

    return dictionary;
  }

  @Test
  @DisplayName("插入一条记录并获取")
  void insertOne() {
    Dictionary dictionary = getDictionary();

    Integer insNum = dictionaryMapper.insert(dictionary);
    Assert.isTrue(insNum.equals(1), "insert success");

    String id = dictionary.getId();
    Dictionary getDictionary = dictionaryMapper.selectById(id);

    Assert.isTrue(getDictionary.getName().trim().equals(dictionary.getName()), "Name not equal");
    Assert.isTrue(getDictionary.getRemarks().trim().equals(dictionary.getRemarks()), "remarks not equal");
    Assert.isTrue(getDictionary.getValue().trim().equals(dictionary.getValue()), "value not equal");
    Assert.isTrue(getDictionary.getKey().trim().equals(dictionary.getKey()), "key not equal");

  }

Custom comprehensive query

Dao layer: Define fuzzy query based on name to get all entities

 public interface DictionaryMapper extends BaseMapper<Dictionary> {

  List<Dictionary> getByName(String name);

}

xml file:
Using xml query, the difference from jpa is that you need to write sql statement yourself. Although inconvenient, it leaves us a lot of room for manipulation.

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.record.server.mapper.DictionaryMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.record.server.model.Dictionary">
        <id column="id" property="id" />
        <result column="key" property="key" />
        <result column="name" property="name" />
        <result column="remarks" property="remarks" />
        <result column="value" property="value" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, key, name, remarks, value
    </sql>

    <select id="getByName" resultMap="BaseResultMap">
        select *
        from t_dictionary
        WHERE name LIKE '%${name}%'
    </select>


</mapper>