List of usage examples for java.sql ResultSet getStatement
Statement getStatement() throws SQLException;
Statement object that produced this ResultSet object. From source file:org.saiku.web.rest.resources.BasicTagRepositoryResource.java
@GET
@Produces({ "text/csv" })
@Path("/{cubeIdentifier}/{tagName}/export/csv")
public Response getDrillthroughExport(@PathParam("cubeIdentifier") String cubeIdentifier,
@PathParam("tagName") String tagName, @QueryParam("maxrows") @DefaultValue("0") Integer maxrows,
@QueryParam("returns") String returns, @QueryParam("connection") String connection,
@QueryParam("catalog") String catalog, @QueryParam("schema") String schema,
@QueryParam("cube") String cube, @QueryParam("additional") String additional) {
ResultSet rs = null;
try {//from w w w . ja v a 2 s. co m
List<Integer> cellPosition = new ArrayList<Integer>();
cellPosition.add(0);
List<KeyValue<String, String>> additionalColumns = new ArrayList<KeyValue<String, String>>();
if (additional != null) {
for (String kvs : additional.split(",")) {
String[] kv = kvs.split(":");
if (kv.length == 2) {
additionalColumns.add(new KeyValue<String, String>(kv[0], kv[1]));
}
}
}
SaikuTag tag = getTag(cubeIdentifier, tagName);
if (tag != null) {
String queryName = UUID.randomUUID().toString();
SaikuCube saikuCube = new SaikuCube(connection, cube, cube, cube, catalog, schema);
olapQueryService.createNewOlapQuery(queryName, saikuCube);
SaikuQuery q = olapQueryService.simulateTag(queryName, tag);
if (!cube.startsWith("[")) {
cube = "[" + cube + "]";
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
boolean first = true;
for (SaikuTuple tuple : tag.getSaikuTuples()) {
String mdx = null;
for (SaikuMember member : tuple.getSaikuMembers()) {
if (mdx == null) {
mdx = "SELECT (" + member.getUniqueName();
} else {
mdx += ", " + member.getUniqueName();
}
}
boolean where = true;
if (tag.getSaikuDimensionSelections() != null) {
for (SaikuDimensionSelection sdim : tag.getSaikuDimensionSelections()) {
if (sdim.getSelections().size() > 1) {
where = false;
}
}
}
if (where) {
mdx += ") ON COLUMNS from " + cube;
SelectNode sn = (new DefaultMdxParserImpl().parseSelect(q.getMdx()));
final Writer writer = new StringWriter();
sn.getFilterAxis().unparse(new ParseTreeWriter(new PrintWriter(writer)));
if (StringUtils.isNotBlank(writer.toString())) {
mdx += "\r\nWHERE " + writer.toString();
}
System.out.println("Executing... :" + mdx);
olapQueryService.executeMdx(queryName, mdx);
rs = olapQueryService.drillthrough(queryName, cellPosition, maxrows, returns);
byte[] doc = olapQueryService.exportResultSetCsv(rs, ",", "\"", first, additionalColumns);
first = false;
outputStream.write(doc);
} else {
if (tag.getSaikuDimensionSelections() != null) {
for (SaikuDimensionSelection sdim : tag.getSaikuDimensionSelections()) {
for (SaikuSelection ss : sdim.getSelections()) {
if (ss.getType() == Type.MEMBER) {
String newmdx = mdx;
newmdx += "," + ss.getUniqueName() + ") ON COLUMNS from " + cube;
System.out.println("Executing... :" + newmdx);
olapQueryService.executeMdx(queryName, newmdx);
rs = olapQueryService.drillthrough(queryName, cellPosition, maxrows,
returns);
byte[] doc = olapQueryService.exportResultSetCsv(rs, ",", "\"", first,
additionalColumns);
first = false;
outputStream.write(doc);
}
}
}
}
}
}
byte csv[] = outputStream.toByteArray();
String name = SaikuProperties.webExportCsvName;
return Response.ok(csv, MediaType.APPLICATION_OCTET_STREAM)
.header("content-disposition", "attachment; filename = " + name + "-drillthrough.csv")
.header("content-length", csv.length).build();
}
} catch (Exception e) {
log.error("Cannot export drillthrough tag (" + tagName + ")", e);
return Response.serverError().build();
}
finally {
if (rs != null) {
try {
Statement statement = rs.getStatement();
statement.close();
rs.close();
} catch (SQLException e) {
throw new SaikuServiceException(e);
} finally {
rs = null;
}
}
}
return Response.serverError().build();
}
From source file:nl.nn.adapterframework.util.JdbcUtil.java
public static void fullClose(Connection connection, ResultSet rs) { Statement statement = null;//from ww w. ja v a 2 s. c o m if (rs == null) { log.warn("resultset to close was null"); return; } try { statement = rs.getStatement(); } catch (SQLException e) { log.warn("Could not obtain statement or connection from resultset", e); } finally { try { rs.close(); } catch (SQLException e) { log.warn("Could not close resultset", e); } finally { if (statement != null) { fullClose(connection, statement); } } } }
From source file:nl.strohalm.cyclos.utils.JDBCWrapper.java
/** * Closes the given result set AND it's statement, ignoring any exceptions *//*from w w w.j a v a 2s . c o m*/ public static void closeQuietly(final ResultSet rs) { Statement st = null; try { st = rs.getStatement(); } catch (final Exception e) { // Ignore } try { rs.close(); } catch (final Exception e) { // Ignore } closeQuietly(st); }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
@Test public void preparedStmtAndResultSet() throws SQLException { final String tableName = "bigtop_jdbc_psars_test_table"; try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists " + tableName); stmt.execute("create table " + tableName + " (bo boolean, ti tinyint, db double, fl float, " + "i int, lo bigint, sh smallint, st varchar(32))"); }//from w ww .ja v a2 s.co m // NOTE Hive 1.2 theoretically support binary, Date & Timestamp in JDBC, but I get errors when I // try to put them in the query. try (PreparedStatement ps = conn .prepareStatement("insert into " + tableName + " values (?, ?, ?, ?, ?, ?, ?, ?)")) { ps.setBoolean(1, true); ps.setByte(2, (byte) 1); ps.setDouble(3, 3.141592654); ps.setFloat(4, 3.14f); ps.setInt(5, 3); ps.setLong(6, 10L); ps.setShort(7, (short) 20); ps.setString(8, "abc"); ps.executeUpdate(); } try (PreparedStatement ps = conn.prepareStatement("insert into " + tableName + " (i, st) " + "values(?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) { ps.setNull(1, Types.INTEGER); ps.setObject(2, "mary had a little lamb"); ps.executeUpdate(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.clearParameters(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.execute(); } try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int colCnt = md.getColumnCount(); LOG.debug("Column count is " + colCnt); for (int i = 1; i <= colCnt; i++) { LOG.debug("Looking at column " + i); String strrc = md.getColumnClassName(i); LOG.debug("Column class name is " + strrc); int intrc = md.getColumnDisplaySize(i); LOG.debug("Column display size is " + intrc); strrc = md.getColumnLabel(i); LOG.debug("Column label is " + strrc); strrc = md.getColumnName(i); LOG.debug("Column name is " + strrc); intrc = md.getColumnType(i); LOG.debug("Column type is " + intrc); strrc = md.getColumnTypeName(i); LOG.debug("Column type name is " + strrc); intrc = md.getPrecision(i); LOG.debug("Precision is " + intrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); boolean boolrc = md.isAutoIncrement(i); LOG.debug("Is auto increment? " + boolrc); boolrc = md.isCaseSensitive(i); LOG.debug("Is case sensitive? " + boolrc); boolrc = md.isCurrency(i); LOG.debug("Is currency? " + boolrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); intrc = md.isNullable(i); LOG.debug("Is nullable? " + intrc); boolrc = md.isReadOnly(i); LOG.debug("Is read only? " + boolrc); } while (rs.next()) { LOG.debug("bo = " + rs.getBoolean(1)); LOG.debug("bo = " + rs.getBoolean("bo")); LOG.debug("ti = " + rs.getByte(2)); LOG.debug("ti = " + rs.getByte("ti")); LOG.debug("db = " + rs.getDouble(3)); LOG.debug("db = " + rs.getDouble("db")); LOG.debug("fl = " + rs.getFloat(4)); LOG.debug("fl = " + rs.getFloat("fl")); LOG.debug("i = " + rs.getInt(5)); LOG.debug("i = " + rs.getInt("i")); LOG.debug("lo = " + rs.getLong(6)); LOG.debug("lo = " + rs.getLong("lo")); LOG.debug("sh = " + rs.getShort(7)); LOG.debug("sh = " + rs.getShort("sh")); LOG.debug("st = " + rs.getString(8)); LOG.debug("st = " + rs.getString("st")); LOG.debug("tm = " + rs.getObject(8)); LOG.debug("tm = " + rs.getObject("st")); LOG.debug("tm was null " + rs.wasNull()); } LOG.debug("bo is column " + rs.findColumn("bo")); int intrc = rs.getConcurrency(); LOG.debug("concurrency " + intrc); intrc = rs.getFetchDirection(); LOG.debug("fetch direction " + intrc); intrc = rs.getType(); LOG.debug("type " + intrc); Statement copy = rs.getStatement(); SQLWarning warning = rs.getWarnings(); while (warning != null) { LOG.debug("Found a warning: " + warning.getMessage()); warning = warning.getNextWarning(); } rs.clearWarnings(); } }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testParentReferences() throws Exception { /* Test parent references from Statement */ Statement s = this.con.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName); assertTrue(s.getConnection() == this.con); assertTrue(rs.getStatement() == s); rs.close();// w w w. j av a2 s. c o m s.close(); /* Test parent references from PreparedStatement */ PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName); rs = ps.executeQuery(); assertTrue(ps.getConnection() == this.con); assertTrue(rs.getStatement() == ps); rs.close(); ps.close(); /* Test DatabaseMetaData queries which do not have a parent Statement */ DatabaseMetaData md = this.con.getMetaData(); assertTrue(md.getConnection() == this.con); rs = md.getCatalogs(); assertNull(rs.getStatement()); rs.close(); rs = md.getColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getFunctions(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getImportedKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getPrimaryKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedureColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedures(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getSchemas(); assertNull(rs.getStatement()); rs.close(); rs = md.getTableTypes(); assertNull(rs.getStatement()); rs.close(); rs = md.getTables(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getTypeInfo(); assertNull(rs.getStatement()); rs.close(); }
From source file:org.apache.sqoop.common.test.db.DatabaseProvider.java
/** * Close given result set (if not null) and associated statement. * * @param rs ResultSet to close./*from w ww . ja v a 2 s . c o m*/ */ public void closeResultSetWithStatement(ResultSet rs) { if (rs != null) { try { Statement stmt = rs.getStatement(); rs.close(); stmt.close(); } catch (SQLException e) { LOG.info("Ignoring exception: ", e); } } }
From source file:org.batoo.jpa.core.impl.criteria.path.BasicPath.java
/** * {@inheritDoc}/*www . jav a2 s . c om*/ * */ @Override @SuppressWarnings("unchecked") public X handle(QueryImpl<?> query, SessionImpl session, ResultSet row) throws SQLException { final X value = (X) this.mapping.getColumn().convertValue(row.getStatement().getConnection(), row.getObject(this.fieldAlias)); return (X) (this.getConverter() != null ? this.getConverter().convert(value) : value); }
From source file:org.ensembl.healthcheck.testcase.eg_compara.AbstractControlledRows.java
/** * //from ww w. j a v a 2s . c om * Will check, if the current for of the ResultSet is present in the master database. * * The columns are passed in each time so this doesn't have to be generated for each * call. * * @param masterTableName * @param sqlTemplateComparaMaster * @param columns * @param rsFromTestDb * @throws SQLException */ protected boolean isCurrentRowInMaster(final ResultSet rsFromTestDb, final SqlTemplate sqlTemplateComparaMaster, final String masterTableName, final List<String> columns) throws SQLException { int numColumns = rsFromTestDb.getMetaData().getColumnCount(); List<Object> columnValuesObjects = new ArrayList<Object>(numColumns); for (int currentColIndex = 0; currentColIndex < numColumns; currentColIndex++) { Object value = rsFromTestDb.getObject(currentColIndex + 1); columnValuesObjects.add(currentColIndex, value); } String countMatchingRowsSql = "select count(*) from " + masterTableName + " where " + asParameterisedWhereClause(columns, columnValuesObjects); final EnsTestCase thisTest = this; boolean isInMasterDb = sqlTemplateComparaMaster.execute(countMatchingRowsSql, new ResultSetCallback<Boolean>() { @Override public Boolean process(ResultSet rsFromMaster) throws SQLException { int numColumns = rsFromMaster.getMetaData().getColumnCount(); if (numColumns != 1) { throw new RuntimeException("Expected one column, but got " + numColumns + " instead!" + resultSetRowAsString(rsFromMaster)); } rsFromMaster.next(); int numberOfMatchingRowsInMaster = rsFromMaster.getInt(1); if (numberOfMatchingRowsInMaster == 1) { return true; } if (numberOfMatchingRowsInMaster == 0) { return false; } ReportManager.problem(thisTest, rsFromMaster.getStatement().getConnection(), "Found " + numberOfMatchingRowsInMaster + " " + "matching rows in the master database!\n" + "The row searched for was:\n" + resultSetRowAsString(rsFromTestDb)); // We return true, because there is a row in the master // database. The tested database has passed for this row, // it is the master database that has the problem. // return true; } }, columnValuesObjects.toArray()); return isInMasterDb; }
From source file:org.ensembl.healthcheck.util.DBUtils.java
public static boolean compareResultSets(ResultSet rs1, ResultSet rs2, EnsTestCase testCase, String text, boolean reportErrors, boolean warnNull, String singleTableName, int[] columns, boolean comparingSchema) { // quick tests first // Check for object equality if (rs1.equals(rs2)) { return true; }/*from w w w . j a v a 2 s . c o m*/ try { // get some information about the ResultSets String name1 = getShortDatabaseName(rs1.getStatement().getConnection()); String name2 = getShortDatabaseName(rs2.getStatement().getConnection()); // Check for same column count, names and types ResultSetMetaData rsmd1 = rs1.getMetaData(); ResultSetMetaData rsmd2 = rs2.getMetaData(); if (rsmd1.getColumnCount() != rsmd2.getColumnCount() && columns == null) { if (reportErrors) { ReportManager.problem(testCase, name1, "Column counts differ " + singleTableName + " " + name1 + ": " + rsmd1.getColumnCount() + " " + name2 + ": " + rsmd2.getColumnCount()); } return false; // Deliberate early return for performance // reasons } if (columns == null) { columns = new int[rsmd1.getColumnCount()]; for (int i = 0; i < columns.length; i++) { columns[i] = i + 1; } } for (int j = 0; j < columns.length; j++) { int i = columns[j]; // note columns indexed from l if (!((rsmd1.getColumnName(i)).equals(rsmd2.getColumnName(i)))) { if (reportErrors) { ReportManager.problem(testCase, name1, "Column names differ for " + singleTableName + " column " + i + " - " + name1 + ": " + rsmd1.getColumnName(i) + " " + name2 + ": " + rsmd2.getColumnName(i)); } // Deliberate early return for performance reasons return false; } if (rsmd1.getColumnType(i) != rsmd2.getColumnType(i)) { if (reportErrors) { ReportManager.problem(testCase, name1, "Column types differ for " + singleTableName + " column " + i + " - " + name1 + ": " + rsmd1.getColumnType(i) + " " + name2 + ": " + rsmd2.getColumnType(i)); } return false; // Deliberate early return for performance // reasons } } // for column // make sure both cursors are at the start of the ResultSet // (default is before the start) rs1.beforeFirst(); rs2.beforeFirst(); // if quick checks didn't cause return, try comparing row-wise int row = 1; while (rs1.next()) { if (rs2.next()) { String str = name1 + " and " + name2 + text + " " + singleTableName + " with columns "; for (int j = 0; j < columns.length; j++) { int i = columns[j]; str += rsmd1.getColumnName(i) + " " + Utils.truncate(rs1.getString(i), 250, true) + ", "; // note columns indexed from 1 if (!compareColumns(rs1, rs2, i, warnNull)) { str += " differ for values " + Utils.truncate(rs1.getString(i), 250, true) + ", " + Utils.truncate(rs2.getString(i), 250, true); if (reportErrors) { ReportManager.problem(testCase, name1, str); } return false; } } row++; } else { // rs1 has more rows than rs2 if (reportErrors) { ReportManager.problem(testCase, name1, singleTableName + " has more rows in " + name1 + " than in " + name2); } return false; } } // while rs1 // if both ResultSets are the same, then we should be at the end of // both, i.e. .next() should return false String extra = comparingSchema ? ". This means that there are missing columns in the table, rectify!" : ""; if (rs1.next()) { if (reportErrors) { ReportManager.problem(testCase, name1, name1 + " " + singleTableName + " has additional rows that are not in " + name2 + extra); } return false; } else if (rs2.next()) { if (reportErrors) { ReportManager.problem(testCase, name1, name2 + " " + singleTableName + " has additional rows that are not in " + name1 + extra); } return false; } } catch (SQLException se) { throw new SqlUncheckedException("Could not compare two result sets", se); } return true; }
From source file:org.ensembl.healthcheck.util.DBUtils.java
/** * Compare a particular column in two ResultSets. * //w w w .j a v a2 s . c o m * @param rs1 * The first ResultSet to compare. * @param rs2 * The second ResultSet to compare. * @param i * The index of the column to compare. * @return True if the type and value of the columns match. */ public static boolean compareColumns(ResultSet rs1, ResultSet rs2, int i, boolean warnNull) { try { ResultSetMetaData rsmd = rs1.getMetaData(); Connection con1 = rs1.getStatement().getConnection(); Connection con2 = rs2.getStatement().getConnection(); if (rs1.getObject(i) == null) { if (warnNull) { logger.fine("Column " + rsmd.getColumnName(i) + " is null in table " + rsmd.getTableName(i) + " in " + DBUtils.getShortDatabaseName(con1)); } return (rs2.getObject(i) == null); // true if both are null } if (rs2.getObject(i) == null) { if (warnNull) { logger.fine("Column " + rsmd.getColumnName(i) + " is null in table " + rsmd.getTableName(i) + " in " + DBUtils.getShortDatabaseName(con2)); } return (rs1.getObject(i) == null); // true if both are null } // Note deliberate early returns for performance reasons switch (rsmd.getColumnType(i)) { case Types.INTEGER: return rs1.getInt(i) == rs2.getInt(i); case Types.SMALLINT: return rs1.getInt(i) == rs2.getInt(i); case Types.TINYINT: return rs1.getInt(i) == rs2.getInt(i); case Types.VARCHAR: String s1 = rs1.getString(i); String s2 = rs2.getString(i); // ignore "AUTO_INCREMENT=" part in final part of table // definition s1 = s1.replaceAll("AUTO_INCREMENT=[0-9]+ ", ""); s2 = s2.replaceAll("AUTO_INCREMENT=[0-9]+ ", ""); return s1.equals(s2); case Types.FLOAT: return rs1.getFloat(i) == rs2.getFloat(i); case Types.DOUBLE: return rs1.getDouble(i) == rs2.getDouble(i); case Types.TIMESTAMP: return rs1.getTimestamp(i).equals(rs2.getTimestamp(i)); default: // treat everything else as a String (should deal with ENUM and // TEXT) if (rs1.getString(i) == null || rs2.getString(i) == null) { return true; // ???? } else { return rs1.getString(i).equals(rs2.getString(i)); } } // switch } catch (SQLException se) { throw new SqlUncheckedException("Could not compare two columns sets", se); } }