mybatis mysql 自定义函数_Mybatis之执行自定义SQL举例

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

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

依赖包：

1

2 org.mybatis

3 mybatis

4 3.4.6

5

6

7

8 mysql

9 mysql-connector-java

10 5.1.47

11

mybatis配置文件：

mybatis-config-dao.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2 "http://mybatis.org/dtd/mybatis-3-config.dtd">

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

1 packagetestTraffic.mapper.shelby;2

3 importjava.util.List;4 importjava.util.Map;5

6 public interfaceISqlMapper {7

8 Integer insert(String statement);9

10 Integer delete(String statement);11

12 Integer update(String statement);13

14 List>selectList(String statement);15

16 String selectOne(String statement);17 }

sqlMapper.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4

5

6 ${value}7

8

9

10 ${value}11

12

13

14 ${value}15

16

17

18 ${value}19

20

21

22 ${value}23

24

25

测试代码：

1 packagetestTraffic.testDemo;2

3 importorg.apache.ibatis.session.SqlSession;4 importorg.apache.logging.log4j.LogManager;5 importorg.apache.logging.log4j.Logger;6 importorg.junit.Test;7 importtestTraffic.mapper.shelby.ISqlMapper;8 importtestTraffic.mapper.shelby.UserMapper;9 importtestTraffic.utils.MybatisUtils;10

11 importjava.util.List;12

13 /**

14 * @Auther:GongXingRui15 * @Date:2019/1/2416 * @Description:17 **/

18 public classTestMybatisDemo {19 private static Logger logger = LogManager.getLogger(TestMybatisDemo.class);20

21 //直接执行SQL语句

22 @Test23 public voidtestMybatisSelectOne() {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 @Test32 public voidtestMybatisSelectList() {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 @Test41 public voidtestMybatisDeleteInsert() {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 }

自己封装的Mybatis工具类(MybatisUtils.class)

1 packagetestTraffic.utils;2

3 importorg.apache.ibatis.io.Resources;4 importorg.apache.ibatis.session.SqlSession;5 importorg.apache.ibatis.session.SqlSessionFactory;6 importorg.apache.ibatis.session.SqlSessionFactoryBuilder;7

8 importjava.io.IOException;9 importjava.io.InputStream;10

11 /**

12 * @Description: 生成mybatis的session对象13 **/

14 public classMybatisUtils {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 voidinit() {21 try{22 InputStream inputStream =Resources.getResourceAsStream(resource);23 //创建工厂

24 sqlSessionFactory = newSqlSessionFactoryBuilder().build(inputStream);25 //创建session对象

26 session = sqlSessionFactory.openSession(true);27 } catch(IOException e) {28 e.printStackTrace();29 }30 }31

32 public staticSqlSession getSession() {33 if (session == null) {34 init();35 }36 returnsession;37 }38

39 public static voidclose() {40 if (session != null) {41 session.close();42 session = null;43 }44 }45

46 public static T getMapper(ClasstClass) {47 if (session == null) {48 init();49 }50 returnsession.getMapper(tClass);51 }52

53 public static voidcommit() {54 if (session != null) {55 session.commit();56 }57 }58

59

60 }