mysql 动态查询_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"

缓存

两次查询仅有一次调用SQL语句，第二次从缓存中命中。

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 = newAddresses();

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: 7DEBUG [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: 1DEBUG [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: 7DEBUG [main] - ==> Preparing: select country,state,city from addresses DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 7

动态SQL

动态SQL-if

select * from addresses

and COUNTRY = #{country}

and city = #{city}

public voidm3(){

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-模糊查询

第一种

在应用程序层面加入%：

Listaddresses = mapper.queryLike("%Luo%");

select * from addresses where city like #{city}

第二种

通过mysql的函数 concat 完成：

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

第二种

通过band标签对变量重新绑定，在用新的变量查询

select * from addresses where city like #{_city}