Knowledgebase
How to connect to MySQL with JSP/JDBC?
Posted by Customer Service on 22 June 2005 11:31 AM
Use this test code:
<%@ page import="java.sql.*" %>
<% Connection connection = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost/<DATABASE NAME>?user=<USERNAME>&password=<PASSWORD>");
Statement sql = connection.createStatement();
ResultSet result = sql.executeQuery("SELECT field FROM table");
while(result.next() )
{
out.println(result.getString("field") + "<br />");
} %>

If you need further assistance, open a trouble ticket.
(837 vote(s))
Helpful
Not helpful

Comments (3)
John Weidner
27 January 2006 10:28 PM
You can also use the connection pooling that Tomcat provides. Here's how got it to work with tomcat 5.5.9

Add the resource to your Context definition (in server.xml)

<Resource name="jdbc/Pool1" auth="Container" type="javax.sql.DataSource"
maxActive="40" maxIdle="10" maxWait="100"
username="studysta_user1" password="abcdefg" driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost/studysta_studystack"/>

Then, add a corresponding resource-ref to your web.xml

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/Pool1</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Finally, in a servlet or JSP page, you can get a connection from the pool using:

// Initialize the "pool"
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource dataSource = (DataSource) envCtx.lookup("jdbc/Pool1");


// Allocate and use a connection from the pool
Connection conn = dataSource.getConnection();


// code to use connection


conn.close();
Joel Davis
30 May 2006 05:09 PM
Instead of adding something to the server.xml file, a DBTest/META-INF/context.xml can be used for jsp and jsf. This will minimize the problems.

DBTest/META-INF/context.xml:

<Context path="/DBTest" docBase="DBTest"
debug="5" reloadable="true" crossContext="true" override="true">

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" poolPreparedStatements="true" removeAbandoned="true"
removeAbandonedTimeout="300" logAbandoned="true"
username="username" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/testDB?autoReconnect=true"/>
</Context>

DBTest/WEB-INF/web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<description>MySQL Test App</description>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

<servlet>
<servlet-name>Faces Servlet</servlet-name>
<servlet-class>javax.faces.webapp.FacesServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
<servlet-name>Faces Servlet</servlet-name>
<url-pattern>/faces/*</url-pattern>
</servlet-mapping>
</web-app>


DBTest/WEB-INF/faces-config.xml:

<?xml version="1.0"?>
<!DOCTYPE faces-config PUBLIC
"-//Sun Microsystems, Inc.//DTD JavaServer Faces Config 1.0//EN"
"http://java.sun.com/dtd/web-facesconfig_1_0.dtd">
<faces-config>
<navigation-rule>
<from-view-id>/index.jsp</from-view-id>
<navigation-case>
<from-outcome>loginSuccess</from-outcome>
<to-view-id>/welcome.jsp</to-view-id>
</navigation-case>
<navigation-case>
<from-outcome>loginFailure</from-outcome>
<to-view-id>/sorry.jsp</to-view-id>
</navigation-case>
<navigation-case>
<from-outcome>internalError</from-outcome>
<to-view-id>/error.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/welcome.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/sorry.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/error.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<managed-bean>
<managed-bean-name>user</managed-bean-name>
<managed-bean-class>com.corejsf.UserBean</managed-bean-class>
<managed-bean-scope>session</managed-bean-scope>
</managed-bean>
</faces-config>


DBTest/WEB-INF/classes/com/corejsf/UserBean.java:

package com.corejsf;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class UserBean
{
private String name;
private String password;
private boolean loggedIn;
private Logger logger = Logger.getLogger("com.corejsf");
public String getName() { return name; }
public void setName(String newValue) { name = newValue; }
public String getPassword() { return password; }
public void setPassword(String newValue) { password = newValue; }
public String login()
{
try {
doLogin();
}catch (SQLException ex) {
logger.log(Level.SEVERE, "loginAction", ex);
return "internalError";
}
catch (NamingException ex) {
logger.log(Level.SEVERE, "loginAction", ex);
return "internal Exception";
}
if (loggedIn)
return "loginSuccess";
else
return "loginFailure";
}
public String logout()
{
loggedIn = false;
return "login";
}

public void doLogin() throws SQLException, NamingException
{
Context ctx = new InitialContext();
if(ctx == null) throw new NamingException("No Initial Context");
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB");
if(ds == null) throw new NamingException("No Data Source.");
Connection conn = ds.getConnection();
if(conn == null) throw new SQLException("No Connection.");
try {
PreparedStatement passwordQuery = conn.prepareStatement(
"SELECT password from users WHERE username = ?");
passwordQuery.setString(1, name);
ResultSet result = passwordQuery.executeQuery();
if(!result.next()) return;
String storedPassword = result.getString("password");
loggedIn = password.equals(storedPassword.trim());
}
finally
{
conn.close();
}
}
}


DBTest/WEB-INF/classes/com/corejsf/messages.properties:

title=A Database Application
enterNameAndPassword=Please enter your name and password.
name=Name
password=Password
welcome=Welcome to JavaServer Faces,
authError=Authentication Error
authError_detail=Sorry, your username/password combination was not found. \
Please try again.
internalError=Internal Error
internalError_detail=To our chagrin, an internal error has occurred. \
Please report this problem to our technical staff.
login=Login
logout=Logout
continue=Continue

DBTest/index.html:

<html>
<head>
<meta http-equiv="Refresh" content= "0; URL=index.jsp"/>
<title>Start Web Application</title>
</head>
<body>
<p>Please wait for the web application to start.</p>
</body>
</html>

DBTest/welcome.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>
<body>
<h:form>
<p>
<h:outputText value="#{msgs.welcome}"/>
<h:outputText value="#{user.name}"/>
</p>
<p>
<h:commandButton value="#{msgs.logout}" action="#{user.logout}"/>
</p>
</h:form>
</body>
</f:view>
</html>

DBTest/index.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>

<body>
<h:form>
<h1><h:outputText value="#{msgs.enterNameAndPassword}"/></h1>
<h:panelGrid columns="2">
<h:outputText value="#{msgs.name}"/>
<h:inputText value="#{user.name}"/>
<h:outputText value="#{msgs.password}"/>
<h:inputSecret value="#{user.password}"/>
</h:panelGrid>
<h:commandButton value="#{msgs.login}" action="#{user.login}"/>
</h:form>
</body>
</f:view>
</html>

DBTest/sorry.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>
<body>
<h:form>
<h1><h:outputText value="#{msgs.authError}"/></h1>
<p>
<h:outputText value="#{msgs.authError_detail}"/>!
</p>
<p>
<h:commandButton value="#{msgs.continue}" action="login"/>
</p>
</h:form>
</body>
</f:view>
</html>

DBTest/error.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>

<body>
<h:form>
<h1><h:outputText value="#{msgs.internalError}"/></h1>
<p><h:outputText value="#{msgs.internalError_detail}"/></p>
<p>

<h:commandButton value="#{msgs.continue}" action="login"/>

</p>
</h:form>
</body>
</f:view>
</html>

After you make these files with the directory structure, make a mysql database named testDB(or whatever you want), load it with usernames and passwords.

Don't forget to have all of the appropriate jsf,jsp, jstl and mysql jar files in the tomcat/common/lib directory of your server.

compile the UserBean.java file and let the class file stay in the same folder.

create the DBTest.war file. (from the DBTest main directory in dos prompt -> c:\DBTest>jar cvf DBTest.war *

Upload the jar file in your home directory and either restart the server or upload through the manager application of tomcat.

call with url: http://www.[localhost].com/DBTest/faces/index.html

I had a really hard time with this but I finally got it to work and I sincerely hope it helps a few of you out there struggling with java server faces.


Joel Davis
30 May 2006 05:09 PM
Instead of adding something to the server.xml file, a DBTest/META-INF/context.xml can be used for jsp and jsf. This will minimize the problems.

DBTest/META-INF/context.xml:

<Context path="/DBTest" docBase="DBTest"
debug="5" reloadable="true" crossContext="true" override="true">

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" poolPreparedStatements="true" removeAbandoned="true"
removeAbandonedTimeout="300" logAbandoned="true"
username="username" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/testDB?autoReconnect=true"/>
</Context>

DBTest/WEB-INF/web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<description>MySQL Test App</description>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

<servlet>
<servlet-name>Faces Servlet</servlet-name>
<servlet-class>javax.faces.webapp.FacesServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
<servlet-name>Faces Servlet</servlet-name>
<url-pattern>/faces/*</url-pattern>
</servlet-mapping>
</web-app>


DBTest/WEB-INF/faces-config.xml:

<?xml version="1.0"?>
<!DOCTYPE faces-config PUBLIC
"-//Sun Microsystems, Inc.//DTD JavaServer Faces Config 1.0//EN"
"http://java.sun.com/dtd/web-facesconfig_1_0.dtd">
<faces-config>
<navigation-rule>
<from-view-id>/index.jsp</from-view-id>
<navigation-case>
<from-outcome>loginSuccess</from-outcome>
<to-view-id>/welcome.jsp</to-view-id>
</navigation-case>
<navigation-case>
<from-outcome>loginFailure</from-outcome>
<to-view-id>/sorry.jsp</to-view-id>
</navigation-case>
<navigation-case>
<from-outcome>internalError</from-outcome>
<to-view-id>/error.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/welcome.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/sorry.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<navigation-rule>
<from-view-id>/error.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/index.jsp</to-view-id>
</navigation-case>
</navigation-rule>
<managed-bean>
<managed-bean-name>user</managed-bean-name>
<managed-bean-class>com.corejsf.UserBean</managed-bean-class>
<managed-bean-scope>session</managed-bean-scope>
</managed-bean>
</faces-config>


DBTest/WEB-INF/classes/com/corejsf/UserBean.java:

package com.corejsf;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class UserBean
{
private String name;
private String password;
private boolean loggedIn;
private Logger logger = Logger.getLogger("com.corejsf");
public String getName() { return name; }
public void setName(String newValue) { name = newValue; }
public String getPassword() { return password; }
public void setPassword(String newValue) { password = newValue; }
public String login()
{
try {
doLogin();
}catch (SQLException ex) {
logger.log(Level.SEVERE, "loginAction", ex);
return "internalError";
}
catch (NamingException ex) {
logger.log(Level.SEVERE, "loginAction", ex);
return "internal Exception";
}
if (loggedIn)
return "loginSuccess";
else
return "loginFailure";
}
public String logout()
{
loggedIn = false;
return "login";
}

public void doLogin() throws SQLException, NamingException
{
Context ctx = new InitialContext();
if(ctx == null) throw new NamingException("No Initial Context");
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB");
if(ds == null) throw new NamingException("No Data Source.");
Connection conn = ds.getConnection();
if(conn == null) throw new SQLException("No Connection.");
try {
PreparedStatement passwordQuery = conn.prepareStatement(
"SELECT password from users WHERE username = ?");
passwordQuery.setString(1, name);
ResultSet result = passwordQuery.executeQuery();
if(!result.next()) return;
String storedPassword = result.getString("password");
loggedIn = password.equals(storedPassword.trim());
}
finally
{
conn.close();
}
}
}


DBTest/WEB-INF/classes/com/corejsf/messages.properties:

title=A Database Application
enterNameAndPassword=Please enter your name and password.
name=Name
password=Password
welcome=Welcome to JavaServer Faces,
authError=Authentication Error
authError_detail=Sorry, your username/password combination was not found. \
Please try again.
internalError=Internal Error
internalError_detail=To our chagrin, an internal error has occurred. \
Please report this problem to our technical staff.
login=Login
logout=Logout
continue=Continue

DBTest/index.html:

<html>
<head>
<meta http-equiv="Refresh" content= "0; URL=index.jsp"/>
<title>Start Web Application</title>
</head>
<body>
<p>Please wait for the web application to start.</p>
</body>
</html>

DBTest/welcome.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>
<body>
<h:form>
<p>
<h:outputText value="#{msgs.welcome}"/>
<h:outputText value="#{user.name}"/>
</p>
<p>
<h:commandButton value="#{msgs.logout}" action="#{user.logout}"/>
</p>
</h:form>
</body>
</f:view>
</html>

DBTest/index.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>

<body>
<h:form>
<h1><h:outputText value="#{msgs.enterNameAndPassword}"/></h1>
<h:panelGrid columns="2">
<h:outputText value="#{msgs.name}"/>
<h:inputText value="#{user.name}"/>
<h:outputText value="#{msgs.password}"/>
<h:inputSecret value="#{user.password}"/>
</h:panelGrid>
<h:commandButton value="#{msgs.login}" action="#{user.login}"/>
</h:form>
</body>
</f:view>
</html>

DBTest/sorry.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>
<body>
<h:form>
<h1><h:outputText value="#{msgs.authError}"/></h1>
<p>
<h:outputText value="#{msgs.authError_detail}"/>!
</p>
<p>
<h:commandButton value="#{msgs.continue}" action="login"/>
</p>
</h:form>
</body>
</f:view>
</html>

DBTest/error.jsp:

<html>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<f:view>
<head>
<f:loadBundle basename="com.corejsf.messages" var="msgs" />
<title><h:outputText value="#{msgs.title}"/></title>
</head>

<body>
<h:form>
<h1><h:outputText value="#{msgs.internalError}"/></h1>
<p><h:outputText value="#{msgs.internalError_detail}"/></p>
<p>

<h:commandButton value="#{msgs.continue}" action="login"/>

</p>
</h:form>
</body>
</f:view>
</html>

After you make these files with the directory structure, make a mysql database named testDB(or whatever you want), load it with usernames and passwords.

Don't forget to have all of the appropriate jsf,jsp, jstl and mysql jar files in the tomcat/common/lib directory of your server.

compile the UserBean.java file and let the class file stay in the same folder.

create the DBTest.war file. (from the DBTest main directory in dos prompt -> c:\DBTest>jar cvf DBTest.war *

Upload the jar file in your home directory and either restart the server or upload through the manager application of tomcat.

call with url: http://www.[localhost].com/DBTest/faces/index.html

I had a really hard time with this but I finally got it to work and I sincerely hope it helps a few of you out there struggling with java server faces.