protected int getDBSequenceValue( boolean currentVal ) { // Placeholder for the value… int currentValue = 0; // This statement retrieves the current value from the database String sql; if ( currentVal ) sql = “select ” + mySequenceName + “.currval from dual”; else sql = “select ” + mySequenceName + “.nextval from dual”; try { if ( myConnection.getStatement().execute( sql ) ) { // The results are received here… ResultSet rs = myConnection.getStatement().getResultSet(); rs.next(); currentValue = rs.getInt( 1 ); } } catch ( SQLException e ) { // No records? Set to 1… currentValue = 1; } return( currentValue ); } } The getDBSequenceValue() method constructs an SQL statement dynamically with the construction values that were passed. This statement is then sent to the database and the results are returned. The SequenceGenerator Class The last class of note is the SequenceGenerator class. This class generates a unique sequence number that identifies the rows in your database tables. For example, you can use the SequenceGenerator class to generate employee identification numbers. You’ll see that use illustrated in Chapter 13, “Employee Files.” The SequenceGenerator works on a simple principle. Given a table and column, it retrieves the data in that column of the table in reverse sorted order. Therefore, the first row returned is the highest. It takes this highest value and increments it, creating your unique number. The constructor accepts as input the necessary components to create the sequence retrieving SQL: public
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services
OracleSequence { //**************************************************************************** //* Members &nb sp; * //**************************************************************************** /** * The name of the database sequence */ String mySequenceName; /** * The name of the database sequence */ OracleConnector myConnection; //**************************************************************************** //* Constructors &nbs p; * //**************************************************************************** public OracleSequence( OracleConnector connector, String sequenceName ) { myConnection = connector; mySequenceName = sequenceName; } //**************************************************************************** //* getNextValue &nbs p; * //**************************************************************************** public int getNextValue() { return( getDBSequenceValue( false ) ); } //**************************************************************************** //* getCurrentValue & nbsp; * //**************************************************************************** public int getCurrentValue() { return( getDBSequenceValue( true ) ); } //**************************************************************************** //* getDBSequenceValue &nbs p; * //****************************************************************************
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services
//* DBConnector ; * //**************************************************************************** public abstract class DBConnector implements Connector The DBConnector class implements the Connector interface but not the entire interface. Extensions of this class must implement one method, getConnectionURL; therefore it is declared abstract. Instance Variables The DBConnector class has several important instance variables: l protected Connection myConnection: Holds a JDBC Connection object for talking to the database l protected Statement myStatement: Holds a JDBC Statement object for executing SQL queries l protected boolean isConnected: Holds an indicator of the connection status l protected String lastError: Holds the last error that occurred, if any All of these instance variables are protected. Only myStatement is available outside of the DBConnector hierarchy through the getStatement() method. This class is mainly used to execute database queries. OracleSequence A sequence is a database object that provides unique numbers for storing in the database. For example, you could set up a sequence to provide new employee identification numbers for your employee table. This sequence would then generate the numbers for you. This class encapsulates using an Oracle sequence. The sequence must exist for this class to work. If you don’t have any sequences, you can create one with the following command: CREATE SEQUEncE sequence name IncREMENT BY increment START WITH starting value where sequence name is the name you wish to call the sequence. increment is the amount to increment each time. starting value is the value at which to start the sequence. Note The preceding command line is only enough to get by. Many more command options are available. Consult your Oracle manual for more information. This class provided two methods: getCurrentValue() and getNextValue(). Each returns ints and provides the current and next values of the Oracle sequence. Listing 10.2 shows the source code for the OracleSequence class. Listing 10.2. The OracleSequence class. //**************************************************************************** //* OracleSequence &n bsp; * //**************************************************************************** public class
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services
; * //**************************************************************************** public String generateInsertSQL( boolean addParen ); The generateInsertSQL method takes a single argument: addParen. In an SQL INSERT statement, you must place an opening parenthesis (() in front of the very first column. By setting this argument to true, the parenthesis is placed in front of the INSERT SQL returned. Sample return values follow: emp_salary = 75000 if addParen is set to false or ( emp_salary = 750000 if addParen is set to true These statements can be concatenated and used in an SQL INSERT statement. The Classes Let’s create some classes to access these JDBC data sources. The first class you’ll create should encapsulate the connection to a particular database. This class also should implement the Connector interface you’ve just defined. You might want to build several of these specialized connection classes however, so you’ll place all of your base functionality into an abstract class, then create subclasses of it that implement the database specifics. The first class, DBConnector, is the abstract base class. You will use it as a basis for specific database connection objects. Several classes will be derived from this base class: l MSQLConnector: Connects to mSQL data sources l MSSQLServerConnector: Connects to Microsoft SQL Server data sources l ODBcconnector: Connects to ODBC data sources l OracleConnector: Connects to Oracle data sources l SybaseConnector: Connects to Sybase data sources These classes really do nothing more than provide the base class with a database URL with which to connect. The DriverManager does the real work. The last two classes that you’ve built for your intranet are the OracleSequence and the SequenceGenerator classes. OracleSequence uses Oracle’s sequence object to create the unique number. This object is Oracle’s internal counter. When you query it for its next value, it increments its counter and stores the result. It also returns the result to you. Because not everyone will have access to Oracle sequences, however, the SequenceGenerator class is provided. The SequenceGenerator class provides the exact same functionality as the OracleSequence class but in a generic manner that can be used with any database. Let’s look at some classes in depth. The DBConnector Class The DBConnector class encapsulates a lot of the repetitive tasks that are needed to connect with a JDBC data source. It is an abstract class; therefore, it is incomplete. You cannot instantiate the DBConnector class directly; you can only instantiate its derivatives. The declaration of the DBConnector class follows: //****************************************************************************
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services
public String getConnectionURL() The connection URL is used to connect with a JDBC data source. It is up to the implementor of this interface to return the correct connection URL for the database with which it is working. The SQLFactory Interface The second interface you need is one with which you can create classes that generate SQL statements for the objects that they contain. The implementor of this interface decides the method of generation. Why would you want to generate SQL on-the-fly? Because with SQL, you can build smart objects that will know and keep track of whether they have been modified. If you then place these smart objects into a smart container, the container can produce an SQL statement to update the database. But there needs to be a template or pattern for these smart objects to follow, and that’s where the SQLFactory class comes in. The SQLFactory interface exists to provide a common set of methods for objects to use to generate SQL. The SQL generated is used to send information back to the database when values change. Chapter 11, “User Interface Classes,” contains some classes that implement this interface. Note Chapter 6 discussed the INSERT and UPDATE SQL commands. These two commands are used to ins ert and update records, or rows, in a database and are the subject of these interface methods. The SQLFactory interface defines several methods. Two methods are very important: generateUpdateSQL and generateInsertSQL. The generateUpdateSQL Method This method is defined as follows: //**************************************************************************** //* generateUpdateSQl ; * //**************************************************************************** public String generateUpdateSQL( boolean addSet ); The generateUpdateSQL method takes a single argument: addSet. In an SQL UPDATE statement, you must place the keyword SET in front of the very first column. By setting this argument to true, a SET is placed in front of the UPDATE SQL returned. Sample return values follow: emp_salary = 75000 if addSet is set to false or SET emp_salary = 750000 if addSet is set to true These statements can be concatenated and used in an SQL UPDATE statement. The generateInsertSQL Method This method is defined as follows: //**************************************************************************** //* generateInsertSQL
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services
Let’s take a look at each of these interface methods. The connect Method The connect method is defined as follows: //**************************************************************************** //* connect &nb sp; * //**************************************************************************** public boolean connect( String user, String password, String server ); The user’s name and password and the server where the data exists are accepted as input arguments. The third parameter, the server where the data exists, might be optional with some Connector implementations, but I have included it here for completeness. The getConnectionURL method used this information to connect with a JDBC data source. The implementor must connect to the database in this method. The disconnect Method The disconnect method is defined as follows: //**************************************************************************** //* disconnect * //**************************************************************************** public boolean disconnect(); Nothing spectacular here. The implementor must disconnect from the database in this method. The connected Method The connected method simply returns true or false if the object is currently connected to a database. The method is defined as follows: //**************************************************************************** //* connected & nbsp; * //**************************************************************************** public boolean connected(); The implementor must keep track of the connection status and report on it through this method. The getConnectionURL Method The final method in the Connector interface is the getConnectionURL method. It is defined as follows: //**************************************************************************** //* constructConnectionURL * //****************************************************************************
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services
} } This program uses jdbcKona drivers to communicate with a Personal Oracle7 database on a local machine. When Personal Oracle7 is installed, it creates a database alias called tcp-loopback.world. You will connect with this alias in this example. Also included with Personal Oracle7 is a set of sample tables with data. This program will retrieve and print three of the columns from the employee table that comes with Personal Oracle7. What is Personal Oracle7? Personal Oracle7 is a single-user Oracle product. It runs in either Windows 95 or Windows NT, and provides all of the functionality of the full-blown Oracle server product at a fraction of the cost. It is an excellent tool with which database developers can tune their craft; it is also good for application development. However, Personal Oracle7 is not like many desktop databases on the market today. There is really no comparison between Personal Oracle7 and Microsoft Access. Access provides you with a complete development environment rife with tools. Personal Oracle7 is simply a database and provides no user-friendly development tools such as wizards. Personal Oracle7 does come with a very nice interface program that lets you edit many database objects with dialog boxes instead of with SQL statements. Personal Oracle7 can be downloaded for a free trial from the Oracle Web site at http://www.oracle.com Information about Microsoft Access can be found at http://www.microsoft.com Making JDBC Easy to Use To make JDBC even easier to use in your intranet applications, you’re going to create a set of interfaces and classes. These objects will greatly simplify your use of JBDC and database programming in general. As you have been doing, you’ll look at the interfaces first then move on to the classes. Two interfaces in particular will be useful to us: Connector and SQLFactory. They both provide very different functions, so let’s cover them individually. The Connector Interface The Connector interface defines the pattern or template that a class must follow to comply with your database connection standard, JDBC. The standard is defined by this interface, however, for the most part. By extending this interface, you are extending the standard. The Connector is the place to implement the functionality your applications required. The Connector interface defines four standard methods, which follow: l connect: Connects with a data source l disconnect: Disconnects from a data source l connected: Indicates the connection status l getConnectionURL: Returns an URL used for connecting to a JDBC data source
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services
{ //**************************************************************************** //* main * //**************************************************************************** public static void main( String args[] ) throws Exception { // Find the class… Class.forName( “weblogic.jdbc.oci.Driver” ); // Open a connection… Connection myConnection = DriverManager.getConnection( “jdbc:weblogic:oracle:tcp-loopback.world”, “scott”, “tiger” ); // Create a statement… Statement myStatement = myConnection.createStatement(); // Execute a query… try { // Execute the query… myStatement.execute( “select * from emp” ); // Get the result set… ResultSet mySet = myStatement.getResultSet(); // Advance to the next row… while ( mySet.next() ) { // Get the data… int empno = mySet.getInt( “empno” ); String ename = mySet.getString( “ename” ); long salary = mySet.getLong( “sal” ); // Print it all out… System.out.println( Integer.toString( empno ) + ” - ” + ename + ” - ” + Integer.toString( empno ) ); } } catch ( SQLException e ) { System.out.println( “SQL Error: ” + e.toString() ); } // Close everything up… myStatement.close(); myConnection.close();
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services
l short getShort() l int getInt() l long getLong() l float getFloat() l double getDouble() l Numeric getNumeric() l byte[] getBytes() l java.sql.Date getDate() l java.sql.Time getTime() l java.sql.Timestamp getTimestamp() l java.io.InputStream getAsciiStream() l java.io.InputStream getUnicodeStream() l java.io.InputStream getBinaryStream() Getting to the Next Row When you’ve retrieved all the data you can from a column, it is time to move on to the next row. Moving to the next row is done with the next() method. This method returns a boolean value indicating the status of the row. Internally, it moves the ResultSet’s cursor to the next row, thereby giving access to the data stored in the columns of that row. When a ResultSet object is created, its position is always before the first row of data contained within. This makes it necessary to call the next() method before you can access any column data. The first call to next() makes the first row available to your program. Subsequent calls to next() make the next rows available. If no more rows are available, next() returns false. A JDBC Sample Program Programming concepts aren’t always clear when you read about them, therefore a small example of JDBC program is definitely in order. The following program in Listing 10.1 is a sample program that illustrates the concepts presented so far. These concepts follow: l Connecting to a JDBC data source l Executing a query l Parsing the results of the query Listing 10.1. A sample JDBC program. //**************************************************************************** //* Imports &nb sp; * //**************************************************************************** import java.sql.*; //**************************************************************************** //* JDBCExample ; * //**************************************************************************** public class JDBCExample
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services
To connect with a JDBC data source, a uniform resource locator, or URL, is used. The format follows: jdbc:: where sub-protocol is the name of the driver set that defines a particular database connectivity method. This can be represented by several drivers. sub-name is the additional information necessary to complete the URL. This information is different depending on the sub-protocol. You are undoubtedly familiar with HTTP URLs. The JDBC URL simply prepends a jdbc. Let’s look at an example JDBC URL. Let’s say you want to connect with an mSQL data source on host hermy.munster.com on port 4333. The instance name is simply called data. The connection URL would be jdbc:msql://hermy.munster.com:4333/data msql is the sub-protocol, and //hermy.munster.com:4333/data is the sub-name. To connect with an Oracle data source with the jdbcKona drivers from WebLogic, the following URL: jdbc:weblogic:oracle is sufficient. This is because the jdbcKona drivers use Oracle’s SQL*Net software, which maintains its own set of network addresses for database instances. All it needs to know is the type of database with which it is connecting. Remember that Vector that holds driver information in the DriverManager class? Well, here’s where you’ll use it. When you call the getConnection() method, the DriverManager asks each driver that has registered with it whether the database connection URL is valid. If one driver responds positively, the DriverManager assumes a match. If no driver responds positively, an SQLException is thrown. The DriverManager returns the error “no suitable driver,” which means that of all the drivers that the DriverManager knows about, not one of them could figure out the URL you passed to it. Assuming that the URL was good and a Driver stepped up to the plate and said, “I can handle this!”, then the DriverManager will return a Connection object to you. What can you do with a Connection object? Not much. This class is nothing more than an encapsulation of your database connection. It is a factory and manager object, and is responsible for creating and managing Statement objects. The Statement Class Picture your Connection as an open pipeline to your database. Database transactions travel back and forth between your program and the database through this pipeline. The Statement class represents these transactions. The Statement class encapsulates SQL queries to your database. Using several methods, these calls return objects that contain the results of your SQL query. When you execute an SQL query, the data that is returned to you is commonly called the result set. You can choose from several result sets, depending on your needs: ResultSet executeQuery( String sqlStatement ) This method sends the SQL query contained in sqlStatement and returns a single set of results. This method is best used in sending SELECT statements. These statements typically return a result set. l
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services