自定义sql(扩展用法)
方案一
注册sql到Mybatis Configuration中
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.yzh.cases.entity.BotIntegrationCategory;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import org.junit.platform.commons.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
@Autowired
SqlSessionFactory sqlSessionFactory;
@Test
public void doSearchDataSource() {
String sql = "SELECT * FROM table_name WHERE column_a = #{paramA} AND column_b = #{paramB} AND column_c = #{paramC}";
Configuration configuration = sqlSessionFactory.getConfiguration();
LanguageDriver driver = configuration.getDefaultScriptingLanguageInstance();
String statementId = sql.hashCode() + SqlCommandType.SELECT.toString();
if (!configuration.hasStatement(statementId)) {
SqlSource sqlSource = driver.createSqlSource(configuration, sql, Map.class);
MappedStatement.Builder builder = new MappedStatement.Builder(configuration, statementId, sqlSource, SqlCommandType.SELECT);
MappedStatement statement = builder.resultMaps(new ArrayList<ResultMap>() {
{
List<ResultMapping> resultMappings = new ArrayList<>();
resultMappings.add(new ResultMapping.Builder(configuration, "id", "id", Long.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "parentId", "parent_id", Long.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "orderNum", "order_num", Integer.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "level", "level", Integer.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "type", "type", Short.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "createTime", "create_time", Date.class).build());
resultMappings.add(new ResultMapping.Builder(configuration, "updateTime", "update_time", Date.class).build());
this.add(new ResultMap.Builder(configuration, "baseResultMap", BotIntegrationCategory.class, resultMappings).build());
}
}).build();
configuration.addMappedStatement(statement);
configuration.setCallSettersOnNulls(true);
}
SqlSession sqlSession = sqlSessionFactory.openSession(true);
Map<String, Object> params = new HashMap<>();
params.put("paramA", "valueA");
params.put("paramB", "valueB");
params.put("paramC", (short) 1);
try {
List<BotIntegrationCategory> list = sqlSession.selectList(statementId, params);
System.out.println(JSON.toJSONString(list));
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
方案二
public DataSource datasource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://ip:port/datasource?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
dataSource.setUsername("用户名");
dataSource.setPassword("密码");
return dataSource;
}
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
PathMatchingResourcePatternResolver resResolver = new PathMatchingResourcePatternResolver();
bean.setMapperLocations(new Resource[]{resResolver.getResource(ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + "/*.xml")});
return bean.getObject();
}
@Test
public void doSearch() {
DataSource datasource = datasource();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = namedParameterJdbcTemplate(datasource);
String sql = "SELECT * FROM table_name WHERE column_a = :paramA AND column_b = :paramA limit :start,:nums";
Map<String, Object> params = new HashMap<>();
params.put("paramA", "value_1");
params.put("paramB", "value_2");
params.put("start", 0);
params.put("nums", 2);
List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql, params);
System.out.println(JSON.toJSONString(list));
RowMapper<BotIntegrationCategory> rowMapper = new RowMapper<BotIntegrationCategory>() {
@Override
public BotIntegrationCategory mapRow(ResultSet rs, int rowNum) throws SQLException {
System.out.println("-----------------------" + rowNum);
BotIntegrationCategory po = new BotIntegrationCategory();
po.setId(rs.getLong("id"));
po.setParentId(rs.getLong("parent_id"));
po.setOrderNum(StringUtils.isNotBlank(rs.getString("order_num"))?Integer.parseInt(rs.getString("order_num")):null);
po.setLevel(StringUtils.isNotBlank(rs.getString("level"))?Integer.parseInt(rs.getString("level")):null);
po.setType(rs.getShort("type")); po.setClassificationName(rs.getString("classification_name"));
po.setCreateTime(rs.getDate("create_time"));
po.setUpdateTime(rs.getDate("update_time"));
po.setIsDeleted(rs.getBoolean("is_deleted"));
return po;
}
};
List<BotIntegrationCategory> query = namedParameterJdbcTemplate.query(sql, params, rowMapper);
System.out.println(JSON.toJSONString(query));
}