Re: database - jdbc setup help

[Lutz Zetzsche]

Hi Bagus,

Am Freitag, 13. Mai 2005 04:04 schrieb Bagus:
> Wow, great.
>
> That worked like a charm. After following your simple instructions,
> your script listed my database tables. Amazing!

Many thanks for the compliment. :-) I have worked this out for me from 
the various how-tos. The problem is that most of them presume that you 
know some prerequisites. And a newbie can fail there. So I have 
documented it with a working example for me once and forever.


> I don't quite get the JSTL stuff tho... Your script says:
> > <%--
> > 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"%>
> > --%>
>
> So where are the "Projects View", "Libraries Node", "Add Library
> action", etc. I was thinking maybe in the tomcat admin or manager web
> interfaces, but couldn't find it.
>
> Thanks,

Well, I created this test page with NetBeans IDE and just changed to the 
default page what I needed to. The JSTL things are added by NetBeans. 

However, if you would like to get JSTL to work you only have to download 
it somewhere and put the classes into the suitable classes resp. lib 
directory. As I haven't used JSTL yet, I have no further details but I 
am sure you can easily work it out with Google.


Best wishes

Lutz

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: database - jdbc setup help

[Bagus]

Wow, great.

That worked like a charm. After following your simple instructions, your
script listed my database tables. Amazing!

I don't quite get the JSTL stuff tho... Your script says:

> <%--
> 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"%>
> --%>

So where are the "Projects View", "Libraries Node", "Add Library action",
etc. I was thinking maybe in the tomcat admin or manager web interfaces, but
couldn't find it.

Thanks,

b.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: database - jdbc setup help

[Geoff Wiggs]

Lutz,

Thank you for the excellent primer on Tomcat DB setup.  I'm going to keep
this around forever.  Quick question, how can you tell that it is really
using the db pool?  Is there a monitoring tool somewhere that would show the
number of connections in use?

Geoff Wiggs
Orcas Technologies, Inc


-Original Message-
From: Lutz Zetzsche [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 12, 2005 12:14 AM
To: Tomcat Users List
Subject: Re: database - jdbc setup help

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:



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: database - jdbc setup help

[Terence M. Bandoian]

For information about JDBC, you may want to try the Sun JDBC tutorial
 -
http://java.sun.com/docs/books/tutorial/jdbc/

-Terence

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: database - jdbc setup help

[Lutz Zetzsche]

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:



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:


MySQL Database Connection
jdbc/{ResourceName}
javax.sql.DataSource
Container


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"%> 
--%>

http://www.w3.org/TR/html4/loose.dtd";>




JSP Page



JSP Page


<%

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("");
while (res.next()) {
  out.print(""+res.getString(1)+"");
}