实验报告1--MyBatis实现数据库操作

资料下载链接

实验报告1

一、实现思路

        完成一个员工管理系统，实现员工数据的增删改查。要求：

        要求根据员工表在数据库中创建一个employee表，完成一个员工管理系统，该系统需要实现以下几个功能：

        1、根据id查询员工信息

        2、新增员工信息

        3、根据id修改员工信息

        4、根据id删除员工信息

二、实验步骤

1、数据库环境

        mybatis数据库，employee表

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `position` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '张三', '20', '员工');
INSERT INTO `employee` VALUES ('2', '李四', '18', '员工');
INSERT INTO `employee` VALUES ('3', '王五', '35', '经理');

2、新建项目

        Name：Experiment1，GroupID：com.sw

3、引入依赖

        pom.xml文件

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.2</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.27</version>
</dependency>
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
  <scope>test</scope>
</dependency>

4、数据库连接配置文件

        src/main/resources，新建数据库连接的配置文件db.properties。要求MySQL8版本及以上

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=123456

5、MyBatis核心配置文件

        src/main/resources，新建MyBatis的核心配置文件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>
        <package name="com.sw.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,db.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- mapping文件路径配置 -->
    <mappers>
        <package name="com.sw.mapper"/>
    </mappers>
</configuration>

6、搭建项目框架

        src/main/java，新建com.sw.pojo包

        src/main/java，新建com.sw.mapper包，EmployeeMapper接口

        src/main/java，新建com.sw.util包，工具类MyBatisUtils

        src/main/resources，新建com/sw/mapper目录

7、数据封装类

        com.sw.pojo包，新建Employee类

public class Employee {
    private Integer id;
    private String name;
    private Integer age;
    private String position;
    //get、set
    //tostring
}

8、Mybatis映射文件

        src/main/resources，新建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为映射的根节点-->
<!--mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="com.sw.mapper.EmployeeMapper">
</mapper>

9、根据id查询员工信息

        com.sw.mapper包，EmployeeMapper接口

    Employee getOne(int id);

        映射文件EmployeeMapper.xml，实现select方法

<mapper namespace="com.sw.pojo.Employee">
    <select id="getOne" parameterType="int" resultType="Employee">
       select * from employee where id = #{id}
    </select>
</mapper>

        为com.sw.mapper包EmployeeMapper接口的getOne方法创建测试，右键→Generate→Test

    SqlSession sqlSession = null;
    EmployeeMapper employeeMapper = null;
​
    @Before
    public void setUp() throws Exception {
        sqlSession = MyBatisUtils.getSession();
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }
​
    @After
    public void tearDown() throws Exception {
        if (sqlSession != null){
            sqlSession.close();
        }
    }
​
    @Test
    public void getOne() {
        Employee employee = employeeMapper.getOne(1);
        System.out.println(employee);
    }

10、新增员工信息

        com.sw.mapper包，EmployeeMapper接口

    int insertOne(Employee employee);

        映射文件EmployeeMapper.xml，实现insert方法

<insert id="insertOne" parameterType="Employee"  keyProperty="id" useGeneratedKeys="true">
   insert into employee (id,name,age,position) values (null ,#{name},#{age},#{position})
</insert>

        测试

    @Test
    public void insertOne() {
        Employee employee = new Employee();
        employee.setName("赵六");
        employee.setAge(42);
        employee.setPosition("员工");
        int res = employeeMapper.insertOne(employee);
        sqlSession.commit();
        if (res>0){
            System.out.println("新增员工信息成功");
        }
    }

11、根据id修改员工信息

        com.sw.mapper包，EmployeeMapper接口

    Employee getOne(int id);

        映射文件EmployeeMapper.xml，实现select方法

    <update id="updateOne" parameterType="Employee">
       update employee set name=#{name},age=#{age},position=#{position} where id=#{id}
    </update>

        测试

    @Test
    public void updateOne() {
        Employee employee = employeeMapper.getOne(4);
        employee.setPosition("CEO");
        int res = employeeMapper.updateOne(employee);
        sqlSession.commit();
        if (res>0){
            System.out.println("根据id修改员工信息成功");
        }
    }

12、根据id删除员工信息

        com.sw.mapper包，EmployeeMapper接口

    int deleteOne(int id);

        映射文件EmployeeMapper.xml，实现select方法

    <delete id="deleteOne" parameterType="int">
       delete from employee where id = #{id}
    </delete>

        测试

    @Test
    public void deleteOne() {
        int res = employeeMapper.deleteOne(4);
        sqlSession.commit();
        if (res>0){
            System.out.println("根据id删除员工信息成功");
        }
    }