接口UserMapper
package com.wzx.mapper;
import com.wzx.pojo.Users;
import org.apache.ibatis.annotations.Param;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* 数据访问层的接口,规定数据库中可进行的各种操作
*/
public interface UserMapper {
//查询去拿不用户信息
List<Users> getAll();
//根据用户主键查用户
Users getById(Integer id);
//根据用户名模糊查询用户
List<Users> getByName(String name);
//用户的更新
int update(Users users);
//根据主键删除用户
int delete(Integer id);
//增加用户
int insert(Users users);
//模糊用户名和地址查询
List<Users> getByNameOrAddress(
@Param("columnName") String columnName,
@Param("columnValue") String columnValue);
//按照指定的条件进行多条件查询
List<Users> getByCondition(Users users);
//有选择的更新
int updateBySet(Users users);
//查询多个指定id的用户信息
List<Users> getByIds(Integer []arr);
//批量删除
int deleteBatch(Integer []arr);
//批量增加
int insertBatch(List<Users> list);
//查询指定日期范围内的用户
List<Users> getByBirthday(Date begin, Date end);
//入参是map
List<Users> getByMap(Map map);
//返回值是map(一行)
Map getReturnMap(Integer id);
//返回值是map(多行)
List<Map> getMulMap();
}
UserMapperxml
<?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.wzx.mapper.UserMapper">
<!-- 定义代码片段-->
<sql id="allColumns">
id,
username,
birthday,
sex,
address
</sql>
<select id="getAll" resultType="users">
select
<include refid="allColumns">
</include>
from users
</select>
<!-- private Integer id;
private String userName;
private Date birthday;
private String sex;
private String address;-->
<update id="update" parameterType="users">
update users
set username=#{userName},
birthday=#{birthday},
sex#{sex},
address#{address}
where id = #{id}
</update>
<select id="getById" parameterType="int" resultType="users">
select
<include refid="allColumns">
</include>
from users
where id = #{id}
</select>
<select id="getByName" parameterType="string" resultType="users">
select
<include refid="allColumns">
</include>
from users
where username like '%${userName}%'
</select>
<delete id="delete" parameterType="users">
delete
from users
where id = #{id}
</delete>
<insert id="insert" parameterType="users">
insert into (id,username,birthday,sex,address) values (#{id},#{userName},
#{birthday},
#{sex},
#{address} )
</insert>
<!-- 如果参数超过一个则parameterType不写
模糊用户名和地址查询
List<Users> getByNameOrAddress(
@Param("columnName")String columnName,
@Param("columnValue")String columnValue);
-->
<select id="getByNameOrAddress" resultType="users">
select
<include refid="allColumns">
</include>
from users
where ${columnName} like concat('%', #{columnValue}, '%')
</select>
<!-- //按照指定的条件进行多条件查询
List<Users> getByCondition(Users users);
根据实体类中的成员变量是否有值来决定是否添加条件
private Integer id; 0
private String userName; null or ""
private Date birthday; null
private String sex; null
private String address; null
-->
<select id="getByCondition" parameterType="users" resultType="users">
select
<include refid="allColumns">
</include>
from users
<where>
<if test="userName != null and userName != ''">
and username like concat('%', #{userName}, '%')
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="address != null and address != ''">
and address like concat('%', #{address}, '%')
</if>
</where>
</select>
<update id="updateBySet" parameterType="users">
update users
<set>
<if test="userName != null and userName != ''">
username=#{userName},
</if>
<if test="birthday != null">
birthday=#{birthday},
</if>
<if test="sex != null and sex != ''">
sex=#{sex},
</if>
<if test="address != null and address != ''">
address=#{address}
</if>
</set>
where id = #{id}
</update>
<!--//查询多个指定id的用户信息
List<Users> getByIds(Integer []arr);-->
<select id="getByIds" resultType="users">
select
<include refid="allColumns">
</include>
from users
where id in
<!--
collection:用来指定入参的类型,如果是List集合,则为list,如果是Map集合,则为map,如果是数组,则为array.
item:每次循环遍历出来的值或对象
separator:多个值或对象或语句之间的分隔符
open:整个循环外面的前括号
close:整个循环外面的后括号
-->
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<!--//批量删除
int deleteBatch(Integer []arr);-->
<delete id="deleteBatch">
delete
from users
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!-- //批量增加
int insertBatch(List<Users> list);
private Integer id; 0
private String userName; null or ""
private Date birthday; null
private String sex; null
private String address; null
-->
<insert id="insertBatch">
insert into users(username, birthday, sex, address)
values
<foreach collection="list" item="u" separator=",">
(#{u.userName}, #{u.birthday}, #{u.sex}, #{u.address})
</foreach>
</insert>
<!--
//查询指定日期范围内的用户
List<Users> getByBirthday(Date begin,Date end);
-->
<select id="getByBirthday" resultType="users">
select
<include refid="allColumns">
</include>
from users
where birthday between #{arg0} and #{arg1}
</select>
<!--
//入参是map
List<Users> getByMap(Map map);
-->
<select id="getByMap" resultType="users">
select
<include refid="allColumns">
</include>
from users
where birthday between #{birthdayBegin} and #{birthdayEnd}
</select>
<!--
//返回值是map(一行)
Map getReturnMap(Integer id);
-->
<select id="getReturnMap" resultType="map">
select username, address
from users
where id = #{id}
</select>
<!--
//返回值是map(多行)
List<Map> getMulMap();
-->
<select id="getMulMap" resultType="map">
select username,address
from users
</select>
</mapper>
测试类
package com.wzx;
import com.wzx.mapper.UserMapper;
import com.wzx.pojo.Users;
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 java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class MyTest {
SqlSession sqlSession;
UserMapper mapper;
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
@Before
public void openSqlSession() throws IOException {
//读取核心配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//取出SqlSession对象
sqlSession = factory.openSession();
//取出动态代理的对象,完成接口中方法的调用,实则是使用xml文件中的标签的功能
mapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void closeSqlSession() {
sqlSession.close();
}
@Test
public void testGetAll() {
// 就是在调用接口的方法,mybatis框架已经为我们把功能代理出来了
List<Users> list = mapper.getAll();
System.out.println(list);
list.forEach(users -> System.out.println(users));
}
@Test
public void testupdate() throws ParseException {
Users u = new Users(7, "wzx", sf.parse("2001-01-01"), "2", "asd");
int update = mapper.update(u);
System.out.println(update);
sqlSession.commit();
}
@Test
public void selectById() {
Users byId = mapper.getById(7);
System.out.println(byId);
}
@Test
public void getByName(){
List<Users> name = mapper.getByName("张");
name.forEach(users -> System.out.println(users));
}
@Test
public void delete(){
int delete = mapper.delete(7);
System.out.println(delete);
sqlSession.commit();
}
@Test
public void insert() throws ParseException {
Users users = new Users(8, "asd", sf.parse("2022-11-22"), "1", "asd");
int insert = mapper.insert(users);
System.out.println(insert);
sqlSession.commit();
}
@Test
public void testGetByCondition(){
Users users = new Users();
// users.setSex("1");
// users.setUserName("小");
// users.setAddress("南");
List<Users> list=mapper.getByCondition(users);
list.forEach(useruser -> System.out.println(useruser));
for (Users users1 : list) {
System.out.println(users1);
}
}
@Test
public void testUpdateSet(){
Users users = new Users();
users.setId(6);
users.setUserName("lla");
int update = mapper.updateBySet(users);
System.out.println(update);
sqlSession.commit();
}
@Test
public void testGetByIds(){
Integer []array={2,4,6};
List<Users> byIds = mapper.getByIds(array);
byIds.forEach(userss -> System.out.println(userss));
}
@Test
public void testDeleteBatch(){
Integer []array={2,4};
int i = mapper.deleteBatch(array);
sqlSession.commit();
System.out.println(i);
}
@Test
public void testInsertBatch() throws ParseException {
Users u1 = new Users("aa", sf.parse("2002-02-02"), "2", "朝阳1");
Users u2 = new Users("bb", sf.parse("2002-02-02"), "2", "朝阳2");
Users u3 = new Users("cc", sf.parse("2002-02-02"), "2", "朝阳3");
Users u4 = new Users("dd", sf.parse("2002-02-02"), "2", "朝阳4");
List<Users> list=new ArrayList<>();
list.add(u1);
list.add(u2);
list.add(u3);
list.add(u4);
int i = mapper.insertBatch(list);
sqlSession.commit();
System.out.println(i);
}
@Test
public void testGetBirthday() throws ParseException {
Date begin = sf.parse("1999-01-01");
Date end = sf.parse("2001-12-31");
List<Users> list = mapper.getByBirthday(begin,end);
list.forEach(userss -> System.out.println(userss));
}
@Test
public void testGetMap() throws ParseException {
Date begin=sf.parse("1999-01-01");
Date end=sf.parse("1999-12-31");
Map map = new HashMap<>() ;
map.put("birthdayBegin",begin);
map.put("birthdayEnd",end);
List<Users> list=mapper.getByMap(map);
}
@Test
public void testgetReturnMap(){
Map returnMap = mapper.getReturnMap(3);
System.out.println(returnMap);
}
@Test
public void testgetMulMap(){
List<Map> map = mapper.getMulMap();
map.forEach(map1 -> System.out.println(map1));
}
}