Home arrow Java arrow Page 3 - Database Programming in Java Using JDBC
JAVA

Database Programming in Java Using JDBC


An application that does not persist its data in a database is a rarity. Programming languages reflect this trend. That's why all languages provide a robust and flexible library for database access. Java is no exception.

Author Info:
By: A.P.Rajshekhar
Rating: 4 stars4 stars4 stars4 stars4 stars / 56
August 14, 2007
TABLE OF CONTENTS:
  1. · Database Programming in Java Using JDBC
  2. · Accessing the Database using JDBC, Step by Step
  3. · Accessing the Database using JDBC continued
  4. · Using JDBC in the Real World

print this article
SEARCH DEVARTICLES

Database Programming in Java Using JDBC - Accessing the Database using JDBC continued
(Page 3 of 4 )

Instantiating a statement object

A statement represents a query to be executed at the database server against a database. In other words, a statement object is responsible for executing a SQL query as well as retrieving the result of the executed query. JDBC provides three types of statements based on the type of query to be executed. They are: Statement, PreparedStatement, and CallableStatement. They are based on the type of query to be executed.

Statement is the simplest type that represents a simple query. Its object can be instantiated using any of the following forms of the createStatement() method of the Connection interface:

·createStatement() -

Returns a Statement object with default concurrency conditions.

·createStatement(int resultSetType, int resultSetConcurrency)-

Returns a Statement object with concurrency condition and type of ResultSet according to the values passed as values. The most commonly used resulSetTypes include ResultSet.TYPE_FORWARD_ONLY (indicating that the data can be read only in forward direction and once read cannot be moved back to a previous data) and ResultSet.TYPE_SCROLL_SENSITIVE (indicating that the data can be read in both forward and backward directions, and that the changes done by any other operation are visible instantly). The commonly used values for resultSetConcurrency are CONCUR_READ_ONLY (indicating that ResultSet may not be updated) and CONCUR_UPDATABLE (indicating that object may be updated).

For example, to create a Statement object that would provide a ResultSet object which is scrollable and updatable, the statement would be

Statement statement = connection.createStatement(

ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

PreparedStatement conserves resources. Whenever a query is sent to the database server, it goes through four steps: parsing the query, compiling the query, linking and executing the query. When a statement object is used to execute a query all four steps are repeated again and again. This can create resource hogging. The alternative to it is a PreparedStatement object. If a PreparedStatement object is used, the first three steps are performed only once at the start and in successive calls; the values are then passed to the linked query and it is executed. To create an object of PreparedStatement, any of the following forms of prepareStatement can be used:

·prepareStatement(String query)-

This form accepts a parameterized SQL query as a parameter and returns an object of PreparedStatement. "Select * from user where user_id=?" is an example of a parameterized query.

·prepareStatement(String query, int resultSetType, int resultSetConcurrency)-

This form is similar to the first form with the added options of specifying whether ResultSets are scrollable and updatable or not. The values for the two parameters are the same as those described in the Statement section.

For example, to create an instance of PreparedStatement which provides an updatable and scrollable ResultSet, the statements would be:

String query= "Select * from user where user_id=?";

PreparedStatement pStatement = connection.prepareStatement(

query,

ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

To call procedures and functions within a database, one can use CallableStatement. However, if the underlying database doesn't support procedures and functions, then the CallableStatement object won't work. For example, versions of MySQL database prior to 5.0 didn't support functions and procedures. To create an object of CallableStatement, use any of the following forms of the prepareCall() method of Connection:

·prepareCall(String query)-

This returns a CallableStatement object that can be used to execute a procedure or function, which is passed as the query. The query is of the form "{sum(?,?)}" where sum is the function/procedure to be called.

·prepareCall(String sql, int resultSetType, int resultSetConcurrency)-

To get a ResultSet which is both updatable and scrollable, this form can be used. The resultSetType and resultSetConcurrency are same as that used with prepareStatement().

For example, to call a procedure whose name is sum, the statement would be

CallableStatement cStatement = connection.prepareCall(

"{sum(?,?)}");

The next step is to retrieve the ResultSet.

Retrieving the ResultSet object

The rows retrieved by the execution of a SQL query are given back by JDBC in the form of a ResultSet object. A ResultSet contains all the rows retrieved by a query. To retrieve a ResultSet object, one can call the executeQuery() method of the Statement object. If the Statement object is of the type PreparedStatement, then executeQuery() without any argument needs to be called. If it is of the type Statement, then a SQL query will have to be passed to the method. For example, to retrieve a ResultSet from a Statement for the query "Select * from user", the code would be

ResultSet result = statement.executeQuery("Select * from user");

The next step is to get data from the ResultSet.

Accessing the data from the Resultset object

The specialty of ResultSet is that it can be iterated over as a collection and for each iteration, the data can be accessed as it is an array using an index. A ResultSet object can be iterated over using its next() method. During each iteration, one row is retrieved from the number of rows returned by the execution of the SQL query. The columns within the row can be accessed using different forms of the get() method of the ResultSet object. The forms depend upon the data-type of the column to be accessed such as getString() if the column type is varchar, getInt() if the column type is int, and so on. Mostly getString() is used to retrieve data from the columns. The argument that needs to be passed to the method is either a string containing the column name or the integer value representing the index. The index starts from 1 and not 0.

For example, if the user table has a column named "name," then the statements to retrieve the values for the "name" column would be

while(result.next){

System.out.println(result.getString("name));

}

That completes the steps in using JDBC to access the database. Next we will look at an application that will implement these steps to connect to MySQL.


blog comments powered by Disqus
JAVA ARTICLES

- Java Too Insecure, Says Microsoft Researcher
- Google Beats Oracle in Java Ruling
- Deploying Multiple Java Applets as One
- Deploying Java Applets
- Understanding Deployment Frameworks
- Database Programming in Java Using JDBC
- Extension Interfaces and SAX
- Entities, Handlers and SAX
- Advanced SAX
- Conversions and Java Print Streams
- Formatters and Java Print Streams
- Java Print Streams
- Wildcards, Arrays, and Generics in Java
- Wildcards and Generic Methods in Java
- Finishing the Project: Java Web Development ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials