mybatis使用char类型字段查询oracle数据库时结果返回null

阳光穿透心脏的1/2处 2022-06-10 08:19 227阅读 0赞

同事在学mybatis时，遇到了一个问题就是，使用char类型字段作为查询条件时一直都查不出数据，其他类型的则可以。  
使用的数据库是oracle，查询条件字段类型是char(50)，java代码对应的是String类型。  
后来经过排查，是由于在oracle中，char类型字段，如果内容长度不够，会自动以空格方式补足长度。如字段 `name char(5)`，若值为`sgl`，那么oracle会自动用空格补足长度，最终值为`sgl`。

# 一、解决方法： #

方法1：先用trim()函数把值去掉两边空格再作为条件查询，如：

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

改为：

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

方法2：将字段类型char()改为varchar2()类型。一般情况下，只有所有值长度都一样时才用char()类型，比如性别字段，用0表示男和1表示女时，就可以用char(1)，如果值的长度不固定，有长有短，最好别用char()类型。

# 二、深入了解mybatis返回null #

抛开mybatis框架，回到原始的jdbc查询，当使用oracle的char类型作为条件查询数据时，只有值完全一样时才能查到数据。  
如创建一个测试表：

create table t_user( user_name char(5) );
    insert into t_user (user_name)values('sgl');
    
    select '"'||user_name||'"' from t_user; -- 查询结果为"sgl ",可以看出oracle自动补了两个空格

通过jdbc的PreparedStatement方式查询数据：

conn=getConnection();
    ps=conn.prepareStatement("select * from t_user where user_name=?");
    ps.setString(1,"sgl");
    ResultSet rs = ps.executeQuery();

通过上面方式是无法查到数据的，因为查询条件值”sgl”和数据库中值”sgl “是不相等的。  
如果值用“sgl ”可以查到数据：

conn=getConnection();
    ps=conn.prepareStatement("select * from t_user where user_name=?");
    ps.setString(1,"sgl "); -- 增加两个空格不足5位长度
    ResultSet rs = ps.executeQuery();

如果使用trim()方式也可以查询到数据，如：

conn=getConnection();
    ps=conn.prepareStatement("select * from t_user where trim(user_name)=?"); -- 先对数据库中user_name进行去空格，然后再比较
    ps.setString(1,"sgl");
    ResultSet rs = ps.executeQuery();

现在回到mybatis，同事的Mapper文件里查询sql如下：

<select id="selectByName" resultType="com.entity.Data" parameterType="java.lang.String">
      select * from data where data.name=#{name}
    </select>

main方法内容为：

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);
    }

其实，通过查看源码或将日志改为debug级别，可以看出在mybatis底层，会将查询语句使用PreparedStatement预编译，然后再将参数设置进去。如下面是mybatis打印出来的日志：

==> Preparing: select * from data where data.name=? 
    ==> Parameters: sgl(String)

根据前面的jdbc查询，我们知道原因，所以很容易理解mybatis中的问题。

另外，mysql下面，当char类型字段的值不足时，好像并不自动将值以空格补足，尽管如此，当值长度不固定时，也不推荐使用char类型。

jdbc查询完整的代码如下：  
jdbc工具类：

package com.songguoliang.url;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.ResourceBundle;
    /** * 纯jdbc连接数据类 * @author sgl * */
    public class PureJdbcDao { 
        private static ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        private static int reCount = 0;
        /** * 获取连接 * @return */
        private static Connection getConnection(){
            Connection conn=null;
            try {
                Class.forName(bundle.getString("driverClassName"));
                conn = DriverManager.getConnection(bundle.getString("url") ,
                        bundle.getString("username") , bundle.getString("password"));
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                if(null==conn&&reCount<5){
                    try {
                        Thread.sleep(10000);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    reCount++;
                    System.out.println("数据库第"+reCount+"次重连");
                    conn = getConnection();
                }
            }
            return conn;
        }
        /** * 查询数据 * @param sql * @return */
        public static List<String[]>query(String sql){
            List<String[]>result=new ArrayList<String[]>();
            Connection conn=null;
            Statement stmt=null;
            try {
                //System.out.println("[PureJdbcDao]查询语句：" + sql);
                conn=getConnection();
                stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                ResultSetMetaData rsMeta = rs.getMetaData();
                while(rs.next()){
                    int columnNum=rsMeta.getColumnCount();
                    String []field=new String[columnNum];
                    String fieldValue=null;
                    for(int i=1;i<=columnNum;i++){
                        fieldValue=rs.getString(i);
                        if(fieldValue==null){
                            fieldValue="";
                        }
                        field[i-1]=fieldValue;
                    }
                    result.add(field);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                try {
                    if(stmt!=null){
                        stmt.close();
                    }
                    if(conn!=null){
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return result;
        }
        public static List<String[]>query(String sql,List<String>params){
            List<String[]>result=new ArrayList<String[]>();
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                conn=getConnection();
                ps=conn.prepareStatement(sql);
                for(int i=0;i<params.size();i++){
                    ps.setString(i+1,params.get(i));
                }
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData rsMeta = rs.getMetaData();
                while(rs.next()){
                    int columnNum=rsMeta.getColumnCount();
                    String []field=new String[columnNum];
                    String fieldValue=null;
                    for(int i=1;i<=columnNum;i++){
                        fieldValue=rs.getString(i);
                        if(fieldValue==null){
                            fieldValue="";
                        }
                        field[i-1]=fieldValue;
                    }
                    result.add(field);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                try {
                    if(ps!=null){
                        ps.close();
                    }
                    if(conn!=null){
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return result;
        }
        /** * 执行sql语句 * @param sql */
        public static void execute(String sql){
            Connection conn=null;
            Statement stmt=null;
            try {
                //System.out.println("[PureJdbcDao]sql语句：" + sql);
                conn = getConnection();
                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                stmt.execute(sql);
                conn.commit();
            } catch (SQLException e) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }finally{
                try {
                    if(stmt!=null){
                        stmt.close();
                    }
                    if(conn!=null){
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

测试类：

package com.songguoliang;
    import java.util.Arrays;
    import java.util.List;
    
    import com.songguoliang.url.PureJdbcDao;
    
    public class Test {
        public static void main(String[] args) {
            //List<String[]>list=PureJdbcDao.query("select * from t_user where user_name=?",Arrays.asList("sgl")); // 查询到条数：0
            //List<String[]>list=PureJdbcDao.query("select * from t_user where user_name=?",Arrays.asList("sgl ")); //查询到条数：1
            List<String[]>list=PureJdbcDao.query("select * from t_user where trim(user_name)=?",Arrays.asList("sgl")); //查询到条数：1
            System.out.println("查询到条数："+list.size());
        }
    }