Accessing Database from JSP

Explanation of Code

Declaring Variables:

Java is a strongly typed language which means, that variables must be explicitly declared before use and must be declared with the correct data types.

In the above example code we declare some variables for making connection.

Theses variables are-

Connection con=null; ResultSet rst=null; Statement stmt=null;

The objects of type Connection, ResultSet and Statement are associated with the Java sql.

"con" is a Connection type object variable that will hold Connection type object.

"rst" is a ResultSet type object variable that will hold a result set returned by a database query.

"stmt" is a object variable of Statement .Statement Class methods allow to execute any query

Connection to database:

The first task of this programmer is to load database driver.

This is achieved using the single line of code:-

String driver = "org.gjt.mm.mysql.Driver"; Class.forName(driver).newInstance();

The next task is to make a connection.

This is done using the single line of code :-

String url="jdbc:mysql://localhost/books?user=<userName>&password=<password>"; con=DriverManager.getConnection(url);

When url is passed into getConnection() method of DriverManager class it returns connection object.

Executing Query or Accessing data from database:

This is done using following code :-

stmt=con.createStatement(); //create a Statement object rst=stmt.executeQuery("select * from books_details");

stmt is the Statement type variable name and rst is the RecordSet type variable.

A query is always executed on a Statement object.

A Statement object is created by calling createStatement() method on connection object con.

The two most important methods of this Statement interface are executeQuery() and executeUpdate().

The executeQuery() method executes an SQL statement that returns a single ResultSet object.

The executeUpdate() method executes an insert, update, and delete SQL statement.

The method returns the number of records affected by the SQL statement execution.

After creating a Statement ,a method executeQuery() or executeUpdate() is called on Statement object stmt and a SQL query string is passed in method executeQuery() or executeUpdate().

This will return a ResultSet rst related to the query string.

Reading values from a ResultSet:

while(rst.next()) { %> <tr><td><%=no%></td><td><%=rst.getString("book_name")%></td> <td><%=rst.getString("author")%></td></tr> <% }

The ResultSet represents a table-like database result set.

A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. Therefore, to access the first row in the ResultSet, we use the next () method. This method moves the cursor to the next record and returns true if the next row is valid, and false if there are no more records in the ResultSet object.

Other important methods are getXXX () methods, where XXX is the data type returned by the method at the specified index, including String, long, and int.

The indexing used is 1-based.

For example, to obtain the second column of type String, we use the following code:

resultSet.getString(2);

We can also use the getXXX () methods that accept a column name instead of a column index.

For instance, the following code retrieves the value of the column LastName of type String.

resultSet.getString("book_name");

The above example shows how we can use the next() method as well as the getString() method.

Here we retrieve the 'book_name' and 'author' columns from a table called 'books_details'. We then iterate through the returned ResultSet and print all the book name and author name in the format " book name | author " to the web page.