Hibernate+MySQL实现动态创建视图从而实现分表数据查询

首先，讲一讲我的思路。

因为我们硬件设备向PC端传数据是每秒中1条数据，车间总共200多台设备，所以粗略计算，一个月的数据量怎么着都要上亿，因此，分表是必然的！（上一个博文讲述的是分表，今天讲查询分表数据）因为我的子表是按月建表，且表名以“年月”为后缀，如：“202001”，所以，我前端在按时间段来进行查数据的时候，我就可以根据这个时间段来找我要用到的子表名，然后做各个子表的查询并做“UNION”关联，这样就可以拿到我想要的数据了。

接下来要讲的是我的一个失败的案例。

上述存过，我将循环体放在了存储过程中，并设置了创建视图的拼接字符串“@str1”，且“@str1”的一部分放到了循环体中，但是当我开始执行测试了的时候，我发现最终的“@str1”放在循环体中的部分始终只出现了一次。在这里我的循环体是没有问题的，获取到的每一个子表名都循环过了，拼接的字符串只拼接了一次是因为我获取不到循环过程中的变量值！（如果SQL循环体可以在MySql中实现拼接，还请大佬指教！）无奈，这样的结果我无法使用，于是我就想办法，既然后MySQL可以通过concat拼接字符串，那我把创建视图的SQL放到java代码中实现拼接再通过存过抛给数据库执行不也照样可以实现？

于是，我就这样做了。具体如下：

1、根据前端传过来的时间区间分割重组(分割重组在步骤2的方法中进行，这里使用的是步骤2中分割后的字符串)，然后查找对应的表名。

@SuppressWarnings("unchecked")
	public String[] selectTables(String str1, String str2) {
		session = sessionFactory.getCurrentSession();
		String sql = "SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'mes' AND table_name LIKE \"tb_information_coll_%\" AND SUBSTRING(table_name, 21, 6) BETWEEN "
				+ str1 + " AND " + str2 + " ORDER BY table_name DESC";
		Query q = session.createSQLQuery(sql);
		List<Object> list = q.list();
		String tbName[] = new String[list.size()];
		for (int i = 0; i < list.size(); i++) {
			tbName[i] = (String) list.get(i);
		}
		return tbName;
	}

2、创建数组接收来自步骤1中返回的数组，然后遍历，SQL语句在此遍历中实现拼接。遍历完成后开始调用MySQL的存储过程，至此，动态视图创建完毕，可以查询了！

/**
	 * 执行动态创建视图的存过
	 * 
	 * @param parameter
	 */
	@SuppressWarnings("deprecation")
	public void execProcCreateView(Map<String, Object> parameter) {
		try {
			String sql = "CREATE VIEW view_weldingdata AS SELECT * FROM tb_information_coll";
			// 接收前端返回的参数
			if (parameter.get("str1") != null
					&& !((String) parameter.get("str1")).isEmpty()) {
				if (parameter.get("str2") != null
						&& !((String) parameter.get("str2")).isEmpty()) {
					// System.out.println("=========" + parameter +
					// "==========");
					String str1 = (String) parameter.get("str1");
					String str2 = (String) parameter.get("str2");
					// 根据“-”分隔字符串
					String[] arrStr1 = str1.split("-");
					String[] arrStr2 = str2.split("-");

					// 得到下边SQL需要用到的字符串
					str1 = arrStr1[0] + arrStr1[1];
					str2 = arrStr2[0] + arrStr2[1];

					String str[] = selectTables(str1, str2);
					for (int i = 0; i < str.length; i++) {
						sql = sql + " UNION SELECT * FROM " + str[i];
					}
				} else {
					String arrStr[] = selectTablesAll();
					for (int i = 0; i < arrStr.length; i++) {
						sql = sql + " UNION SELECT * FROM " + arrStr[i];
					}
				}

			} else {
				String arrStr[] = selectTablesAll();
				for (int i = 0; i < arrStr.length; i++) {
					sql = sql + " UNION SELECT * FROM " + arrStr[i];
				}
			}

			// System.out.println(sql);
			session = sessionFactory.getCurrentSession();
			session.beginTransaction();
			CallableStatement st = null;
			st = session.connection().prepareCall("{call P_createview1(?)}");
			st.setString(1, sql);
			st.execute();
			session.getTransaction().commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

补充，这样存过就显得尤为简单，具体如下：

create procedure P_createview1(datastr1 varchar(200))
BEGIN
	-- 定义变量
DROP VIEW IF EXISTS view_weldingdata;
SET @sss=datastr1;
PREPARE stmt FROM @sss;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

好了，大功告成，完美实现动态视图的创建，大大优化了亿万级数据的查询。