[ Previous part | Simple programming examples ]
Use the following as an example for your program. The example includes detailed explanations about key lines in the code. You can view the detailed explanations in the following ways:
Note: Read the Code example disclaimer for important legal information.
////////////////////////////////////////////////////////////////////////////////// // // JDBCQuery example. This program uses the IBM Toolbox for Java JDBC driver to // query a table and output its contents. // // Command syntax: // JDBCQuery system collectionName tableName // // For example, // JDBCQuery MySystem qiws qcustcdt // // This source is an example of IBM Toolbox for Java JDBC driver. // ////////////////////////////////////////////////////////////////////////////////// import java.sql.*; public class JDBCQuery { // Format a string so that it has the specified width. private static String format (String s, int width) { String formattedString; // The string is shorter than specified width, // so we need to pad with blanks. if (s.length() < width) { StringBuffer buffer = new StringBuffer (s); for (int i = s.length(); i < width; ++i) buffer.append (" "); formattedString = buffer.toString(); } // Otherwise, we need to truncate the string. else formattedString = s.substring (0, width); return formattedString; } public static void main (String[] parameters) { // Check the input parameters. if (parameters.length != 3) { System.out.println(""); System.out.println("Usage:"); System.out.println(""); System.out.println(" JDBCQuery system collectionName tableName"); System.out.println(""); System.out.println(""); System.out.println("For example:"); System.out.println(""); System.out.println(""); System.out.println(" JDBCQuery mySystem qiws qcustcdt"); System.out.println(""); return; } String system = parameters[0]; String collectionName = parameters[1]; String tableName = parameters[2]; Connection connection = null; try { DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); Note 1// Get a connection to the database. Since we do not // provide a user id or password, a prompt will appear. connection = DriverManager.getConnection ("jdbc:as400://" + system); DatabaseMetaData dmd = connection.getMetaData (); Note 2
// Execute the query. Statement select = connection.createStatement (); ResultSet rs = select.executeQuery ("SELECT * FROM " + collectionName + dmd.getCatalogSeparator() + tableName); Note 3
// Get information about the result set. Set the column // width to whichever is longer: the length of the label // or the length of the data. ResultSetMetaData rsmd = rs.getMetaData (); int columnCount = rsmd.getColumnCount (); Note 4
String[] columnLabels = new String[columnCount]; int[] columnWidths = new int[columnCount]; for (int i = 1; i <= columnCount; ++i) { columnLabels[i-1] = rsmd.getColumnLabel (i); columnWidths[i-1] = Math.max (columnLabels[i-1].length(), rsmd.getColumnDisplaySize (i)); Note 5
} // Output the column headings. for (int i = 1; i <= columnCount; ++i) { System.out.print (format (rsmd.getColumnLabel(i), columnWidths[i-1])); System.out.print (" "); } System.out.println (); // Output a dashed line. StringBuffer dashedLine; for (int i = 1; i <= columnCount; ++i) { for (int j = 1; j <= columnWidths[i-1]; ++j) System.out.print ("-"); System.out.print (" "); } System.out.println (); // Iterate throught the rows in the result set and output // the columns for each row. while (rs.next ()) { for (int i = 1; i <= columnCount; ++i) { String value = rs.getString (i); if (rs.wasNull ()) value = "<null>"; Note 6
System.out.print (format (value, columnWidths[i-1])); System.out.print (" "); } System.out.println (); } } catch (Exception e) { System.out.println (); System.out.println ("ERROR: " + e.getMessage()); } finally { // Clean up. try { if (connection != null) connection.close (); } catch (SQLException e) { // Ignore. } } System.exit (0); } }
This line loads the IBM Toolbox for Java JDBC driver. A JDBC driver mediates between JDBC and the database with which you are working.
This line retrieves the connection's meta data, an object that describes many of the characteristics of the database.
These lines set the column width to either the length of the label or the length of the data, whichever is longer.
This block of code iterates through all of the rows in the table and displays the contents of each column in each row.