02_Mybatis

Mybatis

原生jdbc操作数据库分析

package com.test.jdbc;

import com.test.pojo.User;

import java.sql.*;

public class jdbcDemo {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        /**
         *  问题一：，每次链接数据库都需要创建链接，浪费资源
         *  解决： 链接池技术，dbcp，druid，c3p0
         */
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url  = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8";
        //获取链接
        Connection connection = DriverManager.getConnection(url, "root", "luxingchi");
        /**
         * 问题二：sql语句硬编码
         * 解决：mybatis吧sql语句写在xml文件中
         *
         * 问题三：sql语句传入参数与占位符一一对应，耦合度太高
         * 解决：
         *
         */
        String sql = "select * FROM USER ";
        //创建预编译语句执行平台
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //执行语句
        ResultSet resultSet = preparedStatement.executeQuery();
        /**
         * 问题四：结果集的处理繁琐
         * 希望：查询结果是一条记录则返回一个对象，如果是多个数据则返回一个集合
         */
        //处理结果集
        while (resultSet.next()){
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));

            System.out.println(user);

        }
        resultSet.clearWarnings();
        preparedStatement.close();
        connection.close();

    }

}

Mybatis快速入门

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>
    <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/test?characterEncoding=utf8" />
                <property name="username" value="root" />
                <property name="password" value="luxingchi" />
            </dataSource>
        </environment>
    </environments>
    <!--关联xml文件-->
    <mappers>
        <mapper resource="UserMapper.xml"></mapper>
    </mappers>

</configuration>

UserMapper.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="test">
    <!--查询所有的用户-->
    <select id="selectUser" resultType="com.test.pojo.User">
        SELECT * FROM USER
    </select>
</mapper>

 /**
     * 测试查询所有的方法
     * @throws IOException
     */
    @Test
    public void testSelectUser() throws IOException {
//      使用构建者模式new一个构建者
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//      使用mybatis提供的Resources.getResourcesAsStream方法读取全局配置文件SqlMapConfig.xml
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//      使用构建者创建一个工厂
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
//      工厂生产出一个会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> users = sqlSession.selectList("test.selectUser");
        for (int i = 0; i < users.size(); i++) {
            User user =  users.get(i);
            System.out.println(user);
        }
        sqlSession.close();
        resourceAsStream.close();
    }

测试结果
User{id=1, username='小林', password='111'}
User{id=2, username='小卢', password='222'}
User{id=3, username='老白', password='333'}
User{id=4, username='小黄', password='444'}

Mybatis的CRUD操作

根据用户id查询用户

UserMapper.xml
 <!--根据id查询用户-->
    <select id="queryById" parameterType="Integer" resultType="com.test.pojo.User">
        SELECT * FROM USER WHERE id = #{id}
    </select>

/**
     * 根据id查询用户名
     */
    @Test
    public void testQueryById () {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        User u = sqlSession.selectOne("test.queryById", 1);
        System.out.println(u);
        sqlSession.close();
    }

User{id=1, username='小林', password='111'}

根据用户名模糊查询用户列表

   <!--根据用户名模糊查询用户列表-->
    <select id="queryByUsername" parameterType="String" resultType="com.test.pojo.User">
        SELECT * FROM USER WHERE username LIKE #{username}
    </select>

   /**
     * 根据用户名模糊查询
     */
    @Test
    public void testQueryByUsername() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> list = sqlSession.selectList("test.queryByUsername", "%小%");
        for (int i = 0; i < list.size(); i++) {
            User user =  list.get(i);
            System.out.println(user);
        }
        sqlSession.close();
    }

[DEBUG][2019/06/02 19:30:26497][org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:137)]
Opening JDBC Connection
[DEBUG][2019/06/02 19:30:26682][org.apache.ibatis.datasource.pooled.PooledDataSource.popConnection(PooledDataSource.java:406)]
Created connection 1320677379.
[DEBUG][2019/06/02 19:30:26683][org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101)]
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4eb7f003]
[DEBUG][2019/06/02 19:30:26684][org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)]
==>  Preparing: SELECT * FROM USER WHERE username LIKE ? 
[DEBUG][2019/06/02 19:30:26711][org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)]
==> Parameters: %小%(String)
[DEBUG][2019/06/02 19:30:26730][org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)]
<==      Total: 3
User{id=1, username='小林', password='111'}
User{id=2, username='小卢', password='222'}
User{id=4, username='小黄', password='444'}

小结

实现添加用户

   <!--添加用户-->
    <insert id="saveUser" parameterType="com.test.pojo.User">
        insert INTO USER(username,password) VALUES (#{username},#{password})
    </insert>

  /**
     * 插入用户
     */
    @Test
    public void testSaveUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User("卢星驰","123456");
        int i = sqlSession.insert("test.saveUser", user);
        
        System.out.println((i >= 0) ? "成功" : "失败");
    }

成功

添加用户的自增主键返回

  <!--自增主键返回-->
    <insert id="saveUserAndReturn" parameterType="com.test.pojo.User">
        <selectKey order="AFTER" resultType="int" keyProperty="id">
            SELECT last_insert_id()
        </selectKey>
        insert INTO USER (username,password) VALUES (#{username},#{password})
    </insert>

 /**
     * 自增主键返回
     */
    @Test
    public void testSaveUserAndReturn() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("夏琳");
        user.setPassword("123");

        int i = sqlSession.insert("test.saveUserAndReturn", user);
        System.out.println((i >= 0) ? "success" : "feil");
        System.out.println(user);

    }

success
User{id=7, username='夏琳', password='123'}

根据用户id修改用户名

 <!--根据用户名修改id-->
    <update id="upadteById" parameterType="com.test.pojo.User" >
        UPDATE USER SET username=#{username} WHERE id=#{id}
    </update>

  /**
     * 根据更新用户名
     */
    @Test
    public void testUpadteById () {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        sqlSession.update("test.upadteById",new User(1,"更新的名字","1"));
        sqlSession.commit();
        sqlSession.close();
    }

根据用户id删除用户名

<!--根据用户id删除-->
    <delete id="deleteById" parameterType="int">
        DELETE FROM USER WHERE id=#{id}
    </delete>

/**
     * 根据id删除
     */
    @Test
    public void testDeleteById() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.delete("test.deleteById",1);
        sqlSession.commit();
        sqlSession.close();
    }

分析Mybatis解决了原生jdbc的哪些问题

Mybatis开发Dao的两种方式

原生方式

public interface UserMapper {
    //根据id查找
    User queryUserByMapperId (Integer id) throws Exception;
}

public class UserMapperImpl implements UserMapper {

    private SqlSessionFactory sqlSessionFactory;

    /*public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }*/

    public UserMapperImpl(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public User queryUserByMapperId(Integer id) throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("test.queryUserByMapperId", id);

        return user;
    }
}

public class testDao {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        sqlSessionFactory = sqlSessionFactoryBuilder.build(stream);

    }

    @Test
    public void testMapper() throws Exception {
        UserMapper userMapper = new UserMapperImpl(sqlSessionFactory);
//        UserMapper userMapper = new UserMapperImpl();
//        userMapper.
        User user = userMapper.queryUserByMapperId(4);
        System.out.println(user);

    }

Mapper方式

public interface MapperTest {
    //根据id查找
    User queryUserByMapperId (Integer id) throws Exception;
}

<mapper namespace="com.test.mapper.MapperTest">

    <select id="queryUserByMapperId" parameterType="Integer" resultType="user">
        SELECT * from USER WHERE id=#{id}
    </select>
</mapper>

public class testMapper {
    private SqlSessionFactory sqlSessionFactory;

    /**
     * 初始化方法，产生一个SqlSessionFactoryBuilder
     * @throws IOException
     */
    @Before
    public void init() throws IOException {
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
    }
    /**
     * 动态代理
     */
    @Test
    public void testMapper() throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();
//        获取MapperTest的代理对象
        MapperTest mapper = sqlSession.getMapper(MapperTest.class);
        User user = mapper.queryUserByMapperId(4);
        System.out.println(user);
    }


}

Mybatis全局配置文件的使用

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">

UserMapper.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.dt
        d">

properties文件的引入

properties（属性）

<!--
    properties：引入外部资源文件
    resource:指向classpath下的资源文件路径
    注意事项：优先级问题，外部引入的资源文件的配置项的优先级   高于  子标签property配置信息
-->
<properties resource="db.properties">
    <property name="jdbc.username" value="root1"/>
</properties>

typeAliases别名

mybatis默认支持的别名

别名	映射的类型
_byte	byte
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
map	Map

自定义别名

<!--
    typeAliases定义别名，主要给pojo定义别名
-->
<typeAliases>
    <!--
        type：给哪个类定义别名
        alias：别名名称
    -->
    <!--<typeAlias type="com.ujiuye.mybatis.pojo.User" alias="user"/>-->

    <!--批量定义别名：package指定要扫描的包路径
        注意：package会扫描包及其子包下的所有类，不要类名相重复的情况，否则异常
    -->
    <package name="com.ujiuye.mybatis.pojo"/>
</typeAliases>

mapper映射器

引入sql语句的xml配置文件，把sql语句加载到内存当中

l 万能型选手，基础crud、原始dao开发、mapper开发都可以使用这种方式

l 针对mapper方式开发dao层，我们进行一个增强（另外两种加载xml的方式）

mapper class属性：执行mapper接口类的全限定类名

​ 前提要求：

​ 1、编译之后 mapper.xml和mapper.class在同一个目录文件夹中

2、两个文件名称相同

<!--
    规范：编译之后，UserMapper接口类的class要和UserMapper.xml在同一个目录中，而且文件名相同
-->
<mapper class="com.ujiuye.mybatis.mapper.UserMapper"/>
<!--批量扫描mapper接口类，其实是扫描到包下面的一个个类，然后再按照<mapper class的形式去加载
    所以，package扫描的要求（规范）和直接使用class属性是一样的
-->
<package name="com.ujiuye.mybatis.mapper"/>

Mybatis的输入类型和输出类型

parameterType

l 传递简单数据类型（String、Int等，八种基本类型及其包装类型就叫做简单数据类型）

参考上面（# 参数名任意，$取参参数名固定为value字符串${value}）

l 传递Pojo对象（保存用户，传入pojo对象）

参考上面（不管#还是$取参，参数名为属性名）

l 传递Pojo包装对象

n 需求：使用POJO包装对象，根据用户名模糊查询用户列表

n 什么是Pojo包装对象

//    根据id查找
    User queryUserById(QueryVo queryVo) throws Exception;

  <!--使用复合的方式查询-->
    <select id="queryUserById" parameterType="QueryVo" resultType="user">
        SELECT * from USER WHERE id=#{user.id}
    </select>

/**
     * 通过id查询user表
     */
    @Test
    public void testQueryUserById () throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        QueryVo queryVo = new QueryVo();
        User u = new User();
        u.setId(1);
        queryVo.setUser(u);
        User user = mapper.queryUserById(queryVo);
        System.out.println(user);
        sqlSession.close();

    }

resultType

l 输出简单类型

查询user表总记录数

l 输出Pojo对象

参考上面

l 输出Pojo列表

参考上面

resultMap

ResultType：自动映射，要求pojo属性名和查询列名保持一致
	订单表POJO类
package com.ujiuye.mybatis.pojo;

import java.util.Date;

public class Orders {
    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", userId=" + userId +
                ", number='" + number + '\'' +
                ", createtime=" + createtime +
                ", note='" + note + '\'' +
                '}';
    }
}

<?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.ujiuye.mybatis.mapper.OrdersMapper">

    <!--
        resultType：自动映射，要求查询列名和pojo属性名保持一致
            1 最终封装的对象
            2 查询列名和pojo属性名一致
        当不一致的时候，要使用手动映射
        resultMap：配置手动映射
    -->
    <select id="queryOrdersAllList" resultMap="ordersResultMap">
        select * from orders
    </select>


    <resultMap id="ordersResultMap" type="orders">
        <!--手动配置映射关系-->
        <!--
            主键字段不一致的时候使用<id>标签配置
            普通字段不一致的时候使用<result>标签配置
            property严格区分大小写，要和pojo属性名称一致
        -->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    </resultMap>
</mapper>

Mybatis多参数传入

注意：使用Pojo封装多个数据传入Mybatis是一种万能型方式

如果拆分成多个参数传入的话，形如

//    根据用户名和id进行查询
    User queryUserByIdAndUsername(Integer id,String username) throws Exception;

那么在xml中取参的时候，取参参数名为arg0、arg1、arg2…等

arg0 对应第一个参数

arg1 对应第二个参数

arg2 对应第三个参数

arg3 对应第四个参数

……

形如：

<!--使用多参数查询-->
    <select id="queryUserByIdAndUsername" resultType="user">
        SELECT * from USER WHERE id=#{arg0} AND username=#{arg1}
    </select>