List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
protected void parseField(Object o, MetaField f, ResultSet rs, int j) throws SQLException { switch (f.getType()) { case MetaField.BOOLEAN: { boolean bv = rs.getBoolean(j); if (rs.wasNull()) { f.setBoolean(o, null);//w w w .j a va 2 s. c o m } else { f.setBoolean(o, new Boolean(bv)); } } break; case MetaField.BYTE: { byte bv = rs.getByte(j); if (rs.wasNull()) { f.setByte(o, null); } else { f.setByte(o, new Byte(bv)); } } break; case MetaField.SHORT: { short sv = rs.getShort(j); if (rs.wasNull()) { f.setShort(o, null); } else { f.setShort(o, new Short(sv)); } } break; case MetaField.INT: { int iv = rs.getInt(j); if (rs.wasNull()) { f.setInt(o, null); } else { f.setInt(o, new Integer(iv)); } } break; case MetaField.DATE: { Timestamp tv = rs.getTimestamp(j); if (rs.wasNull()) { f.setDate(o, null); } else { f.setDate(o, new java.util.Date(tv.getTime())); } } break; case MetaField.LONG: { long lv = rs.getLong(j); if (rs.wasNull()) { f.setLong(o, null); } else { f.setLong(o, new Long(lv)); } } break; case MetaField.FLOAT: { float fv = rs.getFloat(j); if (rs.wasNull()) { f.setFloat(o, null); } else { f.setFloat(o, new Float(fv)); } } break; case MetaField.DOUBLE: { double dv = rs.getDouble(j); if (rs.wasNull()) { f.setDouble(o, null); } else { f.setDouble(o, new Double(dv)); } } break; case MetaField.STRING: f.setString(o, rs.getString(j)); break; case MetaField.OBJECT: f.setObject(o, rs.getObject(j)); break; } }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@SuppressWarnings({ "rawtypes", "unchecked" })
private PostgresDocument createEmptyDocument(final Connection conn, final String table, final String name,
final String db) throws BackendException {
Statement st = null;//from w w w .ja va 2 s . c o m
Statement st2 = null;
ResultSet rs = null;
ResultSet rs2 = null;
PostgresDocument doc = new PostgresDocument();
doc.setTable(this.getSchemaName(table, db) + "." + this.getPlainTableName(table));
doc.setDatabase(db);
doc.setName(name);
try {
String schema = this.getSchemaName(table, db);
String plainTable = this.getPlainTableName(table);
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
StringBuilder sb = new StringBuilder("");
sb.append("select c.column_name, c.column_default, c.data_type, ccu.table_name, ccu.column_name");
sb.append(" from information_schema.columns c");
sb.append(" left join information_schema.key_column_usage kcu");
sb.append(" on kcu.table_schema = c.table_schema and kcu.table_name = c.table_name");
sb.append(" and kcu.column_name = c.column_name");
sb.append(" left join information_schema.table_constraints tc");
sb.append(" on tc.constraint_type='FOREIGN KEY' and tc.table_schema = c.table_schema");
sb.append(" and tc.table_name = c.table_name and tc.constraint_name = kcu.constraint_name");
sb.append(" left join information_schema.constraint_column_usage ccu");
sb.append(
" on ccu.constraint_schema = tc.constraint_schema and ccu.constraint_name = tc.constraint_name");
sb.append(" where c.table_schema='%s' and c.table_name='%s'");
sb.append(" order by c.ordinal_position");
String queryString = String.format(sb.toString().replaceAll("[ ]+", " "), schema, plainTable);
this.logString(queryString, "?");
rs = st.executeQuery(queryString);
if (this.getNumRows(rs) == 0) {
throw new BackendException(String.format("Table %s.%s has no columns which is not supported.",
this.getSchemaName(table, db), this.getPlainTableName(table)));
}
String nameField = this.getNameField(conn, table, db);
while (rs.next()) {
String ctype = rs.getString(3);
String cname = rs.getString(1);
PostgresField field = null;
if ("character varying".equals(ctype)) {
field = new PostgresField<String>();
field.setType(FieldType.string);
field.setValue(rs.getString(2));
} else if ("text".equals(ctype)) {
field = new PostgresField<String>();
field.setType(FieldType.text);
field.setValue(rs.getString(2));
} else if ("integer".equals(ctype) || "bigint".equals(ctype) || "smallint".equals(ctype)
|| "real".equals(ctype)) {
field = new PostgresField<Integer>();
field.setType(FieldType.dec);
field.setValue(rs.getInt(2));
} else if ("numeric".equals(ctype)) {
field = new PostgresField<Double>();
field.setType(FieldType.num);
field.setValue(rs.getDouble(2));
} else if ("date".equals(ctype)) {
field = new PostgresField<Date>();
field.setType(FieldType.date);
field.setValue(rs.getDate(2));
}
if (field != null) {
field.setName(cname);
field.setUsage(FieldUsage.normal);
if (nameField.equals(cname)) {
field.setValue(name);
} else if ("version".equals(cname)) {
field.setUsage(FieldUsage.hidden);
}
String foreignTable = rs.getString(4);
String foreignColumn = rs.getString(5);
if (!StringUtils.isEmpty(foreignTable) && !StringUtils.isEmpty(foreignColumn)) {
st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
field.setUsage(FieldUsage.fixed);
StringBuilder sb2 = new StringBuilder();
sb2.append("select distinct \"%s\" from \"%s\" order by \"%s\"");
String queryString2 = String.format(sb2.toString().replaceAll("[ ]+", " "), foreignColumn,
foreignTable, foreignColumn);
this.logString(queryString2, "?");
rs2 = st2.executeQuery(queryString2);
while (rs2.next()) {
field.getAllowedValues().add(rs2.getObject(1));
}
}
doc.addField(cname, field);
}
}
} catch (SQLException e) {
throw new BackendException(e);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(rs2);
DbUtils.closeQuietly(st);
DbUtils.closeQuietly(st2);
}
return doc;
}
From source file:org.ramadda.geodata.cdmdata.PointDatabaseTypeHandler.java
/** * _more_//from w ww . ja v a 2 s .co m * * @param request _more_ * @param entry _more_ * * @return _more_ * * @throws Exception _more_ */ private Result doSearch(Request request, Entry entry) throws Exception { String format = request.getString(ARG_POINT_FORMAT, FORMAT_HTML); String baseName = IOUtil.stripExtension(entry.getName()); boolean redirect = request.get(ARG_POINT_REDIRECT, false); request.remove(ARG_POINT_REDIRECT); request.remove(ARG_POINT_SEARCH); if (format.equals(FORMAT_TIMESERIES) || format.equals(FORMAT_TIMESERIES_CHART)) { StringBuffer sb = new StringBuffer(); getHtmlHeader(request, sb, entry, null); if (format.equals(FORMAT_TIMESERIES)) { request.put(ARG_POINT_FORMAT, FORMAT_TIMESERIES_IMAGE); String redirectUrl = request.getRequestPath() + "/" + baseName + ".png" + "?" + request.getUrlArgs(null, getSet(OP_LT)); sb.append(HtmlUtils.img(redirectUrl, "Image is being processed...")); } else { /* // for amcharts flash if (chartTemplate == null) { chartTemplate = getRepository().getResource( "/org/ramadda/repository/resources/chart/amline.html"); chartTemplate = chartTemplate.replace("${urlroot}", getRepository().getUrlBase()); chartTemplate = chartTemplate.replace("${urlroot}", getRepository().getUrlBase()); chartTemplate = chartTemplate.replace("${urlroot}", getRepository().getUrlBase()); } */ // for dycharts javascript chartTemplate = getRepository() .getResource("/org/ramadda/repository/resources/chart/dycharts.html"); chartTemplate = chartTemplate.replace("${urlroot}", getRepository().getUrlBase()); String title = request.getString(ARG_POINT_TIMESERIES_TITLE, entry.getName()); if (title.equals("")) { title = entry.getName(); } chartTemplate = chartTemplate.replace("${title}", title); chartTemplate = chartTemplate.replace("${options}", ""); String html = chartTemplate; request.put(ARG_POINT_FORMAT, FORMAT_TIMESERIES_DATA); String dataUrl = request.getRequestPath() + "/" + baseName + ".xml" + "?" + request.getUrlArgs(null, getSet(OP_LT)); html = html.replace("${dataurl}", dataUrl); sb.append(html); } return new Result("Search Results", sb); } if (redirect) { String urlSuffix = ".html"; if (format.equals(FORMAT_CSV) || format.equals(FORMAT_CSVIDV) || format.equals(FORMAT_CSVHEADER)) { urlSuffix = ".csv"; } else if (format.equals(FORMAT_XLS)) { urlSuffix = ".xls"; } else if (format.equals(FORMAT_KML)) { urlSuffix = ".kml"; } else if (format.equals(FORMAT_NETCDF)) { urlSuffix = ".nc"; } String redirectUrl = request.getRequestPath() + "/" + HtmlUtils.urlEncode(baseName) + urlSuffix + "?" + request.getUrlArgs(null, getSet(OP_LT)); return new Result(redirectUrl); } if (format.equals(FORMAT_SCATTERPLOT)) { } String tableName = getTableName(entry); Date[] dateRange = request.getDateRange(ARG_POINT_FROMDATE, ARG_POINT_TODATE, null); List<Clause> clauses = new ArrayList<Clause>(); if (dateRange[0] != null) { clauses.add(Clause.ge(COL_DATE, dateRange[0])); } if (dateRange[1] != null) { clauses.add(Clause.le(COL_DATE, dateRange[1])); } if (request.defined(ARG_POINT_BBOX + "_north")) { clauses.add(Clause.le(COL_LATITUDE, request.get(ARG_POINT_BBOX + "_north", 90.0))); } if (request.defined(ARG_POINT_BBOX + "_south")) { clauses.add(Clause.ge(COL_LATITUDE, request.get(ARG_POINT_BBOX + "_south", 90.0))); } if (request.defined(ARG_POINT_BBOX + "_west")) { clauses.add(Clause.ge(COL_LONGITUDE, request.get(ARG_POINT_BBOX + "_west", -180.0))); } if (request.defined(ARG_POINT_BBOX + "_east")) { clauses.add(Clause.le(COL_LONGITUDE, request.get(ARG_POINT_BBOX + "_east", 180.0))); } if (request.defined(ARG_POINT_HOUR)) { clauses.add(Clause.eq(COL_HOUR, request.getString(ARG_POINT_HOUR))); } if (request.defined(ARG_POINT_MONTH)) { clauses.add(Clause.eq(COL_MONTH, request.getString(ARG_POINT_MONTH))); } List<PointDataMetadata> metadata = getMetadata(getTableName(entry)); List<PointDataMetadata> tmp = new ArrayList<PointDataMetadata>(); if (request.get(ARG_POINT_PARAM_ALL, false)) { tmp = metadata; } else { List<String> whatList = (List<String>) request.get(ARG_POINT_PARAM, new ArrayList()); HashSet seen = new HashSet(); for (String col : whatList) { seen.add(col); } for (PointDataMetadata pdm : metadata) { if (seen.contains("" + pdm.getColumnNumber())) { tmp.add(pdm); } } } //Strip out the month/hour List<PointDataMetadata> columnsToUse = new ArrayList<PointDataMetadata>(); for (PointDataMetadata pdm : tmp) { //Skip the db month and hour if (pdm.getColumnName().equals(COL_MONTH) || pdm.getColumnName().equals(COL_HOUR)) { continue; } columnsToUse.add(pdm); } for (PointDataMetadata pdm : metadata) { if (pdm.isBasic() && !pdm.getColumnName().equals(COL_ALTITUDE)) { continue; } String suffix = "" + pdm.getColumnNumber(); if (!request.defined(ARG_POINT_FIELD_VALUE + suffix)) { continue; } if (pdm.isString()) { String value = request.getString(ARG_POINT_FIELD_VALUE + suffix, ""); if (request.get(ARG_POINT_FIELD_EXACT + suffix, false)) { clauses.add(Clause.eq(pdm.getColumnName(), value)); } else { clauses.add(Clause.like(pdm.getColumnName(), "%" + value + "%")); } } else { String op = request.getString(ARG_POINT_FIELD_OP + suffix, OP_LT); double value = request.get(ARG_POINT_FIELD_VALUE + suffix, 0.0); if (op.equals(OP_LT)) { clauses.add(Clause.le(pdm.getColumnName(), value)); } else if (op.equals(OP_GT)) { clauses.add(Clause.ge(pdm.getColumnName(), value)); } else { clauses.add(Clause.eq(pdm.getColumnName(), value)); } } } StringBuffer cols = null; List<PointDataMetadata> queryColumns = new ArrayList<PointDataMetadata>(); //Always add the basic columns for (PointDataMetadata pdm : metadata) { if (pdm.isBasic()) { queryColumns.add(pdm); } } for (PointDataMetadata pdm : columnsToUse) { if (!pdm.isBasic()) { queryColumns.add(pdm); } } for (PointDataMetadata pdm : queryColumns) { if (cols == null) { cols = new StringBuffer(); } else { cols.append(","); } cols.append(pdm.getColumnName()); } if (cols == null) { cols = new StringBuffer(); } String sortByCol = COL_DATE; String orderDir = (request.get(ARG_POINT_ASCENDING, false) ? " ASC " : " DESC "); String sortByArg = request.getString(ARG_POINT_SORTBY, ""); for (PointDataMetadata pdm : metadata) { if (pdm.getColumnName().equals(sortByArg)) { sortByCol = sortByArg; break; } } int max = request.get(ARG_MAX, 1000); int stride = request.get(ARG_POINT_STRIDE, 1); if (stride > 1) { max = max * stride; } Statement stmt = getDatabaseManager().select( cols.toString(), Misc.newList(tableName), Clause.and(Clause.toArray(clauses)), " ORDER BY " + sortByCol + orderDir + getDatabaseManager().getLimitString(request.get(ARG_SKIP, 0), max), max); SqlUtil.Iterator iter = getDatabaseManager().getIterator(stmt); ResultSet results; int cnt = 0; List<PointData> pointDataList = new ArrayList<PointData>(); int skipHowMany = 0; while ((results = iter.getNext()) != null) { if (skipHowMany > 0) { skipHowMany--; continue; } if (stride > 1) { skipHowMany = stride - 1; } int col = 1; PointData pointData = new PointData(results.getInt(col++), getDatabaseManager().getDate(results, col++), checkReadValue(results.getDouble(col++)), checkReadValue(results.getDouble(col++)), checkReadValue(results.getDouble(col++)), 0, 0); List values = new ArrayList(); //Add in the selected basic values for (PointDataMetadata pdm : columnsToUse) { if (pdm.isBasic()) { values.add(pointData.getValue(pdm.getColumnName())); } } while (col <= queryColumns.size()) { PointDataMetadata pdm = queryColumns.get(col - 1); if (pdm.isDate()) { continue; } if (pdm.isString()) { pointData.setValue(pdm, results.getString(col).trim()); } else { double d = checkReadValue(results.getDouble(col)); pointData.setValue(pdm, new Double(d)); } col++; } pointDataList.add(pointData); } if (format.equals(FORMAT_HTML) || format.equals(FORMAT_TIMELINE)) { return makeSearchResultsHtml(request, entry, columnsToUse, pointDataList, format.equals(FORMAT_TIMELINE)); } if (format.equals(FORMAT_KML)) { return makeSearchResultsKml(request, entry, columnsToUse, pointDataList, format.equals(FORMAT_TIMELINE)); } else if (format.equals(FORMAT_CSV) || format.equals(FORMAT_CSVIDV) || format.equals(FORMAT_CSVHEADER) || format.equals(FORMAT_XLS)) { ensureMinimalParameters(columnsToUse, metadata); return makeSearchResultsCsv(request, entry, columnsToUse, pointDataList, format); } else if (format.equals(FORMAT_CHART)) { return makeSearchResultsChart(request, entry, columnsToUse, pointDataList); } else if (format.equals(FORMAT_TIMESERIES_IMAGE)) { return makeSearchResultsTimeSeries(request, entry, columnsToUse, pointDataList); } else if (format.equals(FORMAT_TIMESERIES_DATA)) { return makeSearchResultsTimeSeriesData(request, entry, columnsToUse, pointDataList); } else if (format.equals(FORMAT_SCATTERPLOT_IMAGE)) { return makeSearchResultsScatterPlot(request, entry, columnsToUse, pointDataList); } else if (format.equals(FORMAT_MAP)) { return makeSearchResultsMap(request, entry, columnsToUse, pointDataList); } else { ensureMinimalParameters(columnsToUse, metadata); return makeSearchResultsNetcdf(request, entry, columnsToUse, pointDataList); } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * //from w w w.j a v a2 s .c o m */ public void process() throws SQLException { buildTags = new BuildTags(); buildTags.setDbConn(dbConn); buildTags.setDbConn2(dbConn); buildTags.initialPrepareStatements(); BasicSQLUtils.setDBConnection(dbConn); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); geoStmt1 = dbConn.prepareStatement( "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?"); geoStmt2 = dbConn .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?"); agentStmt = dbConn .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?"); tagStmt = dbConn.prepareStatement( "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?"); BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'"); boolean doTags = true; if (doTags) { int divId = 2; int dspId = 3; int colId = 32768; String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, " + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, " + "dir, dist, gender, " + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); String rlStr = String.format( "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)", common); String agStr = String .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common); String adStr = String.format( "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)", common); String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?"; String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?"; PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); PreparedStatement rlStmt = dbConn.prepareStatement(rlStr); PreparedStatement agStmt = dbConn.prepareStatement(agStr); PreparedStatement adStmt = dbConn.prepareStatement(adStr); PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr); PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); String city = rs.getString(2); String state = rs.getString(3); String zip = rs.getString(4); String country = rs.getString(5); Date date = rs.getDate(6); double lat = rs.getDouble(7); boolean isLatNull = rs.wasNull(); double lon = rs.getDouble(8); boolean isLonNull = rs.wasNull(); String dir = rs.getString(9); String dist = rs.getString(10); String gender = rs.getString(11); String rep_first = rs.getString(12); String rep_last = rs.getString(13); String rep_city = rs.getString(14); String rep_state = rs.getString(15); String rep_country = rs.getString(16); String rep_zip = rs.getString(17); String t_first = rs.getString(18); //String t_middle = rs.getString(19); String t_last = rs.getString(20); String t_city = rs.getString(21); String t_state = rs.getString(22); String t_country = rs.getString(23); String t_zip = rs.getString(24); //String t_org = rs.getString(25); double t_lat = rs.getDouble(26); boolean isTLatNull = rs.wasNull(); double t_lon = rs.getDouble(27); boolean isTLonNull = rs.wasNull(); //String oldState = state; city = condense(rep_city, t_city, city); state = condense(rep_state, state, t_state); country = condense(rep_country, country, t_country); zip = condense(rep_zip, zip, t_zip); rep_first = condense(rep_first, t_first); rep_last = condense(rep_last, t_last); /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || (t_state != null && t_state.equals("IA")) || (oldState != null && oldState.equals("IA"))); if (debug && (state == null || !state.equals("IA"))) { System.out.println("ouch"); }*/ if (rep_first != null && rep_first.length() > 50) { rep_first = rep_first.substring(0, 50); } lat = isLatNull && !isTLatNull ? t_lat : lat; lon = isLonNull && !isTLonNull ? t_lon : lon; try { // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID Integer geoId = buildTags.getGeography(country, state, null); // Latitude varies between -90 and 90, and Longitude between -180 and 180. if (lat < -90.0 || lat > 90.0) { lcStmt.setObject(1, null); lcStmt.setObject(4, null); } else { lcStmt.setDouble(1, lat); lcStmt.setString(4, Double.toString(lat)); lcUpStmt.setDouble(1, lat); lcUpStmt.setString(4, Double.toString(lat)); } if (lon < -180.0 || lon > 180.0) { lcStmt.setObject(2, null); lcStmt.setObject(5, null); } else { lcStmt.setDouble(2, lon); lcStmt.setString(5, Double.toString(lon)); lcUpStmt.setDouble(2, lon); lcUpStmt.setString(5, Double.toString(lon)); } String locName = null; String fullName = null; Integer locId = null; geoId = buildTags.getGeography(country, state, null); if (geoId != null) { fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); if (locId == null) { lcStmt2.setString(1, "%" + city); lcStmt2.setString(2, country + "%"); ResultSet lcRS = lcStmt2.executeQuery(); if (lcRS.next()) { locId = lcRS.getInt(1); if (!lcRS.wasNull()) { localityHash.put(locName, locId); } } lcRS.close(); } } else { //unknown++; fullName = "Unknown"; locName = buildTags.buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setByte(3, (byte) 0); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setString(8, getLocalityName(country, state, null, city)); lcStmt.setObject(9, geoId); lcStmt.setTimestamp(10, ts); lcStmt.setInt(11, 1); lcStmt.setInt(12, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); } else if (!isLatNull && !isLonNull) { int count = BasicSQLUtils.getCountAsInt( "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = " + locId); if (count == 1) { lcUpStmt.setByte(3, (byte) 0); lcUpStmt.setString(6, "Point"); lcUpStmt.setInt(7, locId); lcUpStmt.executeUpdate(); } } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setInt(2, dspId); ceStmt.setInt(3, locId); ceStmt.setTimestamp(4, ts); ceStmt.setInt(5, 1); ceStmt.setInt(6, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, dir); coStmt.setString(5, dist); coStmt.setInt(6, colId); coStmt.setInt(7, colId); coStmt.setInt(8, ceId); coStmt.setTimestamp(9, ts); coStmt.setInt(10, 1); coStmt.setInt(11, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); Integer agentId = getAgentId(agentStmt, rep_first, rep_last); if (agentId == null) { agStmt.setInt(1, 0); agStmt.setString(2, rep_first); agStmt.setString(3, rep_last); agStmt.setTimestamp(4, ts); agStmt.setInt(5, 1); agStmt.setInt(6, 1); agStmt.executeUpdate(); agentId = BasicSQLUtils.getInsertedId(agStmt); if (agentId != null) { adStmt.setString(1, rep_city); adStmt.setString(2, rep_state); adStmt.setString(3, rep_zip); adStmt.setString(4, rep_country); adStmt.setInt(5, agentId); adStmt.setTimestamp(6, ts); adStmt.setInt(7, 1); adStmt.setInt(8, 1); adStmt.executeUpdate(); } else { log.error("agentId is null after being created: " + rep_first + ", " + rep_last); } } // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); } catch (Exception ex) { log.debug(recNum + " tag[" + tag + "]"); ex.printStackTrace(); } cnt++; if (cnt % 100 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); rlStmt.close(); agStmt.close(); adStmt.close(); lcUpStmt.close(); buildTags.cleanup(); } }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testDoubleFloat() throws SQLException, IOException { trace("test DOUBLE - FLOAT"); ResultInHBasePrinter.printFMETA(TEST_UTIL.getConfiguration(), LOG); ResultInHBasePrinter.printMETA(TEST_UTIL.getConfiguration(), LOG); ResultSet rs; Object o;/*from w ww .ja v a 2 s . c o m*/ stat = conn.createStatement(); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(11, -1, -1, 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(12,.0, .0, 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(13, 1., 1., 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(14, 12345678.89, 12345678.89, 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(15, 99999999.99, 99999999.99, 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(16, -99999999.99, -99999999.99, 2, 'testDoubleFloat')"); stat.execute( "INSERT INTO test (column1,column5,column4,column2,column3) VALUES(17, -99999999.99, -99999999.99, 2, 'testDoubleFloat')"); // stat.execute("INSERT INTO test (column1,column5,column4,column2,column3) VALUES(8, NULL, NULL, 2, 'testDoubleFloat')"); rs = stat.executeQuery( "SELECT column1,column5,column4 FROM test where column3='testDoubleFloat' ORDER BY column1"); // assertResultSetMeta(rs, 3, new String[] { "ID", "D", "R" }, new int[] { // Types.INTEGER, Types.DOUBLE, Types.REAL }, new int[] { 10, 17, 7 }, // new int[] { 0, 0, 0 }); BigDecimal bd; rs.next(); assertTrue(rs.getInt(1) == 11); assertTrue(!rs.wasNull()); assertTrue(rs.getInt(2) == -1); assertTrue(rs.getInt(3) == -1); assertTrue(!rs.wasNull()); bd = rs.getBigDecimal(2); assertTrue(bd.compareTo(new BigDecimal("-1.00")) == 0); assertTrue(!rs.wasNull()); o = rs.getObject(2); trace(o.getClass().getName()); assertTrue(o instanceof Double); assertTrue(((Double) o).compareTo(new Double("-1.00")) == 0); o = rs.getObject(3); trace(o.getClass().getName()); assertTrue(o instanceof Float); assertTrue(((Float) o).compareTo(new Float("-1.00")) == 0); rs.next(); assertTrue(rs.getInt(1) == 12); assertTrue(!rs.wasNull()); assertTrue(rs.getInt(2) == 0); assertTrue(!rs.wasNull()); assertTrue(rs.getInt(3) == 0); assertTrue(!rs.wasNull()); bd = rs.getBigDecimal(2); assertTrue(bd.compareTo(new BigDecimal("0.00")) == 0); assertTrue(!rs.wasNull()); bd = rs.getBigDecimal(3); assertTrue(bd.compareTo(new BigDecimal("0.00")) == 0); assertTrue(!rs.wasNull()); rs.next(); assertEquals(1.0, rs.getDouble(2)); assertEquals(1.0f, rs.getFloat(3)); rs.next(); assertEquals(12345678.89, rs.getDouble(2)); assertEquals(12345678.89f, rs.getFloat(3)); rs.next(); assertEquals(99999999.99, rs.getDouble(2)); assertEquals(99999999.99f, rs.getFloat(3)); rs.next(); assertEquals(-99999999.99, rs.getDouble(2)); assertEquals(-99999999.99f, rs.getFloat(3)); // rs.next(); // checkColumnBigDecimal(rs, 2, 0, null); // checkColumnBigDecimal(rs, 3, 0, null); // assertTrue(!rs.next()); // stat.execute("DROP TABLE test"); }
From source file:com.sdcs.courierbooking.service.UserServiceImpl.java
@Override public String delBoyDeliveredLists(String deliveryBoyId) { // TODO Auto-generated method stub JSONObject deliveredList = new JSONObject(); ResultSet deliveredListResultSet = userDao.delBoyDeliveredListsAbcd(deliveryBoyId); if (deliveredListResultSet != null) { Timestamp name = null;//from www . java2s. co m String name2 = "0"; int number = 0; double extraDistance = 0.00; double damount = 0.00; double tamount = 0.00; double textradistance = 0.00; String date = ""; try { JSONArray couriersArray = new JSONArray(); while (deliveredListResultSet.next()) { JSONObject couriersObject = new JSONObject(); name = deliveredListResultSet.getTimestamp("time_of_booking"); String amounta = deliveredListResultSet.getString("total_amount"); damount = Double.parseDouble(amounta); extraDistance = deliveredListResultSet.getDouble("extra_distance"); String DATE_FORMAT = "yyyy/MM/dd"; SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT); date = sdf.format(name); if (date.equals(name2)) { tamount = damount + tamount; textradistance = textradistance + extraDistance; number = number + 1; } else if (name2 != "0") { couriersObject.put("time_of_booking", name2); couriersObject.put("number_of_couriers", number); couriersObject.put("payedstatus", "Delivered"); couriersObject.put("distance", textradistance); couriersObject.put("amount", tamount); couriersArray.put(couriersObject); tamount = damount; textradistance = extraDistance; number = 1; name2 = date; } else { tamount = damount; textradistance = extraDistance; number = 1; name2 = date; } } JSONObject couriersObject = new JSONObject(); double roundOff = Math.round(tamount * 100.0) / 100.0; couriersObject.put("time_of_booking", date); couriersObject.put("payedstatus", "Delivered"); couriersObject.put("number_of_couriers", number); couriersObject.put("distance", textradistance); couriersObject.put("amount", roundOff); /*couriersObject.put("content",deliveredListResultSet.getString("content")); couriersObject.put("vehicle_needed",deliveredListResultSet.getString("vehicle_needed")); couriersObject.put("deliveryboys_needed",deliveredListResultSet.getString("vehicle_needed")); */ couriersArray.put(couriersObject); deliveredList.put("result", true); deliveredList.put("all_couriers", couriersArray); System.out.println(deliveredList); } catch (SQLException e) { deliveredList.put("result", false); try { deliveredListResultSet.close(); } catch (SQLException sqlEx) { System.out.println("Exception in QuickHistoryServerImpl"); } } finally { try { deliveredListResultSet.close(); } catch (SQLException e) { System.out.println("Exception in QuickHistoryServerImpl"); } } } else { deliveredList.put("result", false); } return deliveredList.toString(); }
From source file:edu.ku.brc.specify.tools.webportal.BuildSearchIndex2.java
License:asdf
/** * /*from w w w.j ava 2 s .co m*/ */ public boolean index(QBDataSourceListenerIFace progressListener) { if (progressListener != null) { progressListener.loading(); } long startTime = System.currentTimeMillis(); IndexWriter[] writers = null; long totalRecs = 0; List<String> solrFldXml = null; List<String> portalFldJson = null; try { for (int i = 0; i < analyzers.length; i++) { files[i] = new File(fileNames[i]); analyzers[i] = new StandardAnalyzer(Version.LUCENE_47, CharArraySet.EMPTY_SET); FileUtils.deleteDirectory(files[i]); } System.out.println("Indexing to directory '" + INDEX_DIR + "'..."); ExportMappingHelper map = new ExportMappingHelper(dbConn, mapping.getId()); Map<Integer, String> shortNames = getShortNamesForFields(map); totalRecs = BasicSQLUtils.getCount(dbConn, "SELECT COUNT(*) FROM " + map.getCacheTblName()); if (progressListener != null) { progressListener.loaded(); progressListener.rowCount(totalRecs); } long procRecs = 0; Statement stmt = null; Statement stmt2 = null; Statement stmt3 = null; try { writers = new IndexWriter[analyzers.length]; for (int i = 0; i < files.length; i++) { writers[i] = new IndexWriter(FSDirectory.open(files[i]), new IndexWriterConfig(Version.LUCENE_47, analyzers[i])); } System.out.println("Total Records: " + totalRecs); //stmt = dbConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY); stmt = dbConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); stmt2 = dbConn2.createStatement(); stmt3 = dbConn3.createStatement(); stmt3.setFetchSize(Integer.MIN_VALUE); //pStmt = dbConn3.prepareStatement("SELECT Text1 FROM preparation WHERE CollectionObjectID = ? AND Text1 IS NOT NULL"); String sql = createQuery(map.getCacheTblName()); System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); //may consume all memory for giant caches ResultSetMetaData md = rs.getMetaData(); StringBuilder indexStr = new StringBuilder(); StringBuilder contents = new StringBuilder(); StringBuilder sb = new StringBuilder(); String lat1 = null, lng1 = null, lat2 = null, lng2 = null; solrFldXml = getFldsXmlForSchema(map, shortNames); portalFldJson = getModelInJson(map, shortNames); while (rs.next()) { Document doc = new Document(); indexStr.setLength(0); contents.setLength(0); sb.setLength(0); lat1 = null; lng1 = null; lat2 = null; lng2 = null; for (int c = 1; c <= md.getColumnCount(); c++) { if (includeColumn(c)) { String value = ""; try { value = rs.getString(c); } catch (Exception ex) { ex.printStackTrace(); } if (c == 1) { //doc.add(new Field("spid", value, Field.Store.YES, Field.Index.ANALYZED)); doc.add(new StringField("spid", value, Field.Store.YES)); } else { if (value != null) { ExportMappingInfo info = map.getMappingByColIdx(c - 2); String fldType = getSolrFldType(info); if (fldType.equals("string")) { if (info.isFullTextSearch()) { doc.add(new TextField(shortNames.get(c - 2), value, Field.Store.YES)); } else { doc.add(new StringField(shortNames.get(c - 2), value, Field.Store.YES)); } } else if (fldType.equals("boolean")) { doc.add(new StringField(shortNames.get(c - 2), value, Field.Store.YES)); } else { if (fldType.endsWith("int")) { doc.add(new IntField(shortNames.get(c - 2), rs.getInt(c), Field.Store.YES)); } else if (fldType.endsWith("double")) { doc.add(new DoubleField(shortNames.get(c - 2), rs.getDouble(c), Field.Store.YES)); } else if (fldType.endsWith("long")) { doc.add(new LongField(shortNames.get(c - 2), rs.getLong(c), Field.Store.YES)); } else if (fldType.endsWith("float")) { doc.add(new FloatField(shortNames.get(c - 2), rs.getFloat(c), Field.Store.YES)); } } contents.append(StringUtils.isNotEmpty(value) ? value : " "); contents.append('\t'); if ("latitude1" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lat1 = value; } else if ("latitude2" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lat2 = value; } else if ("longitude1" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lng1 = value; } else if ("longitude2" .equalsIgnoreCase(map.getMappingByColIdx(c - 2).getSpFldName())) { lng2 = value; } } } } } indexStr.append(contents); //XXX what, exactly, are the reasons for the store/tokenize settings on these 2? //Ditto for store setting for geoc and img below? doc.add(new TextField("cs", indexStr.toString(), Field.Store.NO)); doc.add(new StringField("contents", contents.toString(), Field.Store.YES)); if (lat1 != null && lng1 != null) { String geoc = lat1 + " " + lng1; // if (lat2 != null && lng2 != null) // { // geoc += " " + lat2 + " " + lng2; // } doc.add(new StringField("geoc", geoc, Field.Store.NO)); } String attachments = getAttachments(dbConn2, "collectionobject", rs.getInt(1), false); if (attachments != null && attachments.length() > 0) { doc.add(new StringField("img", attachments, Field.Store.YES)); } writers[0].addDocument(doc); //System.out.println(procRecs+" "+rs.getString(1)); procRecs++; if (procRecs % 1000 == 0) { System.out.println(procRecs); if (progressListener != null) { progressListener.currentRow(procRecs - 1); } } if (procRecs % 100000 == 0) { System.out.println("Optimizing..."); //writers[0].optimize(); } } rs.close(); writePortalJsonToFile(portalFldJson); writeSolrFldXmlToFile(solrFldXml); writePortalInstanceJsonToFile(); } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } finally { if (stmt != null) { try { if (stmt != null) stmt.close(); if (stmt2 != null) stmt2.close(); if (stmt3 != null) stmt3.close(); } catch (SQLException e) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), e); return false; } } } } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } finally { for (Analyzer a : analyzers) { a.close(); } analyzers = null; for (IndexWriter writer : writers) { try { System.out.println("Optimizing..."); //writer.optimize(); writer.close(); System.out.println("Done Optimizing."); } catch (Exception ex) { UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(this.getClass(), ex); return false; } writer = null; } } buildZipFile(); if (progressListener != null) { progressListener.done(totalRecs); } long endTime = System.currentTimeMillis(); System.out.println("Time: " + (endTime - startTime) / 1000); return true; }
From source file:org.apache.ddlutils.platform.PlatformImplBase.java
/** * This is the core method to retrieve a value for a column from a result set. Its primary * purpose is to call the appropriate method on the result set, and to provide an extension * point where database-specific implementations can change this behavior. * // w w w.j a v a 2 s .co m * @param resultSet The result set to extract the value from * @param columnName The name of the column; can be <code>null</code> in which case the * <code>columnIdx</code> will be used instead * @param columnIdx The index of the column's value in the result set; is only used if * <code>columnName</code> is <code>null</code> * @param jdbcType The jdbc type to extract * @return The value * @throws SQLException If an error occurred while accessing the result set */ protected Object extractColumnValue(ResultSet resultSet, String columnName, int columnIdx, int jdbcType) throws SQLException { boolean useIdx = (columnName == null); Object value; switch (jdbcType) { case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: value = useIdx ? resultSet.getString(columnIdx) : resultSet.getString(columnName); break; case Types.NUMERIC: case Types.DECIMAL: value = useIdx ? resultSet.getBigDecimal(columnIdx) : resultSet.getBigDecimal(columnName); break; case Types.BIT: case Types.BOOLEAN: value = new Boolean(useIdx ? resultSet.getBoolean(columnIdx) : resultSet.getBoolean(columnName)); break; case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: value = new Integer(useIdx ? resultSet.getInt(columnIdx) : resultSet.getInt(columnName)); break; case Types.BIGINT: value = new Long(useIdx ? resultSet.getLong(columnIdx) : resultSet.getLong(columnName)); break; case Types.REAL: value = new Float(useIdx ? resultSet.getFloat(columnIdx) : resultSet.getFloat(columnName)); break; case Types.FLOAT: case Types.DOUBLE: value = new Double(useIdx ? resultSet.getDouble(columnIdx) : resultSet.getDouble(columnName)); break; case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: value = useIdx ? resultSet.getBytes(columnIdx) : resultSet.getBytes(columnName); break; case Types.DATE: value = useIdx ? resultSet.getDate(columnIdx) : resultSet.getDate(columnName); break; case Types.TIME: value = useIdx ? resultSet.getTime(columnIdx) : resultSet.getTime(columnName); break; case Types.TIMESTAMP: value = useIdx ? resultSet.getTimestamp(columnIdx) : resultSet.getTimestamp(columnName); break; case Types.CLOB: Clob clob = useIdx ? resultSet.getClob(columnIdx) : resultSet.getClob(columnName); if (clob == null) { value = null; } else { long length = clob.length(); if (length > Integer.MAX_VALUE) { value = clob; } else if (length == 0) { // the javadoc is not clear about whether Clob.getSubString // can be used with a substring length of 0 // thus we do the safe thing and handle it ourselves value = ""; } else { value = clob.getSubString(1l, (int) length); } } break; case Types.BLOB: Blob blob = useIdx ? resultSet.getBlob(columnIdx) : resultSet.getBlob(columnName); if (blob == null) { value = null; } else { long length = blob.length(); if (length > Integer.MAX_VALUE) { value = blob; } else if (length == 0) { // the javadoc is not clear about whether Blob.getBytes // can be used with for 0 bytes to be copied // thus we do the safe thing and handle it ourselves value = new byte[0]; } else { value = blob.getBytes(1l, (int) length); } } break; case Types.ARRAY: value = useIdx ? resultSet.getArray(columnIdx) : resultSet.getArray(columnName); break; case Types.REF: value = useIdx ? resultSet.getRef(columnIdx) : resultSet.getRef(columnName); break; default: value = useIdx ? resultSet.getObject(columnIdx) : resultSet.getObject(columnName); break; } return resultSet.wasNull() ? null : value; }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testInt() throws SQLException { trace("test INT"); ResultSet rs; Object o;/* www . ja v a2s . c o m*/ stat = conn.createStatement(); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(31,-1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(32,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(33,1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(34," + Integer.MAX_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(35," + Integer.MIN_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(36,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(37,0, 'testInt')"); // this should not be read - maxrows=6 // MySQL compatibility (is this required?) rs = stat.executeQuery("SELECT column1,column2,column3 FROM test where column3='testInt' ORDER BY column1"); // MySQL compatibility assertEquals(1, rs.findColumn("column1")); assertEquals(2, rs.findColumn("column2")); ResultSetMetaData meta = rs.getMetaData(); assertEquals(3, meta.getColumnCount()); assertTrue(rs.getRow() == 0); rs.next(); trace("default fetch size=" + rs.getFetchSize()); // 0 should be an allowed value (but it's not defined what is actually // means) rs.setFetchSize(1); assertThrows(SQLErrorCode.INVALID_VALUE_2, rs).setFetchSize(-1); // fetch size 100 is bigger than maxrows - not allowed rs.setFetchSize(6); assertTrue(rs.getRow() == 1); assertEquals(2, rs.findColumn("COLUMN2")); assertEquals(2, rs.findColumn("column2")); assertEquals(2, rs.findColumn("Column2")); assertEquals(1, rs.findColumn("COLUMN1")); assertEquals(1, rs.findColumn("column1")); assertEquals(1, rs.findColumn("Column1")); assertEquals(1, rs.findColumn("colUMN1")); assertTrue(rs.getInt(2) == -1 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("column2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("Column2") == -1 && !rs.wasNull()); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); o = rs.getObject("column2"); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); o = rs.getObject(2); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); assertTrue(rs.getBoolean("Column2")); assertTrue(rs.getByte("Column2") == (byte) -1); assertTrue(rs.getShort("Column2") == (short) -1); assertTrue(rs.getLong("Column2") == -1); assertTrue(rs.getFloat("Column2") == -1.0); assertTrue(rs.getDouble("Column2") == -1.0); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); assertTrue(rs.getInt("COLUMN1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("Column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt(1) == 31 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 2); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(!rs.getBoolean(2)); assertTrue(rs.getByte(2) == 0); assertTrue(rs.getShort(2) == 0); assertTrue(rs.getLong(2) == 0); assertTrue(rs.getFloat(2) == 0.0); assertTrue(rs.getDouble(2) == 0.0); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(rs.getInt(1) == 32 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 3); assertTrue(rs.getInt("COLUMN1") == 33 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == 1 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 4); assertTrue(rs.getInt("COLUMN1") == 34 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == Integer.MAX_VALUE && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 5); assertTrue(rs.getInt("column1") == 35 && !rs.wasNull()); assertTrue(rs.getInt("column2") == Integer.MIN_VALUE && !rs.wasNull()); assertTrue(rs.getString(1).equals("35") && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 6); assertTrue(rs.getInt("column1") == 36 && !rs.wasNull()); assertTrue(rs.getInt("column2") == 0 && !rs.wasNull()); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(rs.getInt(1) == 36 && !rs.wasNull()); assertTrue(rs.getString(1).equals("36") && !rs.wasNull()); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(!rs.wasNull()); // assertFalse(rs.next()); // assertEquals(0, rs.getRow()); // there is one more row, but because of setMaxRows we don't get it }
From source file:helma.objectmodel.db.NodeManager.java
/** * Create a new Node from a ResultSet./*ww w . ja v a 2s. c o m*/ */ public Node createNode(DbMapping dbm, ResultSet rs, DbColumn[] columns, int offset) throws SQLException, IOException, ClassNotFoundException { HashMap propBuffer = new HashMap(); String id = null; String name = null; String protoName = dbm.getTypeName(); DbMapping dbmap = dbm; Node node = new Node(safe); for (int i = 0; i < columns.length; i++) { int columnNumber = i + 1 + offset; // set prototype? if (columns[i].isPrototypeField()) { String protoId = rs.getString(columnNumber); protoName = dbm.getPrototypeName(protoId); if (protoName != null) { dbmap = getDbMapping(protoName); if (dbmap == null) { // invalid prototype name! app.logError("No prototype defined for prototype mapping \"" + protoName + "\" - Using default prototype \"" + dbm.getTypeName() + "\"."); dbmap = dbm; protoName = dbmap.getTypeName(); } } } // set id? if (columns[i].isIdField()) { id = rs.getString(columnNumber); // if id == null, the object doesn't actually exist - return null if (id == null) { return null; } } // set name? if (columns[i].isNameField()) { name = rs.getString(columnNumber); } Property newprop = new Property(node); switch (columns[i].getType()) { case Types.BIT: case Types.BOOLEAN: newprop.setBooleanValue(rs.getBoolean(columnNumber)); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: newprop.setIntegerValue(rs.getLong(columnNumber)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: newprop.setFloatValue(rs.getDouble(columnNumber)); break; case Types.DECIMAL: case Types.NUMERIC: BigDecimal num = rs.getBigDecimal(columnNumber); if (num == null) { break; } if (num.scale() > 0) { newprop.setFloatValue(num.doubleValue()); } else { newprop.setIntegerValue(num.longValue()); } break; case Types.VARBINARY: case Types.BINARY: newprop.setJavaObjectValue(rs.getBytes(columnNumber)); break; case Types.BLOB: case Types.LONGVARBINARY: { InputStream in = rs.getBinaryStream(columnNumber); if (in == null) { break; } ByteArrayOutputStream bout = new ByteArrayOutputStream(); byte[] buffer = new byte[2048]; int read; while ((read = in.read(buffer)) > -1) { bout.write(buffer, 0, read); } newprop.setJavaObjectValue(bout.toByteArray()); } break; case Types.LONGVARCHAR: try { newprop.setStringValue(rs.getString(columnNumber)); } catch (SQLException x) { Reader in = rs.getCharacterStream(columnNumber); if (in == null) { newprop.setStringValue(null); break; } StringBuffer out = new StringBuffer(); char[] buffer = new char[2048]; int read; while ((read = in.read(buffer)) > -1) { out.append(buffer, 0, read); } newprop.setStringValue(out.toString()); } break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: newprop.setStringValue(rs.getString(columnNumber)); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newprop.setDateValue(rs.getTimestamp(columnNumber)); break; case Types.NULL: newprop.setStringValue(null); break; case Types.CLOB: Clob cl = rs.getClob(columnNumber); if (cl == null) { newprop.setStringValue(null); break; } char[] c = new char[(int) cl.length()]; Reader isr = cl.getCharacterStream(); isr.read(c); newprop.setStringValue(String.copyValueOf(c)); break; default: newprop.setStringValue(rs.getString(columnNumber)); break; } if (rs.wasNull()) { newprop.setStringValue(null); } propBuffer.put(columns[i].getName(), newprop); // mark property as clean, since it's fresh from the db newprop.dirty = false; } if (id == null) { return null; } else { Transactor tx = Transactor.getInstance(); if (tx != null) { // Check if the node is already registered with the transactor - // it may be in the process of being DELETED, but do return the // new node if the old one has been marked as INVALID. DbKey key = new DbKey(dbmap, id); Node dirtyNode = tx.getDirtyNode(key); if (dirtyNode != null && dirtyNode.getState() != Node.INVALID) { return dirtyNode; } } } Hashtable propMap = new Hashtable(); DbColumn[] columns2 = dbmap.getColumns(); for (int i = 0; i < columns2.length; i++) { Relation rel = columns2[i].getRelation(); if (rel != null && rel.isPrimitiveOrReference()) { Property prop = (Property) propBuffer.get(columns2[i].getName()); if (prop == null) { continue; } prop.setName(rel.propName); // if the property is a pointer to another node, change the property type to NODE if (rel.isReference() && rel.usesPrimaryKey()) { // FIXME: References to anything other than the primary key are not supported prop.convertToNodeReference(rel); } propMap.put(rel.propName, prop); } } node.init(dbmap, id, name, protoName, propMap); return node; }