 Home ::
The Java Grinder ::
Coffee Talk ::
Newest Nodes ::
LibraryUser Options: Guest User :: Log Out |
|
|
|
|
 JDBC What Is JDBC?JDBC is basically a set of classes that allow for interaction with various database systems through a standard Java interface. That standard interface utilizes Structured Query Language (SQL) in order to interact with that database. The classes used for JDBC are found within the package java.sql, which comes standard with any JDK version 1.1 or later. If you have JDK 1.2 or later, you actually get JDBC 2.0, which has some extra functionality. I'll discuss a little more about this later but, for the most part, this tutorial will cover JDBC 1.0 (I'll explain why later).How Does JDBC Work?JDBC acts as a "middle layer" between your Java application and the database driver. There is a DriverManager class that acts as the "middleman." From that DriverManager, you can load the proper driver for whatever type of database you want to use and then interact with that database through that DriverManager (you actually interact through a Connection object, but that object is retrieved from the DriverManager - more about this in a bit). This middle layer allows you to interact with numerous types of databases (Oracle, ODBC, Sybase, etc...) all in the same fashion. Therefore, if you need to make a change in what database you're using, you need only to change the driver that you're loading. The rest of the database interaction is identical.There is currently a new version of JDBC, JDBC 2.0, which provides some excellent flexibility (I'll hit some of the highlights toward the end of this tutorial). Unfortunately, database developers are not required to create JDBC 2.0 compliant drivers. Most database systems only provide JDBC 1.0 solutions. Fortunately, the two are backward compatible so, even though you most like already have JDBC 2.0 (it comes with JDK 1.2+), you can use it as if it was JDBC 1.0 and simply ignore the extra functionality. How Do I Use JDBC?First, let me give a textual representation of a table that I'll use for examples.
Now, here's a simple example program that displays the contents of this database table. I'll discuss each part of this program in detail afterward. 1: import java.sql.*;
2:
3: public static void main(java.lang.String[] args)
4: {
5: // First, we need to load the driver
6: try
7: {
8: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
9: }
10: catch (ClassNotFoundException e)
11: {
12: System.out.println("Unable to load driver: " + e);
13: }
14:
15: // Now, we can open a connection to the database and begin w+
orking
16: try
17: {
18: // Get a DB connection from the DriverManager
19: Connection dbConnect = DriverManager.getConnection("jdbc+
:odbc:Tutorial", "", "");
20:
21: // Let's create a simple SQL statement to get all of the+
22: // data from our table
23: Statement sqlStmt = dbConnect.createStatement();
24: ResultSet tableData = sqlStmt.executeQuery("SELECT * FRO+
M JUNKIES");
25:
26: // Display the contents
27: while ( tableData.next() )
28: {
29: String nick = tableData.getString("Nickname");
30: int nodeID = tableData.getInt("HomenodeID");
31:
32: System.out.println(nick + " has node " + nodeID);
33: }
34:
35: // Close all database resources to make sure they're fre+
e
36: tableData.close();
37: sqlStmt.close();
38: dbConnect.close();
39: }
40: catch (SQLException e)
41: {
42: System.out.println("An error occured while interacting w+
ith DB: " + e);
43: }
44: }
45:
46: This gives the following output:
47:
48: crazyinsomniac has node 393
49: Moon has node 327
50: Sherlock has node 575
Now, let's look at each piece of this code more carefully and dissect what is taking place.
Lines 5 - 13 1: // First, we need to load the driver
2: try
3: {
4: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
5: }
6: catch (ClassNotFoundException e)
7: {
8: System.out.println("Unable to load driver: " + e);
9: }
This short block loads the proper driver for the database we intend to use. In this case, I used Microsoft Access to create the database, which uses ODBC. In order to handle ODBC databases, there is a JDBC-ODBC "bridge" which acts as the driver. That's what I'm loading here. There are a number of different drivers you might need (depending upon the type of database you wish to use and how you want to use it). You can check here for more information about drivers.
We're using the method Class.forName in order to load the driver. Notice that this method takes a String as a parameter. It is fairly common practice to take that driver name and put it into a properties file so that, in the case you need to change databases, the code can remain unchanged. All you need to do is change the properties file. Lines 18-19 1: // Get a DB connection from the DriverManager
2: Connection dbConnect = DriverManager.getConnection("jdbc:odbc:Tu+
torial", "", "");
This snippet gets a Connection object from the DriverManager. This is the connection object that I alluded to earlier. All interaction with the database is done through this object. It will allow us to perform queries and updates to our databse. The parameters might look a little funny, so let's look at those.
The first parameter is the database's URL. In this case, since I'm using a ODBC database, I had to set up an ODBC source and gave it the name "Tutorial." Therefore, my URL became "jdbc:odbc:Tutorial." Look at your database documentation to determine what your URL will be. The second and third parameters are the username and password fields, respectively. If, as in this case, your database doesn't require a username or password, you can simply send empty strings. Lines 21-24 1: // Let's create a simple SQL statement to get all of the
2: // data from our table
3: Statement sqlStmt = dbConnect.createStatement();
4: ResultSet tableData = sqlStmt.executeQuery("SELECT * FROM JUNKIE+
S");
In these lines, we're generating a query and retrieving that data from the database. Notice that I first get a Statement object from the Connection object. A Statement object is basically used to perform a single SQL statement. In this case, I use the executeQuery method to execute the simple "select" statement shown. You can also use executeUpdate (which returns the number of rows updated) to perform a SQL statement that does not return values and execute a statement regardless of its type (for example, if you're performing a query entered by a user, you might not know ahead of time the type of query). Using this method requires a little more work to determine what happened, but it can be done. (More about this later.)
Also notice the type of the return value: ResultSet. A ResultSet is a fancy container that holds the results of the query. A ResultSet works much like an iterator in many respects. Look at the next section to see how it is used. Lines 26-33 1: // Display the contents
2: while ( tableData.next() )
3: {
4: String nick = tableData.getString("Nickname");
5: int nodeID = tableData.getInt("HomenodeID");
6:
7: System.out.println(nick + " has node " + nodeID);
8: }
In this section, we're going to navigate through the ResultSet object and display all of the data to the screen.
Note one key difference between a ResultSet and an iterator. In line 27, I refer to tableData.next() without ever calling a hasNext() method. That's because, when you get a ResultSet, you are initially place one row before the first row. Therefore, you must call next() at least once simply to get to the first row of data. In line 29, I use the method getString in order to retrieve the data for the current row in the column I specified (in this case, "Nickname"). In the next line, I use getInt. There are methods for any type you'd like, including Object, for retrieving data from a database. Each of them will get that data and attempt to put it into the data type that you've requested. If it can't be, a SQLException will be thrown. Lines 35-381: // Close all database resources to make sure they're free 2: tableData.close(); 3: sqlStmt.close(); 4: dbConnect.close();These lines simply close all of the database resources. In most cases, if you close a database resource, all of it's dependent resources will close with it (i.e. closing a Connection will close any Statements issued from it which will close any ResultSet's obtained from them). Using that is usually considered "bad form." It is much safer to close each resource explicitly to ensure that they are released. Lines 40-43 1: catch (SQLException e)
2: {
3: System.out.println("An error occured while interacting with +
DB: " + e);
4: }
This code catches a SQLException. Seems simple enough. I'd just like to make a couple quick points about the SQLException class. This class extends Exception (as you'd expect) but it adds a few extra methods. SQLExceptions can be chained, so a getNextException has been added that will return another SQLException, if there are any more. It also has the ability give you the SQL state and vendor specific error codes. You can look here for full documentation on the SQLException class.
In addition to the SQLException class, there is also a SQLWarning class. These are not thrown like SQLException objects. Rather, if you want them, you have to retrieve them on your own from the Statement.getWarnings or ResultSet.getWarnings methods. Just like SQLException, SQLWarning allows the ability for chaining, so there is a getNextWarning method. Example ConclusionNow that you've seen how JDBC works, hopefully, this all makes much more sense to you. As I continue, I'm going to hit on various aspects of JDBC and show small snippets of code outside of the context of a full program. If you have trouble determining where something fits in, come back to this example.StatementsStatementWe've already looked at the Statement class somewhat. It represents a basic SQL statement and allows you to perform basic operations and queries on your database. There are a few methods that are very important within the Statement class that I'll point out.executeQuery - This method allows you to perform a basic SELECT statement and returns a ResultSet with the values matching that query. executeUpdate - This method allows you to perform an update (update, delete, etc.) statement and returns the number of rows affected by the statement. execute - This method performs a SQL statement without prior knowledge of the type of statement (update or query). After performing the statement, you can use getResultSet and getUpdateCount to determine what action actually took place. getWarnings - This method gets a SQLWarning object, if any exist. These are seldom used in production, but can be useful in debugging. close - Releases database resources tied up for this Statement. PreparedStatementThe PreparedStatement class allows you to set up and compile a SQL statement in advance. You can then set parameters within the statement and execute it. This can greatly increase performance if you're constantly using similar SQL statements. Let's look at an example:1: String sql = "INSERT INTO JUNKIES (Nickname, HomenodeID) VALUES + (?, ?)"; 2: PreparedStatement preparedStmt = dbConnect.prepareStatement(sql)+ ; 3: 4: // Add ybiC with homenode id 458 to the table 5: preparedStmt.clearParameters(); 6: preparedStmt.setString(1, "ybiC"); 7: preparedStmt.setInt(2, 458); 8: preparedStmt.executeUpdate();This snippet, when executed, will add a new entry into the table. Any time that you'd like to add a new entry in this way, you can set the parameters and call executeUpdate (there are executeQuery and execute methods similar to those for Statement) to execute the statement. Notice that parameters are set from left to right, starting at 1. Therefore, to set the first parameter (the left-most question mark), I use the number 1 as my index. For those of you familiar with DBI, the use of question marks in PreparedStatement objects is almost identical to the use of "Placeholders" in DBI. These types of statements are very useful when it comes to performing the same type of statement repeatedly. Unlike this example, however, you probably want to use constants form the parameter numbers to avoid overuse of "magic numbers." CallableStatementThe CallableStatement class allows you to call procedures within your database system. Most databases come with some sort internal programming language, like Oracle's PL/SQL. You can write methods using these languages to perform operations and then call those procedures using a CallableStatement object. This can be an excellent way to help separate the SQL from your application's business logic. This area is by no means my area of expertise, so I have "borrowed" the following example from Java Enterprise is a Nutshell, published by O'Reilly: 1: // Oracle PL/SQL stored procedure
2: CREATE OR REPLACE PROCEDURE sp_interest
3: (id IN INTEGER
4: bal IN OUT FLOAT) IS
5: BEGIN
6: SELECT balance
7: INTO bal
8: FROM accounts
9: WHERE account_id = id;
10:
11: bal := bal + bal * 0.03;
12:
13: UPDATE accounts
14: SET balance = bal
15: WHERE account_id = id;
16:
17: END;
18:
19: // Java Code
20: CallableStatement cstmt = con.prepareCall("{call sp_interest(?,?+
)}");
21: cstmt.registerOutParameter(2, Types.FLOAT);
22: cstmt.setInt(1, accountID);
23: cstmt.setFloat(2, 2343.23);
24: cstmt.execute();
25: out.println("New Balance:" + cstmt.getFloat(2));
Hopefully, that makes some sense to you. You can see that we're using the prepareCall method just like with the PareparedStatement class. There are some slight modifications of syntax to differentiate this type of statement from the other, however. As I mentioned, I've done very little with this. If you really want to learn about these (which probably means you're doing an awful lot with databases already) check out Java Enterprise in a Nutshell or Database Programming with JDBC and Java.
ResultSetAs we've already seen, the results that you get from a query are returned in a ResultSet object. There isn't much more to tell you about ResultSet objects that we haven't already seen but there are a couple of points I'd like to make.You can use the getXXX methods (getString, getInt, etc.) to get data from a row in two ways - I've only shown you one. The following code will, from my example table, result in the same data being retrieved: 1: String nick = tableData.getString("Nickname");
2: int nodeID = tableData.getInt("HomenodeID");
3:
4: // is equivalent to...
5:
6: String nick = tableData.getString(1);
7: int nodeID = tableData.getInt(2);
As you can see, you can use use column indices to get at the data in their respective rows. Notice, however, that these indices are "1 based" as opposed to Java's array implementation that is "0 based." This can be very useful if you don't know the column names at compile time. You can use the metadata classes in order to get information about tables and databases at runtime. I'll discuss those next.
One other useful feature about ResultSet ojects is the method wasNull(). This method can be called after a retrieving a piece of data to determine if that piece of data was null in the database or not. This can be especially useful when using methods like getInt(). Since this method must return a int primitive, null can not be returned. Therefore, -1 is returned by default. Using wasNull can help you determine if what was returned was valid data or simply garbage because the field was null. MetadataMetadata is simply data about data. It's data that gives you more information about the type of structure of other data. In JDBC, there are two metadata classes: DatabaseMetaData and ResultSetMetaData.DatabaseMetaDataA DatabaseMetaData object can be retrieved by calling getMetaData on your connection object. Once you have the DatabaseMetaData object, you can get almost any bit of data you'd like about your database such as: the driver name, the driver version, the database type, the database version, the SQL keywords supported, table names, the maximum number of connections, etc. The list goes on and on. Check out the API to see a full list of all the methods provided by this class (there's a ton).ResultSetMetaDataThis class is very much like the DatabaseMetaData class except that you get it by calling getMetaData on a ResultSet object rather than on a Connection object. It then provides specific data about about that result set. Using this class, you can get information about column names, column data types, wheter columns are nullable, etc. In many ways, using a ResultSetMetaData object, you can learn about a specific table, much like you can learn about a class through Reflection.TransactionsIn case you haven't heard of transactions before, they are simply a set of database statements that must be performed atomically, or at least appear to perform that way. The key here is the idea of commits and rollbacks. You begin a transaction by performing a SQL statement. You continue doing this until all statements within the transaction have been completed. You then "commit" the transaction. In the case that any of the statements fails, however, you need to undo, or rollback, the statements that had already been completed. In this way, you ensure that either all of the statements are completed or none of them are completed. For example, let's say your doing a monetary transfer from one account to another. This would require you to debit some funds from one record and then add them to another. In such a case, you want to ensure that either both actions take place or neither of them take place. You don't want to debit one account only to have the credit fail on you later. In such a case, you'd want to rollback the debit operation.By default, each individual statement is considered a full transaction in Java. Therefore, after every statement is executed, it is commited. In order to handle a multi-statement transaction, you need to turn off this feature. This can be done with a simple call to your connection object's method setAutoCommit(false). Here's a snippet of code that would perform an operation similar to my example: 1: try
2: {
3: // First, turn off this feature
4: dbConnection.setAutoCommit(false);
5:
6: String qry = "UPDATE ACCOUNT SET BALANCE = ? WHERE ACT_ID = +
?";
7: PreparedStatement ps = dbConnection.prepareStatement(qry);
8:
9: ps.clearParameters();
10: ps.setInt(1, originalBalance1 - transferAmount);
11: ps.setInt(2, originAccountID);
12: ps.executeUpdate();
13:
14: ps.clearParameters();
15: ps.setInt(1, originalBalance2 + transferAmount);
16: ps.setInt(2, destinationAccountID);
17: ps.executeUpdate();
18:
19: dbConnection.commit();
20: }
21: catch (SQLException e)
22: {
23: dbConnection.rollback();
24: }
As you can see, this code tries to perform two operations. If the both succeed, the transaction is commited and life goes on happily. If either of the updates fails, however, an exception is caught, the transaction is rolled back, and life goes on...almost as happily.
JDBC 2.0As I had mentioned earlier, the new version of JDBC offers some increased functionality. Since not all database vendors currently supply JDBC 2.0 compliant drivers, however, using these functions can "tie you in" to certain databases, limiting your own flexibility. I'll briefly point out a few of the features available in JDBC 2.0.Handling ResultsIn JDBC 1.0, you could only go on to the next record in a ResultSet. In JDBC 2.0, you can go forward, backward, jump around, and update records with a result set. There is some really nice functionality here (some of the best stuff in 2.0, I think). It's too bad that you can't use it very much yet.Batch UpdatesYou can perform batches of updates rather than doing each update separately. This can greatly increase performance in the case that you need to do hundreds or thousands of updates at one time.Object Oriented DatabasesSlowly but surely, databases are being released that are capable of understanding, storing, and translating Java classes. JDBC 2.0 adds functionality to deal with those types of database and take advantage of that.JDBC Standard ExtensionThe JDBC Standard Extension is comprised of the javax.sql package. This package offers a number of benefits that can be vital to an enterprise level application. Unfortunately, there is little I can do but highlight some of the benefits provided by this extension. In general, you would have to acquire a Manager tool (Guildenstern mentioned the use of Poolman) that would reside in the "middle layer." This manager would be responsible for handling connections and setting properties for various data sources. If you want to implement some or all of the features mentioned here, check the documentation for your specific manager software. You can also go here to get a description of what the managers are doing behind the scenes.As far as your application code is concerned, however, almost nothing changes. There is a key difference that you should note, however. In order to retrieve a connection, you will use a DataSource object rather than a DriverManager class. From that, you will call getConnection, just as you always did. After that point, nothing is different from before. Connection PoolingAs convenient as JDBC is, creating new connections all the time has a lot of overhead and, if you're writing software for a business that may require hundred or thousands of database connections in a day, your software simply won't be efficient. This extension offers Connection Pooling. Connection Pooling is avhieved through opening a connection to a data source and simply reusing that source, rather than creating new ones constantly. This can greatly increase the efficieny of your application.Naming ServicesUsing naming services, you can register a data source with a JNDI naming service. In this way, you can access a DataSource object by using code that looks like this: 1: ctx = new InitialContext();
2: DataSource ds = (DataSource)ctx.lookup("jdbc/registeredDBName");
Without using JNDI, you'd have to look at your manager software's documentation in order to see how to retrieve a DataSource object. Using this can eliminate all Manager dependent code from your application code, increasing portability or modifiability of your application and architecture.
Distributed TransactionsDistributed Transactions allow transactions (see the previous section) to occur over multiple databases. Therefore, if you have databases on various machines, you'd be able to perform a transaction (which behaves atomically) that access different databases, probably on different machines.ConclusionWell, I think it might be a bit of an understatement to say that there's a lot of information here. I used pretty much all of the knowledge I have on JDBC and compiled it with Java Enterprise in a Nutshell to try to cover a little bit of everything within JDBC. Covering it all in detail would take forever. If you really need to use JDBC a lot, I'd recommend looking into the resources at the end of this tutorial. Trust me, it's not really as hard as it seems at first. Sun did a nice job on JDBC.For more information:
[Comment on this Tutorial] |
|
|||||||||||||||||||||||||||
Maintained by Sherlock
Are you
in the right place? Take a peak at the Post FAQ!
This web site is powered by the Everything
Engine and hosted by Oakbox
