3、MyBatis框架——使用MyBatis操作数据库进行增删改查

目录

1、创建数据库

2、创建一个空工程，在空工程中添加module，创建Maven工程java项目，并完善工程目录

3、在pom.xml中配置依赖

4、在resources文件夹添加mybatis全局配置文件

5、创建实体类Admin

6、配置SQL定位标识文件

7、测试类

---

1、创建数据库

CREATE DATABASE mybatisdatabase;
USE mybatisdatabase;
CREATE TABLE IF NOT EXISTS `admin`(
`uid` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(20),
`upassword` VARCHAR(20) NOT NULL,
`address` VARCHAR(10) NOT NULL
);

INSERT INTO `admin`(`username`,`upassword`,`address`) VALUES
('张三','123456','安徽合肥包河区'),
('李四','456789','安徽合肥高新区');

2、创建一个空工程，在空工程中添加module，创建Maven工程java项目，并完善工程目录

3、在pom.xml中配置依赖

<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.mybatis</groupId>
  <artifactId>mybatisdatebase</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>mybatisdatebase</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

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

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

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

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
    </dependency>
  </dependencies>
</project>

4、在resources文件夹添加mybatis全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:127.0.0.1:3306/java2218"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="admin-mapper.xml"/>
    </mappers>
</configuration>

5、创建实体类Admin

package com.mybatis;

import lombok.Data;

@Data
public class Admin {
    private int uid;
    private String username;
    private String upassword;
    private String address;
}

6、配置SQL定位标识文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis">

    <select id="selectOne" resultType="com.mybatis.Admin">
        select * from admin where uid = #{uid}
    </select>

            <!--    resultType:返回结果类型-->
    <select id="selectAll" resultType="com.mybatis.Admin">
        select * from admin
    </select>

            <!--parameterType：参数类型-->
    <insert id="insert" parameterType="com.mybatis.Admin">
        INSERT INTO `admin`(`username`,`upassword`,`address`) VALUES (#{username},#{upassword},#{address})
    </insert>

        <!--    返回自增id:
        useGeneratedKeys：设置是否返回自增主键
        keyColum：数据库中的主键字段
        keyProperty：主键对应的实体类属性-->
    <insert id="add" parameterType="com.mybatis.Admin" useGeneratedKeys="true" keyColumn="uid" keyProperty="uid">
        INSERT INTO `admin`(`username`,`upassword`,`address`) VALUES (#{username},#{upassword},#{address})
    </insert>

<!--    delete的返回值类型和参数类型可写可不写-->
    <delete id="delete">
        delete from admin where uid = #{uid}
    </delete>

    <update id="update" parameterType="com.mybatis.Admin">
        update admin set username = #{username},upassword = #{upassword} where uid = #{uid}
    </update>
</mapper>

7、测试类

package com.mybatis;

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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class AppTest {
    SqlSessionFactory sqlSessionFactory = null;

    //创建SqlSessionFactory工厂
    @Before
    public void init(){
        System.out.println("init()方法被执行");
        InputStream resourceAsStream = null;
        try {
            resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    }

    @Test
    public void selectOne(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        System.out.println("-----根据uid查询用户信息-----");
        Admin admin = sqlSession.selectOne("com.mybatis.selectOne",1);
        System.out.println("admin = " + admin);
        //关闭资源
        sqlSession.close();
    }

    @Test
    public void selectAll(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        System.out.println("-----查询所有用户信息-----");
        List<Admin> adminList = sqlSession.selectList("com.mybatis.selectAll");
        System.out.println("adminList = " + adminList);
        sqlSession.close();
    }

    @Test
    public void insert(){
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Admin admin = new Admin();
        admin.setUsername("老六");
        admin.setUpassword("147258");
        admin.setAddress("山东曹县");
        //返回受影响的行数
        int line = sqlSession.insert("com.mybatis.insert",admin);
        System.out.println("line = " + line);
        //手动提交事务
        sqlSession.commit();
        //关闭资源
    }

    @Test
    public void add(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Admin admin = new Admin();
        admin.setUsername("老八");
        admin.setUpassword("369147");
        admin.setAddress("安徽芜湖");
        //返回受影响的行数
        int line = sqlSession.insert("com.mybatis.add",admin);
        //提交事务
        sqlSession.commit();
        //输出添加的记录（uid）
        System.out.println("admin = " + admin);
        //关闭资源
        sqlSession.close();
    }

    @Test
    public void delete(){
        //开启事务的自动提交
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        int delete = sqlSession.delete("com.mybatis.delete", 4);
        //打印影响的行数
        System.out.println("delete = " + delete);
        //关闭资源
        sqlSession.close();
    }

    @Test
    public void update(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        Admin admin = new Admin();
        admin.setUsername("小六");
        admin.setUpassword("987654");
        admin.setUid(3);
        int line = sqlSession.update("com.mybatis.update",admin);
        System.out.println("line = " + line);
        //关闭资源
        sqlSession.close();
    }
}