List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "UPDATE" on the given tableName with attribute values * and where clause./*from w w w . j a va 2 s . c om*/ * * @param tableName * @param attributeNameValue * @param whereClause * @return * @throws SQLException */ public int updateRow(String tableName, Map attributeNameValue, String whereClause, String dbType) throws SQLException { StringBuffer stmt = new StringBuffer(); PreparedStatement prepStmt = null; int rowsUpdated = 0; Object attribute = null; Iterator itr = null; String[] key = new String[attributeNameValue.size()]; int count = 0; stmt.append("UPDATE " + tablePrefix_ + tableName.trim() + " SET "); itr = attributeNameValue.keySet().iterator(); while (itr.hasNext()) { key[count] = (String) itr.next(); stmt.append(key[count++] + " = ?,"); } /* * for (int i = 0; i < attributeNames.size(); i++) { * stmt.append(attributeNames.get(i) + " = ?,"); } */ stmt = stmt.deleteCharAt(stmt.length() - 1); if (whereClause != null && !"".equals(whereClause)) { stmt.append(" WHERE "); stmt.append(whereClause); } // stmt = stmt.deleteCharAt(stmt.length()); log.debug("************ UPDATE QUERY ************"); log.debug(stmt.toString()); log.debug("**************************************"); try { String statement = new GenericSQLModifier(dbType, false).modifySQL(stmt.toString()); prepStmt = sqlConnection_.prepareStatement(statement); itr = attributeNameValue.keySet().iterator(); for (count = 0; count < key.length; count++) { attribute = attributeNameValue.get(key[count]); if (attribute instanceof String) { prepStmt.setString(count + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(count + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(count + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(count + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(count + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(count + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(count + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(count + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(count + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(count + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(count + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(count + 1, (Timestamp) attribute); } } rowsUpdated = prepStmt.executeUpdate(); } catch (Exception e) { log.error("Exception @ updateRow: " + e.getMessage()); } finally { prepStmt.close(); } return rowsUpdated; }
From source file:org.wso2.ws.dataservice.DBUtils.java
public static PreparedStatement getProcessedPreparedStatement(HashMap inputs, HashMap params, HashMap paramOrder, HashMap originalParamNames, HashMap paramTypes, Connection conn, String sqlStatement, String callee, String serviceName) throws AxisFault { String paramName = null;//from www . ja v a2 s . c o m String originalParamName = null; String sqlType = null; String value = null; String paramType = null; log.debug("[" + serviceName + "] Processing prepared statement for SQL " + sqlStatement); Set paramNames = params.keySet(); Object pramNameArray[] = paramNames.toArray(); try { PreparedStatement sqlQuery = null; if ("SQL".equals(callee)) { sqlQuery = conn.prepareStatement(sqlStatement); //SQL expects parameters, but not params set in config file if (sqlStatement.indexOf("?") > -1 && pramNameArray.length == 0) { throw new AxisFault( "[" + serviceName + "] SQL : " + sqlStatement + " expects one or more parameters. " + "But none is mentioned in the configuration file."); } } else if ("STORED-PROCEDURE".equals(callee)) { sqlQuery = conn.prepareCall(sqlStatement); } for (int i = 0; i < pramNameArray.length; i++) { paramName = (String) paramOrder.get(new Integer(i + 1)); originalParamName = (String) originalParamNames.get(new Integer(i + 1)); sqlType = (String) params.get(paramName); paramType = (String) paramTypes.get(paramName); value = (String) inputs.get(paramName); log.debug("[" + serviceName + "] Param name : " + paramName + " SQL Type : " + sqlType + " Value : " + value); if ("IN".equals(paramType) || "INOUT".equals(paramType)) { if (value == null || value.trim().length() == 0) { log.error("[" + serviceName + "] Empty value found for parameter : " + originalParamName); throw new AxisFault( "[" + serviceName + "] Empty value found for parameter : " + originalParamName); } } //work-a-round for setting NULL if ("NULL".equalsIgnoreCase(value)) { value = null; } //TODO : work-a-round for setting space if (sqlType == null) { // Defaults to string if ("IN".equals(paramType)) { sqlQuery.setString(i + 1, value); } else if ("INOUT".equals(paramType)) { sqlQuery.setString(i + 1, value); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } } else if (DBConstants.DataTypes.INTEGER.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setInt(i + 1, Integer.parseInt(value)); } else { ((CallableStatement) sqlQuery).setInt(i + 1, Integer.parseInt(value)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setInt(i + 1, Integer.parseInt(value)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.INTEGER); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.INTEGER); } } else if (DBConstants.DataTypes.STRING.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setString(i + 1, value); } else { ((CallableStatement) sqlQuery).setString(i + 1, value); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setString(i + 1, value); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } } else if (DBConstants.DataTypes.DOUBLE.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setDouble(i + 1, Double.parseDouble(value)); } else { ((CallableStatement) sqlQuery).setDouble(i + 1, Double.parseDouble(value)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setDouble(i + 1, Double.parseDouble(value)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DOUBLE); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DOUBLE); } } else if (DBConstants.DataTypes.DATE.equals(sqlType)) { try { //Only yyyy-MM-dd part is needed String modifiedValue = value.substring(0, 10); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setDate(i + 1, Date.valueOf(modifiedValue)); } else { ((CallableStatement) sqlQuery).setDate(i + 1, Date.valueOf(modifiedValue)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setDate(i + 1, Date.valueOf(modifiedValue)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } } catch (IllegalArgumentException e) { log.error("Incorrect date format(" + value + ") for parameter : " + paramName, e); throw new AxisFault("Incorrect date format for parameter : " + paramName + ".Date should be in yyyy-mm-dd format.", e); } } else if (DBConstants.DataTypes.TIMESTAMP.equals(sqlType)) { Timestamp timestamp = getTimestamp(value, paramName); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setTimestamp(i + 1, timestamp); } else { ((CallableStatement) sqlQuery).setTimestamp(i + 1, timestamp); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setTimestamp(i + 1, timestamp); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIMESTAMP); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIMESTAMP); } } else if (DBConstants.DataTypes.TIME.equals(sqlType)) { Time time = getTime(value, paramName); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setTime(i + 1, time); } else { ((CallableStatement) sqlQuery).setTime(i + 1, time); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setTime(i + 1, time); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIME); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIME); } } else { log.error("[" + serviceName + "] Unsupported data type : " + sqlType + " as input parameter."); throw new AxisFault("[" + serviceName + "] Found Unsupported data type : " + sqlType + " as input parameter."); } } return sqlQuery; } catch (NumberFormatException e) { log.error("[" + serviceName + "] Incorrect value found for parameter : " + originalParamName, e); throw new AxisFault("[" + serviceName + "] Incorrect value found for parameter : " + originalParamName, e); } catch (SQLException e) { log.error("[" + serviceName + "] Error occurred while preparing prepared statement for sql : " + sqlStatement, e); throw new AxisFault("[" + serviceName + "] Error occurred while preparing prepared statement for sql : " + sqlStatement, e); } }
From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java
protected int loadCust(int whseKount, int distWhseKount, int custDistKount) { int k = 0;//from ww w .ja v a2 s . c o m int t = 0; Customer customer = new Customer(); History history = new History(); PrintWriter outHist = null; try { PreparedStatement custPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_CUSTOMER); PreparedStatement histPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_HISTORY); now = new java.util.Date(); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv")); LOG.debug("\nWriting Customer file to: " + fileLocation + "customer.csv"); outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv")); LOG.debug("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv"); } t = (whseKount * distWhseKount * custDistKount * 2); LOG.debug("\nStart Cust-Hist Load for " + t + " Cust-Hists @ " + now + " ..."); for (int w = 1; w <= whseKount; w++) { for (int d = 1; d <= distWhseKount; d++) { for (int c = 1; c <= custDistKount; c++) { Timestamp sysdate = new java.sql.Timestamp(System.currentTimeMillis()); customer.c_id = c; customer.c_d_id = d; customer.c_w_id = w; // discount is random between [0.0000 ... 0.5000] customer.c_discount = (float) (TPCCUtil.randomNumber(1, 5000, gen) / 10000.0); if (TPCCUtil.randomNumber(1, 100, gen) <= 10) { customer.c_credit = "BC"; // 10% Bad Credit } else { customer.c_credit = "GC"; // 90% Good Credit } if (c <= 1000) { customer.c_last = TPCCUtil.getLastName(c - 1); } else { customer.c_last = TPCCUtil.getNonUniformRandomLastNameForLoad(gen); } customer.c_first = TPCCUtil.randomStr(TPCCUtil.randomNumber(8, 16, gen)); customer.c_credit_lim = 50000; customer.c_balance = -10; customer.c_ytd_payment = 10; customer.c_payment_cnt = 1; customer.c_delivery_cnt = 0; customer.c_street_1 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_street_2 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_city = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_state = TPCCUtil.randomStr(3).toUpperCase(); // TPC-C 4.3.2.7: 4 random digits + "11111" customer.c_zip = TPCCUtil.randomNStr(4) + "11111"; customer.c_phone = TPCCUtil.randomNStr(16); customer.c_since = sysdate; customer.c_middle = "OE"; customer.c_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(300, 500, gen)); history.h_c_id = c; history.h_c_d_id = d; history.h_c_w_id = w; history.h_d_id = d; history.h_w_id = w; history.h_date = sysdate; history.h_amount = 10; history.h_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 24, gen)); k = k + 2; if (outputFiles == false) { custPrepStmt.setLong(1, customer.c_w_id); custPrepStmt.setLong(2, customer.c_d_id); custPrepStmt.setLong(3, customer.c_id); custPrepStmt.setDouble(4, customer.c_discount); custPrepStmt.setString(5, customer.c_credit); custPrepStmt.setString(6, customer.c_last); custPrepStmt.setString(7, customer.c_first); custPrepStmt.setDouble(8, customer.c_credit_lim); custPrepStmt.setDouble(9, customer.c_balance); custPrepStmt.setDouble(10, customer.c_ytd_payment); custPrepStmt.setLong(11, customer.c_payment_cnt); custPrepStmt.setLong(12, customer.c_delivery_cnt); custPrepStmt.setString(13, customer.c_street_1); custPrepStmt.setString(14, customer.c_street_2); custPrepStmt.setString(15, customer.c_city); custPrepStmt.setString(16, customer.c_state); custPrepStmt.setString(17, customer.c_zip); custPrepStmt.setString(18, customer.c_phone); custPrepStmt.setTimestamp(19, customer.c_since); custPrepStmt.setString(20, customer.c_middle); custPrepStmt.setString(21, customer.c_data); custPrepStmt.addBatch(); histPrepStmt.setInt(1, history.h_c_id); histPrepStmt.setInt(2, history.h_c_d_id); histPrepStmt.setInt(3, history.h_c_w_id); histPrepStmt.setInt(4, history.h_d_id); histPrepStmt.setInt(5, history.h_w_id); histPrepStmt.setTimestamp(6, history.h_date); histPrepStmt.setDouble(7, history.h_amount); histPrepStmt.setString(8, history.h_data); histPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); custPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + customer.c_id + ","; str = str + customer.c_d_id + ","; str = str + customer.c_w_id + ","; str = str + customer.c_discount + ","; str = str + customer.c_credit + ","; str = str + customer.c_last + ","; str = str + customer.c_first + ","; str = str + customer.c_credit_lim + ","; str = str + customer.c_balance + ","; str = str + customer.c_ytd_payment + ","; str = str + customer.c_payment_cnt + ","; str = str + customer.c_delivery_cnt + ","; str = str + customer.c_street_1 + ","; str = str + customer.c_street_2 + ","; str = str + customer.c_city + ","; str = str + customer.c_state + ","; str = str + customer.c_zip + ","; str = str + customer.c_phone; out.println(str); str = ""; str = str + history.h_c_id + ","; str = str + history.h_c_d_id + ","; str = str + history.h_c_w_id + ","; str = str + history.h_d_id + ","; str = str + history.h_w_id + ","; str = str + history.h_date + ","; str = str + history.h_amount + ","; str = str + history.h_data; outHist.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for [c] } // end for [d] } // end for [w] long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); histPrepStmt.clearBatch(); transCommit(); now = new java.util.Date(); if (outputFiles == true) { outHist.close(); } LOG.debug("End Cust-Hist Data Load @ " + now); } catch (SQLException se) { LOG.debug(se.getMessage()); transRollback(); if (outputFiles == true) { outHist.close(); } } catch (Exception e) { e.printStackTrace(); transRollback(); if (outputFiles == true) { outHist.close(); } } return (k); }
From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
/** * Sets a specific value on a prepared statement *//*from ww w. j a v a 2 s . c o m*/ protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value) throws SQLException { int j = index; switch (f.getType()) { case MetaField.BOOLEAN: { if (value == null) { s.setNull(j, Types.BIT); } else if (value instanceof Boolean) { s.setBoolean(j, ((Boolean) value).booleanValue()); } else { s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue()); } } break; case MetaField.BYTE: { if (value == null) { s.setNull(j, Types.TINYINT); } else if (value instanceof Byte) { s.setByte(j, ((Byte) value).byteValue()); } else { s.setByte(j, Byte.valueOf(value.toString()).byteValue()); } } break; case MetaField.SHORT: { if (value == null) { s.setNull(j, Types.SMALLINT); } else if (value instanceof Short) { s.setShort(j, ((Short) value).shortValue()); } else { s.setShort(j, Short.valueOf(value.toString()).shortValue()); } } break; case MetaField.INT: { if (value == null) { s.setNull(j, Types.INTEGER); } else if (value instanceof Integer) { s.setInt(j, ((Integer) value).intValue()); } else { s.setInt(j, Integer.valueOf(value.toString()).intValue()); } } break; case MetaField.DATE: // NOTE DATE IS TREATED AS LONG! { if (value == null) { s.setNull(j, Types.TIMESTAMP); } else if (value instanceof java.util.Date) { s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime())); } else { s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue())); } } break; case MetaField.LONG: { if (value == null) { s.setNull(j, Types.BIGINT); } else if (value instanceof Long) { s.setLong(j, ((Long) value).longValue()); } else { s.setLong(j, Long.valueOf(value.toString()).longValue()); } } break; // WARNING: This should not be a valid key case MetaField.FLOAT: { if (value == null) { s.setNull(j, Types.FLOAT); } else if (value instanceof Float) { s.setFloat(j, ((Float) value).floatValue()); } else { s.setFloat(j, Float.valueOf(value.toString()).floatValue()); } } break; // WARNING: This should not be a valid key case MetaField.DOUBLE: { if (value == null) { s.setNull(j, Types.DOUBLE); } else if (value instanceof Double) { s.setDouble(j, ((Double) value).doubleValue()); } else { s.setDouble(j, Double.valueOf(value.toString()).doubleValue()); } } break; case MetaField.STRING: if (value == null) { s.setNull(j, Types.VARCHAR); } else { s.setString(j, value.toString()); } break; case MetaField.OBJECT: //if ( value == null ) // s.setNull( j, Types.BLOB ); //else s.setObject(j, value); break; } }
From source file:HSqlManager.java
public static void primerAnalysis(Connection connection, int bps) throws SQLException, IOException { long time = System.currentTimeMillis(); DpalLoad.main(new String[1]); HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64; String base = new File("").getAbsolutePath(); CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE = ImportPhagelist.getInstance(); INSTANCE.parseAllPhages(bps);// w w w .ja va 2 s. co m System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); time = System.currentTimeMillis(); written = true; Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); stat.execute("SET FILES LOG FALSE\n"); // PreparedStatement st = db.prepareStatement("Insert INTO Primerdb.Primers" + // "(Bp,Sequence, CommonP, UniqueP, Picked, Strain, Cluster)" + // " Values(?,?,true,false,false,?,?)"); PreparedStatement st = db.prepareStatement( "INSERT INTO Primerdb.Primers" + "(Bp,Sequence,Strain,Cluster,Tm,GC,UniqueP,CommonP,Hairpin) " + "VALUES(?,?,?,?,?,?,true,true,?)"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); 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(strain.equals("-myco")) { // if (r[2].equals("xkcd")) { // strain = r[0]; // } // }else if(strain.equals("-arthro")){ // if (r[2].equals("ArV1")) { // strain = r[0]; // } // } } call.close(); Set<String> strains = phages.stream().map(y -> y[0]).collect(Collectors.toSet()); for (String x : strains) { Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]) .collect(Collectors.toSet()); Map<String, Integer> clustersNum = new HashMap<>(); Map<Integer, String> clustersName = new HashMap<>(); Map<Integer, List<String>> clusters = new HashMap<>(); Map<Bytes, Primer> primers = new HashMap<>(); int i = 0; for (String cluster : clust) { clustersName.put(i, cluster); clustersNum.put(cluster, i); i++; } clust.parallelStream() .forEach(cluster -> clusters.put(clustersNum.get(cluster), phages.stream().filter(a -> a[0].equals(x) && a[1].equals(cluster)).map(a -> a[2]) .collect(Collectors.toList()))); for (int z : clusters.keySet()) { // try { List<String> clustphages = clusters.get(z); for (String phage : clustphages) { Set<Bytes> phagprimers = //Read from CSV file here //Premade CSV files of all possible //primers in a phage genome CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv").stream() .map(l -> new Bytes(l.getBytes())).collect(Collectors.toSet()); for (Bytes primer : phagprimers) { if (!primers.containsKey(primer)) { primers.put(primer, new Primer(z)); } else { Primer select = primers.get(primer); select.phageCount++; if (!select.containsCluster(z)) { select.addCluster(z); } } } } System.out.println(clustersName.get(z)); } int count = 0; Iterator<Map.Entry<Bytes, Primer>> primersSet = primers.entrySet().iterator(); while (primersSet.hasNext()) { Map.Entry<Bytes, Primer> primer = primersSet.next(); Primer primerInf = primer.getValue(); if (primerInf.clusters.length != 1) { primer.setValue(null); } else { int primerClust = -1; for (int cluster : primerInf.clusters) { primerClust = cluster; } if (primerInf.phageCount != clusters.get(primerClust).size()) { primer.setValue(null); } else { count++; } } } System.out.print("Unique Count: "); System.out.println(count); System.out.print("Primer Count: "); System.out.println(primers.size()); i = 0; for (Bytes a : primers.keySet()) { Primer primerInf = primers.get(a); if (primerInf != null) { String primerClust = ""; for (int cluster : primerInf.clusters) { primerClust = clustersName.get(cluster); } String str = new String(a.bytes); try { st.setInt(1, bps); st.setString(2, str); st.setString(3, x); st.setString(4, primerClust); // st.setDouble(5, HSqlPrimerDesign.primerTm(str, 0, 800, 1.5, 0.2)); st.setDouble(5, HSqlPrimerDesign.easytm(str)); st.setDouble(6, HSqlPrimerDesign.gcContent(str)); st.setBoolean(7, HSqlPrimerDesign.calcHairpin(str, 4)); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + primerClust); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } } if (i > 0) { st.executeBatch(); db.commit(); } // } System.out.println("Unique Updated"); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); } stat.execute("SET FILES LOG TRUE;"); st.close(); stat.close(); }
From source file:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public List<PartitionDescProto> getPartitionsByAlgebra(PartitionsByAlgebraProto request) throws UndefinedDatabaseException, UndefinedTableException, UndefinedPartitionMethodException, UnsupportedException {/*from w ww.j a v a 2 s . c o m*/ Connection conn = null; PreparedStatement pstmt = null; ResultSet res = null; int currentIndex = 1; String selectStatement = null; Pair<String, List<PartitionFilterSet>> pair = null; List<PartitionDescProto> partitions = new ArrayList<>(); List<PartitionFilterSet> filterSets = null; int databaseId = getDatabaseId(request.getDatabaseName()); int tableId = getTableId(databaseId, request.getDatabaseName(), request.getTableName()); if (!existPartitionMethod(request.getDatabaseName(), request.getTableName())) { throw new UndefinedPartitionMethodException(request.getTableName()); } try { TableDescProto tableDesc = getTable(request.getDatabaseName(), request.getTableName()); pair = getSelectStatementAndPartitionFilterSet(tableDesc.getTableName(), tableDesc.getPartition().getExpressionSchema().getFieldsList(), request.getAlgebra()); selectStatement = pair.getFirst(); filterSets = pair.getSecond(); conn = getConnection(); pstmt = conn.prepareStatement(selectStatement); // Set table id by force because first parameter of all direct sql is table id pstmt.setInt(currentIndex, tableId); currentIndex++; for (PartitionFilterSet filter : filterSets) { // Set table id by force because all filters have table id as first parameter. pstmt.setInt(currentIndex, tableId); currentIndex++; for (Pair<Type, Object> parameter : filter.getParameters()) { switch (parameter.getFirst()) { case BOOLEAN: pstmt.setBoolean(currentIndex, (Boolean) parameter.getSecond()); break; case INT8: pstmt.setLong(currentIndex, (Long) parameter.getSecond()); break; case FLOAT8: pstmt.setDouble(currentIndex, (Double) parameter.getSecond()); break; case DATE: pstmt.setDate(currentIndex, (Date) parameter.getSecond()); break; case TIMESTAMP: pstmt.setTimestamp(currentIndex, (Timestamp) parameter.getSecond()); break; case TIME: pstmt.setTime(currentIndex, (Time) parameter.getSecond()); break; default: pstmt.setString(currentIndex, (String) parameter.getSecond()); break; } currentIndex++; } } res = pstmt.executeQuery(); while (res.next()) { PartitionDescProto.Builder builder = PartitionDescProto.newBuilder(); builder.setId(res.getInt(COL_PARTITIONS_PK)); builder.setPartitionName(res.getString("PARTITION_NAME")); builder.setPath(res.getString("PATH")); builder.setNumBytes(res.getLong(COL_PARTITION_BYTES)); partitions.add(builder.build()); } } catch (SQLException se) { throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt, res); } return partitions; }
From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java
/** * bind the variables to the prepared statement * // ww w . j a v a 2 s . c o m * @param type * @param mapInfo * @param ps * @param annoId * @param anno * @throws SQLException */ private void saveAnnoBindVariables(final Type type, final AnnoMappingInfo mapInfo, PreparedStatement ps, int annoId, FeatureStructure anno, final BiMap<Annotation, Integer> mapAnnoToId) throws SQLException { // set anno_base_id int argIdx = 1; ps.setInt(argIdx++, annoId); if (mapInfo.getCoveredTextColumn() != null) { String trunc = null; if (anno instanceof Annotation) { trunc = truncateString(((Annotation) anno).getCoveredText(), mapInfo.getCoveredTextColumn().getSize()); } ps.setString(argIdx++, trunc); } if (!Strings.isNullOrEmpty(mapInfo.getUimaTypeIdColumnName())) { ps.setInt(argIdx++, mapInfo.getUimaTypeId()); } // iterate over fields for (Map.Entry<String, ColumnMappingInfo> fieldEntry : mapInfo.getMapField().entrySet()) { ColumnMappingInfo fieldMapInfo = fieldEntry.getValue(); String fieldName = fieldMapInfo.getAnnoFieldName(); Feature feat = type.getFeatureByBaseName(fieldName); if (fieldMapInfo.getConverter() != null) { try { String prop = anno.getFeatureValueAsString(feat); ps.setObject(argIdx, fieldMapInfo.getConverter().convert(fieldMapInfo.getTargetType(), prop)); } catch (Exception e) { throw new RuntimeException(e); } } else if (!feat.getRange().isPrimitive()) { // feature is a structure/annotation FeatureStructure fs = anno.getFeatureValue(feat); if (fs == null) { // feature is null - set the column to null ps.setNull(argIdx, fieldMapInfo.getSqlType()); } else { if (fieldMapInfo.getJxpath() != null) { // jxpath to pull out feature attribute Object o = this.extractFeature(fieldMapInfo.getJxpath(), fs); if (o == null) { // extracted value null - set column to null ps.setNull(argIdx, fieldMapInfo.getSqlType()); } else if (o instanceof String) { // string - truncate as needed String trunc = truncateString((String) o, fieldMapInfo.getSize()); ps.setString(argIdx, trunc); } else { // set value ps.setObject(argIdx, o); } } else { // reference to another annotation - get the other // anno's id Integer refAnnoId = null; if (fs instanceof Annotation) { refAnnoId = mapAnnoToId.get(fs); } if (refAnnoId != null) { ps.setInt(argIdx, refAnnoId); } else { ps.setNull(argIdx, Types.INTEGER); } } } } else { if ("uima.cas.Integer".equals(feat.getRange().getName())) { ps.setInt(argIdx, anno.getIntValue(feat)); } else if ("uima.cas.Short".equals(feat.getRange().getName())) { ps.setShort(argIdx, anno.getShortValue(feat)); } else if ("uima.cas.Long".equals(feat.getRange().getName())) { ps.setLong(argIdx, anno.getLongValue(feat)); } else if ("uima.cas.Float".equals(feat.getRange().getName())) { ps.setFloat(argIdx, anno.getFloatValue(feat)); } else if ("uima.cas.Double".equals(feat.getRange().getName())) { ps.setDouble(argIdx, anno.getDoubleValue(feat)); } else if ("uima.cas.Byte".equals(feat.getRange().getName())) { ps.setByte(argIdx, anno.getByteValue(feat)); } else if ("uima.cas.Boolean".equals(feat.getRange().getName())) { ps.setBoolean(argIdx, anno.getBooleanValue(feat)); } else if ("uima.cas.String".equals(feat.getRange().getName())) { String trunc = truncateString(anno.getStringValue(feat), fieldMapInfo.getSize()); ps.setString(argIdx, trunc); } } argIdx++; } }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. */// www . ja v a 2 s . c o m public void setDouble(PreparedStatement stmnt, int idx, double val, Column col) throws SQLException { stmnt.setDouble(idx, val); }
From source file:com.gtwm.pb.model.manageData.DataManagement.java
/** * Used by both the public saveRecord and globalEdit methods *///from w w w . j a v a2 s .c o m private void saveRecord(HttpServletRequest request, TableInfo table, LinkedHashMap<BaseField, BaseValue> dataToSave, boolean newRecord, Set<Integer> rowIds, SessionDataInfo sessionData, List<FileItem> multipartItems) throws InputRecordException, ObjectNotFoundException, SQLException, CantDoThatException, CodingErrorException, DisallowedException, MissingParametersException { if ((dataToSave.size() == 0) && (!newRecord)) { // Note: this does actually happen quite a lot, from two particular // users, therefore I've commented out the log warning. // Haven't tracked down the cause but it doesn't seem to be creating // a problem. // logger.warn("Call to saveRecord with no data to save. User = " // + request.getRemoteUser() + ", table = " + table + ", rowIds = " // + rowIds); return; } this.setHiddenFieldValues(request, table, dataToSave, newRecord); boolean globalEdit = false; int rowId = -1; if (rowIds.size() > 1) { globalEdit = true; } else if (rowIds.size() == 1) { rowId = (new LinkedList<Integer>(rowIds)).getFirst(); } else { throw new ObjectNotFoundException("Row ID list " + rowIds + " is invalid"); } StringBuilder SQLCodeBuilder = new StringBuilder(); // Generate CSV of fields and placeholders to use in update/insert SQL // string StringBuilder fieldsCsvBuilder = new StringBuilder(); StringBuilder fieldsAndPlaceholdersCsvBuilder = new StringBuilder(); StringBuilder valuePlaceholdersCsvBuilder = new StringBuilder(); for (BaseField field : dataToSave.keySet()) { fieldsCsvBuilder.append(field.getInternalFieldName()); fieldsCsvBuilder.append(", "); valuePlaceholdersCsvBuilder.append("?, "); fieldsAndPlaceholdersCsvBuilder.append(field.getInternalFieldName()); fieldsAndPlaceholdersCsvBuilder.append("=?, "); } // Used if doing an INSERT String fieldsCsv = fieldsCsvBuilder.toString(); String valuePlaceholdersCsv = valuePlaceholdersCsvBuilder.toString(); // Used if doing an UPDATE String fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsvBuilder.toString(); if (!fieldsCsv.equals("")) { fieldsCsv = fieldsCsv.substring(0, fieldsCsv.length() - 2); valuePlaceholdersCsv = valuePlaceholdersCsv.substring(0, valuePlaceholdersCsv.length() - 2); fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsv.substring(0, fieldsAndPlaceholdersCsv.length() - 2); } if (newRecord) { SQLCodeBuilder.append("INSERT INTO " + table.getInternalTableName()); if (fieldsCsv.equals("")) { SQLCodeBuilder.append(" VALUES(default)"); } else { SQLCodeBuilder.append("(" + fieldsCsv + ") VALUES (" + valuePlaceholdersCsv + ")"); } } else { SQLCodeBuilder.append("UPDATE " + table.getInternalTableName() + " SET " + fieldsAndPlaceholdersCsv); if (globalEdit) { // add filter for various row ids SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + " in (?"); for (int i = 1; i < rowIds.size(); i++) { SQLCodeBuilder.append(",?"); } SQLCodeBuilder.append(")"); } else { // add filter for single row id SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + "=?"); } } Connection conn = null; int fieldNumber = 0; // Will be set if we're inserting a record int newRowId = -1; TableDataInfo tableData = new TableData(table); try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); PreparedStatement statement = conn.prepareStatement(SQLCodeBuilder.toString()); for (BaseField field : dataToSave.keySet()) { // If an exception is raised, currentField will be the field // which caused it // currentField = field; fieldNumber++; BaseValue fieldValue = dataToSave.get(field); if (field instanceof FileField) { if (fieldValue.isNull() || fieldValue.toString().equals("")) { throw new InputRecordException("No file specified for the upload", field); } } if (fieldValue.isNull()) { statement.setNull(fieldNumber, Types.NULL); } else { if (fieldValue instanceof TextValue) { String textValue = ((TextValue) fieldValue).toXmlString(); statement.setString(fieldNumber, textValue); } else if (fieldValue instanceof IntegerValue) { // if no related value, set relation field to null if (field instanceof RelationField && (((IntegerValue) fieldValue).getValueInteger() == -1) || (fieldValue.isNull())) { statement.setNull(fieldNumber, Types.NULL); } else { statement.setInt(fieldNumber, ((IntegerValue) fieldValue).getValueInteger()); } } else if (fieldValue instanceof DurationValue) { statement.setString(fieldNumber, ((DurationValue) fieldValue).getSqlFormatInterval()); } else if (fieldValue instanceof DecimalValue) { statement.setDouble(fieldNumber, ((DecimalValue) fieldValue).getValueFloat()); } else if (fieldValue instanceof DateValue) { if (((DateValue) fieldValue).getValueDate() != null) { java.util.Date javaDateValue = ((DateValue) fieldValue).getValueDate().getTime(); java.sql.Timestamp sqlTimestampValue = new java.sql.Timestamp(javaDateValue.getTime()); statement.setTimestamp(fieldNumber, sqlTimestampValue); } else { statement.setTimestamp(fieldNumber, null); } } else if (fieldValue instanceof CheckboxValue) { statement.setBoolean(fieldNumber, ((CheckboxValue) fieldValue).getValueBoolean()); } else if (fieldValue instanceof FileValue) { statement.setString(fieldNumber, ((FileValue) fieldValue).toString()); } else { throw new CodingErrorException("Field value " + fieldValue + " is of unknown type " + fieldValue.getClass().getSimpleName()); } } } // We've finished setting individual fields, if an SQL error occurs // after here we won't know which // field caused it without looking for it by other means // currentField = null; if (!newRecord) { if (globalEdit) { // Fill in the 'WHERE [row id field] in (?,..,?)' for use in // the UPDATE statement for (Integer aRowId : rowIds) { if (tableData.isRecordLocked(conn, sessionData, aRowId)) { throw new CantDoThatException( "Record " + aRowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(++fieldNumber, aRowId); } } else { // Fill in the 'WHERE [row id field]=?' for use in the // UPDATE statement if (tableData.isRecordLocked(conn, sessionData, rowId)) { throw new CantDoThatException( "Record " + rowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(fieldNumber + 1, rowId); } } int numRowsAffected = statement.executeUpdate(); statement.close(); if ((numRowsAffected != 1) && (!globalEdit)) { conn.rollback(); if (numRowsAffected > 0) { throw new ObjectNotFoundException(String.valueOf(numRowsAffected) + " records would be altered during a single record save"); } else { throw new ObjectNotFoundException( "The current record can't be found to edit - perhaps someone else has deleted it"); } } if (newRecord) { // Find the newly inserted Row ID // postgres-specific code, not database independent String SQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + table.getPrimaryKey().getInternalFieldName() + "_seq')"; statement = conn.prepareStatement(SQLCode); ResultSet results = statement.executeQuery(); if (results.next()) { newRowId = results.getInt(1); } else { results.close(); statement.close(); throw new SQLException( "Row ID not found for the newly inserted record. '" + SQLCodeBuilder + "' didn't work"); } results.close(); statement.close(); } conn.commit(); } catch (SQLException sqlex) { // Find out which field caused the error by looking for internal // field names in the error message String errorMessage = sqlex.getMessage(); for (BaseField possibleCauseField : dataToSave.keySet()) { if (errorMessage.contains(possibleCauseField.getInternalFieldName())) { if (errorMessage.contains("check constraint")) { errorMessage = "The value " + dataToSave.get(possibleCauseField) + " falls outside the allowed range"; } else if (errorMessage.contains("not-null constraint")) { errorMessage = "No value entered"; } else if (errorMessage.contains("unique constraint")) { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " is already in the database and cannot be entered again"; } else if (errorMessage.contains("foreign key constraint") && possibleCauseField instanceof RelationField) { errorMessage = "Please select a valid " + ((RelationField) possibleCauseField).getRelatedTable() + " record first"; } else { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " not allowed (" + Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()) + ")"; } throw new InputRecordException(errorMessage, possibleCauseField, sqlex); } } // Not able to find field errorMessage = Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()); throw new InputRecordException(errorMessage, null, sqlex); } finally { if (conn != null) { conn.close(); } } // If any fields were files to upload, do the actual uploads. // Do this after the commit in case the uploads take a long time and // time out the SQL connection. for (BaseField field : dataToSave.keySet()) { if (field instanceof FileField) { try { if (newRecord) { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), newRowId, multipartItems); } else { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), rowId, multipartItems); } } catch (CantDoThatException cdtex) { throw new InputRecordException("Error uploading file: " + cdtex.getMessage(), field, cdtex); } catch (FileUploadException fuex) { throw new InputRecordException("Error uploading file: " + fuex.getMessage(), field, fuex); } } } if (newRecord) { sessionData.setRowId(table, newRowId); } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); AppUserInfo user = null; if (request.getRemoteUser() == null) { user = ServletUtilMethods.getPublicUserForRequest(request, this.authManager.getAuthenticator()); } else { user = this.authManager.getUserByUserName(request, request.getRemoteUser()); } // Send websocket notification // UsageLogger.sendNotification(user, table, sessionData.getReport(), // rowId, "edit", "Record saved: " + dataToSave); // Log everything apart from hidden (auto set) fields Map<BaseField, BaseValue> dataToLog = new LinkedHashMap<BaseField, BaseValue>(); for (Map.Entry<BaseField, BaseValue> entrySet : dataToSave.entrySet()) { BaseField field = entrySet.getKey(); if (!field.getHidden()) { BaseValue value = entrySet.getValue(); dataToLog.put(field, value); } } if (newRecord) { usageLogger.logDataChange(user, table, null, AppAction.SAVE_NEW_RECORD, newRowId, dataToLog.toString()); } else if (globalEdit) { // TODO: need better logging of global edits usageLogger.logDataChange(user, table, null, AppAction.GLOBAL_EDIT, rowId, dataToLog.toString()); } else { BaseField fieldUpdated = null; Set<BaseField> fieldSet = new TreeSet<BaseField>(); for (BaseField field : dataToSave.keySet()) { if (!field.getHidden()) { fieldSet.add(field); } } if (fieldSet.size() == 1) { fieldUpdated = new LinkedList<BaseField>(fieldSet).getFirst(); } usageLogger.logDataChange(user, table, fieldUpdated, AppAction.UPDATE_RECORD, rowId, dataToLog.toString()); } UsageLogger.startLoggingThread(usageLogger); }
From source file:org.sentinel.instrumentationserver.metadata.MetadataDAO.java
/** * Save the metadata from one XML element in the database. *//* ww w. j ava 2s . co m*/ public void saveMetadataFromXmlElement(Node applicationNode) { String LOGO_BASE_URI = "https://f-droid.org/repo/icons/"; String APP_BASE_URI = "https://f-droid.org/repo/"; String sqlStatementGetMetadataFromXml = QueryBuilder.getQueryToSaveMetadataFromXmlElement(); PreparedStatement preparedStatement; try { preparedStatement = databaseConnection.prepareStatement(sqlStatementGetMetadataFromXml); Element applicationNodeElement = null; if (applicationNode instanceof Element) { applicationNodeElement = (Element) applicationNode; } String logo = null; String appName = null; String packageName = null; String appUrl = null; String summary = null; String description = null; String license = null; String appCategory = null; String webLink = null; String sourceCodeLink = null; String marketVersion = null; String sha256Hash = null; double sizeInBytes = 0; String sdkVersion = null; String permissions = null; String features = null; if (applicationNodeElement.getElementsByTagName("icon").item(0) != null) { logo = LOGO_BASE_URI + applicationNodeElement.getElementsByTagName("icon").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("name").item(0) != null) { appName = applicationNodeElement.getElementsByTagName("name").item(0).getTextContent(); } if (applicationNodeElement.getAttribute("id") != null) { packageName = applicationNodeElement.getAttribute("id"); } if (applicationNodeElement.getElementsByTagName("apkname").item(0) != null) { appUrl = APP_BASE_URI + applicationNodeElement.getElementsByTagName("apkname").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("summary").item(0) != null) { summary = applicationNodeElement.getElementsByTagName("summary").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("desc").item(0) != null) { description = applicationNodeElement.getElementsByTagName("desc").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("license").item(0) != null) { license = applicationNodeElement.getElementsByTagName("license").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("category").item(0) != null) { appCategory = applicationNodeElement.getElementsByTagName("category").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("web").item(0) != null) { webLink = applicationNodeElement.getElementsByTagName("web").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("source").item(0) != null) { sourceCodeLink = applicationNodeElement.getElementsByTagName("source").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("marketversion").item(0) != null) { marketVersion = applicationNodeElement.getElementsByTagName("marketversion").item(0) .getTextContent(); } if (applicationNodeElement.getElementsByTagName("hash").item(0) != null) { sha256Hash = applicationNodeElement.getElementsByTagName("hash").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("size").item(0) != null) { sizeInBytes = Double .parseDouble(applicationNodeElement.getElementsByTagName("size").item(0).getTextContent()); } if (applicationNodeElement.getElementsByTagName("sdkver").item(0) != null) { sdkVersion = applicationNodeElement.getElementsByTagName("sdkver").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("permissions").item(0) != null) { permissions = applicationNodeElement.getElementsByTagName("permissions").item(0).getTextContent(); } if (applicationNodeElement.getElementsByTagName("features").item(0) != null) { features = applicationNodeElement.getElementsByTagName("features").item(0).getTextContent(); } byte[] logoBytes = fetchLogo(logo); preparedStatement.setBytes(1, logoBytes); preparedStatement.setString(2, appName); preparedStatement.setString(3, packageName); preparedStatement.setString(4, appUrl); preparedStatement.setString(5, summary); preparedStatement.setString(6, description); preparedStatement.setString(7, license); preparedStatement.setString(8, appCategory); preparedStatement.setString(9, webLink); preparedStatement.setString(10, sourceCodeLink); preparedStatement.setString(11, marketVersion); preparedStatement.setString(12, sha256Hash); preparedStatement.setDouble(13, sizeInBytes); preparedStatement.setString(14, sdkVersion); preparedStatement.setString(15, permissions); preparedStatement.setString(16, features); preparedStatement.setString(17, sha256Hash); preparedStatement.execute(); preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } }