mybatis执行sql语句

红太狼 2023-10-04 19:58 15阅读 0赞

## 项目场景： ##

使用注解的方式，直接执行sql语句

--------------------

## 源码： ##

TestMyBatisController

package com.test.controller;
    
    import java.util.Map;
    import java.util.StringJoiner;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.alibaba.fastjson.JSONObject;
    import com.test.mapper.TestMapper;
    
    /**
     * 测试MyBatis执行sql
     */
    @RestController
    @RequestMapping("/test")
    public class TestMyBatisController {
    
    	@Autowired
    	private TestMapper testMapper;
    	
        @RequestMapping(value = "/insert", method = {RequestMethod.POST})
        public String insert(@RequestParam(name = "id") String id, 
        					@RequestParam(name = "data") String data,
        					@RequestParam(name = "tableName") String tableName) {
    		
    		//新增字段
            StringJoiner filedModel = new StringJoiner(",");
            StringJoiner filedValue = new StringJoiner(",");
    		
    		Map<String, Object> map = JSONObject.parseObject(data, Map.class);
    		//这里是雪花生成的id
    //		map.put("id", DefaultsCommon.getSnowflakeIdWorkerId());
    		map.put("id", id);
    		//这里是字符串时间，格式：yyyy-MM-dd HH:mm:ss
    //		map.put("create_time", DateUtils.getNowTime());
    //		map.put("update_time", DateUtils.getNowTime());
    		for (String key : map.keySet()) {
    			filedModel.add(key);
    		    filedValue.add("#{"+key+"}");
    		}
    
    		//新增sql语句
            String sql = "insert into " + tableName + "(" + filedModel + ") values (" + filedValue + ")";
            map.put("sql", sql);
    		testMapper.insert(map);
    		return "success";
    	}
    	
        @RequestMapping(value = "/update", method = {RequestMethod.PUT})
        public String update(@RequestParam(name = "id") String id, 
        					@RequestParam(name = "data") String data,
        					@RequestParam(name = "tableName") String tableName) {
    		//修改字段
            StringJoiner filed = new StringJoiner(",");
    		String keyName = "id";
    		String condition = keyName + "=" + "#{"+keyName+"}";
    		Map<String, Object> map = JSONObject.parseObject(data, Map.class);
    		//这里是字符串时间，格式：yyyy-MM-dd HH:mm:ss
    //		map.put("update_time", DateUtils.getNowTime());
    		for (String key : map.keySet()) {
    		    filed.add(key + "=" + "#{"+key+"}");
    		}
    
    		//修改sql语句
            String sql = "update " + tableName + " set " + filed + " where " + condition;
            map.put("sql", sql);
            map.put(keyName, id);
    		testMapper.update(map);
    		return "success";
    	}
    }

TestMapper

package com.test.mapper;
    
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Update;
    
    @Mapper
    public interface TestMapper {
    
    
        @Insert("${sql}")
        int insert(Map<String, Object> map);
        
        @Update("${sql}")
        int update(Map<String, Object> map);
    }

表结构很简单，id、name、remark、create\_time、update\_time

--------------------

## postman测试： ##

插入接口测试

![eecb8c98a8c04b6e96be99b8fbabd45a.png][]

更新接口测试

![fc7018e74dbc4b51a3a310334e5de0ec.png][]

这里没有做防止sql注入处理，正式环境需要自己处理一下。

--------------------

## 优化： ##

有的同学会说使用美元符号$\{\}的方式不够优雅，如果有的公司需要代码审计可能会无法通过，因为$\{\}可能会被作为代码漏洞。当然如果对于安全要求不高，用上面的方式就可以满足需求。

> 解决方案：使用SqlRunner的方式执行原生SQL，MyBatis提供了一个用于操作数据库的SqlRunner工具类，对JDBC做了很好的封装。结合工具类SQL，能够很方便地通过Java代码执行SQL语句并检索SQL执行结果。

这里对更新的代码进行改造：

SqlRunnerController.java

package com.test.controller;
    
    import java.sql.Connection;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.jdbc.SQL;
    import org.apache.ibatis.jdbc.SqlRunner;
    import org.apache.ibatis.session.SqlSession;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.SqlSessionUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.alibaba.fastjson.JSONObject;
    
    @RestController
    @RequestMapping("/sqlrunner")
    public class SqlRunnerController {
    
    	@Autowired
    	private SqlSessionTemplate sessionTemplate;
    	
    	
        @RequestMapping(value = "/update", method = {RequestMethod.PUT})
        public String updateSqlRunner(@RequestParam(name = "id") String id, 
        					@RequestParam(name = "data") String data,
        					@RequestParam(name = "tableName") String tableName) {
    		SqlSession sqlSession = null;
    		Connection connection = null;
    		SqlRunner sqlRunner = null;
    		try {
    			//获取SqlSession
    			sqlSession = SqlSessionUtils.getSqlSession(sessionTemplate.getSqlSessionFactory(),
    					sessionTemplate.getExecutorType(),sessionTemplate.getPersistenceExceptionTranslator());
    			connection = sqlSession.getConnection();
    			sqlRunner = new SqlRunner(connection);
    			
    			//修改字段
    			List<String> filed = new ArrayList<String>();
    	        List<Object> param = new ArrayList<Object>();
    	        
    			String keyName = "id";
    			String condition = keyName + "=?";
    			Map<String, Object> map = JSONObject.parseObject(data, Map.class);
    			//这里是字符串时间，格式：yyyy-MM-dd HH:mm:ss
    //			map.put("update_time", DateUtils.getNowTime());
    			for (Map.Entry<String, Object> entry : map.entrySet()) {
    			    filed.add(entry.getKey() + "=?");
    			    param.add(entry.getValue());
    			}
    
    			//修改sql语句
    			String sql = new SQL()
    					.UPDATE(tableName)
    					.SET(filed.toArray(new String[filed.size()]))
    					.WHERE(condition)
    					.toString();
    			
    	        param.add(id);
    	        
    	        //更新操作
    			sqlRunner.update(sql, param.toArray());
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally {
    			if(sqlRunner != null){
    				sqlRunner.closeConnection();
    			}
    			if(sqlSession != null){
    				//关闭SqlSession
    				SqlSessionUtils.closeSqlSession(sqlSession, sessionTemplate.getSqlSessionFactory());
    			}
    		}
    		return "success";
    	}
    	
    }

测试

![652114a6e78f415aab95e675eb46c1d5.png][]

两种方式大家按需求选择

[eecb8c98a8c04b6e96be99b8fbabd45a.png]: https://img-blog.csdnimg.cn/eecb8c98a8c04b6e96be99b8fbabd45a.png
[fc7018e74dbc4b51a3a310334e5de0ec.png]: https://img-blog.csdnimg.cn/fc7018e74dbc4b51a3a310334e5de0ec.png
[652114a6e78f415aab95e675eb46c1d5.png]: https://img-blog.csdnimg.cn/652114a6e78f415aab95e675eb46c1d5.png