List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param pStmt/*w w w .j a v a 2s . c o m*/ * @param type * @param data * @throws SQLException */ public static void setData(final PreparedStatement pStmt, final int type, final int colInx, final Object data) throws SQLException { if (data == null) { pStmt.setObject(colInx, null); return; } boolean isStr = data instanceof String; switch (type) { case java.sql.Types.TINYINT: case java.sql.Types.SMALLINT: case java.sql.Types.INTEGER: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setInt(colInx, (Integer) data); } break; case java.sql.Types.FLOAT: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setFloat(colInx, (Float) data); } break; case java.sql.Types.VARCHAR: case java.sql.Types.CHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.LONGNVARCHAR: case java.sql.Types.NCHAR: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setString(colInx, (String) data); } break; case java.sql.Types.REAL: case java.sql.Types.DOUBLE: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setDouble(colInx, (Double) data); } break; case java.sql.Types.DATE: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setDate(colInx, (java.sql.Date) data); } break; case java.sql.Types.TIMESTAMP: if (isStr) { pStmt.setString(colInx, (String) data); } else { pStmt.setTimestamp(colInx, (Timestamp) data); } break; case java.sql.Types.BOOLEAN: if (isStr) { String val = (String) data; pStmt.setBoolean(colInx, !val.equalsIgnoreCase("true")); } else { pStmt.setBoolean(colInx, (Boolean) data); } break; case java.sql.Types.BIT: if (data instanceof Boolean) { pStmt.setBoolean(colInx, (Boolean) data); } else { pStmt.setBoolean(colInx, !(((Integer) data) == 0)); } break; default: throw new RuntimeException(String.format("Missing case for SQL Type %d for Column: %d Data[%s]", type, colInx, data.getClass().getSimpleName())); } }
From source file:nl.nn.adapterframework.jdbc.JdbcFacade.java
protected void applyParameters(PreparedStatement statement, ParameterValueList parameters) throws SQLException, SenderException { // statement.clearParameters(); /*/*w w w.jav a 2 s.c om*/ // getParameterMetaData() is not supported on the WebSphere java.sql.PreparedStatement implementation. int senderParameterCount = parameters.size(); int statementParameterCount = statement.getParameterMetaData().getParameterCount(); if (statementParameterCount<senderParameterCount) { throw new SenderException(getLogPrefix()+"statement has more ["+statementParameterCount+"] parameters defined than sender ["+senderParameterCount+"]"); } */ for (int i = 0; i < parameters.size(); i++) { ParameterValue pv = parameters.getParameterValue(i); String paramType = pv.getDefinition().getType(); Object value = pv.getValue(); // log.debug("applying parameter ["+(i+1)+","+parameters.getParameterValue(i).getDefinition().getName()+"], value["+parameterValue+"]"); if (Parameter.TYPE_DATE.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.DATE); } else { statement.setDate(i + 1, new java.sql.Date(((Date) value).getTime())); } } else if (Parameter.TYPE_DATETIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_TIMESTAMP.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_TIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIME); } else { statement.setTime(i + 1, new java.sql.Time(((Date) value).getTime())); } } else if (Parameter.TYPE_XMLDATETIME.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.TIMESTAMP); } else { statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime())); } } else if (Parameter.TYPE_NUMBER.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.NUMERIC); } else { statement.setDouble(i + 1, ((Number) value).doubleValue()); } } else if (Parameter.TYPE_INTEGER.equals(paramType)) { if (value == null) { statement.setNull(i + 1, Types.INTEGER); } else { statement.setInt(i + 1, (Integer) value); } } else if (Parameter.TYPE_INPUTSTREAM.equals(paramType)) { if (value instanceof FileInputStream) { FileInputStream fis = (FileInputStream) value; long len = 0; try { len = fis.getChannel().size(); } catch (IOException e) { log.warn(getLogPrefix() + "could not determine file size", e); } statement.setBinaryStream(i + 1, fis, (int) len); } else if (value instanceof ByteArrayInputStream) { ByteArrayInputStream bais = (ByteArrayInputStream) value; long len = bais.available(); statement.setBinaryStream(i + 1, bais, (int) len); } else { throw new SenderException(getLogPrefix() + "unknown inputstream [" + value.getClass() + "] for parameter [" + pv.getDefinition().getName() + "]"); } } else if ("string2bytes".equals(paramType)) { statement.setBytes(i + 1, ((String) value).getBytes()); } else if ("bytes".equals(paramType)) { statement.setBytes(i + 1, (byte[]) value); } else { statement.setString(i + 1, (String) value); } } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * // w w w . jav a 2 s . c om */ public void process() throws SQLException { int dupAgents = 0; int dupLocality = 0; int unknown = 0; boolean doAll = false; BasicSQLUtils.setDBConnection(dbConn); boolean doTrim = false; if (doTrim || doAll) { String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL); String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1)," + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote); String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL2); String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote2); } IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); IdHashMapper agentMapper; Division division = (Division) session.get(Division.class, 2); initialPrepareStatements(); BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'"); //IdMapperMgr.setSkippingOldTableCheck(true); boolean doAgents = false; if (doAgents || doAll) { agentMapper = new IdTableMapper("agent", "AgentID", false, false); String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Agents..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { String first = rs.getString(1); String last = rs.getString(2); String company = rs.getString(3); String addr1 = rs.getString(4); String addr2 = rs.getString(5); String city = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); String zip = rs.getString(9); String phone = rs.getString(10); String fax = rs.getString(11); String email = rs.getString(12); Integer oldId = rs.getInt(13); if (oldId == null) { log.error("Null primary Id: " + last + " " + first); continue; } Agent agent = getAgent(first, last, city, state); Integer agentId = null; if (agent == null) { agent = new Agent(); agent.initialize(); agent.setFirstName(first); agent.setLastName(last); agent.setEmail(email); agent.setRemarks(company); agent.setDivision(division); Address addr = new Address(); addr.initialize(); addr.setAddress(addr1); addr.setAddress2(addr2); addr.setCity(city); addr.setState(state); addr.setCountry(country); addr.setPostalCode(zip); addr.setPhone1(phone); addr.setFax(fax); agent.getAddresses().add(addr); addr.setAgent(agent); Transaction trans = null; try { trans = session.beginTransaction(); session.saveOrUpdate(agent); session.saveOrUpdate(addr); trans.commit(); agentId = agent.getId(); } catch (Exception ex) { ex.printStackTrace(); try { if (trans != null) trans.rollback(); } catch (Exception ex2) { ex2.printStackTrace(); } } } else { agentId = agent.getId(); dupAgents++; //System.out.println("Found Agent: "+first+", "+last); } agentMapper.put(oldId, agentId); cnt++; if (cnt % 500 == 0) { System.out.println("Agents: " + cnt); } if (cnt % 400 == 0) { HibernateUtil.closeSession(); session = HibernateUtil.getCurrentSession(); hibSession = new HibernateDataProviderSession(session); } } rs.close(); stmt.close(); division = (Division) session.get(Division.class, 2); } else { //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false); IdHashMapper.setEnableDelete(false); agentMapper = new IdTableMapper("agent", "AgentID", null, false, false); } System.out.println("Duplicated Agent: " + dupAgents); boolean doTags = true; if (doTags || doAll) { HashMap<String, Integer> localityHash = new HashMap<String, Integer>(); HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>(); int divId = 2; int dspId = 3; int colId = 4; String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum " + "FROM tag AS t Inner Join page AS p ON t.page = p.page "; 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, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); if (tag != null && tag.startsWith("ERR")) continue; Date date = rs.getDate(2); String wild = rs.getString(3); String gender = rs.getString(4); String city = rs.getString(5); String county = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); //String zip = rs.getString(9); String obs = rs.getString(10); double lat = rs.getDouble(11); double lon = rs.getDouble(12); double angle = rs.getDouble(13); Integer taggerId = rs.getInt(14); String locName = null; String fullName = null; Integer locId = null; Integer geoId = getGeography(country, state, county); if (geoId != null) { //locName = localityHash.get(geoId); 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); } else { unknown++; fullName = "Unknown"; locName = buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setDouble(1, lat); lcStmt.setDouble(2, lon); lcStmt.setByte(3, (byte) 0); lcStmt.setString(4, Double.toString(lat)); lcStmt.setString(5, Double.toString(lon)); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setDouble(8, angle); lcStmt.setString(9, locName); lcStmt.setObject(10, geoId); lcStmt.setTimestamp(11, ts); lcStmt.setInt(12, 1); lcStmt.setInt(13, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); localityHash.put(locName, locId); } else { dupLocality++; } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setString(2, wild); ceStmt.setInt(3, dspId); ceStmt.setInt(4, locId); ceStmt.setTimestamp(5, ts); ceStmt.setInt(6, 1); ceStmt.setInt(7, 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, obs); coStmt.setInt(5, colId); coStmt.setInt(6, colId); coStmt.setInt(7, ceId); coStmt.setTimestamp(8, ts); coStmt.setInt(9, 1); coStmt.setInt(10, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); //Integer coltrId = null; if (taggerId != null) { Integer agentId = agentMapper.get(taggerId); //System.out.println(agentId); if (agentId != null) { // 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(); //coltrId = BasicSQLUtils.getInsertedId(clStmt); //BasicSQLUtils.getInsertedId(clStmt); } else { log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + " AgentID:: " + agentId); } } else { log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId); } cnt++; if (cnt % 1000 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); System.out.println("Duplicated Agent: " + dupAgents); System.out.println("Duplicated Localities: " + dupLocality); System.out.println("Unknown Localities: " + unknown); System.out.println("Localities: " + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality")); } }
From source file:axiom.objectmodel.db.NodeManager.java
private void setStatementValues(PreparedStatement stmt, int stmtNumber, Property p, int columnType) throws SQLException { if (p.getValue() == null) { stmt.setNull(stmtNumber, columnType); } else {/*from w ww. jav a 2s .c om*/ switch (columnType) { case Types.BIT: case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: stmt.setLong(stmtNumber, p.getIntegerValue()); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: stmt.setDouble(stmtNumber, p.getFloatValue()); break; case Types.VARBINARY: case Types.BINARY: case Types.BLOB: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.LONGVARBINARY: case Types.LONGVARCHAR: try { stmt.setString(stmtNumber, p.getStringValue()); } catch (SQLException x) { String str = p.getStringValue(); Reader r = new StringReader(str); stmt.setCharacterStream(stmtNumber, r, str.length()); } break; case Types.CLOB: String val = p.getStringValue(); Reader isr = new StringReader(val); stmt.setCharacterStream(stmtNumber, isr, val.length()); break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: stmt.setTimestamp(stmtNumber, p.getTimestampValue()); break; case Types.NULL: stmt.setNull(stmtNumber, 0); break; default: stmt.setString(stmtNumber, p.getStringValue()); break; } } }
From source file:com.flexoodb.engines.FlexJAXBMappedDBDataEngine.java
private void updatePreparedStatement(String tablename, Hashtable<String, Object[]> fieldswithcontent, PreparedStatement ps) throws Exception { Enumeration en = fieldswithcontent.keys(); int i = 0;//from w ww. j a v a2 s. c om while (en.hasMoreElements()) { i++; try { String field = (String) en.nextElement(); Object[] o2 = fieldswithcontent.get(field); String type = (String) o2[0]; Object o = o2[1]; //System.out.println(field+" "+type+" "+o); if (type.equals("string")) { ps.setString(i, (String) o); } else if (type.equals("byte[]") || type.equals("base64Binary")) { ps.setBinaryStream(i, new ByteArrayInputStream((byte[]) o)); } else if (type.equals("dateTime")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d = null; if (cal.toString().indexOf("0003-11-30") > -1) { ps.setString(i, "0000-00-00 00:00:00"); } else { d = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setTimestamp(i, new java.sql.Timestamp(d.getTime())); } } else if (type.equals("date")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setDate(i, java.sql.Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(d1))); } else if (type.equals("time")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; String c = cal.toString(); c = c.replaceFirst("0003-11-30", "0000-00-00"); Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(c); ps.setTime(i, new java.sql.Time(d1.getTime())); } else if (type.equals("integer")) { ps.setInt(i, ((BigInteger) o).intValue()); } else if (type.equals("double")) { ps.setDouble(i, (Double) o); } else if (type.equals("float")) { ps.setFloat(i, (Float) o); } else if (type.equals("long")) { ps.setLong(i, (Long) o); } else { throw new Exception("unknown type [" + type + "] for field [" + field + "] encountered while trying to update table [" + tablename + "]."); } } catch (Exception e) { e.printStackTrace(); throw e; } } }
From source file:fll.db.Queries.java
/** * Compute the total scores for all entered subjective scores. * // w w w . j av a 2 s. c om * @param connection * @throws SQLException */ @SuppressFBWarnings(value = { "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "Category determines table name") private static void updateSubjectiveScoreTotals(final ChallengeDescription description, final Connection connection, final int tournament) throws SQLException { PreparedStatement updatePrep = null; PreparedStatement selectPrep = null; ResultSet rs = null; try { // Subjective --- for (final ScoreCategory subjectiveElement : description.getSubjectiveCategories()) { final String categoryName = subjectiveElement.getName(); // build up the SQL updatePrep = connection.prepareStatement("UPDATE "// + categoryName // + " SET ComputedTotal = ? WHERE TeamNumber = ? AND Tournament = ? AND Judge = ?"); selectPrep = connection.prepareStatement("SELECT * FROM " // + categoryName // + " WHERE Tournament = ?"); selectPrep.setInt(1, tournament); updatePrep.setInt(3, tournament); rs = selectPrep.executeQuery(); while (rs.next()) { final int teamNumber = rs.getInt("TeamNumber"); final TeamScore teamScore = new DatabaseTeamScore(teamNumber, rs); final double computedTotal; if (teamScore.isNoShow()) { computedTotal = Double.NaN; } else { computedTotal = subjectiveElement.evaluate(teamScore); } if (Double.isNaN(computedTotal)) { updatePrep.setNull(1, Types.DOUBLE); } else { updatePrep.setDouble(1, computedTotal); } updatePrep.setInt(2, teamNumber); final String judge = rs.getString("Judge"); updatePrep.setString(4, judge); updatePrep.executeUpdate(); } rs.close(); updatePrep.close(); selectPrep.close(); } } finally { SQLFunctions.close(rs); SQLFunctions.close(updatePrep); SQLFunctions.close(selectPrep); } }
From source file:HSqlManager.java
@SuppressWarnings("Duplicates") @Deprecated//from w w w . j a v a 2 s . c om public static void mycoUniqueDB(Connection connection, int bps) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { long time = System.currentTimeMillis(); DpalLoad.main(new String[1]); HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64; String base = new File("").getAbsolutePath(); if (!written) { CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE.parseAllPhages(bps); } Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); PrintWriter log = new PrintWriter(new File("javalog.log")); stat.execute("SET FILES LOG FALSE;\n"); PreparedStatement st = db .prepareStatement("UPDATE Primerdb.Primers" + " SET UniqueP = true, Tm = ?, GC =?, Hairpin =?" + "WHERE Cluster = ? and Strain = ? and " + "Sequence = ? and Bp = ?"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); String strain = ""; while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); if (r[2].equals("xkcd")) { strain = r[0]; } } call.close(); String x = strain; Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()); String[] clusters = clust.toArray(new String[clust.size()]); for (String z : clusters) { try { Set<String> nonclustphages = phages.stream().filter(a -> a[0].equals(x) && !a[1].equals(z)) .map(a -> a[2]).collect(Collectors.toSet()); ResultSet resultSet = stat.executeQuery( "Select Sequence from primerdb.primers" + " where Strain ='" + x + "' and Cluster ='" + z + "' and CommonP = true" + " and Bp = " + Integer.valueOf(bps) + " "); Set<CharSequence> primers = Collections.synchronizedSet(new HashSet<>()); while (resultSet.next()) { primers.add(resultSet.getString("Sequence")); } resultSet.close(); for (String phage : nonclustphages) { // String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta"); // String sequence =seqs[0]+seqs[1]; // Map<String, List<Integer>> seqInd = new HashMap<>(); // for (int i = 0; i <= sequence.length()-bps; i++) { // String sub=sequence.substring(i,i+bps); // if(seqInd.containsKey(sub)){ // seqInd.get(sub).add(i); // }else { // List<Integer> list = new ArrayList<>(); // list.add(i); // seqInd.put(sub,list); // } // } // primers = primers.stream().filter(primer->!seqInd.containsKey(primer)).collect(Collectors.toSet()); // primers =Sets.difference(primers,CSV.readCSV(base + "/PhageData/"+Integer.toString(bps) // + phage + ".csv")); CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv").stream() .filter(primers::contains).forEach(primers::remove); // System.gc(); } int i = 0; for (CharSequence a : primers) { try { st.setDouble(1, HSqlPrimerDesign.primerTm(a, 0, 800, 1.5, 0.2)); st.setDouble(2, HSqlPrimerDesign.gcContent(a)); st.setBoolean(3, HSqlPrimerDesign.calcHairpin((String) a, 4)); st.setString(4, z); st.setString(5, x); st.setString(6, a.toString()); st.setInt(7, bps); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } log.println(z); log.flush(); System.gc(); } stat.execute("SET FILES LOG TRUE\n"); st.close(); stat.close(); System.out.println("Unique Updated"); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); }
From source file:assignment3.Populate.java
CreateBusiness() {
try {//from w ww.j a v a2s . c o m
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("JDBC Driver Missing");
e.printStackTrace();
return;
}
System.out.println("Oracle JDBC Driver Connected");
Connection conn = null;
PreparedStatement ps = null;
PreparedStatement psbusinesscat = null;
PreparedStatement pscate = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
JSONParser parser = new JSONParser();
BufferedReader br = new BufferedReader(new FileReader(
"C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_business.json"));
// BufferedReader br = new BufferedReader(new FileReader("C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\smallbusiness.json"));
int batch = 200;
int count = 0;
String sCurrentLine = "";
String temp = "";
ArrayList<String> cat = new ArrayList<String>();
cat.add("Active Life");
cat.add("Arts & Entertainment");
cat.add("Automotive");
cat.add("Car Rental");
cat.add("Cafes");
cat.add("Beauty & Spas");
cat.add("Convenience Stores");
cat.add("Dentists");
cat.add("Doctors");
cat.add("Drugstores");
cat.add("Department Stores");
cat.add("Education");
cat.add("Event Planning & Services");
cat.add("Flower & Gifts");
cat.add("Food");
cat.add("Health & Medical");
cat.add("Home Services");
cat.add("Home & Gardens");
cat.add("Hospitals");
cat.add("Hotels & Travel");
cat.add("Hardware Stores");
cat.add("Grocery");
cat.add("Medical Centers");
cat.add("Nurseries & Gardening");
cat.add("Nightlife");
cat.add("Shopping");
cat.add("Restaurants");
cat.add("Transportation");
while ((sCurrentLine = br.readLine()) != null) {
conn.setAutoCommit(false);
Object obj = parser.parse(sCurrentLine);
JSONObject t = (JSONObject) obj;
String business_id = t.get("business_id").toString();
String city = t.get("city").toString();
String state = t.get("state").toString();
Long review_count = (Long) t.get("review_count");
Double stars = (Double) t.get("stars");
Double latitude = (Double) t.get("latitude");
Double longitude = (Double) t.get("longitude");
String business_name = t.get("name").toString();
String type = t.get("type").toString();
ps = conn.prepareStatement("insert into business values (?, ?, ?,?,?,?, ?, ?,?)");
ps.setString(1, business_id);
ps.setString(2, city);
ps.setString(3, state);
ps.setLong(4, review_count);
ps.setDouble(5, stars);
ps.setDouble(6, latitude);
ps.setDouble(7, longitude);
ps.setString(8, business_name);
ps.setString(9, type);
ps.executeUpdate();
ps.close();
conn.commit();
psbusinesscat = conn.prepareStatement("insert into categories values (?,?,?)");
JSONArray categories = (JSONArray) (t).get("categories");
int numfriends = 0;
if (categories.size() > 0) {
ArrayList<String> categories_list = new ArrayList<String>();
ArrayList<String> subcategories_list = new ArrayList<String>();
ArrayList<String> list = new ArrayList<String>();
// Retrive categories from json and seperate it as category and subcategory
for (int i = 0; i < categories.size(); i++) {
String tempCat = categories.get(i).toString();
if (cat.contains(tempCat))
categories_list.add(tempCat);
else
subcategories_list.add(tempCat);
}
psbusinesscat.setString(1, business_id);
for (int i = 0; i < categories_list.size(); i++) {
System.out.println(categories_list.get(i) + " ");
psbusinesscat.setString(2, categories_list.get(i));
if (subcategories_list.size() > 0) {
// set category for business table
for (int j = 0; j < subcategories_list.size(); j++) {
System.out.println("-" + subcategories_list.get(j));
psbusinesscat.setString(3, subcategories_list.get(j));
psbusinesscat.executeUpdate();
list.add(categories_list.get(i) + "~" + subcategories_list.get(j));
//insert into business categories list
}
} else {
psbusinesscat.setString(3, "");
psbusinesscat.executeUpdate();
System.out.println("");
}
}
psbusinesscat.close();
conn.commit();
List<String> deDupStringList = new ArrayList<>(new HashSet<>(list));
pscate = conn.prepareStatement("insert into categoryclass values (?,?)");
for (int i = 0; i < deDupStringList.size(); i++) {
pscate.setString(1,
deDupStringList.get(i).substring(0, deDupStringList.get(i).indexOf("~")));
System.out.println(deDupStringList.get(i).indexOf("~"));
pscate.setString(2,
deDupStringList.get(i).substring(deDupStringList.get(i).indexOf("~") + 1));
pscate.executeUpdate();
}
pscate.close();
}
conn.commit();
count++;
System.out.println("Record Number :" + count);
if (count % 50 == 0) { //reload driver
conn.close();
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
}
}
System.out.println("Last record inserted is " + count);
conn.commit();
ps.close();
conn.close();
} catch (Exception e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
} finally {
try {
ps.close();
pscate.close();
psbusinesscat.close();
conn.close();
} catch (Exception e) {
}
}
}
From source file:fll.db.Queries.java
/** * Compute the total scores for all entered performance scores. Uses both * verified and unverified scores./* ww w. j a v a 2 s . co m*/ * * @param connection connection to the database * @param tournament the tournament to update scores for. * @throws SQLException */ private static void updatePerformanceScoreTotals(final ChallengeDescription description, final Connection connection, final int tournament) throws SQLException { PreparedStatement updatePrep = null; PreparedStatement selectPrep = null; ResultSet rs = null; try { // build up the SQL updatePrep = connection.prepareStatement( "UPDATE Performance SET ComputedTotal = ? WHERE TeamNumber = ? AND Tournament = ? AND RunNumber = ?"); updatePrep.setInt(3, tournament); selectPrep = connection.prepareStatement("SELECT * FROM Performance WHERE Tournament = ?"); selectPrep.setInt(1, tournament); final PerformanceScoreCategory performanceElement = description.getPerformance(); final double minimumPerformanceScore = performanceElement.getMinimumScore(); rs = selectPrep.executeQuery(); while (rs.next()) { if (!rs.getBoolean("Bye")) { final int teamNumber = rs.getInt("TeamNumber"); final int runNumber = rs.getInt("RunNumber"); final TeamScore teamScore = new DatabaseTeamScore(teamNumber, runNumber, rs); final double computedTotal; if (teamScore.isNoShow()) { computedTotal = Double.NaN; } else { computedTotal = performanceElement.evaluate(teamScore); } if (LOGGER.isTraceEnabled()) { LOGGER.trace("Updating performance score for " + teamNumber + " run: " + runNumber + " total: " + computedTotal); } if (!Double.isNaN(computedTotal)) { updatePrep.setDouble(1, Math.max(computedTotal, minimumPerformanceScore)); } else { updatePrep.setNull(1, Types.DOUBLE); } updatePrep.setInt(2, teamNumber); updatePrep.setInt(4, runNumber); updatePrep.executeUpdate(); } } rs.close(); updatePrep.close(); selectPrep.close(); } finally { SQLFunctions.close(rs); SQLFunctions.close(updatePrep); SQLFunctions.close(selectPrep); } }
From source file:org.apache.openaz.xacml.std.pip.engines.jdbc.ConfigurableJDBCResolver.java
@Override public PreparedStatement getPreparedStatement(PIPEngine pipEngine, PIPRequest pipRequest, PIPFinder pipFinder, Connection connection) throws PIPException { /*//from ww w . ja va 2 s . c om * Do we support the request? */ if (!this.isSupported(pipRequest)) { return null; } PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(this.sqlQuery); } catch (SQLException ex) { this.logger.error("SQLException creating PreparedStatement: " + ex.toString(), ex); // TODO: throw the exception or return a null PreparedStatement? return null; } if (this.parameters.size() > 0) { /* * Gather all of the AttributeValues for parameters to the prepared statement. For now, we assume * a single value for each parameter. If there are multiple values we will log an error and return * a null PreparedStatement. TODO: Should the interface change to return a cross-product of * PreparedStatements to deal with multiple values for parameters? If not, should we just take the * first value and use it as the parameter value? */ for (int i = 0; i < this.parameters.size(); i++) { PIPRequest pipRequestParameter = this.parameters.get(i); PIPResponse pipResponse = pipFinder.getMatchingAttributes(pipRequestParameter, null); if (pipResponse.getStatus() == null || pipResponse.getStatus().isOk()) { Collection<Attribute> listAttributes = pipResponse.getAttributes(); if (listAttributes.size() > 0) { if (listAttributes.size() > 1) { this.logger.error("PIPFinder returned more than one Attribute for " + pipRequestParameter.toString()); throw new PIPException("PIPFinder returned more than one Attribute for " + pipRequestParameter.toString()); } Collection<AttributeValue<?>> listAttributeValuesReturned = listAttributes.iterator().next() .getValues(); if (listAttributeValuesReturned.size() > 0) { if (listAttributeValuesReturned.size() > 1) { this.logger.warn("PIPFinder returned more than one AttributeValue for " + pipRequestParameter.toString()); return null; } AttributeValue<?> attributeValue = listAttributeValuesReturned.iterator().next(); Identifier identifierAttributeValueDataType = attributeValue.getDataTypeId(); try { if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_INTEGER)) { preparedStatement.setInt(i + 1, DataTypes.DT_INTEGER.convert(attributeValue.getValue()).intValue()); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DOUBLE)) { preparedStatement.setDouble(i + 1, DataTypes.DT_DOUBLE.convert(attributeValue.getValue())); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_BOOLEAN)) { preparedStatement.setBoolean(i + 1, DataTypes.DT_BOOLEAN.convert(attributeValue.getValue())); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DATETIME)) { ISO8601DateTime iso8601DateTime = DataTypes.DT_DATETIME .convert(attributeValue.getValue()); java.sql.Date sqlDate = new java.sql.Date( iso8601DateTime.getCalendar().getTimeInMillis()); preparedStatement.setDate(i + 1, sqlDate, iso8601DateTime.getCalendar()); } else if (identifierAttributeValueDataType.equals(XACML3.ID_DATATYPE_DATE)) { ISO8601Date iso8601Date = DataTypes.DT_DATE.convert(attributeValue.getValue()); java.sql.Date sqlDate = new java.sql.Date( iso8601Date.getCalendar().getTimeInMillis()); preparedStatement.setDate(i + 1, sqlDate, iso8601Date.getCalendar()); } else { preparedStatement.setString(i + 1, DataTypes.DT_STRING.convert(attributeValue.getValue())); } } catch (Exception ex) { this.logger.error("Exception setting parameter " + (i + 1) + " to " + attributeValue.toString() + ": " + ex.toString(), ex); return null; } } else { this.logger.warn( "No AttributeValues returned for parameter " + pipRequestParameter.toString()); return null; } } else { this.logger.warn("No Attributes returned for parameter " + pipRequestParameter.toString()); return null; } } else { this.logger.warn("PIPFinder returned status " + pipResponse.getStatus().toString()); return null; } } } return preparedStatement; }