Mybatis学习笔记（三）——入门程序

本博客源码下载：戳我一下

Mybatis学习笔记汇总：戳我一下

一、导入jar包

二、配置log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

三、配置SqlMapConfig.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>
    <!-- 和spring整合后 environments配置将废除-->
    <environments default="development">
        <environment id="development">
        <!-- 使用jdbc事务管理-->
            <transactionManager type="JDBC" />
        <!-- 数据库连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="mysql" />
            </dataSource>
        </environment>
    </environments>

</configuration>

四、创建一个映射类（简单java类）

package com.jiayifan.po;

import java.util.Date;

/**
 * 简单java类，对应数据库中的user表
 * @author 贾一帆
 *
 */
public class User {
    //属性和数据库表结构对应
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }
}

五、配置映射文件并加入到SqlMapConfig.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">
<!-- namespace命名空间，作用是对sql进行分类化管理
注意：如果使用mapper代理方法开发，namespace有特殊的作用
 -->
<mapper namespace="test">
    <!-- 在映射文件中配置很多sql语句 -->
    <!-- 通过select执行查询数据库操作
    id：标识映射文件中的sql。称为statement的id
    #{}表示一个占位符
    parameterType:指定输入参数的类型,这里指定int型，与数据库中类型对应
    #{id}：其中id表示接收输入的参数，参数名称就是id，如果输入的参数是简单类型
    #{}中的参数名可以任意。
    resultType:指定sql输出结果所映射的java对象类型
     -->
    <select id="findUserById" parameterType="int"
     resultType="com.jiayifan.po.User">
        SELECT * FROM USER WHERE id=#{id}
    </select>
    <!-- 
        根据用户名称模糊查询，可能返回多条记录
        resultType:不管返回单条还是多条，都是一条记录锁映射的java对象类型
        ${}:表示拼接sql串，将接受到的参数的内容不加任何修饰的拼接到sql中
        使用该符号拼接sql语句可能引起sql注入
        ${value}：接收输入参数的内容，如果传入的参数是简单类型，那么${}中只能使用value
     -->
    <select id="findUserByName" parameterType="java.lang.String" resultType="com.jiayifan.po.User">
        SELECT * FROM USER WHERE username LIKE '%${value}%'
    </select>
</mapper>

<?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>
    <!-- 和spring整合后 environments配置将废除-->
    <environments default="development">
        <environment id="development">
        <!-- 使用jdbc事务管理-->
            <transactionManager type="JDBC" />
        <!-- 数据库连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="1234" />
            </dataSource>
        </environment>
    </environments>
    <!-- 加载映射文件 -->
    <mappers>
        <mapper resource="sqlmap/User.xml"/>
    </mappers>
</configuration>

六、编写程序操作查询数据库

package com.jiayifan.first;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.List;
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.jupiter.api.Test;
import com.jiayifan.po.User;

/**
 * Mybatis入门程序
 * @author 贾一帆
 *
 */
public class MybatisFirst {
    //根据id查询用户信息，得到一条记录结果
    @Test
    public void findUserById() throws IOException {
        //Mybatis的配置文件
        String resource = "SqlMapConfig.xml";
        //得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlsession = sessionFactory.openSession();
        //通过SQLSession操作数据库
        //第一个参数就是映射文件中的statement的id，等于命名空间.id
        //第二个参数是映射文件中所匹配的parameterType类型参数
        //该函数运行最终的结果就是与你映射文件中所匹配的resoultType类型的对象
        User user = sqlsession.selectOne("test.findUserById", 1);
        System.out.println(user);
        //释放资源
        sqlsession.close();
    }
    //根据用户名称来模糊查询用户列表
    @Test
    public void findUserByName() throws IOException {
        //Mybatis的配置文件
        String resource = "SqlMapConfig.xml";
        //得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlsession = sessionFactory.openSession();
        //通过SQLSession操作数据库
        //第一个参数就是映射文件中的statement的id，等于命名空间.id
        //第二个参数是映射文件中所匹配的parameterType类型参数
        //该函数运行最终的结果就是与你映射文件中所匹配的resoultType类型的对象
        List<User> list = sqlsession.selectList("test.findUserByName","小明");
        System.out.println(list);
        sqlsession.close();
    }
}

程序输出：

DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
Wed Mar 14 19:57:33 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG [main] - Created connection 1668837760.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@63787180]
DEBUG [main] - ==>  Preparing: SELECT * FROM USER WHERE id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
User [id=1, username=王五, sex=2, birthday=null, address=null]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@63787180]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@63787180]
DEBUG [main] - Returned connection 1668837760 to pool.

DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
Wed Mar 14 20:25:40 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG [main] - Created connection 323823279.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@134d26af]
DEBUG [main] - ==>  Preparing: SELECT * FROM USER WHERE username LIKE '%小明%' 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 3
[User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州], User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州], User [id=25, username=陈小明, sex=1, birthday=null, address=河南郑州]]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@134d26af]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@134d26af]
DEBUG [main] - Returned connection 323823279 to pool.

七、入门程序小结

1、#{}和${}
#{}表示一个占位符号，通过#{}可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，#{}可以有效防止sql注入。 #{}可以接收简单类型值或pojo（简单java类）属性值。 如果parameterType传输单个简单类型值，#{}括号中可以是value或其它名称。

${}表示拼接sql串，通过${}可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换，${}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是value。

2、parameterType和resultType
parameterType：指定输入参数类型，mybatis通过从输入对象中获取参数值拼接在sql中。
resultType：指定输出结果类型，mybatis将sql查询结果的一行记录数据映射为resultType指定类型的对象。

3、selectOne和selectList
selectOne查询一条记录，如果使用selectOne查询多条记录则抛出异常：

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:70)

selectList可以查询一条或多条记录。