import java.sql.Date;
import java.util.Calendar;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.cetcnav.suo.ADSB.util.DateUtil;
@Repository("createSpeedTable")
public class CreateSpeedTableDao {
private static final Logger logger = LoggerFactory.getLogger(CreateSpeedTableDao.class);
@Autowired
private JdbcTemplate jdbcTemplate;
public void createSpeedTable(Date dateParam) {
logger.info("开始转存位置数据(createspeedTable)>>...");
String sql = "SELECT distinct date_format(time,'%Y-%m-%d') as date FROM speed";
List<Date> dates = jdbcTemplate.queryForList(sql,Date.class);
Date today = new Date(DateUtil.getCurrentYYR().getTime());
if(dates == null || dates.size() <= 0){
String time = String.valueOf(dateParam); //2016-04-01
String table = "speed_"+time.replace("-", ""); //speed_monitor_20160401
String cSql = "create table IF not exists "+table+" (like speed)"; // INCLUDING DEFAULTS
jdbcTemplate.execute(cSql);
return;
}
for(Date date : dates){
logger.info(date.toString()+" : "+today.toString());
Date dataIndex = new Date(DateUtil.convertDate2FirstSecond(date).getTime());
if(dataIndex.equals(today) || dataIndex.after(today))
continue;
// logger.info("需要转存的日期"+date.toString());
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 = "speed_"+time.replace("-", ""); //speed_monitor_20160401
String cSql = "create table IF not exists "+table+" (like speed )";
jdbcTemplate.execute(cSql);
String query = "select * from speed where time >= '"+String.valueOf(date)+" 00:00:00.0' and time < '"+newDate+"'";
String inSql = "insert into "+table+" "+query+";";
jdbcTemplate.execute(inSql);
String deleteSql = "delete from speed where id in (select id from "+table+")";
jdbcTemplate.execute(deleteSql);
}
}
}
MySQL数据库 数据按照时间每天一张数据表
最新推荐文章于 2023-03-15 16:41:41 发布