List of usage examples for java.sql ResultSet getBinaryStream
java.io.InputStream getBinaryStream(String columnLabel) throws SQLException;
ResultSet object as a stream of uninterpreted bytes. From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Convert the specified column of the SQL ResultSet to the proper * java type./*from w ww .ja v a2 s . c om*/ */ public InputStream getBinaryStream(ResultSet rs, int column) throws SQLException { return rs.getBinaryStream(column); }
From source file:com.flexive.core.storage.GenericDivisionExporter.java
/** * Dump a generic table to XML/*from w ww .j ava 2s.c om*/ * * @param tableName name of the table * @param stmt an open statement * @param out output stream * @param sb an available and valid StringBuilder * @param xmlTag name of the xml tag to write per row * @param idColumn (optional) id column to sort results * @param onlyBinaries process binary fields (else these will be ignored) * @throws SQLException on errors * @throws IOException on errors */ private void dumpTable(String tableName, Statement stmt, OutputStream out, StringBuilder sb, String xmlTag, String idColumn, boolean onlyBinaries) throws SQLException, IOException { ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + (StringUtils.isEmpty(idColumn) ? "" : " ORDER BY " + idColumn + " ASC")); final ResultSetMetaData md = rs.getMetaData(); String value, att; boolean hasSubTags; while (rs.next()) { hasSubTags = false; if (!onlyBinaries) { sb.setLength(0); sb.append(" <").append(xmlTag); } for (int i = 1; i <= md.getColumnCount(); i++) { value = null; att = md.getColumnName(i).toLowerCase(); switch (md.getColumnType(i)) { case java.sql.Types.DECIMAL: case java.sql.Types.NUMERIC: case java.sql.Types.BIGINT: if (!onlyBinaries) { value = String.valueOf(rs.getBigDecimal(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.INTEGER: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (!onlyBinaries) { value = String.valueOf(rs.getLong(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (!onlyBinaries) { value = String.valueOf(rs.getDouble(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (!onlyBinaries) { final Timestamp ts = rs.getTimestamp(i); if (rs.wasNull()) value = null; else value = FxFormatUtils.getDateTimeFormat().format(ts); } break; case java.sql.Types.BIT: case java.sql.Types.CHAR: case java.sql.Types.BOOLEAN: if (!onlyBinaries) { value = rs.getBoolean(i) ? "1" : "0"; if (rs.wasNull()) value = null; } break; case java.sql.Types.CLOB: case java.sql.Types.BLOB: case java.sql.Types.LONGVARBINARY: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARBINARY: case java.sql.Types.VARCHAR: case java.sql.Types.BINARY: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: hasSubTags = true; break; default: LOG.warn("Unhandled type [" + md.getColumnType(i) + "] for [" + tableName + "." + att + "]"); } if (value != null && !onlyBinaries) sb.append(' ').append(att).append("=\"").append(value).append("\""); } if (hasSubTags) { if (!onlyBinaries) sb.append(">\n"); for (int i = 1; i <= md.getColumnCount(); i++) { switch (md.getColumnType(i)) { case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: case java.sql.Types.BINARY: if (idColumn == null) throw new IllegalArgumentException("Id column required to process binaries!"); String binFile = FOLDER_BINARY + "/BIN_" + String.valueOf(rs.getLong(idColumn)) + "_" + i + ".blob"; att = md.getColumnName(i).toLowerCase(); if (onlyBinaries) { if (!(out instanceof ZipOutputStream)) throw new IllegalArgumentException( "out has to be a ZipOutputStream to store binaries!"); ZipOutputStream zip = (ZipOutputStream) out; InputStream in = rs.getBinaryStream(i); if (rs.wasNull()) break; ZipEntry ze = new ZipEntry(binFile); zip.putNextEntry(ze); byte[] buffer = new byte[4096]; int read; while ((read = in.read(buffer)) != -1) zip.write(buffer, 0, read); in.close(); zip.closeEntry(); zip.flush(); } else { InputStream in = rs.getBinaryStream(i); //need to fetch to see if it is empty if (rs.wasNull()) break; in.close(); sb.append(" <").append(att).append(">").append(binFile).append("</").append(att) .append(">\n"); } break; case java.sql.Types.CLOB: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: if (!onlyBinaries) { value = rs.getString(i); if (rs.wasNull()) break; att = md.getColumnName(i).toLowerCase(); sb.append(" <").append(att).append('>'); escape(sb, value); sb.append("</").append(att).append(">\n"); } break; } } if (!onlyBinaries) sb.append(" </").append(xmlTag).append(">\n"); } else { if (!onlyBinaries) sb.append("/>\n"); } if (!onlyBinaries) write(out, sb); } }
From source file:axiom.objectmodel.db.NodeManager.java
/** * Create a new Node from a ResultSet./*w w w.j av a 2 s . c om*/ */ public Node createNode(DbMapping dbm, ResultSet rs, DbColumn[] columns, int offset) throws SQLException, IOException, ClassNotFoundException { HashMap propBuffer = new HashMap(); String id = null; String name = null; String protoName = dbm.getTypeName(); DbMapping dbmap = dbm; Node node = new Node(); for (int i = 0; i < columns.length; i++) { // set prototype? if (columns[i].isPrototypeField()) { protoName = rs.getString(i + 1 + offset); if (protoName != null) { dbmap = getDbMapping(protoName); if (dbmap == null) { // invalid prototype name! app.logError(ErrorReporter.errorMsg(this.getClass(), "createNode") + "Invalid prototype name: " + protoName + " - using default"); dbmap = dbm; protoName = dbmap.getTypeName(); } } } // set id? if (columns[i].isIdField()) { id = rs.getString(i + 1 + offset); // if id == null, the object doesn't actually exist - return null if (id == null) { return null; } } // set name? if (columns[i].isNameField()) { name = rs.getString(i + 1 + offset); } Property newprop = new Property(node); switch (columns[i].getType()) { case Types.BIT: newprop.setBooleanValue(rs.getBoolean(i + 1 + offset)); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: newprop.setIntegerValue(rs.getLong(i + 1 + offset)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: newprop.setFloatValue(rs.getDouble(i + 1 + offset)); break; case Types.DECIMAL: case Types.NUMERIC: BigDecimal num = rs.getBigDecimal(i + 1 + offset); if (num == null) { break; } if (num.scale() > 0) { newprop.setFloatValue(num.doubleValue()); } else { newprop.setIntegerValue(num.longValue()); } break; case Types.VARBINARY: case Types.BINARY: // newprop.setStringValue(rs.getString(i+1+offset)); newprop.setJavaObjectValue(rs.getBytes(i + 1 + offset)); break; case Types.LONGVARBINARY: { InputStream in = rs.getBinaryStream(i + 1 + offset); if (in == null) { break; } ByteArrayOutputStream bout = new ByteArrayOutputStream(); byte[] buffer = new byte[2048]; int read; while ((read = in.read(buffer)) > -1) { bout.write(buffer, 0, read); } newprop.setJavaObjectValue(bout.toByteArray()); } break; case Types.LONGVARCHAR: try { newprop.setStringValue(rs.getString(i + 1 + offset)); } catch (SQLException x) { Reader in = rs.getCharacterStream(i + 1 + offset); char[] buffer = new char[2048]; int read = 0; int r; while ((r = in.read(buffer, read, buffer.length - read)) > -1) { read += r; if (read == buffer.length) { // grow input buffer char[] newBuffer = new char[buffer.length * 2]; System.arraycopy(buffer, 0, newBuffer, 0, buffer.length); buffer = newBuffer; } } newprop.setStringValue(new String(buffer, 0, read)); } break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: newprop.setStringValue(rs.getString(i + 1 + offset)); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newprop.setDateValue(rs.getTimestamp(i + 1 + offset)); break; case Types.NULL: newprop.setStringValue(null); break; case Types.CLOB: Clob cl = rs.getClob(i + 1 + offset); if (cl == null) { newprop.setStringValue(null); break; } char[] c = new char[(int) cl.length()]; Reader isr = cl.getCharacterStream(); isr.read(c); newprop.setStringValue(String.copyValueOf(c)); break; default: newprop.setStringValue(rs.getString(i + 1 + offset)); break; } if (rs.wasNull()) { newprop.setStringValue(null); } propBuffer.put(columns[i].getName(), newprop); // mark property as clean, since it's fresh from the db newprop.dirty = false; } if (id == null) { return null; } Hashtable propMap = new Hashtable(); DbColumn[] columns2 = dbmap.getColumns(); for (int i = 0; i < columns2.length; i++) { Relation rel = columns2[i].getRelation(); if (rel != null && (rel.reftype == Relation.PRIMITIVE || rel.reftype == Relation.REFERENCE)) { Property prop = (Property) propBuffer.get(columns2[i].getName()); if (prop == null) { continue; } prop.setName(rel.propName); // if the property is a pointer to another node, change the property type to NODE if ((rel.reftype == Relation.REFERENCE) && rel.usesPrimaryKey()) { // FIXME: References to anything other than the primary key are not supported prop.convertToNodeReference(rel.otherType, this.app.getCurrentRequestEvaluator().getLayer()); } propMap.put(rel.propName.toLowerCase(), prop); } } node.init(dbmap, id, name, protoName, propMap, safe); return node; }
From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java
private void assertWrongValueFormatColumn(final ResultSet rs) throws Exception { assertFalse(rs.getBoolean(2));/*from w w w. java 2 s. c om*/ assertFalse(rs.getBoolean("ename")); try { rs.getShort(2); fail(); } catch (SQLException ignore) { } try { rs.getShort("ename"); fail(); } catch (SQLException ignore) { } try { rs.getInt(2); fail(); } catch (SQLException ignore) { } try { rs.getInt("ename"); fail(); } catch (SQLException ignore) { } try { rs.getLong(2); fail(); } catch (SQLException ignore) { } try { rs.getLong("ename"); fail(); } catch (SQLException ignore) { } try { rs.getFloat(2); fail(); } catch (SQLException ignore) { } try { rs.getFloat("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDouble(2); fail(); } catch (SQLException ignore) { } try { rs.getDouble("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(2); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBytes(2); fail(); } catch (SQLException ignore) { } try { rs.getBytes("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDate(2); fail(); } catch (SQLException ignore) { } try { rs.getDate("ename"); fail(); } catch (SQLException ignore) { } try { rs.getDate(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getDate("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTime(2); fail(); } catch (SQLException ignore) { } try { rs.getTime("ename"); fail(); } catch (SQLException ignore) { } try { rs.getTime(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTime("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(2); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp("ename"); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(2, Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp("ename", Calendar.getInstance()); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(2); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream("ename"); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(2); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream("ename"); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(2); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream("ename"); fail(); } catch (SQLException ignore) { } }
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private HashMap<Integer, Element> findAdminMenuElements(Document doc, Connection conn, String[] groupKeys, String anonGroupKey, boolean adminRights, boolean includeReadOnlyAccessRight) throws SQLException { // Find access rights HashMap<Integer, Integer> accessRights = findAdminMenuAccessRights(conn, groupKeys, anonGroupKey, adminRights);/*from w w w .j a va 2 s . com*/ // Composte the sql StringBuffer sql = new StringBuffer("SELECT "); sql.append(this.db.tMenu.men_lKey.getName()).append(", "); sql.append(this.db.tMenu.men_sName.getName()).append(", "); sql.append(this.db.tLanguage.lan_sCode.getName()).append(", "); sql.append(this.db.tMenu.men_mei_firstPage.getName()).append(", "); sql.append(this.db.tMenu.men_mei_loginPage.getName()).append(", "); sql.append(this.db.tMenu.men_mei_errorPage.getName()).append(", "); sql.append(this.db.tMenu.men_pat_lKey.getName()).append(", "); sql.append(this.db.tMenu.men_xmlData.getName()); sql.append(" FROM ").append(this.db.tMenu.getName()); sql.append(" LEFT JOIN ").append(this.db.tLanguage.getName()); sql.append(" ON ").append(this.db.tMenu.men_lan_lKey.getName()).append(" = ") .append(this.db.tLanguage.lan_lKey.getName()); if (!adminRights) { sql.append(" WHERE ("); StringBuffer grpSql = new StringBuffer(); grpSql.append(" IN ("); for (int i = 0; i < groupKeys.length; i++) { if (i > 0) { grpSql.append(", "); } grpSql.append("'").append(groupKeys[i]).append("'"); } grpSql.append(")"); sql.append("EXISTS (SELECT * FROM ").append(this.db.tDefaultMenuAR.getName()); sql.append(" WHERE ").append(this.db.tDefaultMenuAR.dma_men_lKey.getName()); sql.append(" = ").append(this.db.tMenu.men_lKey.getName()).append(" AND "); sql.append(this.db.tDefaultMenuAR.dma_grp_hKey.getName()).append(grpSql).append(" AND ("); sql.append(this.db.tDefaultMenuAR.dma_bAdd.getName()).append(" = 1 OR "); sql.append(this.db.tDefaultMenuAR.dma_bAdministrate.getName()).append(" = 1 OR "); sql.append(this.db.tDefaultMenuAR.dma_bCreate.getName()).append(" = 1 OR "); sql.append(this.db.tDefaultMenuAR.dma_bUpdate.getName()).append(" = 1 OR "); if (includeReadOnlyAccessRight) { sql.append(this.db.tDefaultMenuAR.dma_bRead.getName()).append(" = 1 OR "); } sql.append(this.db.tDefaultMenuAR.dma_bDelete.getName()).append(" = 1)) OR "); sql.append("EXISTS (SELECT * FROM ").append(this.db.tMenuItemAR.getName()); sql.append(" WHERE ").append(this.db.tMenuItemAR.mia_mei_lKey.getName()).append(" IN ("); sql.append("SELECT ").append(this.db.tMenuItem.mei_lKey.getName()).append(" FROM "); sql.append(this.db.tMenuItem.getName()).append(" WHERE "); sql.append(this.db.tMenuItem.mei_men_lKey.getName()).append(" = ") .append(this.db.tMenu.men_lKey.getName()); sql.append(") AND ").append(this.db.tMenuItemAR.mia_grp_hKey.getName()).append(grpSql).append(" AND ("); sql.append(this.db.tMenuItemAR.mia_bAdd.getName()).append(" = 1 OR "); sql.append(this.db.tMenuItemAR.mia_bAdministrate.getName()).append(" = 1 OR "); sql.append(this.db.tMenuItemAR.mia_bCreate.getName()).append(" = 1 OR "); sql.append(this.db.tMenuItemAR.mia_bUpdate.getName()).append(" = 1 OR "); if (includeReadOnlyAccessRight) { sql.append(this.db.tMenuItemAR.mia_bRead.getName()).append(" = 1 OR "); } sql.append(this.db.tMenuItemAR.mia_bDelete.getName()).append(" = 1))"); sql.append(")"); } // Execute the sql HashMap<Integer, Element> elements = new HashMap<Integer, Element>(); PreparedStatement stmt = null; ResultSet result = null; try { stmt = conn.prepareStatement(sql.toString()); result = stmt.executeQuery(); while (result.next()) { Integer menKey = result.getInt(1); String name = result.getString(2); String languageCode = result.getString(3); Element elem = doc.createElement("menu"); elem.setAttribute("key", menKey.toString()); elem.setAttribute("name", name); appendMenuAccessRights(elem, accessRights.get(menKey)); elem.setAttribute("language", languageCode); int firstPage = result.getInt(db.tMenu.men_mei_firstPage.getName()); if (!result.wasNull()) { elem.setAttribute("firstpage", String.valueOf(firstPage)); } int loginPage = result.getInt(db.tMenu.men_mei_loginPage.getName()); if (!result.wasNull()) { elem.setAttribute("loginpage", String.valueOf(loginPage)); } int errorPage = result.getInt(db.tMenu.men_mei_errorPage.getName()); if (!result.wasNull()) { elem.setAttribute("errorpage", String.valueOf(errorPage)); } int defaultPageTemplate = result.getInt(db.tMenu.men_pat_lKey.getName()); if (!result.wasNull()) { elem.setAttribute("defaultpagetemplate", String.valueOf(defaultPageTemplate)); } InputStream is = result.getBinaryStream("men_xmlData"); if (!result.wasNull()) { Document menuDataDoc = XMLTool.domparse(is); boolean allowUrl = XMLTool.selectNode(menuDataDoc.getDocumentElement(), "pagetypes/allow[@type = 'url']") != null; boolean allowLabel = XMLTool.selectNode(menuDataDoc.getDocumentElement(), "pagetypes/allow[@type = 'label']") != null; boolean allowSection = XMLTool.selectNode(menuDataDoc.getDocumentElement(), "pagetypes/allow[@type = 'section']") != null; elem.setAttribute("allowurl", String.valueOf(allowUrl)); elem.setAttribute("allowlabel", String.valueOf(allowLabel)); elem.setAttribute("allowsection", String.valueOf(allowSection)); Element defaultCSSElem = XMLTool.getElement(menuDataDoc.getDocumentElement(), "defaultcss"); if (defaultCSSElem != null) { String defaultCssKey = defaultCSSElem.getAttribute("key"); if (StringUtils.isNotEmpty(defaultCssKey)) { ResourceKey resourceKey = new ResourceKey(defaultCSSElem.getAttribute("key")); elem.setAttribute("defaultcss", resourceKey.toString()); elem.setAttribute("defaultcssexists", resourceDao.getResourceFile(resourceKey) != null ? "true" : "false"); } } } elements.put(menKey, elem); } } finally { close(result); close(stmt); } return elements; }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private DataEntry getDataEntryFromRS(ResultSet rs) throws SQLException { DataEntry dataEntry = new DataEntry(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); int columnType; String value;/*w w w.ja v a 2s.c o m*/ ParamValue paramValue; Time sqlTime; Date sqlDate; Timestamp sqlTimestamp; Blob sqlBlob; BigDecimal bigDecimal; InputStream binInStream; boolean useColumnNumbers = this.isUsingColumnNumbers(); for (int i = 1; i <= columnCount; i++) { /* retrieve values according to the column type */ columnType = metaData.getColumnType(i); switch (columnType) { /* handle string types */ case Types.VARCHAR: /* fall through */ case Types.LONGVARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.CLOB: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NCLOB: /* fall through */ case Types.NVARCHAR: /* fall through */ case Types.LONGNVARCHAR: value = rs.getString(i); paramValue = new ParamValue(value); break; /* handle numbers */ case Types.INTEGER: /* fall through */ case Types.TINYINT: /* fall through */ case Types.SMALLINT: value = ConverterUtil.convertToString(rs.getInt(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DOUBLE: value = ConverterUtil.convertToString(rs.getDouble(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.FLOAT: value = ConverterUtil.convertToString(rs.getFloat(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.BOOLEAN: /* fall through */ case Types.BIT: value = ConverterUtil.convertToString(rs.getBoolean(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; case Types.DECIMAL: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; /* handle data/time values */ case Types.TIME: /* handle time data type */ sqlTime = rs.getTime(i); if (sqlTime != null) { value = this.convertToTimeString(sqlTime); } else { value = null; } paramValue = new ParamValue(value); break; case Types.DATE: /* handle date data type */ sqlDate = rs.getDate(i); if (sqlDate != null) { value = ConverterUtil.convertToString(sqlDate); } else { value = null; } paramValue = new ParamValue(value); break; case Types.TIMESTAMP: sqlTimestamp = rs.getTimestamp(i, calendar); if (sqlTimestamp != null) { value = this.convertToTimestampString(sqlTimestamp); } else { value = null; } paramValue = new ParamValue(value); break; /* handle binary types */ case Types.BLOB: sqlBlob = rs.getBlob(i); if (sqlBlob != null) { value = this.getBase64StringFromInputStream(sqlBlob.getBinaryStream()); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BINARY: /* fall through */ case Types.LONGVARBINARY: /* fall through */ case Types.VARBINARY: binInStream = rs.getBinaryStream(i); if (binInStream != null) { value = this.getBase64StringFromInputStream(binInStream); } else { value = null; } paramValue = new ParamValue(value); break; /* handling User Defined Types */ case Types.STRUCT: Struct udt = (Struct) rs.getObject(i); paramValue = new ParamValue(udt); break; case Types.ARRAY: paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); Array dataArray = (Array) rs.getObject(i); if (dataArray == null) { break; } paramValue = this.processSQLArray(dataArray, paramValue); break; case Types.NUMERIC: bigDecimal = rs.getBigDecimal(i); if (bigDecimal != null) { value = ConverterUtil.convertToString(bigDecimal); } else { value = null; } paramValue = new ParamValue(value); break; case Types.BIGINT: value = ConverterUtil.convertToString(rs.getLong(i)); paramValue = new ParamValue(rs.wasNull() ? null : value); break; /* handle all other types as strings */ default: value = rs.getString(i); paramValue = new ParamValue(value); break; } dataEntry.addValue(useColumnNumbers ? Integer.toString(i) : metaData.getColumnLabel(i), paramValue); } return dataEntry; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testDataTypes() throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select * from " + dataTypeTableName + " order by c1"); ResultSetMetaData meta = res.getMetaData(); // row 1/*from ww w. j av a 2s. com*/ assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // getXXX returns 0 for numeric types, false for boolean and null for other assertEquals(0, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(0d, res.getDouble(3), floatCompareDelta); assertEquals(null, res.getString(4)); assertEquals(null, res.getString(5)); assertEquals(null, res.getString(6)); assertEquals(null, res.getString(7)); assertEquals(null, res.getString(8)); assertEquals(0, res.getByte(9)); assertEquals(0, res.getShort(10)); assertEquals(0f, res.getFloat(11), floatCompareDelta); assertEquals(0L, res.getLong(12)); assertEquals(null, res.getString(13)); assertEquals(null, res.getString(14)); assertEquals(null, res.getString(15)); assertEquals(null, res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getString(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); // row 2 assertTrue(res.next()); assertEquals(-1, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(-1.1d, res.getDouble(3), floatCompareDelta); assertEquals("", res.getString(4)); assertEquals("[]", res.getString(5)); assertEquals("{}", res.getString(6)); assertEquals("{}", res.getString(7)); assertEquals("{\"r\":null,\"s\":null,\"t\":null}", res.getString(8)); assertEquals(-1, res.getByte(9)); assertEquals(-1, res.getShort(10)); assertEquals(-1.0f, res.getFloat(11), floatCompareDelta); assertEquals(-1, res.getLong(12)); assertEquals("[]", res.getString(13)); assertEquals("{}", res.getString(14)); assertEquals("{\"r\":null,\"s\":null}", res.getString(15)); assertEquals("[]", res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getTimestamp(17)); assertEquals(null, res.getBigDecimal(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); assertEquals(null, res.getString(23)); // row 3 assertTrue(res.next()); assertEquals(1, res.getInt(1)); assertEquals(true, res.getBoolean(2)); assertEquals(1.1d, res.getDouble(3), floatCompareDelta); assertEquals("1", res.getString(4)); assertEquals("[1,2]", res.getString(5)); assertEquals("{1:\"x\",2:\"y\"}", res.getString(6)); assertEquals("{\"k\":\"v\"}", res.getString(7)); assertEquals("{\"r\":\"a\",\"s\":9,\"t\":2.2}", res.getString(8)); assertEquals(1, res.getByte(9)); assertEquals(1, res.getShort(10)); assertEquals(1.0f, res.getFloat(11), floatCompareDelta); assertEquals(1, res.getLong(12)); assertEquals("[[\"a\",\"b\"],[\"c\",\"d\"]]", res.getString(13)); assertEquals("{1:{11:12,13:14},2:{21:22}}", res.getString(14)); assertEquals("{\"r\":1,\"s\":{\"a\":2,\"b\":\"x\"}}", res.getString(15)); assertEquals("[{\"m\":{},\"n\":1},{\"m\":{\"a\":\"b\",\"c\":\"d\"},\"n\":2}]", res.getString(16)); assertEquals("2012-04-22 09:00:00.123456789", res.getString(17)); assertEquals("2012-04-22 09:00:00.123456789", res.getTimestamp(17).toString()); assertEquals("123456789.0123456", res.getBigDecimal(18).toString()); assertEquals("abcd", res.getString(19)); assertEquals("2013-01-01", res.getString(20)); assertEquals("2013-01-01", res.getDate(20).toString()); assertEquals("abc123", res.getString(21)); assertEquals("abc123 ", res.getString(22)); byte[] bytes = "X'01FF'".getBytes("UTF-8"); InputStream resultSetInputStream = res.getBinaryStream(23); int len = bytes.length; byte[] b = new byte[len]; resultSetInputStream.read(b, 0, len); for (int i = 0; i < len; i++) { assertEquals(bytes[i], b[i]); } // test getBoolean rules on non-boolean columns assertEquals(true, res.getBoolean(1)); assertEquals(true, res.getBoolean(4)); // test case sensitivity assertFalse(meta.isCaseSensitive(1)); assertFalse(meta.isCaseSensitive(2)); assertFalse(meta.isCaseSensitive(3)); assertTrue(meta.isCaseSensitive(4)); // no more rows assertFalse(res.next()); }
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private Element getMenuData(Element rootElement, int menuId) { Document doc = rootElement.getOwnerDocument(); Element menuElement = XMLTool.createElement(doc, rootElement, "menu"); Connection con = null;//from w w w. j a va2 s . com PreparedStatement preparedStmt = null; ResultSet result = null; try { con = getConnection(); preparedStmt = con.prepareStatement(MENU_SELECT_BY_KEY); preparedStmt.setInt(1, menuId); result = preparedStmt.executeQuery(); if (result.next()) { int languageKey = result.getInt("men_lan_lKey"); menuElement.setAttribute("key", String.valueOf(menuId)); menuElement.setAttribute("languagekey", String.valueOf(languageKey)); menuElement.setAttribute("languagecode", result.getString("lan_sCode")); menuElement.setAttribute("language", result.getString("lan_sDescription")); menuElement.setAttribute("runas", result.getString("men_usr_hRunAs")); String name = result.getString("men_sName"); if (!result.wasNull()) { XMLTool.createElement(doc, menuElement, "name", name); } else { XMLTool.createElement(doc, menuElement, "name"); } // firstpage: int frontpageKey = result.getInt("men_mei_firstPage"); if (!result.wasNull()) { XMLTool.createElement(doc, menuElement, "firstpage").setAttribute("key", String.valueOf(frontpageKey)); } else { XMLTool.createElement(doc, menuElement, "firstpage"); } // loginpage: int loginpageKey = result.getInt("men_mei_loginPage"); if (!result.wasNull()) { XMLTool.createElement(doc, menuElement, "loginpage").setAttribute("key", String.valueOf(loginpageKey)); } else { XMLTool.createElement(doc, menuElement, "loginpage"); } // errorpage: int errorpageKey = result.getInt("men_mei_errorPage"); if (!result.wasNull()) { XMLTool.createElement(doc, menuElement, "errorpage").setAttribute("key", String.valueOf(errorpageKey)); } else { XMLTool.createElement(doc, menuElement, "errorpage"); } // Page template: int defaultPagetemplate = result.getInt("men_pat_lKey"); if (!result.wasNull()) { XMLTool.createElement(doc, menuElement, "defaultpagetemplate").setAttribute("pagetemplatekey", String.valueOf(defaultPagetemplate)); } // XML data: InputStream is = result.getBinaryStream("men_xmlData"); if (!result.wasNull()) { Document tmpDoc = XMLTool.domparse(is); menuElement.appendChild(doc.importNode(tmpDoc.getDocumentElement(), true)); } // Menu details. Element detailsElement = XMLTool.createElement(doc, menuElement, "details"); //Statistics URL: String statisticsURL = result.getString(db.tMenu.men_sStatisticsURL.getName()); if (!result.wasNull()) { XMLTool.createElement(doc, detailsElement, db.tMenu.men_sStatisticsURL.getXPath()) .setTextContent(statisticsURL); } else { XMLTool.createElement(doc, detailsElement, db.tMenu.men_sStatisticsURL.getXPath()); } /* Missing fields. New fields are only implemented in SiteXmlCreator. */ } else { String message = "No menu found for menu ID: %0"; VerticalEngineLogger.error(this.getClass(), 10, message, menuId, null); } } catch (SQLException sqle) { VerticalEngineLogger.error(this.getClass(), 20, "A database error occurred: %t", sqle); } finally { close(result); close(preparedStmt); close(con); } return menuElement; }
From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java
@SuppressWarnings("deprecation") private void assertNonExistingColumn(final ResultSet rs) throws Exception { int nonExistingColIndex = Integer.MAX_VALUE; String nonExistingColName = "col" + nonExistingColIndex; try {/*from ww w . j ava 2 s .co m*/ rs.getString(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getString(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex, 1); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName, 1); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColName); fail(); } catch (SQLException ignore) { } }
From source file:org.rhq.enterprise.server.core.plugin.AgentPluginScanner.java
/** * This method scans the database for any new or updated agent plugins and make sure this server * has a plugin file on the filesystem for each of those new/updated agent plugins. * * @return a list of files that appear to be new or updated and should be deployed *//*w w w . j a v a 2 s .com*/ List<File> agentPluginScanDatabase() throws Exception { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; // these are plugins (name/path/md5/mtime) that have changed in the DB but are missing from the file system List<Plugin> updatedPlugins = new ArrayList<Plugin>(); // the same list as above, only they are the files that are written to the filesystem and no longer missing List<File> updatedFiles = new ArrayList<File>(); try { DataSource ds = LookupUtil.getDataSource(); conn = ds.getConnection(); // get all the plugins ps = conn.prepareStatement("SELECT NAME, PATH, MD5, MTIME, VERSION FROM " + Plugin.TABLE_NAME + " WHERE DEPLOYMENT = 'AGENT' AND ENABLED=?"); setEnabledFlag(conn, ps, 1, true); rs = ps.executeQuery(); while (rs.next()) { String name = rs.getString(1); String path = rs.getString(2); String md5 = rs.getString(3); long mtime = rs.getLong(4); String version = rs.getString(5); // let's see if we have this logical plugin on the filesystem (it may or may not be under the same filename) File expectedFile = new File(this.agentPluginDeployer.getPluginDir(), path); File currentFile = null; // will be non-null if we find that we have this plugin on the filesystem already Plugin cachedPluginOnFilesystem = this.agentPluginsOnFilesystem.get(expectedFile); if (cachedPluginOnFilesystem != null) { currentFile = expectedFile; // we have it where we are expected to have it if (!cachedPluginOnFilesystem.getName().equals(name)) { // I have no idea when or if this would ever happen, but at least log it so we'll see it if it does happen log.warn("For some reason, the plugin file [" + expectedFile + "] is plugin [" + cachedPluginOnFilesystem.getName() + "] but the database says it should be [" + name + "]"); } else { log.debug("File system and database agree on a plugin location for [" + expectedFile + "]"); } } else { // the plugin might still be on the file system but under a different filename, see if we can find it for (Map.Entry<File, Plugin> cachePluginEntry : this.agentPluginsOnFilesystem.entrySet()) { if (cachePluginEntry.getValue().getName().equals(name)) { currentFile = cachePluginEntry.getKey(); cachedPluginOnFilesystem = cachePluginEntry.getValue(); log.info("Filesystem has a plugin [" + name + "] at the file [" + currentFile + "] which is different than where the DB thinks it should be [" + expectedFile + "]"); break; // we found it, no need to continue the loop } } } if (cachedPluginOnFilesystem != null && currentFile != null && currentFile.exists()) { Plugin dbPlugin = new Plugin(name, path); dbPlugin.setMd5(md5); dbPlugin.setVersion(version); dbPlugin.setMtime(mtime); Plugin obsoletePlugin = AgentPluginDescriptorUtil.determineObsoletePlugin(dbPlugin, cachedPluginOnFilesystem); if (obsoletePlugin == cachedPluginOnFilesystem) { // yes use == for reference equality! StringBuilder logMsg = new StringBuilder(); logMsg.append("Found agent plugin [").append(name); logMsg.append("] in the DB that is newer than the one on the filesystem: "); logMsg.append("DB path=[").append(path); logMsg.append("]; file path=[").append(currentFile.getName()); logMsg.append("]; DB MD5=[").append(md5); logMsg.append("]; file MD5=[").append(cachedPluginOnFilesystem.getMd5()); logMsg.append("]; DB version=[").append(version); logMsg.append("]; file version=[").append(cachedPluginOnFilesystem.getVersion()); logMsg.append("]; DB timestamp=[").append(new Date(mtime)); logMsg.append("]; file timestamp=[").append(new Date(cachedPluginOnFilesystem.getMtime())); logMsg.append("]"); log.info(logMsg.toString()); updatedPlugins.add(dbPlugin); if (currentFile.delete()) { log.info("Deleted the obsolete agent plugin file to be updated: " + currentFile); this.agentPluginsOnFilesystem.remove(currentFile); } else { log.warn("Failed to delete the obsolete (to-be-updated) agent plugin file: " + currentFile); } currentFile = null; } else if (obsoletePlugin == null) { // the db is up-to-date, but update the cache so we don't check MD5 or parse the descriptor again currentFile.setLastModified(mtime); cachedPluginOnFilesystem.setMtime(mtime); cachedPluginOnFilesystem.setVersion(version); cachedPluginOnFilesystem.setMd5(md5); } else { String message = "It appears the agent plugin [" + dbPlugin + "] in the database may be obsolete. If so, it will be updated soon by the version on the filesystem [" + currentFile + "]."; if (currentFile.getAbsolutePath().equals(expectedFile.getAbsolutePath())) { if (log.isDebugEnabled()) { log.debug(message); } } else { //inform on the info level so that it's clear from the logs that the new file //is going to be used. log.info(message); } } } else { log.info("Found agent plugin in the DB that we do not yet have: " + name); Plugin plugin = new Plugin(name, path, md5); plugin.setMtime(mtime); plugin.setVersion(version); updatedPlugins.add(plugin); this.agentPluginsOnFilesystem.remove(expectedFile); // paranoia, make sure the cache doesn't have this } } JDBCUtil.safeClose(ps, rs); // write all our updated plugins to the file system if (!updatedPlugins.isEmpty()) { ps = conn.prepareStatement("SELECT CONTENT FROM " + Plugin.TABLE_NAME + " WHERE DEPLOYMENT = 'AGENT' AND NAME = ? AND ENABLED = ?"); for (Plugin plugin : updatedPlugins) { File file = new File(this.agentPluginDeployer.getPluginDir(), plugin.getPath()); ps.setString(1, plugin.getName()); setEnabledFlag(conn, ps, 2, true); rs = ps.executeQuery(); rs.next(); InputStream content = rs.getBinaryStream(1); StreamUtil.copy(content, new FileOutputStream(file)); rs.close(); file.setLastModified(plugin.getMtime()); // so our file matches the database mtime updatedFiles.add(file); } } } finally { JDBCUtil.safeClose(conn, ps, rs); } return updatedFiles; }