和使用mysql一样使用。 springdata jpa mybaatis 也一样。
pom.xml:
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.16.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.sea</groupId> <artifactId>prestotest</artifactId> <version>0.0.1-SNAPSHOT</version> <name>prestotest</name> <description>prestoTest</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.62</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <!-- <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency>--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
application.properties：
#datasource.doris.type=org.apache.tomcat.jdbc.pool.DataSource datasource.doris.sql-script-encoding=UTF-8 datasource.doris.jdbc-url=jdbc:mysql://192.168.18.129:9030/sea datasource.doris.username=sea datasource.doris.password=123456 datasource.doris.driver-class-name=com.mysql.jdbc.Driver #datasource.doris.driver-class-name=com.mysql.cj.jdbc.Driver #jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
config:
import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; import javax.sql.DataSource; /** * @PACKAGE : com.sea.doristest.config * @Author : Sea * @Date : 9/21/20 11:44 AM * @Desc : **/ @Configuration public class DorisDataSourceConfig { private static Logger LOG = LoggerFactory.getLogger(DorisDataSourceConfig.class); @Bean(name = "dorisDataSource") @ConfigurationProperties(prefix ="datasource.doris") public DataSource prestoDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "dorisTemplate") public JdbcTemplate dorisJdbcTemplate(@Qualifier("dorisDataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); } }
test case:
@Autowired @Qualifier("dorisTemplate") private JdbcTemplate dorisTemplate; @Test public void testDoris() { String sql="select * from sea.user "; sql="SELECT * FROM example_db.table1"; List<Map<String, Object>> maps = dorisTemplate.queryForList(sql); System.err.println(maps); System.err.println(maps.size()); }
result:
[{siteid=4, citycode=3, username=bush, pv=3}, {siteid=1, citycode=1, username=jim, pv=2}, {siteid=5, citycode=3, username=helen, pv=3}, {siteid=3, citycode=2, username=tom, pv=2}, {siteid=2, citycode=1, username=grace, pv=2}]
5
基本使用之动态查询：
@Service @Slf4j public class BaseServiceImpl implements BaseService { @Autowired @Qualifier("dorisTemplate") public JdbcTemplate dorisJdbcTemplate; // 一、query返回数据 // 1.public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) 返回一个数据，用指定类型接收 // 2.public Map<String, Object> queryForMap(String sql, Object... args) 返回一行数据，用Map集合接收 // 3.public List<Map<String, Object>> queryForList(String sql, Object... args) 返回多行数据，用list接受，list中保存的是多个map // //*重要* 二、使用RowMapper做映射返回对象(此时需要创建实体类emp) // 4.public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) 返回一个映射对象 // 5.public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args) 了 。 返回多个映射对象存入到list集合中 /** * @param criteriaMap * @param db_table "indonesia.dailyitemsold" * @param includeFields "name,age,sales" * @param orderby "order by sold desc " * @return * @throws Exception */ public PageResult<Map<String, Object>> dynamicQueryWithPage(JSONObject criteriaMap,String db_table ,String includeFields,String orderby,Integer curPage,Integer pageSize) throws Exception { Assert.notEmpty(criteriaMap,"criteriaMap can't be null"); curPage=(curPage==null||curPage==0)?1:curPage; pageSize=(pageSize==null||pageSize==0)?10:pageSize; includeFields=StringUtils.isBlank(includeFields)?" * ":includeFields; int curPageSize=(curPage-1)*pageSize; String sql_count="SELECT count(1) FROM "+ db_table +" where "+SqlUtils.mapToWhere(criteriaMap) ; String sql="SELECT "+includeFields+" FROM "+ db_table +" where "+SqlUtils.mapToWhere(criteriaMap) +" "+orderby +" limit "+curPageSize+","+ pageSize ; log.info("sql is: {}",sql); Long totalNum = dorisJdbcTemplate.queryForObject(sql_count, long.class); List<Map<String, Object>> queryForList = dorisJdbcTemplate.queryForList(sql); PageResult<Map<String, Object>> pageResult = new PageResult<Map<String, Object>>(totalNum, curPage, pageSize, queryForList); return pageResult; } @Override public List<Map<String, Object>> queryByXXNoIn(String xxName, List<String> xxNos, String db_table,String includeFields) throws Exception { JSONObject criteriaMap = new JSONObject(); JSONObject incriteriaMap = new JSONObject(); incriteriaMap.put(xxName,String.join(",", xxNos)); Assert.notEmpty(xxNos,"xxNos can't be null"); criteriaMap.put("in", incriteriaMap); includeFields=StringUtils.isBlank(includeFields)?" * ":includeFields; String sql="SELECT "+includeFields+" FROM "+ db_table +" where "+SqlUtils.mapToWhere(criteriaMap)+" limit 1000 "; List<Map<String, Object>> queryForList = dorisJdbcTemplate.queryForList(sql); criteriaMap.clear(); incriteriaMap.clear(); return queryForList; } }
sqlUtils:
/** * ************************************************************************* * <PRE> * @ClassName: : SqlUtils * * @Description: : dynamic sql * * @Creation Date : Jun 15, 2021 1:41:48 PM * * @Author : Sea * * * </PRE> ************************************************************************** */ public class SqlUtils { // @Test // public void testSql() throws Exception { // JSONObject IncriteriaMap = new JSONObject(); // IncriteriaMap.put("sea","aa,bb,cc,dd"); // JSONObject mycriteriaMap = new JSONObject(); // mycriteriaMap.put("in", IncriteriaMap); // String inlude="name,age,total"; // String sql="select " +inlude+ " from user where "+ mapToWhere(mycriteriaMap) +" order by sold desc"; // System.err.println(sql); // } /** * JSONObject andcriteriaMap = new JSONObject(); andcriteriaMap.put("sea", "sea"); andcriteriaMap.put("number", 123); andcriteriaMap.put("double", 12.31); JSONObject orcriteriaMap = new JSONObject(); orcriteriaMap.put("sea", "sea"); orcriteriaMap.put("double", 12.31); JSONObject IncriteriaMap = new JSONObject(); IncriteriaMap.put("sea","aa,bb,cc,dd"); JSONObject mycriteriaMap = new JSONObject(); mycriteriaMap.put("and", andcriteriaMap); mycriteriaMap.put("or", andcriteriaMap); mycriteriaMap.put("in", IncriteriaMap); * @param mycriteriaMap * @return * @throws Exception */ public static String mapToWhere(JSONObject mycriteriaMap) throws Exception { String criteria=""; int criteriaMapsize = mycriteriaMap.size(); for (Entry<String, Object> okv : mycriteriaMap.entrySet()) { String option = okv.getKey(); Map<String, Object> criteriaMap=(Map<String, Object>) okv.getValue();; int msize=criteriaMap.size(); for (Entry<String, Object> kv : criteriaMap.entrySet()) { String key = kv.getKey(); Object value = kv.getValue(); if(StringUtils.isBlank(key)||StringUtils.isBlank(value+"")) { continue; } //if option is in if("in".equalsIgnoreCase(option)) { criteria+=" " +key+" in( " +convert2SqlIn(Arrays.asList((value+"").split(","))) +" )"; }else //option is and | or { if(String.class.isInstance(value)) { criteria+=" " +key+"='" +value +"' "; }else { criteria+=" " +key+"=" +value +" "; } msize--; if(msize!=0) { criteria+=" "+option+" "; } } } criteriaMapsize--; if(criteriaMapsize!=0) { criteria+=" and "; } }; Assert.notNull(criteria==""?null:criteria, "criteria can't be null"); return criteria; } /** * @Desc list<String> to sql in * @param list<String> * @return */ public static String convert2SqlIn(List<String> list){ StringBuilder sb = new StringBuilder(); if(list != null && list.size()>0){ for(int i=0,len=list.size();i<len;i++){ sb.append("'"+ list.get(i) + "'"); if(i < len-1){ sb.append(","); } } } return sb.toString(); } }