Skip to Content

*Introduction:- *

In a J2SE or J2EE application, Java Database Connectivity (JDBC) can be used to create a connection with a SQL database, create database tables, insert values required, retrieve result sets, and update the database. To use a database from a Java Server Page (JSP), one often uses a Java or JSP scriptlet to create a JDBC connection via the getConnection() method.

*DBTags : *

DBTags is a custom tag library that contains tags to perform the same tasks — creating a database connection, creating tables, retrieving result sets, and updating tables with prepared statements — all without the use of JSP scriptlets.

This example illustrates the use of DBTags in an example JSP. The example JSP illustrates obtaining a connection with a Oracle database, creating a table in the database, retrieving a result set from the database, and updating the database table with a prepared statement with the DBTags tags.

We have to follow the following procedures:-

  • Preliminary Setup.
  • Obtaining a Database Connection
  • Creating a Database Table.
  • Generating a ResultSet from a Table.
  • Updating a Database Table with a Prepared Statement.

Preliminary Setup :-</p>
        <p>The Apache DBTags are designed for a web server that supports JavaServer Pages specification 1.2. for the servlet container and Oracle 8.1.7 for the database, although you can use other databases and JSP-compliant web servers. </p>
        <p>First, obtain the DBTags binary distribution from the Apache DBTags site. Copy +dbtags.tld +, the tag library descriptor file, to the +/WEB-INF + directory of your web application server . Copy +dbtags.jar +, the tag library .jar file, to the +/WEB-INF/lib + directory. </p>
        <p>Next, you need to modify the +/WEB-INF/ web.xml + web application deployment descriptor file. Include the following taglib tag in the web-app element of the +web.xml + deployment descriptor. </p>
        <p><taglib> </p>
        <p><taglib-uri>http://jakarta.apache.org/taglibs/dbtags</taglib-uri> </p>
        <p><taglib-location>/WEB-INF/dbtags.tld</taglib-location> </p>
        <p></ taglib > </p>
        <p>Then add the following directive to the  example JSP file,Tags.jsp </p>
        <p><%@ taglib uri=”http://jakarta.apache.org/taglibs/dbtags” prefix=”sql” %> </p>
        <p>The prefix attribute of the taglib directive is the prefix of the tags used as they’ll be used the JSP. The choice of sql is arbitrary, meaning you can use another name if you like. </p>
        <p>Next we need to make the database driver classes available to the JSP. For Oracle, add the zip file +/ora81/jdbc/lib/classes12.zip + to the classpath . This completes the required configuration steps. </p>
        <p>*Integrating in WAS 6.40 : – *</p>
        <p>For running it in SAP WAS 6.40 with MaxDB , create an entry in Visual Adminstrator Qualifying Datasource name , Userid,password etc.Rest of the settings remains the same as we do it for Oracle. </p>
        <p>Obtaining a Database Connection :-</p>
        <p>A database connection is required to work with the database. If a JSP scriptlet is used to obtain the connection, the JDBC driver class has to be loaded with: </p>
        <p>Class.forName( “<driver class>”); </p>
        <p>and a JDBC connection is obtained with: </p>
        <p>Connection conn = </p>
        <p>DriverManager.getConnection( url , </p>
        <p>+userName +, </p>
        <p>+password +); </p>
        <p>With the DBTags tag library, a JDBC connection is obtained with the sql :connection tag. In the JSP, +SQLDBTags.jsp +, a connection with a Oracle database is established with the Oracle OCI Type 2 driver as follows: </p>
        <p>< sql :connection id=”conn1″> </p>
        <p>< sql :userId >SYS</ sql:userId > </p>
        <p>< sql :password > change_on_install </ sql:password > </p>
        <p>< sql:url >jdbc:oracle:oci8:@OracleDB</ sql:url > </p>
        <p><sql:driver>oracle.jdbc.driver.OracleDriver</sql:driver> </p>
        <p></ sql :connection > </p>
        <p>The id attribute identifies the connection so that later tags can refer to it. The sql :driver is the database driver class name. Finally, the sql :userId , sql:password , and sql:url tags provide the other needed parameters to create a database connection; i.e., the same parameters needed for a getConnection() call. </p>
        <p>In addition to the attributes and sql :connection tags used in the example JSP, the sql:connection tag syntax can also have the attributes dataSource , which specifies a DataSource object, and jndiName , which specifies a JNDI name of a datasource . A JNDI-named DataSource can also be specified through the tag sql :jndiName . </p>
        <p>With the sql :connection tag, the JDBC connection is created and given an id that can be used by other tags in the JSP. </p>
        <p>Creating a Database Table :-

To create a database table in a JSP scriptlet, a JDBC Statement has to be created with:

Statement stmt= conn.createStatement ( );

A database table can then created with:

statement.executeQuery ( “Create TABLE +… +” );

With DBTags, a SQL database table is created with the sql :statement tag instead. In the example JSP, the table Employee is created with the columns EmpName , Emp id , Dept , Branch , and Country .

The sql :statement tag has an attribute, conn , which refers to the id of the SQL connection created in the previous section. The table is created as follows:

< sql :statement id=”stmt1″ conn=”conn1″>

*Generating a <strong>ResultSet </strong> from a Database Table :-*

To run a query and get results in a JSP scriptlet, a ResultSet has to be created with:

ResultSet rs = stmt.executeQuery ( “SELECT +… +”);

The result set is iterated with code like the following:

while( rs.next ()){

String var = rs.getString ( ” +… +”);

}

With DBTags sql :resultSet tag is used to retrieve and iterate through a SQL query result set. The sql :getColumn tag retrieves the column values in a result set.

*Get the Output in a JSP:- *


< td >< sql:getColumn position=”1″/>
< td >< sql:getColumn position=”2″/>
< td >< sql:getColumn position=”3″/>
< td >< sql:getColumn position=”4″/>
< td >< sql:getColumn position=”5″/>
</ tr >
</ sql :resultSet >
</ sql :statement >

Inside of the sql :resultSet tag, we can use sql:getColumn tags to retrieve the value for a given column and put it in the HTML. In this case, we have a table row inside of the sql :resultSet , and inside each table cell (

), we use sql:getColumn to populate the cell value. The result looks like the following:

Empname

Empid

Dept

Branch

Country

Arun

100

Sap Netweaver

Bangalore

India

Guru

103

Sap NetWeaver

Chennai

India

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Perumal Kanthan
    Hi Guru subramanian,

    Good work by you..

    Why dont you give some more details about configuaring J2EE engine visual administrator.

    Because i am involving in a demo project .There we need to connect with Oracle database and to process the table details.
    currently we are using sqlj concepts for this.
    Can you explain how this DBTags plays better role than this SQLJ concepts?..

    Waiting for your valuable suggestions.
    Thanks in advance..

    Regards,
    K.Perumal..

    (0) 
    1. Guru Subramanian B Post author
      Perumal,
              For configuring in Visual Adminstrator,you need to go to JDBC adapter service.There give your datasource name,user name and password.Then use these DBTags in your jsp file.

               DBTags are not part of your persistence framework.If you want you can use them in SQLJ.So if you use them in SQLJ then the processing time of your application using the tags will be pretty fast and repetetive operations using the sql can be done easily with the tags.

               Lastly,for using oracle database in your program,
              you have to  create a DSN with vendor database option and link the jar file to the datasource using the Database driver.Then in the content manager service we need to create a alias for the dsn created and look up this datasource alias using JNDI in your java code.

               Hope this helps.
      Regards,
      B.Guru Subramanian.

      (0) 
  2. Niharika Jeena
    Hi Guru Subramaniam,
        Good job on behalf of WAS as very few weblogs in WAS.
      This is pretty nice to use DBTags instead of using JSP scriplets which makes code a little bit difficult to understand.Using DBTags it very clear what we are using and its very simple to understand .
    It will be very helpful for starters like us to have clear understanding and to do a quick start in WAS.
    (0) 

Leave a Reply