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 DBResults object. This has two versions: * one that makes a new connection and another that * uses an existing connection.* 2) createTable. Given a table name, a string denoting * the column formats, and an array of strings denoting * the row values, this method connects to a database, * removes any existing versions of the designated * table, issues a CREATE TABLE command with the * designated format, then sends a series of INSERT INTO * commands for each of the rows. Again, there are * two versions: one that makes a new connection and * another that uses an existing connection.
* 3) printTable. Given a table name, this connects to * the specified database, retrieves all the rows, * and prints them on the standard output. */ public class DatabaseUtilities { /** Connect to database, execute specified query, * and accumulate results into DBRresults object. * If the database connection is left open (use the * close argument to specify), you can retrieve the * connection via DBResults.getConnection. */ public static DBResults getQueryResults(String driver, String url, String username, String password, String query, boolean close) { try { Class.forName(driver); Connection connection = DriverManager.getConnection(url, username, password); return(getQueryResults(connection, query, close)); } catch(ClassNotFoundException cnfe) { System.err.println("Error loading driver: " + cnfe); return(null); } catch(SQLException sqle) { System.err.println("Error connecting: " + sqle); return(null); } } /** Retrieves results as in previous method, but uses * an existing connection instead of opening a new one. */ public static DBResults getQueryResults(Connection connection, String query, boolean close) { try { DatabaseMetaData dbMetaData = connection.getMetaData(); String productName = dbMetaData.getDatabaseProductName(); String productVersion = dbMetaData.getDatabaseProductVersion(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query); ResultSetMetaData resultsMetaData = resultSet.getMetaData(); int columnCount = resultsMetaData.getColumnCount(); String[] columnNames = new String[columnCount]; // Column index starts at 1 (ala SQL) not 0 (ala Java). for(int i=1; i<columncount +1; i++) { columnNames[i-1] = resultsMetaData.getColumnName(i).trim(); } DBResults dbResults = new DBResults(connection, productName, productVersion, columnCount, columnNames); while(resultSet.next()) { String[] row = new String[columnCount]; // Again, ResultSet index starts at 1, not 0. for(int i=1; i<columnCount+1; i++) { String entry = resultSet.getString(i); if (entry != null) { entry = entry.trim(); } row[i-1] = entry; } dbResults.addRow(row); } if (close) { connection.close(); } return(dbResults); } catch(SQLException sqle) { System.err.println("Error connecting: " + sqle); return(null); } } /** Build a table with the specified format and rows. */ public static Connection createTable(String driver, String url, String username, String password, String tableName, String tableFormat, String[] tableRows, boolean close) { try { Class.forName(driver); Connection connection = DriverManager.getConnection(url, username, password); return(createTable(connection, username, password, tableName, tableFormat, tableRows, close)); } catch(ClassNotFoundException cnfe) { System.err.println("Error loading driver: " + cnfe); return(null); } catch(SQLException sqle) { System.err.println("Error connecting: " + sqle); return(null); } } /** Like the previous method, but uses existing connection. */ public static Connection createTable(Connection connection, String username, String password, String tableName, String tableFormat, String[] tableRows, boolean close) { try { Statement statement = connection.createStatement(); // Drop previous table if it exists, but don't get // error if it doesn't. Thus the separate try/catch here. try { statement.execute("DROP TABLE " + tableName); } catch(SQLException sqle) {} String createCommand = "CREATE TABLE " + tableName + " " + tableFormat; statement.execute(createCommand); String insertPrefix = "INSERT INTO " + tableName + " VALUES"; for(int i=0; i<tableRows.length; i++) { statement.execute(insertPrefix + tableRows[i]); } if (close) { connection.close(); return(null); } else { return(connection); } } catch(SQLException sqle) { System.err.println("Error creating table: " + sqle); return(null); } } public static void printTable(String driver, String url, String username, String password, String tableName, int entryWidth, boolean close) { String query = "SELECT * FROM " + tableName; DBResults results = getQueryResults(driver, url, username, password, query, close); printTableData(tableName, results, entryWidth, true); } /** Prints out all entries in a table. Each entry will * be printed in a column that is entryWidth characters * wide, so be sure to provide a value at least as big * as the widest result. */ public static void printTable(Connection connection, String tableName, int entryWidth, boolean close) { String query = "SELECT * FROM " + tableName; DBResults results = getQueryResults(connection, query, close); printTableData(tableName, results,entryWidth, true); } public static void printTableData(String tableName, DBResults results, int entryWidth, boolean printMetaData) { if (results == null) { return; } if (printMetaData) { System.out.println("Database: " + results.getProductName()); System.out.println("Version: " + results.getProductVersion()); System.out.println(); } System.out.println(tableName + ":"); String underline = padString("", tableName.length()+1, "="); System.out.println(underline); int columnCount = results.getColumnCount(); String separator = makeSeparator(entryWidth, columnCount); System.out.println(separator); String row = makeRow(results.getColumnNames(), entryWidth); System.out.println(row); System.out.println(separator); int rowCount = results.getRowCount(); for(int i=0; i<rowCount; i++) { row = makeRow(results.getRow(i), entryWidth); System.out.println(row); } System.out.println(separator); } // A String of the form "| xxx | xxx | xxx |" private static String makeRow(String[] entries, int entryWidth) { String row = "|"; for(int i=0; i<entries.length; i++) { row = row + padString(entries[i], entryWidth, " "); row = row + " |"; } return(row); } // A String of the form "+------+------+------+" private static String makeSeparator(int entryWidth, int columnCount) { String entry = padString("", entryWidth+1, "-"); String separator = "+"; for(int i=0; i<columnCount; i++) { separator = separator + entry + "+"; } return(separator); } private static String padString(String orig, int size, String padChar) { if (orig == null) { orig = ""; } // Use StringBuffer, not just repeated String concatenation // to avoid creating too many temporary Strings. StringBuffer buffer = new StringBuffer(""); int extraChars = size - orig.length(); for(int i=0; i<extrachars ; i++) { buffer.append(padChar); } buffer.append(orig); return(buffer.toString()); } }
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
Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada
