- Here is a Java program that:
- Reads in an SQL query from the terminal
- Then send the
SQL query to
the mySQL server for
execution
- The Java program then
prints:
- The attribute names (obtained from the Meta data object)
- The tuples in the result set
- A simple mySQL client
JDBC program:
import java.sql.*; import java.util.Scanner; public class myClient { public static void main (String args[]) { Scanner in = new Scanner(System.in); String url = "jdbc:mysql://holland.mathcs.emory.edu:3306/"; String dbName = "companyDB"; String userName = "cs377"; String password = "abc123"; int[] length = new int[100]; // Max 100 fields.... /****************************************************************/ try { // Load the MySQL JDBC driver DriverManager.registerDriver( new com.mysql.jdbc.Driver( ) ); } catch (Exception e) { System.out.println("Failed to load JDBC driver."); return; } Connection conn = null; Statement stmt = null; try { // Connect to the database conn = DriverManager.getConnection(url+dbName,userName,password); // Create statement object to send query to SQL server stmt = conn.createStatement (); } catch (Exception e) { System.err.println("problems connecting to " + url+dbName); } /* ============================================================== Start the infinite loop to: 1. Read an SQL query 2. Execute the Query 3. Print the attribute names (meta data) 4. Print the rows =============================================================== */ Boolean done = false; while ( ! done ) { String s; System.out.print("\nmyCli>> "); s = in.nextLine(); // Read SQL command /* ============================================ Check if user wants to exit.... ============================================ */ if ( s.equalsIgnoreCase("exit") ) { done = true; continue; } try { ResultSet rset = stmt.executeQuery( s ); // Exec query ResultSetMetaData meta = rset.getMetaData(); // Get meta data int NCols = meta.getColumnCount(); /* =================================================== **** Print the column names before any data **** =================================================== */ for ( int i = 1; i <= NCols; i++ ) { String name; name = meta.getColumnName(i); System.out.print( name + "\t" ); // Print field name } System.out.println(); /* --------------------------------- Print a dividing line.... --------------------------------- */ System.out.println( "===================================================="); /* =========================================== Fetch and print one row at a time.... =========================================== */ while ( rset.next () ) // Advance to next row { /* =========================================== Fetch the columns (attributes) from a row =========================================== */ for(int i = 1; i <= NCols; i++) { String nextItem; nextItem = rset.getString(i); System.out.print(nextItem + "\t"); } System.out.println(); } rset.close(); // Free result set buffers } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message } } /* ========================================= Exit command was issued ---- Exit ========================================= */ try { stmt.close(); // Free Statement object resources conn.close(); // Close SQL server connection } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message } } }
- The same
Java program as
above, but
with code to
keep track of
the width of
each field:
(So we can print each field with the given width)
import java.sql.*; import java.util.Scanner; public class myClient { public static void main (String args[]) { Scanner in = new Scanner(System.in); String url = "jdbc:mysql://holland.mathcs.emory.edu:3306/"; String dbName = "companyDB"; String userName = "cs377"; String password = "abc123"; int[] length = new int[100]; // Display size of each field.... /****************************************************************/ try { // Load the MySQL JDBC driver DriverManager.registerDriver( new com.mysql.jdbc.Driver( ) ); } catch (Exception e) { System.out.println("Failed to load JDBC driver."); return; } Connection conn = null; Statement stmt = null; try { // Connect to the database conn = DriverManager.getConnection(url+dbName,userName,password); // Create statement object to send query to SQL server stmt = conn.createStatement (); } catch (Exception e) { System.err.println("problems connecting to " + url+dbName); } /* ============================================================== Start the infinite loop to: 1. Read an SQL query 2. Execute the Query 3. Print the attribute names (meta data) 4. Print the rows =============================================================== */ Boolean done = false; while ( ! done ) { String s; System.out.print("\nmyCli>> "); s = in.nextLine(); // Read SQL command /* ============================================ Check if user wants to exit.... ============================================ */ if ( s.equalsIgnoreCase("exit") ) { done = true; continue; } try { ResultSet rset = stmt.executeQuery( s ); // Exec query ResultSetMetaData meta = rset.getMetaData(); // Get meta data int NCols = meta.getColumnCount(); /* =================================================== **** Print the column names before any data **** =================================================== */ for ( int i = 1; i <= NCols; i++ ) { String name; name = meta.getColumnName(i); System.out.print( name ); // Print field name length[i] = Math.max( name.length(), meta.getColumnDisplaySize(i) ) + 2; // 2 spaces between columns // Set the length of the field to print // It's 2 more than the name/display size /* ---------------------------------------------- ****Pad**** the attr name i to length[i] ---------------------------------------------- */ for (int j = name.length(); j < length[i]; j++) System.out.print(" "); } System.out.println(); /* --------------------------------- Print a dividing line.... --------------------------------- */ for ( int i = 1; i <= NCols; i++ ) for ( int j = 0; j < length[i]; j++) System.out.print("="); // Print a dividing line System.out.println(); /* =========================================== Fetch and print one row at a time.... =========================================== */ while ( rset.next () ) // Advance to next row { /* =========================================== Fetch the columns (attributes) from a row =========================================== */ for(int i = 1; i <= NCols; i++) { String nextItem; nextItem = rset.getString(i); System.out.print(nextItem); /* ---------------------------------------------- **Pad** the attr value i to length[i] ---------------------------------------------- */ for (int j = nextItem.length(); j < length[i]; j++) System.out.print(" "); } System.out.println(); } rset.close(); // Free result set buffers } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message } } /* ========================================= Exit command was issued ---- Exit ========================================= */ try { stmt.close(); // Free Statement object resources conn.close(); // Close SQL server connection } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message } } }
- Example Program:
(Demo above code)
- Prog file: /home/cs377001/demo/JDBC/myClient.java
How to run the program:
- Make a copy to your CS377 directory
- To compile: javac -cp ".:/home/cs377001/lib/mysql-connector-java.jar" myClient.java
- To run: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" myClient