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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Standplaats standplaats) throws DAOException { try {//from ww w .j a v a 2 s .com transactionTemplate.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus status) { jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection .prepareStatement("insert into bag_standplaats (" + "bag_standplaats_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel," + "standplaats_status," + "standplaats_geometrie," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer," + "bag_nummeraanduiding_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, standplaats.getIdentificatie()); ps.setInt(2, standplaats.getAanduidingRecordInactief().ordinal()); ps.setLong(3, standplaats.getAanduidingRecordCorrectie()); ps.setInt(4, standplaats.getOfficieel().ordinal()); ps.setInt(5, standplaats.getStandplaatsStatus().ordinal()); ps.setString(6, standplaats.getStandplaatsGeometrie()); ps.setTimestamp(7, new Timestamp(standplaats.getBegindatumTijdvakGeldigheid().getTime())); if (standplaats.getEinddatumTijdvakGeldigheid() == null) ps.setNull(8, Types.TIMESTAMP); else ps.setTimestamp(8, new Timestamp(standplaats.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(9, standplaats.getInOnderzoek().ordinal()); ps.setDate(10, new Date(standplaats.getDocumentdatum().getTime())); ps.setString(11, standplaats.getDocumentnummer()); ps.setLong(12, standplaats.getHoofdAdres()); return ps; } }); insertNevenadressen(TypeAdresseerbaarObject.STANDPLAATS, standplaats); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting standplaats: " + standplaats.getIdentificatie(), e); } }
From source file:net.sf.farrago.namespace.sfdc.SfdcUdx.java
public static void query(String query, String types, PreparedStatement resultInserter) throws SQLException { SoapBindingStub binding = (SoapBindingStub) FarragoUdrRuntime.getDataServerRuntimeSupport(null); try {/*from ww w . j a v a2s . c o m*/ QueryOptions qo = new QueryOptions(); int batchsize = 500; qo.setBatchSize(new Integer(batchsize)); binding.setHeader(new SforceServiceLocator().getServiceName().getNamespaceURI(), "QueryOptions", qo); String objName = query; int fromIdx = query.lastIndexOf(" from"); if (fromIdx > 0) { objName = query.substring(fromIdx + 6); } // strip off quotes for boolean values query = stripQuotes(query, objName); log.info("SFDC Query: " + query); QueryResult qr = binding.query(query); if (qr.isDone()) { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievedAllRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } else { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievingRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } SObject[] records = qr.getRecords(); String[] metadataType = types.split(","); // query is of following format: // "select col1,col2,... from" String cols = query.substring(7); fromIdx = cols.lastIndexOf(" from"); cols = cols.substring(0, fromIdx); cols = cols.trim(); String[] columnValues = new String[metadataType.length]; if (records != null) { boolean bContinue = true; while (bContinue) { // for each record returned in query, // get value of each field for (int i = 0; i < records.length; i++) { MessageElement[] elements = records[i].get_any(); if (elements != null) { for (int j = 0; j < elements.length; j++) { MessageElement elt = elements[j]; String eltVal = elt.getValue(); columnValues[j] = (eltVal != null) ? eltVal : "null"; if (metadataType[j].indexOf("TIMESTAMP") != -1) { // TIMESTAMP if (eltVal != null) { String tstampstr = eltVal.replace("T", " "); tstampstr = tstampstr.substring(0, tstampstr.indexOf(".")); java.sql.Timestamp tstamp = java.sql.Timestamp.valueOf(tstampstr); resultInserter.setTimestamp(j + 1, tstamp); } else { resultInserter.setNull(j + 1, java.sql.Types.TIMESTAMP); } } else if (metadataType[j].indexOf("TIME") != -1) { // TIME if (eltVal != null) { String timestr = eltVal.substring(0, eltVal.indexOf(".")); java.sql.Time time = java.sql.Time.valueOf(timestr); resultInserter.setTime(j + 1, time); } else { resultInserter.setNull(j + 1, java.sql.Types.TIME); } } else if (metadataType[j].indexOf("DATE") != -1) { // DATE if (eltVal != null) { java.sql.Date dt = java.sql.Date.valueOf(eltVal); resultInserter.setDate(j + 1, dt); } else { resultInserter.setNull(j + 1, java.sql.Types.DATE); } } else if (metadataType[j].indexOf("INTEGER") != -1) { // INTEGER if (eltVal != null) { int iValue = 0; iValue = Integer.parseInt(eltVal); resultInserter.setInt(j + 1, iValue); } else { resultInserter.setNull(j + 1, java.sql.Types.INTEGER); } } else if (metadataType[j].indexOf("DOUBLE") != -1) { // DOUBLE if (eltVal != null) { resultInserter.setDouble(j + 1, Double.parseDouble(eltVal)); } else { resultInserter.setNull(j + 1, java.sql.Types.DOUBLE); } } else if (eltVal != null) { // VARCHAR - default int rightParen = metadataType[j].indexOf(")"); int prec = Integer.parseInt(metadataType[j].substring(8, rightParen)); if (eltVal.length() > prec) { eltVal = eltVal.substring(0, prec); columnValues[j] = eltVal; } resultInserter.setString(j + 1, eltVal); } else { resultInserter.setNull(j + 1, java.sql.Types.VARCHAR); } } resultInserter.executeUpdate(); } } if (qr.isDone()) { bContinue = false; } else { boolean relogin = true; int retryCnt = 0; while (relogin) { try { qr = binding.queryMore(qr.getQueryLocator()); relogin = false; } catch (AxisFault a) { if (a.getFaultString().contains("Invalid Session ID") && (retryCnt < RETRY_CNT)) { relogin = true; retryCnt++; binding = (SoapBindingStub) FarragoUdrRuntime .getDataServerRuntimeSupport(binding); } else { throw a; } } } records = qr.getRecords(); if (qr.isDone()) { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievedAllRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } else { if (qr.getRecords() != null) { log.info(SfdcResource.instance().RetrievingRecordsMsg .str(Integer.toString(qr.getRecords().length), objName)); } } } } } } catch (AxisFault ae) { SQLException retryExcn = new SQLException(ae.getFaultString(), null, 460150); Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn); throw SfdcResource.instance().BindingCallException.ex(ae.getFaultString(), chainedEx); } catch (RemoteException re) { SQLException retryExcn = new SQLException(re.getMessage(), null, 460150); Exception chainedEx = FarragoResource.instance().RetryableFailure.ex(retryExcn); throw SfdcResource.instance().BindingCallException.ex(re.getMessage(), chainedEx); } }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Verblijfsobject verblijfsobject) throws DAOException { try {/* w w w. ja va2 s . co m*/ transactionTemplate.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus status) { jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("insert into bag_verblijfsobject (" + "bag_verblijfsobject_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel," + "verblijfsobject_geometrie," + "oppervlakte_verblijfsobject," + "verblijfsobject_status," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer," + "bag_nummeraanduiding_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, verblijfsobject.getIdentificatie()); ps.setInt(2, verblijfsobject.getAanduidingRecordInactief().ordinal()); ps.setLong(3, verblijfsobject.getAanduidingRecordCorrectie()); ps.setInt(4, verblijfsobject.getOfficieel().ordinal()); ps.setString(5, verblijfsobject.getVerblijfsobjectGeometrie()); ps.setInt(6, verblijfsobject.getOppervlakteVerblijfsobject()); ps.setInt(7, verblijfsobject.getVerblijfsobjectStatus().ordinal()); ps.setTimestamp(8, new Timestamp(verblijfsobject.getBegindatumTijdvakGeldigheid().getTime())); if (verblijfsobject.getEinddatumTijdvakGeldigheid() == null) ps.setNull(9, Types.TIMESTAMP); else ps.setTimestamp(9, new Timestamp(verblijfsobject.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(10, verblijfsobject.getInOnderzoek().ordinal()); ps.setDate(11, new Date(verblijfsobject.getDocumentdatum().getTime())); ps.setString(12, verblijfsobject.getDocumentnummer()); ps.setLong(13, verblijfsobject.getHoofdAdres()); return ps; } }); insertGebruikersdoelen(verblijfsobject); insertNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, verblijfsobject); insertGerelateerdePanden(verblijfsobject); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting verblijfsobject: " + verblijfsobject.getIdentificatie(), e); } }
From source file:swp.bibjsf.persistence.Data.java
/** * Reads CSV data from <code>input</code> and inserts them into * <code>table</code>./* w w w .j av a2 s. c o m*/ * * @param input * input stream with CSV data to be imported * @param table * name of table to be filled in * @throws DataSourceException * thrown in case of problems in adding the entries to the table * or in reading the values from the input */ public int importTable(InputStream input, String table) throws DataSourceException { final ColumnDescriptor[] columns = getColumns(table); final String[] expectedColumns = toLabels(columns); if (expectedColumns.length == 0) { // Pathological case. logger.debug("table with no columns"); return 0; } try { CSVReader csvReader = new CSVReader(input, DEFAULT_SEPARATOR, DEFAULT_QUOTE); if (!csvReader.hasColumns(expectedColumns)) { throw new DataSourceException(Messages.get("unexpectedHeader") + ": " + toString(expectedColumns)); } // logger.debug("attempt to read " + csvReader.numberOfRows() + // " data rows"); if (csvReader.numberOfRows() == 0) { // That is not necessarily an error, although it may appear // to be strange that somebody would want to upload an empty // file. Nevertheless, we do not treat this as an error, // because importTable might be called internally by restoring // a backed up database state that has empty tables. Furthermore // we do inform the user, how many entries were stored. logger.debug("CSV file is empty"); return 0; } Connection con = dataSource.getConnection(); try { con.setAutoCommit(false); // construct SQL statement StringBuilder fields = new StringBuilder("INSERT INTO " + table + " ("); StringBuilder placeholders = new StringBuilder("VALUES ("); // append fields by "field1, field2, ..., fieldN" // append placeholders by "?, ?, ..., ?" // where field1 is a non-static field // count the number of such fields in numberOfFields for (int i = 0; i < expectedColumns.length; i++) { fields.append(expectedColumns[i]); fields.append(","); placeholders.append("?,"); } // fields = "INSERT INTO <table> (field1, field2, ..., fieldN," // placeholders = "VALUES (?, ?, ..., ?," // remove last commas fields.deleteCharAt(fields.length() - 1); placeholders.deleteCharAt(placeholders.length() - 1); // close statement parts fields.append(")"); placeholders.append(")"); // fields = "INSERT INTO <table> (field1, field2, ..., fieldN)" // placeholders = "VALUES (?, ?, ..., ?)" final DateFormat formatter = new SimpleDateFormat(DATEFORMAT); // create prepared statement final String query = fields.toString() + placeholders.toString(); // query = "INSERT INTO <table> (field1, field2, ..., fieldN) VALUES (?, ?, ..., ?)" // The field names are read from an input file and, hence, represented // potentially tainted values. Yet, the fields must correspond to the // field names of <table>. Otherwise the SQL statement is invalid. Hence, // if any of the fields were tainted, the query would fail. Furthermore, // the values read from the input file are added via setX statements to this // prepared statement with place holders. Consequently, the query is save. PreparedStatement stmt = con.prepareStatement(query); try { for (int row = 0; row < csvReader.numberOfRows(); row++) { logger.debug("processing line " + (row + 1)); // collect values for (int col = 0; col < expectedColumns.length; col++) { String value; try { value = csvReader.get(expectedColumns[col], row); } catch (UnknownColumn e) { throw new DataSourceException(Messages.get("unexpectedColumn") + " '" + expectedColumns[col] + "' " + Messages.get("inLine") + " " + (row + 2) + ": " + e.getLocalizedMessage()); } if (value.isEmpty()) { stmt.setNull(col + 1, columns[col].type); } else if (columns[col].type == java.sql.Types.DATE) { Date date; try { date = formatter.parse(value); java.sql.Date sqlDate = new java.sql.Date(date.getTime()); stmt.setDate(col + 1, sqlDate); } catch (ParseException e) { throw new DataSourceException(Messages.get("unexpectedDateFormat") + " '" + value + "' " + Messages.get("inLine") + " " + (row + 2) + "; " + Messages.get("expected") + " " + DATEFORMAT + ": " + e.getLocalizedMessage()); } } else { stmt.setString(col + 1, value); } } // return value of execute() can be safely ignored (see // API documentation) try { stmt.execute(); } catch (SQLException e) { throw new DataSourceException( Messages.get("inLine") + " " + (row + 2) + ": " + e.getLocalizedMessage()); } } } finally { stmt.close(); } con.commit(); logger.debug("inserts are committed"); } catch (RuntimeException e) { // This exception handler is subsumed by the following on Exception. // It is there to please findbugs, which otherwise complains. logger.debug("inserts are rolled back"); con.rollback(); throw e; } catch (Exception e) { logger.debug("inserts are rolled back"); con.rollback(); throw e; } finally { con.close(); } return csvReader.numberOfRows(); } catch (CorruptInput e) { throw new DataSourceException(e.getLocalizedMessage()); } catch (SQLException e) { throw new DataSourceException(e.getLocalizedMessage()); } catch (Exception e) { throw new DataSourceException(e.getLocalizedMessage()); } }
From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java
/** * Upgrade database for Roller 2.1.0/*from w w w . ja va 2 s . co m*/ */ private void upgradeTo210(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/200-to-210-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } /* * For Roller 2.1.0 we are going to standardize some of the * weblog templates and make them less editable. To do this * we need to do a little surgery. * * The goal for this upgrade is to ensure that ALL weblogs now have * the required "Weblog" template as their default template. */ successMessage("Doing upgrade to 210 ..."); successMessage("Ensuring that all weblogs use the 'Weblog' template as their default page"); // this query will give us all websites that have modified their // default page to link to something other than "Weblog" PreparedStatement selectUpdateWeblogs = con .prepareStatement("select website.id,template,website.handle from website,webpage " + "where webpage.id = website.defaultpageid " + "and webpage.link != 'Weblog'"); PreparedStatement selectWeblogTemplate = con .prepareStatement("select id from webpage where websiteid = ? and link = 'Weblog'"); PreparedStatement updateWeblogTemplate = con .prepareStatement("update webpage set template = ? where id = ?"); // insert a new template for a website PreparedStatement insertWeblogTemplate = con.prepareStatement("insert into webpage" + "(id, name, description, link, websiteid, template, updatetime) " + "values(?,?,?,?,?,?,?)"); // update the default page for a website PreparedStatement updateDefaultPage = con .prepareStatement("update website set defaultpageid = ? " + "where id = ?"); String description = "This template is used to render the main " + "page of your weblog."; ResultSet websiteSet = selectUpdateWeblogs.executeQuery(); Date now = new Date(); while (websiteSet.next()) { String websiteid = websiteSet.getString(1); String template = websiteSet.getString(2); String handle = websiteSet.getString(3); successMessage("Processing website: " + handle); String defaultpageid = null; // it's possible that this weblog has a "Weblog" template, but just // isn't using it as their default. if so we need to fix that. selectWeblogTemplate.clearParameters(); selectWeblogTemplate.setString(1, websiteid); ResultSet weblogPageSet = selectWeblogTemplate.executeQuery(); if (weblogPageSet.next()) { // this person already has a "Weblog" template, so update it String id = weblogPageSet.getString(1); updateWeblogTemplate.clearParameters(); updateWeblogTemplate.setString(1, template); updateWeblogTemplate.setString(2, id); updateWeblogTemplate.executeUpdate(); // make sure and adjust what default page id we want to use defaultpageid = id; } else { // no "Weblog" template, so insert a new one insertWeblogTemplate.clearParameters(); insertWeblogTemplate.setString(1, websiteid + "q"); insertWeblogTemplate.setString(2, "Weblog"); insertWeblogTemplate.setString(3, description); insertWeblogTemplate.setString(4, "Weblog"); insertWeblogTemplate.setString(5, websiteid); insertWeblogTemplate.setString(6, template); insertWeblogTemplate.setDate(7, new java.sql.Date(now.getTime())); insertWeblogTemplate.executeUpdate(); // set the new default page id defaultpageid = websiteid + "q"; } // update defaultpageid value updateDefaultPage.clearParameters(); updateDefaultPage.setString(1, defaultpageid); updateDefaultPage.setString(2, websiteid); updateDefaultPage.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 210 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); log.error("Problem upgrading database to version 210", e); throw new StartupException("Problem upgrading database to version 210", e); } updateDatabaseVersion(con, 210); }
From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java
/** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusters(java.util.List, java.lang.String, java.util.Date) *//*from w ww . j a va 2 s. c om*/ @Override public void storeClusters(List<DomainCluster> clusters, String sensorname, Date logdate) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement domainsInsertStmt = null; PreparedStatement domainsSelectStmt = null; PreparedStatement clustersInsertStmt = null; PreparedStatement resolvedIPSInsertStmt = null; PreparedStatement clusterResolvedIPSInsertStmt = null; PreparedStatement clusterFeatureVectorsInsertStmt = null; try { con = this.getConnection(); domainsInsertStmt = con .prepareStatement("INSERT INTO domains_" + logDateTable + " VALUES(DEFAULT, ?, ?, ?)"); domainsSelectStmt = con .prepareStatement("SELECT domain_id FROM domains_" + logDateTable + " WHERE domain_name = ?"); clustersInsertStmt = con .prepareStatement("INSERT INTO clusters_" + logDateTable + " VALUES " + "(?, ?, ?, ?)"); resolvedIPSInsertStmt = con .prepareStatement("INSERT INTO resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, inet(?))"); clusterResolvedIPSInsertStmt = con.prepareStatement( "INSERT INTO cluster_resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, ?, inet(?))"); clusterFeatureVectorsInsertStmt = con.prepareStatement("INSERT INTO cluster_feature_vectors_" + logDateTable + "(cluster_id, sensor_name, log_date, network_cardinality, ip_diversity, " + "number_of_domains, ttl_per_domain, ip_growth_ratio, queries_per_domain, avg_last_growth_ratio_single_entry, " + "avg_last_growth_ratio_entries, avg_last_growth_prefix_ratio_entries, last_growth_ratio_cluster," + "last_growth_prefix_ratio_cluster) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int clusterId = 1; for (DomainCluster cluster : clusters) { for (CandidateFluxDomain candidateDomain : cluster.getCandidateDomains()) { String domainName = filterChars(candidateDomain.getDomainName()); String domainNameRev = DomainNameUtils.reverseDomainName(domainName); String secondLevelDomainName = DomainNameUtils.extractEffective2LD(domainName); String secondLevelDomainNameRev = null; if (secondLevelDomainName != null) { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(secondLevelDomainName); } else { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(domainName); } domainsInsertStmt.setString(1, domainNameRev); domainsInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); domainsInsertStmt.setString(3, secondLevelDomainNameRev); executePreparedStatementNoResult(con, domainsInsertStmt); domainsSelectStmt.setString(1, domainNameRev); ResultSet rs = this.executePreparedStatementWithResult(con, domainsSelectStmt); try { if (rs.next()) { int domainId = rs.getInt(1); clustersInsertStmt.setInt(1, clusterId); clustersInsertStmt.setInt(2, domainId); clustersInsertStmt.setString(3, sensorname); clustersInsertStmt.setDate(4, new java.sql.Date(logdate.getTime())); this.executePreparedStatementNoResult(con, clustersInsertStmt); for (InetAddress resolvedIP : candidateDomain.getIps()) { resolvedIPSInsertStmt.setInt(1, domainId); resolvedIPSInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); resolvedIPSInsertStmt.setString(3, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, resolvedIPSInsertStmt); } } } catch (SQLException ex) { if (log.isErrorEnabled()) { log.error("", ex); } } finally { rs.close(); } } /*String nickname = getNicknames((List<String>)cluster.getDomains()); insertQuery = "INSERT INTO cluster_nicknames_"+ logDateTable +" VALUES" + "("+clusterId+", '"+sensorname+"', '"+logDateStr+"', '"+nickname+"')"; performInsertQuery(insertQuery, clusterNicknamesCreateQuery);*/ for (InetAddress resolvedIP : cluster.getIps()) { clusterResolvedIPSInsertStmt.setInt(1, clusterId); clusterResolvedIPSInsertStmt.setString(2, sensorname); clusterResolvedIPSInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterResolvedIPSInsertStmt.setString(4, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, clusterResolvedIPSInsertStmt); } clusterFeatureVectorsInsertStmt.setInt(1, clusterId); clusterFeatureVectorsInsertStmt.setString(2, sensorname); clusterFeatureVectorsInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterFeatureVectorsInsertStmt.setInt(4, cluster.getIps().size()); clusterFeatureVectorsInsertStmt.setDouble(5, cluster.getIpDiversity()); clusterFeatureVectorsInsertStmt.setInt(6, cluster.getDomains().size()); clusterFeatureVectorsInsertStmt.setDouble(7, cluster.getAvgTTLPerDomain()); clusterFeatureVectorsInsertStmt.setDouble(8, cluster.getIpGrowthRatio()); clusterFeatureVectorsInsertStmt.setDouble(9, cluster.getQueriesPerDomain()); Double temp = cluster.getAvgLastGrowthRatioSingleEntry(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(10, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(10, temp); } temp = cluster.getAvgLastGrowthRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(11, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(11, temp); } temp = cluster.getAvgLastGrowthPrefixRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(12, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(12, temp); } temp = cluster.getLastGrowthRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(13, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(13, temp); } temp = cluster.getLastGrowthPrefixRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(14, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(14, temp); } this.executePreparedStatementNoResult(con, clusterFeatureVectorsInsertStmt); clusterId++; } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } } finally { try { if (domainsInsertStmt != null && !domainsInsertStmt.isClosed()) { domainsInsertStmt.close(); } if (domainsSelectStmt != null && !domainsSelectStmt.isClosed()) { domainsSelectStmt.close(); } if (clustersInsertStmt != null && !clustersInsertStmt.isClosed()) { clustersInsertStmt.close(); } if (resolvedIPSInsertStmt != null && !resolvedIPSInsertStmt.isClosed()) { resolvedIPSInsertStmt.close(); } if (clusterResolvedIPSInsertStmt != null && !clusterResolvedIPSInsertStmt.isClosed()) { clusterResolvedIPSInsertStmt.close(); } if (clusterFeatureVectorsInsertStmt != null && !clusterFeatureVectorsInsertStmt.isClosed()) { clusterFeatureVectorsInsertStmt.close(); } if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "INSERT" on the given table and and table prefix for * the supplied attributeValues/*from ww w . jav a2 s . co m*/ * * @param table * @param attributeValues * @return * @throws SQLException */ public boolean insertRow(String table, Map attributeValues) throws SQLException { PreparedStatement prepStmt = null; Object attribute = null; boolean success = false; try { prepStmt = sqlConnection_.prepareStatement(getSQLTableConstants().getInsertStatementSQL(table)); for (int i = 0; i < attributeValues.size(); i++) { attribute = attributeValues.get("" + (i + 1)); // If null, we are unable to determine the SQL param type, // so String is assumed by default. if (attribute == null) { prepStmt.setString(i + 1, null); } else if (attribute instanceof String) { prepStmt.setString(i + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(i + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(i + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(i + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(i + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(i + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(i + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(i + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(i + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(i + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(i + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(i + 1, (Timestamp) attribute); } } success = prepStmt.execute(); } finally { prepStmt.close(); } return success; }
From source file:org.infoglue.cms.controllers.kernel.impl.simple.InstallationController.java
/** * This method issues special blob-inserts command to the db. * I had to build my own adoption of sql to make this feature. */// ww w . jav a 2 s .co m protected void issueSpecialBlobCommand(Connection conn, String originalSql) throws Exception { String sql = originalSql; //Logger.logInfo("SpecialBlob Command:" + sql); try { String valuesPart = sql.substring(sql.indexOf("VALUES") + 6).trim(); sql = sql.substring(0, sql.indexOf("VALUES") + 6); //logger.info("sql:" + sql); //logger.info("valuesPart:" + valuesPart); String tableName = null; int blobColumn = 0; List columns = null; List columnValues = null; StringTokenizer st = new StringTokenizer(sql, " "); int i = 0; while (st.hasMoreTokens()) { String part = st.nextToken(); //Logger.logInfo("Part: " + part); if (i == 1) blobColumn = new Integer(part).intValue(); if (i == 4) tableName = part; if (i == 5) { columns = parseColumns(part); } i++; } columnValues = parseValues(valuesPart); String columnsString = ""; String valuesString = ""; Iterator columnsIterator = columns.iterator(); while (columnsIterator.hasNext()) { columnsString += (columnsString.equals("")) ? (String) columnsIterator.next() : "," + columnsIterator.next(); valuesString += (valuesString.equals("")) ? "?" : ",?"; } sql = "INSERT INTO " + tableName + "(" + columnsString + ") VALUES (" + valuesString + ")"; PreparedStatement ps = conn.prepareStatement(sql); int index = 1; int loopCount = 0; Iterator columnValuesIterator = columnsIterator = columns.iterator(); while (columnsIterator.hasNext()) { columnsIterator.next(); String value = (String) columnValues.get(loopCount); if (index == 1 || value.indexOf("'") == -1) { ps.setInt(index, new Integer(value).intValue()); } else if (index == blobColumn) { //Logger.logInfo("value:" + value); value = value.substring(1, value.length() - 1); //Logger.logInfo("value:" + value); if (value.indexOf("assetBlob:") > -1) { String fileName = value.substring(10); FileInputStream fis = new FileInputStream(fileName); BLOB bl = BLOB.createTemporary(conn, true, BLOB.DURATION_CALL); bl.open(BLOB.MODE_READWRITE); BufferedOutputStream out = new BufferedOutputStream(bl.getBinaryOutputStream()); byte[] buffer = new byte[1024]; int len; while ((len = fis.read(buffer)) >= 0) out.write(buffer, 0, len); out.flush(); fis.close(); out.close(); ps.setBlob(index, bl); } else { CLOB cl = CLOB.createTemporary(conn, true, CLOB.DURATION_CALL); cl.putString(1, value); ps.setClob(index, cl); } } else if (value.indexOf("date:") > -1) { value = value.substring(6); Date date = parseDate(value, "yyyy-MM-dd HH:mm:ss"); ps.setDate(index, new java.sql.Date(date.getTime())); } else { //Logger.logInfo("value:" + value); value = value.substring(1, value.length() - 1); //Logger.logInfo("value:" + value); ps.setString(index, value); } index++; loopCount++; } ps.executeUpdate(); } catch (Exception ex) { logger.error("Command failed: " + ex.getMessage()); logger.error("SQL: " + originalSql); throw ex; } }
From source file:org.gbif.portal.dao.impl.jdbc.RawOccurrenceRecordDAOImpl.java
/** * @see org.gbif.portal.dao.RawOccurrenceRecordDAO#create(org.gbif.portal.model.RawOccurrenceRecord) *//*from w w w. ja va2 s.co 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()); ps.setString(7, StringUtils.trimToNull(rawOccurrenceRecord.getScientificName())); ps.setString(8, StringUtils.trimToNull(rawOccurrenceRecord.getAuthor())); ps.setString(9, StringUtils.trimToNull(rawOccurrenceRecord.getRank())); ps.setString(10, StringUtils.trimToNull(rawOccurrenceRecord.getKingdom())); ps.setString(11, StringUtils.trimToNull(rawOccurrenceRecord.getPhylum())); ps.setString(12, StringUtils.trimToNull(rawOccurrenceRecord.getKlass())); ps.setString(13, StringUtils.trimToNull(rawOccurrenceRecord.getOrder())); ps.setString(14, StringUtils.trimToNull(rawOccurrenceRecord.getFamily())); ps.setString(15, StringUtils.trimToNull(rawOccurrenceRecord.getGenus())); ps.setString(16, StringUtils.trimToNull(rawOccurrenceRecord.getSpecies())); ps.setString(17, StringUtils.trimToNull(rawOccurrenceRecord.getSubspecies())); ps.setString(18, StringUtils.trimToNull(rawOccurrenceRecord.getLatitude())); ps.setString(19, StringUtils.trimToNull(rawOccurrenceRecord.getLongitude())); ps.setString(20, StringUtils.trimToNull(rawOccurrenceRecord.getLatLongPrecision())); ps.setString(21, StringUtils.trimToNull(rawOccurrenceRecord.getMinAltitude())); ps.setString(22, StringUtils.trimToNull(rawOccurrenceRecord.getMaxAltitude())); ps.setString(23, StringUtils.trimToNull(rawOccurrenceRecord.getAltitudePrecision())); ps.setString(24, StringUtils.trimToNull(rawOccurrenceRecord.getMinDepth())); ps.setString(25, StringUtils.trimToNull(rawOccurrenceRecord.getMaxDepth())); ps.setString(26, StringUtils.trimToNull(rawOccurrenceRecord.getDepthPrecision())); ps.setString(27, StringUtils.trimToNull(rawOccurrenceRecord.getContinentOrOcean())); ps.setString(28, StringUtils.trimToNull(rawOccurrenceRecord.getCountry())); ps.setString(29, StringUtils.trimToNull(rawOccurrenceRecord.getStateOrProvince())); ps.setString(30, StringUtils.trimToNull(rawOccurrenceRecord.getCounty())); ps.setString(31, StringUtils.trimToNull(rawOccurrenceRecord.getCollectorName())); ps.setString(32, StringUtils.trimToNull(rawOccurrenceRecord.getLocality())); ps.setString(33, StringUtils.trimToNull(rawOccurrenceRecord.getYear())); ps.setString(34, StringUtils.trimToNull(rawOccurrenceRecord.getMonth())); ps.setString(35, StringUtils.trimToNull(rawOccurrenceRecord.getDay())); ps.setString(36, StringUtils.trimToNull(rawOccurrenceRecord.getBasisOfRecord())); ps.setString(37, StringUtils.trimToNull(rawOccurrenceRecord.getIdentifierName())); ps.setDate(38, createSQLDate(rawOccurrenceRecord.getDateIdentified())); ps.setString(39, StringUtils.trimToNull(rawOccurrenceRecord.getUnitQualifier())); ps.setTimestamp(40, createTime); ps.setTimestamp(41, createTime); return ps; } }, keyHolder); rawOccurrenceRecord.setId(keyHolder.getKey().longValue()); return keyHolder.getKey().longValue(); }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insertNummeraanduidingen(final List<Nummeraanduiding> nummeraanduidingen) throws DAOException { try {/*from ww w .java2 s .c om*/ jdbcTemplate.batchUpdate("insert into bag_nummeraanduiding (" + "bag_nummeraanduiding_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "huisnummer," + "officieel," + "huisletter," + "huisnummertoevoeging," + "postcode," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "type_adresseerbaar_object," + "bron_documentdatum," + "bron_documentnummer," + "nummeraanduiding_status," + "bag_woonplaats_id," + "bag_openbare_ruimte_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setLong(1, nummeraanduidingen.get(i).getIdentificatie()); ps.setInt(2, nummeraanduidingen.get(i).getAanduidingRecordInactief().ordinal()); ps.setLong(3, nummeraanduidingen.get(i).getAanduidingRecordCorrectie()); ps.setInt(4, nummeraanduidingen.get(i).getHuisnummer()); ps.setInt(5, nummeraanduidingen.get(i).getOfficieel().ordinal()); if (nummeraanduidingen.get(i).getHuisletter() == null) ps.setNull(6, Types.VARCHAR); else ps.setString(6, nummeraanduidingen.get(i).getHuisletter()); if (nummeraanduidingen.get(i).getHuisnummertoevoeging() == null) ps.setNull(7, Types.VARCHAR); else ps.setString(7, nummeraanduidingen.get(i).getHuisnummertoevoeging()); if (nummeraanduidingen.get(i).getPostcode() == null) ps.setNull(8, Types.VARCHAR); else ps.setString(8, nummeraanduidingen.get(i).getPostcode()); ps.setTimestamp(9, new Timestamp( nummeraanduidingen.get(i).getBegindatumTijdvakGeldigheid().getTime())); if (nummeraanduidingen.get(i).getEinddatumTijdvakGeldigheid() == null) ps.setNull(10, Types.TIMESTAMP); else ps.setTimestamp(10, new Timestamp( nummeraanduidingen.get(i).getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(11, nummeraanduidingen.get(i).getInOnderzoek().ordinal()); ps.setInt(12, nummeraanduidingen.get(i).getTypeAdresseerbaarObject().ordinal()); ps.setDate(13, new Date(nummeraanduidingen.get(i).getDocumentdatum().getTime())); ps.setString(14, nummeraanduidingen.get(i).getDocumentnummer()); ps.setInt(15, nummeraanduidingen.get(i).getNummeraanduidingStatus().ordinal()); if (nummeraanduidingen.get(i).getGerelateerdeWoonplaats() == null) ps.setNull(16, Types.INTEGER); else ps.setLong(16, nummeraanduidingen.get(i).getGerelateerdeWoonplaats()); ps.setLong(17, nummeraanduidingen.get(i).getGerelateerdeOpenbareRuimte()); } @Override public int getBatchSize() { return nummeraanduidingen.size(); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting nummeraanduidingen", e); } }