Mybatis之执行自定义SQL举例

迈不过友情╰ 2022-09-04 12:42 226阅读 0赞

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

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

依赖包：

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1         <dependency>
     2             <groupId>org.mybatis</groupId>
     3             <artifactId>mybatis</artifactId>
     4             <version>3.4.6</version>
     5         </dependency>
     6 
     7         <dependency>
     8             <groupId>mysql</groupId>
     9             <artifactId>mysql-connector-java</artifactId>
    10             <version>5.1.47</version>
    11         </dependency>

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

mybatis配置文件：

mybatis-config-dao.xml

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1 <?xml version="1.0" encoding="UTF-8" ?>
     2 <!DOCTYPE configuration
     3         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
     4         "http://mybatis.org/dtd/mybatis-3-config.dtd">
     5 <configuration>
     6     
     7     <properties resource="config/jdbc.properties"></properties>
     8 
     9     <environments default="development">
    10         <environment id="development">
    11             <transactionManager type="JDBC"/>
    12             <dataSource type="POOLED">
    13                 <property name="driver" value="${driver}"/>
    14                 <property name="url" value="${url}"/>
    15                 <property name="username" value="${username}"/>
    16                 <property name="password" value="${password}"/>
    17             </dataSource>
    18         </environment>
    19     </environments>
    20 
    21     
    22     <mappers>
    23         <mapper resource="mapper/shelby/userMapper.xml"/>
    24         <mapper resource="mapper/shelby/sqlMapper.xml"/>
    25     </mappers>
    26 
    27 </configuration>

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1 package testTraffic.mapper.shelby;
     2 
     3 import java.util.List;
     4 import java.util.Map;
     5 
     6 public interface ISqlMapper {
     7 
     8     Integer insert(String statement);
     9 
    10     Integer delete(String statement);
    11 
    12     Integer update(String statement);
    13 
    14     List<Map<String, Object>> selectList(String statement);
    15 
    16     String selectOne(String statement);
    17 }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

sqlMapper.xml

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1 <?xml version="1.0" encoding="UTF-8"?>
     2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
     3 
     4 <mapper namespace="testTraffic.mapper.shelby.ISqlMapper">
     5     <insert id="insert" parameterType="java.lang.String">
     6         ${value}
     7     </insert>
     8 
     9     <select id="selectList" parameterType="java.lang.String" resultType="java.util.HashMap" useCache="false">
    10         ${value}
    11     </select>
    12 
    13     <select id="selectOne" parameterType="java.lang.String" resultType="java.lang.String" useCache="false">
    14         ${value}
    15     </select>
    16 
    17     <delete id="delete" parameterType="java.lang.String">
    18         ${value}
    19     </delete>
    20 
    21     <update id="update" parameterType="java.lang.String">
    22         ${value}
    23     </update>
    24 
    25 </mapper>

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

测试代码：

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1 package testTraffic.testDemo;
     2 
     3 import org.apache.ibatis.session.SqlSession;
     4 import org.apache.logging.log4j.LogManager;
     5 import org.apache.logging.log4j.Logger;
     6 import org.junit.Test;
     7 import testTraffic.mapper.shelby.ISqlMapper;
     8 import testTraffic.mapper.shelby.UserMapper;
     9 import testTraffic.utils.MybatisUtils;
    10 
    11 import java.util.List;
    12 
    13 /**
    14  * @Auther:GongXingRui
    15  * @Date:2019/1/24
    16  * @Description:
    17  **/
    18 public class TestMybatisDemo {
    19     private static Logger logger = LogManager.getLogger(TestMybatisDemo.class);
    20 
    21     // 直接执行SQL语句
    22     @Test
    23     public void testMybatisSelectOne() {
    24         String sql = "select user_name from t_admin_user where id = 2";
    25         ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);
    26         String name = sqlMapper.selectOne(sql);
    27         logger.info(name);
    28     }
    29 
    30     // 多个查询
    31     @Test
    32     public void testMybatisSelectList() {
    33         String sql = "select user_name from t_admin_user";
    34         ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);
    35         List list = sqlMapper.selectList(sql);
    36         logger.info(list);
    37     }
    38 
    39     // 删除与插入
    40     @Test
    41     public void testMybatisDeleteInsert() {
    42         ISqlMapper sqlMapper = MybatisUtils.getMapper(ISqlMapper.class);
    43 
    44         String deleteSql = "delete from t_admin_user  WHERE user_name = 'testuser';";
    45         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');";
    46         int n = sqlMapper.delete(deleteSql);
    47         logger.info("删除数据：" + n);
    48         n = sqlMapper.insert(insertSql);
    49         logger.info("插入数据：" + n);
    50 //        MybatisUtils.getSession().commit();
    51     }
    52 
    53 }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

1 package testTraffic.utils;
     2 
     3 import org.apache.ibatis.io.Resources;
     4 import org.apache.ibatis.session.SqlSession;
     5 import org.apache.ibatis.session.SqlSessionFactory;
     6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
     7 
     8 import java.io.IOException;
     9 import java.io.InputStream;
    10 
    11 /**
    12  * @Description: 生成mybatis的session对象
    13  **/
    14 public class MybatisUtils {
    15     private static String resource = "config/mybatis-config-dao.xml";
    16     private static SqlSessionFactory sqlSessionFactory = null;
    17     private static SqlSession session = null;
    18 
    19 
    20     private static void init() {
    21         try {
    22             InputStream inputStream = Resources.getResourceAsStream(resource);
    23 //            创建工厂
    24             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    25 //            创建session对象
    26             session = sqlSessionFactory.openSession(true);
    27         } catch (IOException e) {
    28             e.printStackTrace();
    29         }
    30     }
    31 
    32     public static SqlSession getSession() {
    33         if (session == null) {
    34             init();
    35         }
    36         return session;
    37     }
    38 
    39     public static void close() {
    40         if (session != null) {
    41             session.close();
    42             session = null;
    43         }
    44     }
    45 
    46     public static <T> T getMapper(Class<T> tClass) {
    47         if (session == null) {
    48             init();
    49         }
    50         return session.getMapper(tClass);
    51     }
    52 
    53     public static void commit() {
    54         if (session != null) {
    55             session.commit();
    56         }
    57     }
    58 
    59 
    60 }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

[48304ba5e6f9fe08f3fa1abda7d326ab.png]: /images/20220829/f2d55757e1ad41ec817ab9e7f01a2291.png