package com.cerner.careaware.service.collector; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import org.junit.Test; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport; import com.cerner.careaware.analytics.BaseDbTest4; /** * This test class verifies the number of rows deleted via purge operation. */ public class EventPurgeDaoIT extends BaseDbTest4 { private static final String dbConnection = "jdbc:oracle:thin:@iphddev01:1521:dunv1"; private static final String dbUsername = "caal_junit"; private static final String dbPassword = "caal_junit"; private static final String tableName = "CA_ERR_AL_EVENT"; private static final String qualifierDateField = "LAST_PROCESSED_DTTM"; private static final int batchSize = 10; private static final int bufferHours = 24 * 3; private static final String jdbcDriver = "oracle.jdbc.driver.OracleDriver"; private final static String jobTable = "CA_JOB_STATUS"; private final static String jobField = "LAST_PROCESSED_POINT"; private final static String jobId = "com.cerner.careaware.analytics.events.event.EventProcessingJob"; @Test public void countDeletedRowsFromStagingTest() throws SQLException { Map<String, Integer> map = queryDB().get(0); assertNotNull(map); int rowCount = map.get("rowCount"); System.out.println("Number of rows to be deleted:=" + rowCount); int deleteCount = EventPurgeDao.purge(jdbcDriver, dbConnection, dbUsername, dbPassword, tableName, qualifierDateField, batchSize, bufferHours); System.out.println("Number of rows actually deleted:=" + deleteCount); assertEquals(rowCount, deleteCount); } public List<Map<String, Integer>> queryDB() { NamedParameterJdbcDaoSupport jdbc = new NamedParameterJdbcDaoSupport(); jdbc.setDataSource(cfg.getDaoContext().dataSource()); String jobStatusQuery = "select DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * " + jobField + " AS job_date from " + jobTable + " where JOB_ID = '" + jobId + "'"; System.out.println(jobStatusQuery); RowMapper<Map<String, Object>> jobDateRowMapper = new RowMapper<Map<String, Object>>() { @Override public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException { Map<String, Object> jobDateMap = new HashMap<String, Object>(); jobDateMap.put("job_date", rs.getDate("job_date")); return jobDateMap; } }; Date jobDate = (Date) ((Map<String, Object>) jdbc.getNamedParameterJdbcTemplate().query(jobStatusQuery, jobDateRowMapper).get(0)).get("job_date"); System.out.println(jobDate); if (jobDate != null) { String countQuery = "select count(*) as rowCount FROM " + tableName + " where To_Date(To_Char(" + qualifierDateField + ",'DDMMYYYY'),'DD-MM-YY')" + "<= To_Date('" + jobDate + "','YYYY-MM-DD') - interval '" + bufferHours + "' hour"; RowMapper<Map<String, Integer>> countRowMapper = new RowMapper<Map<String, Integer>>() { @Override public Map<String, Integer> mapRow(ResultSet rs, int rowNum) throws SQLException { Map<String, Integer> countMap = new HashMap<String, Integer>(); countMap.put("rowCount", rs.getInt("rowCount")); return countMap; } }; return jdbc.getNamedParameterJdbcTemplate().query(countQuery, countRowMapper); } return null; } }
setDate