Giáo trình Java

DRAFT, 8/24/01 159 Copyright © 2001 O’Reilly & Associates, Inc. 14 Java Java is one of th Its database acc mature database we cover in this and any other MySQL-specific Instead, the "pro for JDBC and co pendent of MyS tions. In this chapter, guage and Java recommend taki details on how t Chapter 6, Datab and Java, 2nd E The JDBC Like all Java AP port a specific s database access abstractions fro Connection, fo Similarly, a Res statement. Java package which ne of the th coding. L support rgely inde- ke transac- ming lan- e strongly . For more iscussed in with JDBC her to sup- is naturally I are thus atabase. A onnection. L SELECT java.sql API Is, JDBC is a set of classes and interfaces that work toget et of functionality. In the case of JDBC, this functionality . The classes and interfaces that make up the JDBC AP m concepts common to database access for any kind of d r example, is a Java interface representing a database c ultSet represents a result set of data returned from a SQ puts the classes that form the JDBC API together in the Sun introduced in JDK 1.1.concepts. If you do not already have this background, w ng a look at Learning Java (O’Reilly & Associates, Inc.) o build the sort of three-tier database applications we d ase Applications, take a look at Database Programming dition (O’Reilly & Associates, Inc.).Chapter 11 0. e simplest languages in which you can write MySQL applications. ess API JDBC (Java DataBase Connectivity) is one of the more -independent database access APIs in common use. Most of what chapter can be applied to Oracle, Sybase, MS SQL Server, mSQL, database engine as well as MySQL. In fact, nearly no information in this chapter has anything to do wi prietary" information relates only to downloading MySQ nfiguring the runtime environment. Everything else is la QL, excepting for features not supported by MySQL li we assume a basic understanding of the Java program DRAFT, 8/24/01 The underlying JDBC does not sql package are ual database ve something called know only a few the JDBC interfa The first databa your database. D ety of databases tation that will p Though it may s base vendors ge flexibility. As an by other develop Sun has created tectures. Each J mance and flexib Type 1 These drive ODBC bridg kind of drive ODBC API learning JDB production e Type 2 Type 2 drive tains Java co vendor. In t MySQL’s C A tion. Type require the database. Ap ity. Type 3 Type 3 drive API where t server. This middleware architecturedetails of database access naturally differ from vendor to vendor. actually deal with those details. Most of the classes in the java. in fact interfaces—and thus no implementation details. Individ- ndors provide implementations of these interfaces in the form of a JDBC driver. As a database programmer, however, you need to details about the driver you are using—the rest you manage via ces. se-dependent thing you need to know is what drivers exist for ifferent people provide different JDBC implementations for a vari- . As a database programmer, you want to select a JDBC implemen- rovide the greatest stability and performance for your application. eem counterintuitive, JDBC implementations provided by the data- nerally sit at the bottom of the pack when it comes to stability and Open Source project, however, MySQL relies on drivers provided ers in the community. four classifications that divide JDBC drivers based on their archi- DBC driver classification represents a trade-off between perfor- ility. rs use a bridging technology to access a database. The JDBC- e that comes with JDK 1.2 is the most common example of this r. It provides a gateway to the ODBC API. Implementations of the in turn perform the actual database access. Though useful for C and quick testing, bridging solutions are rarely appropriate for nvironments. rs are native API drivers. "Native API" means that the driver con- de that calls native C or C++ methods provided by the database he context of MySQL, a Type 2 driver would be one that used PI under the covers to talk to MySQL on behalf of your applica- 2 drivers generally provide the best performance, but they do installation of native libraries on clients that need to access the plications using Type 2 drivers have a limited degree of portabil- rs provide a client with a pure Java implementation of the JDBC he driver uses a network protocol to talk to middleware on the middleware, in turn, performs the actual database access. The may or may not use JDBC for its database access. The Type 3 is actually more of a benefit to driver vendors than applicationCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 architects si claim suppo weak perfor Type 4 Using netwo directly to t solution. Be Type 4 driv MySQL, how ent Type 4 M Practically speak production appl 4 comes down independence, w and run on any dence since you the database acc cation will not ru form better than Knowing the dr about which JD down to knowin compare to each course, you are well—but we do ers. Of the three My thus likely does driver (also know Table 0-1. . JDBC D Driver Name a Open Source Initi license, the specifi mm (GNU) twz Cauchonce it enables the vendor to write a single implementation and rt for any database that has a JDBC driver. Unfortunately, it has mance and unpredictable stability. rk protocols built into the database engine, Type 4 drivers talk he database using Java sockets. This is the most direct pure Java cause these network protocols are almost never documented, most ers come from the database vendors. The Open Source nature of ever, has enabled several independent developers to write differ- ySQL drivers. ing, Type 2 and Type 4 drivers are the only viable choices for a ication. At an abstract level, the choice between Type 2 and Type to a single issue: Is platform independence critical? By platform e mean that the application can be bundled up into a single jar platform. Type 2 drivers have a hard time with platform indepen- need to package platform-specific libraries with the application. If ess API has not been ported to a client platform, then your appli- n on the platform. On the other hand, Type 2 drivers tend to per- Type 4 drivers. iver type provides only a starting point for making a decision BC driver to use in your application. The decision really comes g the drivers that exist for your database of choice and how they other. Table 11-1 lists the JDBC drivers available for MySQL. Of also able to use any sort of ODBC bridge to talk to MySQL as not recommend it under any circumstance for MySQL develop- SQL JDBC drivers, twz sees the least amount of development and not serve the interests of most programmers these days. The GNU n as mm MySQL), on the other hand, has seen constant develop- rivers for MySQL OSIa License ative ( For drivers released under an OSI-approved c license is referenced. JDBC Version Home Page LGPL 1.x and 2.x no 1.x tjFM/ QPL 2.x mysql/index.xtpCopyright © 2001 O’Reilly & Associates, Inc. ment and is the cho claims signif The JDBC A We have already ent vendors. Fig perspective. In s faces. Under the forms the actual JDBC is divided • java.sql • javax.sql The java.sql classes and inter Optional Packag not have to imp J2SE as of JDK 1 As it turns out, important that i instead be part o ity, the Optional Connecting JDBC represents Connecting to M interface from y database connec 1. Through a JD 2. Using the JD The first method database. Data them is still spot manager connec Figure 0-1. . The JDDRAFT, 8/24/01 most mature of the three JDBC drivers. Not to be outdone, Cau- icant performance benefits over the GNU driver. rchitecture mentioned that JDBC is a set of interfaces implemented by differ- ure 11-1 shows how database access works from an application’s hort, the application simply makes method calls to the JDBC inter- covers, the implementation being used by that application per- database calls. up into two Java packages: package was the original package that contained all of the JDBC faces. JDBC 2.0, however, introduced something called the JDBC e—the javax.sql package—with interfaces that a driver does lement. In fact, the interfaces themselves are not even part of the .3 (though it always has been part of the J2EE). some of the functionality in the JDBC Optional Package is so t has been decided that it is no longer "optional" and should f the J2SE with the release of JDK 1.4. For backwards compatibil- Package classes remain in javax.sql. to MySQL a connection to a database through the Connection interface. ySQL thus requires you to get an instance of the Connection our JDBC driver. JDBC supports two ways of getting access to a tion: BC Data Source BC Driver Manager —the data source—is the preferred method for connecting to a sources come from the Optional Package and thus support for ty. No matter what environment you are in, you can rely on driver tivity. FIGURE14-1.BMP BC architectureCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Data Source Con Data source con nection to any d Context ctx = new DataSource ds = (D Connection conn = The first line in Interface (JNDI*) services. Naming to associate rela example, netwo In order to print of the technical The user simply words, stored al tion could acces Though data so directory, you w store data sourc tory for the dat source is "jdbc/m the directory by Though this sou source got in the matically, puttin ing code: SomeDataSourceC Context ctx = new // configure the DS ctx.bind("jdbc/my We have two bi SourceClass Source interfac vendor—but no * A full discussion o provider (analogou provider. You also you can always pra /java.sun.com/prodnectivity nectivity is very simple. In fact, the following code makes a con- atabase—it is not specific to MySQL: InitialContext(); ataSource)ctx.lookup("jdbc/myds"); ds.getConnection("userid", "password"); this example actually comes from the Java Naming and Directory API. JNDI is an API that provides access to naming and directory and directory services are specialized data stores that enable you ted data under a familiar name. In a Windows environment, for rk printers are stored in Microsoft ActiveDirectory under a name. to the networked color printer, a user does not need to know all details about the printer. Those details are stored in the directory. needs to know the name of the printer. The directory, in other l of the details about the printer in a directory where an applica- s those details by name. urce connectivity does not require a data source be stored in a ill find that a directory is the most common place you will want to e configuration details. As a result, you can simply ask the direc- a source by name. In the above example, the name of the data yds". JNDI enables your application to grab the data source from its name without worrying about all of the configuration details. nds simple enough, you are probably wondering how the data directory in the first place. Someone has to put it there. Program- g the data source in the directory can be as simple as the follow- lass ds = new SomeDataSourceClass(); InitialContext(); by setting configuration attributes ds", ds); ts of "magic" in this code. The first bit of magic is the SomeData- class. In short, it is an implementation of the javax.sql.Data- e. In some cases, this implementation may come from the JDBC t always. In fact, none of the MySQL drivers currently ship with a f JNDI is way beyond the scope of this chapter. You minimally need a JNDI service s to a JDBC driver) and to set some environment variables to support that service need a directory service to talk to. If you do not have access to a directory service, ctice using the file system service provider available on the JNDI home page at http:/ ucts/jndi or use the driver manager approach.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 DataSource im Orion or WebLo implementation Configuring you source impleme want to know implementation on driver manag Though we have matically, you s data source pro mentation shoul configuration fo such a tool. A to enter a new dat tion to the direc shown earlier in Driver Manager One of the f DriverManager sql.Driver cl URLs you provid It tells a Driver provides the Dri JDB sens men acce is th vide diffe The protocol pa Caucho MySQL mysql. The subp MySQL drivers r nection. Optionaplementation. If you are using some sort of application server like gic, then those application servers will provide a DataSource for you that will work with MySQL. r data source depends on the properties demanded by the data ntation class. In most cases, a data source implementation will the JDBC URL and name of the java.sql.Driver interface for the driver. We will cover these two things in the next section er connectivity. been very vague about configuring a JDBC data source program- hould not despair. You should never have to configure a JDBC grammatically. The vendor that provides your data source imple- d provide you with a configuration tool capable for publishing the r a data source to a directory. All application servers come with ol of this sort will prompt you for the values it needs in order to a source in a directory and then allow you to save that configura- tory. Your application can then access the data source by name as the chapter. Connectivity ew implementation classes in the java.sql.package is the class. It maintains a list of implementations of the JDBC java. ass and provides you with database connections based on JDBC e it. A JDBC URL comes in the form of jdbc:protocol:subprotocol. Manager which database engine you wish to connect to and it verManager with enough information to make a connection. C uses the word “driver” in multiple contexts. In the lower-case e, a JDBC driver is the collection of classes that together imple- t all of the JDBC interfaces and provide an application with ss to at least one database. In the upper-case sense, the Driver e class that implements java.sql.Driver. Finally, JDBC pro- s a DriverManager that can be used to keep track of all of the rent Driver implementations. rt of the URL refers to a given JDBC driver. The protocol for the driver, for example, is mysql-caucho while the GNU driver uses rotocol provides the implementation-specific connection data. All equire a host name and database name in order to make a con- lly, they may require a port if your database engine is not run-Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 ning as root. Table 11-2 shows the configuration information for the MySQL JDBC drivers. As you can see from the Caucho ally the preferred Your first task is ager. There are 1. You can spe command lin drivers=com 2. You can ex Class.for MysqlDriv For portability’s some sort of co tion data from will have no dep simply change th Caucho or from Once you have Connection. You the information includes a JDBC parameters: Connection conn = Table 0-2. . Config Driver Imple Caucho com. Driv GNU org. twz twz1 Mysq, the URLs for the GNU driver and twz driver are very different driver. As a general rule, the format of the Caucho driver is actu- format since the you can specify properties separately. to register the driver implementation with the JDBC DriverMan- two key ways to register a driver: cify the name of the drivers you want to have registered on the e of your application using the jdbc.drivers property: java -Djdbc. .caucho.jdbc.mysql.Driver MyAppClass. plicitly load the class in your program by doing a new or a Name(): Class.forName("twz1.jdbc.mysql.jdbc- er").newInstance(). sake, we recommend that you put all configuration information in nfiguration file like a properties file and then load the configura- that configuration file. By taking this approach, your application endencies on MySQL or the JDBC driver you are using. You can e values in the configuration file to move from the GNU driver to MySQL to Oracle. registered your driver, you can then ask the DriverManager for a do this by calling the getConnection() method in the driver with identifying the desired connection. This information minimally URL, user ID, and password. You may optionally include a set of DriverManager.getConnection("jdbc:mysql-caucho://carthage/Web", "someuser", "somepass"); uration Information for MySQL JDBC Drivers mentation URL caucho.jdbc.mysql. er jdbc:mysql-caucho://HOST[:PORT]/DB gjt.mm.mysql.Driver jdbc:mysql://[HOST][:PORT]/ DB[?PROP1=VAL1][&PROP2=VAL2]... .jdbc.mysql.jdbc- lDriver jdbc:z1MySQL://HOST[:PORT]/ DB[?PROP1=VAL1][&PROP2=VAL2]...Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 This code return server on the "someuser" and simplest URL, co just ask that you as part of the JD and Table 11-4 l Table 0-3. . URL Pr Name autoReconnect characterEncodin initialTimeout maxReconnects maxRows password useUnicode user Table 0-4. . URL Pr Name autoReX cacheMode cachePath connectionTimeo db dbmdDB dbmdMaxRows dbmdPasswords a connection associated with the database "Web" on the MySQL machine carthage using the Caucho driver under the user ID authenticated with "somepass". Though the Caucho driver has the nnecting with the other drivers is not much more difficult. They specify connection properties such as the user ID and password BC URL. Table 11-3 lists the URL properties for the GNU driver ists them for the twz driver. operties for the GNU (mm) JDBC Driver Default Description false Causes the driver to attempt a reconnect when the connection dies. g none The Unicode encoding to use when Uni- code is the character set. 2 The initial time between reconnects in sec- onds when autoReconnect is set. 3 The maximum number of times the driver should attempt a reconnect. 0 The maximum number of rows to return for queries. 0 means return all rows. none The password to use in connecting to MySQL false Unicode is the character set to be used for the connection. none The user to use for the MySQL connection. operties for the twz JDBC Driver Default Description true Manages automatic reconnect for data update statements. memory Dictates where query results are cached. . The directory to which result sets are cached if cacheMode is set t "disk". ut 120 The amount of time, in seconds, that a thread will wait on action by a connection before throwing an exception. mysql The MySQL database to which the driver is connected. The MySQL database to use for database meta-data operations. 66536 The maximum number of rows returned by a database meta-data operation. The password to use for database meta- data operations.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 As a result, conn Connection conn = Web?user=someu or for twz: Connection conn = DriverManager.g dbmdUser dbmdXcept debugFile debugRead debugWrite host maxField maxRows moreProperties multipleQuery password port socketTimeout user RSLock Table 0-4. . URL Pr Nameections for these two drivers commonly look like: DriverManager.getConnection("jdbc:mysql://carthage/ ser&password=somepass"); etConnection("jdbc:z1MySQL://carthage/Web?user=someuser&password="somepass"); The user ID to use for database meta-data operations. false Exceptions will be thrown on unsupported database meta-data operations instead of the JDBC-compliant behavior of returning an empty result. none Enables debugging to the specified file. false When debugging is enabled, data read from MySQL is dumped to the debug file. This will severely degrade the performance of the driver. false When debugging is enabled, data written to MySQL is dumped to the debug file. This will severely degrade the performance of the driver. localhost The host machine on which MySQL is run- ning. 65535 The maximum field size for data returned by MySQL. Any extra data is silently trun- cated. Integer.MAX_ VALUE The maximum number of rows that can be returned by a MySQL query. none Tells the driver to look for more properties in the named file. true Will force the caching of the result set allowing multiple queries to be open at once. none The password used to connect to MySQL. 3306 The port on which MySQL is listening. none The time in seconds that a socket connec- tion will block before throwing an excep- tion. none The user used to connect to MySQL. false Enables locking of result sets for a state- ment for use in multiple threads. operties for the twz JDBC Driver Default DescriptionCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Instead of passi second and third part of the URL JDBC, however, properties to get Properties p = new Connection conn; p.put("user", "som p.put("password", p.put("useUnicode p.put("characterEn conn = DriverMan Unfortunately, th inconsistent. It i ever unwieldy it Example 11-1 sh Example 0-1. A Co import java.sql.*; public class Connect { public static void main Connection con = nu try { // here is the JDBC String url = "jdbc: // more on what th Statement stmt; ResultSet rs; // either pass this a // -Djdbc.drivers= // or load it here lik Class.forName("o // here is where the con = DriverMana } catch( SQLExceptio e.printStackTrace( } finally { if( con != null ) { try { con.close() catch( Exceptio } }ng the basic connection properties of "user" and "password" as a argument to getConnection(), GNU and twz instead pass them as . In fact, you can pass any of the properties as part of the URL. has a standard mechanism for passing driver-specific connection Connect(): Properties(); euser"); "somepass"); ", "true"); coding", "UTF-8"); ager.getConnection(url, p); e way in which MySQL supports these optional properties is a bit s thus best to go with the preferred manner for your driver, how- makes the URLs. ows how to make a connection to MySQL using the GNU driver. mplete Sample of Making a JDBC Connection (String argv[]) { ll; URL for this database mysql://athens.imaginary.com/Web?user=someuser&password=somepass"; e Statement and ResultSet classes do later s a property, i.e. org.gjt.mm.mysql.Driver e we are doing in this example rg.gjt.mm.mysql.Driver"); connection is made ger.getConnection(url); n e ) { ); ; } n e ) { }Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 The line con = nection in this e class names are acceptable is be lier, you want to real applications Maintainin Though our foc your application do not write cod the word “porta dependencies, a We discussed h dent, but we d Because both ar ime arguments o elegant since it similar solution are requiring tha they run an app Properties Files A more elegant properties file. P and its subclasse from a text file. F the properties fil istrator. Example } } Example 0-2. The Driver=org.gjt.mm.my URL=jdbc:mysql://athen Example 0-1. A CoDriverManager.getConnection(url) makes the database con- xample. In this case, the JDBC URL and Driver implementation actually hard coded into this application. The only reason this is cause this application is an example driver. As we mentioned ear- get this information from a properties file or the command line in . g Portability Using Properties Files us is on MySQL, it is good Java programming practice to make s completely portable. To most people, portability means that you e that will run on only one platform. In the Java world, however, ble” is a much stronger term. It means no hardware resource nd that means no database dependencies. ow the JDBC URL and Driver name are implementation depen- id not discuss the details of how to avoid hard coding them. e simple strings, you can pass them on the command line as runt- r as parameters to applets. While that solution works, it is hardly requires command line users to remember long command lines. A might be to prompt the user for this information; but again, you t the user remember a JDBC URL and a Java class name each time lication. solution than either of the above solutions would be to use a roperties files are supported by the java.util.ResourceBundle s to enable an application to extract runtime specific information or a JDBC application, you can stick the URL and Driver name in e, leaving the details of the connectivity up to an application admin- 11-2 shows a properties file that provides connection information. SelectResource.properties File with Connection Details for a Connection sql.Driver s.imaginary.com/Web?user=someuser&password=somepass mplete Sample of Making a JDBC Connection (continued)Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Example 11-3 sh We have gotten ple connection JDBC requires a minimum SQL s calls, your appl nately, MySQL i advanced SQL2 Data Sources Re Earlier in the ch cifically, we sta through Java co you go about co ing the data sou erties, you shou source to suppo Example 0-3. Usin import java.sql.*; import java.util.*; public class Connect { public static void main Connection con = nu ResourceBundle bun try { String url = bundle Statement stmt; ResultSet rs; Class.forName(bu // here is where the con = DriverMana } catch( SQLExceptio e.printStackTrace( } finally { if( con != null ) { try { con.close() catch( Exceptio } } } }ows the portable Connect class. rid of anything specific to MySQL or the GNU driver in the sam- code. One important issue still faces portable JDBC developers. ny driver to support SQL2 entry level. This is an ANSI standard for upport. As long as you use SQL2 entry level SQL in your JDBC ication will be 100% portable to other database engines. Fortu- s SQL2 entry level, even though it does not support many of the features. visited apter, we fudged a bit on how data sources were configured. Spe- ted that you can configure a data source either using a tool or de. In most cases you will do so using a tool. The way in which nfiguring a data source is very dependent on the vendor provid- rce. Now that you have a greater appreciation of connection prop- ld have a good idea of what you will need to configure a data rt MySQL. g a Properties File to Maintain Portability (String argv[]) { ll; dle = ResourceBundle.getBundle("SelectResource"); .getString("URL"); ndle.getString("Driver")); connection is made ger.getConnection(url); n e ) { ); ; } n e ) { }Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 In order to bette application, it h J2EE compliant application, it is base. The JSP m InitialContext ctx = DataSource ds = (D Connection = ds.g This looks famil AddressBook" b editing a file call <data-source con class="co name="A url="jdbc: location=" Simple Da The Connect ex MySQL. A datab The simplest for statements. Und create Statemen Example 11-4 sh Example 0-4. Inser import java.sql.*; import java.util.*; public class Insert { // We are inserting into // and TEST_VAL (ch // args[0] is the TEST_ public static void main Connection con = nu ResourceBundle bun try { String url = bundle Statement stmt; Class.forName(bu // here is where the con = DriverMana stmt = con.createS stmt.executeUpdar illustrate the way in which a data source can be set up for an elps to look at a real world application environment. Orion is a application server that is free for non-commercial use. In this serving up Java Server Pages (JSPs) that go against a MySQL data- akes the following JDBC call in order to do its database work: new InitialContext(); ataSource)ctx.lookup("jdbc/AddressBook"); etConnection(); iar so far? Of course, it begs the question: how exactly does "jdbc/ ook get configured? In Orion, you configure the data source by ed data-sources.xml. Here is the entry for "jdbc/AddressBook": nection-driver="org.gjt.mm.mysql.Driver" m.evermind.sql.DriverManagerDataSource" ddressBook" mysql://carthage/Address?user=test&password=test" jdbc/AddressBook"/> tabase Access ample did not do much. It simply showed you how to connect to ase connection is useless unless you actually talk to the database. ms of database access are SELECT, INSERT, UPDATE, and DELETE er the JDBC API, you use your database Connection instance to t instances. A Statement represents any kind of SQL statement. ows how to insert a row into a database using a Statement. ting a Row into MySQL Using a JDBC Statement Object a table that has two columns: TEST_ID (int) ar(55)) ID and args[1] the TEST_VAL (String argv[]) { ll; dle = ResourceBundle.getBundle("SelectResource"); .getString("URL"); ndle.getString("Driver")); connection is made ger.getConnection(url, "user", "pass"); tatement(); te("INSERT INTO TEST (TEST_ID, TEST_VAL) " +Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 If this were a re an INT for the T entry did not ex performing an in creates an empty executeUpdate execution. As its ing the database or instead to de database modific Queries and Queries are a bi tion from the da that represents z has an execute except it return returned by exe the retrieval of m MySQL, howeve tant for you to else’s code writt query. Figure 11 "VALU } catch( SQLExceptio e.printStackTrace( } finally { if( con != null ) { try { con.close() catch( Exceptio } } } } Example 0-5. A Sim import java.sql.*; import java.util.*; public class Select { public static void main Connection con = nu Example 0-4. Inseral application, we would of course verified that the user entered EST_ID, that it was not a duplicate key, and that the TEST_VAL ceed 55 characters. This example nevertheless shows how simple sert is. The createStatement() method does just what it says: it SQL statement associated with the Connection in question. The () method then passes the specified SQL on to the database for name implies, executeUpdate() expects SQL that will be modify- in some way. You can use it to insert new rows as shown earlier, lete rows, update rows, create new tables, or do any other sort of ation. Result Sets t more complicated than updates because queries return informa- tabase in the form of a ResultSet. A ResultSet is an interface ero or more rows matching a database query. A JDBC Statement Query() method that works like the executeUpdate() method— s a ResultSet from the database. Exactly one ResultSet is cuteQuery(), however, you should be aware that JDBC supports ultiple result sets for databases that support multiple result sets. r, does not support multiple result sets. It is nevertheless impor- be aware of this issue in case you are ever looking at someone en against another database engine. Example 11-5 shows a simple -2 shows the data model behind the test table. ES(" + args[0] + ",'" + args[1] + "')"); n e ) { ); ; } n e ) { } ple Query (String argv[]) { ll; ting a Row into MySQL Using a JDBC Statement ObjectCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 The Select app the ResultSet ResultSet does ResourceBundle bun ResourceBundle try { String url = bundle Statement stmt; ResultSet rs; Class.forName(bu // here is where the con = DriverMana stmt = con.createS rs = stmt.executeQ System.out.println while(rs.next()) { int a= rs.getInt(" String str = rs.ge System.out.prin System.out.prin System.out.prin } stmt.close(); } catch( SQLExceptio e.printStackTrace( } finally { if( con != null ) { try { con.close() catch( Exceptio } } } } Figure 0-2. The tes Example 0-5. A Simlication executes the query and then loops through each row in using the next() method. Until the first call to next(), the not point to any row. Each call to next() points the ResultSet dle = .getBundle("SelectResource"); .getString("URL"); ndle.getString("Driver")); connection is made ger.getConnection(url, "user", "pass"); tatement(); uery("SELECT * from TEST ORDER BY TEST_ID"); ("Got results:"); TEST_ID"); tString("TEST_VAL"); t(" key= " + a); t(" str= " + str); t("\n"); n e ) { ); ; } n e ) { } t table from the sample database ple Query (continued) test test_id : LONG test_val : CHAR(255)Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 to the subseque false. You can specify around in the r instances genera support scrollab form of the crea Statement st The first argume be scrollable. B argument relates beyond the scop With a scrollable wards through t trary rows. Like except in the o you attempt to set to a specific ber of rows befo Dealing with a r value in the da retrieve the colu cation, the call t to getString() ods accept eithe name. You shou costs since retri retrieving them b One area of mi NULL. SQL is sp not represent as types as nulls. A from MySQL thr is in that column As its name im NULL. For calls SQL NULL is rea somewhat redunnt row. You are done processing rows when next() returns that you result set is scrollable, meaning that you can move esult set—not just forward on a row-by-row basis. The ResultSet ted by a Statement are scrollable if the statement was created to le result sets. Connection enables this to happen by an alternate teStatement() method: mt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); nt says that any result sets of the newly created statement should y default, a statement’s result sets are not scrollable. The second to an advanced feature of JDBC, updatable result sets, that lies e of this book. result set, you can make calls to previous() to navigate back- he results and absolute() and relative() to move to arbi- next(), previous() moves one row through the result set, pposite direction. The previous() method returns false when move before the first row. Finally absolute() moves the result row, whereas relative() moves the result set a specific num- re or after the current row. ow means getting the values for each of its columns. Whatever the tabase, you can use the getter methods in the ResultSet to mn value as whatever Java datatype you like. In the Select appli- o getInt() returned the TEST_ID column as an int and the call returned the TEST_VAL column as a String. These getter meth- r the column number—starting with column 1—or the column ld, however, avoid retrieving values using a column name at all eving results by column name is many, many times slower than y column number. smatch between Java and MySQL lies in the concept of a SQL ecifically able to represent some data types as null that Java can- null. In particular, Java has no way of representing primitive data s a result, you cannot immediately determine whether a 0 returned ough getInt() really means a 0 is in that column or if no value . JDBC addresses this mismatch through the wasNull() method. plies, wasNull() returns true if the last value fetched was SQL returning a Java object, the value will generally be NULL when a d from the database. In these instances, wasNull() may appear dant. For primitive datatypes, however, a valid value—like 0—Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 may be returned value was NULL Error Hand All JDBC metho thing happens d exception, deal w Each of the JDBC Practically speak whose calling p seen so far, you database connec matically. If you ever, it is a goo connection whe clean up happen close the databa Dynamic So far we have done at compile provided, no on tool that deter Statement clas either a query o information abou which you can ac Meta Data The term meta d about some obje the object. For umns associated the code was w thus not consid ResultSet. Dat database progra JDBC designers ResultSetMetaon a fetch. The wasNull() method gives you a way to see if that in the database. ling and Clean Up d calls can throw SQLException or one of its subclasses if some- uring a database call. Your code should be set up to catch this ith it, and clean up any database resources that have been allocated. classes mentioned so far has a close() method associated with it. ing, however, you only really need to make sure you close things rocess might remain open for a while. In the examples we have only really need to close your database connections. Closing the tion closes any statements and result sets associated with it auto- intend to leave a connection open for any period of time, how- d idea to go ahead and close the statements you create using that n you finish with them. In the JDBC examples you have seen, this s in a finally clause. You do this since you want to make sure to se connection no matter what happens. Database Access dealt with applications where you know exactly what needs to be time. If this were the only kind of database support that JDBC e could ever write tools like the mysql interactive command line mines SQL calls at runtime and executes them. The JDBC s provides the execute() method for executing SQL that may be r an update. Additionally, ResultSet instances provide runtime t themselves in the form of an interface called ResultSetMetaData cess via the getMetaData() call in the ResultSet. ata sounds officious, but it is really nothing other than extra data ct that would otherwise waste resources if it were actually kept in example, simple applications do not need the name of the col- with a ResultSet—the programmer probably knew that when ritten. Embedding this extra information in the ResultSet class is ered by JDBC’s designers to be core to the functionality of a a such as the column names, however, is very important to some mmers—especially those writing dynamic database access. The provide access to this extra information—the meta data—via the Data interface. This class specifically provides:Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 • The number • Whether NUL • The label to • The name fo • The source t • The datatype Example 11-6 sh mysql that accep rest of the code rest of the exam Example 0-6. An A import java.sql.*; public class Exec { public static void main Connection con = nu String sql = ""; for(int i=0; i<args.len sql = sql + args[i]; if( i < args.length - sql = sql + " "; } } System.out.println(" try { Class.forName("c String url = "jdbc: con = DriverMana Statement s = con if( s.execute(sql) ) ResultSet r = s.g ResultSetMetaD int cols = meta.g int rownum = 0 while( r.next() ) rownum++; System.out.pr for(int i=0; i< System.out + } System.out.pr } } of columns in a result set L is a valid value for a column use for a column header r a given column able for a given column of a given column ows some of the source code from a command line tool like ts arbitrary user input and sends it to MySQL for execution. The for this example can be found at the O’Reilly Web site with the ples from this book. pplication for Executing Dynamic SQL (String args[]) { ll; gth; i++) { 1 ) { Executing: " + sql); om.caucho.jdbc.mysql.Driver").newInstance(); mysql-caucho://athens.imaginary.com/TEST"; ger.getConnection(url, "test", "test"); .createStatement(); { etResultSet(); ata meta = r.getMetaData(); etColumnCount(); ; { intln("Row: " + rownum); cols; i++) { .print(meta.getColumnLabel(i+1) + ": " r.getObject(i+1) + ", "); intln("");Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Each result set p method. In the many columns a well as the nam for our result se getColumnLabe Processing D The concept intr not know wheth the SQL call thr statement return it returns true, getResultSet cessing. If, on t modification, yo modified by the A Guest B You have proba Chapter 6, howe We have packag knowledge we h a Web site in a d lets are not in th else { System.out.prin } s.close(); con.close(); } catch( Exception e ) e.printStackTrace( } finally { if( con != null ) { try { con.close() catch( SQLExc } } } } Example 0-6. An Arovides a ResultSetMetaData instance via the getMetaData() case of dynamic database access, we need to find out the how re in a result set so that we are certain to retrieve each column as es of each of the columns for display to the user. The meta data t provides all of this information via the getColumnCount() and l() methods. ynamic SQL oduced in Example 11-6 is the dynamic SQL call. Because we do er we will be processing a query or an update, we need to pass ough the execute() method. This method returns true if the ed a result set or false if none was produced. In the example, if the application gets the returned ResultSet through a call to (). The application can then go on to do normal result set pro- he other hand, the statement performed some sort of database u can call getUpdateCount() to find out how many rows were statement. ook Servlet bly heard quite a bit of talk about Java applets. We discussed in ver, how doing database access in the client is a really bad idea. ed with the examples in this book a servlet that uses the JDBC ave discussed in this chapter to store the comments from visitors to atabase and then display the comments in the database. While serv- emselves part of the three-tier solution we discussed in Chapter 6, tln(s.getUpdateCount() + " rows affected."); { ); ; } eption e ) { } pplication for Executing Dynamic SQLCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 this example sho example, all you dles HTTP POST code is either sim chapter. You ca guestbook.shtml.uld provide a useful example of how JDBC can be used. For this need to know about servlets is that the doPost() method han- events and doGet() handles HTTP GET events. The rest of the ple Java code or an illustration of the database concepts from this n see the servlet in action at © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Copyright © 2001 O’Reilly & Associates, Inc.