投稿
  1. 极客之音首页
  2. 后端开发
  3. Java

mybatis 一对一查询及一对多查询

飞熊 • • Java • 阅读 77

追求适度，才能走向成功；人在顶峰，迈步就是下坡；身在低谷，抬足既是登高；弦，绷得太紧会断；人，思虑过度会疯；水至清无鱼，人至真无友，山至高无树；适度，不是中庸，而是一种明智的生活态度。

导读：本篇文章讲解 mybatis 一对一查询及一对多查询，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

举例：学生(Student)和班级（ClassInfo）为例:

学生—–班级：为一对一的关系 即：一个学生只有一个相对应的班级。

嵌套结果使用双标签\<association>\</association>

一对一实体类创建如下：
1.数据库中创建班级表ClassInfo和学生表Student

create table classInfo(
cid int primary key auto_increment,
cname varchar(20),
cinfo varchar(20)
);
#插入测试数据
insert into classInfo(cname,cinfo) values ('一班','英语班');
insert into classInfo(cname,cinfo) values ('二班','数学班');
insert into classInfo(cname,cinfo) values ('三班','语文班');
#查询数据表
select * from classInfo;
#根据id查询数据
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
);
#插入测试数据
insert into student(sname,sex,phone,address,cid) values ('张三','男',12345645,'河南',101);
insert into student(sname,sex,phone,address,cid) values ('李四','女',12345678,'海南',102);
insert into student(sname,sex,phone,address,cid) values ('王二','女',12345689,'云南',103);
insert into student(sname,sex,phone,address,cid) values ('小明','男',12345612,'湖南',102);
#查询数据表
select * from student;
#根据id查询数据

 在idea中的bin包中导入jar包，配置mybatis.xml核心配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />

    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <typeAliases>
        <package name="com.su.bean"/>
    </typeAliases>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <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>

    <mappers>
        <package name="com.su.dao"/>
    </mappers>
</configuration>

 3.在src中new一个file起名为jdbc(外部文件)和log4j(日志打印)

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/li
jdbc.username=root
jdbc.password=root
log4j.rootLogger=TRACE,stdout  

log4j.appender.stdout=org.apache.log4j.ConsoleAppender   
#log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout   


log4j.appender.logfile=org.apache.log4j.FileAppender   
log4j.appender.logfile.File=wocao.log   
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout   
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n   
log4j.logger.mapperNS =TRACE

log4j.logger.com.mybatis=DEBUG  
log4j.logger.com.mybatis.common.jdbc.SimpleDataSource=DEBUG   
log4j.logger.com.mybatis.common.jdbc.ScriptRunner=DEBUG   
log4j.logger.com.mybatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG   
log4j.logger.java.sql.Connection=DEBUG  
log4j.logger.java.sql.Statement=DEBUG  
log4j.logger.java.sql.PreparedStatement=DEBUG  
log4j.logger.java.sql.ResultSet=DEBUG  

log4j.logger.org.springframework=error 
log4j.logger.org.apache=ERROR  
log4j.logger.org.mybatis=DEBUG

 4.1创建班级（ClassInfo）表的实体类

mybatis 一对一查询及一对多查询

public class ClassInfo {
    private Integer cid;
    private String cname;
    private String cinfo;

 //省略了get set方法和toString方法（alt+insert:）
}

 4.2学生(Student)表的实体类mybatis 一对一查询及一对多查询

public class Student {
    private Integer sid;
    private String sname;
    private String sex;
    private String phone;
    private String address;
    private ClassInfo classInfo;

//省略了get set方法和toString方法（alt+insert:）
}

  5.1班级（ClassInfo）表的xml文件及接口

mybatis 一对一查询及一对多查询

<?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是映射的dao接口-->
<mapper namespace="com.su.dao.ClassInfoDao">
<!--全查-->
    <select id="selectAll" resultType="classInfo">
        select * from classInfo
    </select>
<!--根据id查-->
    <select id="selectByCid" parameterType="int" resultType="classInfo">
        select * from classInfo where cid=#{cid};
    </select>
<!--添加-->
    <insert id="insert" parameterType="classInfo">
     insert into classInfo(cname,cinfo) values (#{cname},#{cinfo});
    </insert>
<!--删除-->
    <delete id="delete" parameterType="int">
       delete from classInfo where cid=#{cid};
    </delete>
</mapper>

mybatis 一对一查询及一对多查询

package com.su.dao;

import com.su.bean.ClassInfo;

import java.util.List;

public interface ClassInfoDao {
    //全查
    List<ClassInfo> selectAll();
    //根据id查
    ClassInfo selectByCid(int cid);
    //添加
    int insert(ClassInfo classInfo);
    //删除
    int delete(int cid);
}

5.2学生(Student)表的xml文件及接口

mybatis 一对一查询及一对多查询

<?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.su.dao.StudentDao">
        <!--定义手动映射关系：数据库中字段 和 类的属性 的映射-->
        <resultMap id="studentMap" type="com.su.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: 自动关联查询：根据外键did执行dept表的关联查询，把查询出来部门信息封装到emp的dept属性 -->
            <association column="cid" select="com.su.dao.ClassInfoDao.selectByCid" property="classInfo">
            </association>
        </resultMap>

        <!-- 1.完成学生信息的全查询，要求配置一对一能够查询学生关联的班级信息   -->
        <select id="selectAll" resultMap="studentMap">
        select * from student;
    </select>
        <!--2.完成学生信息的模糊查询，查询条件包含姓名、电话、地址等，要求使用动态SQL语句-->
        <select id="seach" parameterType="student" resultMap="studentMap">
            select * from student
            <where>
                <if test="sex != null and sex != ''">
                    and sex like concat('%',#{sex},'%')
                </if>
                <if test="address != null and address != ''">
                    and address like concat('%',#{address},'%')
                </if>
            </where>
        </select>
        <!-- 3. 完成学生信息的修改，要求使用动态SQL语句  -->
        <update id="update" parameterType="student">
            update student
            <set>
                <if test="sname!=null and sname!=''">
                    sname=#{sname},
                </if>
                <if test="sex!=null and sex!=''">
                    sex=#{sex},
                </if>
                <if test="phone!=null and phone!=''">
                    phone=#{phone},
                </if>
                <if test="address!=null and address!=''">
                    address=#{address},
                </if>

            </set>
            where sid=#{sid};
        </update>
</mapper>

mybatis 一对一查询及一对多查询

package com.su.dao;

import com.su.bean.ClassInfo;
import com.su.bean.Student;

import java.util.List;

public interface StudentDao {
    //   全查
    List<Student> selectAll();
    //   修改
    int update(Student student);
    //   模糊查询
    List<Student>seach(Student keyword);

}

ClassInfo表（全查，根据id查，删除，新增的测试类）

package com.su.test;

import com.su.bean.ClassInfo;
import com.su.dao.ClassInfoDao;
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 ClassInfoTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    ClassInfoDao classInfoDao=null;
    //Before：前置通知
    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        sqlSession=factory.openSession();
        classInfoDao=sqlSession.getMapper(ClassInfoDao.class);
    }
    //     全查
    @Test
    public void testSelectAll(){
        List<ClassInfo> classInfoList = classInfoDao.selectAll();
        for (ClassInfo classInfo:classInfoList){
            System.out.println(classInfo);
        }
    }
    //根据id查
    @Test
    public void testSelectByCid(){
        ClassInfo classInfo = classInfoDao.selectByCid(1);
        System.out.println(classInfo);
    }
    //添加
    @Test
    public void testInsert(){
        //定义user对象，封装数据
        ClassInfo classInfo = new ClassInfo();
        classInfo.setCid(111);
        classInfo.setCname("111");
        classInfo.setCinfo("111");
        int n = classInfoDao.insert(classInfo);
        if (n > 0) {
            System.out.println("添加成功");
        } else {
            System.out.println("添加失败");
        }
    }
    //删除
    @Test
    public void testDelete(){
        int i = classInfoDao.delete(7);
        if (i>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

Student(全查，模糊查询，修改的测试类) 

package com.su.test;

import com.su.bean.Student;
import com.su.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);
    }

    //1.完成学生信息的全查询，要求配置一对一能够查询学生关联的班级信息
    @Test
    public void testSelectAll() {
        List<Student> studentList = studentDao.selectAll();
        for (Student student : studentList) {
            System.out.println(student);
        }
    }

    //2.模糊查询
    @Test
    public void testSeach() {
        Student seach = new Student();
        seach.setSex("女");
        seach.setAddress("郑州");
        List<Student> studentList = studentDao.seach(seach);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }

    //3.修改
    @Test
    public void testUpdate() {
        Student student = new Student();
        student.setSid(3);
        student.setSname("小小");
        student.setSex("女");
        student.setPhone("45678931");
        student.setAddress("河南");
        int i = studentDao.update(student);
        if (i > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
    }

    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

 效果展示：

ClassInfo表

mybatis 一对一查询及一对多查询

 Student表mybatis 一对一查询及一对多查询

 一对多查询创建如下：

班级—–学生：为一对多的关系 即：一个班级可以有多个学生。

嵌套结果使用双标签\<collection>\</collection>

注：外部文件（jdbc）和日志打印(log4j)以及mybatis.xml核心配置文件如一对一相同

 班级（ClassInfo）表的实体类mybatis 一对一查询及一对多查询

public class ClassInfo {
    private Integer cid;
    private String cname;
    private String cinfo;
    List<Student> students;

 //省略了get set方法和toString方法（alt+insert:）
}

  学生(Student)表的实体类

mybatis 一对一查询及一对多查询

public class Student {
    private Integer sid;
    private String sname;
    private String sex;
    private String phone;
    private String address;
    private Integer cid;

//省略了get set方法和toString方法（alt+insert:）
}

 学生(Student)表的xml文件及接口mybatis 一对一查询及一对多查询

<?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是映射的dao接口-->
        <mapper namespace="com.su.dao.ClassInfoDao">
<resultMap id="classMap" type="com.su.bean.ClassInfo">
    <id property="cid" column="cid" />
    <result property="cname" column="cname"/>
    <result property="cinfo" column="cinfo"/>
    <!--配置一对多关系：自动根据cid去关联查询student表中该cid的信息-->
    <collection column="cid" select="com.su.dao.StudentDao.selectByCid" property="students">
    </collection>
</resultMap>

<select id="selectByCid" parameterType="int" resultMap="classMap">
        select * from classInfo where cid=#{cid};
    </select>
        </mapper>

mybatis 一对一查询及一对多查询

package com.su.dao;

import com.su.bean.ClassInfo;

import java.util.List;

public interface ClassInfoDao {
    //根据id查
    ClassInfo selectByCid(int cid);

}

  班级(ClassInfo)表的xml文件及接口

<?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.su.dao.StudentDao">

    <select id="selectBySid" parameterType="int" resultType="student">
        select * from student where sid=#{sid}
    </select>

    <select id="selectBySid" parameterType="int" resultType="student">
        select * from student where cid=#{cid}
    </select>
</mapper>
package com.su.dao;

import com.su.bean.ClassInfo;
import com.su.bean.Student;

import java.util.List;

public interface StudentDao {
    Student selectBySid(int sid);

    List<Student> selectByCid(int cid);
}

ClassInfoTest测试类

package com.su.test;

import com.su.bean.ClassInfo;
import com.su.dao.ClassInfoDao;
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 ClassInfoTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    ClassInfoDao classInfoDao=null;
    //Before：前置通知
    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        sqlSession=factory.openSession();
        classInfoDao=sqlSession.getMapper(ClassInfoDao.class);
    }
    //根据id查
    @Test
    public void testSelectByCid(){
        ClassInfo classInfo = classInfoDao.selectByCid(1);
        System.out.println(classInfo);
    }


    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

StudentTest测试类

package com.su.test;

import com.su.bean.Student;
import com.su.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 testSelectByEid(){
        Student student =studentDao.selectBySid(1);
        System.out.println(student);
    }


    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

效果展示：

mybatis 一对一查询及一对多查询

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/130144.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！