List of usage examples for java.sql PreparedStatement setByte
void setByte(int parameterIndex, byte x) throws SQLException;
byte value. 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 . j a v a2 s .c om*/ */ 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:com.zimbra.cs.db.DbMailItem.java
/** * Return the conversation ids corresponds to messages with given cutoff time. * @param folder//w w w. j a v a 2s . c om * @param messageSyncStart * @param type * @param descending * @param older * @return * @throws ServiceException */ public static TypedIdList listConvItems(Folder folder, long messageSyncStart, MailItem.Type type, boolean descending, boolean older) throws ServiceException { Mailbox mbox = folder.getMailbox(); assert Db.supports(Db.Capability.ROW_LEVEL_LOCKING) || Thread.holdsLock(mbox); TypedIdList result = new TypedIdList(); DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; ResultSet rs = null; try { if (older) { stmt = conn.prepareStatement( "SELECT parent_id FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + " type = ? AND date < ?" + " ORDER BY date" + (descending ? " DESC" : "")); } else { stmt = conn.prepareStatement( "SELECT parent_id FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + " type = ? AND date >= ?" + " ORDER BY date" + (descending ? " DESC" : "")); } int pos = 1; pos = setMailboxId(stmt, mbox, pos); stmt.setByte(pos++, MailItem.Type.MESSAGE.toByte()); //message's parent_id is always conversation.. stmt.setLong(pos++, messageSyncStart); rs = stmt.executeQuery(); while (rs.next()) { int id = rs.getInt(1); if (id != 0 && !result.contains(id)) { result.add(MailItem.Type.CONVERSATION, id, ""); } } return result; } catch (SQLException e) { throw ServiceException.FAILURE("fetching item list for folder " + folder.getId(), e); } finally { DbPool.closeResults(rs); DbPool.closeStatement(stmt); } }
From source file:com.zimbra.cs.db.DbMailItem.java
public static TypedIdList listItems(Folder folder, long messageSyncStart, MailItem.Type type, boolean descending, boolean older) throws ServiceException { Mailbox mbox = folder.getMailbox();/*from w w w .ja va 2s . co m*/ assert Db.supports(Db.Capability.ROW_LEVEL_LOCKING) || Thread.holdsLock(mbox); TypedIdList result = new TypedIdList(); DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; ResultSet rs = null; try { if (older) { stmt = conn.prepareStatement("SELECT id, type, uuid FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + " type = ? AND folder_id = ? AND date < ?" + " ORDER BY date" + (descending ? " DESC" : "")); } else { stmt = conn.prepareStatement("SELECT id, type, uuid FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + " type = ? AND folder_id = ? AND date >= ?" + " ORDER BY date" + (descending ? " DESC" : "")); } int pos = 1; pos = setMailboxId(stmt, mbox, pos); stmt.setByte(pos++, type.toByte()); stmt.setInt(pos++, folder.getId()); stmt.setLong(pos++, messageSyncStart); rs = stmt.executeQuery(); while (rs.next()) { MailItem.Type dataType = MailItem.Type.of(rs.getByte(2)); result.add(dataType, rs.getInt(1), rs.getString(3)); } return result; } catch (SQLException e) { throw ServiceException.FAILURE("fetching item list for folder " + folder.getId(), e); } finally { DbPool.closeResults(rs); DbPool.closeStatement(stmt); } }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. *///from w w w . j a v a 2 s . c o m public void setByte(PreparedStatement stmnt, int idx, byte val, Column col) throws SQLException { stmnt.setByte(idx, val); }
From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java
/** * bind the variables to the prepared statement * // ww w .ja va2 s .c om * @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:com.zimbra.cs.db.DbMailItem.java
public void update(MailItem item, Metadata metadata) throws ServiceException { String name = item.getName().isEmpty() ? null : item.getName(); checkNamingConstraint(mailbox, item.getFolderId(), name, item.getId()); DbConnection conn = mailbox.getOperationConnection(); PreparedStatement stmt = null; try {/*from w ww .ja v a 2 s. c o m*/ stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET type = ?, imap_id = ?, index_id = ?, parent_id = ?, date = ?, size = ?, flags = ?," + " blob_digest = ?, sender = ?, recipients = ?, subject = ?, name = ?," + " metadata = ?, mod_metadata = ?, change_date = ?, mod_content = ?, locator = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?"); int pos = 1; stmt.setByte(pos++, item.getType().toByte()); if (item.getImapUid() >= 0) { stmt.setInt(pos++, item.getImapUid()); } else { stmt.setNull(pos++, Types.INTEGER); } if (item.getIndexStatus() == MailItem.IndexStatus.NO) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, item.getIndexId()); } // messages in virtual conversations are stored with a null parent_id if (item.getParentId() <= 0) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, item.getParentId()); } stmt.setInt(pos++, (int) (item.getDate() / 1000)); stmt.setLong(pos++, item.getSize()); stmt.setLong(pos++, item.getInternalFlagBitmask()); stmt.setString(pos++, item.getDigest()); stmt.setString(pos++, item.getSortSender()); stmt.setString(pos++, item.getSortRecipients()); stmt.setString(pos++, item.getSortSubject()); stmt.setString(pos++, name); stmt.setString(pos++, checkMetadataLength(metadata.toString())); stmt.setInt(pos++, mailbox.getOperationChangeID()); stmt.setInt(pos++, mailbox.getOperationTimestamp()); stmt.setInt(pos++, item.getSavedSequence()); stmt.setString(pos++, item.getLocator()); pos = setMailboxId(stmt, mailbox, pos); stmt.setInt(pos++, item.getId()); stmt.executeUpdate(); if (mailbox.isItemModified(item, Change.FLAGS)) { DbTag.updateTagReferences(mailbox, item.getId(), item.getType(), item.getInternalFlagBitmask(), item.isUnread(), item.getTags()); } } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(item.getName(), e); } else { throw ServiceException .FAILURE("Failed to update item mbox=" + mailbox.getId() + ",id=" + item.getId(), e); } } finally { DbPool.closeStatement(stmt); } }
From source file:com.zimbra.cs.db.DbMailItem.java
public void create(UnderlyingData data) throws ServiceException { if (data.id <= 0 || data.folderId <= 0 || data.parentId == 0) { throw ServiceException.FAILURE("invalid data for DB item create", null); }/*from w ww .ja v a2 s. c o m*/ assert mailbox.isNewItemIdValid(data.id) : "[bug 46549] illegal id for mail item"; //temporarily for bug 46549 checkNamingConstraint(mailbox, data.folderId, data.name, data.id); DbConnection conn = mailbox.getOperationConnection(); PreparedStatement stmt = null; try { MailItem.Type type = MailItem.Type.of(data.type); stmt = conn.prepareStatement("INSERT INTO " + getMailItemTableName(mailbox) + "(" + MAILBOX_ID + " id, type, parent_id, folder_id, index_id, imap_id, date, size, locator, blob_digest, unread," + " flags, tag_names, sender, recipients, subject, name, metadata, mod_metadata, change_date," + " mod_content, uuid) VALUES (" + MAILBOX_ID_VALUE + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int pos = 1; pos = setMailboxId(stmt, mailbox, pos); stmt.setInt(pos++, data.id); stmt.setByte(pos++, data.type); if (data.parentId <= 0) { // Messages in virtual conversations are stored with a null parent_id stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.parentId); } stmt.setInt(pos++, data.folderId); if (data.indexId == MailItem.IndexStatus.NO.id()) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.indexId); } if (data.imapId <= 0) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.imapId); } stmt.setInt(pos++, data.date); stmt.setLong(pos++, data.size); stmt.setString(pos++, data.locator); stmt.setString(pos++, data.getBlobDigest()); switch (type) { case MESSAGE: case CHAT: case FOLDER: stmt.setInt(pos++, data.unreadCount); break; default: stmt.setNull(pos++, Types.INTEGER); break; } stmt.setInt(pos++, data.getFlags()); stmt.setString(pos++, DbTag.serializeTags(data.getTags())); stmt.setString(pos++, sender); stmt.setString(pos++, recipients); stmt.setString(pos++, data.getSubject()); stmt.setString(pos++, data.name); stmt.setString(pos++, checkMetadataLength(data.metadata)); stmt.setInt(pos++, data.modMetadata); if (data.dateChanged > 0) { stmt.setInt(pos++, data.dateChanged); } else { stmt.setNull(pos++, Types.INTEGER); } stmt.setInt(pos++, data.modContent); stmt.setString(pos++, data.uuid); int num = stmt.executeUpdate(); if (num != 1) { throw ServiceException.FAILURE("failed to create object", null); } DbTag.storeTagReferences(mailbox, data.id, type, data.getFlags(), data.unreadCount > 0); DbTag.storeTagReferences(mailbox, data.id, type, data.getTags()); } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(data.id, e); } else { throw ServiceException.FAILURE("Failed to create id=" + data.id + ",type=" + data.type, e); } } finally { DbPool.closeStatement(stmt); } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * //from w w w.j a v a 2 s . c om */ public void process() throws SQLException { buildTags = new BuildTags(); buildTags.setDbConn(dbConn); buildTags.setDbConn2(dbConn); buildTags.initialPrepareStatements(); BasicSQLUtils.setDBConnection(dbConn); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); geoStmt1 = dbConn.prepareStatement( "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?"); geoStmt2 = dbConn .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?"); agentStmt = dbConn .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?"); tagStmt = dbConn.prepareStatement( "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?"); BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'"); boolean doTags = true; if (doTags) { int divId = 2; int dspId = 3; int colId = 32768; String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, " + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, " + "dir, dist, gender, " + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); String rlStr = String.format( "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)", common); String agStr = String .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common); String adStr = String.format( "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)", common); String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?"; String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?"; PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); PreparedStatement rlStmt = dbConn.prepareStatement(rlStr); PreparedStatement agStmt = dbConn.prepareStatement(agStr); PreparedStatement adStmt = dbConn.prepareStatement(adStr); PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr); PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); String city = rs.getString(2); String state = rs.getString(3); String zip = rs.getString(4); String country = rs.getString(5); Date date = rs.getDate(6); double lat = rs.getDouble(7); boolean isLatNull = rs.wasNull(); double lon = rs.getDouble(8); boolean isLonNull = rs.wasNull(); String dir = rs.getString(9); String dist = rs.getString(10); String gender = rs.getString(11); String rep_first = rs.getString(12); String rep_last = rs.getString(13); String rep_city = rs.getString(14); String rep_state = rs.getString(15); String rep_country = rs.getString(16); String rep_zip = rs.getString(17); String t_first = rs.getString(18); //String t_middle = rs.getString(19); String t_last = rs.getString(20); String t_city = rs.getString(21); String t_state = rs.getString(22); String t_country = rs.getString(23); String t_zip = rs.getString(24); //String t_org = rs.getString(25); double t_lat = rs.getDouble(26); boolean isTLatNull = rs.wasNull(); double t_lon = rs.getDouble(27); boolean isTLonNull = rs.wasNull(); //String oldState = state; city = condense(rep_city, t_city, city); state = condense(rep_state, state, t_state); country = condense(rep_country, country, t_country); zip = condense(rep_zip, zip, t_zip); rep_first = condense(rep_first, t_first); rep_last = condense(rep_last, t_last); /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || (t_state != null && t_state.equals("IA")) || (oldState != null && oldState.equals("IA"))); if (debug && (state == null || !state.equals("IA"))) { System.out.println("ouch"); }*/ if (rep_first != null && rep_first.length() > 50) { rep_first = rep_first.substring(0, 50); } lat = isLatNull && !isTLatNull ? t_lat : lat; lon = isLonNull && !isTLonNull ? t_lon : lon; try { // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID Integer geoId = buildTags.getGeography(country, state, null); // Latitude varies between -90 and 90, and Longitude between -180 and 180. if (lat < -90.0 || lat > 90.0) { lcStmt.setObject(1, null); lcStmt.setObject(4, null); } else { lcStmt.setDouble(1, lat); lcStmt.setString(4, Double.toString(lat)); lcUpStmt.setDouble(1, lat); lcUpStmt.setString(4, Double.toString(lat)); } if (lon < -180.0 || lon > 180.0) { lcStmt.setObject(2, null); lcStmt.setObject(5, null); } else { lcStmt.setDouble(2, lon); lcStmt.setString(5, Double.toString(lon)); lcUpStmt.setDouble(2, lon); lcUpStmt.setString(5, Double.toString(lon)); } String locName = null; String fullName = null; Integer locId = null; geoId = buildTags.getGeography(country, state, null); if (geoId != null) { fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); if (locId == null) { lcStmt2.setString(1, "%" + city); lcStmt2.setString(2, country + "%"); ResultSet lcRS = lcStmt2.executeQuery(); if (lcRS.next()) { locId = lcRS.getInt(1); if (!lcRS.wasNull()) { localityHash.put(locName, locId); } } lcRS.close(); } } else { //unknown++; fullName = "Unknown"; locName = buildTags.buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setByte(3, (byte) 0); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setString(8, getLocalityName(country, state, null, city)); lcStmt.setObject(9, geoId); lcStmt.setTimestamp(10, ts); lcStmt.setInt(11, 1); lcStmt.setInt(12, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); } else if (!isLatNull && !isLonNull) { int count = BasicSQLUtils.getCountAsInt( "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = " + locId); if (count == 1) { lcUpStmt.setByte(3, (byte) 0); lcUpStmt.setString(6, "Point"); lcUpStmt.setInt(7, locId); lcUpStmt.executeUpdate(); } } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setInt(2, dspId); ceStmt.setInt(3, locId); ceStmt.setTimestamp(4, ts); ceStmt.setInt(5, 1); ceStmt.setInt(6, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, dir); coStmt.setString(5, dist); coStmt.setInt(6, colId); coStmt.setInt(7, colId); coStmt.setInt(8, ceId); coStmt.setTimestamp(9, ts); coStmt.setInt(10, 1); coStmt.setInt(11, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); Integer agentId = getAgentId(agentStmt, rep_first, rep_last); if (agentId == null) { agStmt.setInt(1, 0); agStmt.setString(2, rep_first); agStmt.setString(3, rep_last); agStmt.setTimestamp(4, ts); agStmt.setInt(5, 1); agStmt.setInt(6, 1); agStmt.executeUpdate(); agentId = BasicSQLUtils.getInsertedId(agStmt); if (agentId != null) { adStmt.setString(1, rep_city); adStmt.setString(2, rep_state); adStmt.setString(3, rep_zip); adStmt.setString(4, rep_country); adStmt.setInt(5, agentId); adStmt.setTimestamp(6, ts); adStmt.setInt(7, 1); adStmt.setInt(8, 1); adStmt.executeUpdate(); } else { log.error("agentId is null after being created: " + rep_first + ", " + rep_last); } } // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); } catch (Exception ex) { log.debug(recNum + " tag[" + tag + "]"); ex.printStackTrace(); } cnt++; if (cnt % 100 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); rlStmt.close(); agStmt.close(); adStmt.close(); lcUpStmt.close(); buildTags.cleanup(); } }
From source file:edu.iu.incntre.flowscalestatcollector.StatCollector.java
public void startUp() { logger.trace("Startup of StatCollector"); try {/*w ww . j av a2 s. c o m*/ if (isQuery) { // initiate sqlite database try { Class.forName(databaseClass); conn = DriverManager.getConnection(databaseDriver, dbUsername, dbPassword); } catch (ClassNotFoundException e2) { logger.error("{}", e2); } catch (SQLException e1) { // TODO Auto-generated catch block logger.error("{}", e1); } // end initiate database // start up thread statThread = new Thread(new Runnable() { @Override public void run() { try { logger.trace("Starting Thread .."); logger.trace("Getting flows from switch every {} seconds", intervalTime); List<OFStatistics> portStats; List<OFStatistics> flowStats; List<OFPhysicalPort> portStatus; SwitchDevice swd = null; String[] datapathIdStringElements = datapathIdStrings.split(","); try { while (statThread != null) { calendar = Calendar.getInstance(); logger.trace("getting flows from switches"); //check if conn is null if it is, reset connection if (conn == null) { conn = DriverManager.getConnection(databaseDriver, dbUsername, dbPassword); } for (String datapathIdString : datapathIdStringElements) { try { swd = flowscaleController.getSwitchDevices() .get(HexString.toLong(datapathIdString)); if (swd == null) { logger.info("switch {} does not exist, is it connected?", datapathIdString); continue; } logger.info("Getting flows from switch {} with ID {}", swd.getSwitchName(), datapathIdString); try { portStats = flowscaleController .getSwitchStatisticsFromInterface(datapathIdString, "port"); flowStats = flowscaleController .getSwitchStatisticsFromInterface(datapathIdString, "flow"); portStatus = swd.getPortStates(); if (flowStats != null && portStats != null) { String flowStatsJSON = JSONConverter.toStat(flowStats, "flow") .toJSONString(); String portStatsJSON = JSONConverter.toStat(portStats, "port") .toJSONString(); String portStatusJSON = JSONConverter.toPortStatus(portStatus) .toJSONString(); // initialize or set hashmaps HashMap<Long, Long> tempPortStatTransmitted; HashMap<Long, Long> tempPortStatReceived; HashMap<String, Long> tempFlowStat; long datapathId = HexString.toLong(datapathIdString); if (tempPortStatTransmittedHashMap.get(datapathId) == null) { tempPortStatTransmitted = new HashMap<Long, Long>(); tempPortStatTransmittedHashMap.put(datapathId, tempPortStatTransmitted); } else { tempPortStatTransmitted = tempPortStatTransmittedHashMap .get(datapathId); } if (tempPortStatReceivedHashMap.get(datapathId) == null) { tempPortStatReceived = new HashMap<Long, Long>(); tempPortStatReceivedHashMap.put(datapathId, tempPortStatReceived); } else { tempPortStatReceived = tempPortStatReceivedHashMap .get(datapathId); } if (tempFlowStatHashMap.get(datapathId) == null) { tempFlowStat = new HashMap<String, Long>(); tempFlowStatHashMap.put(datapathId, tempFlowStat); } else { tempFlowStat = tempFlowStatHashMap.get(datapathId); } storeSwitchDetails(HexString.toLong(datapathIdString), portStatsJSON, flowStatsJSON, portStatusJSON, tempPortStatTransmitted, tempPortStatReceived, tempFlowStat); } else { logger.error( "Switch {} returned a null result possibility because the switch is not connected to the controller", datapathIdString); } } catch (NoSwitchException e1) { // TODO Auto-generated catch block logger.error("Switch {} with ID {} is not connected aborting", swd.getSwitchName(), datapathIdString); } catch (IOException e1) { logger.error("IOException {}", e1); } catch (InterruptedException e1) { logger.error("Thread Interrupted {}", e1); killThread(); } catch (ExecutionException e1) { logger.error("Execution Exception {}", e1); } catch (TimeoutException e1) { logger.error("Switch Timeout Exception {}", e1); killThread(); } } catch (Exception e) { logger.error("unchecked exception here {}", e); killThread(); shutDown(); Thread.yield(); } } try { Thread.sleep(intervalTime); } catch (InterruptedException e) { logger.error("{}", e); break; } } } catch (Exception e) { logger.error("exception in while {}", e); shutDown(); } try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } catch (Exception generalException) { logger.error("General Exception throws {} ", generalException); } } /** * insert details into database, 3 tables will be populated: flow_stats, port_stats ,and port_status * * @param datapathId * @param portStats * @param flowStats * @param portStatus * @param tempPortStatTransmitted * @param tempPortStatReceived * @param tempFlowStat */ private void storeSwitchDetails(long datapathId, String portStats, String flowStats, String portStatus, HashMap<Long, Long> tempPortStatTransmitted, HashMap<Long, Long> tempPortStatReceived, HashMap<String, Long> tempFlowStat) { Object obj = JSONValue.parse(portStats); JSONArray jsonArray = (JSONArray) obj; for (int i = 0; i < jsonArray.size(); i++) { JSONObject jsonObject = (JSONObject) jsonArray.get(i); long transmittedPackets = (Long) jsonObject.get("transmit_packets"); long receivedPackets = (Long) jsonObject.get("receive_packets"); long portId = (Long) jsonObject.get("port_id"); // logger.info("the port is {}", portId); // logger.info("{} packets transmitted and {} packets received", // receivedPackets,transmittedPackets); PreparedStatement prep = null; try { prep = null; if (conn != null) { prep = conn.prepareStatement("insert into port_stats values (?,?,?,?,?);"); } else { logger.error("no connection object instantiated aborting .. "); return; } prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); if (tempPortStatTransmitted.get(portId) != null) { long currentTransmittedPackets = transmittedPackets - tempPortStatTransmitted.get(portId); if (currentTransmittedPackets < 0) { prep.setLong(5, transmittedPackets); } else { prep.setLong(5, currentTransmittedPackets); } } else { prep.setLong(5, transmittedPackets); } tempPortStatTransmitted.put(portId, transmittedPackets); // take care of port received if (tempPortStatReceived.get(portId) != null) { long currentReceivedPackets = receivedPackets - tempPortStatReceived.get(portId); if (currentReceivedPackets < 0) { prep.setLong(4, receivedPackets); } else { prep.setLong(4, currentReceivedPackets); } } else { prep.setLong(4, receivedPackets); } tempPortStatReceived.put(portId, receivedPackets); prep.setLong(3, portId); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLRecoverableException sqlRecoverableException) { logger.error("{}", sqlRecoverableException); //exit function since there is a timeout return; } catch (SQLException e) { logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } Object flowJSONobj = JSONValue.parse(flowStats); JSONArray flowJsonArray = (JSONArray) flowJSONobj; for (int i = 0; i < flowJsonArray.size(); i++) { JSONObject jsonObject = (JSONObject) flowJsonArray.get(i); long packets = (Long) jsonObject.get("packet_count"); String matchString = (String) jsonObject.get("match"); String action = (String) jsonObject.get("actions"); long priority = (Long) jsonObject.get("priority"); PreparedStatement prep = null; try { prep = conn.prepareStatement("insert into flow_stats values (?,?,?,?,?,?);"); String insertString = datapathId + "," + calendar.getTimeInMillis() + "," + matchString + "," + action; logger.debug("flow_stat values to insert are {}", insertString); prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); if (tempFlowStat.get(matchString) != null) { long packetsReceived = packets - tempFlowStat.get(matchString); if (packetsReceived < 0) { prep.setLong(5, packets); } else { prep.setLong(5, packetsReceived); } } else { prep.setLong(5, packets); } tempFlowStat.put(matchString, packets); prep.setString(3, matchString); prep.setString(4, action); prep.setShort(6, (short) priority); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("error when insert flow {} in switch {}", matchString, datapathId); logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } Object portStatusJSONobj = JSONValue.parse(portStatus); JSONArray portStatusJsonArray = (JSONArray) portStatusJSONobj; for (int i = 0; i < portStatusJsonArray.size(); i++) { byte portStatusValue = 0; JSONObject jsonObject = (JSONObject) portStatusJsonArray.get(i); long portId = (Long) jsonObject.get("port_id"); String portAddress = (String) jsonObject.get("port_address"); try { portStatusValue = (byte) (Integer.parseInt(jsonObject.get("state").toString()) % 2); } catch (NumberFormatException nfe) { logger.error("{}", nfe); continue; } PreparedStatement prep = null; try { prep = conn.prepareStatement("insert into port_status values (?,?,?,?,?);"); prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); prep.setLong(3, portId); prep.setString(4, portAddress); prep.setByte(5, portStatusValue); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } } }, "Switch Stat Collector"); statThread.start(); } } catch (Exception e) { logger.error("general excecption thrown {}", e); } }