//*************************************************************************** // (c) Copyright IBM Corp. 2007 All rights reserved. // // The following sample of source code ("Sample") is owned by International // Business Machines Corporation or one of its subsidiaries ("IBM") and is // copyrighted and licensed, not sold. You may use, copy, modify, and // distribute the Sample in any form without payment to IBM, for the purpose of // assisting you in the development of your applications. // // The Sample code is provided to you on an "AS IS" basis, without warranty of // any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR // IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF // MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do // not allow for the exclusion or limitation of implied warranties, so the above // limitations or exclusions may not apply to you. IBM shall not be liable for // any damages you suffer as a result of using, copying, modifying or // distributing the Sample, even if IBM has been advised of the possibility of // such damages. //*************************************************************************** // // SOURCE FILE NAME: TbRead.java // // SAMPLE: How to read table data // // SQL Statements USED: // SELECT // // JAVA 2 CLASSES USED: // Statement // PreparedStatement // ResultSet // // Classes used from Util.java are: // Db // Data // JdbcException // // // Output will vary depending on the JDBC driver connectivity used. //*************************************************************************** // // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //**************************************************************************/ import java.lang.*; import java.sql.*; class TbRead { public static void main(String argv[]) { try { Db db = new Db(argv); System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO READ TABLE DATA."); // connect to the 'sample' database db.connect(); // different ways to read table data execQuery(db.con); execPreparedQuery(db.con); execPreparedQueryWithParam(db.con); execPreparedQueryWithUnknownOutputColumn(db.con); mostSimpleSubselect(db.con); basicSubselect(db.con); groupBySubselect(db.con); subselect(db.con); rowSubselect(db.con); fullselect(db.con); selectStatement(db.con); basicSubselectFromMultipleTables(db.con); basicSubselectFromJoinedTable(db.con); basicSubselectUsingSubquery(db.con); basicSubselectUsingCorrelatedSubquery(db.con); subselectUsingGroupingSets(db.con); subselectUsingRollup(db.con); subselectUsingCube(db.con); selectUsingQuerySampling(db.con); // disconnect from the 'sample' database db.disconnect(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // main // helping function static void OrgTbContentDisplay(Connection con) { try { int deptnumb = 0; String deptname = ""; int manager = 0; String division = ""; String location = ""; System.out.println(); System.out.println(" SELECT * FROM org"); System.out.println( " DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION\n" + " -------- -------------- ------- ---------- --------------"); Statement stmt = con.createStatement(); // perform a SELECT against the "org" table in the sample database. ResultSet rs = stmt.executeQuery("SELECT * FROM org"); // retrieve and display the result from the SELECT statement while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); manager = rs.getInt(3); division = rs.getString(4); location = rs.getString(5); System.out.println( " " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14) + " " + Data.format(manager, 7) + " " + Data.format(division, 10) + " " + Data.format(location, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // OrgTableContentDisplay // helping function static void DepartmentTbContentDisplay(Connection con) { try { String deptno = ""; String departmentDeptname = ""; String mgrno = ""; String admrdept = ""; String departmentLocation = ""; System.out.println(); System.out.println( " SELECT * FROM department"); System.out.println( " DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION"); System.out.println( " ------ ---------------------------- ------ -------- --------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM department"); while (rs.next()) { deptno = rs.getString(1); departmentDeptname = rs.getString(2); mgrno = rs.getString(3); admrdept = rs.getString(4); departmentLocation = rs.getString(5); System.out.print(" " + Data.format(deptno, 6) + " " + Data.format(departmentDeptname, 28)); if (mgrno != null) { System.out.print(" " + Data.format(mgrno, 6)); } else { System.out.print(" - "); } System.out.print(" " + Data.format(admrdept,8)); if (departmentLocation != null) { System.out.print(" " + Data.format(departmentLocation, 16)); } else { System.out.print(" - "); } System.out.println(); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // DepartmentTbContentDisplay // helping function static void EmployeeTbPartialContentDisplay(Connection con) { try { String job = ""; int edlevel = 0; double comm = 0.0; System.out.println(); System.out.println(" Perform:\n" + " SELECT job, edlevel, comm\n" + " FROM employee\n" + " WHERE job IN('DESIGNER', 'FIELDREP')\n" + "\n" + " Results:\n" + " JOB EDLEVEL COMM\n" + " -------- ------- -----------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT job, edlevel, comm " + " FROM employee " + " WHERE job IN('DESIGNER', 'FIELDREP')"); while (rs.next()) { job = rs.getString(1); edlevel = rs.getInt(2); comm = rs.getDouble(3); System.out.println(" " + Data.format(job, 8) + " " + Data.format(edlevel, 7) + " " + Data.format(comm, 10, 2)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // EmployeeTbPartialContentDisplay static void execQuery(Connection con) { try { System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE JAVA 2 CLASS:\n" + " Statement\n" + "TO EXECUTE A QUERY."); Statement stmt = con.createStatement(); // execute the query System.out.println(); System.out.println( " Execute Statement:\n" + " SELECT deptnumb, location FROM org WHERE deptnumb < 25"); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, location FROM org WHERE deptnumb < 25 "); System.out.println(); System.out.println(" Results:\n" + " DEPTNUMB LOCATION\n" + " -------- --------------"); int deptnumb = 0; String location = ""; while (rs.next()) { deptnumb = rs.getInt(1); location = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(location, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } static void execPreparedQuery(Connection con) { try { System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE JAVA 2 CLASS:\n" + " PreparedStatement\n" + "TO EXECUTE A PREPARED QUERY."); Statement stmt = con.createStatement(); // prepare the query System.out.println(); System.out.println( " Prepare Statement:\n" + " SELECT deptnumb, location FROM org WHERE deptnumb < 25"); PreparedStatement pstmt = con.prepareStatement( "SELECT deptnumb, location FROM org WHERE deptnumb < 25 "); System.out.println(); System.out.println(" Execute prepared statement"); ResultSet rs = pstmt.executeQuery(); System.out.println(); System.out.println(" Results:\n" + " DEPTNUMB LOCATION\n" + " -------- --------------"); int deptnumb = 0; String location = ""; while (rs.next()) { deptnumb = rs.getInt(1); location = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(location, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } static void execPreparedQueryWithParam(Connection con) { try { System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE JAVA 2 CLASS:\n" + " PreparedStatement\n" + "TO EXECUTE A PREPARED QUERY WITH PARAMETERS."); Statement stmt = con.createStatement(); // prepare the query System.out.println(); System.out.println( " Prepare Statement:\n" + " SELECT deptnumb, location FROM org WHERE deptnumb < ?"); PreparedStatement pstmt = con.prepareStatement( "SELECT deptnumb, location FROM org WHERE deptnumb < ?"); System.out.println(); System.out.println(" Set parameter value: parameter 1 = 25"); pstmt.setInt(1, 25); System.out.println(); System.out.println(" Execute prepared statement"); ResultSet rs = pstmt.executeQuery(); System.out.println(); System.out.println(" Results:\n" + " DEPTNUMB LOCATION\n" + " -------- --------------"); int deptnumb = 0; String location = ""; while (rs.next()) { deptnumb = rs.getInt(1); location = rs.getString(2); System.out.println( " " + Data.format(deptnumb, 8) + " " + Data.format(location, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } static void execPreparedQueryWithUnknownOutputColumn(Connection con) { try { System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE JAVA 2 CLASS:\n" + " PreparedStatement\n" + "TO EXECUTE A PREPARED QUERY WITH UNKNOWN OUTPUT COLUMNS."); Statement stmt = con.createStatement(); // prepare the query System.out.println(); System.out.println(" Prepare Statement:\n" + " SELECT * FROM org WHERE deptnumb < 25"); PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM org WHERE deptnumb < 25"); System.out.println(); System.out.println(" Execute prepared statement"); ResultSet rs = pstmt.executeQuery(); ResultSetMetaData rsms = rs.getMetaData(); int colCount = rsms.getColumnCount(); int[] colSize = new int[colCount]; String[] colLabel = new String[colCount]; String[] colTypeName = new String[colCount]; for (int i = 0 ; i < colCount ; i++) { colSize[i] = rsms.getColumnDisplaySize(i+1); colLabel[i] = rsms.getColumnLabel(i+1); colTypeName[i] = rsms.getColumnTypeName(i+1); } System.out.println(); System.out.print(" Results:\n" + " "); // print the columns' name for (int i = 0 ; i < colCount ; i++) { System.out.print(colLabel[i] + " "); int spaceCounter = colLabel[i].length(); while (spaceCounter < colSize[i]) { System.out.print(" "); spaceCounter++; } } System.out.println(); // print the line under each column's name int[] actualColSize = new int[colCount]; System.out.print(" "); for (int i = 0 ; i < colCount ; i++) { int dashCounter = 0; while (dashCounter < colSize[i] || dashCounter < colLabel[i].length()) { System.out.print("-"); dashCounter++; } actualColSize[i] = dashCounter; System.out.print(" "); } System.out.println(); // print the result set while (rs.next()) { System.out.print(" "); for (int i = 0 ; i < colCount ; i++) { // check the TYPE of the column to retrieve the value of // each column if (colTypeName[i].equals("SMALLINT")) { System.out.print( Data.format(rs.getInt(i+1), actualColSize[i])); } else if (colTypeName[i].equals("VARCHAR")) { System.out.print(Data.format(rs.getString(i+1), actualColSize[i])); } else if (colTypeName[i].equals("INTEGER")) { Integer tempInteger = new Integer(0); tempInteger = Integer.valueOf(rs.getString(i+1)); System.out.print( Data.format(tempInteger, actualColSize[i])); } else if (colTypeName[i].equals("DOUBLE")) { Double tempDouble = new Double(0.0); tempDouble = Double.valueOf( Double.toString(rs.getDouble(i+1))); System.out.print( Data.format(tempDouble, actualColSize[i], 2)); } else { System.out.println("Error: " + "Cannot read the column's type"); } System.out.print(" "); } System.out.println(); } // end while rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } static void mostSimpleSubselect(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SIMPLE SUBSELECT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println(" Perform:\n" + " SELECT deptnumb, deptname FROM org\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname FROM org"); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // mostSimpleSubselect static void basicSubselect(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING A WHERE CLAUSE."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT deptnumb, deptname FROM org WHERE deptnumb < 30\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname FROM org WHERE deptnumb < 30"); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // basicSubselect static void groupBySubselect(Connection con) { try { String division = ""; int maxDeptnumb = 0; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A 'GROUP BY' SUBSELECT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT division, MAX(deptnumb) FROM org GROUP BY division\n" + "\n" + " Results:\n" + " DIVISION MAX(DEPTNUMB)\n" + " ---------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT division, MAX(deptnumb) FROM org GROUP BY division"); while (rs.next()) { division = rs.getString(1); maxDeptnumb = rs.getInt(2); System.out.println(" " + Data.format(division, 10) + " " + Data.format(maxDeptnumb, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // groupBySubselect static void subselect(Connection con) { try { int maxDeptnumb = 0; String division = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println(" Perform:\n" + " SELECT division, MAX(deptnumb)\n" + " FROM org\n" + " WHERE location NOT IN 'New York'\n" + " GROUP BY division\n" + " HAVING division LIKE '%%ern'\n" + "\n" + " Results:\n" + " DIVISION MAX(DEPTNUMB)\n" + " ---------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT division, MAX(deptnumb) " + " FROM org " + " WHERE location NOT IN 'New York' " + " GROUP BY division " + " HAVING division LIKE '%ern'"); while (rs.next()) { division = rs.getString(1); maxDeptnumb = rs.getInt(2); System.out.println(" " + Data.format(division, 10) + " " + Data.format(maxDeptnumb, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // subselect static void rowSubselect(Connection con) { try { System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A 'ROW' SUBSELECT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println(" Perform:\n" + " SELECT deptnumb, deptname\n" + " FROM org\n" + " WHERE location = 'New York'\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname " + " FROM org " + " WHERE location = 'New York' "); rs.next(); int deptnumb = rs.getInt(1); String deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // rowSubselect static void fullselect(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A FULLSELECT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println(" Perform:\n" + " SELECT deptnumb, deptname\n" + " FROM org\n" + " WHERE deptnumb < 20\n" + " UNION\n" + " VALUES(7, 'New Deptname')\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT deptnumb, deptname " + " FROM org " + " WHERE deptnumb < 20 " + " UNION " + " VALUES(7, 'New Deptname')"); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // fullselect static void selectStatement(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SELECT STATEMENT."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println(" Perform:\n" + " SELECT deptnumb, deptname\n" + " FROM org\n" + " WHERE deptnumb > 30\n" + " ORDER BY deptname\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- ------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname " + " FROM org " + " WHERE deptnumb > 30 " + " ORDER BY deptname"); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 18)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // selectStatement static void basicSubselectFromMultipleTables(Connection con) { try { int deptnumb = 0; String deptno = ""; String orgDeptname = ""; String departmentDeptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT FROM MULTIPLE TABLES."); // display the content of the 'org' table OrgTbContentDisplay(con); // display DEPARTMENT table content DepartmentTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT deptnumb, o.deptname, deptno, d.deptname\n" + " FROM org o, department d\n" + " WHERE deptnumb <= 15 AND deptno LIKE '%%11'\n" + "\n" + " Results:\n" + " DEPTNUMB ORG.DEPTNAME DEPTNO DEPARTMENT.DEPTNAME\n" + " -------- -------------- ------ -------------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, o.deptname, deptno, d.deptname " + " FROM org o, department d " + " WHERE deptnumb <= 15 AND deptno LIKE '%11'"); while (rs.next()) { deptnumb = rs.getInt(1); orgDeptname = rs.getString(2); deptno = rs.getString(3); departmentDeptname = rs.getString(4); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(orgDeptname, 14) + " " + Data.format(deptno, 6) + " " + Data.format(departmentDeptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // TbBasicSelectFromMultipleTables static void basicSubselectFromJoinedTable(Connection con) { try { int deptnumb = 0; int manager = 0; String deptno = ""; String mgrno = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT FROM JOINED TABLES."); // display the content of the 'org' table OrgTbContentDisplay(con); // display DEPARTMENT table content DepartmentTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT deptnumb, manager, deptno, mgrno\n" + " FROM org\n" + " INNER JOIN department\n" + " ON manager = INTEGER(mgrno)\n" + " WHERE deptnumb BETWEEN 20 AND 100\n" + "\n" + " Results:\n" + " DEPTNUMB MANAGER DEPTNO MGRNO\n" + " -------- ------- ------ ------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, manager, deptno, mgrno " + " FROM org " + " INNER JOIN department " + " ON manager = INTEGER(mgrno) " + " WHERE deptnumb BETWEEN 20 AND 100 "); while (rs.next()) { deptnumb = rs.getInt(1); manager = rs.getInt(2); deptno = rs.getString(3); mgrno = rs.getString(4); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(manager, 7) + " " + Data.format(deptno, 5) + " " + Data.format(mgrno, 6)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // basicSubselectFromJoinedTable static void basicSubselectUsingSubquery(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING SUBQUERY."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT deptnumb, deptname\n" + " FROM org\n" + " WHERE deptnumb < (SELECT AVG(deptnumb) FROM org)\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname " + " FROM org " + " WHERE deptnumb < (SELECT AVG(deptnumb) FROM org)"); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // basicSubselectUsingSubquery static void basicSubselectUsingCorrelatedSubquery(Connection con) { try { int deptnumb = 0; String deptname = ""; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING CORRELATED SUBQUERY."); // display the content of the 'org' table OrgTbContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT deptnumb, deptname\n" + " FROM org o1\n" + " WHERE deptnumb > (SELECT AVG(deptnumb)\n" + " FROM org o2\n" + " WHERE o2.division = o1.division)\n" + "\n" + " Results:\n" + " DEPTNUMB DEPTNAME\n" + " -------- --------------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT deptnumb, deptname " + " FROM org o1 " + " WHERE deptnumb > (SELECT AVG(deptnumb) " + " FROM org o2 " + " WHERE o2.division = o1.division) "); while (rs.next()) { deptnumb = rs.getInt(1); deptname = rs.getString(2); System.out.println(" " + Data.format(deptnumb, 8) + " " + Data.format(deptname, 14)); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // basicSubselectUsingCorrelatedSubquery static void subselectUsingGroupingSets(Connection con) { try { String job = null; Integer edlevel = new Integer(0); Double commSum = new Double(0.0); System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING GROUPING SETS."); EmployeeTbPartialContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT job, edlevel, SUM(comm)\n" + " FROM employee\n" + " WHERE job IN('DESIGNER', 'FIELDREP')\n" + " GROUP BY GROUPING SETS((job, edlevel), (job))\n" + "\n" + " Results:\n" + " JOB EDLEVEL SUM(COMM)\n" + " -------- ------- -----------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT job, edlevel, SUM(comm) " + " FROM employee " + " WHERE job IN('DESIGNER','FIELDREP') " + " GROUP BY GROUPING SETS ((job, edlevel),(job))"); while (rs.next()) { if (rs.getString(1) != null) { job = rs.getString(1); System.out.print(" " + Data.format(job, 8)); } else { System.out.print(" -"); } if (rs.getString(2) != null) { edlevel = Integer.valueOf(rs.getString(2)); System.out.print(" " + Data.format(edlevel, 7)); } else { System.out.print(" -"); } if (rs.getDouble(3) != 0.0) { commSum = Double.valueOf( Double.toString(rs.getDouble(3))); System.out.print(" " + Data.format(commSum, 10, 2)); } else { System.out.print(" -"); } System.out.println(); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // subselectUsingGroupingSets static void subselectUsingRollup(Connection con) { try { String job = null; Integer edlevel = new Integer(0); Double commSum = new Double(0.0); System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING ROLLUP."); EmployeeTbPartialContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT job, edlevel, SUM(comm)\n" + " FROM employee\n" + " WHERE job IN('DESIGNER', 'FIELDREP')\n" + " GROUP BY ROLLUP(job, edlevel)\n" + "\n" + " Results:\n" + " JOB EDLEVEL SUM(COMM)\n" + " -------- ------- -----------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT job, edlevel, SUM(comm) " + " FROM employee " + " WHERE job IN('DESIGNER', 'FIELDREP') " + " GROUP BY ROLLUP(job, edlevel)"); while (rs.next()) { if (rs.getString(1) != null) { job = rs.getString(1); System.out.print(" " + Data.format(job, 8)); } else { System.out.print(" -"); } if (rs.getString(2) != null) { edlevel = Integer.valueOf(rs.getString(2)); System.out.print(" " + Data.format(edlevel, 7)); } else { System.out.print(" -"); } if (rs.getDouble(3) != 0.0) { commSum = Double.valueOf( Double.toString(rs.getDouble(3))); System.out.print(" " + Data.format(commSum, 10, 2)); } else { System.out.print(" -"); } System.out.println(); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // subselectUsingRollup static void subselectUsingCube(Connection con) { try { String job = null; Integer edlevel = new Integer(0); Double commSum = new Double(0.0); System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SUBSELECT USING CUBE."); EmployeeTbPartialContentDisplay(con); System.out.println(); System.out.println( " Perform:\n" + " SELECT job, edlevel, SUM(comm)\n" + " FROM employee\n" + " WHERE job IN('DESIGNER', 'FIELDREP')\n" + " GROUP BY CUBE(job, edlevel)\n" + "\n" + " Results:\n" + " JOB EDLEVEL SUM(COMM)\n" + " -------- ------- -----------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT job, edlevel, SUM(comm) " + " FROM employee " + " WHERE job IN('DESIGNER', 'FIELDREP') " + " GROUP BY CUBE(job, edlevel)"); while (rs.next()) { if (rs.getString(1) != null) { job = rs.getString(1); System.out.print(" " + Data.format(job, 8)); } else { System.out.print(" -"); } if (rs.getString(2) != null) { edlevel = Integer.valueOf(rs.getString(2)); System.out.print(" " + Data.format(edlevel, 7)); } else { System.out.print(" -"); } if (rs.getDouble(3) != 0.0) { commSum = Double.valueOf( Double.toString(rs.getDouble(3))); System.out.print(" " + Data.format(commSum, 10, 2)); } else { System.out.print(" -"); } System.out.println(); } rs.close(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // subselectUsingCube static void selectUsingQuerySampling(Connection con) { try { float avg = 0.0f; System.out.println(); System.out.println( "----------------------------------------------------------\n" + "USE THE SQL STATEMENTS:\n" + " SELECT\n" + "TO PERFORM A SELECT USING QUERY SAMPLING "); System.out.println( "\nCOMPUTING AVG(SALARY) WITHOUT SAMPLING \n" + "\n Perform:\n" + " SELECT AVG(salary) FROM employee \n" + "\n Results:\n" + " AVG SALARY\n" + " ----------"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT AVG(salary) FROM employee"); while (rs.next()) { avg = rs.getFloat(1); if (rs.wasNull()) { System.out.println(" -"); } else { System.out.println(" " + avg); } } rs.close(); System.out.println( "\nCOMPUTING AVG(SALARY) WITH QUERY SAMPLING" + "\n - ROW LEVEL SAMPLING " + "\n - BLOCK LEVEL SAMPLING \n" + "\n ROW LEVEL SAMPLING : USE THE KEYWORD 'BERNOULLI'\n" + "\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n" + "SELECTED FOR THE INCLUSION IN THE RESULT WITH A PROBABILITY\n" + "OF P/100, INDEPENDENTLY OF THE OTHER ROWS IN T\n" + "\n Perform:\n" + " SELECT AVG(salary) FROM employee TABLESAMPLE BERNOULLI(25)" + " REPEATABLE(5)\n" + "\n Results:\n" + " AVG SALARY\n" + " ----------"); rs = stmt.executeQuery( "SELECT AVG(salary) FROM employee " + "TABLESAMPLE BERNOULLI(25) REPEATABLE(5)"); while (rs.next()) { avg = rs.getFloat(1); if (rs.wasNull()) { System.out.println(" -"); } else { System.out.println(" " + avg); } } rs.close(); System.out.println( "\n\n BLOCK LEVEL SAMPLING : USE THE KEYWORD 'SYSTEM'\n" + "\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n" + "SELECTED FOR INCLUSION IN THE RESULT WITH A PROBABILITY\n" + "OF P/100, NOT NECESSARILY INDEPENDENTLY OF THE OTHER ROWS\n" + "IN T, BASED UPON AN IMPLEMENTATION-DEPENDENT ALGORITHM\n" + "\n Perform:\n" + " SELECT AVG(salary) FROM employee TABLESAMPLE SYSTEM(50)" + " REPEATABLE(1234)\n" + "\n Results:\n" + " AVG SALARY\n" + " ----------" ); rs = stmt.executeQuery( "SELECT AVG(salary)FROM employee "+ "TABLESAMPLE SYSTEM(50) REPEATABLE(1234)"); while (rs.next()) { avg = rs.getFloat(1); if (rs.wasNull()) { System.out.println(" -"); } else { System.out.println(" " + avg); } } rs.close(); System.out.println( "\nREPEATABLE CLAUSE ENSURES THAT REPEATED EXECUTIONS OF THAT\n" + "TABLE REFERENCE WILL RETURN IDENTICAL RESULTS FOR THE SAME \n" + "VALUE OF THE REPEAT ARGUMENT (IN PARENTHESIS)."); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // selectUsingQuerySampling } // TbRead