List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.exist.xquery.modules.sql.ExecuteFunction.java
/** * evaluate the call to the XQuery execute() function, it is really the main entry point of this class. * * @param args arguments from the execute() function call * @param contextSequence the Context Sequence to operate on (not used here internally!) * * @return A node representing the SQL result set * * @throws XPathException DOCUMENT ME! * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence) *///from w w w. jav a 2s . com @Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a connection and SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); } // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection con = SQLModule.retrieveConnection(context, connectionUID); if (con == null) { return (Sequence.EMPTY_SEQUENCE); } boolean preparedStmt = false; //setup the SQL statement String sql = null; Statement stmt = null; boolean executeResult = false; ResultSet rs = null; try { boolean makeNodeFromColumnName = false; MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; //SQL or PreparedStatement? if (args.length == 3) { // get the SQL statement sql = args[1].getStringValue(); stmt = con.createStatement(); makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue(); //execute the statement executeResult = stmt.execute(sql); } else if (args.length == 4) { preparedStmt = true; //get the prepared statement long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong(); PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID); sql = stmtWithSQL.getSql(); stmt = stmtWithSQL.getStmt(); makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue(); if (!args[2].isEmpty()) { setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0)); } //execute the prepared statement executeResult = ((PreparedStatement) stmt).execute(); } else { //TODO throw exception } // DW: stmt can be null ? // execute the query statement if (executeResult) { /* SQL Query returned results */ // iterate through the result set building an XML document rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int iColumns = rsmd.getColumnCount(); builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); while (rs.next()) { builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colElement = "field"; if (makeNodeFromColumnName && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); if (!makeNodeFromColumnName || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); //get the content if (rsmd.getColumnType(i + 1) == Types.SQLXML) { //parse sqlxml value try { final SQLXML sqlXml = rs.getSQLXML(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setNamespaceAware(true); InputSource src = new InputSource(sqlXml.getCharacterStream()); SAXParser parser = factory.newSAXParser(); XMLReader xr = parser.getXMLReader(); SAXAdapter adapter = new AppendingSAXAdapter(builder); xr.setContentHandler(adapter); xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter); xr.parse(src); } } catch (Exception e) { throw new XPathException( "Could not parse column of type SQLXML: " + e.getMessage(), e); } } else { //otherwise assume string value final String colValue = rs.getString(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } } } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); } else { /* SQL Query performed updates */ builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount())); builder.endElement(); } // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } catch (SQLException sqle) { LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(sqle.getSQLState()); builder.endElement(); builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); String state = sqle.getMessage(); if (state != null) { builder.characters(state); } builder.endElement(); builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(sql)); builder.endElement(); if (stmt instanceof PreparedStatement) { Element parametersElement = (Element) args[2].itemAt(0); if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); builder.startElement( new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); String value = param.getFirstChild().getNodeValue(); String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); builder.startElement( new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type); builder.characters(SQLUtils.escapeXmlText(value)); builder.endElement(); } builder.endElement(); } } builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(getLine())); builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn())); builder.endElement(); builder.endElement(); builder.endDocument(); return ((NodeValue) builder.getDocument().getDocumentElement()); } finally { // close any record set or statement if (rs != null) { try { rs.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } rs = null; } if (!preparedStmt && stmt != null) { try { stmt.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } stmt = null; } } }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
private Map<Long, Map<Integer, Map<String, Object>>> getMetaDataMaps(String channelId, List<Long> messageIds) { if (messageIds.size() > 1000) { throw new DonkeyDaoException("Only up to 1000 message Ids at a time are supported."); }/*from w w w .j a va2s. co m*/ Map<Long, Map<Integer, Map<String, Object>>> metaDataMaps = new HashMap<Long, Map<Integer, Map<String, Object>>>(); PreparedStatement statement = null; ResultSet resultSet = null; try { Map<String, Object> values = new HashMap<String, Object>(); values.put("localChannelId", getLocalChannelId(channelId)); values.put("messageIds", StringUtils.join(messageIds, ",")); // do not cache this statement since metadata columns may be added/removed statement = connection.prepareStatement(querySource.getQuery("getMetaDataMapByMessageId", values)); resultSet = statement.executeQuery(); while (resultSet.next()) { Long messageId = resultSet.getLong("message_id"); Integer metaDataId = resultSet.getInt("metadata_id"); Map<Integer, Map<String, Object>> connectorMetaDataMap = metaDataMaps.get(messageId); if (connectorMetaDataMap == null) { connectorMetaDataMap = new HashMap<Integer, Map<String, Object>>(); metaDataMaps.put(messageId, connectorMetaDataMap); } Map<String, Object> metaDataMap = connectorMetaDataMap.get(metaDataId); if (metaDataMap == null) { metaDataMap = new HashMap<String, Object>(); connectorMetaDataMap.put(metaDataId, metaDataMap); } ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int columnCount = resultSetMetaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { MetaDataColumnType metaDataColumnType = MetaDataColumnType .fromSqlType(resultSetMetaData.getColumnType(i)); Object value = null; switch (metaDataColumnType) {//@formatter:off case STRING: value = resultSet.getString(i); break; case NUMBER: value = resultSet.getBigDecimal(i); break; case BOOLEAN: value = resultSet.getBoolean(i); break; case TIMESTAMP: Timestamp timestamp = resultSet.getTimestamp(i); if (timestamp != null) { value = Calendar.getInstance(); ((Calendar) value).setTimeInMillis(timestamp.getTime()); } break; default: throw new Exception("Unrecognized MetaDataColumnType"); } //@formatter:on metaDataMap.put(resultSetMetaData.getColumnName(i).toUpperCase(), value); } } return metaDataMaps; } catch (Exception e) { throw new DonkeyDaoException(e); } finally { close(resultSet); close(statement); } }
From source file:org.exist.xquery.modules.oracle.ExecuteFunction.java
@Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { if (args.length == 5 || args.length == 6) { // was a connection and PL/SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); }// ww w .j a v a 2 s .c o m // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection connection = SQLModule.retrieveConnection(context, connectionUID); if (connection == null) { return (Sequence.EMPTY_SEQUENCE); } // get the PL/SQL statement String plSql = args[1].getStringValue(); // get the input parameters (if any) Element parameters = null; if (!args[2].isEmpty()) { parameters = (Element) args[2].itemAt(0); } // was a result set position specified? int resultSetPos = 0; if (!args[3].isEmpty()) { resultSetPos = ((IntegerValue) args[3].itemAt(0)).getInt(); } boolean haveReturnCode = false; int plSqlSuccess = 1; // default value of 1 for success if (args.length == 6) { // a return code is expected so what is the value indicating success? plSqlSuccess = ((IntegerValue) args[5].itemAt(0)).getInt(); haveReturnCode = true; } CallableStatement statement = null; ResultSet resultSet = null; try { MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; statement = connection.prepareCall(plSql); if (haveReturnCode) { statement.registerOutParameter(1, Types.NUMERIC); } if (resultSetPos != 0) { statement.registerOutParameter(resultSetPos, OracleTypes.CURSOR); } if (!args[2].isEmpty()) { setParametersOnPreparedStatement(statement, parameters); } statement.execute(); if (haveReturnCode) { int returnCode = statement.getInt(1); if (returnCode != plSqlSuccess) { LOG.error(plSql + " failed [" + returnCode + "]"); return (Sequence.EMPTY_SEQUENCE); } } if (resultSetPos != 0) { // iterate through the result set building an XML document builder.startDocument(); builder.startElement(new QName("result", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); resultSet = (ResultSet) statement.getObject(resultSetPos); ResultSetMetaData rsmd = resultSet.getMetaData(); int iColumns = rsmd.getColumnCount(); while (resultSet.next()) { builder.startElement(new QName("row", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(resultSet.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colValue = resultSet.getString(i + 1); String colElement = "field"; if (((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement( new QName(colElement, OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); if (!((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName("type", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName("type", Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); if (resultSet.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), "true"); } if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } else { // there was no result set so just return an empty sequence return (Sequence.EMPTY_SEQUENCE); } } catch (SQLException sqle) { LOG.error("oracle:execute() Caught SQLException \"" + sqle.getMessage() + "\" for PL/SQL: \"" + plSql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); String sqlState = sqle.getSQLState(); if (sqlState != null) { builder.characters(sqle.getSQLState()); } else { builder.characters("null"); } builder.endElement(); builder.startElement(new QName("message", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(sqle.getMessage()); builder.endElement(); builder.startElement(new QName("stack-trace", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("oracle", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(plSql)); builder.endElement(); int line = getLine(); int column = getColumn(); builder.startElement(new QName("xquery", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(line)); builder.addAttribute(new QName("column", null, null), String.valueOf(column)); builder.endElement(); builder.endElement(); builder.endDocument(); return (NodeValue) builder.getDocument().getDocumentElement(); } finally { release(connection, statement, resultSet); } } else { throw new XPathException("Invalid number of arguments [" + args.length + "]"); } }
From source file:ProcessRequest.java
public void parseQueryResults(ResultSet rs, String table, OutputStream os, boolean append) throws SQLException, JSONException, IOException { //JSONArray resultJSONArray = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); rs.last();// w w w. j a va2s . c o m int rows = rs.getRow(); os.write(new String("total rows: " + rows).getBytes()); rs.first(); int rowCount = 0; while (rs.next()) { if (!rs.isFirst() || append) { os.write(new String(",\n").getBytes()); os.write(new String("" + rowCount).getBytes()); } if (rowCount >= 69) System.out.println("break point"); rowCount++; JSONObject result = new JSONObject(); JSONObject resultMeta = new JSONObject(); resultMeta.put("table", table); result.put("metadata", resultMeta); for (int i = 1; i <= columns; i++) { //out.println("<td>"+rs.getString(i)+"</td>"); int type = rsmd.getColumnType(i); //result.put(rsmd.getColumnName(i), rs.get) switch (type) { case Types.BIT: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.TINYINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.SMALLINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.INTEGER: //System.out.println(rsmd.getColumnName(i) + " type: "+type); result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.BIGINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.FLOAT: result.put(rsmd.getColumnName(i), rs.getFloat(i)); break; case Types.REAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DOUBLE: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.NUMERIC: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DECIMAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.CHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.VARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.DATE: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIME: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIMESTAMP: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.BINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.VARBINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.LONGVARBINARY: result.put(rsmd.getColumnName(i), rs.getLong(i)); break; case Types.NULL: result.put(rsmd.getColumnName(i), ""); break; case Types.BOOLEAN: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.ROWID: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.NCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.NVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGNVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.SQLXML: case Types.NCLOB: case Types.DATALINK: case Types.REF: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.BLOB: case Types.CLOB: default: result.put(rsmd.getColumnName(i), rs.getString(i)); break; } } //if(table.equals("Ticket")) //System.out.println(result.toString(5)); //if(result.getInt("TicketNumber")==126868) // System.out.println("break point"); //resultJSONArray.put(result); os.write(result.toString(5).getBytes()); } //return resultJSONArray; }
From source file:com.egt.core.db.xdp.RecursoCachedRowSetDataProvider.java
private int getSqlColumnType(String fieldId) { try {/*ww w. ja v a 2 s . c o m*/ ResultSetMetaData rsmd = this.getCachedRowSet().getMetaData(); int numberOfColumns = rsmd.getColumnCount(); for (int i = 1; i <= numberOfColumns; i++) { if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) { return rsmd.getColumnType(i); } } } catch (Exception ex) { TLC.getBitacora().fatal(ex); } return java.sql.Types.OTHER; }
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/*w w w. ja v a 2s. c om*/ */ 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:org.eclipse.smila.connectivity.framework.crawler.jdbc.JdbcCrawler.java
/** * Populates the {@link #_groupingRanges}-{@link ArrayList} according to the configuration specified in the * {@link Grouping}-attribute of the {@link DataSourceConnectionConfig}. The SQL-statements needed for this are * executed via a local {@link Statement}-object, just as the data is retrieved via a local {@link ResultSet}-object. * //from w ww . j a v a 2 s . c om * @throws CrawlerCriticalException * If any of the following conditions occur: * <ul> * <li>Any of the columns used for grouping has a data type which is not supported: !(Number||String)</li> * <li>A SQLException is raised while retrieving the grouping data from the database</li> * </ul> */ private void prepareGrouping() throws CrawlerCriticalException { final Grouping grouping = _process.getSelections().getGrouping(); BigInteger stepping = BigInteger.ONE; ResultSet groupingResultSet = null; ResultSetMetaData groupingMetaData = null; if (grouping != null) { _groupingRanges = new ArrayList<GroupingRange>(); final String groupingSQL = grouping.getSQL(); stepping = grouping.getStepping(); Statement groupingStatement = null; try { groupingStatement = _connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); _log.debug("Executing SQL for grouping preparation: [" + groupingSQL + "]"); groupingResultSet = groupingStatement.executeQuery(groupingSQL); groupingMetaData = groupingResultSet.getMetaData(); _log.debug("Retrieved groupingResultSet with [" + groupingMetaData.getColumnCount() + "] columns"); for (int i = 1; i <= groupingMetaData.getColumnCount(); i++) { Class<?> columnClass = null; try { columnClass = Class.forName(groupingMetaData.getColumnClassName(i)); } catch (final ClassNotFoundException e) { _log.error("This should never happen: the class[" + groupingMetaData.getColumnClassName(i) + "] for the column " + i + " in the grouping result set could not be resolved"); } if (Number.class.isAssignableFrom(columnClass)) { _log.debug("RowNr " + i + " of the grouping result set is of type [" + columnClass.getName() + "], which is derived from [Number]: fine for use in a grouping"); continue; } else if (String.class.equals(columnClass)) { _log.debug("RowNr " + i + " of the grouping result set is of type [String]: fine for use in a grouping"); } else { throw new CrawlerCriticalException("RowNr " + i + " of the grouping result set is of type [" + columnClass.getName() + "]: NOT supported as a grouping field"); } } int groupingRecords = 0; PreparedStatementTypedParameter[] startValues = null; PreparedStatementTypedParameter[] endValues = null; final PreparedStatementTypedParameter[] finalValues = new PreparedStatementTypedParameter[groupingMetaData .getColumnCount()]; while (groupingResultSet.next()) { if (groupingRecords == 0) { startValues = new PreparedStatementTypedParameter[groupingMetaData.getColumnCount()]; for (int i = 1; i <= groupingMetaData.getColumnCount(); i++) { startValues[i - 1] = new PreparedStatementTypedParameter(groupingResultSet.getObject(i), (i * 2) - 1, groupingMetaData.getColumnType(i)); } } groupingRecords++; if (groupingRecords == stepping.intValue()) { endValues = new PreparedStatementTypedParameter[groupingMetaData.getColumnCount()]; for (int i = 1; i <= groupingMetaData.getColumnCount(); i++) { endValues[i - 1] = new PreparedStatementTypedParameter(groupingResultSet.getObject(i), i * 2, groupingMetaData.getColumnType(i)); } final GroupingRange groupingRange = new GroupingRange(startValues, endValues); _groupingRanges.add(groupingRange); if (_log.isTraceEnabled()) { _log.trace( "Added GroupingRange: [" + groupingRange.toString() + "] to _groupingRanges"); } groupingRecords = 0; continue; } for (int i = 1; i <= groupingMetaData.getColumnCount(); i++) { finalValues[i - 1] = new PreparedStatementTypedParameter(groupingResultSet.getObject(i), i * 2, groupingMetaData.getColumnType(i)); } } if (groupingRecords != 0 && stepping.intValue() != 1) { final GroupingRange finalgroupingRange = new GroupingRange(startValues, finalValues); _groupingRanges.add(finalgroupingRange); _log.debug( "Added final GroupingRange [" + finalgroupingRange.toString() + "] to _groupingRanges"); } } catch (final SQLException e1) { throw new CrawlerCriticalException("Encountered SQLException while preparing Groupings"); } finally { try { if (groupingStatement != null) { groupingStatement.close(); } } catch (final SQLException e) { _log.error("Could not closeGrouping statement"); } try { groupingResultSet.close(); _log.debug("Closed Grouping Resultset"); } catch (final SQLException e) { _log.error("Could not close Resultset for Grouping statement"); } } } // set current grouping to first grouping in list (if list is not empty) _groupingRangesIterator = _groupingRanges.iterator(); if (_groupingRangesIterator.hasNext()) { _currentGroupingRange = _groupingRangesIterator.next(); } _log.debug(String.format("Prepared %d grouping ranges based on specified stepping of %d", _groupingRanges.size(), stepping.intValue())); }
From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java
/** * Use a ResultSet to populate the MetaData information * // ww w . ja v a 2 s.c o m * @param rs * @throws SQLException * @throws ConsistencyError */ private void setMetaData(ResultSet rs) throws SQLException, ConsistencyError { assert (fields == null); fields = new LinkedHashMap<String, FieldInfo>(); ResultSetMetaData meta_data = rs.getMetaData(); int md_columns = meta_data.getColumnCount(); boolean seen_key = false; // Logger log = ctx.getLogger(getClass()); for (int i = 1; i <= md_columns; i++) { String returned_name = meta_data.getTableName(i); if (returned_name.length() > 0 && !returned_name.equalsIgnoreCase(table_name)) { throw new ConsistencyError("Table names do not match " + getTag() + "!=" + returned_name); } // if we don't know for sure assume no nulls boolean can_null = (meta_data.isNullable(i) == ResultSetMetaData.columnNullable); String name = meta_data.getColumnName(i); if (!seen_key && meta_data.isAutoIncrement(i)) { seen_key = true; id_name = name; } else { // log.debug("Metadata "+name+" "+meta_data.getColumnType(i)); int columnType = meta_data.getColumnType(i); int columnDisplaySize = meta_data.getColumnDisplaySize(i); fields.put(dbFieldtoTag(name), new FieldInfo(name, columnType, columnDisplaySize, can_null)); } } if (use_id && !seen_key) { // Note we need an up-to-date mysql driver for the isAutoIncrement // method to work properly. otherwise default to first col and hope id_name = meta_data.getColumnName(1); fields.remove(dbFieldtoTag(id_name)); } // cache the qualified form as this is used frequently StringBuilder sb = new StringBuilder(); sb.append(alias_name); sb.append("."); sb.append(id_name); qualified_id_name = sb.toString(); }
From source file:ips1ap101.lib.core.db.xdp.RecursoCachedRowSetDataProvider.java
private int getSqlColumnType(String fieldId) { try {//from w ww .j av a 2 s . co m ResultSetMetaData rsmd = getCachedRowSet().getMetaData(); int numberOfColumns = rsmd.getColumnCount(); for (int i = 1; i <= numberOfColumns; i++) { if (rsmd.getColumnName(i).equalsIgnoreCase(fieldId)) { return rsmd.getColumnType(i); } } } catch (Exception ex) { TLC.getBitacora().fatal(ex); } return java.sql.Types.OTHER; }