Open Data Access
Open Data Access
As mentioned in Chapter 1, "Microsoft .NET and J2EE Fundamentals," database connectivity APIs are core packages for both .NET and J2EE. The J2EE 1.3 specification promotes the use of JDBC to provide database access. For .NET, ADO.NET is used.
To recap, JDBC is an API that allows Java applications to access fields, records, tables, and stored procedures from any database for which a JDBC driver exists. Although JDBC 3.0 is in its final release, we'll use JDBC 2.0 calls for the chapter's examples. Many vendors have produced JDBC-compatible drivers for a number of popular databases. These include Microsoft SQL Server, many versions of the Oracle database, and the IBM DB2 database.
ADO.NET is the next stage in the evolution of Microsoft's data access strategy ”with an emphasis on providing database access and connectivity for the .NET Framework. In addition to simple database connectivity, ADO.NET has a strong relationship with XML (to enable persistence and transport) and introduces the concept of a DataSet , allowing a disconnected view of data.
ADO.NET uses managed providers to connect to a database. Managed providers are database drivers that expose APIs by using classes that are based on managed code (which is Microsoft Intermediate Language, or MSIL, under the covers). Today, managed providers exist for SQL Server, Oracle 8i, and DB2. In addition, an ADO.NET managed provider can be used to access other drivers that are based on either OLEDB or ODBC.
Creating a Sample Database Table
To see examples of how to connect to a shared database, we'll use a table that simulates incoming orders for the stock-trading scenario that we've used in previous chapters. To keep things simple, the database defines a single table named ORDERTBL, which has the schema shown in Table 7.1.
| Column Name | SQL Data Type |
|---|---|
| ID | BIGINT |
| TYPE | CHAR(1) |
| ACCOUNT_ID | BIGINT |
| TICKER | CHAR(6) |
| QTY | INT |
| PRICE | MONEY |
In this example, ID correlates to the ID of the order. TYPE indicates whether the item is a purchase (indicated by a P ) or a sale (indicated by an S ). ACCOUNT_ID is an account number for the trade. TICKER is the ticker symbol of the stock. QTY is the amount bought or sold. PRICE is the price of the stock at the time of the trade. Based on the examples you've seen so far in the book, imagine how a table of this type could be used to store records of stock trades.
To create this shared database and table, run the SQL script (dbscript.sql) located in the C:\Interoperability\Samples\Resource\Database\SQL directory. With Microsoft SQL Server 2000 installed, this script can be processed by loading and executing this file within the Query Analyzer, which can be accessed from the Microsoft SQL Server program group .
| Tip | During SQL Server installation, it's important to ensure that the SQL Server And Windows authentication mode is selected in the properties dialog box of the SQL Server instance, as shown in Figure 7.1. If the authentication mode is set to Windows Only, a JDBC application won't be able to connect to the database. This is because the Java Virtual Machine, or JVM, doesn't have access to the Windows authentication credentials stored on the machine.  Figure 7.1: Configuring the SQL Server security properties. |
Running this script will create a new database named SharedDB, which has a table named ORDERTBL, the result of which is shown in Figure 7.2. A few sample records have also been included. The script also creates a test user account named testuser. This account is given a default password of StrongPassword and ownership rights of the SharedDB database. This user account is used by the client sample code in this chapter.
Figure 7.2: Processing the dbscript.sql in Query Analyzer.
Connecting to Microsoft SQL Server 2000 Using JDBC
To allow Microsoft SQL Server 2000 to be accessed from a Java application, a number of JDBC drivers are available. Some of these are free, some are commercial, and in the past some have even been packaged with a number of J2EE application servers.
Microsoft's offering is a JDBC driver that's available as a free download through the Microsoft Developer Network (MSDN). The driver is a Type 4 JDBC driver.
| Note | JDBC drivers are categorized into four types. Type 1 and Type 2 drivers require native code to be installed on the client machine. Type 3 drivers do not require native code but require additional security to work over the Internet. Type 4 drivers are preferred because they use the native protocol to access the database without requiring any additional installation. |
Downloading and Installing the Driver
The Microsoft SQL Server 2000 Driver for JDBC (Service Pack 1) can be downloaded by navigating to http://msdn.microsoft.com/downloads/list/sqlserver.asp and selecting the appropriate product.
The installation for the Microsoft Windows operating system is a simple SETUP.EXE. A TAR package is also available for running it on UNIX systems.
Simple JDBC Access
Once the installation has completed, three Java Archive (JAR) files can be found in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib directory. These three JAR files contain the required libraries to connect to an instance of Microsoft SQL Server 2000 by using JDBC.
To see the JDBC driver in action, look at the sample located in the C:\Interoperability\Samples\Resource\Database\Simple\JDBC directory. Stepping through this sample (Client.java), you can examine how the driver works. Because the driver is based on JDBC, you need to import classes from the existing java.sql.* packages:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement;
After the declaration for your main class, you need to load the SQL Server 2000 Driver for JDBC by using the class loader. This ensures that the driver is loaded for the following JDBC calls:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); If you plan to use this sample code with an alternative JDBC driver and/or database, this line will need to reference the driver that you want to use.
Once the class is loaded, a connection string is built to specify how to connect to your database. This connection string will determine the parameters to use to find and connect to the instance of SQL Server. The format of a default connection string follows , and the list explains the different components in the string.
jdbc:microsoft:sqlserver:// server : port; DatabaseName= db
-
server The machine name where SQL is installed.
-
port The IP port that the driver should use to connect with SQL Server. If defaults have been accepted during the SQL Server installation, this port should be 1433.
-
db The name of the database to access.
The username and password that should be used to access the server and the database are supplied separately when the database connection is created.
This sample uses a connection string that connects to a local instance of SQL Server 2000, via a user named testuser and a password of StrongPassword . The DriverManager.getConnection method is used to create the connection:
Connection conn = DriverManager.getConnection("jdbc:microsoft:" +"sqlserver://localhost:1433;DatabaseName=SharedDB", "testuser","StrongPassword"); After the connection to the database has been successfully established, a SELECT statement is issued to the SQL Server instance:
Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM ORDERTBL"); This query requests all records from the order table. The query will execute and an object of type java.sql.ResultSet is returned. You can then iterate through the ResultSet to display the data from the query.
while (rs.next()) { System.out.println(rs.getString("ID") +"\t"+rs.getString("TYPE") +"\t"+rs.getString("ACCOUNT_ID") +"\t"+rs.getString("TICKER") +"\t"+rs.getString("QTY") +"\t"+rs.getString("PRICE")); } This code displays the values for each of the six columns defined within the table, simply by using the getString method from the ResultSet . (We'll perform more complex calls later in the chapter.) To complete this JDBC sample, we wrap the code in a try catch block. Some of the JDBC calls throw explicit exceptions that are caught by trapping for a general exception.
Build and run the code by entering ant run at a command prompt in the Simple\JDBC directory. As you saw, this sample accesses the Microsoft SQL Server 2000 Driver for JDBC at run time. The build script assumes the libraries (JAR files) are located in the default installation directory (C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib). If this isn't the case, you should modify the C:\Interoperability\Samples\Config\sqljdbc.properties file to reflect the correct directory.
Upon successfully calling the database, a list of records will be returned:
1000 P 47238 CONT 400 12.7700 1001 P 93083 CONT 200 12.7800 1002 S 39223 NWND 1000 50.1200 1003 P 29080 WOOD 150 14.4500 1004 S 37973 FTHC 10000 3.4500 1005 P 32279 WOOD 800 14.7900 1006 P 38084 LUCP 5000 46.4400 1007 S 39397 COHO 200 21.6000 1008 S 71129 FABR 950 15.1100 1009 P 38293 TRRE 250 9.3500 1010 P 38293 COHO 250 21.5800
Connecting to Microsoft SQL Server 2000 Using ADO.NET
Now that we've looked at how the JDBC driver can be used to access the sample database and display some simple records, let's look at the same functionality using ADO.NET.
Simple ADO.NET Access
Again, with the JDBC driver, the best way to show simple access to a database by using ADO.NET is with a sample. The code from the previous sample, but this time using ADO.NET rather than JDBC, can be found in the C:\Interoperability\Samples\Resource\Database\Simple\ADO.NET directory.
Most of the APIs required to access the SQL Managed Provider for ADO.NET reside within the System.Data.Sql namespace. This namespace is imported at the start of the sample:
using System.Data.SqlClient;
The connection to the database is defined by using the SqlConnection class. A connection string is built that includes the machine, username, password, and database information. This is similar to how the JDBC connection string was built, but it uses different parameters. The connection is opened with the Open method:
SqlConnection conn = new SqlConnection("Data Source=localhost; User ID=testuser; " + "Password=StrongPassword; Initial Catalog=SharedDB"); conn.Open(); To issue a command to the database, a SqlCommand is constructed that contains the select statement that will be executed. The results from the select statement will be returned as a SqlDataReader object:
SqlCommand cmd = new SqlCommand("SELECT * FROM ORDERTBL", conn); SqlDataReader reader = cmd.ExecuteReader(); As you did with the Java ResultSet , to extract the fields from this reader, you index them with the GetValue method. This will return the values in a format that will allow them to be written to the console:
while (reader.Read()) { Console.WriteLine(reader.GetValue(0)+"\t" +reader.GetValue(1)+"\t" +reader.GetValue(2)+"\t" +reader.GetValue(3)+"\t" +reader.GetValue(4)+"\t" +reader.GetValue(5)); } As you continue to look at other samples in this chapter, you'll see how to use the actual types of the fields instead of getting a default value from them.
Build and run the ADO.NET code by entering nant run at a command prompt in the Simple\ADO.NET directory. Upon successfully calling the database, a list of records matching the ones that were observed with the JDBC sample will be returned.
EAN: 2147483647
Pages: 132