动态分页查询
public class BaseDao{
private SqlSessionTemplate session;
/**
* 分页查询
* @param listSql 当前页数据的SQL
* @param countSql 总条数SQL
* @param queryMap 动态条件
* @param pageNO 页码
* @param pageCount 每页显示条数
* @return
* @throws Exception
*/
public CutPageBean cutPage(String listSql,String countSql,Map queryMap,int pageNO,int pageCount)throws Exception{
}
private SqlSessionTemplate session;
/**
* 分页查询
* @param listSql 当前页数据的SQL
* @param countSql 总条数SQL
* @param queryMap 动态条件
* @param pageNO 页码
* @param pageCount 每页显示条数
* @return
* @throws Exception
*/
public CutPageBean cutPage(String listSql,String countSql,Map queryMap,int pageNO,int pageCount)throws Exception{
}
mybatis支持动态SQL拼接,通过include标签可以链接指定的sql标签
<select id="cutFindAllUser" resultType="user">
select * from t_user where 1=1
<include refid="dynaUserSql"/>
limit #start#,#pageCount#;
</select>
<select id="countUser" resultType="int">
select count(*) from t_user where 1=1
<include refid="dynaUserSql"/> ;
</select>
<select id="cutFindAllUser" resultType="user">
select * from t_user where 1=1
<include refid="dynaUserSql"/>
limit #start#,#pageCount#;
</select>
<select id="countUser" resultType="int">
select count(*) from t_user where 1=1
<include refid="dynaUserSql"/> ;
</select>
<sql id="dynaUserSql">
<if test="userName != null and userName != ''">
and userName like '%${userName}%'
</if>
<if test="birthday != null">
<![CDATA[
and birthday > #{birthday}
]]>
</if>
</sql>
<if test="userName != null and userName != ''">
and userName like '%${userName}%'
</if>
<if test="birthday != null">
<![CDATA[
and birthday > #{birthday}
]]>
</if>
</sql>
mybatis注解
使用mybatis注解开发,可以省去类配置文件,简洁方便。但是比较复杂的SQL和动态SQL还是建议书写类配置文件。
<!-- 定义接口,在接口方法中直接书写SQL -->
public interface IUserDao {
@Insert("insert into t_user(userName,classId) values(#{userName},#{classId})")
public void add(UserBean bean) throws Exception;
@Delete("delete from t_user where id=#{id}")
public void del(int id) throws Exception;
<!-- 定义接口,在接口方法中直接书写SQL -->
public interface IUserDao {
@Insert("insert into t_user(userName,classId) values(#{userName},#{classId})")
public void add(UserBean bean) throws Exception;
@Delete("delete from t_user where id=#{id}")
public void del(int id) throws Exception;
//读文件
String resource = "mybatis.cfg.xml";
Reader reader = null;
try {
reader = Resources.getResourceAsReader(resource);
} catch (IOException e1) {e1.printStackTrace();}
//创建会话工厂
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
//创建会话
session = ssf.openSession();
//得到接口对象
IUserDao dao = session.getMapper(IUserDao.class);
//直接调用业务方法即可完成业务逻辑
dao.insert(new UserBean("ff",9,"tt"));
String resource = "mybatis.cfg.xml";
Reader reader = null;
try {
reader = Resources.getResourceAsReader(resource);
} catch (IOException e1) {e1.printStackTrace();}
//创建会话工厂
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
//创建会话
session = ssf.openSession();
//得到接口对象
IUserDao dao = session.getMapper(IUserDao.class);
//直接调用业务方法即可完成业务逻辑
dao.insert(new UserBean("ff",9,"tt"));
编写注解方式的DAO接口
package com.lovo.dao;
public interface IManDao {
@Insert("insert into t_man(manName,birthday) values(#{manName},#{birthday})")
public void add(ManBean bean)throws Exception;
@Select("select * from t_man")
public List<ManBean> findAll()throws Exception;
}
package com.lovo.dao;
public interface IManDao {
@Insert("insert into t_man(manName,birthday) values(#{manName},#{birthday})")
public void add(ManBean bean)throws Exception;
@Select("select * from t_man")
public List<ManBean> findAll()throws Exception;
}
自动扫描指定包中的接口
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lovo.dao" />
</bean>
自动扫描指定包中类的注解
<context:component-scan base-package="com.lovo.service.impl"></context:component-scan>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lovo.dao" />
</bean>
自动扫描指定包中类的注解
<context:component-scan base-package="com.lovo.service.impl"></context:component-scan>
@Transactional(readOnly=true) 配置只读事务
public class ManServiceImpl extends SqlSessionDaoSupport implements IManService {
@Autowired 自动装配
private IManDao dao;
@Transactional(readOnly=false,rollbackFor=Exception.class) 配置回滚事务
public void add(ManBean bean) throws Exception {
this.dao.add(bean);
}
}
public class ManServiceImpl extends SqlSessionDaoSupport implements IManService {
@Autowired 自动装配
private IManDao dao;
@Transactional(readOnly=false,rollbackFor=Exception.class) 配置回滚事务
public void add(ManBean bean) throws Exception {
this.dao.add(bean);
}
}
<resultMap type="Lock" id="lockWithKeyMap"><!-- 查询锁和钥匙的 结果返回集定义映射 -->
<result property="lock_id" column="lock_id" javaType="java.lang.Integer"/>
<result property="lcok_name" column="lock_name" javaType="java.lang.String"/>
<collection property="keys" column="lock_id" select="com.lovo.dao.IKeyDao.findKeyByLockId"
javaType="java.util.List"/><!-- xml之间相互调用, collection对象集合,property:java中 column:数据库中-->
<result property="lock_id" column="lock_id" javaType="java.lang.Integer"/>
<result property="lcok_name" column="lock_name" javaType="java.lang.String"/>
<collection property="keys" column="lock_id" select="com.lovo.dao.IKeyDao.findKeyByLockId"
javaType="java.util.List"/><!-- xml之间相互调用, collection对象集合,property:java中 column:数据库中-->
<association property="lock" column="lock_id" select="com.lovo.dao.ILockDao.findById" javaType="Lock"/>
<!-- lock_id锁的id,select中查询可以用到, 调用ILockDao中的findById方法 ,association单个对象-->
</resultMap>
<!-- lock_id锁的id,select中查询可以用到, 调用ILockDao中的findById方法 ,association单个对象-->
</resultMap>
<select id="findKeyWithLock" parameterType="Key" resultMap="keyWithLock"><!-- 查询钥匙和锁 ,按钥匙id-->
<![CDATA[
<![CDATA[
// select * from t_key k inner join t_lock l on k.lock_id=l.lock_id and k.key_id=#{k.key_id}
select * from t_key
]]>
<where>
<include refid="idColumns"/>
</where>
</select>
select * from t_key
]]>
<where>
<include refid="idColumns"/>
</where>
</select>
一对多;多对多;定义结果集映射
<resultMap type=""id="MapName1">
<result property="" column="" javaType="">
</resultMap>
<select property="" resultMap="MapName1">
缓存:
一级缓存:事务级别缓存,随事务的生命周期存在 结束:session.close( ) session
二级缓存:应用级别缓存,随应用的生命周期存在 (当一级缓存失效后,数据存入二级缓存) sessionFactory (session工厂)
注:一个工厂持有多个session,多个session间共享一个sessionFactory缓存,(一级缓存特点:生命周期短)
一级缓存默认存在,二级缓存需手动开启
查询先从一级缓存中查询,再查询二级缓存(延迟加载(返回,有一个模拟对象,当需要查询对象具体属性时,才查数据库)),再查询数据库
缓存中可以存放的数据:1.小数据,2.低并发,3.不经常修改
应用程序 内 存 数据库
并发
Class App( 一级缓存 二级缓存 处理, DB
) session sessionFactory 查询
缓存 注:Hibernate ,Mybatis 相同