List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:lcn.module.batch.core.item.database.support.MethodMapItemPreparedStatementSetter.java
/** * params ? ? sqlType PreparedStatement? ?? *///from ww w. j a v a 2 s. c om public void setValues(T item, PreparedStatement ps, String[] params, String[] sqlTypes, Map<String, Method> methodMap) throws SQLException { ReflectionSupport<T> reflector = new ReflectionSupport<T>(); for (int i = 0; i < params.length; i++) { try { if (sqlTypes[i].equals("String")) { ps.setString(i + 1, (String) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("int")) { ps.setInt(i + 1, (Integer) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("double")) { ps.setDouble(i + 1, (Double) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Date")) { ps.setDate(i + 1, (Date) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte")) { ps.setByte(i + 1, (Byte) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("short")) { ps.setShort(i + 1, (Short) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("boolean")) { ps.setBoolean(i + 1, (Boolean) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("long")) { ps.setLong(i + 1, (Long) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Float")) { ps.setFloat(i + 1, (Float) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("BigDecimal")) { ps.setBigDecimal(i + 1, (BigDecimal) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte[]")) { ps.setBytes(i + 1, (byte[]) reflector.invokeGettterMethod(item, params[i], methodMap)); } else { throw new SQLException(); } } catch (IllegalArgumentException e) { ReflectionUtils.handleReflectionException(e); } } }
From source file:egovframework.rte.bat.core.item.database.support.EgovMethodMapItemPreparedStatementSetter.java
/** * params ? ? sqlType PreparedStatement? ?? *///from w w w. j av a 2s .c om public void setValues(T item, PreparedStatement ps, String[] params, String[] sqlTypes, Map<String, Method> methodMap) throws SQLException { EgovReflectionSupport<T> reflector = new EgovReflectionSupport<T>(); for (int i = 0; i < params.length; i++) { try { if (sqlTypes[i].equals("String")) { ps.setString(i + 1, (String) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("int")) { ps.setInt(i + 1, (Integer) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("double")) { ps.setDouble(i + 1, (Double) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Date")) { ps.setDate(i + 1, (Date) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte")) { ps.setByte(i + 1, (Byte) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("short")) { ps.setShort(i + 1, (Short) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("boolean")) { ps.setBoolean(i + 1, (Boolean) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("long")) { ps.setLong(i + 1, (Long) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("Float")) { ps.setFloat(i + 1, (Float) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("BigDecimal")) { ps.setBigDecimal(i + 1, (BigDecimal) reflector.invokeGettterMethod(item, params[i], methodMap)); } else if (sqlTypes[i].equals("byte[]")) { ps.setBytes(i + 1, (byte[]) reflector.invokeGettterMethod(item, params[i], methodMap)); } else { throw new SQLException(); } } catch (IllegalArgumentException e) { ReflectionUtils.handleReflectionException(e); } } }
From source file:com.concursive.connect.web.modules.productcatalog.beans.OrderBean.java
/** * Description of the Method//from w ww . jav a 2s . c om * * @param db Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public boolean insert(Connection db) throws SQLException { try { db.setAutoCommit(false); // Insert the base order PreparedStatement pst = db.prepareStatement("INSERT INTO customer_order " + "(ipaddress, browser, total_price, order_by) VALUES (?,?,?,?) "); int i = 0; pst.setString(++i, ipAddress); pst.setString(++i, browser); pst.setDouble(++i, productList.getTotalPrice()); DatabaseUtils.setInt(pst, ++i, userId); pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "customer_order_order_id_seq", -1); // Insert the products productList.setOrderId(id); productList.insert(db); // Insert the contact info if (contactInformation.isValid()) { contactInformation.setOrderId(id); contactInformation.insert(db); } // Insert the addresses if (billing.isValid()) { billing.setOrderId(id); billing.insert(db); } if (shipping.isValid()) { shipping.setOrderId(id); shipping.insert(db); } // Insert the payment info if (payment.isValid()) { payment.setOrderId(id); payment.setChargeAmount(getChargeAmount()); payment.insert(db); } db.commit(); // Finalize saved = true; return true; } catch (Exception e) { db.rollback(); LOG.error("insert", e); throw new SQLException("Could not save"); } finally { db.setAutoCommit(true); } }
From source file:at.alladin.rmbt.statisticServer.export.ExportResource.java
@Get public Representation request(final String entity) { //Before doing anything => check if a cached file already exists and is new enough String property = System.getProperty("java.io.tmpdir"); final String filename_zip; final String filename_csv; //allow filtering by month/year int year = -1; int month = -1; int hours = -1; boolean hoursExport = false; boolean dateExport = false; if (getRequest().getAttributes().containsKey("hours")) { // export by hours try {// w w w.j a v a2 s .com hours = Integer.parseInt(getRequest().getAttributes().get("hours").toString()); } catch (NumberFormatException ex) { //Nothing -> just fall back } if (hours <= 7 * 24 && hours >= 1) { //limit to 1 week (avoid DoS) hoursExport = true; } } else if (!hoursExport && getRequest().getAttributes().containsKey("year")) { // export by month/year try { year = Integer.parseInt(getRequest().getAttributes().get("year").toString()); month = Integer.parseInt(getRequest().getAttributes().get("month").toString()); } catch (NumberFormatException ex) { //Nothing -> just fall back } if (year < 2099 && month > 0 && month <= 12 && year > 2000) { dateExport = true; } } if (hoursExport) { filename_zip = FILENAME_ZIP_HOURS.replace("%HOURS%", String.format("%03d", hours)); filename_csv = FILENAME_CSV_HOURS.replace("%HOURS%", String.format("%03d", hours)); cacheThresholdMs = 5 * 60 * 1000; //5 minutes } else if (dateExport) { filename_zip = FILENAME_ZIP.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%", String.format("%02d", month)); filename_csv = FILENAME_CSV.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%", String.format("%02d", month)); cacheThresholdMs = 23 * 60 * 60 * 1000; //23 hours } else { filename_zip = FILENAME_ZIP_CURRENT; filename_csv = FILENAME_CSV_CURRENT; cacheThresholdMs = 3 * 60 * 60 * 1000; //3 hours } final File cachedFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv)); final File generatingFile = new File( property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp"); if (cachedFile.exists()) { //check if file has been recently created OR a file is currently being created if (((cachedFile.lastModified() + cacheThresholdMs) > (new Date()).getTime()) || (generatingFile.exists() && (generatingFile.lastModified() + cacheThresholdMs) > (new Date()).getTime())) { //if so, return the cached file instead of a cost-intensive new one final OutputRepresentation result = new OutputRepresentation( zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) { @Override public void write(OutputStream out) throws IOException { InputStream is = new FileInputStream(cachedFile); IOUtils.copy(is, out); out.close(); } }; if (zip) { final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT); disposition.setFilename(filename_zip); result.setDisposition(disposition); } return result; } } final String timeClause; if (dateExport) timeClause = " AND (EXTRACT (month FROM t.time AT TIME ZONE 'UTC') = " + month + ") AND (EXTRACT (year FROM t.time AT TIME ZONE 'UTC') = " + year + ") "; else if (hoursExport) timeClause = " AND time > now() - interval '" + hours + " hours' "; else timeClause = " AND time > current_date - interval '31 days' "; final String sql = "SELECT" + " ('P' || t.open_uuid) open_uuid," + " ('O' || t.open_test_uuid) open_test_uuid," + " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') time_utc," + " nt.group_name cat_technology," + " nt.name network_type," + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_lat" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_lat*1111)/1111" + " ELSE null" + " END) lat," + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_long" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_long*741)/741 " + " ELSE null" + " END) long," + " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN" + " 'rastered'" + //make raster transparent " ELSE t.geo_provider" + " END) loc_src," + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') " + " THEN round(t.geo_accuracy::float * 10)/10 " + " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100" + // limit accuracy to 100m " WHEN (t.geo_accuracy < ?) THEN round(t.geo_accuracy::float * 10)/10" + " ELSE null END) loc_accuracy, " + " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code," + " t.gkz gkz," + " t.country_location country_location," + " t.speed_download download_kbit," + " t.speed_upload upload_kbit," + " round(t.ping_median::float / 100000)/10 ping_ms," + " t.lte_rsrp," + " t.lte_rsrq," + " ts.name server_name," + " duration test_duration," + " num_threads," + " t.plattform platform," + " COALESCE(adm.fullname, t.model) model," + " client_software_version client_version," + " network_operator network_mcc_mnc," + " network_operator_name network_name," + " network_sim_operator sim_mcc_mnc," + " nat_type," + " public_ip_asn asn," + " client_public_ip_anonymized ip_anonym," + " (ndt.s2cspd*1000)::int ndt_download_kbit," + " (ndt.c2sspd*1000)::int ndt_upload_kbit," + " COALESCE(t.implausible, false) implausible," + " t.signal_strength" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id" + " WHERE " + " t.deleted = false" + timeClause + " AND status = 'FINISHED'" + " ORDER BY t.uid"; final String[] columns; final List<String[]> data = new ArrayList<>(); PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); //insert filter for accuracy double accuracy = Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT")); ps.setDouble(1, accuracy); ps.setDouble(2, accuracy); ps.setDouble(3, accuracy); ps.setDouble(4, accuracy); ps.setDouble(5, accuracy); ps.setDouble(6, accuracy); if (!ps.execute()) return null; rs = ps.getResultSet(); final ResultSetMetaData meta = rs.getMetaData(); final int colCnt = meta.getColumnCount(); columns = new String[colCnt]; for (int i = 0; i < colCnt; i++) columns[i] = meta.getColumnName(i + 1); while (rs.next()) { final String[] line = new String[colCnt]; for (int i = 0; i < colCnt; i++) { final Object obj = rs.getObject(i + 1); line[i] = obj == null ? null : obj.toString(); } data.add(line); } } catch (final SQLException e) { e.printStackTrace(); return null; } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); } catch (final SQLException e) { e.printStackTrace(); } } final OutputRepresentation result = new OutputRepresentation( zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) { @Override public void write(OutputStream out) throws IOException { //cache in file => create temporary temporary file (to // handle errors while fulfilling a request) String property = System.getProperty("java.io.tmpdir"); final File cachedFile = new File( property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp"); OutputStream outf = new FileOutputStream(cachedFile); if (zip) { final ZipOutputStream zos = new ZipOutputStream(outf); final ZipEntry zeLicense = new ZipEntry("LIZENZ.txt"); zos.putNextEntry(zeLicense); final InputStream licenseIS = getClass().getResourceAsStream("DATA_LICENSE.txt"); IOUtils.copy(licenseIS, zos); licenseIS.close(); final ZipEntry zeCsv = new ZipEntry(filename_csv); zos.putNextEntry(zeCsv); outf = zos; } final OutputStreamWriter osw = new OutputStreamWriter(outf); final CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat); for (final String c : columns) csvPrinter.print(c); csvPrinter.println(); for (final String[] line : data) { for (final String f : line) csvPrinter.print(f); csvPrinter.println(); } csvPrinter.flush(); if (zip) outf.close(); //if we reach this code, the data is now cached in a temporary tmp-file //so, rename the file for "production use2 //concurrency issues should be solved by the operating system File newCacheFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv)); Files.move(cachedFile.toPath(), newCacheFile.toPath(), StandardCopyOption.ATOMIC_MOVE, StandardCopyOption.REPLACE_EXISTING); FileInputStream fis = new FileInputStream(newCacheFile); IOUtils.copy(fis, out); fis.close(); out.close(); } }; if (zip) { final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT); disposition.setFilename(filename_zip); result.setDisposition(disposition); } return result; }
From source file:org.wso2.carbon.device.mgt.core.device.details.mgt.dao.impl.DeviceDetailsDAOImpl.java
@Override public void addDeviceLocation(DeviceLocation deviceLocation, int enrollmentId) throws DeviceDetailsMgtDAOException { Connection conn;//from w w w. jav a2s. com PreparedStatement stmt = null; try { conn = this.getConnection(); stmt = conn.prepareStatement("INSERT INTO DM_DEVICE_LOCATION (DEVICE_ID, LATITUDE, LONGITUDE, STREET1, " + "STREET2, CITY, ZIP, STATE, COUNTRY, GEO_HASH, UPDATE_TIMESTAMP, ENROLMENT_ID) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setInt(1, deviceLocation.getDeviceId()); stmt.setDouble(2, deviceLocation.getLatitude()); stmt.setDouble(3, deviceLocation.getLongitude()); stmt.setString(4, deviceLocation.getStreet1()); stmt.setString(5, deviceLocation.getStreet2()); stmt.setString(6, deviceLocation.getCity()); stmt.setString(7, deviceLocation.getZip()); stmt.setString(8, deviceLocation.getState()); stmt.setString(9, deviceLocation.getCountry()); stmt.setString(10, GeoHashGenerator.encodeGeohash(deviceLocation)); stmt.setLong(11, System.currentTimeMillis()); stmt.setInt(12, enrollmentId); stmt.execute(); } catch (SQLException e) { throw new DeviceDetailsMgtDAOException("Error occurred while adding the device location to database.", e); } finally { DeviceManagementDAOUtil.cleanupResources(stmt, null); } }
From source file:it.attocchi.db.JdbcConnector.java
@Deprecated public boolean executeStored(boolean keepConnOpen, String storedName, Object... params) throws Exception { boolean res = false; try {/* ww w. j a v a2s .c om*/ String paramSign = StringUtils.repeat("?", ",", params.length); String query = "EXEC " + storedName + " " + paramSign; // res = getConnection().prepareStatement(aQuery).executeQuery(); PreparedStatement ps = getConnection().prepareStatement(query); // ps.setEscapeProcessing(true); for (int i = 0; i < params.length; i++) { Object param = params[i]; if (param instanceof String) ps.setString(i + 1, params[i].toString()); else if (param instanceof Integer) { ps.setInt(i + 1, Integer.parseInt(params[i].toString())); } else if (param instanceof Double) { ps.setDouble(i + 1, Double.parseDouble(params[i].toString())); } else if (param instanceof Float) { ps.setDouble(i + 1, Float.parseFloat(params[i].toString())); } } logger.debug(ps); res = ps.execute(); } finally { if (!keepConnOpen) close(); } return res; }
From source file:nl.nn.adapterframework.statistics.jdbc.StatisticsKeeperStore.java
private void applyParam(PreparedStatement stmt, int pos, double value) throws SQLException { if (Double.isNaN(value)) { if (trace && log.isDebugEnabled()) log.debug("pos [" + pos + "] set double param [" + value + "], setting to NULL"); stmt.setNull(pos, Types.DOUBLE); } else {/*from w w w. j a v a 2s . c om*/ if (trace && log.isDebugEnabled()) log.debug("pos [" + pos + "] set double param [" + value + "]"); stmt.setDouble(pos, value); } }
From source file:org.geotoolkit.index.tree.manager.postgres.LucenePostgresSQLTreeEltMapper.java
@Override public void setTreeIdentifier(final NamedEnvelope env, final int treeIdentifier) throws IOException { try {//from w w w. j ava2 s . com if (env != null) { final PreparedStatement existStmt = conn .prepareStatement("SELECT \"id\" FROM \"" + schemaName + "\".\"records\" WHERE \"id\"=?"); existStmt.setInt(1, treeIdentifier); final ResultSet rs = existStmt.executeQuery(); final boolean exist = rs.next(); rs.close(); existStmt.close(); if (exist) { final PreparedStatement stmt = conn.prepareStatement("UPDATE \"" + schemaName + "\".\"records\" " + "SET \"identifier\"=?, \"nbenv\"=?, \"minx\"=?, \"maxx\"=?, \"miny\"=?, \"maxy\"=? " + "WHERE \"id\"=?"); stmt.setString(1, env.getId()); stmt.setInt(2, env.getNbEnv()); stmt.setDouble(3, env.getMinimum(0)); stmt.setDouble(4, env.getMaximum(0)); stmt.setDouble(5, env.getMinimum(1)); stmt.setDouble(6, env.getMaximum(1)); stmt.setInt(7, treeIdentifier); try { stmt.executeUpdate(); } finally { stmt.close(); } // conn.commit(); } else { final PreparedStatement stmt = conn.prepareStatement( "INSERT INTO \"" + schemaName + "\".\"records\" " + "VALUES (?, ?, ?, ?, ?, ?, ?)"); stmt.setInt(1, treeIdentifier); stmt.setString(2, env.getId()); stmt.setInt(3, env.getNbEnv()); stmt.setDouble(4, env.getMinimum(0)); stmt.setDouble(5, env.getMaximum(0)); stmt.setDouble(6, env.getMinimum(1)); stmt.setDouble(7, env.getMaximum(1)); try { stmt.executeUpdate(); } finally { stmt.close(); } // conn.commit(); } } else { final PreparedStatement remove = conn .prepareStatement("DELETE FROM \"" + schemaName + "\".\"records\" WHERE \"id\"=?"); remove.setInt(1, treeIdentifier); try { remove.executeUpdate(); } finally { remove.close(); } // conn.commit(); } } catch (SQLException ex) { throw new IOException("Error while setting tree identifier for envelope :" + env, ex); } }
From source file:com.spvp.dal.MySqlDatabase.java
@Override public Boolean ucitajGradoveUBazu(ArrayList<Grad> gradovi) throws SQLException { Connection conn = null;/* w ww . ja v a 2s. com*/ Boolean status = false; try { conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO gradovi (ime, longitude, latitude, veci_centar)" + "VALUES(?,?,?,?)"); for (Grad x : gradovi) { pstmt.clearParameters(); pstmt.setString(1, x.getImeGrada()); pstmt.setDouble(2, x.getLongitude()); pstmt.setDouble(3, x.getLatitude()); pstmt.setBoolean(4, x.getVeciCentar()); pstmt.addBatch(); } pstmt.executeBatch(); status = true; } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); } finally { if (conn != null) conn.close(); } return status; }
From source file:org.jboss.dashboard.dataset.sql.SQLStatement.java
/** * Get a JDBC prepared statement representing the SQL sentence. *///from w w w . j av a2 s . c o m public PreparedStatement getPreparedStatement(Connection connection) throws SQLException { PreparedStatement preparedStatement = connection.prepareStatement(SQLSentence); int psParamIndex = 1; Iterator paramIt = SQLParameters.iterator(); while (paramIt.hasNext()) { Object param = paramIt.next(); if (param instanceof String) preparedStatement.setString(psParamIndex, (String) param); else if (param instanceof Date) preparedStatement.setTimestamp(psParamIndex, new Timestamp(((Date) param).getTime())); else if (param instanceof Float) preparedStatement.setFloat(psParamIndex, ((Float) param).floatValue()); else if (param instanceof Double) preparedStatement.setDouble(psParamIndex, ((Double) param).doubleValue()); else if (param instanceof Number) preparedStatement.setLong(psParamIndex, ((Number) param).longValue()); else if (param instanceof Boolean) preparedStatement.setBoolean(psParamIndex, ((Boolean) param).booleanValue()); else if (param instanceof LabelInterval) preparedStatement.setString(psParamIndex, ((LabelInterval) param).getLabel()); psParamIndex++; } return preparedStatement; }