最近做的新项目使用的是springboot+mybatis框架的,因为之前都是用的springboot+jpa,mybatis许久未用,因此上手有点生疏,这里做一下总结。
三、启动类
四、Mapping.xml
一、项目依赖准备
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency><!--添加JDBC依赖--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-->hana库<--> <dependency> <groupId>com.sap.cloud.db.jdbc</groupId> <artifactId>ngdbc</artifactId> <version>2.4.64</version> </dependency> <!-- oracle连接--> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.4</version> </dependency>
这些是项目搭建要用到的一些依赖,springboot项目基础配置了,这里注意一个数据库连接用的是springboot的jdbc而不是alibaba的druid,之后如果用PageHelper插件话会有用到!
二、application.yml
spring: # 用户配置开发(dev) profiles: # active: prod # active:dev active: test http: encoding: charset: utf-8 enabled: true force: true
然后在application-test.yml的配置为:
server: ###本地测试用 #注意:后面必须有空格 tomcat: uri-encoding: UTF-8 #项目名/端口 servlet: context-path: /demo port: 8085 spring: application: name: demo datasource: # mysql配置 # driver-class-name: com.mysql.jdbc.Driver # url: jdbc:mysql://127.0.0.0:3306/dm?useUnicode=true&characterEncoding=UTF8&useSSL=false # username: root # password: root dbcp2: max-idle: 300 max-wait-millis: 50000 min-idle: 6 initial-size: 10 #这里配置连接的数据库类型,还可以做成根据不同database-id从而达到实现多数据库动态切换的配置 type: oracle.jdbc.pool.OracleDataSource #oracle 数据库的配置 driver-class-name: oracle.jdbc.driver.OracleDriver url: jdbc:oracle:thin:@127.0.0.0:1521:orcl username: root password: **** #mybatis扫描mapping文件路径的配置 mybatis: mapper-locations: classpath:mapping/*.xml type-aliases-package: com.demo.common.entity configuration: call-setters-on-nulls: true
三、启动类
在SpringbootApplication里的配置为:
/**
* demo 服务启动类
*/
@SpringBootApplication
@MapperScan(basePackages ={"com.demo.common.mapper"})
@RestController
public class DemoServiceApplication {
public static void main(String[] args) {
ConfigurableApplicationContext ctx = SpringApplication.run(DemoServiceApplication.class, args);
String project = ctx.getEnvironment().getProperty("server.servlet.context-path");
String port = ctx.getEnvironment().getProperty("server.port");
System.out.println("服务启动完成! http://localhost:" + port + project);
}
//application文件配置格式转换
@Bean
public static PropertySourcesPlaceholderConfigurer placeholderConfigurer() {
PropertySourcesPlaceholderConfigurer c = new PropertySourcesPlaceholderConfigurer();
c.setIgnoreUnresolvablePlaceholders(true);
return c;
}
}
其中, @MapperScan这个注解就是扫描mybatis的mapping.xml文件。
四、Mapping映射
这里是dao层实现,首先创建mapper类
public interface ProcessTotalMapper {
List<Map<String,Object>> getProcessTotalByMonth(Map<String,Object> map)throws DimpException;
List<Map<String, BigDecimal>> getLimitScoreByMonth(Map<String,Object> map)throws DimpException;
List<Map<String,Object>> getTooltipData(Map<String,Object> map)throws DimpException;
List<Map<String ,Object>> findByPaging(Map<String,Object> param)throws DimpException;
}
然后创建mapping.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dimp.demo.common.mapper.ProcessTotalMapper" >
<select id="getProcessTotalByMonth" resultType="map" parameterType="map">
select *
from l2_qm3_whole_process_total T1
where T1.STATIS_MONTH <= #{STATIS_MONTH} and T1.STATIS_MONTH >= #{END_MONTH} and T1.COL = '综合' and T1.RP_ID = #{RP_ID}
order by T1.STATIS_MONTH asc
</select>
<select id="getLimitScoreByMonth" resultType="map" parameterType="map" statementType="STATEMENT">
select distinct ${statement}
from l2_qm3_whole_process_total wpt
where wpt.STATIS_MONTH = ${STATIS_MONTH} and wpt.COL= '综合' and wpt.RP_ID = ${RP_ID}
</select>
</mapper>
namespace就是mapper.java所在的项目路径,select元素中的id就是mapper中对应的方法名,parameterType是 参数类型,resultType是返回结果类型,可以是基本数据类型,也可以是自定义的实体类型,自定义实体类型方式如下:
<resultMap id="scores" type="com.dimp.demo.common.entity.ProcessScore">
<result column="STATIS_MONTH" property="statisMonth"/>
<result column="RP_ID" property="rpId"/>
<result column="RP_NAME" property="rpName"/>
<result column="SCORE" property="score"/>
</resultMap>
<select id="getProcessTotalScoreByRpId" parameterType="map" resultMap="scores">
select *
from "L2_QM3_WHOLE_PROCESS_SCORE" s
where s.STATIS_MONTH < #{STATIS_MONTH}
and s.STATIS_MONTH > #{END_MONTH}
and s.RP_ID = #{RP_ID}
</select>
用resultMap元素实现查询结果和实体类列名对应,type就是要返回的实体类,column是数据库中的列名,property是实体类的字段名,在方法中使用时,返回结果要用resultMap属性,属性值就是resultMap的id。
五、动态sql的实现
在项目需求中,遇到数据库中数据列不确定的问题,但是查询的时候如果使用select * 进行全字段查询又很不效率,所以后端通过外部配置xml文件实现动态列名的查询,那么在mybatis里要动态查询,则要通过参数形式为STATEMENT实现:
<select id="getTooltipData" resultType="map" parameterType="map" statementType="STATEMENT">
select ${statement} from l2_qm3_whole_process_total wpt
where
wpt.RP_ID= ${RP_ID} and wpt.COL !='综合' and wpt.STATIS_MONTH = ${STATIS_MONTH}
order by
wpt.STATIS_MONTH ${orderby}
</select>
<select id="findByPaging" resultType="map" statementType="STATEMENT" parameterType="map">
select ${cols }
from ${tableName }
where ${wherestr }
</select>
需要注意的是,使用statementType的话,mybatis里的sql语句为 '非预编译';
1.使用 statementType="STATEMENT",需要把 #{}改成 ${};
2.传入的参数注意在java中拼接的时候加上 "'" 引号;