Hi Bagus,
Am Donnerstag, 12. Mai 2005 00:38 schrieb Bagus:
> I've now set up Tomcat 5.5.4 and have installed
> mysql-standard-4.1.9-unknown-freebsd4.7-i386 on my Freebsd 5.3 box
> intel box.
> I've loaded some databases and tables into the database.
>
> How do I get them to work together with jsp?
>
> Can anyone point me to a short-sweet tutorial that can get me from
> here to there?
>
> I'm not sure if I need to install jdbc and the mysql driver or if
> those are included in the tomcat release.
>
> A tutorial that takes me through all that would be great.
Firstly, you have to install the MySQL JDBC driver. That means you have
to download it from here:
http://dev.mysql.com/doc/mysql/en/java-connector.html
Then untar it somewhere outside the $CATALINA_HOME and only put the
mysql-connector-java-3.1.8-bin.jar file into
$CATALINA_HOME/common/lib/. Put it only there and nowhere else!
Secondly, nest the following Resource definition into the Context
element of your web application's context file:
<Resource
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
logAbandoned="true"
maxActive="100"
maxIdle="30"
maxWait="10000"
name="jdbc/{ResourceName}"
password="{UnencryptedUserPassword}"
removeAbandoned="true"
removeAbandonedTimeout="300"
type="javax.sql.DataSource"
url="jdbc:mysql://localhost:3306/{DatabaseName}?autoReconnect=true"
username="{DatabaseUserName}"
/>
You have to set the following parameters accordingly:
- name/{ResourceName}:
How you want to call your resource is up to you, i.e.
"jdbc/{DatabaseName}" where {DatabaseName} stands for the name of the
database which this resource will connect to.
- url/{DatabaseName}:
The hostname should normally be "localhost" and the port "3306";
you only have to change this, if your database doesn't run on
"localhost" and port 3306;
most importantly you have to set the database name right; here you
tell the resource which database from the mysql server to use;
it is vitally important to set the database name absolute correctly,
as it is also case-sensitive (dataBase != database !!!); if it is no
absolutely correct, your database connection will fail!
- username/{DatabaseUserName}:
A database user which has permission to access the database,
specified in the url. If necessary, grant rights in the mysql.user
table and in other tables of the mysql database.
- password/{UnencryptedUserPassword}:
The unencrypted password of the mysql user, specified with username.
Thirdly, you have set the following resource reference in the web.xml of
your web application:
<resource-ref>
<description>MySQL Database Connection</description>
<res-ref-name>jdbc/{ResourceName}</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
The description, you choose, is free. The res-ref-name has to be the
name of the resource defined in your Context file (see above).
After you have made all these configurations, you have to restart Tomcat
to activiate the new resource. Please note, that the above
configuration uses the Tomcat DBCP:
http://jakarta.apache.org/tomcat/tomcat-5.5-doc/jndi-datasource-examples-howto.html
And finally, you will find a short test script at the bottom of this
mail which I have used to test my MySQL database connection. It will
show you the tables in the database if the connection was established
successfully and the sql query was executed successfully as well. Don't
forget to set the {ResourceName} correctly in line 41 of the script.
If you should run Tomcat using the Security Manager, you have to grant
the necessary runtime and network-socket permissions for the connection
in the §CATALINA_HOME/conf/catalina.policy file. I don't think that you
run Tomcat with the Security Manager, but if you should, just drop a
line. Yesterday I have figured out, which permission I have to set to
connect to a MySQL database while the Security Manager is running.
Good luck!
Lutz
<[EMAIL PROTECTED] contentType="text/html"%>
<[EMAIL PROTECTED] pageEncoding="UTF-8"%>
<%@ page import="javax.naming.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="java.sql.*" %>
<%--
The taglib directive below imports the JSTL library. If you uncomment
it,
you must also add the JSTL library to the project. The Add Library...
action
on Libraries node in Projects view can be used to add the JSTL 1.1
library.
--%>
<%--
<[EMAIL PROTECTED] uri="http://java.sun.com/jsp/jstl/core"; prefix="c"%>
--%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd";>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>JSP Page</h1>
<%
InitialContext cxt = null;
DataSource ds = null;
Connection conn = null;
Statement sql = null;
ResultSet res = null;
try {
cxt = new InitialContext();
ds = (DataSource)
cxt.lookup("java:/comp/env/jdbc/{ResourceName}");
out.print(ds);
conn = ds.getConnection();
sql = conn.createStatement();
sql.execute("show tables;");
res = sql.getResultSet();
out.print("<ul>");
while (res.next()) {
out.print("<li style=\"color: #00ff00;
\">"+res.getString(1)+"</li>");
}
out.print("</ul>");
res.close();
res = null;
sql.close();
sql = null;
conn.close();
conn = null;
} catch (NamingException namE) {
out.print("<p>NamingException:<br><b style=\"color: #ff0000;
\">"+namE+"</b></p>");
} catch (SQLException sqlE) {
out.print("<p>SQLException:<br><b style=\"color: #ff0000;
\">"+sqlE+"</b></p>");
} catch (NullPointerException nulE) {
out.print("<p>NullPointerException:<br><b style=\"color: #ff0000;
\">"+nulE+"</b></p>");
} catch (Exception E) {
out.print("<p>Exception:<br><b style=\"color: #ff0000;
\">"+E+"</b></p>");
} finally {
if (res != null) {
try { res.close(); } catch (SQLException e) { ; }
res = null;
}
if (sql != null) {
try { sql.close(); } catch (SQLException e) { ; }
sql = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
%>
<%--
This example uses JSTL, uncomment the taglib directive above.
To test, display the page like this:
index.jsp?sayHello=true&name=Murphy
--%>
<%--
<c:if test="${param.sayHello}">
<!-- Let's welcome the user ${param.name} -->
Hello ${param.name}!
</c:if>
--%>
</body>
</html>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
