![mybatis 使用自定义sql 语句
[数据库教程]](/upload/202205/01/ev5gvrx0cpt.jpg)
新建一个接口 SqlBaseMapper 封装常用的增删改查
public interface SqlBaseMapper {
Map<String, Object> sqlSelectOne(String sql);
Map<String, Object> sqlSelectOne(String sql, Object value);
<T> T sqlSelectOne(String sql, Class<T> resultType);
<T> T sqlSelectOne(String sql, Object value, Class<T> resultType);
List<Map>> sqlSelectList(String sql);
List<Map>> sqlSelectList(String sql, Object value);
<T> List<T> sqlSelectList(String sql, Class<T> resultType);
<T> List<T> sqlSelectList(String sql, Object value, Class<T> resultType);
int sqlInsert(String sql);
int sqlInsert(String sql, Object value);
int sqlUpdate(String sql);
int sqlUpdate(String sql, Object value);
int sqlDelete(String sql);
int sqlDelete(String sql, Object value);
}
新建一个SqlMapper 实现SqlBaseMapper接口
@Component
public class SqlMapper implements SqlBaseMapper {
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;
}
}
@Override
public Map sqlSelectOne(String sql) {
List> list = this.sqlSelectList(sql);
return (Map) this.getOne(list);
}
@Override
public Map sqlSelectOne(String sql, Object value) {
List> list = this.sqlSelectList(sql, value);
return (Map) this.getOne(list);
}
@Override
public T sqlSelectOne(String sql, Class resultType) {
List list = this.sqlSelectList(sql, resultType);
return this.getOne(list);
}
@Override
public T sqlSelectOne(String sql, Object value, Class resultType) {
List list = this.sqlSelectList(sql, value, resultType);
return this.getOne(list);
}
@Override
public List> sqlSelectList(String sql) {
String msId = this.msUtils.select(sql);
return this.sqlSession.selectList(msId);
}
@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);
}
@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);
}
@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);
}
@Override
public int sqlInsert(String sql) {
String msId = this.msUtils.insert(sql);
return this.sqlSession.insert(msId);
}
@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);
}
@Override
public int sqlUpdate(String sql) {
String msId = this.msUtils.update(sql);
return this.sqlSession.update(msId);
}
@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);
}
@Override
public int sqlDelete(String sql) {
String msId = this.msUtils.delete(sql);
return this.sqlSession.delete(msId);
}
@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", resultType, 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", Integer.TYPE, 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
@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", Country.class);
//查询，带参数
countryList = sqlMapper.selectList("select * from country where id < #{id}", 11, Country.class);
//复杂点的查询，这里参数和上面不同的地方，在于传入了一个对象
Country country = new Country();
country.setId(11);
countryList = sqlMapper.selectList("", country, Country.class);
##复杂查询使用map传入参数
Map map=new HashMap<>();
map.put("id","21321312312312312");
map.put("status","1");
sqlMapper.sqlSelectList("select * from tb_admin where id=#{id} and status=#{status}",map,Admin.class);
###selectOne 查询单条数据
Map map = sqlMapper.selectOne("select * from country where id = 35");
map = sqlMapper.selectOne("select * from country where id = #{id}", 35);
Country country = sqlMapper.selectOne("select * from country where id = 35", Country.class);
country = sqlMapper.selectOne("select * from country where id = #{id}", 35, Country.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%‘)", tc);
##delete 删除使用
result = sqlMapper.delete("delete from country where id = 35");
result = sqlMapper.delete("delete from country where id = #{id}", 35);
如果实现 了 Interceptor 类进行SQL二次处理封装，会报二次编译的问题
mybatis 使用自定义sql 语句
原文地址：https://www.cnblogs.com/Mr-lin66/p/13378304.html
