JSTL SQL Tags

10 Dec

I came across this JSTL SQL Tags while trying to find the way for fixing the session problem where in I was losing the session information for some reasons while displaying the result sets from servlets to JSP.JSTL tags were easy to learn and helped me in quickly bringing the page live.

JSTL SQL tags I believe is mostly related to interacting with database and provides a great interface from pulling the data out of database with minimal efforts.In case if you are in Netbeans its more of cakewalk,just pull and drop widgets from Netbeans palette and point it to the relevant data source.Database can be accessed both via JNDI Data source or by writing the connection strings in sql tags itself.We can just pull the data from the database and display it in with c out tag by iterating the result set. With SQL tags we can also set up the max result set size that can be retrieved in the database calls.

The first and foremost part to use JSTL tags is import JSTL tag library jars(Standard and JSTL.jars) and add it to the lib directory of the project and add the below line on the top of the page

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

Below is sample code for SQL Tags to access database directly with sql setdatasource tag

<sql:setDataSource var="databasename"
     url="jdbc:mysql://localhost:3306/databasename"
     driver="com.mysql.jdbc.Driver"
     user="databaseuserid" password="databasepassword"/>

In case if you have datasource defined in web xml file of the server, then its also quite easy to point the datasource with sql setDatasource tag,

<sql:setDataSource  dataSource="jdbc/databasename"/>

Once you have datasource configured correctly, you can fire the query to fetch your results with c tags of JSTL tag library you can iterate and display the datasets in the page.

<sql:query var="resultset" scope="page"  maxRows="100">
   SELECT xx from tablename where xx == y orderby z Desc;
</sql:query >

maxRows attribute in the above sql query will be fetch the  number of rows of data what you require to display in the page.Scope is what you set for depending on your requirements.It could be be either session/page/application etc.Once you have result sets ,you can display it in the page and rest is all formatting the page container elements in the HTML Tags.

I would like to  say that most experts in java web world will not prefer to directly interact with database from the web page but again if you have a small applications with known set of users, then probably I feel its alright to access the database from the page.

In case if you are developing with Netbeans, then I feel it makes your job quite easy and fast.

Advertisements

3 Responses to “JSTL SQL Tags”

  1. practice tests December 16, 2011 at 2:18 am #

    Great posting! I really like the way you are sharing the unique take on this subject. Keep it up!

  2. Business Entrepreneurs December 25, 2011 at 1:21 am #

    I’ve heard some good things about this blog. Remember to balance the pics with the text tho. cheers!

  3. Ewa Flecha January 15, 2012 at 1:46 am #

    Thank you for taking time to write this write-up. It’s been very useful. It could not have come at a greater time for me! http://www.ctctradeshows.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: