SpringData JPA调用Mysql 存储过程 + 视图解决数据库分表查询问题

近期，由于大量数据日志的原因，需要对数据库进行分表，每天一张表。以下为实现过程，废话不多说，上code

1、Mysql存储过程 

delimiter //
CREATE PROCEDURE p_createview() 
BEGIN
  -- 定义变量
DECLARE done INT DEFAULT FALSE; 

DECLARE idx INT DEFAULT 1;

DECLARE tbName VARCHAR (32); 
  -- 定义游标
DECLARE allTable CURSOR FOR (SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'xxxxx' AND table_name -- 此处xxxxx代表您的数据库名，其他地方为表名 
	LIKE 'xxxxx%');
  -- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 
DROP VIEW IF EXISTS xxxxx_view; 
  -- 打开游标
OPEN allTable; 
  -- 当s不等于1时，也就是未遍历完时，会一直循环
myLoop: LOOP -- 开始循环体，myLoop为自定义循环名，结束循环时用到
 -- 将游标中的值赋给变量
 FETCH allTable INTO tbName;
 IF done THEN -- 当done 为TRUE 表示遍历完成，退出循环
	LEAVE myLoop; -- 结束循环
 END IF;
 -- 执行业务逻辑
 IF idx = 1 THEN
	SET @str1 = CONCAT('CREATE VIEW xxxxx_view as select * from ',tbName); 
 ELSE
	SET @str1 = CONCAT(@str1,' union all select * from ',tbName); 
 END IF;
 SET idx = idx + 1;
END LOOP myLoop; -- 结束循环
CLOSE allTable; -- 关闭游标

PREPARE stmt FROM @str1; -- 声明变量取出@str1的值
EXECUTE stmt; -- 执行变量值
DEALLOCATE PREPARE stmt;  -- 销毁变量，解除分配

END // -- 结束存储过程
delimiter ;

2、调用存储过程

 使用原生调用 em 代表的是EntityManager，要注入到当前的业务类中。

@PersistenceContext
private EntityManager em;

Query nativeQuery = em.createNativeQuery("{call p_createview()}");
nativeQuery.executeUpdate();

PS : 要在调用存储过程的方法上加上

package javax.transaction

事务注解@Transactional，否则 会有以下异常产生

javax.persistence.transactionrequiredexception executing an update/delete query

3、创建与视图对应的Entity

如下所示，最好在表名的末尾加上view来标明该表是一张视图表

@Entity
@Table(name = "order_detail_view")
public class OrderEntity{

    @Id
    private String id;
    /**
     * 订单id
     */
    @Column(name = "order_id" )
    private String orderId;

    /**
     * 商品id
     */
    @Column(name = "product_id" )
    private String productId;

    /**
     * 商品名字
     */
    @Column(name = "product_name" )
    private String productName;

    /**
     * 商品价格
     */
    @Column(name = "product_price" )
    private Double productPrice;

    /**
     * 商品数量
     */
    @Column(name = "product_quantity" )
    private Integer productQuantity;

    /**
     * 商品小图
     */
    @Column(name = "product_icon" )
    private String productIcon;

}

OK，可以测试一下看看。

附：下面是建表的存储过程，一并附上记录一下。

delimiter //
CREATE definer = root@`%` PROCEDURE p_createtable(IN tabname VARCHAR(32))
BEGIN
 -- DROP TABLE IF EXISTS tabname;
 SET @sqlstr = CONCAT('CREATE TABLE IF NOT EXISTS ',tabname,
' (
    id        CHAR(32)             NOT NULL COMMENT \'主键ID\',
    name      VARCHAR(36)          NULL COMMENT \'名称\',
    age       TINYINT(3)           NULL COMMENT \'年龄\',
    INDEX Index_name (name),
	PRIMARY KEY (id)
) COMMENT \'用户表\';');
PREPARE sqlstr FROM @sqlstr;
EXECUTE sqlstr;
DEALLOCATE PREPARE sqlstr;
END //
delimiter ;
	

PS：上面这里替换成自己需要的字段。像这种有要传参数的需要，在调用的时候

Query nativeQuery = em.createNativeQuery("{call p_createtable(?)}");
nativeQuery.setParameter(1,tabName);
nativeQuery.executeUpdate();

PS：其他地方需要注意的与上面的视图创建的是一样的，比如要记得加上事务管理，否则会抛异常出来