配置多数据源
1.applicaation.properties中配置两个数据源的信息,包括url,username,password
2.在spring.xml(位于resource/spring文件夹中)中配置主数据源个数据源二。两个数据源的配置大同小异,略有区别:
数据源一
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"
primary="true" >
数据源二
<bean id="productDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"
primary="false" >
二者的id和primary不同。
<property name="url" value="${product.datasource.url}" />
<property name="username" value="${product.datasource.username}" />
<property name="password" value="${product.datasource.password}" />
根据实际情况填写value。
主数据源
/*第一句要写*/
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
数据源二
<bean id="productJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="productDataSource"/>
</bean>
3.application.properties需要加一句话
spring.main.allow-bean-definition-overriding = true
否则报错:Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
使用多数据源
主数据源使用方式同单数据源
controller调用service,service调用接口mapper,接口mapper再调用resource中的mapper.xml、
从数据源使用sql语句的方式:
在controller中直接写sql语句
//"productJdbcTemplate"在spring-jdbc.xml中查准名字
@Resource(name = "productJdbcTemplate")
private JdbcTemplate productJdbcTemplate;
final String sql = "SELECT id,name, product_no, create_time, update_time from Product";
List<Product> dataList = productJdbcTemplate.query(sql, new ProductMapperDto());
HashMap<String,Object> resMap = Maps.newHashMap();
resMap.put("dataList",dataList);
在controller中调用的new ProductMapperDto(),需要在dto文件夹或者request文件夹新建,这两个文件夹专门用来定义DTO的,数据传输对象。
import com.snow.helloworld.model.entity.Product;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ProductMapperDto implements RowMapper<Product> {
public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
Product user = new Product();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setProductNo(rs.getString("product_no"));
user.setCreateTime(rs.getTimestamp("create_time"));
user.setUpdateTime(rs.getTimestamp("update_time"));
return user;
}
}
Controller中的sql语句示例还有,
@RequestMapping(value = "/product/update",method = RequestMethod.POST,consumes = MediaType.APPLICATION_JSON_VALUE)
public BaseResponse update(@RequestBody Product product){
BaseResponse baseResponse = new BaseResponse(StatusCode.Success);
try {
log.info("接收前端传输的数据:{}",product);
if(product.getId() == null || product.getId() <= 0){
baseResponse = new BaseResponse(StatusCode.Entity_Not_Exit);
}else {
final String sql = "update Product set name = ?, product_no = ? where id = ?";
int res = productJdbcTemplate.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, product.getName());
ps.setString(2, product.getProductNo());
ps.setInt(3, product.getId());
}
});
log.info("修改内容:{}",res);
}
}catch (Exception e){
baseResponse = new BaseResponse(StatusCode.Fail.getCode(),e.getMessage());
e.printStackTrace();
}
return baseResponse;
}
其中的Product类定义在model层的entity,
@Data
@ToString
public class Product {
private Integer id;
private String name;
private String productNo;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date updateTime;
}
SpringBoot整合MyBatis逆向工程
在project下新创建module,然后配置pom.xml
<dependencies>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.7</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- mybatis-generator自动生成代码插件 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
<configuration>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</plugin>
</plugins>
</build>
步骤二:在resources下创建generatorConfig.xml,名字不能出错。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--加载资源文件,文件名视情况改-->
<properties resource="generation.properties"></properties>
<!--指定数据库jdbc驱动jar包的位置,位置视情况改-->
<classPathEntry location="G:\LocalWarehouse\mysql\mysql-connector-java\5.1.38\mysql-connector-java-5.1.38.jar"/>
<context id="testTables" targetRuntime="MyBatis3">
<commentGenerator>
<!--是否去除自动生成的注释 true是:false 否-->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库连接,配置视情况改-->
<jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="${product.datasource.url}" userId="${product.datasource.username}"
password="${product.datasource.password}"></jdbcConnection>
<!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer,为 true时把JDBC DECIMAL和NUMERIC类型解析为java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--targetPackage目标包,生成实体类的位置-->
<javaModelGenerator targetPackage="com.lwc.entity" targetProject="src/main/java">
<!--enableSubPackages,是否让schema作为包的后缀-->
<property name="enableSubPackages" value="false"/>
<!--从数据库返回的值被清除前后空格-->
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--targetProject:mapper映射文件生成的位置-->
<sqlMapGenerator targetPackage="mapper" targetProject="./src/main/resources">
<property name="enableSubPackages" value="false"></property>
</sqlMapGenerator>
<!--targetPackage:mapper接口生成的位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.lwc.dao" targetProject="src/main/java">
<property name="enableSubPackages" value="false"/>
</javaClientGenerator>
<!--指定数据库表,要和数据库中进行对应,否则将会出错,数据库表名视情况改-->
<table tableName="product" domainObjectName="product"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
selectByExampleQueryId="false"></table>
<!-- <table tableName="t_news" domainObjectName="tNews"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
selectByExampleQueryId="false"></table>-->
</context>
</generatorConfiguration>
要使用低版本的mysql,不能超过6。
步骤三:在resources下创建generation.properties,名字与generator.xml中要匹配上。
product.datasource.url = jdbc:mysql://localhost:3306/db_springboot_teach_03?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
product.datasource.username=root
product.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
步骤四:运行