List of usage examples for java.sql PreparedStatement setFloat
void setFloat(int parameterIndex, float x) throws SQLException;
float value. From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. *///ww w . j a v a 2 s.c o m public void setFloat(PreparedStatement stmnt, int idx, float val, Column col) throws SQLException { stmnt.setFloat(idx, val); }
From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
/** * This method bind values to prepared statement. * * @param type data Type/* w ww . j a va2 s .co m*/ * @param value String value * @param ordinalPosition Ordinal Position * @param sqlStatement Statement * @throws SQLException * @throws ParseException * @throws ODataServiceFault */ private void bindValuesToPreparedStatement(int type, String value, int ordinalPosition, PreparedStatement sqlStatement) throws SQLException, ParseException, ODataServiceFault { byte[] data; try { switch (type) { case Types.INTEGER: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setInt(ordinalPosition, ConverterUtil.convertToInt(value)); } break; case Types.TINYINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setByte(ordinalPosition, ConverterUtil.convertToByte(value)); } break; case Types.SMALLINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setShort(ordinalPosition, ConverterUtil.convertToShort(value)); } break; case Types.DOUBLE: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setDouble(ordinalPosition, ConverterUtil.convertToDouble(value)); } break; case Types.VARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.LONGVARCHAR: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setString(ordinalPosition, value); } break; case Types.CLOB: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setClob(ordinalPosition, new BufferedReader(new StringReader(value)), value.length()); } break; case Types.BOOLEAN: /* fall through */ case Types.BIT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setBoolean(ordinalPosition, ConverterUtil.convertToBoolean(value)); } break; case Types.BLOB: /* fall through */ case Types.LONGVARBINARY: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { data = this.getBytesFromBase64String(value); sqlStatement.setBlob(ordinalPosition, new ByteArrayInputStream(data), data.length); } break; case Types.BINARY: /* fall through */ case Types.VARBINARY: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { data = this.getBytesFromBase64String(value); sqlStatement.setBinaryStream(ordinalPosition, new ByteArrayInputStream(data), data.length); } break; case Types.DATE: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setDate(ordinalPosition, DBUtils.getDate(value)); } break; case Types.DECIMAL: /* fall through */ case Types.NUMERIC: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setBigDecimal(ordinalPosition, ConverterUtil.convertToBigDecimal(value)); } break; case Types.FLOAT: /* fall through */ case Types.REAL: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setFloat(ordinalPosition, ConverterUtil.convertToFloat(value)); } break; case Types.TIME: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setTime(ordinalPosition, DBUtils.getTime(value)); } break; case Types.LONGNVARCHAR: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NVARCHAR: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setNString(ordinalPosition, value); } break; case Types.NCLOB: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setNClob(ordinalPosition, new BufferedReader(new StringReader(value)), value.length()); } break; case Types.BIGINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setLong(ordinalPosition, ConverterUtil.convertToLong(value)); } break; case Types.TIMESTAMP: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setTimestamp(ordinalPosition, DBUtils.getTimestamp(value)); } break; default: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setString(ordinalPosition, value); } break; } } catch (DataServiceFault e) { throw new ODataServiceFault(e, "Error occurred while binding values. :" + e.getMessage()); } }
From source file:edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService.java
/** * Fixes the Schema for Database Version 1.2 * @param conn//from w w w . j a v a 2s . c om * @throws Exception */ private boolean doFixesForDBSchemaVersions(final Connection conn, final String databaseName) throws Exception { ///////////////////////////// // PaleoContext ///////////////////////////// getTableNameAndTitleForFrame(PaleoContext.getClassTableId()); Integer len = getFieldLength(conn, databaseName, "paleocontext", "Text1"); alterFieldLength(conn, databaseName, "paleocontext", "Text1", 32, 64); alterFieldLength(conn, databaseName, "paleocontext", "Text2", 32, 64); len = getFieldLength(conn, databaseName, "paleocontext", "Remarks"); if (len == null) { int count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM paleocontext"); int rv = update(conn, "ALTER TABLE paleocontext ADD Remarks VARCHAR(60)"); if (rv != count) { errMsgList.add("Error updating PaleoContext.Remarks"); return false; } } frame.incOverall(); DBConnection dbc = DBConnection.getInstance(); ///////////////////////////// // FieldNotebookPage ///////////////////////////// getTableNameAndTitleForFrame(FieldNotebookPage.getClassTableId()); len = getFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber"); if (len != null && len == 16) { alterFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber", 16, 32); update(conn, "ALTER TABLE fieldnotebookpage ALTER COLUMN ScanDate DROP DEFAULT"); } frame.incOverall(); ///////////////////////////// // Project Table ///////////////////////////// alterFieldLength(conn, databaseName, "project", "projectname", 50, 128); frame.incOverall(); ///////////////////////////// // AttachmentImageAttribute Table ///////////////////////////// if (doesTableExist(databaseName, "attachmentimageattribute")) { alterFieldLength(conn, databaseName, "attachmentimageattribute", "CreativeCommons", 128, 500); frame.incOverall(); } ///////////////////////////// // LocalityDetail ///////////////////////////// String tblName = getTableNameAndTitleForFrame(LocalityDetail.getClassTableId()); boolean statusOK = true; String sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'localitydetail' AND COLUMN_NAME = 'UtmScale' AND DATA_TYPE = 'varchar'", dbc.getDatabaseName()); int count = BasicSQLUtils.getCountAsInt(sql); if (count > 0) { Vector<Object[]> values = query("SELECT ld.LocalityDetailID, ld.UtmScale, l.LocalityName " + "FROM localitydetail ld INNER JOIN locality l ON ld.LocalityID = l.LocalityID WHERE ld.UtmScale IS NOT NULL"); update(conn, "ALTER TABLE localitydetail DROP COLUMN UtmScale"); addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude"); addColumn(conn, databaseName, tblName, "MgrsZone", "VARCHAR(4)", "UtmScale"); HashMap<String, String> badLocalitiesHash = new HashMap<String, String>(); try { PreparedStatement pStmt = conn .prepareStatement("UPDATE localitydetail SET UtmScale=? WHERE LocalityDetailID=?"); for (Object[] row : values) { Integer locDetailId = (Integer) row[0]; String scale = (String) row[1]; String locName = (String) row[2]; scale = StringUtils.contains(scale, ',') ? StringUtils.replace(scale, ",", "") : scale; if (!StringUtils.isNumeric(scale)) { badLocalitiesHash.put(locName, scale); continue; } float scaleFloat = 0.0f; try { scaleFloat = Float.parseFloat(scale); } catch (NumberFormatException ex) { badLocalitiesHash.put(locName, scale); continue; } pStmt.setFloat(1, scaleFloat); pStmt.setInt(2, locDetailId); pStmt.execute(); } pStmt.close(); } catch (SQLException ex) { statusOK = false; } if (badLocalitiesHash.size() > 0) { try { File file = new File( UIRegistry.getUserHomeDir() + File.separator + "localitydetailerrors.html"); TableWriter tblWriter = new TableWriter(file.getAbsolutePath(), "Locality Detail Errors"); tblWriter.startTable(); tblWriter.logHdr(new String[] { "Locality Name", "Scale" }); for (String key : badLocalitiesHash.keySet()) { tblWriter.log(key, badLocalitiesHash.get(key)); } tblWriter.endTable(); tblWriter.flush(); tblWriter.close(); UIRegistry.showLocalizedError("LOC_DETAIL_ERRORS", badLocalitiesHash.size(), file.getAbsoluteFile()); badLocalitiesHash.clear(); if (file.exists()) { try { AttachmentUtils.openURI(file.toURI()); } catch (Exception ex) { ex.printStackTrace(); } } } catch (IOException ex) { ex.printStackTrace(); } } } else { addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude"); } frame.incOverall(); ////////////////////////////////////////////// // collectingeventattribute Schema 1.3 ////////////////////////////////////////////// DBMSUserMgr dbmsMgr = DBMSUserMgr.getInstance(); if (dbmsMgr.connectToDBMS(itUserNamePassword.first, itUserNamePassword.second, dbc.getServerName())) { boolean status = true; Connection connection = dbmsMgr.getConnection(); try { // Add New Fields to Determination tblName = getTableNameAndTitleForFrame(Determination.getClassTableId()); addColumn(conn, databaseName, tblName, "VarQualifier", "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER Qualifier"); addColumn(conn, databaseName, tblName, "SubSpQualifier", "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER VarQualifier"); frame.incOverall(); // CollectingEventAttributes sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collectingeventattribute' AND COLUMN_NAME = 'CollectionMemberID'", dbc.getDatabaseName()); count = BasicSQLUtils.getCountAsInt(sql); connection.setCatalog(dbc.getDatabaseName()); //int numCEAttrs = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute"); if (count > 0) { HashMap<Integer, Integer> collIdToDispIdHash = new HashMap<Integer, Integer>(); sql = "SELECT UserGroupScopeId, DisciplineID FROM collection"; for (Object[] cols : query(sql)) { Integer colId = (Integer) cols[0]; Integer dspId = (Integer) cols[1]; collIdToDispIdHash.put(colId, dspId); } count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute"); IdMapperMgr.getInstance().setDBs(connection, connection); IdTableMapper mapper = new IdTableMapper("ceattrmapper", "id", "SELECT CollectingEventAttributeID, CollectionMemberID FROM collectingeventattribute", true, false); mapper.setFrame(frame); mapper.mapAllIdsNoIncrement(count > 0 ? count : null); Statement stmt = null; try { getTableNameAndTitleForFrame(CollectingEventAttribute.getClassTableId()); stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); update(conn, "DROP INDEX COLEVATSColMemIDX on collectingeventattribute"); update(conn, "ALTER TABLE collectingeventattribute DROP COLUMN CollectionMemberID"); update(conn, "ALTER TABLE collectingeventattribute ADD COLUMN DisciplineID int(11)"); update(conn, "CREATE INDEX COLEVATSDispIDX ON collectingeventattribute(DisciplineID)"); double inc = count > 0 ? (100.0 / (double) count) : 0; double cnt = 0; int percent = 0; frame.setProcess(0, 100); frame.setProcessPercent(true); PreparedStatement pStmt = conn.prepareStatement( "UPDATE collectingeventattribute SET DisciplineID=? WHERE CollectingEventAttributeID=?"); ResultSet rs = stmt .executeQuery("SELECT CollectingEventAttributeID FROM collectingeventattribute"); while (rs.next()) { Integer ceAttrId = rs.getInt(1); Integer oldColId = mapper.get(ceAttrId); if (oldColId != null) { Integer dispId = collIdToDispIdHash.get(oldColId); if (dispId != null) { pStmt.setInt(1, dispId); pStmt.setInt(2, ceAttrId); pStmt.execute(); } else { log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId + "] ceAttrId[" + ceAttrId + "]"); } } else { log.debug("Error getting mapped Collection ID[" + oldColId + "] ceAttrId[" + ceAttrId + "]"); } cnt += inc; if (((int) cnt) > percent) { percent = (int) cnt; frame.setProcess(percent); } } rs.close(); pStmt.close(); frame.setProcess(100); } catch (SQLException ex) { ex.printStackTrace(); } finally { if (stmt != null) stmt.close(); } mapper.cleanup(); } frame.incOverall(); //----------------------------- // Collectors //----------------------------- sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collector' AND COLUMN_NAME = 'CollectionMemberID'", dbc.getDatabaseName()); count = BasicSQLUtils.getCountAsInt(sql); if (count > 0) { HashMap<Integer, Integer> collIdToDivIdHash = new HashMap<Integer, Integer>(); sql = "SELECT c.UserGroupScopeId, d.DivisionID FROM collection c INNER JOIN discipline d ON c.DisciplineID = d.UserGroupScopeId"; for (Object[] cols : query(sql)) { Integer colId = (Integer) cols[0]; Integer divId = (Integer) cols[1]; collIdToDivIdHash.put(colId, divId); } count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collector"); IdMapperMgr.getInstance().setDBs(connection, connection); IdTableMapper mapper = new IdTableMapper("collectormap", "id", "SELECT CollectorID, CollectionMemberID FROM collector", true, false); mapper.setFrame(frame); mapper.mapAllIdsNoIncrement(count > 0 ? count : null); getTableNameAndTitleForFrame(Collector.getClassTableId()); Statement stmt = null; try { stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); update(conn, "DROP INDEX COLTRColMemIDX on collector"); update(conn, "ALTER TABLE collector DROP COLUMN CollectionMemberID"); update(conn, "ALTER TABLE collector ADD COLUMN DivisionID INT(11)"); update(conn, "CREATE INDEX COLTRDivIDX ON collector(DivisionID)"); double inc = count > 0 ? (100.0 / (double) count) : 0; double cnt = 0; int percent = 0; frame.setProcess(0, 100); frame.setProcessPercent(true); PreparedStatement pStmt = conn .prepareStatement("UPDATE collector SET DivisionID=? WHERE CollectorID=?"); ResultSet rs = stmt.executeQuery("SELECT CollectorID FROM collector"); while (rs.next()) { Integer coltrId = rs.getInt(1); Integer oldColId = mapper.get(coltrId); if (oldColId != null) { Integer divId = collIdToDivIdHash.get(oldColId); if (divId != null) { pStmt.setInt(1, divId); pStmt.setInt(2, coltrId); pStmt.execute(); } else { log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId + "]"); } } else { log.debug("Error getting mapped Collector ID[" + oldColId + "]"); } cnt += inc; if (((int) cnt) > percent) { percent = (int) cnt; frame.setProcess(percent); } } rs.close(); pStmt.close(); frame.setProcess(100); } catch (SQLException ex) { ex.printStackTrace(); } finally { if (stmt != null) stmt.close(); } mapper.cleanup(); frame.incOverall(); } } catch (Exception ex) { ex.printStackTrace(); } finally { frame.getProcessProgress().setIndeterminate(true); frame.setDesc("Loading updated schema..."); if (!status) { //UIRegistry.showLocalizedError("SCHEMA_UPDATE_ERROR", errMsgStr); JTextArea ta = UIHelper.createTextArea(); ta.setText(errMsgStr); CellConstraints cc = new CellConstraints(); PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g", "f:p:g")); pb.add(new JScrollPane(ta, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED), cc.xy(1, 1)); pb.setDefaultDialogBorder(); CustomDialog dlg = new CustomDialog((Frame) UIRegistry.getTopWindow(), getResourceString("SCHEMA_UPDATE_ERROR"), true, pb.getPanel()); UIHelper.centerAndShow(dlg); } dbmsMgr.close(); } } return statusOK; }
From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Set the Statement column so that the results are mapped correctly. * * @param statement the prepared statement * @param position the position of the column * @param value the value of the column// w w w.ja va 2 s. c o m */ public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception { if (value instanceof String) { value = ((String) value).trim(); } if (typeObject == null) { throw new SQLException("Can't set column because the type is unrecognized"); } if (value == null) { /** If the value is null, set the column value null and return **/ statement.setNull(position, typeObject.intValue()); return; } if ("".equals(value)) { switch (typeObject.intValue()) { case Types.CHAR: case Types.CLOB: case Types.VARCHAR: /** If the value is an empty string and the column is a string type, we can continue **/ break; default: /** If the value is an empty string and the column is something else, we treat it as a null value **/ statement.setNull(position, typeObject.intValue()); return; } } File file = null; int length = -1; InputStream asciiStream = null; //System.out.println("========================================================================"); //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue()); switch (typeObject.intValue()) { case Types.CLOB: //System.out.println("CLOB"); Clob clob = null; if (value instanceof Clob) { clob = (Clob) value; } else if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; clob = new ClobHelper(asciiStream, length); } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new ByteArrayInputStream(asciiText.getBytes()); length = asciiText.length(); clob = new ClobHelper(asciiStream, length); } statement.setClob(position, clob); break; case Types.CHAR: // simple large object, e.g. Informix's TEXT //System.out.println("CHAR"); if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes())); length = asciiText.length(); } statement.setAsciiStream(position, asciiStream, length); break; case Types.BIGINT: //System.out.println("BIGINT"); BigDecimal bd = null; if (value instanceof BigDecimal) { bd = (BigDecimal) value; } else if (value instanceof Number) { bd = BigDecimal.valueOf(((Number) value).longValue()); } else { bd = new BigDecimal(value.toString()); } statement.setBigDecimal(position, bd); break; case Types.TINYINT: //System.out.println("TINYINT"); Byte b = null; if (value instanceof Byte) { b = (Byte) value; } else if (value instanceof Number) { b = new Byte(((Number) value).byteValue()); } else { b = new Byte(value.toString()); } statement.setByte(position, b.byteValue()); break; case Types.DATE: //System.out.println("DATE"); Date d = null; if (value instanceof Date) { d = (Date) value; } else if (value instanceof java.util.Date) { d = new Date(((java.util.Date) value).getTime()); } else if (value instanceof Calendar) { d = new Date(((Calendar) value).getTime().getTime()); } else { d = Date.valueOf(value.toString()); } statement.setDate(position, d); break; case Types.DOUBLE: //System.out.println("DOUBLE"); double db; if (value instanceof Number) { db = (((Number) value).doubleValue()); } else { db = Double.parseDouble(value.toString()); } statement.setDouble(position, db); break; case Types.FLOAT: //System.out.println("FLOAT"); float f; if (value instanceof Number) { f = (((Number) value).floatValue()); } else { f = Float.parseFloat(value.toString()); } statement.setFloat(position, f); break; case Types.NUMERIC: //System.out.println("NUMERIC"); long l; if (value instanceof Number) { l = (((Number) value).longValue()); } else { l = Long.parseLong(value.toString()); } statement.setLong(position, l); break; case Types.SMALLINT: //System.out.println("SMALLINT"); Short s = null; if (value instanceof Short) { s = (Short) value; } else if (value instanceof Number) { s = new Short(((Number) value).shortValue()); } else { s = new Short(value.toString()); } statement.setShort(position, s.shortValue()); break; case Types.TIME: //System.out.println("TIME"); Time t = null; if (value instanceof Time) { t = (Time) value; } else if (value instanceof java.util.Date) { t = new Time(((java.util.Date) value).getTime()); } else { t = Time.valueOf(value.toString()); } statement.setTime(position, t); break; case Types.TIMESTAMP: //System.out.println("TIMESTAMP"); Timestamp ts = null; if (value instanceof Time) { ts = (Timestamp) value; } else if (value instanceof java.util.Date) { ts = new Timestamp(((java.util.Date) value).getTime()); } else { ts = Timestamp.valueOf(value.toString()); } statement.setTimestamp(position, ts); break; case Types.ARRAY: //System.out.println("ARRAY"); statement.setArray(position, (Array) value); // no way to convert string to array break; case Types.STRUCT: //System.out.println("STRUCT"); case Types.OTHER: //System.out.println("OTHER"); statement.setObject(position, value); break; case Types.LONGVARBINARY: //System.out.println("LONGVARBINARY"); statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime())); break; case Types.VARCHAR: //System.out.println("VARCHAR"); statement.setString(position, value.toString()); break; case Types.BLOB: //System.out.println("BLOB"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else { Blob blob = null; if (value instanceof Blob) { blob = (Blob) value; } else if (value instanceof File) { file = (File) value; blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof String) { file = new File((String) value); blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof Part) { Part anyFile = (Part) value; blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize()); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); statement.setBlob(position, blob); } break; case Types.VARBINARY: //System.out.println("VARBINARY"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else if (value instanceof Part) { statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize()); } else { if (value instanceof File) { file = (File) value; } else if (value instanceof String) { file = new File((String) value); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); FileInputStream input = new FileInputStream(file); statement.setBinaryStream(position, input, (int) file.length()); } break; case Types.INTEGER: //System.out.println("INTEGER"); Integer i = null; if (value instanceof Integer) { i = (Integer) value; } else if (value instanceof Number) { i = new Integer(((Number) value).intValue()); } else { i = new Integer(value.toString()); } statement.setInt(position, i.intValue()); break; case Types.BIT: //System.out.println("BIT"); Boolean bo = null; if (value instanceof Boolean) { bo = (Boolean) value; } else if (value instanceof Number) { bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1); } else { bo = BooleanUtils.toBooleanObject(value.toString()); } statement.setBoolean(position, bo.booleanValue()); break; default: //System.out.println("default"); throw new SQLException("Impossible exception - invalid type "); } //System.out.println("========================================================================"); }
From source file:at.alladin.rmbt.statisticServer.StatisticsResource.java
private PreparedStatement selectProviders(final Connection conn, final boolean group, final float quantile, final int durationDays, final double accuracy, final String country, final boolean useMobileProvider, final String where, final boolean signalMobile) throws SQLException { PreparedStatement ps; String sql = String.format("SELECT" + (group ? " p.name, p.shortname, " : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(signal_strength::bigint, ?::double precision) quantile_signal," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping," + " sum((speed_download >= ?)::int)::double precision / count(speed_download) down_green," + " sum((speed_download < ? and speed_download >= ?)::int)::double precision / count(speed_download) down_yellow," + " sum((speed_download < ?)::int)::double precision / count(speed_download) down_red," + " sum((speed_upload >= ?)::int)::double precision / count(speed_upload) up_green," + " sum((speed_upload < ? and speed_upload >= ?)::int)::double precision / count(speed_upload) up_yellow," + " sum((speed_upload < ?)::int)::double precision / count(speed_upload) up_red," + " sum((signal_strength >= ?)::int)::double precision / count(signal_strength) signal_green," + " sum((signal_strength < ? and signal_strength >= ?)::int)::double precision / count(signal_strength) signal_yellow," + " sum((signal_strength < ?)::int)::double precision / count(signal_strength) signal_red," + " sum((ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_green," + " sum((ping_shortest > ? and ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_yellow," + " sum((ping_shortest > ?)::int)::double precision / count(ping_shortest) ping_red" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " JOIN provider p ON" + (useMobileProvider ? " t.mobile_provider_id = p.uid" : " t.provider_id = p.uid") + " WHERE %s" + ((country != null && useMobileProvider) ? " AND t.network_sim_country = ?" : "") + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND t.time > NOW() - CAST(? AS INTERVAL)" + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY p.uid" : "") + " ORDER BY count DESC", where); if (country != null) { sql = String.format("SELECT" + ((group && useMobileProvider) ? " p.name AS name, p.shortname AS shortname, p.mccmnc AS sim_mcc_mnc, " : "") + ((group && !useMobileProvider) ? " public_ip_as_name AS name, public_ip_as_name AS shortname, t.public_ip_asn AS asn, " : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(signal_strength::bigint, ?::double precision) quantile_signal," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping," + " sum((speed_download >= ?)::int)::double precision / count(speed_download) down_green," + " sum((speed_download < ? and speed_download >= ?)::int)::double precision / count(speed_download) down_yellow," + " sum((speed_download < ?)::int)::double precision / count(speed_download) down_red," + " sum((speed_upload >= ?)::int)::double precision / count(speed_upload) up_green," + " sum((speed_upload < ? and speed_upload >= ?)::int)::double precision / count(speed_upload) up_yellow," + " sum((speed_upload < ?)::int)::double precision / count(speed_upload) up_red," + " sum((signal_strength >= ?)::int)::double precision / count(signal_strength) signal_green," + " sum((signal_strength < ? and signal_strength >= ?)::int)::double precision / count(signal_strength) signal_yellow," + " sum((signal_strength < ?)::int)::double precision / count(signal_strength) signal_red," + " sum((ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_green," + " sum((ping_shortest > ? and ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_yellow," + " sum((ping_shortest > ?)::int)::double precision / count(ping_shortest) ping_red" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + (useMobileProvider ? " LEFT JOIN mccmnc2name p ON p.uid = t.mobile_sim_id" : "") + " WHERE %s" + " AND " + (useMobileProvider/*from w w w . j av a2 s . c o m*/ ? "p.country = ? AND ((t.country_location IS NULL OR t.country_location = ?) AND (NOT t.roaming_type = 2))" : "t.country_geoip = ? ") + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND t.time > NOW() - CAST(? AS INTERVAL)" + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + ((group && (useMobileProvider)) ? " GROUP BY p.uid, p.mccmnc" : "") + ((group && (!useMobileProvider)) ? " GROUP BY t.public_ip_as_name, t.public_ip_asn" : "") + " ORDER BY count DESC", where); } ps = conn.prepareStatement(sql); int i = 1; for (int j = 0; j < 3; j++) ps.setFloat(i++, quantile); ps.setFloat(i++, 1 - quantile); // inverse for ping final int[] td = classification.THRESHOLD_DOWNLOAD; ps.setInt(i++, td[0]); ps.setInt(i++, td[0]); ps.setInt(i++, td[1]); ps.setInt(i++, td[1]); final int[] tu = classification.THRESHOLD_UPLOAD; ps.setInt(i++, tu[0]); ps.setInt(i++, tu[0]); ps.setInt(i++, tu[1]); ps.setInt(i++, tu[1]); final int[] ts = signalMobile ? classification.THRESHOLD_SIGNAL_MOBILE : classification.THRESHOLD_SIGNAL_WIFI; ps.setInt(i++, ts[0]); ps.setInt(i++, ts[0]); ps.setInt(i++, ts[1]); ps.setInt(i++, ts[1]); final int[] tp = classification.THRESHOLD_PING; ps.setInt(i++, tp[0]); ps.setInt(i++, tp[0]); ps.setInt(i++, tp[1]); ps.setInt(i++, tp[1]); if (country != null) { if (useMobileProvider) { ps.setString(i++, country.toLowerCase()); //mccmnc2name.country ps.setString(i++, country.toUpperCase()); //country_location } else { ps.setString(i++, country.toUpperCase()); } } ps.setString(i++, String.format("%d days", durationDays)); if (accuracy > 0) { ps.setDouble(i++, accuracy); } System.out.println(ps); return ps; }
From source file:SeedGenerator.MainForm.java
private void jButtonProfilerActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonProfilerActionPerformed try {/*from www.ja v a 2 s. co m*/ // System.out.println(searchEngine.name + " ilk aramaya girdi: " + (new Timestamp(System.currentTimeMillis())).toString()); String url; try { String SQL = "SELECT user, sum(connect) c FROM profiler.tumkayit group by user;"; PreparedStatement selectstmt = con.prepareStatement(SQL); ResultSet rs = selectstmt.executeQuery(); while (rs.next()) { String user = rs.getString("user"); float sumofconnect = (float) rs.getInt("c"); String SQLs = "SELECT mainCategory, user , sum(connect) as c FROM profiler.tumkayit where user = ? and\n" + "domain NOT like '%duba.net%' and domain NOT like '%sahibinden.com%' and domain NOT like '%vine.co%' and domain NOT like '%mediafire.com%' and domain NOT like '%filmindirmobil.com%' and domain NOT like '%twitch.tv%' and domain NOT like '%ttvnw.net%' and domain NOT like '%hurriyet.com.tr%' and domain NOT like '%gstatic.com%' and domain NOT like '%google%' and domain NOT like '%4sqi.net%' and domain NOT like '%doubleclick.net%' and domain NOT like '%clipconverter.cc%' and domain NOT like '%spotify.com%' and domain NOT like '%microsoft.com%' and domain NOT like '%vk.me%' and domain NOT like '%apple.com%' and domain NOT like '%dailymotion.com%' and domain NOT like '%mncdn.com%' and domain NOT like '%windowsupdate.com%' and domain NOT like '%instagram.com%' and domain NOT like '%mail.ru%'and domain NOT like '%google.com%' and domain NOT like '%googlevideo.com%' and mainCategory != 'Unknown' and mainCategory != 'Empty Site or Under Construction' and mainCategory != 'Search Engines' and mainCategory != 'Advertisements' \n" + "group by maincategory, user order by c desc;"; PreparedStatement selectstmts = con.prepareStatement(SQLs); selectstmts.setString(1, user); ResultSet rss = selectstmts.executeQuery(); int i = 1; PreparedStatement selectstmt12; String SQL12 = "INSERT INTO profiler.analiz2 (user,category,freq,ord) values (?,?,?,?);"; selectstmt12 = con.prepareStatement(SQL12); while (rss.next()) { selectstmt12.setString(1, rss.getString("user")); selectstmt12.setString(2, rss.getString("mainCategory")); selectstmt12.setFloat(3, ((float) rss.getInt("c")) / sumofconnect); selectstmt12.setInt(4, i); selectstmt12.addBatch(); i++; } selectstmt12.executeBatch(); } } catch (Exception ex) { System.out.println(ex.getMessage()); } // webClient.closeAllWindows(); } catch (Exception ex) { String x = ex.getMessage(); try { System.out.println(" hataya girdi 60 saniye bekleyecek. " + x); Thread.sleep(60000); } catch (Exception eex) { } } // try { // // System.out.println(searchEngine.name + " ilk aramaya girdi: " + (new Timestamp(System.currentTimeMillis())).toString()); // String url; // try { // String SQL = "SELECT idprofilerurl,url,category FROM profiler.profilerurl where mainCategory is null;"; // PreparedStatement selectstmt = con.prepareStatement(SQL); // ResultSet rs = selectstmt.executeQuery(); // // while (rs.next()) { // if (rs.getString("category").split(";")[0].contains("\r")) { // System.out.println(""); // } // String SQL12 = "UPDATE profiler.profilerurl set mainCategory=? where idprofilerurl = ?;"; // PreparedStatement selectstmt12 = con.prepareStatement(SQL12); // selectstmt12.setString(1, rs.getString("category").split(";")[0].replaceAll("\r", "")); // selectstmt12.setInt(2, rs.getInt("idprofilerurl")); // selectstmt12.executeUpdate(); // // } // } catch (Exception ex) { // System.out.println(ex.getMessage()); // } // // // webClient.closeAllWindows(); // } catch (Exception ex) { // String x = ex.getMessage(); // try { // System.out.println(" hataya girdi 60 saniye bekleyecek. " + x); // Thread.sleep(60000); // } catch (Exception eex) { // } // } // // jButtonProfiler.setBackground(Color.GREEN); // // int crawlId = createCrawl(getQueryText(), "SearchEngineCrawler"); // // maxPage = Integer.decode(txtMaxPage.getText()); // // threadArray = new Thread[maxPage]; // // // List selist = lstSearchEngines.getSelectedValuesList(); // // jProgressBar1.setMaximum(maxPage * selist.size()); // int i = 0; //// for(i= 0;i<20;i++) //// { // //// WorkerProfilerQueue sworker = new WorkerProfilerQueue(this, i); //// sworker.setName("Worker "+i); //// //threadArray[i++] = sworker; //// threadsSearchQueue[i] = sworker; //// sworker.start(); // WorkerProfilerQueue sworker = new WorkerProfilerQueue(i); // sworker.setName("Worker 1"); // sworker.start(); //// WorkerProfilerQueue sworker1 = new WorkerProfilerQueue( i); //// sworker1.setName("Worker 2"); //// sworker1.start(); //// WorkerProfilerQueue sworker2 = new WorkerProfilerQueue( i); //// sworker2.setName("Worker 3"); //// sworker2.start(); //// WorkerProfilerQueue sworker3 = new WorkerProfilerQueue(i); //// sworker3.setName("Worker 4"); //// sworker3.start(); //// WorkerProfilerQueue sworker4 = new WorkerProfilerQueue(i); //// sworker4.setName("Worker 5"); //// sworker4.start(); //// } // try { // // Thread.sleep(300); // } catch (Exception ex) { // }// search(getSearchEngineFromName((String) se)); // // TODO add your handling code here: }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Set a properties data for inserts or updates * * @param insert perform insert or update? * @param prop current property * @param allData all data of the instance (might be needed to buld references, etc.) * @param con an open and valid connection * @param data current property data * @param ps prepared statement for the data table * @param ft fulltext indexer * @param upperColumnPos position of the uppercase column (if present, else <code>-1</code>) * @param includeFullText add fulltext entries? Will be skipped for position only changes * @throws SQLException on errors * @throws FxUpdateException on errors * @throws FxDbException on errors * @throws FxNoAccessException for FxNoAccess values *//*from w w w.ja v a 2 s .co m*/ private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con, FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos, boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException { FxValue value = data.getValue(); if (value instanceof FxNoAccess) throw new FxNoAccessException("ex.content.value.noaccess"); if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) { if (((FxPropertyAssignment) data.getAssignment()).isMultiLang()) throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi", data.getXPathFull()); else throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single", data.getXPathFull()); } int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2; int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS; final FxEnvironment env = CacheAdmin.getEnvironment(); if (prop.getDataType().isSingleRowStorage()) { //Data types that just use one db row can be handled in a very similar way Object translatedValue; GregorianCalendar gc = null; final long[] translatedLanguages = value.getTranslatedLanguages(); for (long translatedLanguage : translatedLanguages) { translatedValue = value.getTranslation(translatedLanguage); if (translatedValue == null) { LOG.warn("Translation for " + data.getXPath() + " is null!"); } ps.setLong(pos_lang, translatedLanguage); if (!value.isMultiLanguage()) ps.setBoolean(pos_isdef_lang, true); else ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage)); if (upperColumnPos != -1) { final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale() : Locale.getDefault(); ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale)); } int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop); switch (prop.getDataType()) { case Double: checkDataType(FxDouble.class, value, data.getXPathFull()); ps.setDouble(pos[0], (Double) translatedValue); break; case Float: checkDataType(FxFloat.class, value, data.getXPathFull()); ps.setFloat(pos[0], (Float) translatedValue); break; case LargeNumber: checkDataType(FxLargeNumber.class, value, data.getXPathFull()); ps.setLong(pos[0], (Long) translatedValue); break; case Number: checkDataType(FxNumber.class, value, data.getXPathFull()); ps.setInt(pos[0], (Integer) translatedValue); break; case HTML: checkDataType(FxHTML.class, value, data.getXPathFull()); boolean useTidy = ((FxHTML) value).isTidyHTML(); ps.setBoolean(pos[1], useTidy); final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue); if (useTidy) { translatedValue = extractorInput; } final HtmlExtractor result = new HtmlExtractor(extractorInput, true); setBigString(ps, pos[2], result.getText()); setBigString(ps, pos[0], (String) translatedValue); break; case String1024: case Text: checkDataType(FxString.class, value, data.getXPathFull()); setBigString(ps, pos[0], (String) translatedValue); break; case Boolean: checkDataType(FxBoolean.class, value, data.getXPathFull()); ps.setBoolean(pos[0], (Boolean) translatedValue); break; case Date: checkDataType(FxDate.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); //strip all time information, this might not be necessary since ps.setDate() strips them //for most databases but won't hurt either ;) gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); break; case DateTime: checkDataType(FxDateTime.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); break; case DateRange: checkDataType(FxDateRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis())); break; case DateTimeRange: checkDataType(FxDateTimeRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis())); break; case Binary: checkDataType(FxBinary.class, value, data.getXPathFull()); BinaryDescriptor binary = (BinaryDescriptor) translatedValue; if (!binary.isNewBinary()) { ps.setLong(pos[0], binary.getId()); } else { try { //transfer the binary from the transit table to the binary table BinaryDescriptor created = binaryStorage.binaryTransit(con, binary); ps.setLong(pos[0], created.getId()); //check all other properties if they contain the same handle //and replace with the data of the new binary for (FxData _curr : allData) { if (_curr instanceof FxPropertyData && !_curr.isEmpty() && ((FxPropertyData) _curr).getValue() instanceof FxBinary) { FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue(); _val._replaceHandle(binary.getHandle(), created); } } } catch (FxApplicationException e) { throw new FxDbException(e); } } break; case SelectOne: checkDataType(FxSelectOne.class, value, data.getXPathFull()); ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId()); break; case SelectMany: checkDataType(FxSelectMany.class, value, data.getXPathFull()); SelectMany sm = (SelectMany) translatedValue; for (int i1 = 0; i1 < sm.getSelected().size(); i1++) { FxSelectListItem item = sm.getSelected().get(i1); if (i1 > 0) { if (batchContentDataChanges()) ps.addBatch(); else ps.executeUpdate(); } ps.setLong(pos[0], item.getId()); ps.setString(pos[1], sm.getSelectedIdsList()); ps.setLong(pos[2], sm.getSelectedIds().size()); } if (sm.getSelected().size() == 0) ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row break; case Reference: //reference integrity check is done prior to saving ps.setLong(pos[0], ((FxPK) translatedValue).getId()); break; case InlineReference: default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType()) : getValueDataUpdatePos(prop.getDataType()); if (value.hasValueData(translatedLanguage)) { ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage)); } else ps.setNull(valueDataPos, Types.NUMERIC); if (batchContentDataChanges()) ps.addBatch(); else { try { ps.executeUpdate(); } catch (SQLException e) { LOG.error(prop.getName(), e); throw e; } } } } else { switch (prop.getDataType()) { //TODO: implement datatype specific insert default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } } if (ft != null && prop.isFulltextIndexed() && includeFullText) ft.index(data); }
From source file:br.com.cobranca.util.Util.java
public static <T> boolean alterarRegistro(T objAlterado, Class<T> classe, Connection con, String strWhere) throws Exception, SQLException { if (strWhere == null || strWhere.trim().equals("")) { return false; }//from w w w . java 2 s .c o m PreparedStatement ps = null; ResultSet rs = null; T objOriginal = classe.newInstance(); try { // Recuperar objeto original no banco de dados String nomeTabela = objAlterado.getClass().getSimpleName(); String strSql = "SELECT * FROM " + nomeTabela + " " + strWhere; ps = con.prepareStatement(strSql); rs = ps.executeQuery(); if (rs.next()) { objOriginal = Util.atribuirValores(classe, rs); } else { return false; } rs.close(); ps.close(); // Comparar valores dos dois objetos strSql = "UPDATE " + nomeTabela + " SET "; boolean efetuarAlteracao; boolean usarVirgula = false; for (Field field : objAlterado.getClass().getDeclaredFields()) { efetuarAlteracao = false; String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else { return false; } if (efetuarAlteracao) { if (usarVirgula) { strSql = strSql + ", "; //usarVirgula = false; } strSql = strSql + nomeColuna + " = ? "; usarVirgula = true; } } //Se no houve alterao, retorna falso if (!strSql.contains("?")) { return true; } strSql = strSql + strWhere; ps = con.prepareStatement(strSql); int i = 1; // ps.set?() for (Field field : objAlterado.getClass().getDeclaredFields()) { String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setInt(i, valorAlterado); } i++; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { ps.setString(i, valorAlterado); i++; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDouble(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setFloat(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setLong(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setBoolean(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDate(i, new java.sql.Date(valorAlterado.getTime())); } i++; } } else { return false; } } // fim int qtdLinhasAfetadas = ps.executeUpdate(); if (qtdLinhasAfetadas <= 0) { return false; } } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } } return true; }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public void importData(String txt, int collectionID) throws ParseException { studySummaryCache.clear();/* w ww. j av a 2s.c o m*/ collectionSummaryCache.clear(); Collection<Record> rcs = new ArrayList<Record>(); int cpos = 0; int len = txt.length(); int ln = 0; char colSeparator = '\0'; if (!txt.startsWith(SAMPLE_ID_COL)) { if (!txt.startsWith("\"" + SAMPLE_ID_COL + "\"")) throw new ParseException(1, "The first column must be " + SAMPLE_ID_COL); colSeparator = txt.charAt(SAMPLE_ID_COL.length() + 2); } else colSeparator = txt.charAt(SAMPLE_ID_COL.length()); if (colSeparator != '\t' && colSeparator != ',') throw new ParseException(1, "Column separator must be either tab or comma"); String sep = "" + colSeparator; String lineSep = "\r\n"; if (txt.indexOf(lineSep) == -1) lineSep = "\n"; if (txt.indexOf(lineSep) == -1) throw new ParseException(1, "File must contains at least 2 lines separated by either \\n or \\r\\n "); Map<ParameterPart, List<Variant>> tmpVarisMap = new TreeMap<ParameterPart, List<Variant>>(); // String qSep = "\""+sep; List<String> parts = new ArrayList<String>(100); FullPartRef[] pparts = null; while (cpos < len) { // ln++; // System.out.println("Line: "+ln); int pos = txt.indexOf(lineSep, cpos); if (pos == -1) break; parts.clear(); StringUtil.splitExcelString(txt.substring(cpos, pos), sep, parts); // String[] parts = txt.substring(cpos, pos).split(sep,-2); if (ln == 0) { ln++; pparts = analyzeHeader(parts); } else { ln++; // String[] row = new String[header.length]; if (parts.size() == 0) break; Record rc = new Record(); rc.setCollectionId(collectionID); for (int i = -1; i < pparts.length; i++) { if (i == -1) rc.setCollectionRecordIDs(parts.get(0)); else { String val = ""; if (i + 1 < parts.size()) val = parts.get(i + 1).trim(); ParameterPart pp = pparts[i].getParameterPart(); if (val.length() != 0) { if (pp.isEnum()) { short vid = pp.getVariantIndexByValue(val); // short vid = pparts[i].getParameterPart().getVariantID(val); if (vid == -1) { if (pp.isPredefined()) throw new ParseException(ln, "Variant '" + val + "' is not allowed for column: '" + pparts[i].getParameter().getCode() + '.' + pp.getName() + "'"); List<Variant> tmpVaris = tmpVarisMap.get(pp); short n = -1; if (tmpVaris == null) { tmpVaris = new ArrayList<Variant>(5); tmpVarisMap.put(pp, tmpVaris); } else { n = 1; for (Variant v : tmpVaris) { if (v.getName().equals(val)) { v.incCount(); break; } n++; } if (n > tmpVaris.size()) n = -1; } if (n < 0) { Variant nV = new Variant(val, 0, false); nV.setId(0); nV.incCount(); tmpVaris.add(nV); n = (short) tmpVaris.size(); } vid = (short) -n; } VariantPartValue pv = new VariantPartValue(pp); pv.setVariant(vid); rc.addPartValue(pv); } else { if (ParameterPart.SECURED_VARIANT_SIGN.equals(val)) { PartValue pv = new PartValue(pp); rc.addPartValue(pv); } else { Variable vrbl = (Variable) pp; if (vrbl.getType() == Type.REAL) { float realValue; try { realValue = Float.parseFloat(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for REAL type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } RealPartValue rpv = new RealPartValue(pp); rpv.setRealValue(realValue); rc.addPartValue(rpv); } else if (vrbl.getType() == Type.INTEGER || vrbl.getType() == Type.DATE) { int intValue; try { intValue = Integer.parseInt(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } IntPartValue ipv = new IntPartValue(pp); ipv.setIntValue(intValue); rc.addPartValue(ipv); } else if (vrbl.getType() == Type.BOOLEAN) { boolean boolValue; try { boolValue = Boolean.parseBoolean(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } if ((!boolValue) && "1".equals(val)) boolValue = true; IntPartValue ipv = new IntPartValue(pp); ipv.setIntValue(boolValue ? 1 : 0); rc.addPartValue(ipv); } else throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } } } else { PartValue pv = new EmptyPartValue(pp); rc.addPartValue(pv); } } } rcs.add(rc); } cpos = pos + lineSep.length(); } Connection conn = null; ResultSet rst = null; try { conn = dSrc.getConnection(); PreparedStatement recstmt = conn.prepareStatement("INSERT INTO " + TBL_RECORD + " (" + FLD_COLLECTION_RECORD_ID + "," + FLD_COUNT + "," + FLD_COLLECTION_ID + ") VALUES (?,1,?)", Statement.RETURN_GENERATED_KEYS); PreparedStatement contstmt = conn .prepareStatement("INSERT INTO " + TBL_RECORD_CONTENT + " (" + FLD_INT_VALUE + "," + FLD_REAL_VALUE + "," + FLD_RECORD_ID + "," + FLD_PART_ID + ") VALUES (?,?,?,?)"); PreparedStatement updcontstmt = conn .prepareStatement("UPDATE " + TBL_RECORD_CONTENT + " SET " + FLD_INT_VALUE + "=?," + FLD_REAL_VALUE + "=? WHERE " + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?"); PreparedStatement rmcontstmt = conn.prepareStatement("DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?"); PreparedStatement insvarstmt = conn .prepareStatement( "INSERT INTO " + TBL_VARIANT + " (" + FLD_PART_ID + ',' + FLD_NAME + ',' + FLD_VARI_CODING + ',' + FLD_PREDEFINED + ") VALUES (?,?,0,0)", Statement.RETURN_GENERATED_KEYS); PreparedStatement delRecStmt = null; PreparedStatement delRecContStmt = null; for (Record r : rcs) { Record exR = findRecord(r); int rid = 0; if (exR == null) { if (r.getPartValues().size() > 0) { recstmt.setString(1, r.getCollectionRecordIDs()); recstmt.setInt(2, collectionID); recstmt.executeUpdate(); rst = recstmt.getGeneratedKeys(); if (rst.next()) rid = rst.getInt(1); r.setId(rid); data.add(r); } } else { rid = exR.getId(); if (r.getPartValues().size() == 0) { if (delRecStmt == null) { delRecStmt = conn .prepareStatement("DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?"); delRecContStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?"); } delRecStmt.setInt(1, rid); delRecStmt.executeUpdate(); delRecContStmt.setInt(1, rid); delRecContStmt.executeUpdate(); data.remove(exR); for (PartValue pv : exR.getPartValues()) { if (pv instanceof VariantPartValue) pv.getPart().uncountVariantByIndex(((VariantPartValue) pv).getVariant()); } continue; } } for (PartValue pv : r.getPartValues()) { PartValue opv = exR != null ? exR.getPartValue(pv.getPartID()) : null; if (pv instanceof EmptyPartValue) { if (opv != null) { exR.removePartValue(opv); rmcontstmt.setInt(1, rid); rmcontstmt.setInt(2, pv.getPartID()); rmcontstmt.executeUpdate(); if (opv instanceof VariantPartValue) opv.getPart().uncountVariantByIndex(((VariantPartValue) opv).getVariant()); if (exR.getPartValues().size() == 0) { if (delRecStmt == null) { delRecStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?"); delRecContStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?"); } delRecStmt.setInt(1, exR.getId()); delRecStmt.executeUpdate(); delRecContStmt.setInt(1, exR.getId()); delRecContStmt.executeUpdate(); data.remove(exR); exR = null; } } } else { PreparedStatement stmt = null; if (opv != null) stmt = updcontstmt; else stmt = contstmt; stmt.setInt(3, rid); stmt.setInt(4, pv.getPartID()); if (pv instanceof IntPartValue) { stmt.setInt(1, ((IntPartValue) pv).getIntValue()); stmt.setNull(2, java.sql.Types.FLOAT); } else if (pv instanceof RealPartValue) { stmt.setNull(1, java.sql.Types.INTEGER); stmt.setFloat(2, ((RealPartValue) pv).getRealValue()); } else if (pv instanceof VariantPartValue) { short vidx = ((VariantPartValue) pv).getVariant(); Variant tv = null; if (vidx < 0) { List<Variant> tmpVaris = tmpVarisMap.get(pv.getPart()); tv = tmpVaris.get((-vidx) - 1); if (tv.getId() == 0) { insvarstmt.setInt(1, pv.getPartID()); insvarstmt.setString(2, tv.getName()); insvarstmt.executeUpdate(); ResultSet vrst = insvarstmt.getGeneratedKeys(); vrst.next(); tv.setId(vrst.getInt(1)); vrst.close(); short nidx = pv.getPart().addVariant(tv); tv.setCoding(nidx); ((VariantPartValue) pv).setVariant(nidx); } else ((VariantPartValue) pv).setVariant((short) tv.getCoding()); } else { tv = pv.getPart().getVariant(vidx); tv.incCount(); } stmt.setInt(1, tv.getId()); stmt.setNull(2, java.sql.Types.FLOAT); } else { stmt.setNull(1, java.sql.Types.INTEGER); stmt.setNull(2, java.sql.Types.FLOAT); } stmt.executeUpdate(); if (opv != null) exR.removePartValue(opv); if (exR != null) exR.addPartValue(pv); } } if (exR != null) exR.completeRecord(); else r.completeRecord(); } recstmt.close(); contstmt.close(); updcontstmt.close(); rmcontstmt.close(); insvarstmt.close(); for (List<Variant> tvl : tmpVarisMap.values()) for (Variant tv : tvl) tv.setCoding(0); Statement stmt = conn.createStatement(); stmt.executeUpdate("UPDATE " + TBL_COLLECTION + " SET " + FLD_LAST_UPDATE + "=" + System.currentTimeMillis() + " WHERE " + FLD_ID + "=" + collectionID); stmt.close(); Collections.sort(data, RecordComparator.getIntstance()); prepareCounts(); // TODO count only parameter with new data } catch (SQLException e) { Log.error("SQL error", e); throw new ParseException(0, "SQL error"); } catch (Exception e1) { logger.error("Data import error", e1); throw new ParseException(0, "Unknown error: " + e1.getMessage()); } finally { if (rst != null) try { rst.close(); } catch (SQLException e) { } if (conn != null) try { conn.close(); } catch (SQLException e) { Log.error("Connection closing error", e); } } }
From source file:com.ws.WS_TCS201.java
@Path("/Act") @JSONP(queryParam = "callback") @POST// www . jav a 2 s .c om @Produces({ "application/x-javascript" }) public String Act(@QueryParam("callback") String callback, @FormParam("state") String state, @FormParam("com") String com, @FormParam("emp") String emp, @FormParam("tcd") String tcd, @FormParam("year") String year, @FormParam("strdate") String strdate, @FormParam("enddate") String enddate, @FormParam("days") String days, @FormParam("comment") String comment, @FormParam("oldstrdate") String oldstrdate, @Context HttpServletResponse servletResponse) { JSONObject obj1 = new JSONObject(); LinkedList l1 = new LinkedList(); String cSQL = ""; int yeartkb = 0, yeartkt = 0, cnttkb = 0, cnttkt = 0; float cntdays = 0, allowdays = 0, tmplst = 0; String tmptck = "-"; String msg = ""; String yearStartDate = ""; PreparedStatement prepStmt = null; try { if (!state.equals("D") && (msg.equals(""))) { // if (state.equals("I")) { oldstrdate = "0"; } //? cSQL = " SELECT tceemp, tceapd, tceall, tcetkb, tcetkt FROM TCSTCE " + " WHERE tcecom= ? AND tceemp= ? " + " ORDER BY tceapd DESC"; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); ResultSet result = prepStmt.executeQuery(); if (result.next()) { // Object obj = result.getObject(3); allowdays = Integer.parseInt(obj.toString()); obj = result.getObject(4); yeartkb = Integer.parseInt(obj.toString()); obj = result.getObject(5); yeartkt = Integer.parseInt(obj.toString()); //*************************************************************** if (msg.equals("")) { obj = result.getObject(2); yearStartDate = obj.toString(); if (Integer.parseInt(yearStartDate) <= 20100913) { //2010.9.13?? 01/01 if ((Integer.parseInt(strdate.substring(4)) < Integer.parseInt("0101")) && (Integer.parseInt(enddate.substring(4)) >= Integer.parseInt("0101"))) { msg = "C"; } } else { //2010.9.13? ?? yearStartDate = year + yearStartDate.substring(4); if ((Integer.parseInt(strdate) < Integer.parseInt(yearStartDate)) && (Integer.parseInt(enddate) >= Integer.parseInt(yearStartDate))) { msg = "C"; } } } } else { msg = "H"; } // if ((Integer.parseInt(strdate) > Integer.parseInt(enddate)) && (msg.equals(""))) { msg = "A"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorA.html"); //return "N"; } //??***************************************************** if ((state.equals("I") || (state.equals("U") && !strdate.equals(oldstrdate))) && (msg.equals(""))) { cSQL = "SELECT * FROM TCSTCH " + "WHERE TCHCOM=? AND TCHEMP=? AND " + " ?<=TCHDED AND ?>=TCHDST "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); //prepStmt.setString(3, tcd); prepStmt.setString(3, strdate); prepStmt.setString(4, enddate); result = prepStmt.executeQuery(); if (result.next()) { msg = "B"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorB.html"); //return "N"; } } //************************************************************* /* if ((Integer.parseInt(strdate.substring(0,4)) != Integer.parseInt(enddate.substring(0,4))) && (msg.equals(""))) { msg = "C:??"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorC.html"); //return "N"; }*/ //<=- SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); Calendar c1 = Calendar.getInstance(); Calendar c2 = Calendar.getInstance(); c1.setTime(sdf.parse(strdate)); c2.setTime(sdf.parse(enddate)); cntdays = c2.get(Calendar.DATE) - c1.get(Calendar.DATE) + 1; if ((Integer.parseInt(days) > cntdays) && (msg.equals(""))) { msg = "E"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorE.html"); //return "N"; } //?? if (tcd.equals("B") || tcd.equals("T")) { cSQL = " SELECT SUM(CASE WHEN tchtcd = \"B\" THEN 1 ELSE 0 END ) AS sumtkb," + " SUM(CASE WHEN tchtcd = \"T\" AND tchtck = \"A\" THEN 1 WHEN tchtcd = \"T\" AND tchtck = \"B\" THEN 2 ELSE 0 END ) AS sumtkt " + " FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? AND tchyer = ? AND tchdst <> ? AND tchtcd IN (\"B\",\"T\") AND tchtck NOT IN (\"-\",\"0\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, strdate); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(1); if (obj != null) { cnttkb = Integer.parseInt(obj.toString()); } obj = result.getObject(2); if (obj != null) { cnttkt = Integer.parseInt(obj.toString()); } } if (cnttkb > yeartkb || cnttkt > yeartkt) { tmptck = "0"; } } else if (tcd.equals("M") && (msg.equals(""))) { cSQL = " SELECT * FROM TCSTCH " + " WHERE tchcom = ? AND tchemp= ? AND tchtcd = ? AND tchtck NOT IN (\"-\",\"X\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, tcd); result = prepStmt.executeQuery(); if (result.next()) { msg = "F"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorF.html"); //return "N"; } } else if (tcd.equals("F")) { cSQL = " SELECT COUNT(*) AS times FROM TCSTCH " + " WHERE tchcom = ? AND tchemp= ? AND tchyer = ? AND tchtcd = ? AND tchtck NOT IN (\"-\",\"X\") "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, tcd); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(1); if (obj != null && Integer.parseInt(obj.toString()) == 1) { tmptck = "X"; } } } //?******************************************************** if (tcd.equals("C") && (msg.equals(""))) { cSQL = " SELECT tchtcd, sum(tchday) AS tchday FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? " + " AND tchyer = ? AND tchdst <> ? " + " AND tchtcd = \"C\" " + " GROUP BY tchemp "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, oldstrdate); result = prepStmt.executeQuery(); if (!result.wasNull()) { Object obj = result.getObject(2); cntdays = Integer.parseInt(obj.toString()) + Integer.parseInt(days); if (cntdays > 5) { msg = "G"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorG.html"); //return "N"; } } } if (msg.equals("")) { //<=? cSQL = " SELECT * FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? " + " AND tchyer = ? AND tchdst <> ? " + " ORDER BY tchdst DESC "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, year); prepStmt.setString(4, oldstrdate); result = prepStmt.executeQuery(); if (result.next()) { Object obj = result.getObject(9); tmplst = Float.parseFloat(obj.toString()); if (state.equals("U")) { obj = result.getObject(8); tmplst = tmplst + Integer.parseInt(obj.toString()); } tmplst = tmplst - Integer.parseInt(days); } else { tmplst = allowdays - Integer.parseInt(days); } if (tmplst < 0) { msg = "D"; //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorD.html"); //return "N"; } } } if (!msg.equals("")) { obj1.put("Msg", msg); return obj1.toString(); } // DateFormat day = new SimpleDateFormat("yyyyMMdd"); String upddate = day.format(new java.util.Date()); DateFormat time = new SimpleDateFormat("HHmmss"); String updtime = time.format(new java.util.Date()); if (state.equals("I")) { cSQL = " INSERT INTO TCSTCH VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setInt(2, Integer.parseInt(year)); prepStmt.setString(3, emp); prepStmt.setString(4, tcd); prepStmt.setFloat(5, Float.parseFloat(String.valueOf(allowdays))); prepStmt.setInt(6, Integer.parseInt(strdate)); prepStmt.setInt(7, Integer.parseInt(enddate)); prepStmt.setFloat(8, Float.parseFloat(days)); prepStmt.setFloat(9, tmplst); prepStmt.setString(10, tmptck); prepStmt.setString(11, ""); prepStmt.setString(12, comment); prepStmt.setString(13, "WS_TCS201"); prepStmt.setString(14, "TEST"); prepStmt.setInt(15, Integer.parseInt(upddate)); prepStmt.setInt(16, Integer.parseInt(updtime)); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SI"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorI.html"); //return "N"; } } else if (state.equals("U")) { cSQL = " UPDATE TCSTCH " + " SET tchdst = ?, " + " tchded = ?, " + " tchday = ?, " + " tchlst = ?, " + " tchtxt = ?, " + " tchpg = ?, " + " tchus = ?, " + " tchdt = ?, " + " tchtm = ? " + " WHERE tchcom = ? AND tchemp = ? AND tchtcd = ? AND tchdst = ? "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setInt(1, Integer.parseInt(strdate)); prepStmt.setInt(2, Integer.parseInt(enddate)); prepStmt.setFloat(3, Float.parseFloat(days)); prepStmt.setFloat(4, tmplst); prepStmt.setString(5, comment); prepStmt.setString(6, "WS_TCS201"); prepStmt.setString(7, "TEST"); prepStmt.setInt(8, Integer.parseInt(upddate)); prepStmt.setInt(9, Integer.parseInt(updtime)); prepStmt.setString(10, com); prepStmt.setString(11, emp); prepStmt.setString(12, tcd); prepStmt.setString(13, oldstrdate); String tmptest = prepStmt.toString(); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SU"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorU.html"); //return "N"; } } else if (state.equals("D")) { cSQL = " DELETE FROM TCSTCH " + " WHERE tchcom = ? AND tchemp = ? AND tchtcd = ? AND tchdst = ? "; prepStmt = connection.prepareStatement(cSQL); prepStmt.setString(1, com); prepStmt.setString(2, emp); prepStmt.setString(3, tcd); prepStmt.setString(4, oldstrdate); if (prepStmt.executeUpdate() == 0) { obj1.put("Msg", "SD"); return obj1.toString(); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_ErrorD.html"); //return "N"; } } obj1.put("Msg", "SY"); //servletResponse.sendRedirect("/RestApache/Pages/TCS211_Success.html"); } catch (SQLException e) { prepStmt = null; e.printStackTrace(); } catch (Exception e) { prepStmt = null; e.printStackTrace(); } return obj1.toString(); //return "Y"; }