Accessing a database with JDBC

Access | data | Database Publisher: Flyfox

In this exercise you will learn to use JDBC to access the database and insert a basic knowledge of the records to the database.

In this exercise, you will write the register method of the Registrationservlet class. This class is the same as the one you used in SERVLET1A practice. In this exercise, you will use the functionality of the database. This exercise will show you the steps to implement your user needs.

Part One: Understanding the working principle of Regsiter () method in Registrationservlet

In this exercise, you will work on the Com.ibm.waslab.JDBC bag. Expand Registrationservlet class to understand register (). This method throws a SqlException. The complete method declaration is:

Public synchronized void Register (Properties forminput)

Throws sqlexception{



}

The register () method is invoked from the Dopost () method when the servlet obtains control. The register () method actually writes a new record to the database. We will write the register () method in the second section. At the same time, we need to be sure that some of the setup steps are complete.

1. Open the Init () method and enter the following code after the call to Super.init ():

Load JDBC driver for DB2

Try

{

Class.forName (Jktoysdbinfo.gerdriver ());

}

catch (ClassNotFoundException e)

{

Errolog ("JDBC Driver not Found" +e);

}



Save the Init () method. Remember that before drive management can get a connection, a correct database drive must be loaded by the servlet. Each servlet is only once in the servlet's init () method. This method does not really establish a connection, it just allows the connection to be built.



Note: In this case, we load the DB2 app drive. This drive is used when the database is mounted on the same machine that the servlet will be running. If you are accessing a database on another machine, you will use the net drive:

COM.ibm.db2.jdbc.net.DB2Driver

When using a net drive to get a connection, you need to provide more information in the URL used in getconnection (). The machine name or URL where the database resides, and the port number on which the DB2 Java gateway listens. A valid URL parameter looks like this:

Jdbc:db2://servrid:8888/databasename

Part II

1. Now, go back and reopen the register () method. The first line of code creates a database connection:

Connection conn=drivermanager.getconnection (Url,user,password);

You use the drive manager's static method Getconnection () to pass the database URL, a valid user ID and password to it. Url,userid and password are obtained from a property file when the class is loaded (see static variable declaration).

2. Now it's the challenging part. The first thing we want to do is find the last customer number that has been assigned, so that we can assign a new customer number to the new customer being registered (higher than the previous one). We want to store this new customer number in an int variable named NextID. In order to do this, you will need to be sure to use the statement class and the ResultSet class. The following SQL;

Select MAX (Custno) FORM "+dbowner+". CUSTOMER "

will allow you to obtain the current highest customer number. Try writing this code yourself, based on examples of course notes. If you need help, look at the answer page.

3. You will then use a prepared statement object to insert the information in the client's attribute object into the database. Enter the following line:

Insert record in the database

PreparedStatement insertstatement=

Conn.preparestatement ("INSERT into" +dbowner+.) CUSTOMER (Fname,lname,addr,city,state,age,zip,custno) "+" VALUES (?,?,?,?,?,?,?,?));

The above line requires the connection to create a prepared statement object called Insertstatement. The SQL statement is passed as a parameter. The value of each column in the database row is represented by a question mark. Each question mark must be replaced by a value of the correct type. Enter the following line:

Insertstatement.setstring (1,forminput.getproperty ("FNAME"));

Insertstatement.setstring (2,forminput.getproperty ("LNAME"));

Insertstatement.setstring (3,forminput.getproperty ("ADDR"));

Insertstatement.setstring (4,forminput.getproperty ("City"));

Insertstatement.setstring (5,forminput.getproperty ("state"));

Insertstatement.setstring (6,forminput.getproperty ("Age"));

Insertstatement.setstring (7,forminput.getproperty ("ZIP"));

Insertstatement.setint (8,nextid);

4. Insertstatement to perform updates to the database. Enter the following two lines:

Insertstatement.executeupdate ();

If an error occurs when a connection is created, the statement or access to the database, this method throws a SqlException.

5. Finally you need to store the customer number to the Forminput Property object. Enter the following line:

Forminput.put ("Custno", New Integer (NextID). toString ());

6. Save the method. There should be no mistake.

7. You will then need to edit the WebSphere configuration file for the Jkregister servlet. (Edit [X:]\IBMVJAVA\IDE\PROJECT_RESOURCES\IBM WebSphere Test environment\properties\server\servlet\servletservice\ Serrvlets.properties ".)

Change the line that looks like this:

Servlet. Jkregister.code=com.ibm.waslab.servlet1.registrationservlet

For this:

Servlet. Jkregister,code=com.ibm.waslab.jdbc.registrationservlet

8. If your webserver is running, turn it off. In Vaj, start Serunner. Open your Web browser to the registration page of the JK Toys website. Enter the registration information in the form and press the Register button. Your new servlet will be invoked. Log in to the Jktoys site to make sure that registration has occurred. Your new customer number should be found in the database.



What have you done in this exercise

In this exercise, you have written the Register method for the registration servlet. This method uses JDBC to create a new customer number, registering a new customer to the database. Now you have the basics of accessing the database in your servlets.



Answer

The following code creates an SQL statement that executes the SQL query defined in the previous page.



Get Next customer number

Statement sqlstatement=conn.createstatement ();

ResultSet result=sqlstatement.executequery ("Select MAX" (Custno) from "+dbowner+". CUSTOMER ");

The results of the query are placed in the ResultSet object called result. The resulting collection contains a cursor that initially points to the front of the first row returned. In order to get the value of the query the cursor must be moved forward with next (). With this method, the cursor can only move forward. The following lines move the cursor to the returned value, increasing by 1.

int nextid=0;

if (result.next () = =true)

{

Nextid=result.getint (1) +1;

}

The If check determines whether a row is returned. If not, the result is false. We do not deal with the error, but it can simply be used to set the NextID to handle. We can also use the ResultSet method Getint (String), the name of the column is "Custno", but because we know that only one column will be returned, we choose to use the Getint (int).