<?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="xyz.slienceme.dao.IUserDao">
<!-- 配置 查询结果的列名和实体类的属性名的对应关系-->
<resultMap id="userMap" type="xyz.slienceme.domain.User">
<!-- 主键字段的对应 -->
<id property="userId" column="id"/>
<!-- 非主键字段的对应 -->
<result property="userName" column="username"/>
<result property="userAddress" column="address"/>
<result property="userSex" column="sex"/>
<result property="userBirthday" column="birthday"/>
</resultMap>
<!-- 查询所有操作 -->
<!-- <select id="findAll" resultType="xyz.slienceme.domain.User">-->
<!-- 修改后，数据库字段与类名不同-->
<select id="findAll" resultMap="userMap">
select * from user;
</select>
<!-- 保存用户-->
<insert id="saveUser" parameterType="xyz.slienceme.domain.User">
<!-- 配置插入操作后，获取插入数据的id-->
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
select last_insert_id();
</selectKey>
insert into user(username,address,sex,birthday)values(#{username},#{address},#{sex},#{birthday});
</insert>
<!-- 更新用户-->
<update id="updateUser" parameterType="xyz.slienceme.domain.User">
update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id};
</update>
<!-- 删除用户-->
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id=#{uid};
</delete>
<!--根据id查询用户 -->
<select id="findById" parameterType="INT" resultType="xyz.slienceme.domain.User">
select * from user where id=#{uid};
</select>
<!--根据名称模糊查询用户信息 -->
<select id="findByName" parameterType="string" resultType="xyz.slienceme.domain.User">
-- select * from user where username like #{name};
select * from user where username like '%${value}%';
</select>
<!--获取用户的总记录条数 -->
<select id="findTotal" resultType="int">
select count(id) from user;
</select>
<!--根据Queryvo中的条件查询用户-->
<select id="findUserByVo" parameterType="xyz.slienceme.domain.QueryVo" resultType="xyz.slienceme.domain.User">
select * from user where username like #{user.username};
</select>
</mapper>
# test
## xyz.slienceme.test.MybatisTest
- 这里简单提及一下模糊查询，两个注解的使用
```java
package xyz.slienceme.test;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import xyz.slienceme.dao.IUserDao;
import xyz.slienceme.domain.QueryVo;
import xyz.slienceme.domain.User;
import java.io.InputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;
public class MybatisTest {
private InputStream in;
private SqlSession sqlSession;
private IUserDao userDao;
@Before
public void init() throws Exception{
//1.读取配置文件，生成字节输入流
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.获取SqlSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//3.获取SqlSession对象
sqlSession = factory.openSession();
//4.获取dao的代理对象
userDao = sqlSession.getMapper(IUserDao.class);
}
@After
public void destroy() throws Exception{
//提交事务
sqlSession.commit();
//6.释放资源
sqlSession.close();
in.close();
}
/**
* 测试查询所有
*/
@Test
public void testFindAll(){
//5.执行查询所有方法
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
}
}
/**
* 测试保存操作
* @throws Exception
*/
@Test
public void testSave() throws Exception{
User user = new User();
user.setUsername("mybatis last insert");
// user.setUsername("mybatis saveuser");
user.setAddress("北京市顺义区");
user.setSex("男");
Date date = new Date();
LocalDateTime localDateTime = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
user.setBirthday(localDateTime);
System.out.println("保存操作之前："+user);
//5.执行保存方法
userDao.saveUser(user);
System.out.println("保存操作之后："+user);
}
/**
* 测试更新操作
* @throws Exception
*/
@Test
public void testUpdate() throws Exception{
User user = new User();
user.setId(50);
user.setUsername("mybatis update");
user.setAddress("北京市顺义区");
user.setSex("女");
Date date = new Date();
LocalDateTime localDateTime = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
user.setBirthday(localDateTime);
//5.执行保存方法
userDao.updateUser(user);
}
/**
* 测试删除操作
* @throws Exception
*/
@Test
public void testDelete(){
userDao.deleteUser(50);
}
/**
* 测试通过id查询操作
* @throws Exception
*/
@Test
public void testFindOne(){
User user = userDao.findById(48);
System.out.println(user);
}
/**
* 测试模糊查询操作
* @throws Exception
*/
@Test
public void testFindByName(){
// List<User> users = userDao.findByName("%王%");
List<User> users = userDao.findByName("王");
for (User user : users) {
System.out.println(user);
}
}
/**
* 测试查询用户的总记录条数
* @throws Exception
*/
@Test
public void testFindTotal(){
int count = userDao.findTotal();
System.out.println(count);
}
/**
* 测试使用Queryvo作为条件
* @throws Exception
*/
@Test
public void testFindByVo(){
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("%王%");
vo.setUser(user);
// List<User> users = userDao.findByName("%王%");
List<User> users = userDao.findUserByVo(vo);
for (User u : users) {
System.out.println(u);
}
}
}
