Mysql動的SQL

mysql動的SQL
解決する

Error querying database.  Cause: org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement 'com.zwt.mapper.AddressesMapper.queryTrim'.  It's likely that neither a Result Type nor a Result Map was specified.

の質問
sql文に返されるタイプを追加します.

resultType="com.zwt.pojo.Addresses"

キャッシュ
2回のクエリで1回だけSQL文が呼び出され、2回目のキャッシュからヒットしました.

SqlSession sqlSession = MybatisUtil.getSession();
AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);
​
List addresses = mapper.listAll();
        System.out.println(addresses);
List addresses2 = mapper.listAll();
        System.out.println(addresses2);
        sqlSession.commit();
        sqlSession.close();

DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 7
 
キャッシュ失効方式
クエリー後に削除するとキャッシュが無効になります

SqlSession sqlSession = MybatisUtil.getSession(); 
AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);
List addresses = mapper.listAll();
//     ：             
Addresses a = new Addresses(); 
a.setId(2); 
a.setCountry("2"); 
a.setCity("3"); 
a.setState("4"); 
a.setZip("5"); 
mapper.update(a); 
List addresses2 = mapper.listAll(); 
sqlSession.commit(); 
sqlSession.close();

DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 7 DEBUG [main] - ==> Preparing: update addresses SET country = ?, city = ?, state = ?, zip = ? WHERE id = ? DEBUG [main] - ==> Parameters: 2(String), 3(String), 4(String), 5(String), 2(Long) DEBUG [main] - <== Updates: 1 DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters:
キャッシュの強制クリア

List addresses = mapper.listAll();
//          sqlSession.clearCache();
List addresses2 = mapper.listAll();
sqlSession.commit();
sqlSession.close();

DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 7 DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 7
 
動的SQL
動的SQL-if

<select id="queryByCountryCity" resultType="com.zwt.pojo.Addresses">    
    select * from addresses 
    <where> 
    <if test="country != null and city = '' "> and COUNTRY = #{country}if>                   
<if test="city != null">and city = #{city}if>                            
where>
select>

public void m3(){        
    SqlSession sqlSession = MybatisUtil.getSession();        
    AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);//                 
List addresses = mapper.queryByCountryCity(null, "Perry");//                 
List addresses = mapper.queryByCountryCity("China", null);                   
List addresses = mapper.queryByCountryCity("Taylor", "");                    
System.out.println(addresses);        
    sqlSession.close();

 
countryが空でなくcityが空である場合、countryクエリを介します.
cityが空でない場合はcityクエリ
動的SQL-ファジイクエリ
第1種
アプリケーションレベルで%を追加:

List<Addresses> addresses = mapper.queryLike("%Luo%");
select * from addresses where city like  #{city}

第2種
mysqlの関数concatで完了します.

select * from addresses where city like concat('%', #{city}, '%')

第2種
bandラベルで変数を再バインドし、新しい変数でクエリー

<bind name = "_city" value = " '%' + city + '%'">bind>
select * from addresses where city like  #{_city}

 
転載先:https://www.cnblogs.com/SuperT/p/11337890.html