连接池
连接池可以减少我们获取连接的时间
mybatis连接池提供了3种方式的配置:
主配置文件SqlMapConfig.xml中的dataSource标签,type属性就是表示采用何种连接池方式。
type属性的取值:
- POOLED 采用传统的javax.sql.DataSource规范中的连接池,mybatis中有针对规范的实现
- UNPOOLED 采用传统的获取连接的方式,虽然也实现Javax.sql.DataSource接口,但是并没有使用池的思想。
- JNDI 采用服务器提供的JNDI技术实现,来获取DataSource对象,不同的服务器所能拿到DataSource是不一样。注意:如果不是web或者maven的war工程,是不能使用的。tomcat服务器,采用连接池就是dbcp连接池。
快捷键
ctrl+n 快速打开类
事务提交方式变为自动
SqlSession sqlSession = sqlSessionFactory.openSession(true);//手动将它的事务提交方式改为自动提交
//sqlSession.commit();//不需要手动提交事务了
Mybatis动态SQL-if标签
IUserDao.java
List<User> findUserByCondition(User user);
IUserDao.xml
用if标签拼接条件查询的username
<select id="findUserByCondition" resultType="user">
select * from user where 1=1
<if test="username != null">
and username = #{username}
</if>
</select>
测试类
@Test
public void testFindCondition(){
User u = new User();
u.setUsername("老王");
List<User> users = userDao.findUserByCondition(u);
for (User user : users) {
System.out.println(user);
}
}
为了不写where 1=1,可以用where标签
<select id="findUserByCondition" resultMap="userMap" parameterType="user">
select * from user
<where>
<if test="userName != null">
and username = #{userName}
</if>
<if test="userSex != null">
and sex = #{userSex}
</if>
</where>
</select>
foreach和sql标签
foreach标签
当要使用数组或list的值时,使用foreach遍历
IUserDao.java
List<User> findUserByIds(QueryVo vo);
QueryVo加入ids和get、set方法
private List<Integer> ids;
IUserDao.xml
<select id="findUserByIds" parameterType="QueryVo" resultType="user">
select * from user
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
测试类
@Test
public void testFindByIds(){
ArrayList<Integer> list = new ArrayList<>();
list.add(41);
list.add(42);
list.add(45);
QueryVo vo = new QueryVo();
vo.setIds(list);
List<User> byIds = userDao.findUserByIds(vo);
for (User byId : byIds) {
System.out.println(byId);
}
}
sql标签
为了节约代码量,将大量重复的sql语句提炼出来
提炼代码
<sql id="defaultUser">
select * from user
</sql>
用include标签引入即可
<select id="findUserByIds" parameterType="QueryVo" resultType="user">
<include refid="defaultUser"></include>
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
单表查询
和之前类似IUserDao.xml
<select id="findById" parameterType="int" resultType="user">
select * from user where id = #{uid}
</select>
可以查询出所有user表中的信息
一对一操作——实体类中含有另一个实体类(查账户时用户信息一并显示)
两种方法:
- 重写一个domain继承之前的然后重写toString,resultType直接写新的domain就好了
- 在账户里,多一个user属性,需要在xml中把resultType重写成自定义的resultMap
Account.java里的属性-多了User
private Integer id;
private Integer uid;
private Double money;
private User user;
IAccountDao.xml
<!-- 定义封装account和user的resultMap colume对应数据库,property对应javabean-->
<resultMap id="accountUserMap" type="account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!-- 一对一的关系映射:配置封装user的内容-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="accountUserMap">
select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
</select>
Test.java
@Test
public void testFindAll(){
List<Account> accounts = accountDao.findAll();
for (Account account : accounts) {
System.out.println("-------------");
System.out.println(account);
System.out.println(account.getUser());
}
}
一对多操作 查user同时查出其拥有的多个账户
首先User类里添加account的集合
private List<Account> accounts;
xml文件里修改(因为返回的user里有账户集合,用collection标签)
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<collection property="accounts" ofType="account">
<id column="id" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userAccountMap">
select * from user u left outer join account a on u.id = a.uid
</select>
多对多(role,user,user_role)
user_role是中间表(多对多就是两个一对多组成的,用中间表来联系)
查询某个role,把其中的user角色都查出来,其中一个role里有user集合,所以用collection
xml
<!--定义role表的ResultMap-->
<resultMap id="roleMap" type="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<!--查询所有-->
<select id="findAll" resultMap="roleMap">
select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id = ur.uid
</select>