JPA hibernate spring repository pgsql java 工程（三）：数据表分区与自动创建：策略

参考文献：

[1] JPA的查询语言—使用原生SQL[EB/OL].[2016-03-30].http://blog.csdn.net/chenallen1025/article/details/9169543

[2]PostgreSQL学习手册(表的继承和分区)[EB/OL].[2016-03-30].http://www.cnblogs.com/stephen-liu74/archive/2012/04/27/2291814.html

[3]PostgreSQL 快速创建空表TIPS[EB/OL].[2016-03-30].http://blog.chinaunix.net/uid-259788-id-4679667.html

[4]Spring Boot JDBC 连接数据库 [EB/OL].[2016-03-31].http://blog.csdn.net/catoop/article/details/50507516
[5]Spring JdbcTemplate方法详解 [EB/OL].[2016-03-31].http://www.cnblogs.com/wanggd/p/3140506.html

[6]Spring中jdbcTemplate的使用queryForObject，query [EB/OL].[2016-03-31].http://blog.163.com/guomaolin_gavin/blog/static/19961830720126611158848/

[7]Spring中JdbcTemplate中使用RowMapper[EB/OL].[2016-03-31].http://cxl2012.iteye.com/blog/1969394
[8]Spring JdbcTemplate方法详解[EB/OL].[2016-03-31].http://blog.csdn.net/dyllove98/article/details/7772463

[9]使用spring的jdbcTemplate-----用JDBC模板查询数据库[EB/OL].[2016-03-31].http://501565246-qq-com.iteye.com/blog/759879

==============================================================================================================

由于spring boot mvc 框架下表以bean的形式建立映射关系，对于一些数据量特别大的表格，可能要按天，按月来建一张新表进行转存，需要定时建表和存数据。

以下通过 JdbcTemplate 来进行对postgresql数据库的直接操作。

jpa中直接封装了JdbcTemplate的方法

location_monitor 是一个存定位信息的表格

相关sql语句

insert into location_monitor_1 select * from location_monitor;
delete from location_monitor_1 where lm_id in (select lm_id from location_monitor);
select count(*) from location_monitor_20160401;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Vector;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;


public class LocationMonitorRepositoryImpl implements LocationMonitorRepositoryCustom{
@PersistenceContext
private EntityManager em;
@Autowired
private JdbcTemplate jdbcTemplate; //自动获取 <span style="font-family: Arial, Helvetica, sans-serif;">JdbcTemplate </span>
<span style="white-space:pre"></span>@Autowired
<span style="white-space:pre"></span>private PlatformTransactionManager platformTransactionManager;
public void createTable(Date date) {
//DefaultTransactionDefinition def = new DefaultTransactionDefinition();   //注释的部分是为了事务方法操作的
//TransactionStatus status = platformTransactionManager.getTransaction(def);
//try {
String time = String.valueOf(date);  //2016-04-01 通过表名+日期的方式建表
Calendar c = Calendar.getInstance();
c.setTime(date); 
int day = c.get(Calendar.DATE);
c.set(Calendar.DATE, day+1);
Date newDate = new java.sql.Date(c.getTime().getTime());
String table = "location_monitor_"+time.replace("-", "");  //location_monitor_20160401
String cSql = "create table IF not exists "+table+"  (like location_monitor INCLUDING DEFAULTS)";//创建表格
jdbcTemplate.execute(cSql);
String query = "select * from location_monitor where lm_time_at_app >= '"+String.valueOf(date)+" 00:00:00.0' and lm_time_at_app < '"+newDate+"'"; //查找某一天的旧表的数据
String inSql = "insert into "+table+" "+query+";"; //把旧表的数据插入到新表中
jdbcTemplate.execute(inSql);
String deleteSql = "delete from location_monitor where lm_id in (select lm_id from "+table+")"; //删除旧表的数据
jdbcTemplate.execute(deleteSql);
//} catch (DataAccessException ex) {
//platformTransactionManager.rollback(status); // 也可以執行status.setRollbackOnly();
//        throw ex;
//}
//platformTransactionManager.commit(status);
}

@SuppressWarnings("deprecation")
@Override
public List<LocationMonitor> search(Timestamp begin,Timestamp end){  //查找一天内 某个时间段的所有数据
Calendar now = Calendar.getInstance();
Calendar ca1 = Calendar.getInstance();
Calendar ca2 = Calendar.getInstance();

now.setTimeInMillis(System.currentTimeMillis());
ca1.setTimeInMillis(begin.getTime());
ca2.setTimeInMillis(end.getTime());

Date dnow = new Date(now.getTimeInMillis());
Date d1 = new Date(ca1.getTimeInMillis());
Date d2 = new Date(ca2.getTimeInMillis());

String tablename="";
if(String.valueOf(d1).equals(String.valueOf(d2))){
if(String.valueOf(d1).equals(String.valueOf(dnow))){ //今天的数据
tablename = "location_monitor";
}
else {
tablename = "location_monitor_"+String.valueOf(d1).replace("-", "");
}
if(isTableExist(tablename) == false) return null; //判断表是否存在
String sql = "select * from "+tablename+" ";

String sqlS = sql + " order by lm_id asc";
List<LocationMonitor> lmone = (List<LocationMonitor>)jdbcTemplate.query(sqlS,new LocationMonitorMapper()); //参考文献[7]
return lmone <span style="font-family: Arial, Helvetica, sans-serif;">;</span>
}
return null;
}
/*
 * false表示不存在
 * true表示表存在
 */
public boolean isTableExist(String tablename){
String sql = "select count(*) from pg_tables where tablename= '"+tablename+"'";  
int num = jdbcTemplate.queryForObject(sql, Integer.class);
return num > 0;
}
}

class LocationMonitorMapper implements RowMapper{
@Override 
public Object mapRow(ResultSet rs ,int rowNum)throws SQLException{
LocationMonitor lm = new LocationMonitor();
lm.setId(rs.getLong("lm_id"));
lm.setLongitude(rs.getDouble("lm_longitude"));
lm.setLatitude(rs.getDouble("lm_latitude"));
lm.setCreateTime(rs.getTimestamp("lm_creat_time"));
lm.setUpdateTime(rs.getTimestamp("lm_update_time"));
return lm;
}
}

更多相关文章

随机推荐