List of usage examples for java.sql PreparedStatement setFloat
void setFloat(int parameterIndex, float x) throws SQLException;
float value. From source file:uk.ac.ebi.orchem.search.SimilaritySearch.java
/** * Performs a similarity search between a query molecule and the orchem fingerprint table. * * @param queryFp fingerprint of the query molecule * @param _cutOff tanimoto score below which to stop searching * @param _topN top N results after which to stop searching * @param debugYN Y or N to debug output back * @param idsOnlyYN Y or N to indicate to just return IDs of results (faster) * @param extraWhereClause option to include an extra SQL where clause refering to the base compound table * @return array of {@link uk.ac.ebi.orchem.bean.OrChemCompound compounds} * @throws Exception//from ww w . j a v a 2 s . c o m */ private static oracle.sql.ARRAY search(BitSet queryFp, Float _cutOff, Integer _topN, String debugYN, String idsOnlyYN, String extraWhereClause) throws Exception { /* * The comment block below describes the search algorithm. From: "Bounds and Algorithms for Fast Exact Searches of Chemical Fingerprints in Linear and Sub-Linear Time" S.Joshua Swamidass and Pierre Baldi http://dx.doi.org/10.1021/ci600358f Top K Hits ---------- We can search for the top K hits by starting from the maximum (where A=B), and exploring discrete possible values of B right and left of the maximum. More precisely, for binary fingerprints, we first index the molecules in the database by their fingerprint "bit count" to enable efficient referencing of a particular bit count bin. Next, with respect to a particular query, we calculate the bound on the similarity for every bit count in the database. Then we sort these bit counts by their associated bound and iterate over the molecules in the database, in order of decreasing bound. As we iterate, we calculate the similarity between the query and the database molecule and use a heap to efficiently track the top hits. The algorithm terminates when "the lowest similarity value in the heap is greater than the bound associated with the current database bin" Algorithm 1 Top K Search Require: database of fingerprints binned by bit count Bs Ensure: hits contains top K hits which satisfy SIMILARITY( ) > T 1: hits <- MINHEAP() 2: bounds <- LIST() 3: for all B in database do //iterate over bins 4: tuple <- TUPLE(BOUND(A,B),B) 5: LISTAPPEND(bounds, tuple) 6: end for 7: QUICKSORT(bounds) //NOTE: the length of bounds is constant 8: for all bound, B in bounds do //iterate in order of decreasing bound 9: if bound < T then 10: break //threshold stopping condition 11: end if 12: if K HEAPSIZE(hits) and bound < MINSIMILARITY(hits) then 13: break //top-K stopping condition 14: end if 15: for all in database[B] do 16: S=SIMILARITY( ) 17: tuple <- TUPLE(S, ) 18: if S T then 19: continue //ignore this and continue to next 20: else if LENGTH(hits)< K then 21: HEAPPUSH(hits, tuple) 22: else if S > MINSIMILARITY(hits) then 23: HEAPPOPMIN(hits) 24: HEAPPUSH(hits,tuple) 25: end if 26: end for 27: end for 28: return hits */ boolean debugging = false; if (debugYN.toLowerCase().equals("y")) debugging = true; debug("started", debugging); /********************************************************************** * Similarity search algorithm section * * * **********************************************************************/ Comparator heapComparator = new SimHeapElementTanimComparator(); PriorityBuffer heap = null; OracleConnection conn = null; PreparedStatement pstmtFp = null; PreparedStatement pstmLookup = null; String query = " select bit_count, id, fp from orchem_fingprint_simsearch s where bit_count = ? "; float cutOff = _cutOff.floatValue(); int topN = -1; if (_topN == null) { debug("No topN breakout specified.. searching until lower bound reached", debugging); } else { topN = _topN.intValue(); debug("topN is " + topN + ", result set size limited.", debugging); } try { conn = (OracleConnection) new OracleDriver().defaultConnection(); String compoundTableName = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_TABLE, conn); String compoundTablePkColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_PK, conn); String compoundTableMolfileColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_MOL, conn); if (extraWhereClause != null) { query = " select s.bit_count, s.id, s.fp from " + " orchem_fingprint_simsearch s , " + compoundTableName + " c " + " where s.bit_count = ? " + " and s.id = c." + compoundTablePkColumn + " " + " and " + extraWhereClause; debug("QUERY is " + query, debugging); } float queryBitCount = queryFp.cardinality(); byte[] queryBytes = Utils.toByteArray(queryFp, extFpSize); int queryByteArrLen = queryBytes.length; float lowBucketNum = queryBitCount - 1; float highBucketNum = queryBitCount + 1; float currBucketNum = queryBitCount; pstmtFp = conn.prepareStatement(query); pstmtFp.setFetchSize(250); ResultSet resFp = null; boolean done = false; byte[] dbByteArray = null; float tanimotoCoeff = 0f; heap = new PriorityBuffer(true, heapComparator); int bucksSearched = 0; int loopCount = 0; while (!done) { debug("bucket is " + currBucketNum, debugging); loopCount++; pstmtFp.setFloat(1, currBucketNum); bucksSearched++; resFp = pstmtFp.executeQuery(); float bound = 0f; if (currBucketNum < queryBitCount) bound = currBucketNum / queryBitCount; else bound = queryBitCount / currBucketNum; /* Algorithm step 9..11 Here we can break out because the tanimoto score is becoming to low */ if (bound < cutOff) { debug("bound < cutOff, done", debugging); done = true; } if (!done) { //Algorithm 15-26 while (resFp.next()) { dbByteArray = resFp.getBytes("fp"); tanimotoCoeff = calcTanimoto(queryBytes, queryByteArrLen, dbByteArray, queryBitCount, currBucketNum); if (tanimotoCoeff >= cutOff) { SimHeapElement elm = new SimHeapElement(); elm.setID(resFp.getString("id")); elm.setTanimotoCoeff(new Float(tanimotoCoeff)); if (heap.size() < topN || topN == -1) { heap.add(elm); debug("add elem " + elm.getID(), debugging); } else if (tanimotoCoeff > ((SimHeapElement) (heap.get())).getTanimotoCoeff() .floatValue()) { heap.remove(); heap.add(elm); debug("remove + add elem " + elm.getID(), debugging); } } } resFp.close(); /* Algorithm 12-14: * When top N hits is reached, and the lowest score of the * hits is greater than the current bucket bound, stop. * If not, the next bucket may contain a better score, so go on. */ if (topN != -1 && heap.size() >= topN && ((SimHeapElement) (heap.get())).getTanimotoCoeff().floatValue() > bound) { done = true; debug("topN reached, done", debugging); } else { // calculate new currBucket float up = queryBitCount / highBucketNum; float down = lowBucketNum / queryBitCount; if (up > down) { currBucketNum = highBucketNum; highBucketNum++; } else { currBucketNum = lowBucketNum; lowBucketNum--; } if (lowBucketNum < 1 && highBucketNum > extFpSize) done = true; } } } debug("searched bit_count buckets: " + loopCount, debugging); /******************************************************************** * Search completed. * * * * Next section is just looking up the compounds by ID and * * returning the results, sorted by Tanimoto coefficient * * * *******************************************************************/ String lookupCompoundQuery = " select " + compoundTableMolfileColumn + " from " + " " + compoundTableName + " where " + " " + compoundTablePkColumn + " =?"; pstmLookup = conn.prepareStatement(lookupCompoundQuery); List compounds = new ArrayList(); while (heap.size() != 0) { SimHeapElement bElm = (SimHeapElement) heap.remove(); if (idsOnlyYN.equals("N")) { // return structure to user pstmLookup.setString(1, bElm.getID()); ResultSet resLookup = pstmLookup.executeQuery(); if (resLookup.next()) { OrChemCompound c = new OrChemCompound(); c.setId(bElm.getID()); c.setScore(bElm.getTanimotoCoeff().floatValue()); c.setMolFileClob(resLookup.getClob(compoundTableMolfileColumn)); compounds.add(c); } resLookup.close(); } else { // only return ID and score to user OrChemCompound c = new OrChemCompound(); c.setId(bElm.getID()); c.setScore(bElm.getTanimotoCoeff().floatValue()); compounds.add(c); } } pstmLookup.close(); long befSort = System.currentTimeMillis(); Collections.sort(compounds, new OrChemCompoundTanimComparator()); debug("sorting time (ms) " + (System.currentTimeMillis() - befSort), debugging); OrChemCompound[] output = new OrChemCompound[compounds.size()]; for (int i = 0; i < compounds.size(); i++) { output[i] = (OrChemCompound) (compounds.get(i)); } ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("ORCHEM_COMPOUND_LIST", conn); debug("#compounds in result list : " + compounds.size(), debugging); debug("ended", debugging); return new ARRAY(arrayDescriptor, conn, output); } catch (Exception ex) { ex.printStackTrace(); throw (ex); } finally { if (pstmLookup != null) pstmLookup.close(); if (pstmtFp != null) pstmtFp.close(); if (conn != null) conn.close(); } }
From source file:at.alladin.rmbt.statisticServer.StatisticsResource.java
private static PreparedStatement selectDevices(final Connection conn, final boolean group, final float quantile, final int durationDays, final double accuracy, final String country, final boolean useMobileProvider, final String where, final int maxDevices) throws SQLException { PreparedStatement ps; String sql = String.format("SELECT" + (group ? " COALESCE(adm.fullname, t.model) model," : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping" + " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " WHERE %s" + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND time > NOW() - CAST(? AS INTERVAL)" + (useMobileProvider ? " AND t.mobile_provider_id IS NOT NULL" : "") + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY COALESCE(adm.fullname, t.model) HAVING count(t.uid) > 10" : "") + " ORDER BY count DESC" + " LIMIT %d", where, maxDevices); if (country != null) { sql = String.format("SELECT" + (group ? " COALESCE(adm.fullname, t.model) model," : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping" + " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + (useMobileProvider ? " LEFT JOIN mccmnc2name p ON p.uid = t.mobile_sim_id" : "") + " WHERE %s" + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND time > NOW() - CAST(? AS INTERVAL)" + " AND " + (useMobileProvider// w w w .j a v a 2 s. co m ? "p.country = ? AND ((t.country_location IS NULL OR t.country_location = ?) AND (NOT t.roaming_type = 2))" : "t.country_geoip = ? ") + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY COALESCE(adm.fullname, t.model) HAVING count(t.uid) > 10" : "") + " ORDER BY count DESC" + " LIMIT %d", where, maxDevices); } ps = conn.prepareStatement(sql); System.out.println(ps); int i = 1; for (int j = 0; j < 2; j++) ps.setFloat(i++, quantile); ps.setFloat(i++, 1 - quantile); // inverse for ping ps.setString(i++, String.format("%d days", durationDays)); if (country != null) { if (useMobileProvider) { ps.setString(i++, country.toLowerCase()); //mccmnc2name.country ps.setString(i++, country.toUpperCase()); //country_location } else { ps.setString(i++, country.toUpperCase()); } } if (accuracy > 0) { ps.setDouble(i++, accuracy); } return ps; }
From source file:com.draagon.meta.manager.db.ObjectManagerDB.java
protected PreparedStatement getPreparedStatement(Connection c, String query, Collection<?> args) throws MetaException, SQLException { String sql = query; // (String) mOQLCache.get( query ); // If it's not in the cache, then parse it and put it there //if ( sql == null ) //{/*from www. j a va 2s . c o m*/ //Map<String,MetaClass> m = getMetaClassMap( query ); //if ( m.size() > 0 ) { // sql = convertToSQL( query, m ); //} //else sql = query; //mOQLCache.put( query, sql ); //} PreparedStatement s = c.prepareStatement(sql); if (args != null) { int i = 1; for (Object o : args) { if (o instanceof Boolean) { s.setBoolean(i, (Boolean) o); } else if (o instanceof Byte) { s.setByte(i, (Byte) o); } else if (o instanceof Short) { s.setShort(i, (Short) o); } else if (o instanceof Integer) { s.setInt(i, (Integer) o); } else if (o instanceof Long) { s.setLong(i, (Long) o); } else if (o instanceof Float) { s.setFloat(i, (Float) o); } else if (o instanceof Double) { s.setDouble(i, (Double) o); } else if (o instanceof Date) { s.setTimestamp(i, new Timestamp(((Date) o).getTime())); } else if (o == null) { s.setString(i, null); } else { s.setString(i, o.toString()); } // Increment the i i++; } } return s; }
From source file:org.apache.phoenix.query.BaseTest.java
protected static void initATableValues(String tenantId, byte[][] splits, Date date, Long ts, String url) throws Exception { if (ts == null) { ensureTableCreated(url, ATABLE_NAME, splits); } else {/*from w w w . ja v a2 s.co m*/ ensureTableCreated(url, ATABLE_NAME, splits, ts - 5); } Properties props = new Properties(); if (ts != null) { props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts - 3)); } Connection conn = DriverManager.getConnection(url, props); try { // Insert all rows at ts PreparedStatement stmt = conn.prepareStatement("upsert into " + "ATABLE(" + " ORGANIZATION_ID, " + " ENTITY_ID, " + " A_STRING, " + " B_STRING, " + " A_INTEGER, " + " A_DATE, " + " X_DECIMAL, " + " X_LONG, " + " X_INTEGER," + " Y_INTEGER," + " A_BYTE," + " A_SHORT," + " A_FLOAT," + " A_DOUBLE," + " A_UNSIGNED_FLOAT," + " A_UNSIGNED_DOUBLE)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId); stmt.setString(2, ROW1); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 1); stmt.setDate(6, date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 1); stmt.setShort(12, (short) 128); stmt.setFloat(13, 0.01f); stmt.setDouble(14, 0.0001); stmt.setFloat(15, 0.01f); stmt.setDouble(16, 0.0001); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW2); stmt.setString(3, A_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 2); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 2); stmt.setShort(12, (short) 129); stmt.setFloat(13, 0.02f); stmt.setDouble(14, 0.0002); stmt.setFloat(15, 0.02f); stmt.setDouble(16, 0.0002); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW3); stmt.setString(3, A_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 3); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 3); stmt.setShort(12, (short) 130); stmt.setFloat(13, 0.03f); stmt.setDouble(14, 0.0003); stmt.setFloat(15, 0.03f); stmt.setDouble(16, 0.0003); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW4); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 4); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 4); stmt.setShort(12, (short) 131); stmt.setFloat(13, 0.04f); stmt.setDouble(14, 0.0004); stmt.setFloat(15, 0.04f); stmt.setDouble(16, 0.0004); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW5); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 5); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 5); stmt.setShort(12, (short) 132); stmt.setFloat(13, 0.05f); stmt.setDouble(14, 0.0005); stmt.setFloat(15, 0.05f); stmt.setDouble(16, 0.0005); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW6); stmt.setString(3, B_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 6); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 6); stmt.setShort(12, (short) 133); stmt.setFloat(13, 0.06f); stmt.setDouble(14, 0.0006); stmt.setFloat(15, 0.06f); stmt.setDouble(16, 0.0006); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW7); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW8); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 8); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.9)); long l = Integer.MIN_VALUE - 1L; assert (l < Integer.MIN_VALUE); stmt.setLong(8, l); stmt.setInt(9, 4); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 8); stmt.setShort(12, (short) 135); stmt.setFloat(13, 0.08f); stmt.setDouble(14, 0.0008); stmt.setFloat(15, 0.08f); stmt.setDouble(16, 0.0008); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW9); stmt.setString(3, C_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 9); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.3)); l = Integer.MAX_VALUE + 1L; assert (l > Integer.MAX_VALUE); stmt.setLong(8, l); stmt.setInt(9, 3); stmt.setInt(10, 300); stmt.setByte(11, (byte) 9); stmt.setShort(12, (short) 0); stmt.setFloat(13, 0.09f); stmt.setDouble(14, 0.0009); stmt.setFloat(15, 0.09f); stmt.setDouble(16, 0.0009); stmt.execute(); conn.commit(); } finally { conn.close(); } }
From source file:migration.ProjektMigration.java
/** * Creates the nutzung.//ww w.j ava 2 s . c o m */ public void createNutzung() { String load_sql; Statement load_stmt; ResultSet load_rs; String store_sql; PreparedStatement store_prepstmt; final ResultSet store_rs; try { load_sql = "SELECT Zugriffe, Zeitraum, Nutzungsjahr, Rechnungsbetrag, Titelnummer FROM Nutzungstabelle"; load_stmt = this.leg_con.createStatement(); store_sql = "insert into Nutzung (journal_id, nutzungsjahr, rechnungsbetrag, zeitraum, zugriffe) values (?, ?, ?, ?, ?)"; store_prepstmt = this.tgt_con.prepareStatement(store_sql); // evtl. // brauchen // wir // was // in // Richtung: // Statement.RETURN_GENERATED_KEYS // logger.info("Lese von Nutzungstabelle"); load_stmt.execute(load_sql); load_rs = load_stmt.getResultSet(); // logger.info("Schreibe nach Nutzung"); while (load_rs.next()) { final int titelnummer = load_rs.getInt("Titelnummer"); final int journalID = this.help.getIdFromIntArray(this.help.getJournals(), titelnummer); // System.out.println("Titelnummer: " + titelnummer + // " JournalID " + journalID); if ((titelnummer > 0) && (journalID > 0)) { store_prepstmt.setLong(1, journalID); store_prepstmt.setLong(2, load_rs.getLong("Nutzungsjahr")); store_prepstmt.setFloat(3, load_rs.getFloat("Rechnungsbetrag")); store_prepstmt.setLong(4, load_rs.getLong("Zeitraum")); store_prepstmt.setLong(5, load_rs.getLong("Zugriffe")); store_prepstmt.executeUpdate(); } } } catch (final SQLException e) { e.printStackTrace(); // To change body of catch statement use File | // Settings | File Templates. } // insert into Interesse (besteller_bestellerId, interesse, journal_id) // values (?, ?, ?) // insert into Nutzung (journal_id, nutzungsjahr, rechnungsbetrag, // zeitraum, zugriffe) values (?, ?, ?, ?, ?) // insert into Rechnung (betrag, bezugsform, bezugsjahr, // exemplar_exemplarId, sigel_sigelId) values (?, ?, ?, ?, ?) }
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param preparedState//from w ww . j a v a2s . c o m * @param param * @throws SQLException */ private void setParam(PreparedStatement preparedState, int index, PrepareSqlRequest.StateParam param) throws SQLException { boolean isNull = (null == param.getValue()); Class<?> clazz; try { clazz = Class.forName(param.getClassName()); } catch (ClassNotFoundException e) { throw new InternalErrorException(e); } Rep rep = Rep.of(clazz); switch (rep) { case PRIMITIVE_CHAR: case CHARACTER: case STRING: preparedState.setString(index, isNull ? null : String.valueOf(param.getValue())); break; case PRIMITIVE_INT: case INTEGER: preparedState.setInt(index, isNull ? 0 : Integer.valueOf(param.getValue())); break; case PRIMITIVE_SHORT: case SHORT: preparedState.setShort(index, isNull ? 0 : Short.valueOf(param.getValue())); break; case PRIMITIVE_LONG: case LONG: preparedState.setLong(index, isNull ? 0 : Long.valueOf(param.getValue())); break; case PRIMITIVE_FLOAT: case FLOAT: preparedState.setFloat(index, isNull ? 0 : Float.valueOf(param.getValue())); break; case PRIMITIVE_DOUBLE: case DOUBLE: preparedState.setDouble(index, isNull ? 0 : Double.valueOf(param.getValue())); break; case PRIMITIVE_BOOLEAN: case BOOLEAN: preparedState.setBoolean(index, !isNull && Boolean.parseBoolean(param.getValue())); break; case PRIMITIVE_BYTE: case BYTE: preparedState.setByte(index, isNull ? 0 : Byte.valueOf(param.getValue())); break; case JAVA_UTIL_DATE: case JAVA_SQL_DATE: preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue())); break; case JAVA_SQL_TIME: preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue())); break; case JAVA_SQL_TIMESTAMP: preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue())); break; default: preparedState.setObject(index, isNull ? null : param.getValue()); } }
From source file:edu.jhuapl.openessence.datasource.jdbc.JdbcOeDataSource.java
protected void setArguments(List<Object> arguments, PreparedStatement pStmt) throws SQLException { int argCount = 1; for (Object o : arguments) { // TODO NEED TO ADDRESS THE USE CASES FOR THIS null...POKUAM1...what if not nullable column? if (o == null) { pStmt.setObject(argCount, null); } else if (o instanceof java.sql.Timestamp) { pStmt.setTimestamp(argCount, (java.sql.Timestamp) o); } else if (o instanceof java.util.Date) { pStmt.setTimestamp(argCount, new java.sql.Timestamp(((java.util.Date) o).getTime())); } else if (o instanceof Integer) { pStmt.setInt(argCount, (Integer) o); } else if (o instanceof Long) { pStmt.setLong(argCount, (Long) o); } else if (o instanceof Float) { pStmt.setFloat(argCount, (Float) o); } else if (o instanceof Double) { pStmt.setDouble(argCount, (Double) o); } else if (o instanceof String) { pStmt.setString(argCount, (String) o); } else if (o instanceof Boolean) { pStmt.setBoolean(argCount, (Boolean) o); } else {/*from ww w . j a v a 2s . co m*/ throw new AssertionError("Unexpected object " + o + " " + o.getClass()); } argCount += 1; } }
From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java
public void putJob(JobObject stObj) throws SQLException, StorageException { String query = "MERGE INTO " + "JOBS(job_id, layer_name, state, time_spent, time_remaining, tiles_done, " + "tiles_total, failed_tile_count, bounds, gridset_id, srs, thread_count, " + "zoom_start, zoom_stop, format, job_type, throughput, max_throughput, " + "priority, schedule, run_once, spawned_by, filter_update, parameters, " + "time_first_start, time_latest_start, time_finish) " + "KEY(job_id) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; final Connection conn = getConnection(); try {/* w w w.j a v a2s . c om*/ Long insertId; PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); try { if (stObj.getJobId() == -1) { prep.setNull(1, java.sql.Types.BIGINT); } else { prep.setLong(1, stObj.getJobId()); } prep.setString(2, stObj.getLayerName()); prep.setString(3, stObj.getState().name()); prep.setLong(4, stObj.getTimeSpent()); prep.setLong(5, stObj.getTimeRemaining()); prep.setLong(6, stObj.getTilesDone()); prep.setLong(7, stObj.getTilesTotal()); prep.setLong(8, stObj.getFailedTileCount()); prep.setString(9, stObj.getBounds().toString()); prep.setString(10, stObj.getGridSetId()); prep.setInt(11, stObj.getSrs().getNumber()); prep.setInt(12, stObj.getThreadCount()); prep.setInt(13, stObj.getZoomStart()); prep.setInt(14, stObj.getZoomStop()); prep.setString(15, stObj.getFormat()); prep.setString(16, stObj.getJobType().name()); prep.setFloat(17, stObj.getThroughput()); prep.setInt(18, stObj.getMaxThroughput()); prep.setString(19, stObj.getPriority().name()); prep.setString(20, stObj.getSchedule()); prep.setBoolean(21, stObj.isRunOnce()); prep.setLong(22, stObj.getSpawnedBy()); prep.setBoolean(23, stObj.isFilterUpdate()); prep.setString(24, stObj.getEncodedParameters()); prep.setTimestamp(25, stObj.getTimeFirstStart()); prep.setTimestamp(26, stObj.getTimeLatestStart()); prep.setTimestamp(27, stObj.getTimeFinish()); insertId = wrappedInsert(prep); } finally { close(prep); } if (insertId == null) { log.error("Did not receive an id for " + query); } else { if (stObj.getJobId() == -1) { // only use the inserted id if we were doing an insert. // what insertid will be if we weren't doing an insert is not defined. stObj.setJobId(insertId.longValue()); } } putRecentJobLogs(stObj, conn); } finally { conn.close(); } }
From source file:org.latticesoft.util.resource.dao.Param.java
private void setValueToStatement(Object o, PreparedStatement pstmt) throws SQLException { if (log.isDebugEnabled()) { log.debug(this.sqlIndex + "=" + o); }/* w ww .ja v a2 s . c o m*/ switch (this.sqlType) { case Types.VARCHAR: case Types.CHAR: String s = (String) o; pstmt.setString(this.sqlIndex, s); break; case Types.BOOLEAN: if (o != null && o instanceof Boolean) { boolean b = ((Boolean) o).booleanValue(); pstmt.setBoolean(this.sqlIndex, b); } break; case Types.INTEGER: if (o != null && o instanceof Integer) { int i = ((Integer) o).intValue(); pstmt.setInt(this.sqlIndex, i); } break; case Types.SMALLINT: if (o != null && o instanceof Short) { short ss = ((Short) o).shortValue(); pstmt.setShort(this.sqlIndex, ss); } break; case Types.TINYINT: if (o != null && o instanceof Byte) { byte bb = ((Byte) o).byteValue(); pstmt.setByte(this.sqlIndex, bb); } break; case Types.BIGINT: if (o != null && o instanceof Long) { long l = ((Long) o).longValue(); pstmt.setLong(this.sqlIndex, l); } break; case Types.DOUBLE: if (o != null && o instanceof Double) { double dd = ((Double) o).doubleValue(); pstmt.setDouble(this.sqlIndex, dd); } break; case Types.FLOAT: if (o != null && o instanceof Float) { float f = ((Float) o).floatValue(); pstmt.setFloat(this.sqlIndex, f); } break; case Types.NUMERIC: if (o != null && o instanceof BigDecimal) { BigDecimal bd = (BigDecimal) o; pstmt.setBigDecimal(this.sqlIndex, bd); } break; case Types.TIMESTAMP: if (o != null && o instanceof Timestamp) { Timestamp ts = (Timestamp) o; pstmt.setTimestamp(this.sqlIndex, ts); } break; case Types.NULL: if (log.isDebugEnabled()) { log.debug(this.sqlIndex + " IS NULL"); } pstmt.setNull(this.sqlIndex, Types.NULL); break; default: if (o != null) { pstmt.setObject(this.sqlIndex, o); } } }
From source file:migration.ProjektMigration.java
/** * Creates the besteller.//from w w w .j av a 2s . c o m * * @param con * the con */ public void createBesteller(final Connection con) { String load_sql; Statement load_stmt; ResultSet load_rs; String store_sql; PreparedStatement store_prepstmt; final ResultSet store_rs; try { // insert into Besteller (anrede, bestellerName, // einzahlungErwuenscht, einzahlungFestgelegt, funktion, projekt, // sigel_sigelId) values (?, ?, ?, ?, ?, ?, ?) load_sql = "SELECT Besteller, AnredeKuv, Einzahlungerwuenscht, Einzahlungfestgelegt, Sigel, Projekt, Funktion FROM Bestellertabelle"; load_stmt = this.leg_con.createStatement(); store_sql = "INSERT INTO Besteller (anrede, bestellerName, einzahlungErwuenscht, einzahlungFestgelegt, funktion, projekt, sigel_sigelId) VALUES (?, ?, ?, ?, ?, ?, ?)"; store_prepstmt = this.tgt_con.prepareStatement(store_sql); // evtl. // brauchen // wir // was // in // Richtung: // Statement.RETURN_GENERATED_KEYS final int laenge = this.help.sqlGetLength(con, load_sql); this.bestellers = new String[laenge]; this.bestellers_sigels = new int[laenge]; // logger.info("Lese von Besteller"); load_stmt.execute(load_sql); load_rs = load_stmt.getResultSet(); // logger.info("Schreibe nach Besteller"); for (int i = 0; i < laenge; i++) { load_rs.next(); store_prepstmt.setString(1, load_rs.getString("AnredeKuv")); this.bestellers[i] = load_rs.getString("Besteller"); store_prepstmt.setString(2, this.bestellers[i]); store_prepstmt.setFloat(3, load_rs.getFloat("Einzahlungerwuenscht")); store_prepstmt.setFloat(4, load_rs.getFloat("Einzahlungfestgelegt")); store_prepstmt.setString(5, load_rs.getString("Funktion")); store_prepstmt.setString(6, load_rs.getString("Projekt")); this.bestellers_sigels[i] = this.help.getIdFromStringArray(this.help.getSigel(), load_rs.getString("Sigel")); store_prepstmt.setInt(7, this.bestellers_sigels[i]); store_prepstmt.executeUpdate(); } } catch (final SQLException e) { e.printStackTrace(); // To change body of catch statement use File | // Settings | File Templates. } // insert into Interesse (besteller_bestellerId, interesse, journal_id) // values (?, ?, ?) // insert into Nutzung (journal_id, nutzungsjahr, rechnungsbetrag, // zeitraum, zugriffe) values (?, ?, ?, ?, ?) // insert into Rechnung (betrag, bezugsform, bezugsjahr, // exemplar_exemplarId, sigel_sigelId) values (?, ?, ?, ?, ?) }