Mybatis之执行自定义SQL举例
2023-09-11 14:19:57 时间
本文说明如何使用Mybatis执行我自定义输入的SQL语句。
需要的mybaits文件包括:配置文件(mybatis-config-dao.xml 和 jdbc.properties)、接口文件(ISqlMapper.class)、xml文件 (sqlMapper.xml)、工具类(MybatisUtils.class)。
依赖包:
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>
mybatis配置文件:
mybatis-config-dao.xml
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 <!--从外部配置文件导入jdbc信息--> 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>
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 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 }
sqlMapper.xml
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>
测试代码:
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 }
自己封装的Mybatis工具类(MybatisUtils.class)
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 }
相关文章
- Mybatis:动态SQL分组查询
- Mybatis 字符绑定
- Mybatis操作原生sql
- MyBatis——mybatis基本搭建
- Introduction to MyBatis Generator Mybatis代码生成介绍
- 基于mybatis-generator代码生成工具改(链式方法实体版)
- mybatis generator配置,Mybatis自动生成文件配置,Mybatis自动生成实体Bean配置
- sql语言实践之自学SQL网(SQL Lesson 0-7)
- sql语言实践之自学SQL网(SQL Lesson10,11)
- PL/SQL DEVELOPER 导出表数 和 生成 执行快速 插入表的 sql
- 实战案例:Sql client使用sql操作FlinkCDC2Hudi、支持从savepoint恢复hudi作业
- Mybatis 批量更新(foreach成多条sql) 为什么比 java 中反复执行单条插入语句效率高?
- SQL Server: Get table primary key and Foreign Key using sql query
- sql: MySQL and Microsoft SQL Server Stored Procedures IN, OUT using csharp code
- sql server 数据分析优化实战(一)——SQL语句优化
- mybatis或hibernate框架连接数据库报错:java.sql.SQLException: Access denied for user 'Administrator'@'localhost' (using password: YES)
- 时间格式字段处理(localtimestamp)、报错:operator does not exist: integer == integer、打开本地sql执行显示、MyBatis中${}和#{}使用场景及区别
- [Oracle工程师手记] 利用 DBMS_SQLTUNE.report_sql_monitor 生成 SQL 语句的监控信息
- MyBatis參数格式化异常解决方式:MyBatisSystemException:
- (4.61)sql server执行SQL遇到错误不中断
- springboot 打印 mybatis-plus 的sql
- mybatis plus自动生成pojo试实体类
- 曹工杂谈--使用mybatis的同学,进来看看怎么在日志打印完整sql吧,在数据库可执行那种
- Mybatis+Oracle搭配insert空值报错之myBatis+mysql驱动+oracle驱动的源码分析