实验五、六 MyBatis配置文件编写及CRUD操作

最新推荐文章于 2023-02-02 19:13:05 发布

ayslks

最新推荐文章于 2023-02-02 19:13:05 发布

阅读量799

点赞数

文章标签： mybatis java 数据库

本文链接：https://blog.csdn.net/ayslks/article/details/127565942

版权

配置文件：

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>org.example</groupId>
    <artifactId>ch5.1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>

    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.12</version>
    </dependency>

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

</project>

main/java文件夹下创建Java类文件和Mapper接口文件

com/cqust/ch5/Employee.java

package com.cqust.ch5;

import org.apache.ibatis.type.Alias;

@Alias("employee")
public class Employee {
    
    private int id;//员工编号
    private String name;//员工名称
    private int age;//员工年龄
    private String position;//员工职位

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position;
    }

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

com/cqust/ch5/mapper/EmployeeMapper.java

package com.cqust.ch5.mapper;

import com.cqust.ch5.Employee;

import java.util.List;

public interface EmployeeMapper {

    public List<Employee> selectById(int id);//根据Id查询员工信息

    public void addEmployee(Employee employee);//添加员工信息

    public void updateById(Employee employee);//修改指定Id的员工信息

    public void deleteById(int id);//删除指定Id的员工信息

}

com/cqust/ch5/Student.java

package com.cqust.ch5;

public class Student {
    private String sno;//学号
    private String name;//姓名
    private String major;//专业

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getName() {
        return name;
    }

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

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

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

com/cqust/ch5/mapper/StudentMapper.java

package com.cqust.ch5.mapper;

import com.cqust.ch5.Student;

import java.util.List;

public interface StudentMapper {

    public List<Student> selectByArbitrarily(Student student);//多条件查询

    public List<Student> selectLessThanSno(int number);//单条件查询

}

resources文件夹下创建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>
    <typeAliases>
        <package name="com.cqust.ch5"/>
    </typeAliases>

    <environments default="development">
        <!--配置数据库连接环境，可以配置多个，通过default切换-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///javaee_test?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.cqust.ch5.mapper"/>
    </mappers>

</configuration>

数据库准备

employeeTable表(id设置自动递增)

studentTable表（sno设置自动递增）

resources文件夹下创建mapper.xml文件(注意对应的mapper.xml文件应与mapper.java文件路径一一对应)

com/cqust/ch5/mapper/EmployeeMapper.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.cqust.ch5.mapper.EmployeeMapper">

    <resultMap id="employeeResultMap" type="employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="position" property="position"/>
    </resultMap>


    <insert id="addEmployee" useGeneratedKeys="true" keyProperty="id">
        insert into employeetable(name, age, position) values (#{name}, #{age}, #{position})
    </insert>

    <update id="updateById">
        update employeetable
<set>
    <if test="name!=null and name!=''">
        name=#{name},
    </if>
    <if test="age!=null and age!=''">
        age=#{age},
    </if>
    <if test="position!=null and position!=''">
        position=#{position},
    </if>
</set>
            <where>
                id=#{id}
            </where>
    </update>
    <delete id="deleteById">
        delete from employeetable where id=#{id};
    </delete>

    <select id="selectById" resultMap="employeeResultMap">
        select * from employeetable where id=#{id};
    </select>
</mapper>

com/cqust/ch5/mapper/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.cqust.ch5.mapper.StudentMapper">
    <resultMap id="studentResultMap" type="student">
        <id column="sno" property="sno"/>
        <result column="name" property="name"/>
        <result column="major" property="major"/>
    </resultMap>

    <select id="selectByArbitrarily" resultType="student">
        select * from studenttable
            <where>
                <if test="name!=null and name!='' ">
                    name like #{name};
                </if>
                <if test="(name==null or name=='') and major!=null  and major!='' ">
                    major=#{major};
                </if>
                <if test="name==null and major==null and name=='' and major=='' ">
                    sno!=null;
                </if>
            </where>
    </select>
    <select id="selectLessThanSno" resultType="com.cqust.ch5.Student">
        select * from studenttable
<where>
    sno
    <![CDATA[
    <
    ]]>
    #{number}
</where>
    </select>
</mapper>

编写Test.java测试（此处每个测试方法对应完成各个题目）

package com.cqust.ch5;

import com.cqust.ch5.mapper.EmployeeMapper;
import com.cqust.ch5.mapper.StudentMapper;
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.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Scanner;

public class Test {

    @org.junit.Test
    public void selectByIdTest() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);

        System.out.println("输入需要查找的id：");
        int id=new Scanner(System.in).nextInt();
        List<Employee> employeeList=employeeMapper.selectById(id);
        System.out.println(employeeList);

        sqlSession.close();
    }

    @org.junit.Test
    public void addEmployeeTest() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);

        System.out.println("输入需要添加的员工信息：\n姓名：");
        String name = new Scanner(System.in).nextLine();
        System.out.println("年龄:");
        int age = Integer.parseInt(new Scanner(System.in).nextLine());
        System.out.println("职位：");
        String position = new Scanner(System.in).nextLine();
        Employee employee=new Employee();
        employee.setName(name);
        employee.setAge(age);
        employee.setPosition(position);
        employeeMapper.addEmployee(employee);
        System.out.println("success!");

        sqlSession.close();
    }

    @org.junit.Test
    public void updateByIdTest() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);

        System.out.println("输入需要修改的员工编号：");
        int id=new Scanner(System.in).nextInt();
        employeeMapper.selectById(id);
        System.out.println("输入需要修改的员工信息(年龄不可为空)：\n姓名：");
        String name = new Scanner(System.in).nextLine();
        System.out.println("年龄:");
        int age=new Scanner(System.in).nextInt();
        System.out.println("职位：");
        String position = new Scanner(System.in).nextLine();
        Employee employee=new Employee();
        employee.setId(id);
        employee.setName(name);
        employee.setAge(age);
        employee.setPosition(position);
        employeeMapper.updateById(employee);

        System.out.println("success!");

        sqlSession.close();
    }

    @org.junit.Test
    public void deleteByIdTest() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);

        System.out.println("输入需要删除的员工的id：");
        int id=new Scanner(System.in).nextInt();
        employeeMapper.deleteById(id);

        System.out.println("success!");

        sqlSession.close();
    }

    @org.junit.Test
    public void StudentSelectTest() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class);

        System.out.println("多条件查询：\n姓名：");
        String name=new Scanner(System.in).nextLine();
        System.out.println("专业：");
        String major=new Scanner(System.in).nextLine();
        System.out.println("若以上都为空则查询学号不为空");
        Student student=new Student();
        student.setName(name);
        student.setMajor(major);
        List<Student> studentList=studentMapper.selectByArbitrarily(student);
        System.out.println(studentList);

        sqlSession.close();
    }

    @org.junit.Test
    public void StudentSelectTest2() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class);

        System.out.println("单条件查询：\n学号小于多少值：");
        int number=new Scanner(System.in).nextInt();
        List<Student> studentList=studentMapper.selectLessThanSno(number);
        System.out.println(studentList);

        sqlSession.close();
    }

}