Servlet Advantages and Disadvantages

STEPS TO CONNECT TO DATABASE USING JDBC (Type 4 driver)

In this post I will show you how to connect to a database using JDBC.

STEP 1) Load the Database driver

In this step of the jdbc connection process, we load the driver class by calling Class.forName() with the Driver class name as an argument. Once loaded, the Driver class creates an instance of itself and registers it with the DriverManager. A client can connect to Database Server through JDBC Driver. The return type of the Class.forName (String ClassName) method is “Class”. Class is a class in java.lang package

try{            Class.forName(”oracle.jdbc.Driver.OracleDriver”);       // This is type 4 driver

        } 

catch(Exception x)   {

            System.out.println( “Unable to load the driver class!” );

     }

STEP 2) Establish Connection using getConnection() method of Driver Manager

The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager is considered the backbone of JDBC architecture. DriverManager class manages the JDBC drivers that are installed on the system. Its getConnection() method is used to establish a connection to a database. It uses a username, password, and a jdbc url to establish a connection to the database and returns a connection object. A jdbc Connection represents a session/connection with a specific database. An application can have one or more connections with a single database, or it can have many connections with different databases.

 try{

 Connection conn=DriverManager.getConnection(url,”loginName”,”Pwd”);

}

catch( SQLException x ){

            System.out.println( “Couldn’t get connection!” );

}

The DriverManager provides registerDriver() and deregisterDriver() methods, which allow a Driver implementation to register itself with the DriverManager or remove itself from that list. You can get an enumeration of registered drivers through the getDrivers() method.

STEP 3) Create and execute SQL statements

Once a connection is obtained we can interact with the database. Connection interface defines methods for interacting with the database via the established connection. To execute SQL statements, you need to instantiate a Statement object from your connection object by using the createStatement() method.

Statement statement = dbConnection.createStatement();

A statement object is used to send and execute SQL statements to a database.

Three types of statements are:

1.Statement which is used to run simple sqlstatements like select and update

2. PrepareStatment is used to run Pre compiled sql. 

3. CallableStatement is used to execute the stored procedures.

Creating Statement Object:

Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object's createStatement( ) method, as in the following example:

Statement stmt = null;

try {

   stmt = conn.createStatement( );

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   . . .

}

Once you've created a Statement object, you can then use it to execute a SQL statement with one of its three execute methods.

1.boolean execute(String SQL) : It is used for situations in which you do not know whether the SQL being executed is a query or update .Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.

2.int executeUpdate(String SQL) : Returns the numbers of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.

3.ResultSet executeQuery(String SQL) : Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

Note:More about JDBC Statements refer this tutorial-->LINK

STEP 4)Getting results

Create Resultset object using the statement created above:

ResultSet rs = s.executeQuery("sql statement");

The ResultSet interface provides methods for retrieving and manipulating the results of executed queries. Iterate the result set to get all the values from the database.

ResultSet interface provides access to a table of data generated by executing a Statement. The table rows are retrieved in sequence. A ResultSet maintains a cursor pointing to its current row of data. The next() method is used to successively step through the rows of the tabular results. 

If next() returns true, you have another row to process and any subsequent calls you make to theResultSet object will be in reference to that next row. If there are no rows left, it returns false. . Please note that ResultSet objects allow only one-way navigation through rows from a query--there is no previous() counterpart to thenext() method.

Viewing a Result Set:

The ResultSet interface contains dozens of methods for getting the data of the current row.

There is a get method for each of the possible data types, and each get method has two versions:

1.One that takes in a column name.

                public int getInt(String columnName) throws SQLException
                Returns the int in the current row in the column named columnName

2.One that takes in a column index.

               public int getInt(int columnIndex) throws SQLException
               Returns the int in the current row in the specified column index. The column  

               index starts at 1, meaning the first column of a row is 1, the second column of a

               row is 2, and so on.

Similarly there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as common types such as java.lang.String, java.lang.Object, and java.net.URL

There are also methods for getting SQL data types java.sql.Date, java.sql.Time, java.sql.TimeStamp, java.sql.Clob, and java.sql.Blob. Check the documentation for more information about using these SQL data types.

  

 ResultSet rs=stmt.executeQuery(“select * from emp”);

  while(rs.next())

  {

  out.println(rs.getInt(1));

  out.println(rs.getString(2));

  }

STEP 5)Clean Up (Closing open connections)

In the examples provided so far, you may have noticed many of the objects being closed through a close() method. The Connection, Statement, and ResultSet classes all have close(). A given JDBC implementation may or may not require you to close these objects before reusing. But some might, since they likely are holding precious database resources. It is therefore always a good idea to close any instance of the above objects when you are done with them. If you do manage to close a Connection before committing with auto-commit off, any uncommitted transactions will be lost.

finally{                     //finally block used to close resources

                     try{

                                  if(stmt!=null)

                                  stmt.close();

                             }catch(SQLException se2){

                             }                   // nothing we can do

                        try{

                              if(conn!=null)

                               conn.close();

                                }catch(SQLException se){

                                                   se.printStackTrace();

                                }     
              }   //end finally try

                        PREV                                               NEXT