使用 JdbcTemplate 动态创建表并添加数据 动态连表查询

前面已经说了一个动态的根据当前时间创建表和插入数据的示例，那么如何进行动态的查询呢？

 

我写了这样一个公共方法，仅供参考！

 

这里需要传递两个时间间隔参数，根据时间间隔判断相差的月数，然后从起始时间开始递增月份，然后动态拼装表的名称，如果存在该表则标记需要查询

所有的SQL通过 union all 来连接，最后增加分页的参数，分页只适合MySQL数据库

当然这个示例也只是适合于按月份来存储的情况

 

代码并不是很复杂，希望大家还是通过代码来理解一下：

 

package com;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * @说明 进行测试
 * @author cuisuqiang
 * @version 1.0
 * @since
 */
public class DbTest {
	private static ApplicationContext context = null;

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
		context = new ClassPathXmlApplicationContext("applicationContext.xml");
		String sql = "select * from :TABLE_NAME";
		String key = "nm_cpu_log";
		String beginTime = "2012-1-1 12:12:12";
		String endTime = "2014-12-1 12:12:12";
		String formatkey = "yyyy_MM";
		List list = getListFromEachTable(sql,key,beginTime,endTime,formatkey,1,100);
		if(null != list){
			System.out.println(list.size());
		}else{
			System.out.println("没有数据！");
		}
	}

	/**
	 * 动态查询表数据
	 * @param sqlTemplate 要执行的单条SQL 其中表名称用 :TABLE_NAME 标识
	 * @param tableKey 表前缀，不包含和时间中间的 下划线
	 * @param dateBegin 开始时间 yyyy-MM-dd HH:mm:ss
	 * @param dateEnd 结束时间 yyyy-MM-dd HH:mm:ss
	 * @param dateFormat 时间格式化方式
	 * @param pageIndex 分页索引 从 1 开始
	 * @param pageCount 每页查询数量 无限制
	 * @return 模版查询直接返回
	 */
	@SuppressWarnings("unchecked")
	public synchronized static List getListFromEachTable(String sqlTemplate,
			String tableKey, String dateBegin, String dateEnd,
			String dateFormat, int pageIndex, int pageCount) {
		List list = null;
		try {
			// 一共有多少需要执行的SQL
			List<String> sqlList = new ArrayList<String>();
			JdbcTemplate jt = (JdbcTemplate) context.getBean("jdbcTemplate");
			// 转为时间
			Date dbegin = StringToDateTime(dateBegin);
			Date dend = StringToDateTime(dateEnd);
			// 相差的月数
			int monthCount = calculateMonthIn(dend, dbegin);
			for (int i = 0; i <= monthCount; i++) {
				// 下 N 个月的时间
				Date d = getNextMonth(dbegin, i);
				SimpleDateFormat format = new SimpleDateFormat(dateFormat);
				// 动态表名
				String tableName = tableKey + "_" + format.format(d);
				// 如果有某表
				if (getAllTableName(jt, tableName)) {
					System.out.println("存在表：" + tableName);
					// 替换名称获得SQL
					String sql = sqlTemplate.replace(":TABLE_NAME", tableName);
					sqlList.add(sql);
				}
			}
			// 实际要执行的SQL
			StringBuffer sqlGo = new StringBuffer("");
			if (sqlList.size() > 0) {
				for (int i = 0; i < sqlList.size(); i++) {
					if (i == sqlList.size() - 1) {
						sqlGo.append(sqlList.get(i));
					} else {
						sqlGo.append(sqlList.get(i) + " union all ");
					}
				}
			}
			if (sqlList.size() > 0) {
				// 增加分页
				int cindex = (pageIndex - 1) * pageCount;
				sqlGo.append(" limit " + cindex + "," + pageCount);
			}
			if (!"".equals(sqlGo.toString())) {
				System.out.println("执行的SQL：" + sqlGo.toString());
				// 执行查询
				list = jt.queryForList(sqlGo.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
			list = null;
		}
		return list;
	}

	/**
	 * 将传入的字符串按yyyy-MM-dd HH:mm:ss格式转换成对应的日期对象
	 * @param str 需要转换的字符串
	 */
	public synchronized static Date StringToDateTime(String str) {
		String _pattern = "yyyy-MM-dd HH:mm:ss";
		return StringToDate(str, _pattern);
	}

	/**
	 * 将插入的字符串按格式转换成对应的日期对象
	 * @param str 字符串
	 * @param pattern 格式
	 */
	public synchronized static Date StringToDate(String str, String pattern) {
		Date dateTime = null;
		try {
			if (str != null && !str.equals("")) {
				SimpleDateFormat formater = new SimpleDateFormat(pattern);
				dateTime = formater.parse(str);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return dateTime;
	}

	/**
	 * 两个时间相差的月数
	 * @param date1
	 * @param date2
	 * @return
	 */
	public static int calculateMonthIn(Date date1, Date date2) {
		Calendar cal1 = new GregorianCalendar();
		cal1.setTime(date1);
		Calendar cal2 = new GregorianCalendar();
		cal2.setTime(date2);
		int c = (cal1.get(Calendar.YEAR) - cal2.get(Calendar.YEAR)) * 12
				+ cal1.get(Calendar.MONTH) - cal2.get(Calendar.MONTH);
		return c;
	}

	/**
	 * 获得下几个月的时间对象
	 * @param date
	 * @param count
	 * @return
	 */
	public static Date getNextMonth(Date date, int count) {
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		c.add(Calendar.MONTH, count);
		return c.getTime();
	}

	/**
	 * 查询数据库是否有某表
	 * @param jt
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static boolean getAllTableName(JdbcTemplate jt, String tableName)
			throws Exception {
		Connection conn = jt.getDataSource().getConnection();
		ResultSet tabs = null;
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			String[] types = { "TABLE" };
			tabs = dbMetaData.getTables(null, null, tableName, types);
			if (tabs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			tabs.close();
			conn.close();
		}
		return false;
	}
}

 

 

请您到ITEYE看我的原创：http://cuisuqiang.iteye.com

或支持我的个人博客，地址：http://www.javacui.com

 

评论

3 楼 cuisuqiang 2014-10-09  

gkpzzdu 写道

楼主写的代码和我公司用的代码一模一样，你是哪个公司上班的呢

你说呢

2 楼 gkpzzdu 2014-10-09  

楼主写的代码和我公司用的代码一模一样，你是哪个公司上班的呢

1 楼 cuisuqiang 2012-09-07  

方法进行了更新：

/**
	 * 动态查询表数据
	 * @param sqlTemplate 要执行的单条SQL 其中表名称用 :TABLE_NAME 标识
	 * @param tableKey 表前缀，不包含和时间中间的 下划线
	 * @param dateBegin 开始时间 yyyy-MM-dd HH:mm:ss
	 * @param dateEnd 结束时间 yyyy-MM-dd HH:mm:ss
	 * @param dateFormat 时间格式化方式 yyyy-MM或yyyy_MM或yyyyMM
	 * @param pageIndex 分页索引 从 1 开始
	 * @param pageCount 每页查询数量 无限制
	 * @param orderName 排序的列
	 * @param orderType 排序方式
	 * @param isPager 是否分页
	 * @return 模版查询直接返回
	 */
	@SuppressWarnings("unchecked")
	public synchronized static List getListFromEachTableMonth(String sqlTemplate,
			String tableKey, String dateBegin, String dateEnd,
			String dateFormat, int pageIndex, int pageCount,
			String orderName,String orderType,boolean isPager) {
		List list = null;
		if(null == dateBegin || "".equals(dateBegin))
			dateBegin = "2012-01-01 00:00:00";
		if(null == dateEnd || "".equals(dateEnd))
			dateEnd = DataUtil.DateTimeToString(new Date());
		try {
			// 一共有多少需要执行的SQL
			List<String> sqlList = new ArrayList<String>();
			JdbcTemplate jt = (JdbcTemplate) SpringFactory.getObject("jdbcTemplate");
			// 转为时间
			Date dbegin = DataUtil.StringToDateTime(dateBegin);
			Date dend = DataUtil.StringToDateTime(dateEnd);
			// 相差的月数
			int monthCount = DataUtil.calculateMonthIn(dend, dbegin);
			for (int i = 0; i <= monthCount; i++) {
				// 下 N 个月的时间
				Date d = DataUtil.getNextMonth(dbegin, i);
				SimpleDateFormat format = new SimpleDateFormat(dateFormat);
				// 动态表名
				String tableName = tableKey + "_" + format.format(d);
				// 如果有某表
				if (getAllTableName(jt, tableName)) {
					// 替换名称获得SQL
					String sql = sqlTemplate.replace(":TABLE_NAME", tableName);
					sqlList.add(sql);
				}
			}
			// 实际要执行的SQL
			StringBuffer sqlGo = new StringBuffer("");
			if (sqlList.size() > 0) {
				for (int i = 0; i < sqlList.size(); i++) {
					if (i == sqlList.size() - 1) {
						sqlGo.append(" (" + sqlList.get(i) + ") ");
					} else {
						sqlGo.append(" (" + sqlList.get(i) + ") " + " union all ");
					}
				}
			}
			if (!"".equals(sqlGo.toString())) {
				String sql = "";
				if(null != orderName && !"".equals(orderName) && null != orderType && !"".equals(orderType)){
					sql = "select * from (" + sqlGo.toString() + ") t order by t." + orderName + " " + orderType;
				}else{
					sql = sqlGo.toString();
				}
				if(isPager){
					if (sqlList.size() > 0) {
						// 增加分页
						int cindex = (pageIndex - 1) * pageCount;
						sql += " limit " + cindex + "," + pageCount;
					}
				}
				// 执行查询
				list = jt.queryForList(sql);
			}
		} catch (Exception e) {
			list = null;
		}
		return list;
	}

@SuppressWarnings("unchecked")
	public synchronized static int getListFromEachTableMonthCount(String sqlTemplate,
			String tableKey, String dateBegin, String dateEnd,
			String dateFormat) {
		int count = 0;
		if(null == dateBegin || "".equals(dateBegin))
			dateBegin = "2012-01-01 00:00:00";
		if(null == dateEnd || "".equals(dateEnd))
			dateEnd = DataUtil.DateTimeToString(new Date());
		try {
			// 一共有多少需要执行的SQL
			List<String> sqlList = new ArrayList<String>();
			JdbcTemplate jt = (JdbcTemplate) SpringFactory.getObject("jdbcTemplate");
			// 转为时间
			Date dbegin = DataUtil.StringToDateTime(dateBegin);
			Date dend = DataUtil.StringToDateTime(dateEnd);
			// 相差的月数
			int monthCount = DataUtil.calculateMonthIn(dend, dbegin);
			for (int i = 0; i <= monthCount; i++) {
				// 下 N 个月的时间
				Date d = DataUtil.getNextMonth(dbegin, i);
				SimpleDateFormat format = new SimpleDateFormat(dateFormat);
				// 动态表名
				String tableName = tableKey + "_" + format.format(d);
				// 如果有某表
				if (getAllTableName(jt, tableName)) {
					// 替换名称获得SQL
					String sql = sqlTemplate.replace(":TABLE_NAME", tableName);
					sqlList.add(sql);
				}
			}
			// 实际要执行的SQL
			StringBuffer sqlGo = new StringBuffer("");
			String sql = "";
			if (sqlList.size() > 0) {
				for (int i = 0; i < sqlList.size(); i++) {
					if (i == sqlList.size() - 1) {
						sqlGo.append(" (" + sqlList.get(i) + ") ");
					} else {
						sqlGo.append(" (" + sqlList.get(i) + ") " + " union all ");
					}
				}
			}
			if (sqlList.size() > 0) {
				sql = "select count(*) from (" + sqlGo.toString() + ") t";
			}else{
				count = 0;
			}
			if (sqlList.size() > 0 && !"".equals(sql)) {
				// 执行查询
				count = jt.queryForInt(sql);
			}
		} catch (Exception e) {
			count = 0;
		}
		return count;
	}