使用mybatis，动态sql，注解，一对一的映射完成双表的增删改查操作

本文链接：https://blog.youkuaiyun.com/wangzhigang0926/article/details/129673470

mysql数据库

use web1;
create table classInfo(
cid int primary key auto_increment,
cname varchar(20),
cinfo varchar(20)
)engine=innodb;
drop table if exists classInfo
insert into classInfo(cname,cinfo) values('移动应用开发','全都是it人才');
insert into classInfo(cname,cinfo) values('畜牧','都是养牛的');
insert into classInfo(cname,cinfo) values('农林','都是杨树的');
insert into classInfo(cname,cinfo) values('财务管理','都是会计人才');
insert into classInfo(cname,cinfo) values('财富管理','管理你的财富');
select*from classInfo;
select*from classInfo where cid=1
delete from classInfo where cid=1

drop table if exists classInfo
drop table if exists student
create table student(
sid int primary key auto_increment,
sname varchar(20),
sex varchar(2),
phone varchar(11),
address varchar(50),
cid int,
foreign key (cid) references classInfo(cid)
)engine=innodb;

insert into student(sname,sex,phone,address,cid) values('小红','女','18532125645','南阳',1);
insert into student(sname,sex,phone,address,cid) values('小明','男','15965988745','信阳',1);
insert into student(sname,sex,phone,address,cid) values('小花','女','19956897856','郑州',2);
insert into student(sname,sex,phone,address,cid) values('老八','男','15645568978','洛阳',2);
insert into student(sname,sex,phone,address,cid) values('老刘','男','16656455623','信阳',3);

select*from student
select*from student inner join classInfo on student.cid=classInfo.cid where sid=1
select*from student inner join classInfo on student.cid=classInfo.cid where sname like '%小%' and phone like '%159%' and address like '%信%'

一，创建项目，在项目中创建lib包在里面调用jar包，创建好实体类

二，创建mybatis.xml文件，设置数据库连接（创建一个jdbc.properties文件），日志打印

jdbc.properties文件

mybatis.xml文件

三，创建接口文件InfoDao，在里面使用用注解完成单表的增删改查

package com.wang.dao;

import com.wang.bean.ClassInfo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface InfoDao {
    @Select("select*from classInfo")
    List<ClassInfo> selectAll();
    @Select("select*from classInfo where cid=#{cid}")
    ClassInfo selectByCid(int cid);
    @Insert("insert into classInfo(cname,cinfo) values(#{cname},#{cinfo})")
    int insert(ClassInfo classInfo);
    @Delete("delete from classInfo where cid=#{cid}")
    int delete(int cid);
}

测试类：

package com.wang.test;

import com.wang.bean.ClassInfo;
import com.wang.dao.InfoDao;
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.util.List;

public class InfoTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    InfoDao infoDao=null;
    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        sqlSession=factory.openSession();
        infoDao=sqlSession.getMapper(InfoDao.class);
    }
    @Test
    public void testSelectAll(){
        List<ClassInfo> classInfos = infoDao.selectAll();
        for (ClassInfo classInfo : classInfos) {
            System.out.println(classInfo);
        }
    }
    @Test
    public void testSelectByCid(){
        ClassInfo classInfo = infoDao.selectByCid(1);
        System.out.println(classInfo);
    }
    @Test
    public void testAdd(){
        ClassInfo classInfo=new ClassInfo();
        classInfo.setCname("xx");
        classInfo.setCinfo("xx");
        int n = infoDao.insert(classInfo);
        if (n>0){
            System.out.println("添加成功");
        }
    }
    @Test
    public void testDel(){
        int n = infoDao.delete(6);
        if (n>0){
            System.out.println("删除成功");
        }
    }
    @After
    public void after() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

四，创建StudentDao接口，在里面定义全查，模糊查，修改的接口方法

package com.wang.dao;

import com.wang.bean.Student;

import java.util.List;

public interface StudentDao {
    List<Student> selectAll();

    List<Student> seach(Student student);

    int update(Student student);
}

五，创建StudendDao.xml文件，在里面编写双标表的一对一的手动映射和使用动态sql完成sql语句

<?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.wang.dao.StudentDao">
    <resultMap id="studentMap" type="com.wang.bean.Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="sex" column="sex"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        
        <association property="classInfo" javaType="com.wang.bean.ClassInfo">
            <id property="cid" column="cid"/>
            <result property="cname" column="cname"/>
            <result property="cinfo" column="cinfo"/>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="studentMap">
        select*from student inner join classInfo on student.cid=classInfo.cid
    </select>
    <select id="seach" resultMap="studentMap" parameterType="student">
        select*from student inner join classInfo on student.cid=classInfo.cid
               <where>
            <if test="sname !=null and sname!=''">
                and sname like concat('%',#{sname},'%')
            </if>
                   <if test="phone !=null and phone!=''">
                       and phone like concat('%',#{phone},'%')
                   </if>
                   <if test="address !=null and address!=''">
                       and address like concat('%',#{address},'%')
                   </if>
               </where>
    </select>
    <update id="update" parameterType="student">
        update student
<set>
    <if test="sname !=null and sname!=''">
        sname=#{sname}
    </if>
</set>
where sid=#{sid}
    </update>
</mapper>

测试类：

package com.wang.test;

import com.wang.bean.Student;
import com.wang.dao.InfoDao;
import com.wang.dao.StudentDao;
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.util.List;

public class StudentTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    StudentDao studentDao=null;
    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        sqlSession=factory.openSession();
        studentDao=sqlSession.getMapper(StudentDao.class);
    }
    @Test
    public void testSelectAll(){
        List<Student> students = studentDao.selectAll();
        for (Student student : students) {
            System.out.println(student);
        }
    }
    @Test
    public void testSeach(){
        Student student=new Student();
        student.setSname("小");
        student.setPhone("159");
        student.setAddress("信");
        List<Student> studentList = studentDao.seach(student);
        for (Student student1 : studentList) {
            System.out.println(student1);
        }
    }
    @Test
    public void testDate(){
        Student student=new Student();
        student.setSname("xxx");
        student.setSid(1);
        int n = studentDao.update(student);
        if (n>0){
            System.out.println("cg");
        }
    }
    @After
    public void after() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

附（一对多的手动映射）

<?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.wang.dao.DeptDao">
    <resultMap id="deptMap" type="com.wang.bean.Dept">
        <id property="did" column="did" />
        <result property="dname" column="dname"/>
        <result property="dinfo" column="dinfo"/>
        <!--配置一对多关系：手动映射配置-->
        <collection property="emps" ofType="com.wang.bean.Emp">
            <id property="eid" column="eid" />
            <result property="ename" column="ename" />
            <result property="job" column="job" />
            <result property="sal" column="sal" />
            <result property="phone" column="phone" />
            <result property="address" column="address" />
        </collection>
    </resultMap>

    <select id="selectByDid" parameterType="int" resultMap="deptMap">
        select * from dept inner join emp on dept.did=emp.did where dept.did=#{did};
    </select>
</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.wang.dao.DeptDao">
    <resultMap id="deptMap" type="com.wang.bean.Dept">
        <id property="did" column="did" />
        <result property="dname" column="dname"/>
        <result property="dinfo" column="dinfo"/>
        <!--配置一对多关系：自动根据did去关联查询emp表中该did的信息-->
        <collection column="did" select="com.wang.dao.EmpDao.selectByDid" property="emps">
        </collection>
    </resultMap>

    <select id="selectByDid" parameterType="int" resultMap="deptMap">
        select * from dept where did=#{did};
    </select>
</mapper>

附（一对一的自动映射）