public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
User.java
public class UserCustom extends User {
//添加一些扩展字段
}
UserCustom
public class UserQueryVo {
//用户信息
private User user;
//自定义user的扩展对象
private UserCustom userCustom;
//用户id集合
private List<Integer> ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public UserCustom getUserCustom() {
return userCustom;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
UserQueryVo
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"></properties>
<!-- 定义 别名 -->
<typeAliases>
<!--
单个别名的定义
alias：别名，type：别名映射的类型 -->
<!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
<!-- 批量别名定义
指定包路径，自动扫描包下边的pojo，定义别名，别名默认为类名（首字母小写或大写）
-->
<package name="cn.itcast.mybatis.po"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC" />
<!-- 数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--加载mapper映射
如果将和spring整合后，可以使用整合包中提供的mapper扫描器，此处的mappers不用配置了。
-->
<mappers>
<package name="cn.yzu.mybatis.mapper"/>
</mappers>
</configuration>
public interface UserMapper {
//根据用户id查询用户信息
public User findUserById(int id) throws Exception;
//根据用户名称 查询用户信息
public List<User> findUserByName(String username) throws Exception;
//自定义查询条件查询用户信息
public List<User> findUserList(UserQueryVo userQueryVo) throws Exception;
//查询用户，使用resultMap进行映射
public List<User> findUserListResultMap(UserQueryVo userQueryVo)throws Exception;
//查询用户，返回记录个数
public int findUserCount(UserQueryVo userQueryVo) throws Exception;
//插入用户
public void insertUser(User user)throws Exception;
//删除用户
public void deleteUser(int id) throws Exception;
//修改用户
public void updateUser(User user) throws Exception;
}
UserMapper
<?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">
<!-- namespace命名空间，为了对sql语句进行隔离，方便管理 ，mapper开发dao方式，使用namespace有特殊作用
mapper代理开发时将namespace指定为mapper接口的全限定名
-->
<mapper namespace="cn.yzu.mybatis.mapper.UserMapper">
<!-- 在mapper.xml文件中配置很多的sql语句，执行每个sql语句时，封装为MappedStatement对象
mapper.xml以statement为单位管理sql语句
-->
<!-- 将用户查询条件定义为sql片段
建议对单表的查询条件单独抽取sql片段，提高公用性
注意：不要将where标签放在sql片段
-->
<sql id="query_user_where">
<!-- 如果 userQueryVo中传入查询条件，再进行sql拼接-->
<!-- test中userCustom.username表示从userQueryVo读取属性值-->
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!=''">
and username like '%${userCustom.username}%'
</if>
<if test="userCustom.sex!=null and userCustom.sex!=''">
and sex = #{userCustom.sex}
</if>
<!-- 根据id集合查询用户信息 -->
<!-- 最终拼接的效果：
SELECT id ,username ,birthday FROM USER WHERE username LIKE '%小明%' AND id IN (16,22,25)
collection：集合的属性
open：开始循环拼接的串
close：结束循环拼接的串
item：每次循环取到的对象
separator：每两次循环中间拼接的串
-->
<foreach collection="ids" open=" AND id IN ( " close=")" item="id" separator=",">
#{id}
</foreach>
<!--
SELECT id ,username ,birthday FROM USER WHERE username LIKE '%小明%' AND (id = 16 OR id = 22 OR id = 25)
<foreach collection="ids" open=" AND ( " close=")" item="id" separator="OR">
id = #{id}
</foreach>
-->
<!-- 还有很多的查询条件 -->
</if>
</sql>
<!-- 定义resultMap，列名和属性名映射配置
id：mapper.xml中的唯一标识
type：最终要映射的pojo类型
-->
<resultMap id="userListResultMap" type="user" >
<!-- 列名
id_,username_,birthday_
id：要映射结果集的唯 一标识 ，称为主键
column：结果集的列名
property：type指定的哪个属性中
-->
<id column="id_" property="id"/>
<!-- result就是普通列的映射配置 -->
<result column="username_" property="username"/>
<result column="birthday_" property="birthday"/>
</resultMap>
<!-- 根据id查询用户信息 -->
<select id="findUserById" parameterType="int" resultType="user">
SELECT * FROM USER WHERE id= #{id}
</select>
<!-- 根据用户名称查询用户信息，可能返回多条-->
<select id="findUserByName" parameterType="java.lang.String" resultType="cn.yzu.mybatis.po.User">
select * from user where username like '%${value}%'
</select>
<!-- 自定义查询条件查询用户的信息
%${userCustom.username}%：userCustom是userQueryVo中的属性，通过OGNL获取属性的值
-->
<select id="findUserList" parameterType="userQueryVo" resultType="user">
select id,username,birthday,sex from user
<!-- where标签相当 于where关键字，可以自动去除第一个and -->
<where>
<!-- 引用sql片段，如果sql片段和引用处不在同一个mapper必须前边加namespace -->
<include refid="query_user_where"></include>
<!-- 下边还有很其它的条件 -->
<!-- <include refid="其它的sql片段"></include> -->
</where>
</select>
<!-- 使用resultMap作结果映射
resultMap：如果引用resultMap的位置和resultMap的定义在同一个mapper.xml，直接使用resultMap的id，如果不在同一个mapper.xml要在resultMap的id前边加namespace
-->
<select id="findUserListResultMap" parameterType="userQueryVo" resultMap="userListResultMap">
select id id_,username username_,birthday birthday_ from user where username like '%${userCustom.username}%'
</select>
<!-- 输出简单类型
功能：自定义查询条件，返回查询记录个数，通常用于实现 查询分页
-->
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
select count(*) from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
<!-- 添加用户-->
<insert id="insertUser" parameterType="cn.yzu.mybatis.po.User">
<selectKey keyProperty="id" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
public class UserMapperTest {
// 会话工厂
private SqlSessionFactory sqlSessionFactory;
// 创建工厂
@Before
public void init() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserById() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserById(1);
System.out.println(user);
}
@Test
public void testFindUserByUsername() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.findUserByName("小明");
System.out.println(list);
}
@Test
public void testInsertUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("李奎123");
userMapper.insertUser(user);
sqlSession.commit();
sqlSession.close();
}
// 通过包装类型查询用户信息
@Test
public void testFindUserList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 构造查询条件
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("小明");
userCustom.setSex("1");
userQueryVo.setUserCustom(userCustom);
//id集合
List<Integer> ids = new ArrayList<Integer>();
ids.add(16);
ids.add(22);
userQueryVo.setIds(ids);
List<User> list = userMapper.findUserList(userQueryVo);
sqlSession.close();
System.out.println(list);
}
// 使用resultMap进行结果映射
@Test
public void testFindUserListResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("小明");
userQueryVo.setUserCustom(userCustom);
List<User> list = userMapper.findUserListResultMap(userQueryVo);
sqlSession.close();
System.out.println(list);
}
// 返回查询记录总数
@Test
public void testFindUserCount() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("小明");
userQueryVo.setUserCustom(userCustom);
//id集合
List<Integer> ids = new ArrayList<Integer>();
ids.add(16);
ids.add(22);
userQueryVo.setIds(ids);
int count = userMapper.findUserCount(userQueryVo);
sqlSession.close();
System.out.println(count);
}
}
