Blakes 21 Days Chapter 18 Document
Day 18, Accessing Databases with JDBC 4.2 and Derby
- Top
- 503 Almost all Java programs deal with data in some way.
- So far you have used primitive types, objects, arrays, hash maps, and other data structures.
- 503 Today, you work with data in a more sophisticated way by exploring Java Database Connectivity (JDBC),
- a class library that connects Java programs to relational databases.
- 503 Java includes Java DB, a compact relational database that makes it easier than ever to incorporate a database into your applications.
- Java DB is Oracle's name for Apache Derby, an open source database maintained by the Apache Software Foundation.
- 503 Today, you explore JDBC in the following ways:
- Using JDBC drivers to work with different relational databases
- Accessing a database with Structured Query Language (SQL)
- Reading records from a database using SQL and JDBC
- Adding records to a database using SQL and JDBC
- Creating a new Java DB database and reading its records
Java Database Connectivity
- Top
- 504 Java Database Connectivity (JDBC) is a set of classes that can be used to develop client/server applications
- that work with databases developed by Microsoft, Sybase, Oracle, IBM, and other sources.
- 504 With JDBC, you can use the same methods and classes in Java programs to read and write records and perform other kinds of database access.
- A class called a driver acts as a bridge to the database source.
- There are drivers for each of the popular databases.
- 504 Client/server software connects a user of information with a provider of that information, and it's one of the most common forms of programming.
- You use it every time you use the Web:
- A web browser client requests pages, image files, and other documents using a uniform resource locator (URL).
- Web servers provide the requested information, if it can be found, for the client.
- 504 One of the biggest obstacles faced by database programmers is the wide variety of database formats in use, each with its own proprietary method of accessing data.
- Top
- 504 To simplify using relational database programs, a standard language called Structured Query Language (SQL) was developed.
- This language supplants the need to learn different database-querying languages for each database format.
- Java DB supports SQL.
- 504 In database programming, a request for records in a database is called a query.
- Using SQL, you can send complex queries to a database and get the records you're looking for in any order you specify.
- Top
- 504 Consider the example of a database programmer at a student loan company
- who has been asked to prepare a report on the most delinquent loan recipients.
- The programmer could use SQL to query a database for all records in which the last payment was more than 180 days ago
- and the amount due is more than $0.00.
- SQL also can be used to control the order in which records are returned,
- so the programmer can get the records in the order of
- Social Security number, recipient name, amount owed, or another field in the loan database.
- 504 All this is possible with SQL.
- The programmer doesn't need any of the proprietary languages associated with popular database formats.
- Top
- 504 CAUTION: SQL is supported by many database tools,
- so in theory, you should be able to use the same SQL commands for each database tool that supports the language.
- However, you will still need to learn the idiosyncrasies of a specific database tool when accessing it thru SQL.
- 505 SQL is the industry-standard approach to accessing relational databases.
- JDBC supports SQL, enabling developers to use a wide range of database formats without knowing the specifics of the underlying database.
- JDBC also supports the use of database queries specific to a database format.
- 505 The JDBC class library's approach to access databases with SQL is comparable to existing database-development techniques,
- so interacting with a SQL database by using JDBC isn't much different from using traditional database tools.
- Java programmers who already have some database experience can hit the ground running with JDBC.
- Top
- 505 The JDBC libary includes classes for each of the tasks commonly associated with database usage:
- Making a connection to a database
- Creating a statement using SQL.
- Executing that SQL query in the database
- Viewing the resulting records
- 505 These JDBC classes all are part of the java.sql package.
Database Drivers
- Top
- 505 Java programs that use JDBC classes can follow the familiar programming model of issuing SQL statements and processing the resulting data.
- The format of the database and the platform it was prepared on don't matter.
- 505 This platform and database independence is made possible by a driver manager.
- The classes of the JDBC library are largely dependent on driver managers, which keep track of the drivers required to access database records.
- You need a different driver for each database format that's used in a program, and sometimes you might need several drivers for versions of the same format.
- Java DB includes its own driver.
- 505 JDBC also includes a driver that bridges JDBC and another database-connectivity standard, ODBC.
Examining a Database
- Top
- 505 NetBeans has extensive support for database programming.
- Before you begin writing code, you can use it to connect to a database, learn about the tables it contains, and see the data in those tables.
- 505 To connect to a Java DB database, first you must start the database server.
- 506 In the Projects pane, click the Services tab to bring it to the front, as shown in Figure 18.1.
- The Databases item includes a Java DB item.
- Right-click it and choose Start Server.
Figure 18.1 - Starting the Java DB database server. - goes here
- Top
- 506 The first time you start a Java DB server in NetBeans, it might fail with a security error.
- In that circumstance, NetBeans displays a balloon dialog reporting a Security Manager Problem (Figure 18.2).
Figure 18.2 - Dealing with a Security Manager error. - goes here
- Top
- 506 There are no significant security risks when running Java DB to develop and test JDBC applications in this chapter.
- Click the Disable Security Manager button, then start Java DB again in the Services tab of the Project pane.
- Right-click Java DB and choose Start Server.
- 506 When Java DB launches after any security issues are resolved, it launches and displays a few lines of text to indicate what it's doing.
- This output is shown in Figure 18.3
- 506 This database server calls itself the Apache Derby Network Server, a reflection of the fact that Oracle's Java DB is an implementation of Derby.
Figure 18.3 - Launching a Java DB server with NetBeans. - goes here
- Top
- 507 The server's output indicates that the server is running on port 1527 and is ready to take connections.
- Keep this window open so that you can monitor the server while it runs.
- 507 In the Services pane under Java DB is a sample database named sample.
- Connect to this database by right-clicking sample and choosing Connect.
- 507 An item in the Services pane changes from a broken icon into an unbroken one:
- jdbc:derby://localhost:1527/sample.
- 507 This is an active connection to the database.
- Expand this item, and then expand APP, Tables, and CUSTOMER.
- A list of fields in the CUSTOMER table appears, as shown in Figure 18.4
Figure 18.4 - Examining tables in a Java DB database. - goes here
- Top
- 507 You can view the records in this table by right-clicking CUSTOMER and choosing View Data.
- Two things appear in other panes on NetBeans.
- 508 A SQL command appears where the source code editor normally appears:
- select * from APP.CUSTOMER
- 508 This command, which is called a SQL query, selects all fields from APP.CUSTOMER.
- The asterisk character * could be replaced with the name of one or more fields, separated by commas.
- 508 Another pane displays the result of this command: all the data in this table, organized into rows and columns.
- Each column is a field, and each row is a record in the table.
- 508 Figure 18.5 shows the contents of the CUSTOMER table.
- This is the table you'll be writing Java code to access.
Figure 18.5 - Displaying database records in a table. - goes here
Reading Records from a Database
- Top
- 508 Your first project today is a Java application that connects to a sample Java DB database included with NetBeans and that reads records from a table.
- 508 Working with a database in a Java program is relatively easy if you are conversant with SQL.
- 508 The first task in a JDBC program is to load the driver (or drivers) that will be used to connect to a data source.
- A driver is loaded with the Class.forName(String) method.
- Class, part of the java.lang package, can be used to load classes into the Java Virtual Machine (JVM).
- The forName(String) method loads the class named by the specified string.
- This method can throw a ClassNotFoundException.
- 508 Programs that use Java DB can use org.apache.derby.jdbc.ClientDriver, a driver included with the database.
- Loading this class into the JVM requires the following statement:
- Class.forName("org.apache.derby.jdbc.ClientDriver");
- 509 After the driver has been loaded, you can establish a connection to the data source by using the DriverManager class in the java.sql package.
- Top
- 509 The getConnection(String, String, String) method of DriverManager can be used to set up the connection.
- It returns a reference to a Connection object representing an active data connection.
- 509 This method has three arguments:
- A string identifying the data source and the type of database connectivity used to reach it
- A username
- A password
- 509 The last two items are needed only if the data source is secured with a username and password.
- If it isn't, these arguments can be null strings("").
- 509 Here's the string to use when connecting to the sample database:
- jdbc:derby://localhost:1527/sample
- 509 You've already seen this string in the Services tab of the Projects pane, where it is an item that represents a database connection.
- 509 This string identifies the type of database (jdbc:derby:), the host and port of the database server (localhost:1527), and the name of the database (sample).
- Note the two slash characters (//) after the database type and the one slash after the host and port.
- 509 The second and third arguments to use are app and APP, capitalized as shown.
- They're the username and password.
- 509 The following statement could be used to connect to a database called payroll with a username of doc and a password of 1rover1:
Connection payday = DriverManager.getConnection(
"jdbc:derby://localhost:1527/sample",
"doc", "1rover1");
- Top
- 509 After you have a connection, you can reuse it each time you want to retrieve information from or store information to that connection's data source.
- 509 The getConnection() method and all others called on a data source throw SQLException errors if something goes wrong as the data source is being used.
- SQL has its own error messages, and they are passed along as part of the SQLException objects.
- 510 TIP: NetBeans shows the information required to connect to a database, including the driver class, database connection string, username and password.
- Right-click the database connection, such as jdbc:derby://localhost:1527/sample, and choose Properties from the pop-up menu.
- A dialog containing the class and other information about the connection appears.
- Top
- 510 A SQL statement is represented to Java by a Statement object.
- Statement is an interface, so it can't be instantiated directly.
- However, an object that implements the interface
- is returned by the createStatement() method of a Connection object, as in the following example:
Statement lookSee = payday.createStatement();
- 510 After you have a Statement object, you can use it to conduct a SQL query by calling the object's executeQuery(String) method.
- The String argument should be a SQL query that follows the syntax of that language.
- 510 CAUTION: It's beyond the scope of today's lesson to teach SQL, a rich data-retrieval and storage language
- that has its own new book from this publisher:
- Sams Teach Yourself SQL in 24 Hours, 6th Edition, by Ryan Stephens, Arie D. Jones, and Ron Plew (ISBN: 0-672-33759-2).
- Although you need to learn SQL to do extensive work with it, much of the language is easy to pick up from any examples you can find, such as those you will work with today.
- Top
- 510 The following is an example of a SQL query that could be used on the sample database:
select NAME, CITY from APP>CUSTOMER where (STATE = 'FL')
order by CITY;
- 510 This SQL query retrieves several fields for each record in the database for which the STATE field equals "FL".
- The records returned are sorted according to their CITY field.
- The lowercase parts of the command are SQL keywords.
- The uppercase parts are aspects of the table.
- 510 The following Java statement executes that query on a Statement object named looksee:
ResultSet set = looksee.executeQuery(
"select NAME. CITY from APP.CUSTOMER "
+ " WHERE (STATE = 'FL') order by CITY";
);
- 511 Although SQL queries end with a semicolon character (;), one is not needed in the argument to executeQuery().
- 511 If the SQL query has been phrased correctly,
- the executeQuery() method returns a ResultSet object holding all the records that have been retrieved from the data source.
- 511 NOTE: To add records to a database instead of retrieving them, you should call the statement's executeUpdate() method.
- You'll work with this method later.
- Top
- 511 When a ResultSet is returned from executeQuery(), it is positioned at the first record that has been retrieved.
- The following methods of ResultSet can be used to pull information from the current record.
- getDate(String) returns the Date value stored in the specified field name (using the Date class in the java.sql package, not java.util.Date).
- getDouble(String) returns the double value stored in the specified field name.
- getFloat(String) returns the float value stored in the specified field.
- getInt(String) returns the int value in the field.
- getLong(String) returns the long value in the field.
- getString(String) returns the String value in the field.
- 511 There are just the simplest methods available in the ResultSet interface.
- The methods you should use depends on the form that the field data takes in the database.
- But methods such as getString() and getInt() can be more flexible in the information they retrieve from a record.
- 511 You also can use an integer as the argument to any of these methods, such as getString(5), instead of a string.
- The integer indicates which field to retrieve (1 for the first field, 2 for the second field, and so on).
- Top
- 511 A SQLException is thrown if a database error occurs as you try to retrieve information from a resultset.
- You can call this exception's getSQLState() and getErrorCode() methods to learn more about the error.
- 511 After you have pulled the information you need from a record, you can move to the next record by calling the next() method of the ResultSet object.
- This method returns a false Boolean value when it tries to move past the end of a resultset
- 512 Normally, you can move thru a resultset once from start to finish, after which you can't retrieve its contents again.
- 512 When you're finished using a connection to a data source, you can close it by calling the connection's close() method with no arguments.
- Top
- 512 Listing 18.1 presents the CustomerReporter application, which uses the Java DB driver and a SQL statement to retrieve records from a table in the sample database.
- Four fields are retrieved from each record indicated by the SQL statement:
- TABLEID, TABLENAME, TABLETYPE, and SCHEMAID.
- The resultset is sorted according to the TABLENAME field, and these fields are displayed.
- 512 Before creating this application, you must add the JAVADB library to the project in NetBeans:
- Click the Projects tab in the Projects pane to bring it to the front.
- Scroll down to the bottom of the pane and right-click the Libraries folder.
- Click Add Library from the pop-up menu that appears. The Add Library dialog opens.
- Choose JavaDB under Available Libraries and click Add Library.
- 512 The library now appears in the Library folder.
- Three new JAR files will appear in the Libraries item in the Project pane: derby.jar, derbyclient.jar and derbynet.jar.
- The driver necessary to access the sample database on the Java DB server will be available to the CustomerReporter application.
First Program - Listing 18.1
- Top
- 512 Create the CustomerReporter class in the com.java21days package in NetBeans with the source code of the listing.
Listing 18.1 - The Full Text of CustomerReporter.java
- Top
- page 512 - 513
package com.java21days;
import javax.sql.*;
public class CustomerReporter {
public static void main(String[] arguments) {
String data = "jdbc:derby://localhost:1527/sample";
try (
Connection conn = DriverManager.getConnection(
data, "app", "APP");
Statement st = conn.createStatement()) {
Class.forName("org.apache.derby.jdbc.ClientDriver");
ResultSet rec = at.executeQuery(
"select CUSTOMER_ID, NAME, CITY, STATE " +
"from APP>CUSTOMER " +
"order by CUSTOMER_ID");
while (rec.next()) {
System.out.println("CUSTOMER_ID:\t"
+ rec.getString(1));
System.out.println("NAME:\t" + rec.getString(2));
System.out.println("CITY:\t" + rec.getString(3));
System.out.println("STATE:\t" + rec.getString(4));
System.out.println();
}
st.close();
} catch (SQLExecption e) {
System.out.println("SQL Error: " + s.toString() + " "
+ a.getErrorCode() + " " + s.getSQLState());
} catch (Execption e) {
System.out.println("Error: " + e.toString()
+ e.getMessage());
}
}
}
An Explanation
- Top
- 513 When this program is run with the starting data from the sample database, part of the output is shown in Figure 18.6.
Figure 18.6 - Reading records from a Java DB database. - goes here
- Top
- 514 CAUTION: If you run this application and it fails with an SQL error stating
- "Connection authentication failure occurred. Reason: Userid or password invalid," it may be due to a bug in NetBeans.
- Change the password used as the final argument to the getConnection() method in Lines 9-10 from "APP" to "app"
- and run the program again to see if it resolves the problem.
Writing Records to a Database
- Top
- 514 In the CustomerReporter application, you retrieved data from a database using a SQL statement prepared as a string:
select CUSTOMER_ID, NAME, CITY, STATE from APP.CUSTOMER'FL')
order by CUSTOMER_ID;
- 514 This is a common way to use SQL.
- You could write a program that asks a user to enter a SQL query and then displays the result.
- (However, this would be a terrible idea, because SQL queries can be used to delete records, tables, and even entire databases.)
- Top
- 514 The java.sql package also supports another way to create a SQL statement: a prepared statement.
- 514 A prepared statement, which is represented by the PrepareStatement class, is a SQL statement that is compiled before it is executed.
- This enables the statement to return data more quickly and is a better choice if you are executing a SQL statement repeatedly in the same program.
- 514 To create a prepared statement, call a connection's prepareStatement(String) method with a string that indicates the structure of the SQL statement.
- 514 To indicate the structure, you write a SQL statement in which parameters have been replaced with question marks.
- 514 Here's an example for a connection object called cc:
PreparedStatement ps = cc.preparedStatement(
" select * from APP.CUSTOMER where (ZIP=?) *
+ order by NAME);
- I need to verify that I typed this right.
- 514 Here's another examplewith more than one question mark:
PreparedStatement ps = cc.preparedStatement(
" insert into APP.CUSTOMER " +
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)*);
- I need to verify that I typed this right.
- Top
- 515 The question marks in these SQL statements are placeholders for data.
- Before you can execute the statement, you must put data in each of these places using one of the methods of the PrepareStatement class.
- 515 To put data into a prepared statement, you must call a method with the position of the placeholder followed by the data to insert.
- 515 For example, to put the string "Acme Corp." in the fifth field of the prepared statement, call the setString(int, String) method:
ps.setString(5, "Acme Corp");
- 515 The first argument indicates the placeholder's position, numbered from left to right.
- The first question mark is 1, the second is 2, and so on.
- 515 The second argument is the data to put in the statement at that position.
- Top
- 515 The following methods are available:
- setAsciiStream(int, InputStream, int) - At the position indicated by the first argument, insert the specified InputStream, which represents a stream of ASCII characters. The third argument indicates how many bytes from the input stream to insert.
- setBinaryStream(int, InputStream, int) - At the position indicated by the first argument, insert the specified InputStream, which represents a stream of bytes. The third argument indicates how many bytes to insert from the stream.
- setCharacterStream(int, Reader, int) - At the position indicated by the first argument, insert the specified Reader, which represents a character stream. The third argument indicates how many characters to insert from the stream.
- setBoolean(int, boolean) - Inserts a boolean value at the position indicated by the integer.
- setByte(int, byte) - Inserts a byte value at the indicated position.
- setBytes(int, byte[]) - Inserts an array of bytes at the indicated position.
- setDate(int, Date) - Inserts a date object (from the java.sql package) at the indicated position.
- setDouble(int, double) - Inserts a double value at the indicated position.
- setFloat(int, float) - Inserts a float value at the indicated position.
- setInt(int, int) - Inserts an int value at the indicated position.
- setLong(int, long) - Inserts a long value at the indicated position.
- setShort(int, short) - Inserts a short value at the indicated position.
- setString(int, String) - Inserts a String value at the indicated position.
- Top
- 516 There's also a setNull(int, int) method that stores SQL's version of a null (empty) value at the position indicated by the first argument.
- 516 The second argument to setNull() should be a class variable from the Types class in java.sql to indicate what kind of SQL value belongs in that position.
- 516 There are class variables for each of the SQL data types.
- The list, which is not complete, includes some of the most commonly used variables:
- BIGINT, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, SMALLINT, TINYINT, and VARCHAR.
- 516 The following code puts a null CHAR value at the fifth position in a prepared statement called ps:
ps.setNull(5, Types.CHAR);
- 516 The next project demonstrates the use of a prepared statement to add stock quote data to a database.
- Quotes are collected from Yahoo!.
- 516 As a service to people who follow the stock market, Yahoo! offers a Download Spreadsheet link on its main stock quote page for each ticker symbol.
- 516 To see this link, look up a stock quote on Yahoo! or go directly to a page such as this one:
- 516 At the bottom of the page under the Toolbox heading, you can find a Download Data link.
- I don't think that the Toolbox heading exist anymore.
- Here's what the link to Facebook looks like:
- 516 You can click this link to open the file or save it to a folder on your system.
- The file, which is only one line long, contains the stock's price and volume data saved at the last market close.
- Here's an example of what Facebook's data looked like on Sept. 25, 2015:
- "FB",92.77,"9/25/2015","4:00pm",-1.64,95.85,95.8592.06,28961622 reserve
- Top
- 516 The fields in this data, in order, are
- the ticker symbol, closing price, date, time, price change since yesterday's close, daily low, daily high, daily open, and volume.
- 516 The QuoteData application uses each of these fields except one - the time, which isn't particularly useful because it's always the time the market closed.
- 516 The following takes place in the program:
- A stock's ticker symbol is used as a command-line argument.
- A QuoteData object is created with the ticker symbol as an instance variable called ticker.
- The object's retrieveQuote() method is called to download the stock data from Yahoo! and return it as a String.
- The object's storeQuote() method is called with that String as an argument.
- It saves the stock data to a database using JDBC-ODBC connection.
- 517 Before you can run the application, you must have a database table designed to hold these stock quotes.
- 517 You can create a new table for this purpose in the sample database in NetBeans by following these steps:
- In the Service tab of the Projects pane, open the APP item under the jdbc:derby://localhost:1527/sample item.
- Right-click this item's Tables folder and choose Create Table from the pop-up menu. The Create Table dialog opens, as shown in Figure 18.7.
- In the Table Name field, enter STOCKS.
- Click Add column. The Add Column dialog opens.
- In the Name field, enter TICKER.
- In the Type field, choose VARCHAR.
- In the Size field, enter 10.
- Click OK. The new field appears in the dialog.
- Repeat steps 4-8 for fields named PRICE, DATE, CHANGE, LOW, HIGH, PRICEOPEN, and VOLUME. The type and size are always VARCHAR and 10, respectively.
- Click OK. The STOCKS table appears in the Tables folder.
Figure 18.7 - Creating a new database table in NetBeans. - goes here
Second Program - Listing 18.2
- Top
- 518 Now that you have a database table, you can create the QuoteData application, shown in Listing 18.2, to store stock data in a new record of that table.
- Create the class QuoteData in the com.java21days package in NetBeans.
Listing 18.2 - The Full Text of QuoteData.java
package com.java21days;
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;
public class QuoteData {
private String ticker;
public QuoteData(String inTicker) {
ticker = inTicker;
}
private String retrieveQuote() {
StringBuilder builder = new StringBuilder();
try {
URL page = new URL(
"http://quote.yahoo.com/d/quotes.csv?s=" +
ticker + "&f=sl1d1t1c1ohgv&e=.csv");
String line;
URLConnection conn = page.openConnection();
conn.connect();
InputStreamReader in = new InputStreamReader(
conn.getInputStream());
BufferedReader data = new BufferedReader(in);
while ((line = data.readLine()) != null) {
builder.append(line);
builder.append("\n");
}
} catch (MalformedURLException mue) {
System.out.println("Bad URL: " + mue.getMessage());
} catch (IOException ioe) {
System.out.println("IO Error:" + ioe.getMessage());
}
return builder.toString();
}
private void storeQuote(String data) {
StringTokenizer tokens = new StringTokenizer(data, ",");
String[] fields = new String[9];
for (int i = 0; i < fields.length; i++) {
fields[i] = stripQuotes(tokens.nextToken());
}
String datasource = "jdbc:derby://localhost:1527/sample";
try (
Connection conn = DriverManager.getConnection(
datasource, "app", "app")
) {
Class.forName("org.apache.derby.jdbc.ClientDriver");
PreparedStatement prep2 = conn.prepareStatement(
"insert into " +
"APP.STOCKS(TICKER, PRICE, DATE, CHANGE, LOW, " +
"HIGH, PRICEOPEN, VOLUME) " +
"values(?, ?, ?, ?, ?, ?, ?, ?)");
prep2.setString(1, fields[0]);
prep2.setString(2, fields[1]);
prep2.setString(3, fields[2]);
prep2.setString(4, fields[4]);
prep2.setString(5, fields[5]);
prep2.setString(6, fields[6]);
prep2.setString(7, fields[7]);
prep2.setString(8, fields[8]);
prep2.executeUpdate();
prep2.close();
conn.close();
} catch (SQLException sqe) {
System.out.println("SQL Error: " + sqe.getMessage());
} catch (ClassNotFoundException cnfe) {
System.out.println(cnfe.getMessage());
}
}
private String stripQuotes(String input) {
StringBuilder output = new StringBuilder();
for (int i = 0; i < input.length(); i++) {
if (input.charAt(i) != '\"') {
output.append(input.charAt(i));
}
}
return output.toString();
}
public static void main(String[] arguments) {
if (arguments.length < 1) {
System.out.println("Usage: java QuoteData ticker");
System.exit(0);
}
QuoteData qd = new QuoteData(arguments[0]);
String data = qd.retrieveQuote();
qd.storeQuote(data);
}
}
The Explanation
- Top
- 520 Before you run the application, you must set a command-line argument.
- Choose Run, Set Project Configuration, Customize, and then enter the main class QuoteData and the argument of a valid ticker,
- such as FB (Facebook), GOOG (Google) (now may be: GOOGL (Alphabet) ), or PSO (Pearson PLC).
- 520 The application stores the stock data but does not display any output.
- 520 To see that it worked, right-click the STOCKS table in the Services tab and choose View Data.
- The table records are displayed, they should include at least one day's data for the requested stock ticker symbol, as shown in Figure 18.8.
Figure 18.8 - Records in the STOCKS table. - goes here
- Top
- 520 The retrieveQuote() method (lines 15-37) downloads the quote data from Yahoo! abd saves it as a string.
- The techniques used in this method were covered on Day 17, "Communicating Across the Internet."
- 520 The storeQuote() method (lines 39-73) uses the SQL techniques covered in this section.
- 520 The method begins by using the StringTokenizer class to split the quote into a set of tokens,
- using the comma character (,) as the delimiter between each token.
- The tokens then are stored in a String array with nine elements.
- 520 The array contains the same fields as the Yahoo! data in the same order:
- ticker symbol, closing price, date, time, price change, low, high, open, and volume.
- 520 Next, a data connection to the QuoteData data source is created using the Java DB database driver (lines 45-49).
- 520 This connection then is used to create a prepared statement (lines 52-56).
- This statement uses the insert into SQL statement, which causes data to be stored in a database.
- In this case, the database is sample, and the insert into statement refers to the APP.STOCKS table in that database.
- 520 The prepared statement has eight placeholders.
- Only eight are needed, instead of nine, because the application does not use the time field from the Yahoo! data.
- Top
- 521 A series of setString() methods puts the elements of the String array into the prepared statement, in the same order that the fields exist in the database:
- ticker symbol, closing price, date, price change, low, high, open, and volume (lines 57-64).
- 521 Because some fields in the Yahoo! data are dates, floating-point numbers, and integers,
- you might think that it would be better to use setDate(), setFloat(), and setInt() for that data.
- This application stores all the stock data as strings because that's more likely to work regardless of the database software being used.
- Top
- 521 CAUTION: Some databases you could use in Java programs, including Microsoft Access,
- do not support some of these methods when you are using SQL to work with the database, even though they exist in Java.
- If you tryto use an unsupported method, such as setFloat(), a SQLExecption error occurs.
- It's easier to send a database strings and let the database program automatically convert them into the correct format.
- This is likely to be true when you are working with other databases, the level of SQL support varies based on the product and driver involved.
- Top
- 521 After the statement has been prepared and all the placeholders are filled, the statement's executeUpdate() method is called in line 65.
- This either adds the quote data to the database or throws a SQL error.
- 521 The private method stripQuotes() is used to remove quotation marks from Yahoo!'s stock data.
- This method is called in line 43 to take care of three fields that contain extraneous quotes: the ticker symbol, date, and time.
Moving Through ResultSets
- Top
- 521 The default behaior of resultsets permits one trip thru the set using its next() method to retrieve each record.
- 521 By changing how statements and prepared statements are created, you can produce resultsets that support these additional methods:
- afterLast() moves to a place immediately after the last record in the set.
- beforeFirst() moves to a place immediately before the first record in the set.
- first() moves to the first record in the set.
- last() moves to the last record in the set.
- previous() moves to the previous record in the set.
- 522 These actions are possible when the resultset's policies
- have been specified as arguments to a database connection's createStatement() and prepareStatement() methods.
- 522 Normally, createStatement() takes no arguments, as in this example:
Connection payday = DriverManager.getConnection(
"jdbc:derby://localhost:1527/sample", Doc", "1rover1");
Statement lookSee = payday.CreateStatement();
- Top
- 522 For a more flexible resultset, call createStatement() with three integer arguments that set up how it can be used.
- Here's a rewrite of the preceeding statement:
Statement lookSee = payday.CreateStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.CONCUR_READ_ONLY
ResultSet.CLOSE_CURRSORS_AT_COMMIT);
- 522 The same three arguments can be used in the prepareStatement(String, int, int, int) method after the text of the statement.
- 522 The ResultSet class includes other class variable that offer more options in how sets can be read and modified.
Summary
- Top
- 522 Today ypu learned to read and write database records using classes that work with any of the popular relational databases.
- The techniques used to work with Java DB can be used with Microsoft Access, MySQL, and other programs.
- The only thing that needs to be changed is the database driver class and the strings used to create a connection.
- 522 Using Java Database Connectivity (JDBC), you can incorporate existing data-storage solutions into your Java programs.
- 522 You can connect to several different relational databases in your Java programs by using JDBC and Structured Query Language (SQL), a standard language for reading, writing, and managing a database.
Q & A
- Top | page 523
- Q What's the difference between Java DB and more well-known databases such as Access and MySQL ? Which should I use ?
- A Java DB is intended for database applications that have simpler needs Access and comparable databases.
- The entire application takes up under 4MB of space, making it easy to bundle with Java applications that require database connectivity.
- Oracle employs Java DB in several parts of the Java Enterprise Edition,
- which demonstrates that it can deliver strong, reliable performance on important tasks.
Quiz - Questions
- Top | 523 Review today's material by taking this three-question quiz.
- What does a Statement object represent in a database program ?
- A connection to a database
- A database query written in Structured Query Language
- A data source
- Which Java class represents SQL statements that are compiled before they are executed ?
- Statement
- PreparedStatement
- ResultSet
- What does the Class.forName(String) method accomplish ?
- It provides the name of a class.
- It loads a database driver that can be used to access a database.
- It deletes an object.
Answers
- Top | 523 Note A is 1, B is 2, and C is 3
- B. The class, part of the java.sql package, represents a SQL statement.
- B. Because it is compiled, PreparedStatement is a better choice when you need to execute the same SQL query numerous times.
- B. This static method loads a database driver.
Certification Practice
- Top
- 524 The following question is the kind of thing you could expect to be asked on a Java programming certification test. Answer it without looking at today's material or using the Java compiler to test the code.
- The answer is available on the book's website at www.java21days.com
- Given:
public class ArrayClass {
public static ArrayClass newInstance() {
count++;
return new ArrayClass();
}
public static void main(String arguments[]) {
new ArrayClass();
}
int count = -1;
}
- Which line in this program prevents it from compiling successfully ?
- count++;
- return new ArrayClass();
- public static void main(String arguments[]) {
- int count = -1;
Exercise
- Top
- To extend your knowledge of the subjects covered today, try the following exercises:
- Modify the CustomerReporter application to pull fields from another table in APP.
- Write an application that reads and displays records from the Yahoo! stock quote database.