| The SQL tags provided by JSTL make use of Java Database Connectivity (JDBC) drivers. Most commonly used databases provide a JDBC driver. Sun also makes available a bridge driver that allows Java programs to make use of ODBC databases. Usually, you should try to find a suitable JDBC driver before you use the ODBC bridge driver. However, if no such driver exists, you may access the database using the ODBC bridge. Connecting to a JDBC Data Source To use the JSTL SQL tags, you have to identify a data source. Each of the JSTL SQL tags accepts a dataSource attribute that lets the SQL tag know what data source to use. Once the data source is specified, the JSTL tags will collaborate with that source to access the data requested. JSTL provides three primary ways for setting up this collaboration. Let's take a look at each method. Transparent Collaboration For this approach, you must provide initialization code in the application logic of a server or other related Java code. For example, you could do this by using the application event listener of a server. The servlet must then store the application's default DataSource object in the javax.servlet.jsp.jstl.sql.dataSource application or session-scoped variable. This approach is advantageous because it makes the selection of the data source completely transparent to the page programmer. The JSP programmer does not need to specify a dataSource attribute to any of the JSTL tags being used, as shown here: <sql:query ...> Explicit Collaboration via Application Logic It is also possible for servlet Java code to store a data source in a scoped variable for the page author to use. This variable must be stored at either session or application scope. You may use any scoped variable name. You must communicate the scoped variable name and intended scope to the JSP page programmer, who is then free to use this scoped variable in SQL tags. A typical SQL tag using this method would look like this: <sql:query dataSource="${dataSource}" ...> Explicit Collaboration via the <sql:setDataSource> Tag Both of the previous two approaches require access to more than just the JSP code. It is also possible to specify a data source using only JSTL tags from JSP. To use this method, you must use the <sql:setDataSource> tag to create a DataSource object implemented as a wrapper around JDBC's DriverManager class. The following code loads the driver with the specified classname, and establishes a connection to the database associated with the given JDBC URL: <sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver" url="jdbc:mysql://localhost/forum?user=forumuser"/> <sql:query dataSource="${dataSource}" .../> Using the <sql:setDataSource> Tag You use the <sql:setDataSource> tag to create a data source that is contained in a scoped variable. You can use this tag in calls to the other JSTL SQL tags. The <sql:setDataSource> tag has one form: <sql:setDataSource var="varName" [scope="{page|request|session|application}"] [driver="driverClassName"] [url="jdbcUrl"] [user="userName"]/> The <sql:setDataSource> tag accepts these attributes: | driver | N | The classname of the JDBC data source that is to be used. | | scope | N | The scope of the scoped variable specified by the attribute var. This attribute defaults to page. | | url | N | The URL of the data source. | | user | N | The user that is to be used to log into the data source. | | var | N | The scoped variable that will hold the newly created data source. | As we mentioned earlier, you can use the <sql:setDataSource> tag to create ad hoc connections to a data source from JSP pages. This new data source will be assigned to the variable specified by the var attribute. You specify the scope of this variable with the scope attribute. If no scope is specified, then page scope is assumed. JDBC requires two parameters to connect to a data source. First, you must specify the JDBC driver you are going to use with the driver attribute. Second, you must specify the URL of the database that you are connecting to by using the url attribute. That URL is not the HTTP-type URL that you might be familiar with. The exact format of this URL is specified by the driver you are using. For more information on the format, consult the documentation associated with the driver you are using. To show how to use the driver, we'll now look at the driver tag you must use to connect to MySQL and Microsoft Access. In the following sections, we assume that you have already set up either your Microsoft Access or your MySQL environment for the forum application. First, we'll show you how to connect to a MySQL data source. Connecting to MySQL The current version of MySQL does not come with a JDBC driver. To use MySQL with Java, you must obtain a driver elsewhere. One of the most common drivers used to allow Java to access the MySQL database is known as the MM driver. You can obtain this driver from http://mmmysql.sourceforge.net/. Installing the driver is easy. You must copy the MM driver's JAR file mm.mysql-2.0.12-bin.jar to the lib directory of your Web application (most likely C:\Program Files\Apache Tomcat 4.0\webapps\ROOT\WEB-INF\lib). Once you've done this, you will be ready to use MySQL from your Web applications. If your Tomcat Web server was already running, you must restart it. Now let's look at how you use the setDataSource tag to open a connection to the forum MySQL database. A Web page that performs a simple query is shown in Listing 7.2. Listing 7.2 Connecting to MySQL (query.jsp)<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %> <sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver" url="jdbc:mysql://localhost/forum?user=forumuser"/> <html> <head> <title>Query Example</title> </head> <body> <sql:query var = "users" dataSource="${dataSource}"> select c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type from t_users </sql:query> <table border=1> <c:forEach var="row" items="${users.rows}"> <tr> <td><c:out value="${row.c_uid}"/></td> <td><c:out value="${row.c_pwd}"/></td> <td><c:out value="${row.c_accesses}"/></td> <td><c:out value="${row.c_first}"/></td> <td><c:out value="${row.c_last}"/></td> <td><c:out value="${row.c_bad}"/></td> <td><c:out value="${row.c_posted}"/></td> <td><c:out value="${row.c_type}"/></td> </tr> </c:forEach> </table> </body> </html> The program in Listing 7.2 begins by opening a connection to a MySQL database that resides on the local computer. The following setDataSource command accomplishes this: <sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver" url="jdbc:mysql://localhost/forum?user=forumuser"/> If you want to connect to a MySQL database on a different computer, you must specify its hostname in place of localhost. Once you've executed this command, this means the scoped variable dataSource now contains a connection to the forum MySQL database. We did not specify a scope attribute, so this scoped variable will have page scope. With the data connection open, you may now issue queries against it. This page performs a query against the t_users table and lists all registered users. You must specify the name of the data source in the <sql:query> tag so that the program finds the source: <sql:query var = "users" dataSource="${dataSource}"> select c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type from t_users </sql:query> Now that we've seen how to use the <sql:setDataSource> tag with MySQl, let's see how to use it in Microsoft Access. Connecting to Microsoft Access It is not difficult to connect to a Microsoft Access database using JSTL. The <sql:setDataSource> tag must specify the ODBC bridge and a URL that will enable it to access a Microsoft Access database. We'll take the same basic query that we just used with MySQL and modify the JSP page so that it works with Access. This page is shown in Listing 7.3. Listing 7.3 Connecting to Microsoft Access<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %> <sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:forum"/> <html> <head> <title>Out Examples</title> </head> <body> <sql:query var = "users" dataSource="${dataSource}"> select c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type from t_users </sql:query> <table border=1> <c:forEach var="row" items="${users.rows}"> <tr> <td><c:out value="${row.c_uid}"/></td> <td><c:out value="${row.c_pwd}"/></td> <td><c:out value="${row.c_accesses}"/></td> <td><c:out value="${row.c_first}"/></td> <td><c:out value="${row.c_last}"/></td> <td><c:out value="${row.c_bad}"/></td> <td><c:out value="${row.c_posted}"/></td> <td><c:out value="${row.c_type}"/></td> </tr> </c:forEach> </table> </body> </html> Listing 7.3 is nearly identical to Listing 7.2, with the exception of the driver tag. The driver tag used in Listing 7.3 is designed to work with Access. Here's the setDataSource tag: <sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:forum"/> As you can see, the ODBC bridge driver is specified in the driver attribute. The URL that we've specified gives the DSN forum of the database we are trying to access. This driver tag will create a new driver in the scoped variable dataSource that holds the connection to the Access database. Once this connection is made, the remaining commands are the same ones used for the MySQL connection. NOTE There are some subtle differences between the syntax of SQL under MySQL and Microsoft Access. All of the sample SQL given in this chapter stays generic enough so that it will work on either. This is not always an easy task. If you want to ensure compatibility between two databases with the same SQL, you must test the SQL on both databases to be sure that it will work as you intend.
Connecting to Other Databases Microsoft Access and MySQL are not the only databases that the JSTL SQL tags will work with. You can use any database that has a Java JDBC driver available. To use another database, you must find out the format of its driver and URL values. If you use these values correctly in the <sql:setDataSource> tag, then the database should be accessible to JSP pages. | 
Print 
E-Mail 
Add Note 
Add Bookmark
