mybatis执行sql语句

项目场景：

使用注解的方式，直接执行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测试：

插入接口测试

更新接口测试 

 

 这里没有做防止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";
	}
	
}

测试

 两种方式大家按需求选择