{"id":26828,"date":"2021-05-02T23:10:06","date_gmt":"2021-05-03T03:10:06","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/databaseutilities-java-several-general-purpose-utilities-discussed-and-used-in-the-chapter-programming-code-examples-java-j2ee-j2me-jdbc\/"},"modified":"2021-05-02T23:10:06","modified_gmt":"2021-05-03T03:10:06","slug":"databaseutilities-java-several-general-purpose-utilities-discussed-and-used-in-the-chapter-programming-code-examples-java-j2ee-j2me-jdbc","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=26828","title":{"rendered":"DatabaseUtilities.java: Several general-purpose utilities discussed and used in the chapter. #Programming Code Examples #Java\/J2EE\/J2ME #JDBC"},"content":{"rendered":"
\npackage cwp;\n\nimport java.sql.*;\n\n\/** Three database utilities:
\n *   1) getQueryResults. Connects to a database, executes\n *      a query, retrieves all the rows as arrays\n *      of strings, and puts them inside a DBResults\n *      object. Also places the database product name,\n *      database version, and the names of all the columns\n *      into the DBResults object. This has two versions:\n *      one that makes a new connection and another that\n *      uses an existing connection. 
\n *   2) createTable. Given a table name, a string denoting\n *      the column formats, and an array of strings denoting\n *      the row values, this method connects to a database,\n *      removes any existing versions of the designated\n *      table, issues a CREATE TABLE command with the\n *      designated format, then sends a series of INSERT INTO\n *      commands for each of the rows. Again, there are\n *      two versions: one that makes a new connection and\n *      another that uses an existing connection. <\/p>
\n *   3) printTable. Given a table name, this connects to\n *      the specified database, retrieves all the rows,\n *      and prints them on the standard output.\n *\/\n\npublic class DatabaseUtilities {\n  \n  \/** Connect to database, execute specified query,\n   *  and accumulate results into DBRresults object.\n   *  If the database connection is left open (use the\n   *  close argument to specify), you can retrieve the\n   *  connection via DBResults.getConnection.\n   *\/\n  \n  public static DBResults getQueryResults(String driver,\n                                          String url,\n                                          String username,\n                                          String password,\n                                          String query,\n                                          boolean close) {\n    try {\n      Class.forName(driver);\n      Connection connection =\n        DriverManager.getConnection(url, username, password);\n      return(getQueryResults(connection, query, close));\n    } catch(ClassNotFoundException cnfe) {\n      System.err.println("Error loading driver: " + cnfe);\n      return(null);\n    } catch(SQLException sqle) {\n      System.err.println("Error connecting: " + sqle);\n      return(null);\n    }\n  }\n\n  \/** Retrieves results as in previous method, but uses\n   *  an existing connection instead of opening a new one.\n   *\/\n  \n  public static DBResults getQueryResults(Connection connection,\n                                          String query,\n                                          boolean close) {\n    try {\n      DatabaseMetaData dbMetaData = connection.getMetaData();\n      String productName =\n        dbMetaData.getDatabaseProductName();\n      String productVersion =\n        dbMetaData.getDatabaseProductVersion();\n      Statement statement = connection.createStatement();\n      ResultSet resultSet = statement.executeQuery(query);\n      ResultSetMetaData resultsMetaData =\n        resultSet.getMetaData();\n      int columnCount = resultsMetaData.getColumnCount();\n      String[] columnNames = new String[columnCount];\n      \/\/ Column index starts at 1 (ala SQL) not 0 (ala Java).\n      for(int i=1; i<columncount +1; i++) {\n        columnNames[i-1] =\n          resultsMetaData.getColumnName(i).trim();\n      }\n      DBResults dbResults =\n        new DBResults(connection, productName, productVersion,\n                      columnCount, columnNames);      \n      while(resultSet.next()) {\n        String[] row = new String[columnCount];\n        \/\/ Again, ResultSet index starts at 1, not 0.\n        for(int i=1; i<columnCount+1; i++) {\n          String entry = resultSet.getString(i);\n          if (entry != null) {\n            entry = entry.trim();\n          }\n          row[i-1] = entry;\n        }\n        dbResults.addRow(row);\n      }\n      if (close) {\n        connection.close();\n      }\n      return(dbResults);\n    } catch(SQLException sqle) {\n      System.err.println("Error connecting: " + sqle);\n      return(null);\n    } \n  }\n\n  \/** Build a table with the specified format and rows. *\/\n  \n  public static Connection createTable(String driver,\n                                       String url,\n                                       String username,\n                                       String password,\n                                       String tableName,\n                                       String tableFormat,\n                                       String[] tableRows,\n                                       boolean close) {\n    try {\n      Class.forName(driver);\n      Connection connection =\n        DriverManager.getConnection(url, username, password);\n      return(createTable(connection, username, password,\n                         tableName, tableFormat,\n                         tableRows, close));\n    } catch(ClassNotFoundException cnfe) {\n      System.err.println("Error loading driver: " + cnfe);\n      return(null);\n    } catch(SQLException sqle) {\n      System.err.println("Error connecting: " + sqle);\n      return(null);\n    } \n  }\n\n  \/** Like the previous method, but uses existing connection. *\/\n  \n  public static Connection createTable(Connection connection,\n                                       String username,\n                                       String password,\n                                       String tableName,\n                                       String tableFormat,\n                                       String[] tableRows,\n                                       boolean close) {\n    try {\n      \n      Statement statement = connection.createStatement();\n      \/\/ Drop previous table if it exists, but don't get\n      \/\/ error if it doesn't. Thus the separate try\/catch here.\n      try {\n        statement.execute("DROP TABLE " + tableName);\n      } catch(SQLException sqle) {}\n      String createCommand =\n        "CREATE TABLE " + tableName + " " + tableFormat;\n      statement.execute(createCommand);\n      String insertPrefix =\n        "INSERT INTO " + tableName + " VALUES";\n      for(int i=0; i<tableRows.length; i++) {\n        statement.execute(insertPrefix + tableRows[i]);\n      }\n      if (close) {\n        connection.close();\n        return(null);\n      } else {\n        return(connection);\n      }\n    } catch(SQLException sqle) {\n      System.err.println("Error creating table: " + sqle);\n      return(null);\n    } \n  }\n\n  public static void printTable(String driver,\n                                String url,\n                                String username,\n                                String password,\n                                String tableName,\n                                int entryWidth,\n                                boolean close) {\n    String query = "SELECT * FROM " + tableName;\n    DBResults results =\n      getQueryResults(driver, url, username,\n                      password, query, close);\n    printTableData(tableName, results, entryWidth, true);\n  }\n\n  \/** Prints out all entries in a table. Each entry will\n   *  be printed in a column that is entryWidth characters\n   *  wide, so be sure to provide a value at least as big\n   *  as the widest result.\n   *\/\n\n  public static void printTable(Connection connection,\n                                String tableName,\n                                int entryWidth,\n                                boolean close) {\n    String query = "SELECT * FROM " + tableName;\n    DBResults results =\n      getQueryResults(connection, query, close);\n    printTableData(tableName, results,entryWidth, true);\n  }\n\n  public static void printTableData(String tableName,\n                                    DBResults results,\n                                    int entryWidth,\n                                    boolean printMetaData) {\n    if (results == null) {\n      return;\n    }\n    if (printMetaData) {\n      System.out.println("Database: " +\n                         results.getProductName());\n      System.out.println("Version: " +\n                         results.getProductVersion());\n      System.out.println();\n    }\n    System.out.println(tableName + ":");\n    String underline =\n      padString("", tableName.length()+1, "=");        \n    System.out.println(underline);\n    int columnCount = results.getColumnCount();\n    String separator =\n      makeSeparator(entryWidth, columnCount);\n    System.out.println(separator);\n    String row = makeRow(results.getColumnNames(), entryWidth);\n    System.out.println(row);\n    System.out.println(separator);\n    int rowCount = results.getRowCount();\n    for(int i=0; i<rowCount; i++) {\n      row = makeRow(results.getRow(i), entryWidth);\n      System.out.println(row);\n    }\n    System.out.println(separator);\n  }\n  \n  \/\/ A String of the form "|  xxx |  xxx |  xxx |"\n\n  private static String makeRow(String[] entries,\n                                int entryWidth) {\n    String row = "|";\n    for(int i=0; i<entries.length; i++) {\n      row = row + padString(entries[i], entryWidth, " ");\n      row = row + " |";\n    }\n    return(row);\n  }\n    \n  \/\/ A String of the form "+------+------+------+"\n  \n  private static String makeSeparator(int entryWidth,\n                                      int columnCount) {\n    String entry = padString("", entryWidth+1, "-");\n    String separator = "+";\n    for(int i=0; i<columnCount; i++) {\n      separator = separator + entry + "+";\n    }\n    return(separator);\n  }\n\n  private static String padString(String orig, int size,\n                                  String padChar) {\n    if (orig == null) {\n      orig = "";\n    }\n    \/\/ Use StringBuffer, not just repeated String concatenation\n    \/\/ to avoid creating too many temporary Strings.\n    StringBuffer buffer = new StringBuffer("");\n    int extraChars = size - orig.length();\n    for(int i=0; i<extrachars ; i++) {\n      buffer.append(padChar);\n    }\n    buffer.append(orig);\n    return(buffer.toString());\n  }\n}\n<\/p><\/pre>\n
Note: Brought from our old site: http:\/\/www.salearningschool.com\/example_codes\/ on Jan 2nd, 2017 From: http:\/\/sitestree.com\/?p=10203
 Categories:Programming Code Examples, Java\/J2EE\/J2ME, JDBC
Tags:Java\/J2EE\/J2MEJDBC
 Post Data:2017-01-02 16:04:23<\/p>\n
\t\tShop Online: https:\/\/www.ShopForSoul.com\/<\/a>
\n\t\t(Big Data, Cloud, Security, Machine Learning): Courses:  http:\/\/Training.SitesTree.com<\/a>
\n\t\tIn Bengali: http:\/\/Bangla.SaLearningSchool.com<\/a>
\n\t\thttp:\/\/SitesTree.com<\/a>
\n\t\t8112223 Canada Inc.\/JustEtc: http:\/\/JustEtc.net (Software\/Web\/Mobile\/Big-Data\/Machine Learning) <\/a>
\n\t\tShop Online:  https:\/\/www.ShopForSoul.com\/<\/a>
\n\t\tMedium:  https:\/\/medium.com\/@SayedAhmedCanada <\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
package cwp; import java.sql.*; \/** Three database utilities: * 1) getQueryResults. Connects to a database, executes * a query, retrieves all the rows as arrays * of strings, and puts them inside a DBResults * object. Also places the database product name, * database version, and the names of all the columns * into the … <\/p>\n
Continue reading<\/a><\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1917],"tags":[],"class_list":["post-26828","post","type-post","status-publish","format-standard","hentry","category-fromsitestree-com","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":10113,"url":"http:\/\/bangla.sitestree.com\/?p=10113","url_meta":{"origin":26828,"position":0},"title":"DatabaseUtilities.java: Several general-purpose utilities discussed and used in the chapter.","author":"","date":"August 4, 2015","format":false,"excerpt":"package cwp; import java.sql.*; \/** Three database utilities: \u00a0*\u00a0\u00a0 1) getQueryResults. Connects to a database, executes \u00a0*\u00a0\u00a0\u00a0\u00a0\u00a0 a query, retrieves all the rows as arrays \u00a0*\u00a0\u00a0\u00a0\u00a0\u00a0 of strings, and puts them inside a DBResults \u00a0*\u00a0\u00a0\u00a0\u00a0\u00a0 object. Also places the database product name, \u00a0*\u00a0\u00a0\u00a0\u00a0\u00a0 database version, and the names of all\u2026","rel":"","context":"In "Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8"","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10116,"url":"http:\/\/bangla.sitestree.com\/?p=10116","url_meta":{"origin":26828,"position":1},"title":"DBResults.java: Class to store completed results of a JDBC Query. Differs from a ResultSet in several ways","author":"","date":"August 5, 2015","format":false,"excerpt":"# DBResults.java\u00a0 Class to store completed results of a JDBC Query. Differs from a ResultSet in several ways: \u00a0\u00a0\u00a0 * ResultSet doesn?t necessarily have all the data; reconnection to database occurs as you ask for later rows. \u00a0\u00a0\u00a0 * This class stores results as strings, in arrays. \u00a0\u00a0\u00a0 * This\u2026","rel":"","context":"In "Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8"","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10128,"url":"http:\/\/bangla.sitestree.com\/?p=10128","url_meta":{"origin":26828,"position":2},"title":"extract relevant data from a DBResults","author":"","date":"August 6, 2015","format":false,"excerpt":"# QueryViewer.java\u00a0 An interactive database query viewer. Connects to the specified Oracle or Sybase database, executes a query, and presents the results in a JTable. Uses the following file: \u00a0\u00a0\u00a0 * DBResultsTableModel.java Simple class that tells a JTable how to extract relevant data from a DBResults object (which is used\u2026","rel":"","context":"In "Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8"","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":26830,"url":"http:\/\/bangla.sitestree.com\/?p=26830","url_meta":{"origin":26828,"position":3},"title":"DBResults.java: Class to store completed results of a JDBC Query. Differs from a ResultSet in several ways #Programming Code Examples #Java\/J2EE\/J2ME #JDBC","author":"Author-Check- Article-or-Video","date":"May 2, 2021","format":false,"excerpt":"# DBResults.java Class to store completed results of a JDBC Query. Differs from a ResultSet in several ways: * ResultSet doesn?t necessarily have all the data; reconnection to database occurs as you ask for later rows. * This class stores results as strings, in arrays. * This class includes DatabaseMetaData\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10120,"url":"http:\/\/bangla.sitestree.com\/?p=10120","url_meta":{"origin":26828,"position":4},"title":"EmployeeTest2.java: A test case for the database utilities. Prints results formatted as an HTML table.","author":"","date":"August 5, 2015","format":false,"excerpt":"package cwp; import java.sql.*; \/** Connect to Oracle or Sybase and print \"employees\" table \u00a0*\u00a0 as an HTML table. \u00a0* \u00a0 \u00a0*\/ public class EmployeeTest2 { \u00a0 public static void main(String[] args) { \u00a0\u00a0\u00a0 if (args.length < 5) { \u00a0\u00a0\u00a0\u00a0\u00a0 printUsage(); \u00a0\u00a0\u00a0\u00a0\u00a0 return; \u00a0\u00a0\u00a0 } \u00a0\u00a0\u00a0 String vendorName = args[4];\u2026","rel":"","context":"In "Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8"","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":26852,"url":"http:\/\/bangla.sitestree.com\/?p=26852","url_meta":{"origin":26828,"position":5},"title":"extract relevant data from a DBResults #Programming Code Examples #Java\/J2EE\/J2ME #JDBC","author":"Author-Check- Article-or-Video","date":"May 3, 2021","format":false,"excerpt":"package cwp; import javax.swing.table.*; \/** Simple class that tells a JTable how to extract * relevant data from a DBResults object (which is * used to store the results from a database query). *\/ public class DBResultsTableModel extends AbstractTableModel { private DBResults results; public DBResultsTableModel(DBResults results) { this.results = results;\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/26828","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=26828"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/26828\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=26828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=26828"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=26828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}