mybatis使用自定义sql语句（非Mapper.xmlSQL）

新建⼀个接⼝ SqlBaseMapper 封装常⽤的增删改查

public interface SqlBaseMapper {

/**

* 查询单条数据返回Map *

* @param sql sql语句

* @return Map */

Map sqlSelectOne(String sql);

/**

* 查询单条数据返回Map *

* @param sql sql语句 * @param value 参数

* @return Map */

Map sqlSelectOne(String sql, Object value); /**

* 查询单条数据返回实体类型 *

* @param sql sql语句 * @param resultType 具体类型 * @return 定义的实体类型 */

T sqlSelectOne(String sql, Class resultType);

/**

* 查询单条数据返回实体类型 *

* @param sql sql语句 * @param value 参数

* @param resultType 具体类型 * @return 定义的实体类型 */

T sqlSelectOne(String sql, Object value, Class resultType); /**

* 查询数据返回 *

* @param sql sql语句

* @return List

List> sqlSelectList(String sql);

/**

* 查询数据返回 *

* @param sql sql语句 * @param value 参数

* @return List

List> sqlSelectList(String sql, Object value); /**

* 查询数据返回 *

* @param sql sql语句 * @param resultType 具体类型 * @return List */

List sqlSelectList(String sql, Class resultType);

/**

* 查询数据返回 *

* @param sql sql语句 * @param value 参数

* @param resultType 具体类型 * @return List */

List sqlSelectList(String sql, Object value, Class resultType); /**

* 插⼊数据 *

* @param sql sql语句 * @return int */

int sqlInsert(String sql);

/**

* 插⼊数据 *

* @param sql sql语句 * @param value 参数 * @return int */

int sqlInsert(String sql, Object value); /**

* 更新数据 *

* @param sql sql语句 * @return int */

int sqlUpdate(String sql); /**

* 更新数据

*

* @param sql sql语句 * @param value 参数 * @return int */

int sqlUpdate(String sql, Object value); /**

* 删除数据 *

* @param sql sql语句 * @return int */

int sqlDelete(String sql);

/**

* 查询数据返回List *

* @param sql sql语句 * @param value 参数 * @return int */

int sqlDelete(String sql, Object value);

}

新建⼀个SqlMapper 实现SqlBaseMapper接⼝

/**

* @author chaild

* @Date 2020-7-7 14:43:35 * ⾃定义SQL查询类 */

@Component

public class SqlMapper implements SqlBaseMapper { /**

* 使⽤⽅式 *

* @Autowired private SqlMapper sqlMapper; */

private SqlSession sqlSession;

private SqlMapper.MSUtils msUtils; @Autowired

SqlSessionFactory sqlSessionFactory; public SqlMapper() { }

/**这个注解具体意思可以⾃⼰去了解⼀下**/ @PostConstruct private void init() {

this.sqlSession = sqlSessionFactory.openSession(true);

this.msUtils = new SqlMapper.MSUtils(sqlSession.getConfiguration()); }

private T getOne(List list) { if (list.size() == 1) { return list.get(0);

} else if (list.size() > 1) {

throw new TooManyResultsException(\"Expected one result (or null) to be returned by selectOne(), but found: \" + list.size()); } else {

return null; } }

/**

* 查询单条数据返回Map *

* @param sql sql语句

* @return Map */

@Override

public Map sqlSelectOne(String sql) {

List> list = this.sqlSelectList(sql); return (Map) this.getOne(list); }

/**

* 查询单条数据返回Map *

* @param sql sql语句 * @param value 参数

* @return Map */

@Override

public Map sqlSelectOne(String sql, Object value) { List> list = this.sqlSelectList(sql, value); return (Map) this.getOne(list); }

/**

* 查询单条数据返回实体类型 *

* @param sql sql语句 * @param resultType 具体类型 * @return 定义的实体类型 */

@Override

public T sqlSelectOne(String sql, Class resultType) { List list = this.sqlSelectList(sql, resultType); return this.getOne(list);

}

/**

* 查询单条数据返回实体类型 *

* @param sql sql语句 * @param value 参数

* @param resultType 具体类型 * @return 定义的实体类型 */

@Override

public T sqlSelectOne(String sql, Object value, Class resultType) { List list = this.sqlSelectList(sql, value, resultType); return this.getOne(list); }

/**

* 查询数据返回 *

* @param sql sql语句

* @return List

@Override

public List> sqlSelectList(String sql) { String msId = this.msUtils.select(sql); return this.sqlSession.selectList(msId); }

/**

* 查询数据返回 *

* @param sql sql语句 * @param value 参数

* @return List

@Override

public List> sqlSelectList(String sql, Object value) { Class parameterType = value != null ? value.getClass() : null; String msId = this.msUtils.selectDynamic(sql, parameterType); return this.sqlSession.selectList(msId, value); }

/**

* 查询数据返回 *

* @param sql sql语句 * @param resultType 具体类型 * @return List */

@Override

public List sqlSelectList(String sql, Class resultType) { String msId;

if (resultType == null) {

msId = this.msUtils.select(sql); } else {

msId = this.msUtils.select(sql, resultType); }

return this.sqlSession.selectList(msId); }

/**

* 查询数据返回 *

* @param sql sql语句 * @param value 参数

* @param resultType 具体类型 * @return List */

@Override

public List sqlSelectList(String sql, Object value, Class resultType) { Class parameterType = value != null ? value.getClass() : null; String msId;

if (resultType == null) {

msId = this.msUtils.selectDynamic(sql, parameterType); } else {

msId = this.msUtils.selectDynamic(sql, parameterType, resultType); }

return this.sqlSession.selectList(msId, value); }

/**

* 插⼊数据 *

* @param sql sql语句 * @return int */

@Override

public int sqlInsert(String sql) {

String msId = this.msUtils.insert(sql); return this.sqlSession.insert(msId); }

/**

* 插⼊数据 *

* @param sql sql语句 * @param value 参数 * @return int */

@Override

public int sqlInsert(String sql, Object value) {

Class parameterType = value != null ? value.getClass() : null; String msId = this.msUtils.insertDynamic(sql, parameterType); return this.sqlSession.insert(msId, value); }

/**

* 更新数据 *

* @param sql sql语句 * @return int */

@Override

public int sqlUpdate(String sql) {

String msId = this.msUtils.update(sql); return this.sqlSession.update(msId); }

/**

* 更新数据 *

* @param sql sql语句 * @param value 参数 * @return int */

@Override

public int sqlUpdate(String sql, Object value) {

Class parameterType = value != null ? value.getClass() : null; String msId = this.msUtils.updateDynamic(sql, parameterType); return this.sqlSession.update(msId, value); }

/**

* 删除数据 *

* @param sql sql语句 * @return int */

@Override

public int sqlDelete(String sql) {

String msId = this.msUtils.delete(sql); return this.sqlSession.delete(msId); }

/**

* 查询数据返回List *

* @param sql sql语句 * @param value 参数 * @return int */

@Override

public int sqlDelete(String sql, Object value) {

Class parameterType = value != null ? value.getClass() : null; String msId = this.msUtils.deleteDynamic(sql, parameterType); return this.sqlSession.delete(msId, value); }

/**

* 进⾏预编译 */

private class MSUtils {

private Configuration configuration; private LanguageDriver languageDriver;

private MSUtils(Configuration configuration) { this.configuration = configuration;

this.languageDriver = configuration.getDefaultScriptingLanguageInstance(); }

private String newMsId(String sql, SqlCommandType sqlCommandType) { StringBuilder msIdBuilder = new StringBuilder(sqlCommandType.toString()); msIdBuilder.append(\".\").append(sql.hashCode()); return msIdBuilder.toString(); }

private boolean hasMappedStatement(String msId) { return this.configuration.hasStatement(msId, false); }

private void newSelectMappedStatement(String msId, SqlSource sqlSource, final Class resultType) {

MappedStatement ms = (new MappedStatement.Builder(this.configuration, msId, sqlSource, SqlCommandType.SELECT)).resultMaps(new ArrayList() { {

this.add((new org.apache.ibatis.mapping.ResultMap.Builder(com.culturalCenter.placeManage.mapper.SqlMapper.MSUtils.this.configuration, \"defaultResultMap\new ArrayList(0))).build()); }

}).build();

this.configuration.addMappedStatement(ms); }

private void newUpdateMappedStatement(String msId, SqlSource sqlSource, SqlCommandType sqlCommandType) {

MappedStatement ms = (new MappedStatement.Builder(this.configuration, msId, sqlSource, sqlCommandType)).resultMaps(new ArrayList() { {

this.add((new org.apache.ibatis.mapping.ResultMap.Builder(com.culturalCenter.placeManage.mapper.SqlMapper.MSUtils.this.configuration, \"defaultResultMap\new ArrayList(0))).build()); }

}).build();

this.configuration.addMappedStatement(ms); }

private String select(String sql) {

String msId = this.newMsId(sql, SqlCommandType.SELECT); if (this.hasMappedStatement(msId)) { return msId; } else {

StaticSqlSource sqlSource = new StaticSqlSource(this.configuration, sql); this.newSelectMappedStatement(msId, sqlSource, Map.class); return msId; } }

private String selectDynamic(String sql, Class parameterType) {

String msId = this.newMsId(sql + parameterType, SqlCommandType.SELECT); if (this.hasMappedStatement(msId)) {

return msId; } else {

SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, parameterType); this.newSelectMappedStatement(msId, sqlSource, Map.class); return msId; } }

private String select(String sql, Class resultType) {

String msId = this.newMsId(resultType + sql, SqlCommandType.SELECT); if (this.hasMappedStatement(msId)) { return msId; } else {

StaticSqlSource sqlSource = new StaticSqlSource(this.configuration, sql); this.newSelectMappedStatement(msId, sqlSource, resultType); return msId; } }

private String selectDynamic(String sql, Class parameterType, Class resultType) {

String msId = this.newMsId(resultType + sql + parameterType, SqlCommandType.SELECT); if (this.hasMappedStatement(msId)) { return msId; } else {

SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, parameterType); this.newSelectMappedStatement(msId, sqlSource, resultType); return msId; } }

private String insert(String sql) {

String msId = this.newMsId(sql, SqlCommandType.INSERT); if (this.hasMappedStatement(msId)) { return msId; } else {

StaticSqlSource sqlSource = new StaticSqlSource(this.configuration, sql);

this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.INSERT); return msId; } }

private String insertDynamic(String sql, Class parameterType) {

String msId = this.newMsId(sql + parameterType, SqlCommandType.INSERT); if (this.hasMappedStatement(msId)) { return msId; } else {

SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, parameterType); this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.INSERT); return msId; } }

private String update(String sql) {

String msId = this.newMsId(sql, SqlCommandType.UPDATE); if (this.hasMappedStatement(msId)) { return msId; } else {

StaticSqlSource sqlSource = new StaticSqlSource(this.configuration, sql);

this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.UPDATE); return msId; } }

private String updateDynamic(String sql, Class parameterType) {

String msId = this.newMsId(sql + parameterType, SqlCommandType.UPDATE); if (this.hasMappedStatement(msId)) { return msId; } else {

SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, parameterType); this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.UPDATE); return msId; } }

private String delete(String sql) {

String msId = this.newMsId(sql, SqlCommandType.DELETE); if (this.hasMappedStatement(msId)) { return msId; } else {

StaticSqlSource sqlSource = new StaticSqlSource(this.configuration, sql);

this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.DELETE); return msId; } }

private String deleteDynamic(String sql, Class parameterType) {

String msId = this.newMsId(sql + parameterType, SqlCommandType.DELETE); if (this.hasMappedStatement(msId)) { return msId; } else {

SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, parameterType); this.newUpdateMappedStatement(msId, sqlSource, SqlCommandType.DELETE); return msId; } } }}

然后做⼀个 数据连接⼯⼚类

SqlSessionFactoryConfig/**

* @author chaild

* @Date 2020年6⽉23⽇18:25:22 * 创建SQL连接⼯⼚类 * */

@Configuration

public class SqlSessionFactoryConfig { @javax.annotation.Resource DruidDataSource dataSource;

@Bean @Primary

public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource);//更多参数请⾃⾏注⼊ bean.setPlugins(new Interceptor[]{new SqlInterceptor()});

Resource[] resources = new PathMatchingResourcePatternResolver() .getResources(\"classpath*:mapper/*.xml\"); bean.setMapperLocations(resources); return bean.getObject(); }}

使⽤⽰例：

@Autowired

private SqlMapper sqlMapper;###selectList

//查询，返回List

List> list = sqlMapper.selectList(\"select * from country where id < 11\");

//查询，返回指定的实体类

List countryList = sqlMapper.selectList(\"select * from country where id < 11\class);//查询，带参数

countryList = sqlMapper.selectList(\"select * from country where id < #{id}\class);

//复杂点的查询，这⾥参数和上⾯不同的地⽅，在于传⼊了⼀个对象Country country = new Country();country.setId(11);

countryList = sqlMapper.selectList(\"\class);##复杂查询使⽤map传⼊参数

Map map=new HashMap<>();map.put(\"id\map.put(\"status\

sqlMapper.sqlSelectList(\"select * from tb_admin where id=#{id} and status=#{status}\class);

###selectOne 查询单条数据

Map map = sqlMapper.selectOne(\"select * from country where id = 35\");map = sqlMapper.selectOne(\"select * from country where id = #{id}\

Country country = sqlMapper.selectOne(\"select * from country where id = 35\class);country = sqlMapper.selectOne(\"select * from country where id = #{id}\class);###insert,update,delete

###insert 插⼊数据

int result = sqlMapper.insert(\"insert into country values(1921,'天朝','TC')\");

Country tc = new Country();tc.setId(1921);

tc.setCountryname(\"天朝\");tc.setCountrycode(\"TC\");

//注意这⾥的countrycode和countryname故意写反的

result = sqlMapper.insert(\"insert into country values(#{id},#{countrycode},#{countryname})\" , tc);

###update 更新使⽤

result = sqlMapper.update(\"update country set countryname = '天朝' where id = 35\");tc = new Country();tc.setId(35);

tc.setCountryname(\"天朝\");

int result = sqlMapper.update(\"update country set countryname = #{countryname}\" + \" where id in(select id from country where countryname like 'A%')\

##delete 删除使⽤

result = sqlMapper.delete(\"delete from country where id = 35\");

result = sqlMapper.delete(\"delete from country where id = #{id}\

如果实现 了 Interceptor 类进⾏SQL⼆次处理封装，会报⼆次编译的问题