使用mybatis操作数据库进行一对多关系映射示例练习8

虾米大王

于 2023-07-26 09:28:40 发布

阅读量49

点赞数

分类专栏： Java 文章标签： mybatis 数据库 java

本文链接：https://blog.csdn.net/modern358/article/details/130667522

版权

Java 专栏收录该内容

1267 篇文章 4 订阅

订阅专栏

还是延续之前的空maven工程，新建moudle，命名为mybatis-07

项目总pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.shrimpking</groupId>
    <artifactId>mybatistest02</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-01-self</module>
        <module>mybatis-02</module>
        <module>mybatis-03</module>
        <module>mybatis-04</module>
        <module>mybatis-05</module>
        <module>mybatis-05</module>
        <module>mybatis-06</module>
        <module>mybatis-07</module>
        <module>mybatis-08</module>
        <module>mybatis-09-blog</module>
    </modules>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

项目具体情况截图

下面依次贴出代码：

mybatis-config.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="db.properties"/>

    <typeAliases>
        <typeAlias type="com.shrimpking.pojo.Student" alias="Student"/>
        <typeAlias type="com.shrimpking.pojo.Teacher" alias="Teacher"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/shrimpking/dao/TeacherMapper.xml"/>
        <mapper resource="com/shrimpking/dao/StudentMapper.xml"/>
    </mappers>
</configuration>

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
username=root
password=mysql123

MyBatisUtils.java

package com.shrimpking.utils;

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 java.io.InputStream;

/**
 * @author user1
 */
public class MyBatisUtils
{

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try
        {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession()
    {
        return sqlSessionFactory.openSession();
    }
}

Student.java

package com.shrimpking.pojo;

public class Student
{
    private int id;
    private String name;
    private Teacher teacher;

    public Student()
    {
    }

    public Student(int id, String name, Teacher teacher)
    {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public Teacher getTeacher()
    {
        return teacher;
    }

    public void setTeacher(Teacher teacher)
    {
        this.teacher = teacher;
    }

    @Override
    public String toString()
    {
        return "Student{" + "id=" + id + ", name='" + name + '\'' + ", teacher=" + teacher + '}';
    }
}

Teacher.java

package com.shrimpking.pojo;

public class Teacher
{
    private int id;
    private String name;

    public Teacher()
    {
    }

    public Teacher(int id, String name)
    {
        this.id = id;
        this.name = name;
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    @Override
    public String toString()
    {
        return "Teacher{" + "id=" + id + ", name='" + name + '\'' + '}';
    }
}

数据库：

create table user(
	id int(20) not null,
	name varchar(30) default null,
	pwd varchar(30) default null,
	primary key(id)
);

insert into user values(1,'张三','123456');
insert into user values(2,'李四','123456');
insert into user values(3,'王五','123456');


create table teacher(
	id int(10) not null,
	name varchar(30) default null,
	primary key(id)
);

insert into teacher(id,name) values(1,"啥老师");

create table student(
	id int(10) not null,
	name varchar(30) default null,
	tid int(10) default null,
	primary key(id),
	key fktid(tid),
	constraint fktid foreign key(tid) references teacher(id)
);

insert into student(id,name,tid) values(1,"小明",1);
insert into student(id,name,tid) values(2,"小花",1);
insert into student(id,name,tid) values(3,"小妞",1);
insert into student(id,name,tid) values(4,"小宁",1);
insert into student(id,name,tid) values(5,"小王",1);

select 
s.id as sid,
s.name as sname,
s.tid as stid,
t.id as tid,
t.name as tname
from student s,teacher t where s.tid = t.id


select 
s.id as sid,
s.name as sname,
t.id as tid,
t.name as tname
from teacher t,student s
where t.id = s.tid

create table blog(
	id varchar(50) not null primary key,
	title varchar(100) not null comment '标题',
	author varchar(30) not null comment '作者',
	create_time datetime not null comment '创建时间',
	views int(30) not null comment '浏览量'
);

StudentMapper.java

package com.shrimpking.dao;

import com.shrimpking.pojo.Student;

import java.util.List;

public interface StudentMapper
{

    List<Student> getStudentList();

    List<Student> getStudentList2();
}

TeacherMapper.java

package com.shrimpking.dao;

import com.shrimpking.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper
{

    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacherById(@Param("tid") int id);


}

StudentMapper.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.shrimpking.dao.StudentMapper">

    <!--
        方式二，按照结果查询

    -->
    <select id="getStudentList2" resultMap="studentTeacher2">
        select s.id as sid,
        s.name as sname,
        t.name as tname
        from student s,teacher t
        where s.tid = t.id
    </select>

    <resultMap id="studentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>


    <!--
    方式一，嵌套查询
<select id="getStudentList" resultMap="studentTeacher">
    select * from student;
</select>

<resultMap id="studentTeacher" type="Student">
    <result column="id" property="id"/>
    <result column="name" property="name"/>
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher;
</select>

-->

</mapper>

TeacherMapper.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.shrimpking.dao.TeacherMapper">

</mapper>

UserDaoTest.java

package com.shrimpking.dao;

import com.shrimpking.pojo.Student;
import com.shrimpking.pojo.Teacher;
import com.shrimpking.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest
{

    @Test
    public void test()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacherById(1);
        System.out.println(teacher);
        sqlSession.close();
    }

    @Test
    public void test2()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList();
        for (Student student : studentList)
        {
            System.out.println(student);
        }
        sqlSession.close();
    }

    @Test
    public void test3()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList2();
        for (Student student : studentList)
        {
            System.out.println(student);
        }
        sqlSession.close();
    }

}