When my colleagues were learning mybatis, they encountered a problem that when they used char type fields as query criteria, they could not find the data all the time, while other types could.

The database used is Oracle, the query condition field type is char (50), and the Java code corresponds to string type.

Later, after investigation, it is because in Oracle, if the content length of char type field is not enough, it will automatically make up the length in the form of space. For example, if the value of the field name char (5) is SGL, Oracle will automatically make up the length with spaces, and the final value is SGL.

1.Solution:

Method 1: first use the trim() function to remove the spaces on both sides of the value, and then use it as a condition query

select * from data where data.name=#{name}

Change to:

select * from data where trim(data.name)=#{name}

Method 2: change the field type char() to varchar2(). In general, char () is only used when all values are of the same length, such as gender field. When 0 is used to represent male and 1 is used to represent female, char (1) can be used. If the length of the value is not fixed, it is better not to use char () type.

2.Learn more about mybatis returning null

Leave the mybatis framework aside and go back to the original JDBC query. When the char type of Oracle is used as the condition to query the data, the data can only be found when the value is exactly the same.

To create a test table:

create table t_user(

user_name char(5)

);

insert into t_user (user_name)values('sgl');

select '"'||user_name||'"' from  t_user;

–The query result is “SGL”, which shows that Oracle automatically fills in two spaces

Query data through Preparedstatement of JDBC

conn=getConnection();

ps=conn.prepareStatement("select * from t_user where user_name=?");

ps.setString(1,"sgl");

ResultSet rs = ps.executeQuery();

The data cannot be found through the above method, because the query condition value “SGL” and the database median value “SGL” are not equal.

If the value is “SGL”, the data can be found:

conn=getConnection();

ps=conn.prepareStatement("select * from t_user where user_name=?");

ps.setString(1,"sgl "); -- Add two spaces less than 5 digits in length

ResultSet rs = ps.executeQuery();

If you use the trim () method, you can also query the data, such as:

conn=getConnection();

ps=conn.prepareStatement("select * from t_user where trim(user_name)=?"); -- De-space the user_name in the database first, then compare

ps.setString(1,"sgl");

ResultSet rs = ps.executeQuery();

Now go back to mybatis and query the SQL in my colleague’s mapper file as follows:

select * from data where data.name=#{name}

The main method is as follows:

public static void main(String[] args) {
    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    DataService d = (DataService) ctx.getBean("dataServiceImpl");
    Data data = d.selectByName("sgl");
    System.out.println(data);
}

In fact, by looking at the source code or changing the log to the debug level, you can see that at the bottom of mybatis, the query statement will be precompiled with Preparedstatement, and then the parameters will be set in. For example, the following is the log printed by mybatis:

==> Preparing: select * from data where data.name=?

==> Parameters: sgl(String)

According to the previous JDBC query, we know the reason, so it’s easy to understand the problem in mybatis.

In addition, under mysql, when the value of char type field is insufficient, it doesn’t seem to automatically fill the value with spaces. However, when the value length is not fixed, char type is not recommended.