级联创表

比眉伴天荒 2021-10-08 05:46 404阅读 0赞

package mes.daoImp;  
  
  
import org.hibernate.Session;  
import org.springframework.stereotype.Repository;  
import mes.daoInterface.machInfoDaoInterface;  
  
  
@Repository  
public class machInfoDaoImp extends baseDaoImp implements machInfoDaoInterface \{  
  
  
@Override  
public void createDBMachProduceStatusTable(String machCode) \{  
String sql = getCreateTableSql(machCode);  
Session session = hibernateTemplate.getSessionFactory().getCurrentSession();  
session.createSQLQuery(sql).executeUpdate();  
\}  
  
  
//创表的sql语句  
private String getCreateTableSql(String machCode) \{  
int beginDay=1;  
String sql2="";  
String sql1="create table If Not Exists DBMachProduceStatusTable\_"+machCode+"(id int auto\_increment,dataTime datetime"  
\+ ",machProduceStatus int,PRIMARY KEY (id,dataTime),INDEX dataTime(dataTime)) PARTITION BY RANGE COLUMNS(dataTime) (";  
for(int beginYear=2017;beginYear<=2022;beginYear++)\{  
for(int beginMonth=1;beginMonth<=12;beginMonth++)\{  
sql2=sql2+" PARTITION p\_"+beginYear+"\_"+beginMonth+"\_"+beginDay+" VALUES LESS THAN ('"+  
\+beginYear+"-"+beginMonth+"-"+beginDay+"'),";  
\}  
\}  
String sql3="PARTITION p\_max VALUES LESS THAN MAXVALUE);";   
String sql=sql1+sql2+sql3;  
return sql;   
\}  
  
  
@Override  
public void deleteDBMachProduceStatusTable(String machCode) \{  
String sql="drop table if exists DBMachProduceStatusTable\_"+machCode;  
Session session = hibernateTemplate.getSessionFactory().getCurrentSession();  
session.createSQLQuery(sql).executeUpdate();  
\}  
  
  
  
  
  
  
  
  
  
  
@Override  
public void createDBMachMotorOperatingTable(String machCode) \{  
String sql = getCreateTableSql1(machCode);  
Session session = hibernateTemplate.getSessionFactory().getCurrentSession();  
session.createSQLQuery(sql).executeUpdate();  
\}  
  
  
//创表的sql语句  
private String getCreateTableSql1(String machCode) \{  
//从2017年1月1日开始分区  
int beginDay=1;  
String sql2="";  
String sql1="create table If Not Exists DBMachMotorOperatingTable\_"+machCode+"(id int auto\_increment,dataTime datetime"  
\+ ",machMotorOperatingStatus int,PRIMARY KEY (id,dataTime),INDEX dataTime(dataTime)) PARTITION BY RANGE COLUMNS(dataTime) (";  
for(int beginYear=2017;beginYear<=2022;beginYear++)\{  
for(int beginMonth=1;beginMonth<=12;beginMonth++)\{  
sql2=sql2+" PARTITION p\_"+beginYear+"\_"+beginMonth+"\_"+beginDay+" VALUES LESS THAN ('"+  
\+beginYear+"-"+beginMonth+"-"+beginDay+"'),";  
\}  
\}  
String sql3="PARTITION p\_max VALUES LESS THAN MAXVALUE);";   
String sql=sql1+sql2+sql3;  
return sql;   
\}  
  
@Override  
public void deleteDBMachMotorOperatingTable(String machCode) \{  
String sql="drop table if exists DBMachMotorOperatingTable\_"+machCode;  
Session session = hibernateTemplate.getSessionFactory().getCurrentSession();  
session.createSQLQuery(sql).executeUpdate();  
\}  
  
  
  
  
@Override  
public void cascadingDeleteDBJobProduceStatusDataAndOrderProduceStatusDataAndDBSpcTable(String machCode) \{  
  
\}  
  
  
  
  
  
\}