mybatis学习小结——使用配置文件的方式实现crud

1,新建一个maven项目，配置pom.xml,引入相关包

<dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

2，新建mybatis核心配置文件mybatis-config.xml,配置数据源。配置数据源可以有两种配置方式：

方式一：将数据库连接信息写入另一个properties文件，然后在mybatis-config.xml中引用

方式二：直接在mybatis-config.xml中配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test
jdbc.username=root
jdbc.password=123456

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

    <!--fatal: the different elements label in configuration have stable order-->

    <!--define datasoure method1: config in a properties file and import here-->
    <!--import database config file-->
    <properties resource="db.properties"></properties>

    <!--define nickname-->
    <typeAliases>
        <!--<typeAlias type="com.sohu.zzy.domain.User" alias="User"></typeAlias>-->
        <package name="com.sohu.zzy.domain"></package>
    </typeAliases>

    <!--define datasource-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--define datasoure method2: config in here directly-->
    <!--<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"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>-->


    <!--define mapperxml file source ,package-->
    <mappers>
        <!--<package name="mybatis"></package>-->
        <mapper resource="mybatis/UserMapper.xml"></mapper>
    </mappers>

</configuration>

3，配置文件中的标签说明：

A.<typealiases>

起别名，给某个类起个别名，如果不起别名的话，则一般我们调用、配置的时候需要写包含包名的全路径；配置别名之后，则可以直接使用类名即可调用。两种配置方式：

（1）给单个类配置别名

（2）扫描整个包

B.<mappers>

配置mapper接口与sql的映射扫描，两种方式配置：

（1）单个文件配置

（2）扫描整个包（没有尝试成功，尚未找到原因）

特别注意：

运行过程中可能遇到找不到usermapper方法的映射的报错。

如果出现：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)

一般的原因是Mapper interface和xml文件的定义对应不上，需要检查包名，namespace，函数名称等能否对应上，需要比较细致的对比，我经常就是写错了一两个字母搞的很长时间找不到错误

按以下步骤一一执行：

a：检查xml文件所在的package名称是否和interface对应的package名称一一对应

b：检查xml文件的namespace是否和xml文件的package名称一一对应

c：检查函数名称能否对应上

d：去掉xml文件中的中文注释

e：随意在xml文件中加一个空格或者空行然后保存

一般来说到此就可以排除错误了，如果仍未解决，还有一种可能：就是xml编译的问题，查看编译后的文件是否含有UserMapper.xml文件，如果没有那么就是编译的问题了

解决方法有两种：

a、把*Mapper.xml文件放到resource文件夹下管理

b、pom配置一下编译xml文件

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

4，创建user.java对象类，数据库建相应的表

5，创建UserMapper.java接口类，用于对数据库进行操作的接口

package com.sohu.zzy.mybatis.mapper;

import com.sohu.zzy.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * Created by zhuzhengyi on 2019/6/11.
 */
public interface UserMapper {

    User findById(Integer id);

    /**
     * 查询所有用户
     * @return
     */
    List<User> findAll();

    /**
     * multiple parameters :
    method1: use the parameter order number
    method2: use annotation in usermapper.java
    method3:
     */
    List<User> selectMulParam1(int offset,int pagenum);
    List<User> selectMulParam2(@Param(value = "offset") int offset,
                               @Param(value = "pagenum") int pagenum);
    List<User> selectMulParam3(Map<String,Object> map);


    /**
     * add user
     * @param user
     */
    public void saveUser(User user);

    /**
     * update user
     * @param user
     */
    public void updateUser(User user);

    /**
     * delete user by id
     * @param id
     */
    public void deleteById(int id);
}

6，为接口类中的操作配置相应的sql映射，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="com.sohu.zzy.mybatis.mapper.UserMapper">

    <resultMap id="userResultMap" type="User">

    </resultMap>

    <select id="findById" parameterType="Integer" resultType="User">
        select * from user where id = #{id}
    </select>

    <select id="findAll" resultMap="userResultMap">
        select * from user
    </select>

    <!--multiple parameters :
    method1: use the parameter order number
    method2: use annotation in usermapper.java
    method3:-->
    <select id="selectMulParam1" resultMap="userResultMap">
        SELECT * from user limit #{0},#{1}
    </select>
    <select id="selectMulParam2" resultMap="userResultMap">
        SELECT * from user limit #{offset},#{pagenum}
    </select>
    <select id="selectMulParam3" resultMap="userResultMap">
        SELECT * from user limit #{offset},#{pagenum}
    </select>

    <insert id="saveUser" parameterType="User" >
        insert into user(id,name,password,age,source)VALUES (#{id},#{name},#{password},#{age},#{source})
    </insert>

</mapper>

​

7,采用单例模式创建sqlsessionfactory，这样没次需要调用接口访问数据库的时候可直接从工厂类中获得一个session。如果不是要该方式也可以，但是会导致每次调用接口的时候都需要创建一个session对象，比较麻烦，所以建议使用此方式。

package com.sohu.zzy.mybatis.utils;

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

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

/**
 * Created by zhuzhengyi on 2019/6/11.
 */
public class MybatisUtils {
    /**
     * 单例模式创建sqlsessionfactory
     */

    //provide globel sqlsessionfactory
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //get config file stream
            InputStream ins = Resources.getResourceAsStream("mybatis-config.xml");
            //create sqlsessionfactory object
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(ins);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public MybatisUtils() {
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

8，创建测试用例

package com.sohu.zzy.test;

import com.sohu.zzy.domain.User;
import com.sohu.zzy.mybatis.mapper.UserMapper;
import com.sohu.zzy.mybatis.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhuzhengyi on 2019/6/11.
 */
public class UserMapperTest {

    /**
     * 使用接口userMapper,然后调用接口的方法
     */
    @Test
    public void testFindById(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //run method in proxy object
        User user = userMapper.findById(1);
        sqlSession.close();
        System.out.println(user.getName());

    }

    /**
     * 不使用接口userMapper,直接调用接口的方法
     */
    @Test
    public void testFindById2(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //run method directly without interface
        User user = sqlSession.selectOne("com.sohu.zzy.mybatis.mapper.UserMapper.findById",1);

        sqlSession.close();
        System.out.println(user.getName());

    }

    /**
     * test findall
     */
    @Test
    public void findAll(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //run method in proxy object
        List<User> list = userMapper.findAll();
        sqlSession.close();
        for(User user : list){
            System.out.println(user);
            System.out.println(user.getName());
        }
    }

    /**
     * test multiple parameters method
     */
    @Test
    public void testSelectMulParam1(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //run method in proxy object
        List<User> list = userMapper.selectMulParam1(2,2);
        sqlSession.close();
        for(User user : list){
            System.out.println(user);
            System.out.println(user.getName());
        }
    }
    @Test
    public void testSelectMulParam2(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //run method in proxy object
        List<User> list = userMapper.selectMulParam2(2,2);
        sqlSession.close();
        for(User user : list){
            System.out.println(user);
            System.out.println(user.getName());
        }
    }
    @Test
    public void testSelectMulParam3(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        Map<String,Object> map = new HashMap<String, Object>();
        map.put("offset",2);
        map.put("pagenum",2);
        //run method in proxy object
        List<User> list = userMapper.selectMulParam3(map);
        sqlSession.close();
        for(User user : list){
            System.out.println(user);
            System.out.println(user.getName());
        }
    }

    @Test
    public void saveUser(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setId(5);
        user.setName("xxxx");
        user.setPassword("asdfadf");
        user.setAge(23);
        user.setSource(2);

        userMapper.saveUser(user);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setId(2);
        user.setName("xyyy");
        user.setPassword("asdfadf");
        user.setAge(23);
        user.setSource(2);

        userMapper.updateUser(user);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        //create sqlsession object access sqlsessionfactory
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //get interface object by sqlsession
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        userMapper.deleteById(2);
        sqlSession.commit();
        sqlSession.close();
    }

}