What is the correct way to make my jstl connected to Mysql database in Tomcat?

[tangara]

Hi,

I've tried to created a display page in jsp using jstl.

Basically, I have added in the resource references in xml.

My project name is WebApplication1 and therefore I have put in the resourece name as jdbc/WebApplication1 javax.sql.DataSource Container Shareable database for WebApplication1.

Am not sure what else I must do to make the connection.

Then, in my DisplayData.jsp page I've coded the following:-

<%
response.setHeader("Cache-Control", "no-cache"); //HTTP 1.1
response.setHeader("Pragma", "no-cache"); //HTTP 1.0
response.setDateHeader("Expires", 0); //prevents caching at the proxy server

<jsp:useBean id="Tutors" scope="session" class="databaseAccess.TutorsBean"/>
<jsp:setProperty name ="Tutors" property="*"/>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib prefix="x" uri="http://java.sun.com/jsp/jstl/xml" %>

<%@page language="java" import ="java.sql.*" %>

<sql:setDataSource driver="com.mysql.jdbc.Driver" url="jdbc:mysql://
   localhost:3306/pract1" user="root" password="123"/>

<sql:query var="tutors" dataSource= "jdbc/WebApplication1" maxRows="5">
Select *, GROUP_CONCAT(subject.subjectName SEPARATOR \", \") AS Subj from tutors LEFT JOIN subinter ON subinter.tutorsID = tutors.id  LEFT JOIN subject ON subinter.subjectID = subject.id GROUP BY tutors.id");
  
</sql:query>



<h5>Members Record</h5>
<table border ="1" cellspacing="0" cellspacing="0" align="center" class="bordered">
    <tr><td><b>Member No</b></td>
        <td><b>NRIC</b></td>
        <td><b>Name</b></td>
        <td><b>Email</b></td>

    </tr>

   <c:forEach var="row" items="${TutorsBean.tutors.rows}">
<tr>
<td><c:out value="${row.ID}" /></td>
<td><c:out value="${row.strTutorName}" /></td>
<td><c:out value="${row.strNRICNO}" /></td>
<td><c:out value="${row.strEmail}" /></td>

</tr>
</c:forEach>
</table>

Am not sure why the error message from Netbean IDE:
http status 500

org.apache.jasper.JasperException: An exception occurred processing JSP page /DisplayData.jsp at line 30

I suspect the database part is still not set up probably but I'm not sure what I have to do to make the table display.

Hope some one can tell me what's wrong or what have to be done.

Million thanks.

[Khalid Ali]

What is the exception?
For some basic java and mysql setup issues and there resolutions, please read the sticky it the top of this forum
Troubleshooting guide for some basic....

[pjromdev]

Also, you might need mysql connectors...
http://www.mysql.com/products/connector/
but i don't know if that applies in this case.

[pjromdev]

Just another thing- you might want to think twice about putting the database connection logic in with the view portion of your web page...
I think the proper methodology (one that allows you to easily scale your site and modify in the future) would be to use an 'ini' file for the database login information, and then create a jsp custom tag to use a servlet that has all the database access logic in it.

Try looking into the MVC style of programming.