MyBatis
输入映射和输出映射
输入映射parameterType
parameterMap已经废弃，所以不要使用
Ⅰ基本类型 Ⅱ自定义对象 Ⅲ自定义包装类
在com.sikiedu.bean创建一个UserVo.class
UserVo.java 里面本来假设要封装user表和一些额外数据的
package com.sikiedu.bean;
public class UserVo {
//包装类
//例如 一个表A需要全部的user对象信息和表B的id name属性
//此时就需要把user和表B的数据封装起来 但是表b这里没有 所以只封装了user表
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
修改com.sikiedu.mapper下的UserMapper.java和UserMapper.xml
在UserMapper.java添加一个名为selectUserByUserVoId的方法
//通过UserVo的id查询用户
public User selectUserByUserVoId(UserVo vo);
在UserMapper.xml中配置selectUserByUserVoId
<select id="selectUserByUserVoId" parameterType="UserVo" resultType="user">
select * from user where u_id =#{user.u_id}
</select>
在MapperTest.java中添加一个方法测试
//通过UserVo的id来查询User
@Test
public void Test6() throws IOException {
String resource="sqlMapConfig.xml";
InputStream in=Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
UserVo vo=new UserVo();
User u=new User();
u.setU_id(18);
vo.setUser(u);
User user = mapper.selectUserByUserVoId(vo);
System.out.println(user);
}
输出映射
resultType属于自动映射，resultMap属于手动映射，两者不能同时使用
数据库中的表必须和bean中的属性完全匹配才能使用resultType，若有一点不同则无法映射到数据库
输出基本类型数据resultType
Ⅰ基本类型 Ⅱ自定义对象 Ⅲ集合
修改com.sikiedu.mapper下的UserMapper.java和UserMapper.xml
在UserMapper.java添加一个名为selectUserCount的方法
//查询用户总条数
public Integer selectUserCount();
在UserMapper.xml中配置selectUserCount
<!-- 没有输入参数 -->
<select id="selectUserCount" resultType="Integer">
select count(*) from user
</select>
在MapperTest.java中添加一个方法测试
//查询用户总条数
@Test
public void Test_selectUserCount() throws IOException {
String resource="sqlMapConfig.xml";
InputStream in=Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Integer count = mapper.selectUserCount();
System.out.println(count);
}
输出映射resultMap
Ⅰbean对象字段与数据表字段不匹配 Ⅱ自定义包装类 Ⅲ关联查询
在com.sikiedu.bean创建Country.java
package com.sikiedu.bean;
public class Country {
/*
* CREATE TABLE `country`
* ( `c_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT'国家id',
* `c_countryname` VARCHAR(128) NOT NULL COMMENT '国家名称',
* `c_capital` VARCHAR(128) DEFAULT NULL COMMENT '国家首都名称',
* PRIMARY KEY (`c_id`)
* )ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
*/
//这里为了使用resultMap 所以bean表和数据库中的表数据并不是完全对应的
private Integer id;//和数据库中的c_id不一样 需要手动映射
private String c_countryname;
private String c_capital;//这两个会自动映射的
/* 这里为 get和set方法 以及toString 有参构造器 和无参构造器*/
}
在com.sikiedu.mapper添加接口CountryMapper.java和CountryMapper.xml配置文件
CountryMapper.java
package com.sikiedu.mapper;
import java.util.List;
import com.sikiedu.bean.Country;
public interface CountryMapper {
//查询所有
public List<Country> selectAll();
}
CountryMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sikiedu.mapper.CountryMapper">
<!-- public List<Country> selectAll(); -->
<resultMap type="Country" id="country">
<!--这里的type就是返回值 返回的Country类，因为我们设置了别名-->
<result property="id" column="c_id"/><!--Country类的id与数据库里的c_id映射-->
</resultMap>
<select id="selectAll" resultMap="country"><!--resultMap里使用上面的id-->
select * from country
</select>
</mapper>
在MapperTest.java下添加一个测试方法
// 查询所有country表
@Test
public void Test_selectAll() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
CountryMapper mapper = session.getMapper(CountryMapper.class);
List<Country> list = mapper.selectAll();
for (Country country : list) {
System.out.println(country);
}
}
关联查询
一对一查询
一个用户只有一个国家
修改com.sikiedu.bean包下的UserVo.java
package com.sikiedu.bean;
public class UserVo extends User{
//包装类
private Country country;
public Country getCountry() {
return country;
}
public void setCountry(Country country) {
this.country = country;
}
@Override
public String toString() {
return "UserVo [country=" + country + ", getU_id()=" + getU_id() + ", getU_username()=" + getU_username()
+ ", getU_sex()=" + getU_sex() + "]";
}
}
修改com.sikiedu.mapper下的UserMapper.java和UserMapper.xml
在UserMapper.java中添加selectAllUserVo方法
//查询所有用户包装类
public List<UserVo> selectAllUserVo();
在UserMapper.xml中配置
<!-- 查找所有用户包装类 -->
<resultMap type="UserVo" id="uservolist">
<id property="u_id" column="u_id"/><!-- id写主键 -->
<result property="u_username" column="u_username"/>
<result property="u_sex" column="u_sex"/><!-- result写一般属性 -->
<!-- 一对一关系 -->
<association property="country" javaType="Country">
<result property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
</association>
</resultMap>
<select id="selectAllUserVo" resultMap="uservolist">
select
u.`u_id`,
u.`u_username`,
u.`u_sex`,
c.`c_id`,
c.`c_countryname`
from user u
left join
COUNTRY c
on u.`u_cid`=c.`c_id`
</select>
在MapperTest.java添加方法测试
// 联合查询的一对一查询 一个用户只对应一个国家
@Test
public void Test_selectAllUserVo() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<UserVo> list = mapper.selectAllUserVo();
for (UserVo userVo : list) {
System.out.println(userVo);
}
}
测试数据
UserVo [country=Country [id=1, c_countryname=中国, c_capital=null], getU_id()=1, getU_username()=老王, getU_sex()=1]
UserVo [country=Country [id=1, c_countryname=中国, c_capital=null], getU_id()=21, getU_username()=重樱, getU_sex()=0]
UserVo [country=Country [id=1, c_countryname=中国, c_capital=null], getU_id()=22, getU_username()=铁血, getU_sex()=0]
一对多查询
一个国家可以对应多个用户
在com.sikiedu.bean中创建CountryVo.java
package com.sikiedu.bean;
import java.util.List;
public class CountryVo extends Country {
//需要维护一个User集合
private List<User> userList;
public List<User> getUserList() {
return userList;
}
public void setUserList(List<User> userList) {
this.userList = userList;
}
@Override
public String toString() {
return super.toString()+ " userList ="+userList;
}
}
修改com.sikiedu.mapper下的CountryMapper.java和CountryMapper.xml
在CountryMapper.java中添加selectAllCountryVo方法
//查询所有的CountryVo
public List<CountryVo> selectAllCountryVo();
在CountryMapper.xml中配置
<resultMap type="CountryVo" id="countryVo">
<id property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
<result property="c_capital" column="c_capital"/>
<!-- 一对多关系 -->
<collection property="userList" ofType="User">
<id property="u_id" column="u_id"/>
<result property="u_username" column="u_username"/>
</collection>
</resultMap>
<select id="selectAllCountryVo" resultMap="countryVo">
select
c.`c_id`,
c.`c_countryname`,
c.`c_capital`,
u.`u_id`,
u.`u_username`
from COUNTRY c
left join
USER u
on u.`u_cid`=c.`c_id`
</select>
在MapperTest.java中添加方法测试
// 联合查询的多对一查询 一个国家可以对应多个用户
@Test
public void Test_selectAllCountryVo() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
CountryMapper mapper = session.getMapper(CountryMapper.class);
List<CountryVo> list = mapper.selectAllCountryVo();
for (CountryVo countryVo : list) {
System.out.println(countryVo);
}
}
测试结果
Country [id=1, c_countryname=中国, c_capital=北京] userList =[User [u_id=17, u_username=樱风, u_password=null, u_sex=null, u_createTime=null, u_cid=null]
Country [id=3, c_countryname=美国, c_capital=华盛顿] userList =[User [u_id=2, u_username=jack, u_password=null, u_sex=null, u_createTime=null, u_cid=null], User [u_id=3, u_username=alice, u_password=null, u_sex=null, u_createTime=null, u_cid=null]]
Country [id=4, c_countryname=英国, c_capital=伦敦] userList =[User [u_id=5, u_username=anna, u_password=null, u_sex=null, u_createTime=null, u_cid=null]]
Country [id=5, c_countryname=日本, c_capital=东京] userList =[User [u_id=7, u_username=漩涡鸣人, u_password=null, u_sex=null, u_createTime=null, u_cid=null]
动态SQL六大标签
if标签和where标签和Trim标签
Ⅰ：if标签——多条件查询
Ⅱ：where标签——解决if标签拼接字符串AND符号问题
Ⅲ：trim标签——定制where标签规则
向UserMapper.java和UserMapper.xml添加代码
UserMapper.java
//多条件查询 通过用户对象中的条件查询用户列表
public List<User> selectUserListByUser(User u);
//多条件查询 通过用户对象中的条件查询用户列表Trim
public List<User> selectUserListByUserTrim(User u);
UserMapper.xml
<!-- //多条件查询 通过用户对象中的条件查询用户列表 public List<User> selectUserListByUser(); -->
<select id="selectUserListByUser" parameterType="User" resultType="User">
<!-- 查询用户性别 模糊查询用户名 查询用户c_id 国籍id -->
select *
from user
<where> <!-- where标签可以去掉开头的and -->
<if test="u_sex!=null and u_sex!=''">
and u_sex = #{u_sex}
</if>
<if test="u_username!=null and u_username !=''">
and u_username like "%"#{u_username}"%"
</if>
<if test="u_cid!=null and u_cid!=''">
and u_cid = #{u_cid}
</if>
</where>
</select>
<!-- //多条件查询 通过用户对象中的条件查询用户列表 public List<User> selectUserListByUserTrim(); -->
<select id="selectUserListByUserTrim" parameterType="User" resultType="User">
<!-- 查询用户性别 模糊查询用户名 查询用户c_id 国籍id -->
select *
from user
<!-- trim标签把where标签替换掉 -->
<trim prefix="where" suffixOverrides="and"><!-- 这是前缀where -->
<if test="u_sex!=null and u_sex!=''">
u_sex = #{u_sex} and
</if>
<if test="u_username!=null and u_username !=''">
u_username like "%"#{u_username}"%" and
</if>
<if test="u_cid!=null and u_cid!=''">
u_cid = #{u_cid} and
</if>
</trim>
</select>
在MapperTest.java添加测试方法
// 多条件where标签查询
@Test
public void Test_selectUserListByUser() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_sex("0");
u.setU_username("风");
u.setU_cid(1);
List<User> list = mapper.selectUserListByUser(u);
for (User user : list) {
System.out.println(user);
}
}
// 多条件 trim标签查询
@Test
public void Test_selectUserListByUserTrim() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_sex("0");
u.setU_username("风");
u.setU_cid(1);
List<User> list = mapper.selectUserListByUserTrim(u);
for (User user : list) {
System.out.println(user);
}
}
set标签和foreach标签和sql标签
Ⅳ：set标签——解决更新数据表时字符串拼接逗号问题
Ⅴ：foreach标签——如果需要使用IN查询多条相同数据，可以使用foreach遍历
Ⅵ：sql标签——可以提取重复sql语句片段
向UserMapper.java和UserMapper.xml添加代码
UserMapper.java
//更新用户表
public void updateSetUser(User u);
//foreach标签 使用多个id获取列表 by array
public List<User> selectUserListByIds(Integer[] ids);
//foreach标签 使用多个id获取列表 by list
public List<User> selectUserListByList(List<Integer> idList);
//使用多个id获取列表 by userVo
public List<User> selectUserListByUserVo(UserVo vo);
UserMapper.xml
<!-- 将大量的重复sql语句整合在一起 -->
<sql id="myselect">
select *
from user
</sql>
<!-- //更新用户表 public void updateSetUser(User u); -->
<update id="updateSetUser" parameterType="User">
<!-- 用户名 密码 性别 用id来限制 -->
update user
<set><!-- 用set标签解决后面的逗号问题 -->
<if test="u_username != null and u_username!='' ">
u_username =#{u_username},
</if>
<if test="u_password != null and u_password!='' ">
u_password =#{u_password},
</if>
<if test="u_sex != null and u_sex!='' ">
u_sex =#{u_sex}
</if>
</set>
where u_id = #{u_id}
</update>
<!-- //foreach标签 使用数据来获取用户列表
public List<User> selectUserListByIds(); (1,3,5) 此处的parameterType值写不写都行-->
<select id="selectUserListByIds" parameterType="Integer" resultType="User">
select *
from user
where u_id
in
<!-- (1,3,5) 传递的是数据则collection="array"-->
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!-- //foreach标签 使用集合来获取用户列表-->
<select id="selectUserListByList" parameterType="Integer" resultType="User">
<include refid="myselect"/>
where u_id
in
<!-- (1,3,5) 传递的是集合则collection="list" -->
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!--使用包装类 查询用户列表-->
<select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">
<include refid="myselect"/>
where u_id
in
<!-- 传递的是包装类idList 则collection="idList" -->
<foreach collection="idList" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
使用包装类需要修改UserVo.java类
package com.sikiedu.bean;
import java.util.List;
public class UserVo extends User{
//list将传入多个id进行查询
private List<Integer> idList;
public List<Integer> getIdList() {
return idList;
}
public void setIdList(List<Integer> idList) {
this.idList = idList;
}
}
在MapperTest.java添加测试方法
// 多条件 用set标签更新用户操作
@Test
public void Test_updateSetUser() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_id(1);
u.setU_username("隔壁老王");
u.setU_password("aaa");
u.setU_sex("3");
mapper.updateSetUser(u);
session.commit();// 更新 删除 插入操作必须提交事务
// 生成的sql语句 Preparing: update user SET u_username =?, u_password =?, u_sex =?
// where u_id = ?
}
// foreach遍历 传入数据
@Test
public void Test_selectUserListByIds() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Integer[] ids = { 1, 3, 5 };
List<User> list = mapper.selectUserListByIds(ids);
for (User user : list) {
System.out.println(user);
}
//sql语句Preparing: select * from user where u_id in ( ? , ? , ? )
}
// foreach遍历 传入集合
@Test
public void Test_selectUserListByList() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> idList=new ArrayList<Integer>();
idList.add(1);
idList.add(4);
idList.add(19);
List<User> list = mapper.selectUserListByList(idList);
for (User user : list) {
System.out.println(user);
}
//sql语句Preparing: select * from user where u_id in ( ? , ? , ? )
}
//使用包装类UserVo里的idList
@Test
public void Test_selectUserListByUserVo() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> idList=new ArrayList<Integer>();
idList.add(1);
idList.add(4);
idList.add(19);
UserVo userVo=new UserVo();
userVo.setIdList(idList);
List<User> list = mapper.selectUserListByUserVo(userVo);
for (User user : list) {
System.out.println(user);
}
//sql语句Preparing: select * from user where u_id in ( ? , ? , ? )
}
6399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
