Mybatis之执行自定义SQL举例

本文说明如何使用Mybatis执行我自定义输入的SQL语句。

需要的mybaits文件包括：配置文件（mybatis-config-dao.xml 和 jdbc.properties）、接口文件（ISqlMapper.class）、xml文件 (sqlMapper.xml)、工具类（MybatisUtils.class）。

依赖包：

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

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

 

mybatis配置文件：

mybatis-config-dao.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>
    <!--从外部配置文件导入jdbc信息-->
    <properties resource="config/jdbc.properties"></properties>

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

    <!--指定映射资源文件-->
    <mappers>
        <mapper resource="mapper/shelby/userMapper.xml"/>
        <mapper resource="mapper/shelby/sqlMapper.xml"/>
    </mappers>

</configuration>

jdbc.properties

# jdbc连接信息
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://10.15.1.232:3306/maserati_x??zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
username=root
password=

 

接口文件 ISqlMapper.class

package testTraffic.mapper.shelby;

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

public interface ISqlMapper {

    Integer insert(String statement);

    Integer delete(String statement);

    Integer update(String statement);

    List<Map<String, Object>> selectList(String statement);

    String selectOne(String statement);
}

 

sqlMapper.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="testTraffic.mapper.shelby.ISqlMapper">
    <insert id="insert" parameterType="java.lang.String">
        ${value}
    </insert>

    <select id="selectList" parameterType="java.lang.String" resultType="java.util.HashMap" useCache="false">
        ${value}
    </select>

    <select id="selectOne" parameterType="java.lang.String" resultType="java.lang.String" useCache="false">
        ${value}
    </select>

    <delete id="delete" parameterType="java.lang.String">
        ${value}
    </delete>

    <update id="update" parameterType="java.lang.String">
        ${value}
    </update>

</mapper>

 

测试代码：

package testTraffic.testDemo;

import org.apache.ibatis.session.SqlSession;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.junit.Test;
import testTraffic.mapper.shelby.ISqlMapper;
import testTraffic.mapper.shelby.UserMapper;
import testTraffic.utils.MybatisUtils;

import java.util.List;

/**
 * @Auther:GongXingRui
 * @Date:2019/1/24
 * @Description:
 **/
public class TestMybatisDemo {
    private static Logger logger = LogManager.getLogger(TestMybatisDemo.class);

    // 直接执行SQL语句
    @Test
    public void testMybatisSelectOne() {
        String sql = "select user_name from t_admin_user where id = 2";
        ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);
        String name = sqlMapper.selectOne(sql);
        logger.info(name);
    }

    // 多个查询
    @Test
    public void testMybatisSelectList() {
        String sql = "select user_name from t_admin_user";
        ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);
        List list = sqlMapper.selectList(sql);
        logger.info(list);
    }

    // 删除与插入
    @Test
    public void testMybatisDeleteInsert() {
        ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);

        String deleteSql = "delete from t_admin_user  WHERE user_name = 'testuser';";
        String insertSql = "insert into `t_admin_user` (`id`, `user_name`, `user_password`, `del_flag`, `create_time`, `update_time`) VALUES( null,'testuser','testuser123','0','2019-01-21 19:43:58','2019-01-22 19:44:03');";
        int n = sqlMapper.delete(deleteSql);
        logger.info("删除数据：" + n);
        n = sqlMapper.insert(insertSql);
        logger.info("插入数据：" + n);
//        MybatisUtils.getSession().commit();
    }

}

 

自己封装的Mybatis工具类（MybatisUtils.class）

package testTraffic.utils;

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 java.io.IOException;
import java.io.InputStream;

/**
 * @Description: 生成mybatis的session对象
 **/
public class MybatisUtils {
    private static String resource = "config/mybatis-config-dao.xml";
    private static SqlSessionFactory sqlSessionFactory = null;
    private static SqlSession session = null;


    private static void init() {
        try {
            InputStream inputStream = Resources.getResourceAsStream(resource);
//            创建工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//            创建session对象
            session = sqlSessionFactory.openSession(true);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession() {
        if (session == null) {
            init();
        }
        return session;
    }

    public static void close() {
        if (session != null) {
            session.close();
            session = null;
        }
    }

    public static <T> T getMapper(Class<T> tClass) {
        if (session == null) {
            init();
        }
        return session.getMapper(tClass);
    }

    public static void commit() {
        if (session != null) {
            session.commit();
        }
    }


}