1. Java

MYBATIS-1-第一个例子

Written by Posted on Less than 0 min read

0、编写测试数据库

SHOW DATABASES;

CREATE DATABASE `mybatis` DEFAULT CHARSET utf8; 

USE `mybatis`;

CREATE TABLE `user`(
	`uid` INT(16) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`uname` VARCHAR(32) NOT NULL,
	`upwd` VARCHAR(32) NOT NULL
)ENGINE=INNODB,DEFAULT CHARSET=utf8;

SHOW TABLES;

INSERT INTO `user` VALUES(1,"BH6AOL","123456"),
(2,"BA1AA","654321"),
(3,"BA1BB","111111");

SELECT * FROM `user`;

1、IDEA新建空maven项目，先看我的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>mybatisTest</artifactId>
    <version>1.0-SNAPSHOT</version>


    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>

        <dependency>
<!--            Mysql Driver-->
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>


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

<!--    加载src下的xml文件-->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

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

</project>

再看项目结构截图：

2、编写对应的实体类

package top.sencom.pojo;

import lombok.Builder;
import lombok.Data;
import lombok.ToString;

public class User {
    private int uid;
    private String uname;
    private String upwd;

    public User(int uid, String uname, String upwd) {
        this.uid = uid;
        this.uname = uname;
        this.upwd = upwd;
    }

    public User() {
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpwd() {
        return upwd;
    }

    public void setUpwd(String upwd) {
        this.upwd = upwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", uname='" + uname + '\'' +
                ", upwd='" + upwd + '\'' +
                '}';
    }
}

3、编写Dao层的UserMapper接口和UserMapper.xml

package top.sencom.dao;

import top.sencom.pojo.User;

import java.util.List;

public interface UserMapper {

    // 获取全部用户
    List<User> getUserList();

    // 根据id查询用户
    User getUserById(int uid);

    // 插入用户
    int insertUser(User user);

    // 修改用户
    int updateUser(User user);

    // 删除用户
    int deleteUserById(int uid);
}
<?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="top.sencom.dao.UserMapper">
    <select id="getUserList" resultType="top.sencom.pojo.User">
        select * from user
    </select>

    <select id="getUserById" parameterType="int" resultType="top.sencom.pojo.User">
        select * from user where uid = #{uid}
    </select>

    <insert id="insertUser" parameterType="top.sencom.pojo.User">
        insert into user values (#{uid},#{uname},#{upwd})
    </insert>

    <update id="updateUser" parameterType="top.sencom.pojo.User">
        update user set uname=#{uname},upwd=#{upwd} where uid = #{uid}
    </update>

    <delete id="deleteUserById" parameterType="int" >
        delete from user where uid = #{uid}
    </delete>
</mapper>

4、编写mybatisUtils工具类

4.1、首先编写recourses中的mybaits-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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?
characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="top/sencom/dao/UserMapper.xml"/>
    </mappers>
</configuration>

4.2、编写mybatisUtils

将mybatis的构建 SqlSessionFactory和从 SqlSessionFactory 中获取 SqlSession提取出来写到工具类中

package top.sencom.utils;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.io.Resources;

import java.io.IOException;
import java.io.InputStream;

public class mybatisUtils {
    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        // 获取sqlSessionFactory对象
        String resource = "mybaits-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }


}

5、最后编写测试，测试一下效果

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import top.sencom.dao.UserMapper;
import top.sencom.pojo.User;
import top.sencom.utils.mybatisUtils;

public class mybatisTest {

    @Test
    public void testMybatisOne() {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("查询所有用户:");
        for (User user : mapper.getUserList()) {
            System.out.println(user.toString());
        }
        // 关闭连接
        sqlSession.close();
    }

    @Test
    public void testMybatisTwo(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("查询所用户id为1的用户:");
        User user = mapper.getUserById(1);
        System.out.println(user.toString());

        // 关闭连接
        sqlSession.close();
    }

    @Test
    public void testMybatisThree(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("添加一个新用户:");
        User user = new User(4,"BG6FQO","qwerty");
        mapper.insertUser(user);
        sqlSession.commit();    // !!! 增删改需要提交事务
        // 关闭连接
        sqlSession.close();
    }

    @Test
    public void testMybatisFour(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println("修改用户1的密码:");
        user.setUpwd("abcdef");
        mapper.updateUser(user);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testMybatisFive(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("删除用户通过id方式:");
        mapper.deleteUserById(4);

        sqlSession.commit();
        sqlSession.close();

    }
}

看一下第一个测试的运行结果如下：

User(uid=1, uname=BH6AOL, upwd=123456)
User(uid=2, uname=BA1AA, upwd=654321)
User(uid=3, uname=BA1BB, upwd=111111)

常见问题&&解决方法

问题1

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: The server time zone value... ... ### The error may exist in top/sencom/dao/UserMapper.xml
### The error may involve top.sencom.dao.UserMapper.getUserList
### The error occurred while executing a query
### Cause: java.sql.SQLException: The server time zone value ... ...

显然是mysql连接配置出了问题并且是时区的问题，我们只要在连接配置中指定一下时区就行了例如

serverTimezone=UTC

问题2

org.apache.ibatis.binding.BindingException: Type interface top.sencom.dao.UserMapper is not known to the MapperRegistry.

	at org.apache.ibatis.binding.MapperRegistry.getMapper(MapperRegistry.java:47)
	at org.apache.ibatis.session.Configuration.getMapper(Configuration.java:845)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.getMapper(DefaultSqlSession.java:291)
	at mybatisTest.testMybatisOne(mybatisTest.java:12)
	... ...

显然是你的Mapper没有在mybaits-config.xml中注册，注册一下即可。例如：

<mappers>
<mapper resource="top/sencom/dao/UserMapper.xml"/>
</mappers>

问题3

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error building SqlSession.
### The error may exist in top/sencom/dao/UserMapper.xml
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.io.IOException: Could not find resource top/sencom/dao/UserMapper.xml
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:80)
	at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:64)
	at top.sencom.utils.mybatisUtils.<clinit>(mybatisUtils.java:19)
	... 23 more

这个错误是maven项目的一个坑点，maven 默认是不会导出src目录下的xml文件的，导致生成的target中没有我们的UserMapper.xml，我们要在pom.xml中手动配置一下build规则，如下：

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

 

Related articles

Next