【JAVAEE框架】Mybatis常用操作（CRUD）
哈喽~大家好呀，这篇来看看使用的常用操作。
🥇个人主页：个人主页
🥈 系列专栏：【JAVAEE框架】
🥉与这篇相关的文章：
【JAVAEE框架】Mybatis项目起步讲解 【JAVAEE框架】Mybatis项目起步讲解_程序猿追的博客-CSDN博客 JAVAWEB开发】基于Java+Servlet+Ajax+jsp网上购物系统设计实现 【JAVAWEB开发】基于Java+Servlet+Ajax+jsp网上购物系统设计实现_程序猿追的博客-CSDN博客 Servlet 架构思路（MVC） Servlet 架构思路（MVC）_程序猿追的博客-CSDN博客
目录
一、准备工作
二、查询所有用户信息
三、按 xx 进行查询
四、模糊查询
五、增删改操作
六、联合查询
七、分页查询
一、准备工作
如何新建 Mybatis 项目，可以看看上一篇的讲解——【JAVAEE框架】Mybatis项目起步讲解
准备 tb_brand 表，字段id（id）、品牌名（brand_name）、公司名（company_name）、排序顺序（ordered）、描述信息（description）、状态（status）。
项目结构在上篇有所讲解，这里就不重复了，直接上代码讲解。
二、查询所有用户信息
我们常见的 xx 管理系统最常见的操作之一，查看所有的记录。
mapper
<?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.itxzw.dao.IBrandDao">
<select id="getUserList" resultType="Brand">
select * from tb_brand
</select>
</mapper>IBrandDao
public interface IBrandDao {
public List<Brand> getUserList() throws Exception;
}接着上次的讲，上次我们讲到SqlSession是dao与db建立的一次会话，就像 servlet 的 session 一样，但，有没有想过，我们以后一个项目有成千上万的访问者访问页面（获取 db 的信息），那么岂不是要建立成千上万次会话？这是很不合理的。所以就有了 MyBatisUtil，就像 jdbc 的 Util 一样
MyBatisUtil
public class MyBatisUtil {
private static SqlSessionFactory factory;
static {
try {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = builder.build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession openSession(){
return factory.openSession();
}
public static SqlSession openSession(boolean autoCommit){
return factory.openSession(autoCommit);
}
}
实体类(Brand)
public class Brand {
// id 主键
private Integer id;
// 品牌名称
private String brand_name;
// 企业名称
private String company_name;
// 排序字段
private Integer ordered;
// 描述信息
private String description;
// 状态：0：禁用 1：启用
private Integer status;
public Brand() {
}
public Brand(Integer id, String brand_name, String company_name, Integer ordered, String description, Integer status) {
this.id = id;
this.brand_name = brand_name;
this.company_name = company_name;
this.ordered = ordered;
this.description = description;
this.status = status;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrand_name() {
return brand_name;
}
public void setBrand_name(String brand_name) {
this.brand_name = brand_name;
}
public String getCompany_name() {
return company_name;
}
public void setCompany_name(String company_name) {
this.company_name = company_name;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brand_name='" + brand_name + '\'' +
", company_name='" + company_name + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}测试
public class TestMyBatis {
@Test
public void test01() throws Exception {
IBrandDao userDao = new BrandDao();
List<Brand> userList = userDao.getUserList();
for (Brand brand : userList) {
System.out.println(brand);
}
}
}效果
三、按 xx 进行查询
常见的按照姓名查询、按照 id 查询等，这里就演示 id 查询
mapper
<select id="getBrandById" resultType="Brand" parameterType="java.lang.String">
select * from tb_brand where id = #{id}
</select>IBrandDao
这里就全部给了，这小节就不多写 IBrandDao 了
public interface IBrandDao {
public List<Brand> getBrandList();
public Brand getBrandById(String id);
public List<Brand> getBrandListByCondition(Brand conn);
public List<Brand> getBrandListByCondition(Map<String,String> conn);
public Integer addBrand(Brand brand);
public Integer updateBrand(Brand brand);
public Integer deleteBrand(Brand brand);
public Integer getBrandByCondition(Brand brand);
}测试
@Test
public void test02(){
IBrandDao dao = new BrandDao();
Brand brandList = dao.getBrandById("142");
System.out.println(brandList);
}效果
四、模糊查询
查询品牌含有8的品牌名
mapper
<select id="getBrandListByCondition" resultType="Brand" parameterType="Brand">
<![CDATA[
select t.id,
t.brand_name,
t.company_name,
t.description,
t.status,
t.ordered
from tb_brand t where
]]>
<if test="id != null and id != '' ">
<![CDATA[
id = #{id}
]]>
</if>
<if test="brand_name != null and brand_name != '' ">
<![CDATA[
brand_name like '%${brand_name}%'
]]>
</if>
<if test="company_name != null and company_name != '' ">
<![CDATA[
and company_name like '%${company_name}%'
]]>
</if>
<!-- <if test="description != null and description '' ">-->
<!-- <![CDATA[-->
<!-- and description like '%${description}%'-->
<!-- ]]>-->
<!-- </if>-->
<!-- <if test="status != null and status != '' ">-->
<!-- <![CDATA[-->
<!-- and status = #{status}-->
<!-- ]]>-->
<!-- </if>-->
<!-- <if test="ordered != null and ordered != '' ">-->
<!-- <![CDATA[-->
<!-- and ordered = #{ordered}-->
<!-- ]]>-->
<!-- </if>-->
</select>测试
@Test
public void test03(){
SqlSession sqlSession = MyBatisUtil.openSession();
IBrandDao mapper = sqlSession.getMapper(IBrandDao.class);
Brand brand = new Brand();
brand.setBrand_name("8");
// brand.setCompany_name("小米");
List<Brand> brandList = mapper.getBrandListByCondition(brand);
for (int i = 0; i < brandList.size(); i++) {
System.out.println(brandList.get(i));
}
}效果
扩：
<![CDATA[]]> 用法
在使用mybatis 时我们sql是写在xml 映射文件中，如果写的sql中有一些特殊的字符的话，在解析xml文件的时候会被转义，但我们不希望他被转义，所以我们要使用<![CDATA[ ]]>来解决。
eg：> < 这两个符号
五、增删改操作
mapper
<insert id="addBrand" parameterType="Brand">
<![CDATA[
insert into tb_brand values (#{id}, #{brand_name}, #{company_name}, #{ordered}, #{description}, #{status});
]]>
</insert>
<update id="updateBrand" parameterType="Brand">
<![CDATA[
update tb_brand set company_name = #{company_name} where id = #{id}
]]>
</update>
<delete id="deleteBrand" parameterType="Brand">
<![CDATA[
delete from tb_brand where id = #{id}
]]>
</delete>测试
@Test
public void test04(){
SqlSession sqlSession = MyBatisUtil.openSession();
IBrandDao mapper = sqlSession.getMapper(IBrandDao.class);
Brand brand = new Brand(null, "菠萝手机","菠萝",100,"美国有苹果,中国有菠萝",0);
Integer integer = mapper.addBrand(brand);
sqlSession.commit();
System.out.println(integer);
}
@Test
public void test05(){
SqlSession sqlSession = MyBatisUtil.openSession();
IBrandDao mapper = sqlSession.getMapper(IBrandDao.class);
Brand brand = new Brand();
brand.setId(191);
brand.setCompany_name("大菠萝手机");
Integer integer = mapper.updateBrand(brand);
sqlSession.commit();
System.out.println(integer);
}
@Test
public void test06(){
SqlSession sqlSession = MyBatisUtil.openSession();
IBrandDao mapper = sqlSession.getMapper(IBrandDao.class);
Brand brand = new Brand();
brand.setId(195);
Integer integer = mapper.deleteBrand(brand);
sqlSession.commit();
System.out.println(integer);
}效果
六、联合查询
mapper
<resultMap id="smbmsUser" type="smbmsUser">
<id property="id" column="id"></id>
<association property="role" column="userrole" resultMap="smbmsRole"></association>
<collection property="addresses" column="id" ofType="smbmsAddress" resultMap="smbmsAddress"></collection>
</resultMap>
<resultMap id="smbmsRole" type="smbmsRole">
<id property="id" column="rid"></id>
<result property="createdby" column="rcb"></result>
<result property="creationdate" column="rcd"></result>
<result property="modifyby" column="rmb"></result>
<result property="modifydate" column="rmd"></result>
</resultMap>
<resultMap id="smbmsAddress" type="smbmsAddress">
<id property="id" column="aid"></id>
<result property="createdby" column="acb"></result>
<result property="creationdate" column="acd"></result>
<result property="modifyby" column="amb"></result>
<result property="modifydate" column="amd"></result>
</resultMap>
<select id="getUserListByCondition" parameterType="userCondition" resultMap="smbmsUser">
<![CDATA[
select
u.ID,
u.USERCODE,
u.USERNAME,
u.USERPASSWORD,
u.GENDER,
u.BIRTHDAY,
u.PHONE,
u.ADDRESS,
u.userrole,
r.id rid,
r.rolecode,
r.rolename,
r.createdby rcb,
r.creationdate rcd,
r.modifyby rmb,
r.modifydate rmd,
a.id aid,
a.contact,
a.addressdesc,
a.postcode,
a.tel,
a.createdby acb,
a.creationdate acd,
a.modifyby amb,
a.modifydate amd,
a.userid,
u.CREATEDBY,
u.CREATIONDATE,
u.MODIFYBY,
u.MODIFYDATE
from SMBMS_USER u
left join smbms_role r
on u.userrole = r.id
left join smbms_address a
on u.id = a.userid
where 1=1
]]>
</select>IUserDao
public List<SmbmsUser> getUserListByCondition(UserCondition conn);测试
@Test
public void test01(){
SqlSession sqlSession = MyBatisUtil.openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
UserCondition conn = new UserCondition();
List<SmbmsUser> userList = mapper.getUserListByCondition(conn);
for (SmbmsUser smbmsUser : userList) {
System.out.println(smbmsUser);
}
sqlSession.close();
}效果
七、分页查询
mapper
<select id="selectByPage" parameterType="Brand" resultType="Brand">
select * from tb_brand limit #{begin}, #{size}
</select>IUserDao
public List<Brand> selectByPage(@Param("begin") int begin, @Param("size") int size);测试
@Test
public void test01() {
SqlSession sqlSession = MyBatisUtil.openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
List<Brand> pageBean = mapper.selectByPage(1, 10);
for (Brand brand : pageBean) {
System.out.println(brand);
}
sqlSession.close();
}效果
不积跬步无以至千里，趁年轻，使劲拼，给未来的自己一个交代！向着明天更好的自己前进吧！