JSTL Sql tags with JNDI datasource : In this article we will show you how to create the JNDI datasource and access it using the JSTL tag library. We will use the tomcat DBCP Connection Pool defined in context.xml, to execute an SQL query and output the data using JSTL in a JSP page.
Steps to create JNDI datasource and code :
Step 1: First you need to have some important jar files inside your project's lib and also JDBC driver to tomcat's lib. Following are the jsrs which needed :
commons-dbcp-1.4.jar
JDBC driver
jstl.jar
standard.jar
Step 2 : Now you have all jar files and put them in right place. Now you need to create the context.xml inside the META-INF directory.Set your database connection details in the META-INF/context.xml file (driver name, JDBC URL, username, password).
Step 3 : Create a test jsp file which will lookup for JNDI datasource and get the database connection using it.
Example of Accessing a JNDI DataSource with JSTL :
META-INF/context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/mydatabase"
auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="com.mysql.jdbc.Driver
url="jdbc:mysql://localhost:3306/databaseName"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
TestPage.jsp
<%@ page pageEncoding="UTF-8" contentType="text/html" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<html>
<head>
<title>JSTL with JNDI DBCP Test </title>
</head>
<body>
<h1>JSTL with JNDI DBCP Test Page</h1>
<br/>Executing the query
<br/>
<sql:query var="result" dataSource="jdbc/mydatabase">
SELECT * FROM users
</sql:query>
<table border="1">
<%-- Output column names on a header row --%>
<tr>
<c:forEach var="columnName" items="${result.columnNames}">
<th><c:out value="${columnName}"/></th>
</c:forEach>
</tr>
<%-- Output each row of data --%>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.uid}"/></td>
<td><c:out value="${row.uname}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
0 Comment(s)