List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet getDate
Date getDate(String columnLabel) throws InvalidResultSetAccessException;
From source file:dk.nsi.sdm4.dosering.parser.DoseringParserIntegrationTest.java
@Test public void importTheVersionFileCorrectly() throws Exception { runImporter();/* w ww . j a va2 s . com*/ SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from DosageVersion"); rs.next(); assertThat(rs.getLong("releaseNumber"), equalTo(125L)); assertThat(rs.getDate("releaseDate"), equalTo(date("2011-02-15"))); assertThat(rs.getDate("lmsDate"), equalTo(date("2011-02-02"))); assertThat(rs.getDate("daDate"), equalTo(date("2011-01-24"))); // expect only one row assertFalse(rs.next()); }
From source file:com.zousu.mongopresser.Presser.java
public void press() { //get a list of all the tables and columns logger.info("Starting MySQL to Mongo Conversion..."); logger.info("Preparing Tables..."); mySQLHandler.initialiseDatabase();/*from w ww .ja va2 s .c o m*/ List<Table> tables = mySQLHandler.getTableList(); for (int i = 0; i < tables.size(); i++) { Table table = tables.get(i); List<Column> columns = table.getColumns(); List<DBObject> dboList = new ArrayList<DBObject>(); SqlRowSet rs = mySQLHandler.selectAllFromTable(table.getTableName()); logger.info("Creating objects for " + table.getTableName() + "..."); while (rs.next()) { BasicDBObject dbo = new BasicDBObject(); for (int j = 0; j < columns.size(); j++) { Column col = columns.get(j); String colName = col.getColumnName(); switch (col.getType()) { case Types.INTEGER: case Types.BIGINT: dbo.append(colName, rs.getInt(colName)); break; case Types.DOUBLE: dbo.append(colName, rs.getDouble(colName)); break; case Types.DATE: dbo.append(colName, rs.getDate(colName)); break; default: dbo.append(colName, rs.getString(colName)); break; } } dboList.add(dbo); } //now insert it logger.info("Inserting " + dboList.size() + " mongo rows into " + table.getTableName() + "..."); table.setNumOfRows(dboList.size()); try { mongoHandler.createCollection(table.getTableName(), true); mongoHandler.batchInsert(dboList, table.getTableName()); assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == dboList.size()); logger.info(table.getTableName() + " DONE!"); } catch (CollectionExistException e) { e.printStackTrace(); } } logger.info(tables.size() + " collections added!"); //now check go get it from mongo and check if the data there is correct logger.info("Checking mongo consistency..."); for (int i = 0; i < tables.size(); i++) { Table table = tables.get(i); assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == table.getNumOfRows()); logger.info(table.getTableName() + " consistent!"); } logger.info("MySQL to Mongo Conversion Completed!!!!"); }
From source file:org.kuali.kfs.vnd.batch.dataaccess.DebarredVendorDaoJdbc.java
@Override public List<DebarredVendorMatch> match() { String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'"; String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl"; String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl"; String where = " WHERE " + active; String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" + ", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT"; String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromName = " FROM pur_vndr_dtl_t dtl"; String name = filter("dtl.VNDR_NM", "., "); String eplsName = filter("excl.VNDR_EXCL_NM", "., "); String onName = " ON " + compare(name, eplsName, false); // use = to compare String sqlName = selectName + fromName + joinExcl + onName + where; String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromAlias = " FROM pur_vndr_alias_t als"; String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; String alias = filter("als.VNDR_ALIAS_NM", "., "); String eplsAlias = filter("excl.VNDR_EXCL_NM", "., "); String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where; String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID"; String fromAddr = " FROM pur_vndr_addr_t addr"; String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; ////from ww w .j a va 2s . c o m String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# "); String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# "); String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare // String city = filter("addr.VNDR_CTY_NM", "., "); String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., "); String cmpCity = compare(city, eplsCity, false); // use = to compare // String state = "upper(addr.VNDR_ST_CD)"; String eplsState = "upper(excl.VNDR_EXCL_ST_CD)"; String cmpState = compare(state, eplsState, false); // use = to compare // String zip = filter("addr.VNDR_ZIP_CD", "-"); String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-"); String cmpZip = compare(zip, eplsZip, false); // use = to compare String fullZip = "length(addr.VNDR_ZIP_CD) > 5"; // String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState; String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where; String max = ", MAX(VNDR_ADDR_GNRTD_ID)"; String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" + ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT"; String select = "SELECT " + selectFields + max; String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr; String from = " FROM (" + subqr + ")"; String group = " GROUP BY " + selectFields; String sql = select + from + group; List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>(); try { SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql); DebarredVendorMatch match; while (rs.next()) { match = new DebarredVendorMatch(); match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1))); match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2))); match.setLoadDate(rs.getDate(4)); match.setName(rs.getString(5)); match.setAddress1(rs.getString(6)); match.setAddress2(rs.getString(7)); match.setCity(rs.getString(8)); match.setState(rs.getString(9)); match.setProvince(rs.getString(10)); match.setZip(rs.getString(11)); match.setAliases(rs.getString(12)); match.setDescription(rs.getString(13)); match.setAddressGeneratedId(rs.getLong(14)); // didn't find a matched address, search for best one if (match.getAddressGeneratedId() == 0) { match.setAddressGeneratedId(getMatchAddressId(match)); } DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match); if (oldMatch == null) { // store the match only if an exact old match doesn't exist match.setConfirmStatusCode("U"); // status - Unprocessed matches.add(match); } } } catch (Exception e) { // if exception occurs, return empty results throw new RuntimeException(e); } return matches; }