List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.osmdroid.reader.Reader.java
/** * imports the osm bz2 file into the database * * @param path/*from w w w . j av a 2s. c o m*/ * @param connection * @throws Exception if the file wasn't found, can't write the output file, or there's some kind of IO exception while reading */ public void read(String path, Connection connection) throws Exception { if (path == null) throw new IllegalArgumentException("path"); if (!new File(path).exists()) throw new FileNotFoundException("File Not Found"); PreparedStatement p; try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"nodes\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"lat\" DOUBLE NOT NULL , \"lon\" DOUBLE NOT NULL , \"version\" INTEGER, \"timestamp\" DATETIME, \"uid\" INTEGER, \"user\" TEXT, \"changeset\" INTEGER)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relation_members\" (\"type\" TEXT NOT NULL , \"ref\" INTEGER NOT NULL , \"role\" TEXT, \"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relations\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"user\" TEXT, \"uid\" INTEGER, \"version\" INTEGER, \"changeset\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"tag\" (\"id\" INTEGER NOT NULL , \"k\" TEXT NOT NULL , \"v\" TEXT NOT NULL , \"reftype\" INTEGER NOT NULL DEFAULT -1, PRIMARY KEY( \"reftype\",\"k\" ,\"id\" ) )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"way_no\" (\"way_id\" INTEGER NOT NULL , \"node_id\" INTEGER NOT NULL, PRIMARY KEY (\"way_id\", \"node_id\") ) "); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"ways\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"changeset\" INTEGER, \"version\" INTEGER, \"user\" TEXT, \"uid\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } BufferedReader xmlInputStream = getBufferedReaderForBZ2File(path); XmlPullParserFactory factory = XmlPullParserFactory.newInstance(); factory.setNamespaceAware(false); XmlPullParser xmlStreamReader = factory.newPullParser(); xmlStreamReader.setInput(xmlInputStream); Stack<String> xpath = new Stack<String>(); long recordCount = 0; long batchCount = 0; long lastId = -1; long start = System.currentTimeMillis(); OsmType lastType = OsmType.NODE; long id = -1; long changset = -1; double lat = 0.0; double lon = 0.0; long version = -1; String user = ""; long uid = -1; long inserts = 0; String key = ""; String val = ""; //int eventType = -1; Date timestamp = new Date(System.currentTimeMillis()); //connection.setAutoCommit(false); int eventType = xmlStreamReader.getEventType(); while (eventType != XmlPullParser.END_DOCUMENT) { String tagname = xmlStreamReader.getName(); recordCount++; key = ""; val = ""; id = -1; changset = -1; version = -1; user = ""; uid = -1; //timestamp = new Date(System.currentTimeMillis()); //System.out.println(recordCount); //System.out.println ("XMLEvent " + eventType + " " + tagname); //long btime = System.currentTimeMillis(); switch (eventType) { case XmlPullParser.START_TAG: if (xmlStreamReader.getName().toString().equalsIgnoreCase("osm")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("bounds")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO nodes (id,changeset,version,user,uid,timestamp,lat,lon) " + "VALUES (?,?,?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lat")) { lat = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lon")) { lon = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); p.setDouble(7, lat); p.setDouble(8, lon); try { p.executeUpdate(); inserts++; //batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.NODE; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO relations (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } else { System.err.println("relation attrib unhandled " + xmlStreamReader.getAttributeName(i)); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); // batchCount++; inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.RELATION; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { xpath.push(xmlStreamReader.getName().toString()); p = connection.prepareStatement("INSERT INTO ways (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); inserts++; // batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.WAY; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("tag")) { if (!xpath.isEmpty() && ((xpath.peek().equalsIgnoreCase("way") || xpath.peek().equalsIgnoreCase("node") || xpath.peek().equalsIgnoreCase("relation")) && lastId != -1)) { for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("k")) { key = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("v")) { val = xmlStreamReader.getAttributeValue(i); } else { //uncaptured attribute System.out.println(xmlStreamReader.getAttributeName(i) + "=" + xmlStreamReader.getAttributeValue(i)); } } if (lastId != -1) { p = connection .prepareStatement("INSERT INTO tag (id,k,v,reftype) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setString(3, val); p.setInt(4, lastType.ordinal()); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } // batchCount++; p.close(); p = null; } else { System.err.println("ERR0003"); } } else { System.err.println("ERR0002"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("nd")) { if (xpath.peek().equalsIgnoreCase("way") && lastId != -1) { id = -1; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } } if (id != -1) { p = connection .prepareStatement("INSERT INTO way_no (way_id,node_id) " + "VALUES (?,?); "); p.setLong(1, lastId); p.setLong(2, id); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; //batchCount++; } } else { System.err.println("ERR0001"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("member")) { if ((xpath.peek().equalsIgnoreCase("relation")) && lastId != -1) { //String type = ""; id = -1; //String role = ""; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("type")) { key = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("role")) { val = xmlStreamReader.getAttributeValue(i); } } if (lastId != -1) { p = connection.prepareStatement( "INSERT INTO relation_members (id,type,ref,role) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setLong(3, id); p.setString(4, val); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; // batchCount++; } else { System.err.println("ERR0006"); } } else { System.err.println("ERR0005"); } } else { System.err.println("unhandled node! " + xmlStreamReader.getName().toString()); } break; case XmlPullParser.TEXT: //System.out.print("text!" + xmlStreamReader.getText()); break; case XmlPullParser.END_TAG: //System.out.println("</" + xmlStreamReader.getName().toString() + ">"); if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { if (!xpath.isEmpty()) { xpath.pop(); } } break; default: //do nothing break; } //if (batchCount == 100) { //connection.commit(); // long txPerSecond = (System.currentTimeMillis() - btime); System.out.println((start - System.currentTimeMillis()) + " total elements processed " + recordCount + " inserts " + inserts + " stack " + xpath.size()); //batchCount = 0; //System.gc(); //System.out.println(); // } eventType = xmlStreamReader.next(); } System.out.println(System.currentTimeMillis() - start + "ms"); }
From source file:ro.nextreports.engine.queryexec.QueryExecutor.java
private void setParameterValue(PreparedStatement pstmt, Class paramValueClass, Object paramValue, int index) throws SQLException, QueryException { // for "NOT IN (?)" setting null -> result is undeterminated // ParameterUtil.NULL was good only for list of strings (for NOT IN)! if (ParameterUtil.NULL.equals(paramValue)) { paramValue = null;/*from ww w. ja v a2s . c o m*/ } if (paramValueClass.equals(Object.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.JAVA_OBJECT); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(Boolean.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIT); } else { if (paramValue instanceof IdName) { pstmt.setBoolean(index + 1, (Boolean) ((IdName) paramValue).getId()); } else { pstmt.setBoolean(index + 1, (Boolean) paramValue); } } } else if (paramValueClass.equals(Byte.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TINYINT); } else { if (paramValue instanceof IdName) { pstmt.setByte(index + 1, (Byte) ((IdName) paramValue).getId()); } else { pstmt.setByte(index + 1, (Byte) paramValue); } } } else if (paramValueClass.equals(Double.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DOUBLE); } else { if (paramValue instanceof IdName) { pstmt.setDouble(index + 1, (Double) ((IdName) paramValue).getId()); } else { pstmt.setDouble(index + 1, (Double) paramValue); } } } else if (paramValueClass.equals(Float.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.FLOAT); } else { if (paramValue instanceof IdName) { pstmt.setFloat(index + 1, (Float) ((IdName) paramValue).getId()); } else { pstmt.setFloat(index + 1, (Float) paramValue); } } } else if (paramValueClass.equals(Integer.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.INTEGER); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setInt(index + 1, (Integer) paramValue); } } } else if (paramValueClass.equals(Long.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { pstmt.setLong(index + 1, (Long) ((IdName) paramValue).getId()); } else { pstmt.setLong(index + 1, (Long) paramValue); } } } else if (paramValueClass.equals(Short.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.SMALLINT); } else { if (paramValue instanceof IdName) { pstmt.setShort(index + 1, (Short) ((IdName) paramValue).getId()); } else { pstmt.setShort(index + 1, (Short) paramValue); } } //@todo // ParameterUtil -> values are taken from dialect (where there is no BigDecimal yet!) // or from meta data } else if (paramValueClass.equals(BigDecimal.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DECIMAL); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(BigInteger.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigInteger) { pstmt.setBigDecimal(index + 1, new BigDecimal((BigInteger) (ser))); } else if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(String.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { if (paramValue instanceof IdName) { if (((IdName) paramValue).getId() == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { pstmt.setString(index + 1, ((IdName) paramValue).getId().toString()); } } else { pstmt.setString(index + 1, paramValue.toString()); } } } else if (paramValueClass.equals(Date.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DATE); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setDate(index + 1, new java.sql.Date(date.getTime())); } else { pstmt.setDate(index + 1, new java.sql.Date(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Timestamp.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIMESTAMP); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTimestamp(index + 1, new Timestamp(date.getTime())); } else { pstmt.setTimestamp(index + 1, new Timestamp(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Time.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIME); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTime(index + 1, new Time(date.getTime())); } else { pstmt.setTime(index + 1, new Time(((Date) paramValue).getTime())); } } } else { throw new QueryException("Parameter type " + paramValueClass.getName() + " not supported in query"); } // for logSql() statementParameters.put(index, paramValue); }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * /*from www .j a v a 2 s .co 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:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java
public Map<Integer, APIKey> getAccessTokensByDate(String date, boolean latest, String[] querySql, String loggedInUser) throws AppManagementException { Connection connection = null; PreparedStatement ps = null;//w ww. j a v a2s .c om ResultSet getTokenRS = null; Map<Integer, APIKey> tokenDataMap = new HashMap<Integer, APIKey>(); try { SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH); java.util.Date searchDate = fmt.parse(date); Date sqlDate = new Date(searchDate.getTime()); connection = APIMgtDBUtil.getConnection(); PreparedStatement getToken; if (latest) { getToken = connection.prepareStatement(querySql[0]); } else { getToken = connection.prepareStatement(querySql[1]); } getToken.setDate(1, sqlDate); getTokenRS = getToken.executeQuery(); Integer i = 0; while (getTokenRS.next()) { String authorizedUser = getTokenRS.getString("AUTHZ_USER"); if (AppManagerUtil.isLoggedInUserAuthorizedToRevokeToken(loggedInUser, authorizedUser)) { String accessToken = AppManagerUtil.decryptToken(getTokenRS.getString("ACCESS_TOKEN")); APIKey apiKey = new APIKey(); apiKey.setAccessToken(accessToken); apiKey.setAuthUser(authorizedUser); apiKey.setTokenScope(getTokenRS.getString("TOKEN_SCOPE")); apiKey.setCreatedDate(getTokenRS.getTimestamp("TIME_CREATED").toString().split("\\.")[0]); String consumerKey = getTokenRS.getString("CONSUMER_KEY"); apiKey.setConsumerKey(AppManagerUtil.decryptToken(consumerKey)); apiKey.setValidityPeriod(getTokenRS.getLong("VALIDITY_PERIOD")); tokenDataMap.put(i, apiKey); i++; } } } catch (SQLException e) { handleException("Failed to get access token data. ", e); } catch (ParseException e) { handleException("Failed to get access token data. ", e); } catch (CryptoException e) { handleException("Failed to get access token data. ", e); } finally { APIMgtDBUtil.closeAllConnections(ps, connection, getTokenRS); } return tokenDataMap; }
From source file:br.com.cobranca.util.Util.java
public static <T> boolean alterarRegistro(T objAlterado, Class<T> classe, Connection con, String strWhere) throws Exception, SQLException { if (strWhere == null || strWhere.trim().equals("")) { return false; }//from w w w . j a v a2 s. c om PreparedStatement ps = null; ResultSet rs = null; T objOriginal = classe.newInstance(); try { // Recuperar objeto original no banco de dados String nomeTabela = objAlterado.getClass().getSimpleName(); String strSql = "SELECT * FROM " + nomeTabela + " " + strWhere; ps = con.prepareStatement(strSql); rs = ps.executeQuery(); if (rs.next()) { objOriginal = Util.atribuirValores(classe, rs); } else { return false; } rs.close(); ps.close(); // Comparar valores dos dois objetos strSql = "UPDATE " + nomeTabela + " SET "; boolean efetuarAlteracao; boolean usarVirgula = false; for (Field field : objAlterado.getClass().getDeclaredFields()) { efetuarAlteracao = false; String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else { return false; } if (efetuarAlteracao) { if (usarVirgula) { strSql = strSql + ", "; //usarVirgula = false; } strSql = strSql + nomeColuna + " = ? "; usarVirgula = true; } } //Se no houve alterao, retorna falso if (!strSql.contains("?")) { return true; } strSql = strSql + strWhere; ps = con.prepareStatement(strSql); int i = 1; // ps.set?() for (Field field : objAlterado.getClass().getDeclaredFields()) { String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setInt(i, valorAlterado); } i++; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { ps.setString(i, valorAlterado); i++; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDouble(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setFloat(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setLong(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setBoolean(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDate(i, new java.sql.Date(valorAlterado.getTime())); } i++; } } else { return false; } } // fim int qtdLinhasAfetadas = ps.executeUpdate(); if (qtdLinhasAfetadas <= 0) { return false; } } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } } return true; }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Set a properties data for inserts or updates * * @param insert perform insert or update? * @param prop current property * @param allData all data of the instance (might be needed to buld references, etc.) * @param con an open and valid connection * @param data current property data * @param ps prepared statement for the data table * @param ft fulltext indexer * @param upperColumnPos position of the uppercase column (if present, else <code>-1</code>) * @param includeFullText add fulltext entries? Will be skipped for position only changes * @throws SQLException on errors * @throws FxUpdateException on errors * @throws FxDbException on errors * @throws FxNoAccessException for FxNoAccess values *//* w ww . j a va2s .co m*/ private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con, FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos, boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException { FxValue value = data.getValue(); if (value instanceof FxNoAccess) throw new FxNoAccessException("ex.content.value.noaccess"); if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) { if (((FxPropertyAssignment) data.getAssignment()).isMultiLang()) throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi", data.getXPathFull()); else throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single", data.getXPathFull()); } int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2; int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS; final FxEnvironment env = CacheAdmin.getEnvironment(); if (prop.getDataType().isSingleRowStorage()) { //Data types that just use one db row can be handled in a very similar way Object translatedValue; GregorianCalendar gc = null; final long[] translatedLanguages = value.getTranslatedLanguages(); for (long translatedLanguage : translatedLanguages) { translatedValue = value.getTranslation(translatedLanguage); if (translatedValue == null) { LOG.warn("Translation for " + data.getXPath() + " is null!"); } ps.setLong(pos_lang, translatedLanguage); if (!value.isMultiLanguage()) ps.setBoolean(pos_isdef_lang, true); else ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage)); if (upperColumnPos != -1) { final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale() : Locale.getDefault(); ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale)); } int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop); switch (prop.getDataType()) { case Double: checkDataType(FxDouble.class, value, data.getXPathFull()); ps.setDouble(pos[0], (Double) translatedValue); break; case Float: checkDataType(FxFloat.class, value, data.getXPathFull()); ps.setFloat(pos[0], (Float) translatedValue); break; case LargeNumber: checkDataType(FxLargeNumber.class, value, data.getXPathFull()); ps.setLong(pos[0], (Long) translatedValue); break; case Number: checkDataType(FxNumber.class, value, data.getXPathFull()); ps.setInt(pos[0], (Integer) translatedValue); break; case HTML: checkDataType(FxHTML.class, value, data.getXPathFull()); boolean useTidy = ((FxHTML) value).isTidyHTML(); ps.setBoolean(pos[1], useTidy); final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue); if (useTidy) { translatedValue = extractorInput; } final HtmlExtractor result = new HtmlExtractor(extractorInput, true); setBigString(ps, pos[2], result.getText()); setBigString(ps, pos[0], (String) translatedValue); break; case String1024: case Text: checkDataType(FxString.class, value, data.getXPathFull()); setBigString(ps, pos[0], (String) translatedValue); break; case Boolean: checkDataType(FxBoolean.class, value, data.getXPathFull()); ps.setBoolean(pos[0], (Boolean) translatedValue); break; case Date: checkDataType(FxDate.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); //strip all time information, this might not be necessary since ps.setDate() strips them //for most databases but won't hurt either ;) gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); break; case DateTime: checkDataType(FxDateTime.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); break; case DateRange: checkDataType(FxDateRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis())); break; case DateTimeRange: checkDataType(FxDateTimeRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis())); break; case Binary: checkDataType(FxBinary.class, value, data.getXPathFull()); BinaryDescriptor binary = (BinaryDescriptor) translatedValue; if (!binary.isNewBinary()) { ps.setLong(pos[0], binary.getId()); } else { try { //transfer the binary from the transit table to the binary table BinaryDescriptor created = binaryStorage.binaryTransit(con, binary); ps.setLong(pos[0], created.getId()); //check all other properties if they contain the same handle //and replace with the data of the new binary for (FxData _curr : allData) { if (_curr instanceof FxPropertyData && !_curr.isEmpty() && ((FxPropertyData) _curr).getValue() instanceof FxBinary) { FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue(); _val._replaceHandle(binary.getHandle(), created); } } } catch (FxApplicationException e) { throw new FxDbException(e); } } break; case SelectOne: checkDataType(FxSelectOne.class, value, data.getXPathFull()); ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId()); break; case SelectMany: checkDataType(FxSelectMany.class, value, data.getXPathFull()); SelectMany sm = (SelectMany) translatedValue; for (int i1 = 0; i1 < sm.getSelected().size(); i1++) { FxSelectListItem item = sm.getSelected().get(i1); if (i1 > 0) { if (batchContentDataChanges()) ps.addBatch(); else ps.executeUpdate(); } ps.setLong(pos[0], item.getId()); ps.setString(pos[1], sm.getSelectedIdsList()); ps.setLong(pos[2], sm.getSelectedIds().size()); } if (sm.getSelected().size() == 0) ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row break; case Reference: //reference integrity check is done prior to saving ps.setLong(pos[0], ((FxPK) translatedValue).getId()); break; case InlineReference: default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType()) : getValueDataUpdatePos(prop.getDataType()); if (value.hasValueData(translatedLanguage)) { ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage)); } else ps.setNull(valueDataPos, Types.NUMERIC); if (batchContentDataChanges()) ps.addBatch(); else { try { ps.executeUpdate(); } catch (SQLException e) { LOG.error(prop.getName(), e); throw e; } } } } else { switch (prop.getDataType()) { //TODO: implement datatype specific insert default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } } if (ft != null && prop.isFulltextIndexed() && includeFullText) ft.index(data); }
From source file:com.cloud.configuration.ConfigurationManagerImpl.java
@Override @DB//from w w w. j a v a2 s .c o m public String updateConfiguration(final long userId, final String name, final String category, final String value, final String scope, final Long resourceId) { final String validationMsg = validateConfigurationValue(name, value, scope); if (validationMsg != null) { s_logger.error("Invalid configuration option, name: " + name + ", value:" + value); throw new InvalidParameterValueException(validationMsg); } // If scope of the parameter is given then it needs to be updated in the // corresponding details table, // if scope is mentioned as global or not mentioned then it is normal // global parameter updation if (scope != null && !scope.isEmpty() && !ConfigKey.Scope.Global.toString().equalsIgnoreCase(scope)) { switch (ConfigKey.Scope.valueOf(scope)) { case Zone: final DataCenterVO zone = _zoneDao.findById(resourceId); if (zone == null) { throw new InvalidParameterValueException("unable to find zone by id " + resourceId); } _dcDetailsDao.addDetail(resourceId, name, value, true); break; case Cluster: final ClusterVO cluster = _clusterDao.findById(resourceId); if (cluster == null) { throw new InvalidParameterValueException("unable to find cluster by id " + resourceId); } ClusterDetailsVO clusterDetailsVO = _clusterDetailsDao.findDetail(resourceId, name); if (clusterDetailsVO == null) { clusterDetailsVO = new ClusterDetailsVO(resourceId, name, value); _clusterDetailsDao.persist(clusterDetailsVO); } else { clusterDetailsVO.setValue(value); _clusterDetailsDao.update(clusterDetailsVO.getId(), clusterDetailsVO); } break; case StoragePool: final StoragePoolVO pool = _storagePoolDao.findById(resourceId); if (pool == null) { throw new InvalidParameterValueException("unable to find storage pool by id " + resourceId); } if (name.equals(CapacityManager.StorageOverprovisioningFactor.key())) { if (pool.getPoolType() != StoragePoolType.NetworkFilesystem && pool.getPoolType() != StoragePoolType.VMFS) { throw new InvalidParameterValueException("Unable to update storage pool with id " + resourceId + ". Overprovision not supported for " + pool.getPoolType()); } } _storagePoolDetailsDao.addDetail(resourceId, name, value, true); break; case Account: final AccountVO account = _accountDao.findById(resourceId); if (account == null) { throw new InvalidParameterValueException("unable to find account by id " + resourceId); } AccountDetailVO accountDetailVO = _accountDetailsDao.findDetail(resourceId, name); if (accountDetailVO == null) { accountDetailVO = new AccountDetailVO(resourceId, name, value); _accountDetailsDao.persist(accountDetailVO); } else { accountDetailVO.setValue(value); _accountDetailsDao.update(accountDetailVO.getId(), accountDetailVO); } break; case ImageStore: final ImageStoreVO imgStore = _imageStoreDao.findById(resourceId); Preconditions.checkState(imgStore != null); _imageStoreDetailsDao.addDetail(resourceId, name, value, true); break; default: throw new InvalidParameterValueException("Scope provided is invalid"); } return value; } // Execute all updates in a single transaction final TransactionLegacy txn = TransactionLegacy.currentTxn(); txn.start(); if (!_configDao.update(name, category, value)) { s_logger.error("Failed to update configuration option, name: " + name + ", value:" + value); throw new CloudRuntimeException("Failed to update configuration value. Please contact Cloud Support."); } PreparedStatement pstmt = null; if (Config.XenServerGuestNetwork.key().equalsIgnoreCase(name)) { final String sql = "update host_details set value=? where name=?"; try { pstmt = txn.prepareAutoCloseStatement(sql); pstmt.setString(1, value); pstmt.setString(2, "guest.network.device"); pstmt.executeUpdate(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to update guest.network.device in host_details due to exception ", e); } } else if (Config.XenServerPrivateNetwork.key().equalsIgnoreCase(name)) { final String sql = "update host_details set value=? where name=?"; try { pstmt = txn.prepareAutoCloseStatement(sql); pstmt.setString(1, value); pstmt.setString(2, "private.network.device"); pstmt.executeUpdate(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to update private.network.device in host_details due to exception ", e); } } else if (Config.XenServerPublicNetwork.key().equalsIgnoreCase(name)) { final String sql = "update host_details set value=? where name=?"; try { pstmt = txn.prepareAutoCloseStatement(sql); pstmt.setString(1, value); pstmt.setString(2, "public.network.device"); pstmt.executeUpdate(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to update public.network.device in host_details due to exception ", e); } } else if (Config.XenServerStorageNetwork1.key().equalsIgnoreCase(name)) { final String sql = "update host_details set value=? where name=?"; try { pstmt = txn.prepareAutoCloseStatement(sql); pstmt.setString(1, value); pstmt.setString(2, "storage.network.device1"); pstmt.executeUpdate(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to update storage.network.device1 in host_details due to exception ", e); } } else if (Config.XenServerStorageNetwork2.key().equals(name)) { final String sql = "update host_details set value=? where name=?"; try { pstmt = txn.prepareAutoCloseStatement(sql); pstmt.setString(1, value); pstmt.setString(2, "storage.network.device2"); pstmt.executeUpdate(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to update storage.network.device2 in host_details due to exception ", e); } } else if (Config.SecStorageSecureCopyCert.key().equalsIgnoreCase(name)) { //FIXME - Ideally there should be a listener model to listen to global config changes and be able to take action gracefully. //Expire the download urls final String sqlTemplate = "update template_store_ref set download_url_created=?"; final String sqlVolume = "update volume_store_ref set download_url_created=?"; try { // Change for templates pstmt = txn.prepareAutoCloseStatement(sqlTemplate); pstmt.setDate(1, new Date(-1l));// Set the time before the epoch time. pstmt.executeUpdate(); // Change for volumes pstmt = txn.prepareAutoCloseStatement(sqlVolume); pstmt.setDate(1, new Date(-1l));// Set the time before the epoch time. pstmt.executeUpdate(); // Cleanup the download urls _storageManager.cleanupDownloadUrls(); } catch (final Throwable e) { throw new CloudRuntimeException( "Failed to clean up download URLs in template_store_ref or volume_store_ref due to exception ", e); } } txn.commit(); return _configDao.getValue(name); }
From source file:org.jasig.portal.layout.simple.RDBMUserLayoutStore.java
/** * Save the user layout.//w w w. jav a 2 s .co m * @param person * @param profile * @param layoutXML * @throws Exception */ public void setUserLayout(final IPerson person, final IUserProfile profile, final Document layoutXML, final boolean channelsAdded) { final long startTime = System.currentTimeMillis(); final int userId = person.getID(); final int profileId = profile.getProfileId(); this.transactionOperations.execute(new TransactionCallback<Object>() { @Override public Object doInTransaction(TransactionStatus status) { return jdbcOperations.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection con) throws SQLException, DataAccessException { int layoutId = 0; ResultSet rs; // Eventually we want to be able to just get layoutId from the // profile, but because of the template user layouts we have to do this for now ... layoutId = getLayoutID(userId, profileId); boolean firstLayout = false; if (layoutId == 0) { // First personal layout for this user/profile layoutId = 1; firstLayout = true; } String sql = "DELETE FROM UP_LAYOUT_PARAM WHERE USER_ID=? AND LAYOUT_ID=?"; PreparedStatement pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } sql = "DELETE FROM UP_LAYOUT_STRUCT WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } PreparedStatement structStmt = con.prepareStatement("INSERT INTO UP_LAYOUT_STRUCT " + "(USER_ID, LAYOUT_ID, STRUCT_ID, NEXT_STRUCT_ID, CHLD_STRUCT_ID,EXTERNAL_ID,CHAN_ID,NAME,TYPE,HIDDEN,IMMUTABLE,UNREMOVABLE) " + "VALUES (" + userId + "," + layoutId + ",?,?,?,?,?,?,?,?,?,?)"); PreparedStatement parmStmt = con.prepareStatement("INSERT INTO UP_LAYOUT_PARAM " + "(USER_ID, LAYOUT_ID, STRUCT_ID, STRUCT_PARM_NM, STRUCT_PARM_VAL) " + "VALUES (" + userId + "," + layoutId + ",?,?,?)"); int firstStructId; try { firstStructId = saveStructure(layoutXML.getFirstChild().getFirstChild(), structStmt, parmStmt); } finally { structStmt.close(); parmStmt.close(); } //Check to see if the user has a matching layout sql = "SELECT * FROM UP_USER_LAYOUT WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, layoutId); if (log.isDebugEnabled()) log.debug(sql); rs = pstmt.executeQuery(); try { if (!rs.next()) { // If not, the default user is found and the layout rows from the default user are copied for the current user. int defaultUserId; sql = "SELECT USER_DFLT_USR_ID FROM UP_USER WHERE USER_ID=?"; PreparedStatement pstmt2 = con.prepareStatement(sql); try { pstmt2.clearParameters(); pstmt2.setInt(1, userId); if (log.isDebugEnabled()) log.debug(sql); ResultSet rs2 = null; try { rs2 = pstmt2.executeQuery(); rs2.next(); defaultUserId = rs2.getInt(1); } finally { rs2.close(); } } finally { pstmt2.close(); } // Add to UP_USER_LAYOUT sql = "SELECT USER_ID,LAYOUT_ID,LAYOUT_TITLE,INIT_STRUCT_ID FROM UP_USER_LAYOUT WHERE USER_ID=?"; pstmt2 = con.prepareStatement(sql); try { pstmt2.clearParameters(); pstmt2.setInt(1, defaultUserId); if (log.isDebugEnabled()) log.debug(sql); ResultSet rs2 = pstmt2.executeQuery(); try { if (rs2.next()) { // There is a row for this user's template user... sql = "INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)"; PreparedStatement pstmt3 = con.prepareStatement(sql); try { pstmt3.clearParameters(); pstmt3.setInt(1, userId); pstmt3.setInt(2, rs2.getInt("LAYOUT_ID")); pstmt3.setString(3, rs2.getString("LAYOUT_TITLE")); pstmt3.setInt(4, rs2.getInt("INIT_STRUCT_ID")); if (log.isDebugEnabled()) log.debug(sql); pstmt3.executeUpdate(); } finally { pstmt3.close(); } } else { // We can't rely on the template user, but we still need a row... sql = "INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)"; PreparedStatement pstmt3 = con.prepareStatement(sql); try { pstmt3.clearParameters(); pstmt3.setInt(1, userId); pstmt3.setInt(2, layoutId); pstmt3.setString(3, "default layout"); pstmt3.setInt(4, 1); if (log.isDebugEnabled()) log.debug(sql); pstmt3.executeUpdate(); } finally { pstmt3.close(); } } } finally { rs2.close(); } } finally { pstmt2.close(); } } } finally { rs.close(); } } finally { pstmt.close(); } //Update the users layout with the correct inital structure ID sql = "UPDATE UP_USER_LAYOUT SET INIT_STRUCT_ID=? WHERE USER_ID=? AND LAYOUT_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, firstStructId); pstmt.setInt(2, userId); pstmt.setInt(3, layoutId); if (log.isDebugEnabled()) log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } // Update the last time the user saw the list of available channels if (channelsAdded) { sql = "UPDATE UP_USER SET LST_CHAN_UPDT_DT=? WHERE USER_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setDate(1, new java.sql.Date(System.currentTimeMillis())); pstmt.setInt(2, userId); log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } } if (firstLayout) { int defaultUserId; int defaultLayoutId; // Have to copy some of data over from the default user sql = "SELECT USER_DFLT_USR_ID,USER_DFLT_LAY_ID FROM UP_USER WHERE USER_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); log.debug(sql); rs = pstmt.executeQuery(); try { rs.next(); defaultUserId = rs.getInt(1); defaultLayoutId = rs.getInt(2); } finally { rs.close(); } } finally { pstmt.close(); } sql = "UPDATE UP_USER_PROFILE SET LAYOUT_ID=1 WHERE USER_ID=? AND PROFILE_ID=?"; pstmt = con.prepareStatement(sql); try { pstmt.clearParameters(); pstmt.setInt(1, userId); pstmt.setInt(2, profileId); log.debug(sql); pstmt.executeUpdate(); } finally { pstmt.close(); } } return null; } }); } }); if (log.isDebugEnabled()) { long stopTime = System.currentTimeMillis(); log.debug("RDBMUserLayoutStore::setUserLayout(): Layout document for user " + userId + " took " + (stopTime - startTime) + " milliseconds to save"); } }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
public Map<Integer, APIKey> getAccessTokensByDate(String date, boolean latest, String[] querySql, String loggedInUser) throws APIManagementException { Connection connection = null; PreparedStatement ps = null; ResultSet result = null;//from w ww. ja v a 2 s. co m Map<Integer, APIKey> tokenDataMap = new HashMap<Integer, APIKey>(); try { SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH); java.util.Date searchDate = fmt.parse(date); Date sqlDate = new Date(searchDate.getTime()); connection = APIMgtDBUtil.getConnection(); if (latest) { ps = connection.prepareStatement(querySql[0]); } else { ps = connection.prepareStatement(querySql[1]); } ps.setDate(1, sqlDate); result = ps.executeQuery(); Integer i = 0; boolean accessTokenRowBreaker = false; while (accessTokenRowBreaker || result.next()) { accessTokenRowBreaker = true; String username = result.getString(APIConstants.IDENTITY_OAUTH2_FIELD_AUTHORIZED_USER); String domainName = result.getString(APIConstants.IDENTITY_OAUTH2_FIELD_USER_DOMAIN); String authorizedUserWithDomain = UserCoreUtil.addDomainToName(username, domainName); if (APIUtil.isLoggedInUserAuthorizedToRevokeToken(loggedInUser, authorizedUserWithDomain)) { String accessToken = APIUtil.decryptToken(result.getString("ACCESS_TOKEN")); APIKey apiKey = new APIKey(); apiKey.setAccessToken(accessToken); apiKey.setAuthUser(authorizedUserWithDomain); apiKey.setCreatedDate(result.getTimestamp("TIME_CREATED").toString().split("\\.")[0]); String consumerKey = result.getString("CONSUMER_KEY"); apiKey.setConsumerKey(consumerKey); apiKey.setValidityPeriod(result.getLong("VALIDITY_PERIOD")); // Load all the rows to in memory and build the scope string List<String> scopes = new ArrayList<String>(); String tokenString = result.getString("ACCESS_TOKEN"); do { String currentRowTokenString = result.getString("ACCESS_TOKEN"); if (tokenString.equals(currentRowTokenString)) { scopes.add(result.getString(APIConstants.IDENTITY_OAUTH2_FIELD_TOKEN_SCOPE)); } else { accessTokenRowBreaker = true; break; } } while (result.next()); apiKey.setTokenScope(getScopeString(scopes)); tokenDataMap.put(i, apiKey); i++; } } } catch (SQLException e) { handleException("Failed to get access token data. ", e); } catch (ParseException e) { handleException("Failed to get access token data. ", e); } catch (CryptoException e) { handleException("Failed to get access token data. ", e); } finally { APIMgtDBUtil.closeAllConnections(ps, connection, result); } return tokenDataMap; }
From source file:org.gbif.harvest.portal.synchronise.dao.jdbc.RawOccurrenceRecordDaoImpl.java
/** * @see org.gbif.harvest.portal.synchronise.dao.RawOccurrenceRecordDao#create(org.gbif.harvest.portal.synchronise.model.RawOccurrenceRecord) *//* ww w . j a v a 2 s.c o m*/ public long create(final RawOccurrenceRecord rawOccurrenceRecord) { KeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { Timestamp createTime = new Timestamp(System.currentTimeMillis()); public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement(RawOccurrenceRecordDaoImpl.CREATE_SQL); ps.setLong(1, rawOccurrenceRecord.getDataProviderId()); ps.setLong(2, rawOccurrenceRecord.getDataResourceId()); ps.setLong(3, rawOccurrenceRecord.getResourceAccessPointId()); ps.setString(4, rawOccurrenceRecord.getInstitutionCode()); ps.setString(5, rawOccurrenceRecord.getCollectionCode()); ps.setString(6, rawOccurrenceRecord.getCatalogueNumber()); // ensure line breaking characters are replaced with a space, this breaks the scroop-in (import // of mysql tables into hadoop) ps.setString(7, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getScientificName(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(8, StringUtils.trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace(rawOccurrenceRecord.getAuthor(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(9, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getRank(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(10, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getKingdom(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(11, StringUtils.trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace(rawOccurrenceRecord.getPhylum(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(12, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getKlass(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(13, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getOrder(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(14, StringUtils.trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace(rawOccurrenceRecord.getFamily(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(15, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getGenus(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(16, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getSpecies(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(17, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getSubspecies(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(18, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getLatitude(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(19, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getLongitude(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(20, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getLatLongPrecision(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(21, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getMinAltitude(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(22, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getMaxAltitude(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(23, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getAltitudePrecision(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(24, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getMinDepth(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(25, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getMaxDepth(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(26, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getDepthPrecision(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(27, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getContinentOrOcean(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(28, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getCountry(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(29, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getStateOrProvince(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(30, StringUtils.trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace(rawOccurrenceRecord.getCounty(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(31, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getCollectorName(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(32, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getLocality(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(32, StringUtils.trimToNull(StringUtils.replace(StringUtils.replace( StringUtils.replace(rawOccurrenceRecord.getLocality(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(33, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getYear(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(34, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getMonth(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(35, StringUtils.trimToNull(StringUtils.replace(StringUtils .replace(StringUtils.replace(rawOccurrenceRecord.getDay(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(36, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getBasisOfRecord(), "\n", " "), "\r", " "), "\t", " "))); ps.setString(37, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getIdentifierName(), "\n", " "), "\r", " "), "\t", " "))); ps.setDate(38, createSQLDate(rawOccurrenceRecord.getDateIdentified())); ps.setString(39, StringUtils .trimToNull(StringUtils.replace( StringUtils.replace(StringUtils.replace( rawOccurrenceRecord.getUnitQualifier(), "\n", " "), "\r", " "), "\t", " "))); ps.setTimestamp(40, createTime); ps.setTimestamp(41, createTime); return ps; } }, keyHolder); rawOccurrenceRecord.setId(keyHolder.getKey().longValue()); return keyHolder.getKey().longValue(); }