基于多数据源按年月日进行分表的查询

最近遇到一个项目问题:订单表单独一个数据库,根据日期每天产生一个表格,如何查询数据进行前端展示
由于订单表独立一个数据库,我们必须配置多数据源
基于xml的多数据源配置(本文采用alibaba数据源,配置c3p0的数据源请参考:https://blog.csdn.net/wangpeng047/article/details/8866239):
代码如下(applicationContext.xml):

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
     http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
     http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
     http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
     http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
     http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd"
     default-lazy-init="false">

    <!-- 定义数据源，采用Druid数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.user}" />
        <property name="password" value="${jdbc.password}" />
        <property name="filters" value="stat" />

        <property name="maxActive" value="20" />
        <property name="initialSize" value="1" />
        <property name="maxWait" value="60000" />
        <property name="minIdle" value="1" />
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <property name="validationQuery" value="SELECT 'x'" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxOpenPreparedStatements" value="20" />
    </bean>

    <!-- 定义多数据源 -->
    <bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${slave.jdbc.url}" />
        <property name="username" value="${slave.jdbc.user}" />
        <property name="password" value="${slave.jdbc.password}" />
        <property name="filters" value="stat" />

        <property name="maxActive" value="20" />
        <property name="initialSize" value="1" />
        <property name="maxWait" value="60000" />
        <property name="minIdle" value="1" />
        <property name="timeBetweenEvictionRunsMillis" value="300" />
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <property name="validationQuery" value="SELECT 'x'" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxOpenPreparedStatements" value="20" />
    </bean>

    <bean id="routingDataSource" class="com.framework.web.spring.MyRoutingDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <entry key="dataSource" value-ref="dataSource"></entry>
                <entry key="slaveDataSource" value-ref="slaveDataSource"></entry>
            </map>
        </property>

        <property name="defaultTargetDataSource" ref="dataSource" />
    </bean>
</beans>

配置数据库(application.properties):

#数据源1
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=
jdbc.user=
jdbc.password=
#数据源2
slave.jdbc.driver=com.mysql.jdbc.Driver
slave.jdbc.url=
slave.jdbc.user=
slave.jdbc.password=

数据库的表是按照年月日进行分表的,表名的格式是:具体的表+年月日;例如:aa20200101,aa20200102,aa20200103…
每天的数据都分为一个表,进行查询的时候就必须进行动态传入表名,直入主题:我是如何动态传表的
首先写一个可以生成日期天数的工具类:

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;

import cn.tisson.framework.utils.DateTimeUtils;

public class OrderUtils {
	public static List<String> geTable(Date startDate, Date endDate,String tabl) throws Exception {

        // 返回的日期集合
        List<String> list = new ArrayList<String>();

        DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        try {
          
            Calendar tempStart = Calendar.getInstance();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");// HH:mm:ss
    		SimpleDateFormat formatter2= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//
    		startDate=formatter2.parse(formatter.format(startDate)+" 00:00:00");
            tempStart.setTime(startDate);
            //System.out.println(startDate);
            Calendar tempEnd = Calendar.getInstance();
            tempEnd.setTime(endDate);
            while (tempStart.before(tempEnd)) {
                list.add(tabl+dateFormat.format(tempStart.getTime()));
                tempStart.add(Calendar.DAY_OF_YEAR, 1);
            }
            System.out.println(list);
        } catch (ParseException e) {
            e.printStackTrace();
        }
       
        return list;
    }
    //例如我想查询2020-01-12到2020-01-17的数据,以下为测试
	public static void main(String[] args) throws Exception {
		Date startDate=null;
		startDate=DateTimeUtils.parseDate("2020-01-12 12:31:30", "yyyy-MM-dd HH:mm:ss");
		Date endDate=DateTimeUtils.parseDate("2020-01-17 17:31:30", "yyyy-MM-dd HH:mm:ss");
		geTable(startDate,endDate,"");
	}
}

运行结果为:
[20200112, 20200113, 20200114, 20200115, 20200116, 20200117]

有了时间工具类,接下来我们要进行的是将表名传递到查询语句(下面只列出传递表名的controller代码):

public void list(Request request) throws Exception{
	SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	Date startDate=formatter.parse("2020-01-12 17:31:30");
	Date endDate=formatter.parse("2020-01-17 12:31:30");
	List<String> tablelist=null;	
	//假设表名是aa		
	tablelist=OrderUtils.geTable(startDate, endDate,"aa");
	//tablist的结果:[aa20200112, aa20200113, aa20200114, aa20200115, aa20200116, aa20200117]
	//接下去就是把tablelist加入到你的查询条件上去查找
	//例如findByTablelist(tablelist,)

}

xml查询语句:

<select id="findPage" resultMap="">
	<if test="startDate== null or endDate== '' ">
		<foreach item="tablestr" collection="tablelist" >
			select
			/*查询字段*/
	        <include refid="sql_column_items" />
	        from ${tablestr}//传递表名
	        <where>
	            <include refid="sql_column_where" />
	        </where>
		</foreach>
	</if>
</select>