*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 |
|
