在开发中我们经常遇到多数据源的情况,比如读写分离、分库分表等情况,而且不仅仅是要配置多个数据源,还得在代码中动态的切换数据源。比如写操作访问主库数据源(master),读操作访问从库数据源(slave)。
Spring框架中提供了AbstractRoutingDataSource接口来帮助我们灵活动态的切换数据源。
假设有一个订单表tb_order,表结构如下:
CREATE TABLE `tb_order` (
`id` BIGINT(10) NOT NULL AUTO_INCREMENT COMMENT '自增id主键',
`customer_name` VARCHAR(40) NOT NULL COMMENT '客户名称',
`total_price` DOUBLE (8,2) NOT NULL COMMENT '商品总价',
`amount` INT(6) NOT NULL COMMENT '商品数量',
`address` VARCHAR(200) NOT NULL COMMENT '客户详细地址',
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
tb_order表对应的dao如下:
package com.ricky.codelab.spring.jdbc.dao.impl;
import com.ricky.codelab.spring.domain.Order;
import com.ricky.codelab.spring.jdbc.dao.IOrderDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2016-10-18 23:06
*/
@Repository("orderDao")
public class OrderDaoImpl implements IOrderDao {
@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Override
public long insert(Order order) {
String sql = "INSERT INTO tb_order(customer_name,total_price,amount,address) VALUES (?,?,?,?)";
return jdbcTemplate.update(sql, order.getCustomerName(),
order.getTotalPrice(), order.getAmount(), order.getAddress());
}
@Override
public List<Order> queryOrders(){
return jdbcTemplate.query("SELECT * FROM tb_order", new RowMapper<Order>() {
@Override
public Order mapRow(ResultSet rs, int i) throws SQLException {
Order order = new Order();
order.setId(rs.getLong("id"));
order.setCustomerName(rs.getString("customer_name"));
order.setTotalPrice(rs.getDouble("total_price"));
order.setAmount(rs.getInt("amount"));
order.setAddress(rs.getString("address"));
return order;
}
});
}
}
Order类如下:
package com.ricky.codelab.spring.domain;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2016-10-18 23:02
*/
public class Order {
private Long id;
private String customerName;
private double totalPrice;
private int amount;
private String address;
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Long getId() {
return id;
}
public void setI