When my colleague was studying mybatis, he encountered a problem that when using the char type field as query condition, he could not find the data, and other types of them were OK.
The database used is oracle, the query condition field type is char(50), and the Java code corresponds to the String type.
Later, after troubleshooting, it was because in oracle, if the content length of the char type field is not enough, the length will be automatically filled in the form of space. If the field name char(5), if the value is sgl, then oracle will automatically fill 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 conditional query, such as:
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() type. Generally speaking, char() type is used only when all values have the same length. For example, when the gender field 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 long and short, it is best not to use char() type.
2. Deeply understand mybatis returns null
Putting aside the mybatis framework, back to the original jdbc query, when using the char type of oracle as a condition to query data, the data can only be found when the values are exactly the same.
For example, 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", it can be seen that oracle automatically fills two spaces
Query data through jdbc's PreparedStatement method:
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 value "sgl" are not equal.
If the value is "sgl" you can find the data:
conn=getConnection();ps=conn.prepareStatement("select * from t_user where user_name=?");ps.setString(1,"sgl "); -- Add two spaces less than 5 bits in length ResultSet rs = ps.executeQuery();If you use trim() method, you can also query the data, such as:
conn=getConnection();ps=conn.prepareStatement("select * from t_user where trim(user_name)=?"); -- First despace the user_name in the database, and then compare ps.setString(1,"sgl");ResultSet rs = ps.executeQuery();Now back to mybatis, the colleague's Mapper file is as follows:
<select id="selectByName" resultType="com.entity.Data" parameterType="java.lang.String"> select * from data where data.name=#{name}</select>Main method content is:
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 viewing the source code or changing the log to debug level, it can be seen that at the bottom of mybatis, the query statement will be precompiled using PreparedStatement, and then the parameters will be set in. As follows, the log printed by mybatis:
==> Preparing: select * from data where data.name=?
==> Parameters: sgl(String)
Based on the previous jdbc query, we know the reason, so it is easy to understand the problem in mybatis.
In addition, under mysql, when the value of the char type field is insufficient, it seems that the value is not automatically filled with spaces. Despite this, when the value length is not fixed, it is not recommended to use the char type.
The complete code for jdbc query is as follows:
jdbc tool class:
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;/** * Pure jdbc connection data class* @author sgl * */public class PureJdbcDao { private static ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); private static int reCount = 0; /** * Get the connection* @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("Database"+reCount+"Second reconnect"); conn = getConnection(); } } return conn; } /** * Query data* @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] query statement: " + 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; } /** * Execute sql statement * @param sql */ public static void execute(String sql){ Connection conn=null; Statement stmt=null; try { //System.out.println("[PureJdbcDao]sql statement: " + 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(); } } }}Test class:
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")); // Number of entries found: 0 //List<String[]>list=PureJdbcDao.query("select * from t_user where user_name=?",Arrays.asList("sgl ")); //Query the number of entries: 1 List<String[]>list=PureJdbcDao.query("select * from t_user where trim(user_name)=?",Arrays.asList("sgl")); //Query the number of entries: 1 System.out.println("Query the number of entries: "+list.size()); }}Summarize
The above is the solution introduced by the editor to solve the problem of null when mybatis uses the char type field to query the oracle database. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!