List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Store a new node in the database. The method will retrieve a new database id for the node and update the * passed object. Afterwards, the node data will be inserted into the database using appropriate INSERT * statements. The caller must make sure the connection is committed and closed properly. * <p/>/*from w ww. j a v a 2 s. c om*/ * If the node already has an ID, the method will do nothing (assuming that it is already persistent) * * * @param node * @throws SQLException */ public synchronized void storeNode(KiWiNode node) throws SQLException { // ensure the data type of a literal is persisted first if (node instanceof KiWiLiteral) { KiWiLiteral literal = (KiWiLiteral) node; if (literal.getType() != null && literal.getType().getId() < 0) { storeNode(literal.getType()); } } requireJDBCConnection(); // retrieve a new node id and set it in the node object if (node.getId() < 0) { node.setId(getNextSequence()); } // distinguish the different node types and run the appropriate updates if (node instanceof KiWiUriResource) { KiWiUriResource uriResource = (KiWiUriResource) node; PreparedStatement insertNode = getPreparedStatement("store.uri"); insertNode.setLong(1, node.getId()); insertNode.setString(2, uriResource.stringValue()); insertNode.setTimestamp(3, new Timestamp(uriResource.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiAnonResource) { KiWiAnonResource anonResource = (KiWiAnonResource) node; PreparedStatement insertNode = getPreparedStatement("store.bnode"); insertNode.setLong(1, node.getId()); insertNode.setString(2, anonResource.stringValue()); insertNode.setTimestamp(3, new Timestamp(anonResource.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiDateLiteral) { KiWiDateLiteral dateLiteral = (KiWiDateLiteral) node; PreparedStatement insertNode = getPreparedStatement("store.tliteral"); insertNode.setLong(1, node.getId()); insertNode.setString(2, dateLiteral.stringValue()); insertNode.setTimestamp(3, new Timestamp(dateLiteral.getDateContent().getMillis()), calendarUTC); insertNode.setInt(4, dateLiteral.getDateContent().getZone().getOffset(dateLiteral.getDateContent()) / 1000); if (dateLiteral.getType() != null) insertNode.setLong(5, dateLiteral.getType().getId()); else throw new IllegalStateException("a date literal must have a datatype"); insertNode.setTimestamp(6, new Timestamp(dateLiteral.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiIntLiteral) { KiWiIntLiteral intLiteral = (KiWiIntLiteral) node; PreparedStatement insertNode = getPreparedStatement("store.iliteral"); insertNode.setLong(1, node.getId()); insertNode.setString(2, intLiteral.getContent()); insertNode.setDouble(3, intLiteral.getDoubleContent()); insertNode.setLong(4, intLiteral.getIntContent()); if (intLiteral.getType() != null) insertNode.setLong(5, intLiteral.getType().getId()); else throw new IllegalStateException("an integer literal must have a datatype"); insertNode.setTimestamp(6, new Timestamp(intLiteral.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiDoubleLiteral) { KiWiDoubleLiteral doubleLiteral = (KiWiDoubleLiteral) node; PreparedStatement insertNode = getPreparedStatement("store.dliteral"); insertNode.setLong(1, node.getId()); insertNode.setString(2, doubleLiteral.getContent()); insertNode.setDouble(3, doubleLiteral.getDoubleContent()); if (doubleLiteral.getType() != null) insertNode.setLong(4, doubleLiteral.getType().getId()); else throw new IllegalStateException("a double literal must have a datatype"); insertNode.setTimestamp(5, new Timestamp(doubleLiteral.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiBooleanLiteral) { KiWiBooleanLiteral booleanLiteral = (KiWiBooleanLiteral) node; PreparedStatement insertNode = getPreparedStatement("store.bliteral"); insertNode.setLong(1, node.getId()); insertNode.setString(2, booleanLiteral.getContent()); insertNode.setBoolean(3, booleanLiteral.booleanValue()); if (booleanLiteral.getType() != null) insertNode.setLong(4, booleanLiteral.getType().getId()); else throw new IllegalStateException("a boolean literal must have a datatype"); insertNode.setTimestamp(5, new Timestamp(booleanLiteral.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else if (node instanceof KiWiStringLiteral) { KiWiStringLiteral stringLiteral = (KiWiStringLiteral) node; Double dbl_value = null; Long lng_value = null; if (stringLiteral.getContent().length() < 64 && NumberUtils.isNumber(stringLiteral.getContent())) try { dbl_value = Double.parseDouble(stringLiteral.getContent()); lng_value = Long.parseLong(stringLiteral.getContent()); } catch (NumberFormatException ex) { // ignore, keep NaN } PreparedStatement insertNode = getPreparedStatement("store.sliteral"); insertNode.setLong(1, node.getId()); insertNode.setString(2, stringLiteral.getContent()); if (dbl_value != null) { insertNode.setDouble(3, dbl_value); } else { insertNode.setObject(3, null); } if (lng_value != null) { insertNode.setLong(4, lng_value); } else { insertNode.setObject(4, null); } if (stringLiteral.getLocale() != null) { insertNode.setString(5, stringLiteral.getLocale().getLanguage().toLowerCase()); } else { insertNode.setObject(5, null); } if (stringLiteral.getType() != null) { insertNode.setLong(6, stringLiteral.getType().getId()); } else { insertNode.setObject(6, null); } insertNode.setTimestamp(7, new Timestamp(stringLiteral.getCreated().getTime()), calendarUTC); insertNode.executeUpdate(); } else { log.warn("unrecognized node type: {}", node.getClass().getCanonicalName()); } cacheNode(node); }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors/* www . j a va 2 s . c o m*/ */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }
From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java
protected long _reorganizeSpace(Connection con, SequencerEngine seq, FxTreeMode sourceMode, FxTreeMode destMode, long nodeId, boolean includeNodeId, BigInteger overrideSpacing, BigInteger overrideLeft, FxTreeNodeInfo insertParent, int insertPosition, BigInteger insertSpace, BigInteger insertBoundaries[], int depthDelta, Long destinationNode, boolean createMode, boolean createKeepIds, boolean disableSpaceOptimization) throws FxTreeException { long firstCreatedNodeId = -1; FxTreeNodeInfoSpreaded nodeInfo;// w w w . ja v a 2 s . c om try { nodeInfo = (FxTreeNodeInfoSpreaded) getTreeNodeInfo(con, sourceMode, nodeId); } catch (Exception e) { return -1; } if (!nodeInfo.isSpaceOptimizable() && !disableSpaceOptimization) { // The Root node and cant be optimize any more ... so all we can do is fail :-/ // This should never really happen if (nodeId == ROOT_NODE) { return -1; } //System.out.println("### UP we go, depthDelta=" + depthDelta); return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId, overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries, depthDelta, destinationNode, createMode, createKeepIds, false); } BigInteger spacing = nodeInfo.getDefaultSpacing(); if (overrideSpacing != null && (overrideSpacing.compareTo(spacing) < 0 || overrideLeft != null)) { // override spacing unless it is greater OR overrideLeft is specified (in that case we // have to use the spacing for valid tree ranges) spacing = overrideSpacing; } else { if (spacing.compareTo(GO_UP) < 0 && !createMode && !disableSpaceOptimization) { return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId, overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries, depthDelta, destinationNode, createMode, createKeepIds, false); } } if (insertBoundaries != null && insertPosition == -1) { insertPosition = 0; // insertPosition cannot be negative } Statement stmt = null; PreparedStatement ps = null; ResultSet rs; BigInteger left = overrideLeft == null ? nodeInfo.getLeft() : overrideLeft; BigInteger right = null; String includeNode = includeNodeId ? "=" : ""; long counter = 0; long newId = -1; try { final long start = System.currentTimeMillis(); String createProps = createMode ? ",PARENT,REF,NAME,TEMPLATE" : ""; String sql = " SELECT ID," + StorageManager.getIfFunction( // compute total child count only when the node has children "CHILDCOUNT = 0", "0", "(SELECT COUNT(*) FROM " + getTable(sourceMode) + " WHERE LFT > NODE.LFT AND RGT < NODE.RGT)") + // 3 4 5 6 ", CHILDCOUNT, LFT AS LFTORD,RGT,DEPTH" + createProps + " FROM (SELECT ID,CHILDCOUNT,LFT,RGT,DEPTH" + createProps + " FROM " + getTable(sourceMode) + " WHERE " + "LFT>" + includeNode + nodeInfo.getLeft() + " AND LFT<" + includeNode + nodeInfo.getRight() + ") NODE " + "ORDER BY LFTORD ASC"; stmt = con.createStatement(); rs = stmt.executeQuery(sql); if (createMode) { // 1 2 3 4 5 6 7 8 ps = con.prepareStatement( "INSERT INTO " + getTable(destMode) + " (ID,PARENT,DEPTH,DIRTY,REF,TEMPLATE,LFT,RGT," + //9 10 11 "CHILDCOUNT,NAME,MODIFIED_AT) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?)"); } else { ps = con.prepareStatement("UPDATE " + getTable(sourceMode) + " SET LFT=?,RGT=?,DEPTH=? WHERE ID=?"); } long id; int total_childs; int direct_childs; BigInteger nextLeft; int lastDepth = nodeInfo.getDepth() + (includeNodeId ? 0 : 1); int depth; BigInteger _rgt; BigInteger _lft; Long ref = null; String data = null; String name = ""; Stack<Long> currentParent = null; if (createMode) { currentParent = new Stack<Long>(); currentParent.push(destinationNode); } //System.out.println("Spacing:"+SPACING); while (rs.next()) { //System.out.println("------------------"); id = rs.getLong(1); total_childs = rs.getInt(2); direct_childs = rs.getInt(3); _lft = getNodeBounds(rs, 4); _rgt = getNodeBounds(rs, 5); depth = rs.getInt(6); if (createMode) { // Reading these properties is slow, only do it when needed ref = rs.getLong(8); if (rs.wasNull()) ref = null; name = rs.getString(9); data = rs.getString(10); if (rs.wasNull()) data = null; } left = left.add(spacing).add(BigInteger.ONE); // Handle depth differences if (lastDepth - depth > 0) { BigInteger depthDifference = spacing.add(BigInteger.ONE); left = left.add(depthDifference.multiply(BigInteger.valueOf(lastDepth - depth))); } if (createMode) { if (lastDepth < depth) { currentParent.push(newId); } else if (lastDepth > depth) { for (int p = 0; p < (lastDepth - depth); p++) currentParent.pop(); } } right = left.add(spacing).add(BigInteger.ONE); // add child space if needed if (total_childs > 0) { BigInteger childSpace = spacing.multiply(BigInteger.valueOf(total_childs * 2)); childSpace = childSpace.add(BigInteger.valueOf((total_childs * 2) - 1)); right = right.add(childSpace); nextLeft = left; } else { nextLeft = right; } if (insertBoundaries != null) { // insert gap at requested position // If we're past the gap, keep adding the insert space to left/right because the added // space is never "injected" into the loop, i.e. without adding it the left/right boundaries of // nodes after the gap would be too far to the left. if (_lft.compareTo(insertBoundaries[0]) > 0) { left = left.add(insertSpace); } if (_rgt.compareTo(insertBoundaries[0]) > 0) { right = right.add(insertSpace); } } // sanity checks if (left.compareTo(right) >= 0) { throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right, "left greater than right"); } if (insertParent != null && right.compareTo((BigInteger) insertParent.getRight()) > 0) { throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right, "wrote past parent node bounds"); } // Update the node if (createMode) { newId = createKeepIds ? id : seq.getId(destMode.getSequencer()); if (firstCreatedNodeId == -1) firstCreatedNodeId = newId; // Create the main entry ps.setLong(1, newId); ps.setLong(2, currentParent.peek()); ps.setLong(3, depth + depthDelta); ps.setBoolean(4, destMode != FxTreeMode.Live); //only flag non-live tree's dirty if (ref == null) { ps.setNull(5, java.sql.Types.NUMERIC); } else { ps.setLong(5, ref); } if (data == null) { ps.setNull(6, java.sql.Types.VARCHAR); } else { ps.setString(6, data); } // System.out.println("=> id:"+newId+" left:"+left+" right:"+right); setNodeBounds(ps, 7, left); setNodeBounds(ps, 8, right); ps.setInt(9, direct_childs); ps.setString(10, name); ps.setLong(11, System.currentTimeMillis()); ps.addBatch(); } else { setNodeBounds(ps, 1, left); setNodeBounds(ps, 2, right); ps.setInt(3, depth + depthDelta); ps.setLong(4, id); ps.addBatch(); // ps.executeBatch(); // ps.clearBatch(); } // Prepare variables for the next node left = nextLeft; lastDepth = depth; counter++; // Execute batch every 10000 items to avoid out of memory if (counter % 10000 == 0) { ps.executeBatch(); ps.clearBatch(); } } rs.close(); stmt.close(); stmt = null; ps.executeBatch(); if (LOG.isDebugEnabled()) { final long time = System.currentTimeMillis() - start; LOG.debug("Tree reorganization of " + counter + " items completed in " + time + " ms (spaceLen=" + spacing + ")"); } return firstCreatedNodeId; } catch (FxApplicationException e) { throw e instanceof FxTreeException ? (FxTreeException) e : new FxTreeException(e); } catch (SQLException e) { String next = ""; if (e.getNextException() != null) next = " next:" + e.getNextException().getMessage(); if (StorageManager.isDuplicateKeyViolation(e)) throw new FxTreeException(LOG, e, "ex.tree.reorganize.duplicateKey"); throw new FxTreeException(LOG, e, "ex.tree.reorganize.failed", counter, left, right, e.getMessage() + next); } catch (Exception e) { throw new FxTreeException(e); } finally { try { if (stmt != null) stmt.close(); } catch (Throwable t) { /*ignore*/} try { if (ps != null) ps.close(); } catch (Throwable t) { /*ignore*/} } }
From source file:org.mskcc.cbio.cgds.dao.DaoMutation.java
public int addMutation(ExtendedMutation mutation) throws DaoException { Connection con = null;/*w w w .j a v a 2s .co m*/ PreparedStatement pstmt = null; ResultSet rs = null; try { if (MySQLbulkLoader.isBulkLoad()) { // use this code if bulk loading // write to the temp file maintained by the MySQLbulkLoader myMySQLbulkLoader.insertRecord( Integer.toString(mutation.getGeneticProfileId()), mutation.getCaseId(), Long.toString(mutation.getGene().getEntrezGeneId()), mutation.getSequencingCenter(), mutation.getSequencer(), mutation.getMutationStatus(), mutation.getValidationStatus(), mutation.getChr(), Long.toString(mutation.getStartPosition()), Long.toString(mutation.getEndPosition()), mutation.getProteinChange(), mutation.getMutationType(), mutation.getFunctionalImpactScore(), mutation.getLinkXVar(), mutation.getLinkPdb(), mutation.getLinkMsa(), mutation.getNcbiBuild(), mutation.getStrand(), mutation.getVariantType(), mutation.getReferenceAllele(), mutation.getTumorSeqAllele1(), mutation.getTumorSeqAllele2(), mutation.getDbSnpRs(), mutation.getDbSnpValStatus(), mutation.getMatchedNormSampleBarcode(), mutation.getMatchNormSeqAllele1(), mutation.getMatchNormSeqAllele2(), mutation.getTumorValidationAllele1(), mutation.getTumorValidationAllele2(), mutation.getMatchNormValidationAllele1(), mutation.getMatchNormValidationAllele2(), mutation.getVerificationStatus(), mutation.getSequencingPhase(), mutation.getSequenceSource(), mutation.getValidationMethod(), mutation.getScore(), mutation.getBamFile(), Integer.toString(mutation.getTumorAltCount()), Integer.toString(mutation.getTumorRefCount()), Integer.toString(mutation.getNormalAltCount()), Integer.toString(mutation.getNormalRefCount()), mutation.getOncotatorDbSnpRs(), DaoMutationEvent.filterCosmic(mutation), mutation.getOncotatorRefseqMrnaId(), mutation.getOncotatorCodonChange(), mutation.getOncotatorUniprotName(), mutation.getOncotatorUniprotAccession(), this.boolToStr(mutation.isCanonicalTranscript())); // return 1 because normal insert will return 1 if no error occurs return 1; } else { con = JdbcUtil.getDbConnection(DaoMutation.class); pstmt = con.prepareStatement("INSERT INTO mutation (`GENETIC_PROFILE_ID`, `CASE_ID`," + " `ENTREZ_GENE_ID`," + " `CENTER`, `SEQUENCER`, `MUTATION_STATUS`, `VALIDATION_STATUS`, `CHR`," + " `START_POSITION`, `END_POSITION`, `PROTEIN_CHANGE`, " + " `MUTATION_TYPE`, `FUNCTIONAL_IMPACT_SCORE`, `LINK_XVAR`, `LINK_PDB`," + " `LINK_MSA`, `NCBI_BUILD`, `STRAND`, `VARIANT_TYPE`, `REFERENCE_ALLELE`," + " `TUMOR_SEQ_ALLELE1`, `TUMOR_SEQ_ALLELE2`, `DB_SNP_RS`, `DB_SNP_VAL_STATUS`," + " `MATCHED_NORM_SAMPLE_BARCODE`, `MATCH_NORM_SEQ_ALLELE1`, `MATCH_NORM_SEQ_ALLELE2`," + " `TUMOR_VALIDATION_ALLELE1`, `TUMOR_VALIDATION_ALLELE2`," + " `MATCH_NORM_VALIDATION_ALLELE1`, `MATCH_NORM_VALIDATION_ALLELE2`," + " `VERIFICATION_STATUS`, `SEQUENCING_PHASE`, `SEQUENCE_SOURCE`, `VALIDATION_METHOD`," + " `SCORE`, `BAM_FILE`, `TUMOR_ALT_COUNT`, `TUMOR_REF_COUNT`, `NORMAL_ALT_COUNT`," + " `NORMAL_REF_COUNT`, `ONCOTATOR_DBSNP_RS`, `ONCOTATOR_COSMIC_OVERLAPPING`," + " `ONCOTATOR_REFSEQ_MRNA_ID`, `ONCOTATOR_CODON_CHANGE`, `ONCOTATOR_UNIPROT_ENTRY_NAME`," + " `ONCOTATOR_UNIPROT_ACCESSION`, `CANONICAL_TRANSCRIPT`)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); pstmt.setInt(1, mutation.getGeneticProfileId()); pstmt.setString(2, mutation.getCaseId()); pstmt.setLong(3, mutation.getGene().getEntrezGeneId()); pstmt.setString(4, mutation.getSequencingCenter()); pstmt.setString(5, mutation.getSequencer()); pstmt.setString(6, mutation.getMutationStatus()); pstmt.setString(7, mutation.getValidationStatus()); pstmt.setString(8, mutation.getChr()); pstmt.setLong(9, mutation.getStartPosition()); pstmt.setLong(10, mutation.getEndPosition()); pstmt.setString(11, mutation.getProteinChange()); pstmt.setString(12, mutation.getMutationType()); pstmt.setString(13, mutation.getFunctionalImpactScore()); pstmt.setString(14, mutation.getLinkXVar()); pstmt.setString(15, mutation.getLinkPdb()); pstmt.setString(16, mutation.getLinkMsa()); pstmt.setString(17, mutation.getNcbiBuild()); pstmt.setString(18, mutation.getStrand()); pstmt.setString(19, mutation.getVariantType()); pstmt.setString(20, mutation.getReferenceAllele()); pstmt.setString(21, mutation.getTumorSeqAllele1()); pstmt.setString(22, mutation.getTumorSeqAllele2()); pstmt.setString(23, mutation.getDbSnpRs()); pstmt.setString(24, mutation.getDbSnpValStatus()); pstmt.setString(25, mutation.getMatchedNormSampleBarcode()); pstmt.setString(26, mutation.getMatchNormSeqAllele1()); pstmt.setString(27, mutation.getMatchNormSeqAllele2()); pstmt.setString(28, mutation.getTumorValidationAllele1()); pstmt.setString(29, mutation.getTumorValidationAllele2()); pstmt.setString(30, mutation.getMatchNormValidationAllele1()); pstmt.setString(31, mutation.getMatchNormValidationAllele2()); pstmt.setString(32, mutation.getVerificationStatus()); pstmt.setString(33, mutation.getSequencingPhase()); pstmt.setString(34, mutation.getSequenceSource()); pstmt.setString(35, mutation.getValidationMethod()); pstmt.setString(36, mutation.getScore()); pstmt.setString(37, mutation.getBamFile()); pstmt.setInt(38, mutation.getTumorAltCount()); pstmt.setInt(39, mutation.getTumorRefCount()); pstmt.setInt(40, mutation.getNormalAltCount()); pstmt.setInt(41, mutation.getNormalRefCount()); pstmt.setString(42, mutation.getOncotatorDbSnpRs()); pstmt.setString(43, DaoMutationEvent.filterCosmic(mutation)); pstmt.setString(44, mutation.getOncotatorRefseqMrnaId()); pstmt.setString(45, mutation.getOncotatorCodonChange()); pstmt.setString(46, mutation.getOncotatorUniprotName()); pstmt.setString(47, mutation.getOncotatorUniprotAccession()); pstmt.setBoolean(48, mutation.isCanonicalTranscript()); return pstmt.executeUpdate(); } } catch (SQLException e) { throw new DaoException(e); } finally { JdbcUtil.closeAll(DaoMutation.class, con, pstmt, rs); } }
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 www .j a v a 2 s . c om 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:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
/** * Make specific fields visible.//from ww w . ja v a2s. c o m * @param disciplineDirName the name of the directory for the Discipline * @param discipline the Discipline itself */ public static void doShowHideTablesAndFields(final String disciplineDirName, final Discipline discipline) { final String showFieldsFileName = "show_fields.xml"; String dirName = disciplineDirName != null ? disciplineDirName + File.separator : ""; String filePath = XMLHelper.getConfigDirPath(dirName + showFieldsFileName); File showFieldsFile = new File(filePath); if (showFieldsFile.exists()) { Connection conn = DBConnection.getInstance().getConnection(); PreparedStatement tblStmt = null; PreparedStatement fldStmt = null; try { tblStmt = conn.prepareStatement( "UPDATE splocalecontainer SET IsHidden=? WHERE DisciplineID = ? AND Name = ?"); fldStmt = conn.prepareStatement( "UPDATE splocalecontaineritem SET IsHidden=? WHERE SpLocaleContainerItemID = ?"); Element root = XMLHelper.readDOMFromConfigDir(dirName + showFieldsFileName); if (root != null) { StringBuilder sb = new StringBuilder("In show_fields.xml there were the following errors:\n"); boolean isErr = false; List<?> tables = root.selectNodes("/tables/table"); for (Iterator<?> iter = tables.iterator(); iter.hasNext();) { Element table = (Element) iter.next(); String tName = XMLHelper.getAttr(table, NAME, null); boolean isShwTbl = XMLHelper.getAttr(table, SHOW, true); String doSetTbl = XMLHelper.getAttr(table, SHOW, null); if (StringUtils.isNotEmpty(tName)) { DBTableInfo tbl = DBTableIdMgr.getInstance().getInfoByTableName(tName.toLowerCase()); if (tbl != null) { if (StringUtils.isNotEmpty(doSetTbl)) { tblStmt.setBoolean(1, !isShwTbl); tblStmt.setInt(2, discipline.getId()); tblStmt.setString(3, tName); tblStmt.executeUpdate(); } List<?> fields = table.selectNodes("field"); for (Iterator<?> fIter = fields.iterator(); fIter.hasNext();) { Element fieldEl = (Element) fIter.next(); String fName = XMLHelper.getAttr(fieldEl, NAME, null); boolean isShown = XMLHelper.getAttr(fieldEl, SHOW, true); if (StringUtils.isNotEmpty(fName)) { DBTableChildIFace childInfo = tbl.getItemByName(fName); if (childInfo != null) { String sql = String.format( "SELECT ci.SpLocaleContainerItemID FROM splocalecontainer AS c " + "INNER Join splocalecontaineritem AS ci ON c.SpLocaleContainerID = ci.SpLocaleContainerID " + "WHERE ci.Name = '%s' AND c.Name = '%s' AND c.DisciplineID = %d", fName, tName, discipline.getId()); Integer id = BasicSQLUtils.getCount(sql); if (id != null) { fldStmt.setBoolean(1, !isShown); fldStmt.setInt(2, id); fldStmt.executeUpdate(); } else { sb.append("Unable to find [" + disciplineDirName + "] for table name [" + tName + "] has bad field name[" + fName + "]\n"); } } else { sb.append("[" + disciplineDirName + "] for table name [" + tName + "] has bad field name[" + fName + "]\n"); isErr = true; } } } } else { sb.append("[" + disciplineDirName + "] has bad table name [" + tName + "]\n"); isErr = true; } } } if (isErr) { showError(sb.toString()); } } } catch (Exception ex) { ex.printStackTrace(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BuildSampleDatabase.class, ex); } finally { try { if (tblStmt != null) tblStmt.close(); if (fldStmt != null) fldStmt.close(); } catch (Exception ex) { } } } }
From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java
/** * Transfer a binary from the transit to the 'real' binary table * * @param _con open and valid connection * @param binary the binary descriptor/*from w ww . j ava2s .com*/ * @param id desired id * @param version desired version * @param quality desired quality * @return descriptor of final binary * @throws FxDbException on errors looking up the sequencer */ private BinaryDescriptor binaryTransit(Connection _con, BinaryDescriptor binary, long id, int version, int quality) throws FxDbException { PreparedStatement ps = null; BinaryDescriptor created; FileInputStream fis = null; boolean dbTransit; boolean dbStorage; final long dbThreshold; final long dbPreviewThreshold; final int divisionId = FxContext.get().getDivisionId(); try { final DivisionConfigurationEngine divisionConfig = EJBLookup.getDivisionConfigurationEngine(); dbTransit = divisionConfig.get(SystemParameters.BINARY_TRANSIT_DB); if (id >= 0) { dbThreshold = divisionConfig.get(SystemParameters.BINARY_DB_THRESHOLD); dbPreviewThreshold = divisionConfig.get(SystemParameters.BINARY_DB_PREVIEW_THRESHOLD); } else { //force storage of system binaries in the database dbThreshold = -1; dbPreviewThreshold = -1; } dbStorage = dbThreshold < 0 || binary.getSize() < dbThreshold; } catch (FxApplicationException e) { throw e.asRuntimeException(); } Connection con = null; try { con = Database.getNonTXDataSource(divisionId).getConnection(); con.setAutoCommit(false); double resolution = 0.0; int width = 0; int height = 0; boolean isImage = binary.getMimeType().startsWith("image/"); if (isImage) { try { width = Integer .parseInt(defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "width"), "0")); height = Integer.parseInt( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "height"), "0")); resolution = Double.parseDouble( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "xResolution"), "0")); } catch (NumberFormatException e) { //ignore LOG.warn(e, e); } } created = new BinaryDescriptor(CacheAdmin.getStreamServers(), id, version, quality, System.currentTimeMillis(), binary.getName(), binary.getSize(), binary.getMetadata(), binary.getMimeType(), isImage, resolution, width, height, binary.getMd5sum()); //we can copy the blob directly into the binary table if the database is used for transit and the final binary is //stored in the filesystem final boolean copyBlob = dbTransit && dbStorage; boolean storePrev1FS = false, storePrev2FS = false, storePrev3FS = false, storePrev4FS = false; long prev1Length = -1, prev2Length = -1, prev3Length = -1, prev4Length = -1; if (dbPreviewThreshold >= 0) { //we have to check if preview should be stored on the filesystem ps = con.prepareStatement(BINARY_TRANSIT_PREVIEW_SIZES); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); rs.getLong(1); //check if previewref is null if (rs.wasNull()) { //if previews are not referenced, check thresholds storePrev1FS = (prev1Length = rs.getLong(2)) >= dbPreviewThreshold && !rs.wasNull(); storePrev2FS = (prev2Length = rs.getLong(3)) >= dbPreviewThreshold && !rs.wasNull(); storePrev3FS = (prev3Length = rs.getLong(4)) >= dbPreviewThreshold && !rs.wasNull(); storePrev4FS = (prev4Length = rs.getLong(5)) >= dbPreviewThreshold && !rs.wasNull(); } } if (ps != null) ps.close(); String previewSelect = (storePrev1FS ? ",NULL" : ",PREV1") + (storePrev2FS ? ",NULL" : ",PREV2") + (storePrev3FS ? ",NULL" : ",PREV3") + (storePrev4FS ? ",NULL" : ",PREV4"); //check if the binary is to be replaced ps = con.prepareStatement( "SELECT COUNT(*) FROM " + TBL_CONTENT_BINARY + " WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ResultSet rsExist = ps.executeQuery(); final boolean replaceBinary = rsExist != null && rsExist.next() && rsExist.getLong(1) > 0; ps.close(); int paramIndex = 1; if (replaceBinary) { ps = con.prepareStatement(BINARY_TRANSIT_REPLACE + (copyBlob ? BINARY_TRANSIT_REPLACE_FBLOB_COPY : BINARY_TRANSIT_REPLACE_FBLOB_PARAM) + BINARY_TRANSIT_REPLACE_PARAMS); FxBinaryUtils.removeBinary(divisionId, created.getId()); } else { ps = con.prepareStatement((copyBlob ? BINARY_TRANSIT : BINARY_TRANSIT_FILESYSTEM) + previewSelect + BINARY_TRANSIT_PREVIEW_WHERE); ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex++, created.getQuality()); //quality } File binaryTransit = null; boolean removeTransitFile = false; if (dbTransit) { //transit is handled in the database try { if (!dbStorage) { //binaries are stored on the filesystem binaryTransit = getBinaryTransitFileInfo(binary).getBinaryTransitFile(); removeTransitFile = true; //have to clean up afterwards since its a temporary file we get } } catch (FxApplicationException e) { if (e instanceof FxDbException) throw (FxDbException) e; throw new FxDbException(e); } } else { //transit file resides on the local file system binaryTransit = FxBinaryUtils.getTransitFile(divisionId, binary.getHandle()); removeTransitFile = true; // temporary transit file can be removed as well if (binaryTransit == null) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); } boolean needExplicitBlobInsert = false; if (copyBlob && replaceBinary) ps.setString(paramIndex++, binary.getHandle()); if (!copyBlob) { //we do not perform a simple blob copy operation in the database if (dbStorage) { //binary is stored in the database -> copy it from the transit file (might be a temp. file) if (blobInsertSelectAllowed()) { fis = new FileInputStream(binaryTransit); ps.setBinaryStream(paramIndex++, fis, (int) binaryTransit.length()); } else { ps.setNull(paramIndex++, Types.BINARY); needExplicitBlobInsert = true; } } else { //binary is stored on the filesystem -> move transit file to binary storage file try { if (!FxFileUtils.moveFile(binaryTransit, FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.ORIGINAL.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId()); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId(), e.getMessage()); } ps.setNull(paramIndex++, Types.BINARY); } } // int cnt = paramIndex; //copyBlob ? 4 : 5; ps.setString(paramIndex++, created.getName()); ps.setLong(paramIndex++, created.getSize()); setBigString(ps, paramIndex++, created.getMetadata()); ps.setString(paramIndex++, created.getMimeType()); if (replaceBinary) ps.setNull(paramIndex++, java.sql.Types.NUMERIC); //set preview ref to null ps.setBoolean(paramIndex++, created.isImage()); ps.setDouble(paramIndex++, created.getResolution()); ps.setInt(paramIndex++, created.getWidth()); ps.setInt(paramIndex++, created.getHeight()); ps.setString(paramIndex++, created.getMd5sum()); if (replaceBinary) { ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex, created.getQuality()); //quality } else ps.setString(paramIndex, binary.getHandle()); ps.executeUpdate(); if (needExplicitBlobInsert) { ps.close(); ps = con.prepareStatement( "UPDATE " + TBL_CONTENT_BINARY + " SET FBLOB=? WHERE ID=? AND VER=? AND QUALITY=?"); fis = new FileInputStream(binaryTransit); ps.setBinaryStream(1, fis, (int) binaryTransit.length()); ps.setLong(2, created.getId()); ps.setInt(3, created.getVersion()); //version ps.setInt(4, created.getQuality()); //quality ps.executeUpdate(); } if (removeTransitFile && binaryTransit != null) { //transit file was a temp. file -> got to clean up FxFileUtils.removeFile(binaryTransit); } if (replaceBinary) { ps.close(); //set all preview entries to the values provided by the transit table ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1=NULL,PREV2=NULL,PREV3=NULL,PREV4=NULL WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ps.executeUpdate(); ps.close(); ps = con.prepareStatement( "SELECT PREV1_WIDTH,PREV1_HEIGHT,PREV1SIZE,PREV2_WIDTH,PREV2_HEIGHT,PREV2SIZE,PREV3_WIDTH,PREV3_HEIGHT,PREV3SIZE,PREV4_WIDTH,PREV4_HEIGHT,PREV4SIZE FROM " + TBL_BINARY_TRANSIT + " WHERE BKEY=?"); ps.setString(1, binary.getHandle()); ResultSet rsPrev = ps.executeQuery(); if (rsPrev != null && rsPrev.next()) { long[] data = new long[12]; for (int d = 0; d < 12; d++) data[d] = rsPrev.getLong(d + 1); ps.close(); ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1_WIDTH=?,PREV1_HEIGHT=?,PREV1SIZE=?,PREV2_WIDTH=?,PREV2_HEIGHT=?,PREV2SIZE=?,PREV3_WIDTH=?,PREV3_HEIGHT=?,PREV3SIZE=?,PREV4_WIDTH=?,PREV4_HEIGHT=?,PREV4SIZE=? WHERE ID=? AND VER=? AND QUALITY=?"); for (int d = 0; d < 12; d++) ps.setLong(d + 1, data[d]); ps.setLong(13, created.getId()); ps.setInt(14, created.getVersion()); //version ps.setInt(15, created.getQuality()); //quality ps.executeUpdate(); } } //finally fetch the preview blobs from transit and store them on the filesystem if required if (storePrev1FS || storePrev2FS || storePrev3FS || storePrev4FS) { ps.close(); previewSelect = (!storePrev1FS ? ",NULL" : ",PREV1") + (!storePrev2FS ? ",NULL" : ",PREV2") + (!storePrev3FS ? ",NULL" : ",PREV3") + (!storePrev4FS ? ",NULL" : ",PREV4"); ps = con.prepareStatement("SELECT " + previewSelect.substring(1) + BINARY_TRANSIT_PREVIEW_WHERE); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); if (storePrev1FS) try { if (!FxFileUtils.copyStream2File(prev1Length, rs.getBinaryStream(1), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW1.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]", e.getMessage()); } if (storePrev2FS) try { if (!FxFileUtils.copyStream2File(prev2Length, rs.getBinaryStream(2), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW2.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]", e.getMessage()); } if (storePrev3FS) try { if (!FxFileUtils.copyStream2File(prev3Length, rs.getBinaryStream(3), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW3.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]", e.getMessage()); } if (storePrev4FS) try { if (!FxFileUtils.copyStream2File(prev4Length, rs.getBinaryStream(4), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.SCREENVIEW.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]", e.getMessage()); } } con.commit(); } catch (SQLException e) { throw new FxDbException(e, "ex.db.sqlError", e.getMessage()); } catch (FileNotFoundException e) { throw new FxDbException(e, "ex.content.binary.IOError", binary.getHandle()); } finally { Database.closeObjects(GenericBinarySQLStorage.class, con, ps); FxSharedUtils.close(fis); } return created; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String putMacroAction(int userId, Integer macro, String role, String data) { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String sql = ""; PreparedStatement st; Integer output = 0;// w w w.j a v a 2 s . c o m try { DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); InputSource is = new InputSource(new StringReader(data)); Document doc = documentBuilder.parse(is); Element root = doc.getDocumentElement(); boolean rd = "true".equals(root.getAttribute("read")) ? true : false; boolean wr = "true".equals(root.getAttribute("write")) ? true : false; boolean dl = "true".equals(root.getAttribute("delete")) ? true : false; boolean ad = "true".equals(root.getAttribute("add")) ? true : false; Node typesNode = doc.getElementsByTagName("types").item(0); Node types = typesNode.getFirstChild(); String typesText = ""; if (types != null) typesText = types.getNodeValue(); Node rulesNode = doc.getElementsByTagName("rules").item(0); Node rules = rulesNode.getFirstChild(); String rulesText = ""; if (rules != null) rulesText = rules.getNodeValue(); sql = "UPDATE rule_table SET RD=?, WR=?, DL=?, AD=?, " + "types_id=?, rules_id=? " + "WHERE rule_id=? AND role=?"; st = connection.prepareStatement(sql); st.setBoolean(1, rd); st.setBoolean(2, wr); st.setBoolean(3, dl); st.setBoolean(4, ad); st.setString(5, typesText); st.setString(6, rulesText); st.setInt(7, macro); st.setString(8, role); output = st.executeUpdate(); st.close(); } catch (Exception e) { e.printStackTrace(); } return output.toString(); }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String postAddAction(int userId, Integer macro, String role, String data) { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String sql = ""; PreparedStatement st; Integer output = 0;/*from w w w . j a v a 2 s .co m*/ try { DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); InputSource is = new InputSource(new StringReader(data)); Document doc = documentBuilder.parse(is); Element root = doc.getDocumentElement(); boolean rd = "true".equals(root.getAttribute("read")) ? true : false; boolean wr = "true".equals(root.getAttribute("write")) ? true : false; boolean dl = "true".equals(root.getAttribute("delete")) ? true : false; boolean ad = "true".equals(root.getAttribute("add")) ? true : false; Node typesNode = doc.getElementsByTagName("types").item(0); Node types = typesNode.getFirstChild(); String typesText = ""; if (types != null) typesText = types.getNodeValue(); Node rulesNode = doc.getElementsByTagName("rules").item(0); Node rules = rulesNode.getFirstChild(); String rulesText = ""; if (rules != null) rulesText = rules.getNodeValue(); sql = "INSERT INTO rule_table(rule_id, role, RD, WR, DL, AD, types_id, rules_id) " + "VALUE(?,?,?,?,?,?,?,?)"; st = connection.prepareStatement(sql); st.setInt(1, macro); st.setString(2, role); st.setBoolean(3, rd); st.setBoolean(4, wr); st.setBoolean(5, dl); st.setBoolean(6, ad); st.setString(7, typesText); st.setString(8, rulesText); output = st.executeUpdate(); st.close(); } catch (Exception e) { e.printStackTrace(); } return output.toString(); }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * /*w ww . ja v a2s . c o m*/ */ 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(); } }