{"id":555,"date":"2021-02-22T19:46:53","date_gmt":"2021-02-22T19:46:53","guid":{"rendered":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/?page_id=555"},"modified":"2021-02-23T01:15:39","modified_gmt":"2021-02-23T01:15:39","slug":"using-jdbc","status":"publish","type":"page","link":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/","title":{"rendered":"Using JDBC"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a6d4957dee27\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a6d4957dee27\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Connecting_to_Databases\" >Connecting to Databases<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#JDBC2_Connections_and_JNDI\" >JDBC2 Connections and JNDI<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQL_Errors_and_Warnings\" >SQL Errors and Warnings<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQLCODE\" >SQLCODE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQLSTATE\" >SQLSTATE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_SQL_Diagnostics_Area\" >The SQL Diagnostics Area<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQL_Warnings\" >SQL Warnings<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQLExceptions\" >SQLExceptions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQLWarnings\" >SQLWarnings<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Data_Truncation\" >Data Truncation<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Accessing_Databases\" >Accessing Databases<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_Statement_Interface\" >The&nbsp;Statement&nbsp;Interface<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_executeUpdate_Method\" >The&nbsp;executeUpdate()&nbsp;Method<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Performing_Queries\" >Performing Queries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#SQL_Data_TypeJava_Data_Type_Mapping\" >SQL Data Type\/Java Data Type Mapping<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Data_type_mappings_from_SQL_to_Java\" >Data type mappings from SQL to Java:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Data_type_mappings_from_Java_to_SQL\" >Data type mappings from Java to SQL:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_javasqlTypes_class\" >The&nbsp;java.sql.Types&nbsp;class<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_getXXX_Methods\" >The getXXX() Methods<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Use_of_ResultSetgetXXX_Methods_to_Retrieve_JDBC_Types\" >Use of ResultSet.getXXX Methods to Retrieve JDBC Types<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#getXXXint_columnPosition_versus_getXXXString_columnName\" >getXXX(int columnPosition)&nbsp;versus&nbsp;getXXX(String columnName)<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Creating_Populating_a_Database\" >Creating &amp; Populating a Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Transactions\" >Transactions<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Transaction_Isolation_Levels\" >Transaction Isolation Levels<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Setting_Your_Isolation_Level\" >Setting Your Isolation Level<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Prepared_Callable_Statements\" >Prepared &amp; Callable Statements<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Prepared_Statements\" >Prepared Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_PreparedStatement_Interface\" >The&nbsp;PreparedStatement&nbsp;Interface<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Callable_Statements\" >Callable Statements<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Using_Metadata\" >Using Metadata<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Driver_Metadata\" >Driver Metadata<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-32\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_DatabaseMetadata_Interface\" >The&nbsp;DatabaseMetadata&nbsp;Interface<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-33\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#The_ResultSetMetaData_Interface\" >The&nbsp;ResultSetMetaData&nbsp;Interface<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-34\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/using-jdbc\/#Putting_it_All_Together\" >Putting it All Together<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Connecting_to_Databases\"><\/span>Connecting to Databases<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Connecting to a database with JDBC involves the following:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>Obtain a JDBC Driver and Database<\/strong><br>There are many vendors who supply JDBC drivers for a number of different databases systems. There are plenty of options, including IBM DB2, Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.<br>Note that every JDK comes with an implementation of Apache Derby, that is likely to be a useful way of trying things out. It&#8217;s called Java DB (which isn&#8217;t very helpful).<\/li><li><strong>In your Java program, load the chosen JDBC Driver<\/strong>.<br>There are a number of ways of doing this:<ul><li>Explicitly call&nbsp;<strong>new<\/strong>&nbsp;to load your driver&#8217;s implementation of&nbsp;<strong>Driver<\/strong>.<br>For example:<br><strong>Driver driver = new <em>DriverImplementationClass<\/em>();<\/strong><\/li><li>Use the&nbsp;<strong>jdbc.drivers<\/strong>&nbsp;property.<br>The&nbsp;<strong>DriverManager<\/strong>&nbsp;will automatically load all classes listed in the <strong>jdbc.drivers<\/strong>&nbsp;System property.<br>For example:<br><strong>System.getProperties().put(&#8220;jdbc.drivers&#8221;, &#8220;sun.jdbc.odbc.JdbcOdbcDriver:&#8221; + &#8220;ORG.as220.tinySQL.textFileDriver:&#8221; + &#8220;org.gjt.mm.mysql.Driver&#8221;);<\/strong><br>or the property can be set on the command line:<br>java \u2026 &#8211;<strong>Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver:org.gjt.mm.mysql.Driver MyProgram<\/strong><br>(Note that the items in the property list are separated by colons (&#8216;<strong>:<\/strong>&#8216;).)<\/li><li>Load the class using:<br><strong>Class.forName(&lt;driverImplementationClass&gt;);<\/strong><br>When you load a driver, by whatever mechanism, it registers itself with the&nbsp;<strong>DriverManager<\/strong>.<\/li><\/ul><\/li><li><strong>Get a connection using DriverManager and a JDBC URL.<\/strong><br>For example, here&#8217;s how you typically do it:<br><strong>Connection conn = DriverManager.getConnection(&#8220;jdbc:odbc:Employees&#8221;, username, password);<\/strong><br>There are three forms of the getConnection() method:<br><strong>public static Connection getConnection(String url); <\/strong><br><strong>public static Connection getConnection(String url, String username, String password); <\/strong><br><strong>public static Connection getConnection(String url, Properties props);<\/strong>(All three can throw a&nbsp;<strong>SQLException<\/strong>&nbsp;on failure to get a connection.) This is because some databases require a username and password to make a connection to the database, while others do not. Still others require a more complex set of parameters.<br>A JDBC URL looks like this:<br><strong>jdbc:&lt;subprotocol&gt;:&lt;other-driver-specific-parameters&gt;<\/strong><ul><li>The URL protocol is&nbsp;<strong>jdbc<\/strong><\/li><li>You specify a&nbsp;<em><strong>subprotocol<\/strong><\/em>, which a particular JDBC driver supports<\/li><li>After the subprotocol, you specify a number of driver-specific parameters.<\/li><\/ul><\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Once you&#8217;ve connected, you can access the database using SQL statements.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a simple example, which uses the JDBC\/ODBC Bridge driver:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.SQLException;\n\npublic class ConnectToDatabase\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n        }\n        catch(ClassNotFoundException ex)\n        {\n            \/\/ The Class.forName() failed to find the class\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            \/\/ The getConnection() failed\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><strong><em><u>Note:<\/u><\/em><\/strong>&nbsp; You should be cleaning up (i.e. closing) your connections explicitly, as in the above example.<\/p><\/blockquote>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"JDBC2_Connections_and_JNDI\"><\/span>JDBC2 Connections and JNDI<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In JDBC2, you can use the&nbsp;<em>Java Naming and Directory Interface<\/em>&nbsp;(JNDI) to connect to a database, using code such as:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Context jndiContext = ...;\nDataSource dataSource = (DataSource)jndiContext.lookup(\"jdbc\/myDatabase\");\nConnection conn = dataSource.getConnection(username, password);<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which allows you to specify the data source name externally, bypasses the&nbsp;<strong>DriverManager<\/strong>, and simplifies the code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We will find that a number of Enterprise Java components use JNDI in similar ways. This is a definite trend.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQL_Errors_and_Warnings\"><\/span>SQL Errors and Warnings<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When you use SQL in a program, you need to be able to tell when something goes wrong &#8212; that is when an error occurs.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQLCODE\"><\/span>SQLCODE<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL standard defined a special&nbsp;<em>status parameter<\/em>,&nbsp;<strong>SQLCODE<\/strong>, which you declare in your program as a variable (in C, of type&nbsp;<strong>long<\/strong>). When you execute SQL statements, you specify (explicitly or implicitly, depending on how you are invoking the SQL statements) that <strong>SQLCODE<\/strong> is to be used to contain the return status code. In the SQL standard, the only defined values for SQLCODE were:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>0<\/strong>&nbsp;&#8212;&nbsp;<em><strong>successful completion<\/strong><\/em><\/li><li><strong>+100<\/strong>&nbsp;&#8212;&nbsp;<em><strong>no data<\/strong><\/em>&nbsp;(meaning that the statement did not incur errors, but found no [more] rows on which to operate)<\/li><li><strong>&lt; 0&nbsp;<\/strong>&#8212;&nbsp;<em><strong>error occurred<\/strong><\/em><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The specific negative values were left up to each SQL vendor&#8217;s implementation (when the SQL-89 standard was written, the vendors already had their own sets of incompatible values for <strong>SQLCODE<\/strong>, so the standard could not make their implementations obsolete, nor could it favor one vendor over others).<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQLSTATE\"><\/span>SQLSTATE<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">SQL-92 tried to fix this situation, but could not do away with SQLCODE, nor could it define new SQLCODE values, for fear of making existing applications obsolete. So it&nbsp;<em>deprecated<\/em>&nbsp;SQLCODE, and introduced a second&nbsp;<em>status parameter<\/em>,&nbsp;<strong>SQLSTATE<\/strong>, which has many predefined values, plus lots of room for vendor-specific values. The rules about the contents of SQLState are as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>It is a&nbsp;<em>5-character string<\/em><\/li><li>May only include&nbsp;<em>upper case characters<\/em>&nbsp;(A-Z) and&nbsp;<em>digits&nbsp;<\/em>(0-9)<\/li><li>The first two characters are called the&nbsp;<em>class code<\/em>.<\/li><li>The last three characters are called the&nbsp;<em>subclass code<\/em>.<\/li><li>Any class code that starts with the characters&nbsp;A-H&nbsp;or&nbsp;0-4&nbsp;indicates an SQLSTATE value defined by the SQL standard, or by other standards related to the SQL standard. For those class codes, any subclass code starting with the same character is also defined by the standard.<\/li><li>Any class code that starts with the characters&nbsp;I-Z&nbsp;or&nbsp;5-9&nbsp;is implementor-defined, and all subclass codes (except for&nbsp;000) are also implementor-defined.<\/li><li>Subclass code&nbsp;000&nbsp;always mean no subclass code defined.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">There are many defined values for <strong>SQLSTATE<\/strong> (see a SQL text or a vendor manual for details), but some interesting ones are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>00000<\/strong>&nbsp;&#8212; successful completion<\/li><li><strong>01xxx<\/strong>&nbsp;&#8212; warning values<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_SQL_Diagnostics_Area\"><\/span>The SQL Diagnostics Area<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">However, just getting a single status value (SQLCODE or SQLSTATE, or both) back from the execution of a SQL statement doesn&#8217;t give you much information. For one thing, it is possible for more than one error to occur during the execution of a SQL statement, and you&#8217;d like to be able to obtain all the necessary information about the errors that occurred..<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL standard introduced the concept of a&nbsp;<em>SQL diagnostics area<\/em>. The diagnostics area is structured as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>A&nbsp;<em>header area<\/em>, followed by<\/li><li>One or more&nbsp;<em>detail areas<\/em>&nbsp;(an array)<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The header area contains:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Name<\/strong><\/th><th><strong>Description<\/strong><\/th><\/tr><tr><td>NUMBER<\/td><td>Number of detail entries in this diagnostics area<\/td><\/tr><tr><td>MORE<\/td><td>&#8216;Y&#8217; if all conditions detected by the database were recorded in the diagnostics area<br>&#8216;N&#8217; if additional conditions were detected but not recorded.<\/td><\/tr><tr><td>COMMAND_FUNCTION<\/td><td>If the SQL statement being reported is a static SQL statement, contains a string representing the statement.<br>If the SQL statement was a dynamic SQL statement, will contain &#8216;EXECUTE&#8217; or &#8216;EXECUTE_IMMEDIATE&#8217;, and DYNAMIC_FUNCTION will contain the code for the dynamic SQL statement itself.<\/td><\/tr><tr><td>DYNAMIC_FUNCTION<\/td><td>If the SQL statement was a dynamic SQL statement, contains the code for the SQL statement being executed.<\/td><\/tr><tr><td>ROW_COUNT<\/td><td>Number of rows that were affected by the SQL statement<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The detail area contains the RETURNED_SQLSTATE for the SQL exception, plus much more information, such as the name of the constraint that was violated (if the error was a constraint violation), catalog name, schema name, table, name, column name, etc. For more details, refer to a SQL text or vendor manual.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL standard invented some SQL syntax to allow access to the information in the SQL diagnostics area:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>GET DIAGNOSTICS target = item [ , target = item ]...<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which obtains information from the header area, and:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>GET DIAGNOSTICS EXCEPTION number target = item [ , target = item ]...<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which obtains information from a detail area.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Each item is the name of a field (such as ROW_COUNT) in the diagnostics area header or specified detail area.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQL_Warnings\"><\/span>SQL Warnings<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">When you perform a JDBC operationt, and a SQL error occurs, you can assume that the SQL operation did not execute correctly. In addition to this, it is possible for an operation to succeed, but indicate a&nbsp;<em>warning<\/em>&nbsp;condition.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Back in SQL-89 days, when SQLCODE ruled, the concept of a SQL warning translated into a&nbsp;<em>positive<\/em>&nbsp;value in SQLCODE. One such positive value was 100, which indicated&nbsp;<em>no [more] data<\/em>. When you are executing in a loop, reading data from a database, you better not ignore the&nbsp;<em>no more<\/em>&nbsp;<em>data<\/em>&nbsp;warning, unless you want to end up in an endless loop! So note that warnings are not to be completely ignored&#8230;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQLSTATE values starting with a class code of&nbsp;<strong>01<\/strong>&nbsp;are warnings.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQLExceptions\"><\/span>SQLExceptions<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In JDBC, when an operation produces an SQL error, an&nbsp;<strong>SQLException<\/strong>&nbsp;is thrown.&nbsp;<strong>SQLException<\/strong>&nbsp;extends from Exception, and thus is a&nbsp;<em>checked exception<\/em>, so you typically will have to deal with it via a&nbsp;<strong>try<\/strong>\/<strong>catch<\/strong>&nbsp;block.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are two interesting methods in class <strong>SQLException<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>public String getSQLState()<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which returns the value of SQLSTATE, and:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>public int getErrorCode()<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which returns the vendor-specific error code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Just as a SQL error can represent several error conditions, a <strong>SQLException<\/strong> can also represent several SQL Exceptions. From the thrown SQLException, you can find the next related SQLException using the method:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>public SQLException getNextException()<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and so on. The SQLExceptions are chained, one to the next.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, when you catch a SQLException, the code ought to look something like:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">        <strong>try\n        {\n           \/\/ ...\n        }\n        catch(SQLException ex)\n        {\n            System.err.println(\"\\n--- SQLException caught ---\\n\");\n            for ( ;ex != null; ex = ex.getNextException())\n            {\n                System.err.println(\"Message:   \" + ex.getMessage());\n                System.err.println(\"SQLState:  \" + ex.getSQLState());\n                System.err.println(\"ErrorCode: \" + ex.getErrorCode());\n                ex.printStackTrace(System.err);\n                System.err.println();\n            }\n            \n\t    \/\/ ...\n        }<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQLWarnings\"><\/span>SQLWarnings<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">When a JDBC operation succeeds, but with some conditional state, then a&nbsp;<strong>SQLWarning<\/strong>&nbsp;is&nbsp;<em><strong>associated<\/strong><\/em>&nbsp;with the execution,&nbsp;<em><strong>but is not thrown<\/strong><\/em>.&nbsp;<strong>SQLWarning<\/strong>&nbsp;is a class that extends from SQLException.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If SQLWarnings are not thrown, how do you determine if any warnings occurred, and if so where do you find them?<br><em><strong>Answer:<\/strong><\/em>&nbsp;You have to explicitly test for them!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Connection,&nbsp;ResultSet&nbsp;and&nbsp;Statement&nbsp;each has a method&nbsp;getWarnings(), which returns the first in a set of&nbsp;SQLWarnings. (Like SQLExceptions, multiple SQLWarnings can be chained together.) If there are no SQLWarnings, getWarnings() returns null.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, when you perform some JDBC operation that can produce warnings, you need to write code something like:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">       <strong>m_con = getConnection();\n       SQLWarning warn = m_con.getWarnings();\n       if (warn != null)\n       {\n           printWarnings(warn);\n           \/\/ ...\n       }\n\n    \/\/ ...\n\n    private static void printWarnings(SQLWarning warn)\n    {\n        System.err.println(\"\\n--- SQLWarning ---\\n\");\n        for ( ;warn != null; warn = warn.getNextWarning())\n        {\n            System.err.println(\"Message:   \" + warn.getMessage());\n            System.err.println(\"SQLState:  \" + warn.getSQLState());\n            System.err.println(\"ErrorCode: \" + warn.getErrorCode());\n            warn.printStackTrace(System.err);\n            System.err.println();\n        }\n    }<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Data_Truncation\"><\/span>Data Truncation<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">SQL warnings are not terribly common. By far the most common of SQL warnings are&nbsp;<em><strong>data truncation<\/strong><\/em>&nbsp;warnings, when JDBC or SQL unexpected truncates a data value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There is a special class,&nbsp;<strong>DataTruncation<\/strong>&nbsp;(a subclass of SQLWarning) to represent this.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When JDBC unexpectedly truncates a data value, it:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>reports<\/em>&nbsp;a DataTruncation warning (<em>on reads<\/em>)<\/li><li><em>throws<\/em>&nbsp;a DataTruncation exception (<em>on writes<\/em>).<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The SQLSTATE value for all DataTruncation warnings is &#8220;01004&#8221;.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To help you determine the source of the problem,&nbsp;<strong>DataTruncation<\/strong>&nbsp;includes the following methods:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>public int getIndex()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Get the index of the column or parameter that was truncated.<br>This may be -1 if the column or parameter index is unknown, in which case the &#8220;parameter&#8221; and &#8220;read&#8221; fields should be ignored.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>public boolean getParameter()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Returns true if the value was a parameter; false if it was a column value.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>public boolean getRead()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Returns true if the value was truncated when read from the database; false if the data was truncated on a write.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>public int getDataSize()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Get the number of bytes of data that should have been transferred. This number may be approximate if data conversions were being performed. The value may be &#8220;-1&#8221; if the size is unknown.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>public int getTransferSize()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Get the number of bytes of data actually transferred. The value may be &#8220;-1&#8221; if the size is unknown.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Accessing_Databases\"><\/span>Accessing Databases<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Once you&#8217;ve established a connection with a database, you can now start to use SQL to access the database. To do this, you use the&nbsp;<strong>Statement<\/strong>&nbsp;interface. Each driver has an associated class which implements the <strong>Statement<\/strong> interface (It also has an associated class which implements the&nbsp;<strong>Connection<\/strong>&nbsp;interface.)<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_Statement_Interface\"><\/span>The&nbsp;Statement&nbsp;Interface<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Given a&nbsp;<strong>Connection<\/strong>, you obtain an instance of&nbsp;<strong>Statement<\/strong>&nbsp;as follows:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Connection conn = DriverManager.getConnection(...);\nStatement stmt = conn.createStatement();<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Now, you can use the&nbsp;<strong>Statement<\/strong>&nbsp;to execute a SQL statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>stmt.executeUpdate(\"CREATE TABLE ...\");<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s an example:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class ConnectToDatabase\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        Statement stmt = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.createStatement();\n            stmt.executeUpdate(&quot;CREATE TABLE address &quot; +\n                               &quot;(&quot; +\n                               &quot;Street     char(50),&quot; +\n                               &quot;Town       char(40),&quot; +\n                               &quot;State      char(2),&quot; +\n                               &quot;PostalCode char(9)&quot; +\n                               &quot;)&quot; );\n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                        stmt.close();\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><strong><em><u>Note:<\/u><\/em><\/strong>&nbsp; You should be cleaning up your statements explicitly, as in the above example.<\/p><\/blockquote>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_executeUpdate_Method\"><\/span>The&nbsp;executeUpdate()&nbsp;Method<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">You use&nbsp;<strong>Statement<\/strong>&#8216;s&nbsp;<strong>executeUpdate()<\/strong>&nbsp;method to execute SQL statements such as CREATE TABLE, DROP TABLE, INSERT, UPDATE and DELETE. It returns the count of rows that were affected by the statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, here are some INSERTs:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class ConnectToDatabase\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        Statement stmt = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.createStatement();\n            \n            int totalCount = 0;\n            int count = 0;\n            count = addAddress(stmt, \n                            &quot;27 Bellvue Ave&quot;, &quot;Carson City&quot;, &quot;NV&quot;, &quot;94567&quot;);\n            totalCount += count;\n            count = addAddress(stmt, \n                            &quot;9000 Spontadema St&quot;, &quot;Jersey City&quot;, &quot;NJ&quot;, &quot;34267&quot;);\n            totalCount += count;\n            count = addAddress(stmt, \n                            &quot;756 Fickle Circle&quot;, &quot;Sun City&quot;, &quot;IA&quot;, &quot;87345&quot;);\n            totalCount += count;\n            System.out.println(totalCount + &quot; addresses added.&quot;);\n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                        stmt.close();\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n    \n    private static int addAddress(Statement stmt, \n                                  String street, String town, \n                                  String state, String postalCode)\n        throws SQLException\n    {\n        String st = (street != null) ? (&quot;&#039;&quot; + street + &quot;&#039;&quot;) : (&quot;NULL&quot;);\n        String tw = (town != null) ? (&quot;&#039;&quot; + town + &quot;&#039;&quot;) : (&quot;NULL&quot;);\n        String sta = (state != null) ? (&quot;&#039;&quot; + state + &quot;&#039;&quot;) : (&quot;NULL&quot;);\n        String code = (postalCode != null) ? (&quot;&#039;&quot; + postalCode + &quot;&#039;&quot;) : (&quot;NULL&quot;);\n        \n        String sql =\n            &quot;INSERT INTO address &quot; +\n            &quot;VALUES(&quot; + st + &quot;, &quot; + tw + &quot;, &quot; + sta + &quot;, &quot; + code + &quot;)&quot;;\n        System.out.println(&quot;Executing statement:\\n&quot; + sql);\n        \n        int count = stmt.executeUpdate(sql);\n        return count;\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">and here is a DELETE:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class ConnectToDatabase\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        Statement stmt = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.createStatement();\n            \n            int totalCount = stmt.executeUpdate(&quot;DELETE FROM address&quot;);\n            System.out.println(totalCount + &quot; addresses deleted.&quot;);\n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                        stmt.close();\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><strong><em><u>Note:<\/u><\/em><\/strong>&nbsp;Some ODBC drivers apparently do not support DELETE!<\/p><\/blockquote>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Performing_Queries\"><\/span>Performing Queries<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">If we wish to perform a SQL query (i.e. a SELECT statement), then we have to use the&nbsp;<strong>executeQuery()<\/strong>&nbsp;method, which returns a <strong>ResultSet<\/strong>. <strong>ResultSet<\/strong> represents the rows returned by the query, and you can use its methods to move through the rows, obtaining information about each row&#8217;s column values:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class ConnectToDatabase\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        Statement stmt = null;\n        ResultSet rs = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.createStatement();\n            rs = stmt.executeQuery(\n                        &quot;SELECT street, town, state, postalCode &quot; +\n                        &quot;FROM address &quot; +\n                        &quot;ORDER BY state&quot;);\n            int count = 0;\n            while (rs.next())\n            {\n                String street = rs.getString(1);\n                String town   = rs.getString(2);\n                String state  = rs.getString(3);\n                String postalCode = rs.getString(4);\n                System.out.println(&quot;&#x5B;&quot; + ++count + &quot;]&quot;);\n                System.out.println(&quot; Street: &#x5B;&quot; + street + &quot;]&quot;);\n                System.out.println(&quot; Town:   &#x5B;&quot; + town + &quot;]&quot;);\n                System.out.println(&quot; State:  &#x5B;&quot; + state + &quot;]&quot;);\n                System.out.println(&quot; Postal Code: &#x5B;&quot; + postalCode + &quot;]&quot;);\n            }\n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                    {\n                        if (rs != null)\n                            rs.close();\n                        stmt.close();\n                    }\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s some output from this program:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>[1]\n Street: [756 Fickle Circle                                 ]\n Town:   [Sun City                                ]\n State:  [IA]\n Postal Code: [87345    ]\n[2]\n Street: [9000 Spontadema St                                ]\n Town:   [Jersey City                             ]\n State:  [NJ]\n Postal Code: [34267    ]\n[3]\n Street: [27 Bellvue Ave                                    ]\n Town:   [Carson City                             ]\n State:  [NV]\n Postal Code: [94567    ]<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note the padding that results from using&nbsp;<strong>CHAR(n)<\/strong>&nbsp;data types.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQL_Data_TypeJava_Data_Type_Mapping\"><\/span>SQL Data Type\/Java Data Type Mapping<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In the above example, we did a SELECT and retrieved column values for each row using the&nbsp;<strong>ResultSet<\/strong>&#8216;s&nbsp;<strong>getString()<\/strong>&nbsp;method. We could do that because we knew that all the columns retrieved were of SQL data type <strong>CHAR(n)<\/strong>, and we knew that it maps to Java data type String. If we had to deal with other SQL data types, we would need to know how those data types map to Java data types.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Data_type_mappings_from_SQL_to_Java\"><\/span>Data type mappings from SQL to Java:<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>SQL Data Type<\/strong><\/th><th><strong>Java Data Type<\/strong><\/th><th><strong>Comments<\/strong><\/th><\/tr><tr><td>BIGINT<\/td><td>long<\/td><td>&nbsp;<\/td><\/tr><tr><td>INTEGER,INT<\/td><td>int<\/td><td>&nbsp;<\/td><\/tr><tr><td>SMALLINT<\/td><td>short<\/td><td>&nbsp;<\/td><\/tr><tr><td>TINYINT<\/td><td>byte<\/td><td>&nbsp;<\/td><\/tr><tr><td>NUMERIC,DECIMAL<\/td><td>java.math.BigDecimal<\/td><td><em>Not<\/em>&nbsp;java.sql.Numeric, as several books maintain. (This appears to be a result of an early change in JDBC that was not picked up by some.)<\/td><\/tr><tr><td>FLOAT<\/td><td>double<\/td><td>&nbsp;<\/td><\/tr><tr><td>REAL<\/td><td>float<\/td><td>&nbsp;<\/td><\/tr><tr><td>DOUBLE<\/td><td>double<\/td><td>&nbsp;<\/td><\/tr><tr><td>CHAR<\/td><td>String<\/td><td>Space-padded to the right<\/td><\/tr><tr><td>VARCHAR,LONGVARCHAR<\/td><td>String<\/td><td>&nbsp;<\/td><\/tr><tr><td>BIT<\/td><td>boolean<\/td><td>&nbsp;<\/td><\/tr><tr><td>DATE<\/td><td>java.sql.Date<\/td><td><em>Not<\/em>&nbsp;java.util.Date.<\/td><\/tr><tr><td>TIME<\/td><td>java.sql.Time<\/td><td>&nbsp;<\/td><\/tr><tr><td>TIMESTAMP<\/td><td>java.sql.Timestamp<\/td><td>&nbsp;<\/td><\/tr><tr><td>BINARY,VARBINARY,<br>LONGVARBINARY<\/td><td>byte[]<\/td><td>&nbsp;<\/td><\/tr><tr><td>BLOB<\/td><td>java.sql.Blob<\/td><td>JDBC 2.0 \/ SQL3<\/td><\/tr><tr><td>CLOB<\/td><td>java.sql.Clob<\/td><td>JDBC 2.0 \/ SQL3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Data_type_mappings_from_Java_to_SQL\"><\/span>Data type mappings from Java to SQL:<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Java Data Type<\/strong><\/th><th><strong>SQL Data Type<\/strong><\/th><th><strong>Comments<\/strong><\/th><\/tr><tr><td>boolean<\/td><td>BIT<\/td><td>&nbsp;<\/td><\/tr><tr><td>byte<\/td><td>TINYINT<\/td><td>&nbsp;<\/td><\/tr><tr><td>byte[]<\/td><td>VARBINARY<\/td><td>If value is too large for VARBINARY, mapped to LONGVARBINARY.<\/td><\/tr><tr><td>double<\/td><td>DOUBLE<\/td><td>&nbsp;<\/td><\/tr><tr><td>float<\/td><td>FLOAT<\/td><td>&nbsp;<\/td><\/tr><tr><td>int<\/td><td>INTEGER<\/td><td>&nbsp;<\/td><\/tr><tr><td>java.sql.Date<\/td><td>DATE<\/td><td>&nbsp;<\/td><\/tr><tr><td>java.math.BigDecimal<\/td><td>NUMERIC<\/td><td>&nbsp;<\/td><\/tr><tr><td>String<\/td><td>VARCHAR<\/td><td>If value is too large for VARCHAR, mapped to LONGVARCHAR.<\/td><\/tr><tr><td>java.sql.Time<\/td><td>TIME<\/td><td>&nbsp;<\/td><\/tr><tr><td>java.sql.Timestamp<\/td><td>TIMESTAMP<\/td><td>&nbsp;<\/td><\/tr><tr><td>java.sql.Array<\/td><td>ARRAY<\/td><td>JDBC 2.0 \/ SQL3<\/td><\/tr><tr><td>java.sql.Blob<\/td><td>BLOB<\/td><td>JDBC 2.0 \/ SQL3<\/td><\/tr><tr><td>java.sql.Clob<\/td><td>CLOB<\/td><td>JDBC 2.0 \/ SQL3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_javasqlTypes_class\"><\/span>The&nbsp;java.sql.Types&nbsp;class<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">The&nbsp;<strong>java.sql.Types<\/strong>&nbsp;class that defines constants that are used to identify generic SQL types, called JDBC types. The actual type constant values are equivalent to those in XOPEN.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_getXXX_Methods\"><\/span>The getXXX() Methods<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">Every Java type that corresponds to a SQL type has a getXXX method in the ResultSet interface. However, some SQL type values may be retrieved using more than one of these getXXX methods, since JDBC does conversion in some cases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is a table that shows which SQL types may be retrieved by which methods, and also which method is the recommended method to use:<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Use_of_ResultSetgetXXX_Methods_to_Retrieve_JDBC_Types\"><\/span>Use of ResultSet.getXXX Methods to Retrieve JDBC Types<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><\/th><th>T<br>I<br>N<br>Y<br>I<br>N<br>T<\/th><th>S<br>M<br>A<br>L<br>L<br>I<br>N<br>T<\/th><th>I<br>N<br>T<br>E<br>G<br>E<br>R<\/th><th>B<br>I<br>G<br>I<br>N<br>T<\/th><th>R<br>E<br>A<br>L<\/th><th>F<br>L<br>O<br>A<br>T<\/th><th>D<br>O<br>U<br>B<br>L<br>E<\/th><th>D<br>E<br>C<br>I<br>M<br>A<br>L<\/th><th>N<br>U<br>M<br>E<br>R<br>I<br>C<\/th><th>B<br>I<br>T<\/th><th>C<br>H<br>A<br>R<\/th><th>V<br>A<br>R<br>C<br>H<br>A<br>R<\/th><th>L<br>O<br>N<br>G<br>V<br>A<br>R<br>C<br>H<br>A<br>R<\/th><th>B<br>I<br>N<br>A<br>R<br>Y<\/th><th>V<br>A<br>R<br>B<br>I<br>N<br>A<br>R<br>Y<\/th><th>L<br>O<br>N<br>G<br>V<br>A<br>R<br>B<br>I<br>N<br>A<br>R<br>Y<\/th><th>D<br>A<br>T<br>E<\/th><th>T<br>I<br>M<br>E<\/th><th>T<br>I<br>M<br>E<br>S<br>T<br>A<br>M<br>P<\/th><\/tr><tr><td><code><strong>getByte<\/strong><\/code><\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getShort<\/strong><\/code><\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getInt<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getLong<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getFloat<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getDouble<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getBigDecimal<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getBoolean<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getString<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><\/tr><tr><td><code><strong>getBytes<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>X<\/td><td>X<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getDate<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>X<\/td><td>&nbsp;<\/td><td>x<\/td><\/tr><tr><td><code><strong>getTime<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>X<\/td><td>x<\/td><\/tr><tr><td><code><strong>getTimestamp<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>X<\/td><\/tr><tr><td><code><strong>getAsciiStream<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getUnicodeStream<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getBinaryStream<\/strong><\/code><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>x<\/td><td>x<\/td><td>X<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><code><strong>getObject<\/strong><\/code><\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><td>x<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">An &#8220;x&#8221; indicates that the&nbsp;<code><strong>getXXX<\/strong><\/code>&nbsp;method may legally be used to retrieve the given JDBC type.<br>An &#8220;X&#8221; indicates that the&nbsp;<code><strong>getXXX<\/strong><\/code>&nbsp;method is&nbsp;<em>recommended<\/em>&nbsp;for retrieving the given JDBC type.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">JDBC 2.0 has also introduced the following getXXX methods:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>getArray()<\/li><li>getBlob()<\/li><li>getCLob()<\/li><li>getBigDecimal()<\/li><li>getRef()<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">among others.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"getXXXint_columnPosition_versus_getXXXString_columnName\"><\/span>getXXX(int columnPosition)&nbsp;versus&nbsp;getXXX(String columnName)<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>ResultSet<\/strong> interface has two sets of getXXX() methods &#8212; one that expects a&nbsp;<em>column position<\/em>&nbsp;(1-based), and one that expects a&nbsp;<em>column name<\/em>. It is recommended to use the&nbsp;<em>column position<\/em>&nbsp;version for the following reasons:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>You may not always know the column name ahead of time (for example, when using a&nbsp;<strong>SELECT * FROM table<\/strong>&nbsp;statement)<\/li><li>If there are more than one columns in the select list with the same name, only the first one is retrieved.<\/li><li>There are case-sensitivity issues (should&nbsp;<strong>getXXX(&#8220;name&#8221;)<\/strong>&nbsp;find a column &#8220;<strong>NAME<\/strong>&#8221; ? What if there is a column &#8220;<strong>Name<\/strong>&#8220;?)<\/li><li>It is often considerably less efficient to look up a column by name rather than by position.<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_Populating_a_Database\"><\/span>Creating &amp; Populating a Database<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In&nbsp;<em><strong>CORE Java, Volume II<\/strong><\/em>, Horstmann and Cornell create a program called&nbsp;<strong>MakeDB<\/strong>, which they use to create and populate tables in a database using JDBC. In their program, which JDBC driver to use, the JDBC URL to use, etc., are all specified in a properties file. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>jdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver\njdbc.url=jdbc:odbc:Employees\njdbc.username=myName\njdbc.password=myPassword<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a modified version of the program, called&nbsp;<strong>CreateDatabase<\/strong>, that allows you to specify a file which contains the names of all the tables to be created:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.io.BufferedReader;\nimport java.io.File;\nimport java.io.FileInputStream;\nimport java.io.FileReader;\nimport java.io.IOException;\nimport java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.ResultSet;\nimport java.sql.ResultSetMetaData;\nimport java.sql.Statement;\nimport java.sql.SQLException;\nimport java.util.Properties;\n\npublic class CreateDatabase\n{\n    \/**\n     *  Main entry point\n     *  @param args command line arguments:\n     *  &lt;br&gt;\n     *  The name of a file containing the names of tables\n     *  to be created, one table name per line.\n     *  Each table name must have an associated &lt;tablename&gt;.dat\n     *  file to define the fields and data for the table.\n     *\/\n    public static void main(String&#x5B;] args)\n    {\n        Connection con = null;\n        Statement stmt = null;\n        try\n        {\n            con = getConnection();\n            stmt = con.createStatement();\n            \n            String tableList = &quot;&quot;;\n            if (args.length &gt; 0)\n                tableList = args&#x5B;0];\n            else\n            {\n                System.out.println(\n                       &quot;Usage: CreateDatabase &lt;table-list-file&gt;&quot;);\n                System.exit(0);\n            }\n            \n            \/\/ Create the tables listed in the tableList file\n            createTables(tableList, stmt);\n        }\n        catch (SQLException ex)\n        {\n            dumpSQLException(ex);\n        }\n        catch (IOException ex)\n        {\n            dumpIOException(ex);\n        }\n        finally\n        {\n            try\n            {\n                if (stmt != null)\n                    stmt.close();\n                if (con != null)\n                    con.close();\n            }\n            catch (SQLException ex)\n            {   \/* Do nothing *\/ }\n        }\n    }\n    \n    \/**\n     *  Gets a connection to the appropriate database, based\n     *  on the contents of a properties file.\n     *\/\n    public static Connection getConnection()\n        throws SQLException, IOException\n    {\n        Properties props = new Properties();\n        File propsFile = new File(&quot;jdbc&quot;, &quot;CreateDatabase.properties&quot;);\n        props.load( new FileInputStream(propsFile) );\n        \n        String drivers = props.getProperty(&quot;jdbc.drivers&quot;);\n        if (drivers != null)\n        {\n            \/\/ Set the System property &quot;jdbc.drivers&quot;\n            System.getProperties().put(&quot;jdbc.drivers&quot;, drivers);\n        }\n        String url = props.getProperty(&quot;jdbc.url&quot;);\n        String username = props.getProperty(&quot;jdbc.username&quot;);\n        String password = props.getProperty(&quot;jdbc.password&quot;);\n        \n        return DriverManager.getConnection(url, username, password);\n    }\n    \n    \/**\n     *  Creates the tables listed in the tableList file.\n     *  @param tableList the filename containing the table list\n     *  @param stmt the Statement to use\n     *\/\n    public static void createTables(String tableList,\n                                    Statement stmt)\n        throws IOException                                   \n    {\n        BufferedReader in = null;\n        try\n        {\n            in = new BufferedReader(\n                       new FileReader(\n                            new File(&quot;jdbc&quot;, tableList) ) );\n\n            \/\/ Each line contains the name of a table to create\n            String tableName = null;\n            while (true)\n            {\n                tableName = in.readLine();\n                if (tableName == null)\n                    break;\n                tableName = tableName.trim();\n                try\n                {\n                    createTable(tableName, stmt);\n                    showTable(tableName, stmt);\n                }\n                catch (Exception ex)\n                {\n                    ex.printStackTrace();\n                    \/\/ Ignore any exceptions, so we can continue\n                    \/\/ on to the next table\n                }\n            }\n        }\n        finally\n        {\n            if (in != null)\n                in.close();\n        }\n    }\n    \n    \/**\n     *  Creates the specified table.\n     *  @param tableName the name of the table to create.\n     *  (There must be a file with the name tableName.dat\n     *  that supplies information about the columns and data.)\n     *  @param stmt the Statement to use.\n     *\/\n    public static void createTable(String tableName,\n                                   Statement stmt)\n        throws SQLException, IOException\n    {\n        BufferedReader in = null;\n        try\n        {\n            in = new BufferedReader(\n                       new FileReader(\n                            new File(&quot;jdbc&quot;, tableName + &quot;.dat&quot;) ) );\n\n            \/\/ First, drop the table in case it already exists\n            String command = &quot;DROP TABLE &quot; + tableName;\n            System.out.println(\n                 &quot;Attempting to drop table &quot; + tableName + &quot;...&quot;);\n            try\n            {\n                stmt.executeUpdate(command);\n            }\n            catch (SQLException ex)\n            {\n                \/\/ Ignore; presumably the table doesn&#039;t exist\n            }\n            \n            String line = in.readLine();\n            \n            command = &quot;CREATE TABLE &quot; + tableName +\n                            &quot;(&quot; + line + &quot;)&quot;;\n        \n            System.out.println(&quot;Creating table &quot; + tableName + &quot;...&quot;);\n            System.out.println(&quot;Statement:\\n&quot; + command);\n            \n            stmt.executeUpdate(command);\n            \n            System.out.println(&quot;Populating table &quot; + tableName + &quot;...&quot;);\n            while ( (line = in.readLine()) != null)\n            {\n                command = &quot;INSERT INTO &quot; + tableName +\n                          &quot; VALUES(&quot; + line + &quot;)&quot;;\n                stmt.executeUpdate(command);\n            }\n        }\n        catch (SQLException ex)\n        {\n            dumpSQLException(ex);\n        }\n            \n        finally\n        {\n            if (in != null)\n                in.close();\n        }\n    }\n    \n    \/**\n     *  Displays the contents of the table.\n     *  @param tableName the name of the table\n     *  @param stmt the Statement to use\n     *\/\n    public static void showTable(String tableName, \n                                 Statement stmt)\n        throws SQLException\n    {\n        String query = &quot;SELECT * FROM &quot; + tableName;\n        ResultSet rs = null;\n        try\n        {\n            rs = stmt.executeQuery(query);\n            ResultSetMetaData rsmd = rs.getMetaData();\n            int columnCount = rsmd.getColumnCount();\n            while (rs.next())\n            {\n                for (int i = 1; i &lt;= columnCount; i++)\n                {\n                    if (i &gt; 1)\n                        System.out.print(&quot;, &quot;);\n                    System.out.print(rs.getString(i));\n                }\n                System.out.println();\n            }\n        }\n        finally\n        {\n            if (rs != null)\n                rs.close();\n        }\n    }\n\n    private static void dumpSQLException(SQLException ex)\n    {\n        System.out.println(&quot;SQLException:&quot;);\n        while (ex != null)\n        {\n            System.out.println(&quot;SQLState: &quot; + ex.getSQLState());\n            System.out.println(&quot;Message:  &quot; + ex.getMessage());\n            System.out.println(&quot;Vendor:   &quot; + ex.getErrorCode());\n            ex = ex.getNextException();\n            System.out.println();\n        }\n    }\n    \n    private static void dumpIOException(IOException ex)\n    {\n        System.out.println(&quot;IOException: &quot; + ex);\n        ex.printStackTrace();\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This program contains a number of other improvements over MakeDB:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The filename passed into the program as its first parameter contains a list of the names of tables to be created, one table per line. For example, in our <strong>Employees<\/strong> and <strong>Departments<\/strong> case, this file contains:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Employees\nDepartments<\/strong><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>For each table specified in the above file, the program expects to find a file&nbsp;<code><strong>&lt;table-name&gt;.dat<\/strong><\/code>, which contains the columns to be created for that table, and also the data to be inserted into the table. For example, here&#8217;s the&nbsp;<strong>Employess.dat<\/strong>&nbsp;file:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>EmployeeID integer, Name char(40), Department integer, Age integer, Salary double\n10063, 'Alfred J Prufrock', 10, 45, 120000\n204567, 'Angela Parfitt', 30, 36, 78000\n345890, 'Michael Solistes', 20, 28, 90000\n89567, 'Charles Schultz', 20, 56, 230000\n56435, 'Barbara Smith', 30, 48, 134000<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and here is the&nbsp;<strong>Departments.dat<\/strong>&nbsp;file:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>DepartmentID integer, Name char(40), Manager integer\n10, 'Accounting', 10063\n20, 'Sales', 89567\n30, 'Information Technology', 56435<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">I used this program to populate my database with tables and data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Transactions\"><\/span>Transactions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Transactions are crucial to proper use of databases. They provide the necessary atomicity and isolation necessary for consistent updates to be performed to a database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are some JDBC policies regarding transactions:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>At most one transaction can exist at any time per database connection.<\/li><li>Drivers are required to allow concurrent requests from different threads using the same Connection. If the driver cannot perform the requests in parallel, they are serialized.<\/li><li>You may cancel a statement&#8217;s execution asynchronously by calling Statement&#8217;s cancel() method from another thread.<\/li><li>By default, each (DML) statement encompasses a transaction by itself (autocommit mode).&nbsp;setAutoCommit(false)&nbsp;turns autocommit mode off.<\/li><li>Deadlock detection behavior is not specified. Most implementations throw an exception, but it could be a SQLException, an RuntimeException, or even an Error.<\/li><li>If autocommit mode is disabled, and you perform operations within a transaction that opens locks on database entities (such as rows in a table), those locks remain in effect until the transaction ends, either by explicit calls to Connection&#8217;s commit() or rollback() methods, or when the connection is closed.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Transaction_Isolation_Levels\"><\/span>Transaction Isolation Levels<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Remember the ACID properties that databases must support? The I is for&nbsp;<em><strong>Isolated<\/strong><\/em>, and there are a variety of&nbsp;<em>isolation levels<\/em>&nbsp;supported by various databases. The isolation levels supply a kind of hierarchy that goes all the way from no transactions supported to the most isolated form,&nbsp;<em>serializable<\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The&nbsp;<strong>Connection<\/strong>&nbsp;interface defines a set of isolation levels<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>TRANSACTION_NONE<\/strong>&nbsp;&#8212; Transactions are not supported.<\/li><li><strong>TRANSACTION_READ_COMMITTED<\/strong>&nbsp;&#8212; Dirty reads are prevented; non-repeatable reads and phantom reads can occur.<\/li><li><strong>TRANSACTION_READ_UNCOMMITTED<\/strong>&nbsp;&#8212; Dirty reads, non-repeatable reads and phantom reads can occur.<\/li><li><strong>TRANSACTION_REPEATABLE_READ<\/strong>&nbsp;&#8212; Dirty reads and non-repeatable reads are prevented; phantom reads can occur.<\/li><li><strong>TRANSACTION_SERIALIZABLE<\/strong>&nbsp;&#8212; Dirty reads, non-repeatable reads and phantom reads are prevented.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Setting_Your_Isolation_Level\"><\/span>Setting Your Isolation Level<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You can determine whether your database supports a particular isolation level:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>boolean b = DatabaseMetaData.supportsTransactionIsolationLevel(\n\t\t\tConnection.TRANSACTION_REPEATABLE_READ);<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and you can then set the isolation level using:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);<\/strong><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Prepared_Callable_Statements\"><\/span>Prepared &amp; Callable Statements<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Prepared_Statements\"><\/span>Prepared Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The work that a database must do to execute a SQL statement is not inconsiderable. In general, it must:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Interpret\/Parse the statement for valid syntax; throw an exception if invalid.<\/li><li>Perform semantic analysis on the statement to match referenced tables and columns to tables and columns in the database; throw an exception if a referenced table or column does not exist, or there is a type mismatch of some kind, invalid operation, etc.<\/li><li><em>Optimize<\/em>&nbsp;the statement. In other words, figure out the most efficient way of executing the statement, given the existence (or otherwise) of indexes on columns referenced in the statement, etc.<\/li><li>Create a&nbsp;<em>plan<\/em>. That is, create a series of instructions for how to execute the statement, based on the results of the optimization.<\/li><li>Execute the statement, by following the plan instructions..<\/li><li>Return any results to the user.<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">That&#8217;s a lot of work!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are many applications where the same statement, or almost the same statement, is executed many times during the course of a typical day. This is especially true in OLTP (On-Line Transaction Processing) applications, where there typically is a small number of transactions types being executed many, many times during a day.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What does &#8216;almost the same statement&#8217; mean? Typically, it means that the application is repeatedly executing the same statement over and over again, with only certain parameters changing. For example, consider the following statements:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, salary \nFROM employees\nWHERE salary &gt; 70000;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, salary \nFROM employees\nWHERE salary &gt; 200000;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You&#8217;ll notice that they are essentially the same statement, except for the value supplied in the WHERE clause.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Normally, you&#8217;d execute this by using the following kind of code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>stmt = conn.createStatement();\nrs = stmt.executeQuery(\n                  \"SELECT name, salary \" + \n                  \"FROM employees \" +\n                  \"WHERE salary &gt; 70000\"\n\/\/ ...\nrs = stmt.executeQuery(\n                  \"SELECT name, salary \" + \n                  \"FROM employees \" +\n                  \"WHERE salary &gt; 200000\");\n\/\/ ...<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In other words, you&#8217;d execute almost the same query twice (or in a typically application, many hundreds or thousands of times a day). This can be quite inefficient, because it repeats many of the steps involved in that execution unnecessarily.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_PreparedStatement_Interface\"><\/span>The&nbsp;PreparedStatement&nbsp;Interface<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">There&#8217;s a better way. Notice that the small change in the WHERE clause value doesn&#8217;t really have any significant effect on many of the initial steps for execution of the SQL statement? Let&#8217;s extract the essence of the above statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, salary \nFROM employees\nWHERE salary &gt; ?;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">By replacing the literal value with a &#8216;<strong>?<\/strong>&#8216;, we have&nbsp;<em><strong>parameterized<\/strong><\/em>&nbsp;the statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s see if we can separate out those steps which don&#8217;t have to be performed every time, in order to execute the statement:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Interpret\/Parse the statement for valid syntax; throw an exception if invalid.<\/li><li>Perform semantic analysis on the statement to match referenced tables and columns to tables and columns in the database; throw an exception if a referenced table or column does not exist, or there is a type mismatch of some kind, invalid operation, etc.<\/li><li><em>Optimize<\/em>&nbsp;the statement. In other words, figure out the most efficient way of executing the statement, given the existence (or otherwise) of indexes on columns referenced in the statement, etc.<\/li><li>Create a&nbsp;<em>plan<\/em>. That is, create a series of instructions for how to execute the statement, based on the results of the optimization.<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">In other words, we need to perform most of the work only once, and then when we actually wish to execute the statement, all we have to do is:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Supply the actual value for the parameter<\/li><li>Execute the query, using the plan.<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The execution of the first 4 steps, which need only to be performed once, is known as&nbsp;<em><strong>preparing the statement<\/strong><\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To represent this concept, JDBC provides the&nbsp;<strong>PreparedStatement<\/strong>&nbsp;class. A&nbsp;<strong>PreparedStatement<\/strong>&nbsp;extends from&nbsp;<strong>Statement<\/strong>, so it is a kind of statement that provides additional capabilities:<em><strong>&nbsp;the ability to be prepared<\/strong><\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>PreparedStatement<\/strong>&nbsp;provides the ability to&nbsp;<em><strong>bind parameters<\/strong><\/em>&nbsp;to the various ? present in the statement source. In order to accomplish this, it provides a number of setXXX(&#8230;) methods which can specify the data and type to be bound to each ? in the statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s an example:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.sql.PreparedStatement;\n\npublic class PreparedStatementUse\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        PreparedStatement stmt = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.prepareStatement(\n                        &quot;SELECT name, salary &quot; +\n                        &quot;FROM employees &quot; +\n                        &quot;WHERE salary &gt; ? &quot; +\n                        &quot;ORDER BY salary&quot;);\n\n            listEmployees(stmt, 80000.00);\n            listEmployees(stmt, 200000.00);\n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                    {\n                        stmt.close();\n                    }\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n\n    private static void listEmployees(PreparedStatement stmt, \n                                      double minSalary)\n        throws SQLException\n    {\n        System.out.println(\n            &quot;==List of employees with salary &gt; &quot; + minSalary + &quot;==&quot;);\n        \n        ResultSet rs = null;\n        try\n        {\n            stmt.setDouble(1, minSalary);\n            rs = stmt.executeQuery();\n            \n            int count = 0;\n            while (rs.next())\n            {\n                String name = rs.getString(1);\n                double salary = rs.getDouble(2);\n                System.out.print(&quot; Name:  &quot; + name);\n                System.out.println(&quot;, Salary: $&quot; + salary);\n            }\n        }\n        finally\n        {\n            if (rs != null)\n            {\n                try\n                {\n                    rs.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">which outputs something like:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>==List of employees with salary &gt; 80000.0==\n Name:  Michael Solistes                        , Salary: $90000.0\n Name:  Alfred J Prufrock                       , Salary: $120000.0\n Name:  Barbara Smith                           , Salary: $134000.0\n Name:  Charles Schultz                         , Salary: $230000.0\n==List of employees with salary &gt; 200000.0==\n Name:  Charles Schultz                         , Salary: $230000.0<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Callable_Statements\"><\/span>Callable Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Most real relational databases now provide the ability to store executable procedures, which may be called by clients. These are called&nbsp;<em><strong>persistent stored modules&nbsp;<\/strong><\/em>(PSM) by the SQL standard. To use these procedures, you must do the following:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Create (and debug) the stored procedure.<\/li><li>Store it in the database<\/li><li>Call the procedure from SQL<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">In the case of JDBC, the call to the procedure is done with a&nbsp;<strong>CallableStatement<\/strong>, which extends&nbsp;<strong>PreparedStatement<\/strong>, and provides additional capabilities above and beyond what that class provides.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While&nbsp;<strong>PreparedStatement<\/strong>&nbsp;supplied a number of setXXX(&#8230;) methods which are used to bind input parameters for the statement,&nbsp;<strong>CallableStatement<\/strong>&nbsp;can also bind output parameters, that is parameters that return data back to the client. So&nbsp;<strong>CallableStatement<\/strong>&nbsp;provides a set of getXXX() methods for this purpose.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>To call a stored procedure, you first construct a CallableStatement. For example:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>CallableStatement cs = con.prepareCall(\"{ call DOWORK(?, ?) }\");<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Notice the syntax of the &#8220;SQL statement&#8221;:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>{ call DOWORK(?, ?) }<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is an example of the syntax used by JDBC when it needs to be able to adapt to different native database syntax for a feature. In this case, many databases have their own special syntax for how they make a call to a stored procedure. Rather than forcing clients to write explicit syntax for a call, different for each database driver, JDBC, like ODBC, provides a way of specifying that the appropriate syntax needs to be invoked in a database-specific way. The JDBC driver is responsible for interpreting the syntax, and automatically converting it into the native database call syntax.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Once you have constructed the CallableStatement, you must register each parameter that can return a value (i.e. each OUT or INOUT parameter):<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>cs.registerOutParameter(2, java.sql.VARCHAR);<\/strong><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>You must set the values of the input parameters exactly as you would for a&nbsp;<strong>PreparedStatement<\/strong>.<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>cs.setDouble(1, minSalary);<\/strong><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>You can execute the CallableStatement the same way you execute a PreparedStatement:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>boolean resultSetReturned = cs.execute();<\/strong><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>To retrieve the values of returned parameters, you use the getXXX() methods:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>String s = cs.getString(2);<\/strong><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>It is possible for the&nbsp;<strong>CallableStatement<\/strong>&nbsp;to return more than one <strong>ResultSet<\/strong>, in which case you can call <strong>getMoreResults() <\/strong>to check if there is another result set available.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Stored procedures can also return values.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>If the stored procedure returns a value, it can be constructed using the following syntax:<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>CallableStatement cs = con.prepareCall(\"{ ? = call GETVALUE(?) }\");<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this case, the result is identified as an OUT parameter by the first ?. A return value is treated as the first OUT parameter, and it must be registered in the same way as other OUT parameters.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_Metadata\"><\/span>Using Metadata<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Metadata, that is, data about data, is very valuable for relational databases, especially if you are writing very generalized tools. For example, consider the problem of writing a program to provide an interactive SQL capability. It should be able to connect to any database and then allow the user to type in SQL statements for it to execute against that database. The user would very likely wish to know what tables are available in the database, and, given a table, what columns exist in that table, and so on.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Metadata is even more important in JDBC, because its role is to provide access to many different databases, whose properties vary somewhat from one database to another. So, JDBC provides lots of data about data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Driver_Metadata\"><\/span>Driver Metadata<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You can find out information about your JDBC drivers, using:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>DriverManager.getDrivers()<\/strong><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">and:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Various&nbsp;<strong>Driver<\/strong>&nbsp;informational methods<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.util.Enumeration;\nimport java.util.Properties;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.DriverPropertyInfo;\nimport java.sql.SQLException;\n\n\/**\n*   Class to list the loaded JDBC drivers, together with their properties.\n*\n*   @author Bryan J. Higgs, 4 March 2000\n*\/\npublic class ListDrivers\n{\n    public static void main(String&#x5B;] args)\n    {\n        try\n        {\n            \/\/ Cause the JDBC drivers to be automatically loaded\n            System.getProperties().put(&quot;jdbc.drivers&quot;,\n                                       &quot;sun.jdbc.odbc.JdbcOdbcDriver:&quot; +\n                                       &quot;ORG.as220.tinySQL.textFileDriver:&quot; +\n                                       &quot;org.gjt.mm.mysql.Driver&quot;);\n            \/\/ List details of the loaded drivers\n            listDrivers();\n        }\n        catch (Exception e)\n        {\n            e.printStackTrace();\n        }\n    }\n    \n    private static void listDrivers()\n        throws SQLException\n    {\n        Enumeration drivers = DriverManager.getDrivers();\n        while (drivers.hasMoreElements())\n        {\n            Driver driver = (Driver) drivers.nextElement();\n            \/\/ Display information about the driver\n            print(&quot;Driver: &quot; + driver.getClass().getName());\n            print(&quot;  Major version: &quot; + driver.getMajorVersion());\n            print(&quot;  Minor version: &quot; + driver.getMinorVersion());\n            print(&quot;  JDBC compliant: &quot; + driver.jdbcCompliant());\n            \/\/ List details of the driver properties\n            listDriverProperties(driver);\n        }\n    }\n    \n    private static void listDriverProperties(Driver driver)\n        throws SQLException\n    {\n        DriverPropertyInfo props&#x5B;] = driver.getPropertyInfo(\n\t\t\t\t\t&quot;&quot;, new Properties());\n        if (props != null &amp;&amp; props.length &gt; 0)\n        {\n            print(&quot;  Properties: &quot;);\n            \/\/ Display each property name and value\n            for (int i = 0; i &lt; props.length; i++)\n            {\n                print(&quot;    Name: &quot; + props&#x5B;i].name);\n                print(&quot;      Description: &quot; + props&#x5B;i].description);\n                print(&quot;      Value: &quot; + props&#x5B;i].value);\n                if (props&#x5B;i].choices != null)\n                {\n                    print(&quot;      Choices: &quot;);\n                    for (int choice = 0; \n                         choice &lt; props&#x5B;i].choices.length; \n                         choice++)\n                    {\n                        print(&quot;        &quot; + props&#x5B;i].choices&#x5B;choice]);\n                    }\n                }\n                print(&quot;      Required: &quot; + props&#x5B;i].required);\n            }\n        }\n}\n    \n    private static void print(String text)\n    {\n        System.out.println(text);\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This produces the following results on my machine:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Driver: sun.jdbc.odbc.JdbcOdbcDriver\n  Major version: 1\n  Minor version: 1001\n  JDBC compliant: true\nDriver: ORG.as220.tinySQL.textFileDriver\n  Major version: 0\n  Minor version: 9\n  JDBC compliant: false\nDriver: org.gjt.mm.mysql.Driver\n  Major version: 1\n  Minor version: 2\n  JDBC compliant: false\n  Properties:\n    Name: HOST\n      Description: Hostname of MySQL Server\n      Value: null\n      Required: true\n    Name: PORT\n      Description: Port number of MySQL Server\n      Value: 3306\n      Required: false\n    Name: DBNAME\n      Description: Database name\n      Value: null\n      Required: false\n    Name: user\n      Description: Username to authenticate as\n      Value: null\n      Required: true\n    Name: password\n      Description: Password to use for authentication\n      Value: null\n      Required: true\n    Name: autoReconnect\n      Description: Should the driver try to re-establish bad connections?\n      Value: false\n      Choices:\n        true\n        false\n      Required: false\n    Name: maxReconnects\n      Description: Maximum number of reconnects to attempt if autoReconnect is true\n      Value: 3\n      Required: false\n    Name: initialTimeout\n      Description: Initial timeout (seconds) to wait between failed connections\n      Value: 2\n      Required: false<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_DatabaseMetadata_Interface\"><\/span>The&nbsp;DatabaseMetadata&nbsp;Interface<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Once you have made a connection to a database, you can call the Connection class&#8217; <strong>getMetaData()<\/strong>&nbsp;method to obtain the database&#8217;s metadata, which is encapsulated in the&nbsp;<strong>DatabaseMetaData<\/strong>&nbsp;class. This class supplies a lot of useful information, such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The database product name and version number<\/li><li>The default transaction isolation level<\/li><li>JDBC driver name, and major and minor version numbers<\/li><li>All the &#8220;extra&#8221; characters that can be used in unquoted identifier names (those beyond&nbsp;a-z,&nbsp;A-Z,&nbsp;0-9&nbsp;and&nbsp;_).<\/li><li>The string used to quote SQL identifiers (JDBC-compliant drivers always use the double quote character,&nbsp;&#8221;&nbsp;)<\/li><li>The maximum sizes of names, literals, etc.<\/li><li>The maximum columns per table.<\/li><li>The maximum number of concurrent connections supported<\/li><li>The maximum row size<\/li><li>The tables that exist in the database<\/li><li>Information about the datatypes available for the database<\/li><li>The column names that exist in the database, and their attributes<\/li><li>The indexes that exist in the database, and their statistics.<\/li><li>The procedures that exist in the database<\/li><li>The table privileges<\/li><li>Whether SQL92 entry, intermediate, or full levels are supported.<\/li><li>What foreign key columns reference the primary key columns of a primary key table<\/li><li>What user name is being used for this connection<\/li><li>Whether this database connection is read-only<\/li><li>What keywords are used by the database that are not SQL-92 standard<\/li><li>Whether the database supports column aliasing<\/li><li>Whether multiple result sets from a single&nbsp;execute()&nbsp;call are supported<\/li><li>Whether outer joins are supported<\/li><li>What the primary keys are for a table<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">and a whole host of other information, both basic and esoteric. Take a look at the <strong>DatabaseMetaData<\/strong> class to get a sense for how much information is available!<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_ResultSetMetaData_Interface\"><\/span>The&nbsp;ResultSetMetaData&nbsp;Interface<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Once you have created a&nbsp;<strong>ResultSet<\/strong>, you can query its attributes by using the&nbsp;<strong>ResultSetMetaData<\/strong>&nbsp;interface.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.sql.Driver;\nimport java.sql.DriverManager;\nimport java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.sql.ResultSetMetaData;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class GetResultSetMetaData\n{\n    public static void main(String&#x5B;] args)\n    {\n        Connection conn = null;\n        Statement stmt = null;\n        ResultSet rs = null;\n        try\n        {\n            Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n            conn = DriverManager.getConnection(&quot;jdbc:odbc:Employees&quot;);\n            stmt = conn.createStatement();\n            rs = stmt.executeQuery(\n              &quot;SELECT employees.name, NULL, departments.name, age, salary &quot; + \n              &quot;FROM employees, departments &quot; +\n              &quot;WHERE employees.department = departmentID&quot;);\n                        \n            printResultSetMetaData(rs);\n            \n        }\n        catch(ClassNotFoundException ex)\n        {\n            ex.printStackTrace();\n        }\n        catch(SQLException ex)\n        {\n            ex.printStackTrace();\n        }\n        finally\n        {\n            if (conn != null)\n            {\n                try\n                {\n                    if (stmt != null)\n                    {\n                        if (rs != null)\n                            rs.close();\n                        stmt.close();\n                    }\n                    conn.close();\n                }\n                catch(SQLException ex)\n                { \/* Do nothing *\/ }\n            }\n        }\n    }\n    \n    private static void printResultSetMetaData(ResultSet rs)\n        throws SQLException\n    {\n        ResultSetMetaData rsmd = rs.getMetaData();\n        int count = rsmd.getColumnCount();\n        System.out.println(&quot;ResultSet has &quot; + count + &quot; columns:&quot;);\n        for (int i = 1; i &lt;= count; i++)\n        {\n            println(&quot;&#x5B;&quot; + i + &quot;]&quot;);\n            println(&quot; Name:      &quot; + rsmd.getColumnName(i));\n            println(&quot; Label:     &quot; + rsmd.getColumnLabel(i));\n            println(&quot; Catalog:   &quot; + rsmd.getCatalogName(i));\n            println(&quot; Schema:    &quot; + rsmd.getSchemaName(i));\n            println(&quot; Table:     &quot; + rsmd.getTableName(i));\n            println(&quot; Type:      &quot; + rsmd.getColumnTypeName(i));\n            println(&quot; Display size: &quot; + rsmd.getColumnDisplaySize(i));\n            println(&quot; Precision:    &quot; + rsmd.getPrecision(i));\n            println(&quot; Scale:        &quot; + rsmd.getScale(i));\n            println(&quot; CaseSensitive:&quot; + rsmd.isCaseSensitive(i));\n            println(&quot; Currency:     &quot; + rsmd.isCurrency(i));\n            println(&quot; Nullable:     &quot; + rsmd.isNullable(i));\n            \/\/ etc...\n        }\n    }\n    \n    private static void println(String text)\n    {\n        System.out.println(text);\n    }\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">which outputs, on my machine, with one ODBC data source:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>ResultSet has 5 columns:\n[1]\n Name:      name\n Label:     name\n Catalog:\n Schema:\n Table:\n Type:      CHAR\n Display size: 40\n Precision:    40\n Scale:        0\n CaseSensitive:true\n Currency:     false\n Nullable:     1\n[2]\n Name:      Expr1001\n Label:     Expr1001\n Catalog:\n Schema:\n Table:\n Type:\n Display size: -4\n Precision:    -100\n Scale:        0\n CaseSensitive:false\n Currency:     false\n Nullable:     1\n[3]\n Name:      name\n Label:     name\n Catalog:\n Schema:\n Table:\n Type:      CHAR\n Display size: 40\n Precision:    40\n Scale:        0\n CaseSensitive:true\n Currency:     false\n Nullable:     1<\/strong>\n<strong>[4]  <\/strong>\n <strong>Name:      age  <\/strong>\n <strong>Label:     age  <\/strong>\n <strong>Catalog:  <\/strong>\n <strong>Schema:  <\/strong>\n <strong>Table:  <\/strong>\n <strong>Type:      INTEGER  <\/strong>\n <strong>Display size: 11  <\/strong>\n <strong>Precision:    10  <\/strong>\n <strong>Scale:        0  <\/strong>\n <strong>CaseSensitive:false  <\/strong>\n <strong>Currency:     false <\/strong>\n<strong> Nullable:     1 <\/strong>\n<strong>[5] <\/strong>\n<strong> Name:      salary <\/strong>\n<strong> Label:     salary <\/strong>\n<strong> Catalog: <\/strong>\n<strong> Schema: <\/strong>\n<strong> Table: <\/strong>\n<strong> Type:      FLOAT <\/strong>\n<strong> Display size: 22 <\/strong>\n<strong> Precision:    15 <\/strong>\n<strong> Scale:        0 <\/strong>\n<strong> CaseSensitive:false <\/strong>\n<strong> Currency:     false <\/strong>\n<strong> Nullable:     1<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and the following with a different ODBC data source that contained the same data (which shows some subtle differences):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>[1]\n Name:      name\n Label:     name\n Catalog:\n Schema:\n Table:\n Type:      CHAR\n Display size: 40\n Precision:    40\n Scale:        0\n CaseSensitive:false\n Currency:     false\n Nullable:     1\n[2]\n Name:      Expr1001\n Label:     Expr1001\n Catalog:\n Schema:\n Table:\n Type:      BINARY\n Display size: -4\n Precision:    -100\n Scale:        0\n CaseSensitive:false\n Currency:     false\n Nullable:     1\n[3]\n Name:      name\n Label:     name\n Catalog:\n Schema:\n Table:\n Type:      CHAR\n Display size: 40\n Precision:    40\n Scale:        0\n CaseSensitive:false\n Currency:     false\n Nullable:     1<\/strong>\n<strong>[4] <\/strong>\n<strong> Name:      age <\/strong>\n<strong> Label:     age <\/strong>\n<strong> Catalog: <\/strong>\n<strong> Schema: <\/strong>\n<strong> Table: <\/strong>\n<strong> Type:      INTEGER <\/strong>\n<strong> Display size: 11 <\/strong>\n<strong> Precision:    10 <\/strong>\n<strong> Scale:        0 <\/strong>\n<strong> CaseSensitive:false <\/strong>\n<strong> Currency:     false <\/strong>\n<strong> Nullable:     1 <\/strong>\n<strong>[5] <\/strong>\n<strong> Name:      salary <\/strong>\n<strong> Label:     salary <\/strong>\n<strong> Catalog: <\/strong>\n<strong> Schema: <\/strong>\n<strong> Table: <\/strong>\n<strong> Type:      DOUBLE <\/strong>\n<strong> Display size: 22 <\/strong>\n<strong> Precision:    15 <\/strong>\n<strong> Scale:        0 <\/strong>\n<strong> CaseSensitive:false <\/strong>\n<strong> Currency:     false <\/strong>\n<strong> Nullable:     1<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Putting_it_All_Together\"><\/span><a>Putting it All Together<\/a><span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Here is a program that can connect to a database, and display all the tables in it. Then, if you select one of those tables, you can see the columns in the table, and the data in the rows of that table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Initially, when you run the program, you see a window like this:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignleft size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"200\" src=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables1.jpg\" alt=\"\" class=\"wp-image-566\"\/><\/figure><\/div>\n\n\n\n<div style=\"height:6px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If you select the Employees table, you then see the following:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignleft size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"406\" height=\"210\" src=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables2.jpg\" alt=\"\" class=\"wp-image-567\" srcset=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables2.jpg 406w, https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables2-300x155.jpg 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/figure><\/div>\n\n\n\n<div style=\"height:6px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">which shows the columns in the table, and the values for the first row of the table. You can click on the&nbsp;<strong>Next &gt;<\/strong>&nbsp;button to move to the next row&#8217;s data, etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you select the Departments table, you will see the following:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignleft size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"416\" height=\"164\" src=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables3.jpg\" alt=\"\" class=\"wp-image-568\" srcset=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables3.jpg 416w, https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-content\/uploads\/2021\/02\/ViewTables3-300x118.jpg 300w\" sizes=\"auto, (max-width: 416px) 100vw, 416px\" \/><\/figure><\/div>\n\n\n\n<div style=\"height:8px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">and so on.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is the program that accomplishes this. It is based on the ViewDB program in CORE Java, Vol II: Advanced Features, by Cay Horstmann &amp; Gary Cornell, with some changes and restructuring. It also switches back to using AWT, rather than JFC\/Swing.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage jdbc;\n\nimport java.awt.BorderLayout;\nimport java.awt.Button;\nimport java.awt.Choice;\nimport java.awt.Color;\nimport java.awt.Component;\nimport java.awt.Container;\nimport java.awt.Dimension;\nimport java.awt.Frame;\nimport java.awt.GridBagConstraints;\nimport java.awt.GridBagLayout;\nimport java.awt.Insets;\nimport java.awt.Label;\nimport java.awt.Panel;\nimport java.awt.Rectangle;\nimport java.awt.TextField;\nimport java.awt.Toolkit;\nimport java.awt.event.ActionListener;\nimport java.awt.event.ActionEvent;\nimport java.awt.event.ItemListener;\nimport java.awt.event.ItemEvent;\nimport java.awt.event.WindowAdapter;\nimport java.awt.event.WindowEvent;\nimport java.awt.event.WindowListener;\n\nimport java.sql.Connection;\nimport java.sql.DatabaseMetaData;\nimport java.sql.DriverManager;\nimport java.sql.ResultSet;\nimport java.sql.ResultSetMetaData;\nimport java.sql.Statement;\nimport java.sql.SQLException;\nimport java.sql.SQLWarning;\n\nimport java.util.Vector;\n\nimport messageBox.MessageBox;\n\n\/**\n *  Class to view the tables in a database, and their data.\n *\n *  @author Bryan J. Higgs, 4 March 2000\n *\/\npublic class ViewTables extends Frame\n{\n    public static void main(String&#x5B;] args)\n    {\n        Frame f = new ViewTables();\n        \/\/ Center frame on screen\n        Toolkit toolkit = Toolkit.getDefaultToolkit();\n        Dimension screen = toolkit.getScreenSize();\n        Dimension frame = f.getSize();\n        f.setLocation( (screen.width - frame.width)\/2, \n                       (screen.height - frame.height)\/2);\n        \/\/ Make it visible\n        f.setVisible(true);\n    }\n\n    \/**\n     *  Constructs an instance of ViewTables.\n     *\/\n    public ViewTables()\n    {\n        super(&quot;View Tables&quot;);\n        setSize(300, 200);\n        addWindowListener(new WindowAdapter()\n            {\n                public void windowClosing(WindowEvent ev)\n                {\n                    dispose();\n                    System.exit(0);\n                }\n            }\n        );\n        \n        \/\/ Do layout...\n        \n        \/\/ Top panel goes to the North\n        Panel top = new Panel( new BorderLayout() );\n        top.setBackground(Color.lightGray);\n        \/\/ Contains a label...\n        Label tableLabel = new Label(&quot;Table:&quot;, Label.RIGHT);\n        top.add(tableLabel, BorderLayout.WEST);\n        \/\/ ...and a Choice\n        Choice tableNames = new Choice();\n        tableNames.addItemListener(new ItemListener()\n            {\n                public void itemStateChanged(ItemEvent ev)\n                {\n                    if (ev.getStateChange() == ItemEvent.SELECTED)\n                    {\n                        \/\/ We have selected a table name, so figure\n                        \/\/ out what columns we have and lay them out.\n                        loadColumnDisplay((String)ev.getItem());\n                    }\n                }\n            }\n        );\n        top.add(tableNames, BorderLayout.CENTER);\n        add(top, BorderLayout.NORTH);\n        \n        \/\/ Data panel goes in the Center\n        m_dataPanel = new Panel();\n        \/\/ Initially contains a helpful label\n        m_dataPanel.add( new Label(&quot;Select a table to display data.&quot;) );\n        add(m_dataPanel, BorderLayout.CENTER);\n        \n        \/\/ Bottom panel goes to the South\n        Panel bottom = new Panel();\n        \/\/ Contains just a button\n        m_nextButton = new Button(&quot;Next &gt;&quot;);\n        m_nextButton.setEnabled(false);\n        m_nextButton.addActionListener(new ActionListener()\n            {\n                public void actionPerformed(ActionEvent ev)\n                {\n                    \/\/ Cause the current data to be displayed\n                    \/\/ and the next row to be read.\n                    displayRow();\n                }\n            }\n        );\n        bottom.add(m_nextButton);\n        add(bottom, BorderLayout.SOUTH);\n        \n        \/\/ Load the table names into the choice box.\n        loadTableNames(tableNames);\n    }\n    \n    \/**\n     *  Gets a connection to the specified database, \n     *  using the specified driver.\n     *\/\n    private static Connection getConnection()\n        throws SQLException, ClassNotFoundException\n    {\n        Class.forName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;);\n        Connection con = DriverManager.getConnection(\n\t\t\t\t&quot;jdbc:odbc:Employees&quot;);\n        SQLWarning warn = con.getWarnings();\n        if (warn != null)\n            printSQLWarnings(warn);\n        return con;\n    }\n    \n    \/**\n     *  Loads the specified choice box with the names of all the tables.\n     *\/\n    private void loadTableNames(Choice tableNames)\n    {\n        \/\/ Make the connection, then discover all the tables\n        \/\/ and load their names into the table names choice box.\n        ResultSet metaRs = null;\n        try\n        {\n            m_con = getConnection();\n            m_stmt = m_con.createStatement();\n            \/\/ Obtain the metadata for the tables\n            DatabaseMetaData dbMd = m_con.getMetaData();\n            metaRs = dbMd.getTables(null, \/\/ Catalog name\n                                    null, \/\/ Schema name\n                                    null, \/\/ Table name\n                                    new String &#x5B;] { &quot;TABLE&quot;, &quot;VIEW&quot; }\n                                          \/\/ Types\n                                   );\n            SQLWarning warn = metaRs.getWarnings();\n            if (warn != null)\n                printSQLWarnings(warn);\n            while (metaRs.next())\n            {\n                tableNames.addItem(metaRs.getString(3));\n            }\n        }\n        catch(SQLException ex)\n        {\n            printSQLExceptions(ex);\n            MessageBox.show(this, ex.toString());\n        }\n        catch(ClassNotFoundException ex)\n        {\n            MessageBox.show(this, ex.toString());\n        }\n        finally\n        {\n            try\n            {\n                if (metaRs != null)\n                    metaRs.close();\n            }\n            catch (SQLException ex)\n            {   \/* Do nothing *\/ }\n        }\n    }\n    \n    \/**\n     *  Adds the specified component to the specified container,\n     *  using the specified GridBagConstraints, grid position, \n     *  width &amp; height.\n     *\/\n    private void addComponent(\n                    Container container, Component component,\n                    GridBagConstraints gbc,\n                    int x, int y, int width, int height)\n    {\n        gbc.gridx = x;\n        gbc.gridy = y;\n        gbc.gridwidth = width;\n        gbc.gridheight = height;\n        container.add(component, gbc);\n    }\n    \n    \/**\n     *  Loads the information about the table columns, and\n     *  sets up the GUI layout to accommodate the data for a row.\n     *\/\n    private void loadColumnDisplay(String tableName)\n    {\n        \/\/ Remove the existing data panel\n        remove(m_dataPanel);\n        \/\/ and replace it with a new one\n        m_dataPanel = new Panel()\n        {\n            public Insets getInsets()\n            {\n                return new Insets(5, 5, 5, 5);\n            }\n        };\n        m_dataPanel.setLayout( new GridBagLayout() );\n        \/\/ Remove all the column textfields from the Vector\n        m_fields.removeAllElements();\n        \/\/ Now, execute a SELECT * FROM table query against this \n        \/\/ table and then use the ResultSet meta data to find \n        \/\/ column information.\n        \/\/ Then dynamically lay out the data panel.\n        GridBagConstraints gbc = new GridBagConstraints();\n        gbc.weighty = 100;\n        try\n        {\n            if (m_rs != null)\n                m_rs.close();\n            m_rs = m_stmt.executeQuery(&quot;SELECT * from &quot; + tableName);\n            SQLWarning warn = m_rs.getWarnings();\n            if (warn != null)\n                printSQLWarnings(warn);\n            ResultSetMetaData rsmd = m_rs.getMetaData();\n            for (int col = 1; col &lt;= rsmd.getColumnCount(); col++)\n            {\n                String columnName = rsmd.getColumnLabel(col);\n                int columnWidth = rsmd.getColumnDisplaySize(col);\n                TextField colField = new TextField(columnWidth);\n                \/\/ Keep track of the TextFields in a Vector\n                m_fields.addElement(colField);\n                \/\/ Add a label for the column, containing the column name\n                gbc.weightx = 0;\n                gbc.anchor = GridBagConstraints.EAST;\n                gbc.fill = GridBagConstraints.NONE;\n                addComponent(m_dataPanel, new Label(columnName + &quot;:&quot;), \n                             gbc, 0, col-1, 1, 1);\n                \/\/ Add a text field for the data in the column\n                gbc.weightx = 100;\n                gbc.anchor = GridBagConstraints.WEST;\n                gbc.fill = GridBagConstraints.HORIZONTAL;\n                addComponent(m_dataPanel, colField,\n                             gbc, 1, col-1, 1, 1);\n            }\n            \n        }\n        catch(Exception ex)\n        {\n            MessageBox.show(this, ex.toString());\n        }\n        \/\/ Add the new data panel in place of the old one.\n        add(m_dataPanel, BorderLayout.CENTER);\n        \/\/ Force a layout and sizing.\n        pack();\n        \n        \/\/ Enable Next button so user can navigate rows\n        m_nextButton.setEnabled(true);\n        \n        \/\/ Read first row\n        readRow();\n        \n        \/\/ Display row results\n        displayRow();\n    }\n    \n    \/**\n     *  Reads the next row for the table.\n     *  When it comes to the end of the rows, closes the ResultSet\n     *  and disables the Next button so we can&#039;t go any further.\n     *\/\n    private void readRow()\n    {\n        if (m_rs != null)\n        {\n            try\n            {\n                if (!m_rs.next())   \/\/ Move to next row\n                {\n                    m_rs.close();   \/\/ We&#039;re done\n                    m_rs = null;\n                    \n                    \/\/ Disable the Next button\n                    m_nextButton.setEnabled(false);\n                }\n            }\n            catch (Exception ex)\n            {\n                MessageBox.show(this, ex.toString());\n            }\n        }\n    }\n    \n    \/**\n     *  Displays the current row contents in the GUI text fields.\n     *  Then reads the next row, so we&#039;re ready for it, and so we\n     *  can disable the Next button properly, if there is no next row.\n     *\/\n    private void displayRow()\n    {\n        try\n        {\n            for (int col = 1; col &lt;= m_fields.size(); col++)\n            {\n                String colValue = m_rs.getString(col);\n                TextField colField = (TextField) m_fields.elementAt(col-1);\n                colField.setText(colValue);\n            }\n        }\n        catch (Exception ex)\n        {\n            MessageBox.show(this, ex.toString());\n        }\n        \n        \/\/ Read next row\n        readRow();\n    }\n    \n    \/**\n     *  Prints out a SQLException and its related SQLExceptions\n     *\/\n    private static void printSQLExceptions(SQLException ex)\n    {\n        System.err.println(&quot;\\n--- SQLException caught ---\\n&quot;);\n        for ( ;ex != null; ex = ex.getNextException())\n        {\n            System.err.println(&quot;Message:   &quot; + ex.getMessage());\n            System.err.println(&quot;SQLState:  &quot; + ex.getSQLState());\n            System.err.println(&quot;ErrorCode: &quot; + ex.getErrorCode());\n            ex.printStackTrace(System.err);\n            System.err.println();\n        }\n    }\n    \n    \/**\n     *  Prints out a SQLWarning and its related SQLWarnings\n     *\/\n    private static void printSQLWarnings(SQLWarning warn)\n    {\n        System.err.println(&quot;\\n--- SQLWarning ---\\n&quot;);\n        for ( ;warn != null; warn = warn.getNextWarning())\n        {\n            System.err.println(&quot;Message:   &quot; + warn.getMessage());\n            System.err.println(&quot;SQLState:  &quot; + warn.getSQLState());\n            System.err.println(&quot;ErrorCode: &quot; + warn.getErrorCode());\n            warn.printStackTrace(System.err);\n            System.err.println();\n        }\n    }\n    \n    \/\/\/\/\/ Private data \/\/\/\/\/\n    private Button     m_nextButton;    \/\/ Next button\n    private Panel      m_dataPanel;     \/\/ Panel for column data display\n    private Vector     m_fields = new Vector();\n                                        \/\/ Holds column TextFields\n    \n    private Connection          m_con;  \/\/ Connection to use\n    private Statement           m_stmt; \/\/ Statement to use\n    private ResultSet           m_rs;   \/\/ ResultSet used to read rows\n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">For completeness, here&#8217;s the <strong>MessageBox<\/strong> class that the above program uses:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: java; auto-links: false; title: ; quick-code: false; notranslate\" title=\"\">\npackage messageBox;\n\nimport java.awt.BorderLayout;\nimport java.awt.Button;\nimport java.awt.Dialog;\nimport java.awt.Dimension;\nimport java.awt.Frame;\nimport java.awt.Graphics;\nimport java.awt.Insets;\nimport java.awt.Panel;\nimport java.awt.Rectangle;\nimport java.awt.TextArea;\nimport java.awt.event.ActionEvent;\nimport java.awt.event.ActionListener;\nimport java.awt.event.WindowAdapter;\nimport java.awt.event.WindowEvent;\n\n\/**\n *  Shows a message box with the specified text.\n *  @author Bryan J. Higgs, 4 March 2000\n *\/\npublic class MessageBox extends Dialog\n{\n    \/**\n     *  Shows a message box with the specified message text\n     *  with the specified parent Frame.\n     *\/\n    public static void show(Frame frame, String msg)\n    {\n        MessageBox box = new MessageBox(frame, msg);\n        box.setVisible(true);\n    }\n        \n    \/**\n     *  Constructs a MessageBox instance.\n     *  (Private, so the only way of using a MessageBox is\n     *  via the show() method.)\n     *\/\n    private MessageBox(Frame frame, String msg)\n    {\n        super(frame, &quot;Message&quot;);\n        addWindowListener\n        (\n            new WindowAdapter()\n            {\n                public void windowClosing(WindowEvent ev)\n                {\n                    closeWindow();\n                }\n            }\n        );\n        Panel message = new MessagePanel(msg);\n        add(message, BorderLayout.CENTER);\n        Panel p = new Panel();\n        Button ok = new Button(&quot;OK&quot;);\n        p.add(ok);\n        add(p, BorderLayout.SOUTH);\n        ok.addActionListener\n        (\n            new ActionListener()\n            {\n                public void actionPerformed(ActionEvent ev)\n                {\n                    closeWindow();\n                }\n            }\n        );\n        pack();\n        \n        \/\/ Ensure that the message box displays close to\n        \/\/ its parent frame.\n        Rectangle frameRect = frame.getBounds();\n        Rectangle boxRect = getBounds();\n        boxRect.x = frameRect.x + 10;\n        boxRect.y = frameRect.y + 10;\n        setBounds(boxRect.x, boxRect.y, boxRect.width, boxRect.height);\n    }\n        \n    \/**\n     *  Closes the MessageBox window.\n     *\/\n    private void closeWindow()\n    {\n        setVisible(false);\n        dispose();        \n    }\n    \n    \/**\n     *  Main entry point, for testing.\n     *\/\n    public static void main(String&#x5B;] args)\n    {\n        Frame frame = new Frame(&quot;Testing MessageBox&quot;);\n        frame.setBounds(100, 100, 300, 200);\n        frame.addWindowListener\n        (\n            new WindowAdapter()\n            {\n                public void windowClosing(WindowEvent ev)\n                {\n                    System.exit(0);\n                }\n            }\n        );\n        frame.setVisible(true);\n        show(frame, &quot;Hello\\nHow\\nAre\\nYou&quot;);\n    }\n    \n    \/\/\/\/\/ Inner classes \/\/\/\/\/\n    \n    \/**\n     *  A Message Panel, to display a message in the MessageBox.\n     *\/\n    class MessagePanel extends Panel\n    {\n        MessagePanel(String msg)\n        {\n            m_msg = new TextArea(msg, 4, 30, \n                                 TextArea.SCROLLBARS_NONE);\n            m_msg.setEditable(false);\n            setLayout( new BorderLayout() );\n            add(m_msg, BorderLayout.CENTER);\n        }\n        \n        public Insets getInsets()\n        {\n            return new Insets(10, 10, 10, 10);\n        }\n        \n        \/\/\/\/ Private Data \/\/\/\/\n        private TextArea  m_msg;\n        private Dimension m_dim = null;\n    }\n}\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Connecting to Databases Connecting to a database with JDBC involves the following: Obtain a JDBC Driver and DatabaseThere are many vendors who supply JDBC drivers for a number of different databases systems. There are plenty of options, including IBM DB2, Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.Note that every JDK comes with an implementation of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":542,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","ocean_front_end_style_editor":"no","ocean_post_layout":"left-sidebar","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"ocs-course-topics-sidebar","ocean_second_sidebar":"0","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"0","ocean_custom_header_template":"0","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"0","ocean_menu_typo_font_family":"0","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"0","footnotes":""},"class_list":["post-555","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/comments?post=555"}],"version-history":[{"count":7,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/555\/revisions"}],"predecessor-version":[{"id":570,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/555\/revisions\/570"}],"up":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/542"}],"wp:attachment":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/media?parent=555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}