This is a quick reiteration of key points found in the link below..
make sure you provide the GRANT for insert/select/update to the user you will
be using e.g.
mysql> GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
-> IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
-> id int not null auto_increment primary key,
-> foo varchar(25),
-> bar int);
JDBC access...
I assume you have these resource statements either in applicationContext.xml or
server.xml
<Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/TestDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- MySQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>javauser</value>
</parameter>
<parameter>
<name>password</name>
<value>javadude</value>
</parameter>
<!-- Class name for mm.mysql JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<!-- The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
-->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>
lets not forget web.xml
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd";>
<web-app>
<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>
</web-app>
Now finally using the JDBC reference in the java class
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
DataSource ds =
(DataSource)ctx.lookup(
"java:comp/env/jdbc/TestDB");
InitialContext initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup("java:comp/env/my-datasource");
Connection conn = ds.getConnection();
JNDI access:
ApplicationContext.xml or server.xml first the prelim ConnectionPool params
<Environment name="tyrexDomainConfig" type="java.lang.String"
value="domain-config.xml"/>
<Environment name="tyrexDomainName" type="java.lang.String" value="myDomain"/>
Then in the same file the myDataSource
<Resource name="my-datasource" auth="Container" type="tyrex.resource.Resource"/>
<ResourceParams name="my-datasource">
<parameter>
<name>name</name>
<value>myDataSource</value>
</parameter>
</ResourceParams>and in $TOMCAT_HOME/conf/catalina.policy allow JNDI access to
the described resource
permission org.apache.naming.JndiPermission "jndi://comp.env/my-datasource";
Now the test in java via my-datasource
InitialContext initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup("jndi:comp/env/my-datasource");
Connection conn = ds.getConnection();
More information on configuration and deployment to MySQL DB available at
http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html
HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business
of Sender. This transmission is of a confidential nature and Sender does not
endorse distribution to any party other than intended recipient. Sender does
not necessarily endorse content contained within this transmission.
> Date: Wed, 13 Aug 2008 22:38:32 +0200
> From: [EMAIL PROTECTED]
> Subject: database application
> To: users@tomcat.apache.org
>
> Hi,
>
> Please help me.
>
> I use this introduction:
> http://localhost:8180/tomcat-docs/jndi-datasource-examples-howto.html
>
> database: mysql
> A simple java application works (see PS)
> [EMAIL PROTECTED]:/mnt/hda3/tyuk/dtest/java/database# java DbTableShow2
> +---------------+---------------+---------------+
> | id | foo | bar |
> +---------------+---------------+---------------+
> | 1 | hello | 12345 |
> | 2 | hellox | 123456 |
> +---------------+---------------+---------------+
>
>
> However the tomcat application fails:
> ----------------------------------------------
> description The server encountered an internal error () that prevented it
> from fulfilling this request.
> exception
> javax.servlet.ServletException: Unable to get connection, DataSource invalid:
> "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of
> class '' for connect URL 'null'"
>
> org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
>
> org.apache.jasper.runtime.PageContextImpl.access$11(PageContextImpl.java:784)
>
> org.apache.jasper.runtime.PageContextImpl$12.run(PageContextImpl.java:766)
> java.security.AccessController.doPrivileged(Native Method)
>
> org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:764)
> org.apache.jsp.jsp.test_jsp._jspService(test_jsp.java:81)
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
>
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
> org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> java.lang.reflect.Method.invoke(Method.java:597)
> org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:243)
> java.security.AccessController.doPrivileged(Native Method)
> javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
> org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:272)
>
> org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:161)
> ---------------
>
> Here my configuration file (/etc/tomcat5/server.xml:
> ----------------
> <!-- Server Configuration File for Tomcat 5 on Debian
> You can find a more complete example in /usr/share/doc/tomcat5/examples/
> -->
> <!-- Note that component elements are nested corresponding to their
> parent-child relationships with each other -->
> <!-- A "Server" is a singleton element that represents the entire JVM,
> which may contain one or more "Service" instances. The Server
> listens for a shutdown command on the indicated port.
> Note: A "Server" is not itself a "Container", so you may not
> define subcomponents such as "Valves" or "Loggers" at this level.
> -->
>
> <Server port="8005" shutdown="SHUTDOWN" debug="0">
>
> <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"
> debug="0"/>
> <Listener
> className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"
> debug="0"/>
>
> <!-- Global JNDI resources -->
> <GlobalNamingResources>
>
> <!-- Test entry for demonstration purposes -->
> <Environment name="simpleValue" type="java.lang.Integer" value="30"/>
>
> <!-- Editable user database that can also be used by
> UserDatabaseRealm to authenticate users -->
> <Resource name="UserDatabase" auth="Container"
> type="org.apache.catalina.UserDatabase"
> description="User database that can be updated and saved">
> </Resource>
> <ResourceParams name="UserDatabase">
> <parameter>
> <name>factory</name>
> <value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
> </parameter>
> <parameter>
> <name>pathname</name>
> <value>conf/tomcat-users.xml</value>
> </parameter>
> </ResourceParams>
>
> </GlobalNamingResources>
>
> <!-- A "Service" is a collection of one or more "Connectors" that share
> a single "Container" (and therefore the web applications visible
> within that Container). Normally, that Container is an "Engine",
> but this is not required.
>
> Note: A "Service" is not itself a "Container", so you may not
> define subcomponents such as "Valves" or "Loggers" at this level.
> -->
> <!-- Define the Tomcat Stand-Alone Service -->
> <Service name="Tomcat-Standalone">
> <!-- A "Connector" represents an endpoint by which requests are received
> and responses are returned. Each Connector passes requests on to the
> associated "Container" (normally an Engine) for processing.
> -->
> <!-- Define a non-SSL Coyote HTTP/1.1 Connector on port 8180 -->
> <Connector className="org.apache.coyote.tomcat5.CoyoteConnector"
> port="8180" minProcessors="5" maxProcessors="75"
> enableLookups="true" acceptCount="10" debug="0"
> connectionTimeout="20000" useURIValidationHack="false" />
>
> <!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 -->
> <Connector className="org.apache.coyote.tomcat5.CoyoteConnector"
> port="8009" minProcessors="5" maxProcessors="75"
> enableLookups="true" acceptCount="10" debug="0"
> connectionTimeout="20000" useURIValidationHack="false"
>
> protocolHandlerClassName="org.apache.jk.server.JkCoyoteHandler"/>
>
> <!-- An Engine represents the entry point (within Catalina) that processes
> every request. The Engine implementation for Tomcat stand alone
> analyzes the HTTP headers included with the request, and passes them
> on to the appropriate Host (virtual host). -->
>
> <!-- Define the top level container in our container hierarchy
> The name must be Catalina for correct function of the admin webapp
> -->
> <Engine name="Catalina" defaultHost="localhost" debug="0">
>
> <!-- Global logger unless overridden at lower levels -->
> <Logger className="org.apache.catalina.logger.FileLogger"
> prefix="catalina_" suffix=".log" timestamp="true"/>
>
> <!-- Because this Realm is here, an instance will be shared globally -->
> <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
> debug="0" resourceName="UserDatabase"/>
>
> <!-- Define the default virtual host -->
> <Host name="localhost" debug="0" appBase="webapps"
> unpackWARs="true" autoDeploy="true">
>
> <!-- Logger shared by all Contexts related to this virtual host. -->
> <Logger className="org.apache.catalina.logger.FileLogger"
> directory="logs" prefix="localhost_" suffix=".log"
> timestamp="true"/>
>
> <Context path="/jli/Q2_JSPs"
> docBase="/home/en/public_html/jsp/Beispiele"
> reloadable="true" />
>
> <Context path="/jli/Q3_JSPs"
> docBase="/home/en/public_html/jsp/Kapitel_4/Beispiele"
> reloadable="true" />
>
> <Context path="/jli/Q4_JSPs"
> docBase="/home/en/public_html/jsp/Kapitel_5/Beispiele"
> reloadable="true" />
>
> <!-- This is where I test -->
> <Context path="/jli/DB"
> docBase="/home/en/public_html/jsp/DB"
> reloadable="true" />
>
> <Context path="/ajax/k2"
> docBase="/home/en/public_html/jsp/ajax/Beispiele/kap2"
> reloadable="true" />
>
> <!-- This is what I added -->
>
> <Context path="/DBTest" docBase="DBTest"
> debug="5" reloadable="true" crossContext="true">
>
> <Logger className="org.apache.catalina.logger.FileLogger"
> prefix="localhost_DBTest_log." suffix=".txt"
> timestamp="true"/>
>
> <Resource name="jdbc/TestDB"
> auth="Container"
> type="javax.sql.DataSource"/>
>
> <ResourceParams name="jdbc/TestDB">
> <parameter>
> <name>factory</name>
>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
>
> <!-- Maximum number of dB connections in pool. Make
> sure you
> configure your mysqld max_connections large enough
> to handle
> all of your db connections. Set to 0 for no limit.
> -->
> <parameter>
> <name>maxActive</name>
> <value>100</value>
> </parameter>
>
> <!-- Maximum number of idle dB connections to retain in
> pool.
> Set to -1 for no limit. See also the DBCP
> documentation on this
> and the minEvictableIdleTimeMillis configuration
> parameter.
> -->
> <parameter>
> <name>maxIdle</name>
> <value>30</value>
> </parameter>
>
> <!-- Maximum time to wait for a dB connection to become
> available
> in ms, in this example 10 seconds. An Exception is
> thrown if
> this timeout is exceeded. Set to -1 to wait
> indefinitely.
> -->
> <parameter>
> <name>maxWait</name>
> <value>10000</value>
> </parameter>
>
> <!-- MySQL dB username and password for dB connections
> -->
> <parameter>
> <name>username</name>
> <value>javauser</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>javadude</value>
> </parameter>
>
> <!-- Class name for the old mm.mysql JDBC driver -
> uncomment this entry and comment next
> if you want to use this driver - we recommend
> using Connector/J though
> <parameter>
> <name>driverClassName</name>
> <value>org.gjt.mm.mysql.Driver</value>
> </parameter>
> -->
>
> <!-- Class name for the official MySQL Connector/J
> driver -->
> <parameter>
> <name>driverClassName</name>
> <value>com.mysql.jdbc.Driver</value>
> </parameter>
>
> <!-- The JDBC connection url for connecting to
> your MySQL dB.
> The autoReconnect=true argument to the url
> makes sure that the
> mm.mysql JDBC Driver will automatically
> reconnect if mysqld closed the
> connection. mysqld by default closes idle
> connections after 8 hours.
> -->
> <parameter>
> <name>url</name>
>
> <value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
> </parameter>
> </ResourceParams>
> </Context>
> <!-- End of This is what I added -->
>
> </Host>
> </Engine>
> </Service>
> </Server>
> -------------------------------------
> This is my /home/en/public_html/jsp/DB/WEB-INF/web.xml file:
> ---------------------
> <web-app 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";
> version="2.4">
> <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>
> </web-app>
> ---------------
>
> I copied jstl.jar and standard.jar from tomcat into
> /home/en/public_html/jsp/DB/WEB-INF/lib
>
> /home/en/public_html/jsp/DB/jsp/test.jsp looks:
> ----------------------
> <%@ taglib uri="http://java.sun.com/jsp/jstl/sql"; prefix="sql" %>
> <%@ taglib uri="http://java.sun.com/jsp/jstl/core"; prefix="c" %>
>
> <sql:query var="rs" dataSource="jdbc/TestDB">
> select id, foo, bar from testdata
> </sql:query>
>
> <html>
> <head>
> <title>DB Test</title>
> </head>
> <body>
>
> <h2>Results</h2>
>
> <c:forEach var="row" items="${rs.rows}">
> Foo ${row.foo}<br/>
> Bar ${row.bar}<br/>
> </c:forEach>
>
> </body>
> </html>
> -------------------------
>
> Thanks a lot in advance: Eleonora
>
>
> PS:
> Working java program:
> ---------------------------------
> // DbTableShow2.java
>
> import java.io.*;
> import java.sql.*;
>
> public class DbTableShow2
> {
> public static void main( String[] argv )
> {
> String sDbDrv=null, sDbUrl=null, sTable=null, sUsr="", sPwd="";
> if( 3 <= argv.length ) {
> sDbDrv = argv[0];
> sDbUrl = argv[1];
> sTable = argv[2];
> if( 4 <= argv.length ) sUsr = argv[3];
> if( 5 <= argv.length ) sPwd = argv[4];
> } else {
> // try {
> BufferedReader in = new BufferedReader(
> new InputStreamReader( System.in ) );
> // System.out.println( "Name des Datenbanktreibers eingeben (z.B.
> com.mysql.jdbc.Driver):" );
> // sDbDrv = in.readLine();
> sDbDrv = "com.mysql.jdbc.Driver";
> // System.out.println( "Url der Datenbank eingeben (z.B.
> jdbc:mysql://localhost:3306/MeineDb):" );
> // sDbUrl = in.readLine();
> // sDbUrl = "jdbc:mysql://localhost:3306/employees";
> sDbUrl = "jdbc:mysql://localhost:3306/javatest?autoReconnect=true";
> // System.out.println( "Name der Tabelle eingeben (z.B.
> MeineTestTabelle):" );
> // sTable = in.readLine();
> // sTable = "employee_data";
> sTable = "testdata";
> // System.out.println( "Benutzername (z.B. root):" );
> // sUsr = in.readLine();
> sUsr = "javauser";
> // System.out.println( "Passwort (z.B. mysqlpwd):" );
> // sPwd = in.readLine();
> sPwd = "javadude";
> // } catch( IOException ex ) {
> // System.out.println( ex );
> // }
> }
> if( null != sDbDrv && 0 < sDbDrv.length() &&
> null != sDbUrl && 0 < sDbUrl.length() &&
> null != sTable && 0 < sTable.length() ) {
> Connection cn = null;
> Statement st = null;
> ResultSet rs = null;
> try {
> // Select fitting database driver and connect:
> Class.forName( sDbDrv );
> cn = DriverManager.getConnection( sDbUrl, sUsr, sPwd );
> st = cn.createStatement();
> rs = st.executeQuery( "select * from " + sTable );
> // Get meta data:
> ResultSetMetaData rsmd = rs.getMetaData();
> int i, n = rsmd.getColumnCount();
> // Print table content:
> for( i=0; i<n; i++ )
> System.out.print( "+---------------" );
> System.out.println( "+" );
> for( i=1; i<=n; i++ ) // Attention: first column with 1 instead of > 0
> System.out.print( "| " + extendStringTo14( rsmd.getColumnName( i )
> ) );
> System.out.println( "|" );
> for( i=0; i<n; i++ )
> System.out.print( "+---------------" );
> System.out.println( "+" );
> while( rs.next() ) {
> for( i=1; i<=n; i++ ) // Attention: first column with 1 instead of > 0
> System.out.print( "| " + extendStringTo14( rs.getString( i ) ) );
> System.out.println( "|" );
> }
> for( i=0; i<n; i++ )
> System.out.print( "+---------------" );
> System.out.println( "+" );
> } catch( Exception ex ) {
> System.out.println( ex );
> } finally {
> try { if( null != rs ) rs.close(); } catch( Exception ex ) {}
> try { if( null != st ) st.close(); } catch( Exception ex ) {}
> try { if( null != cn ) cn.close(); } catch( Exception ex ) {}
> }
> }
> }
>
> // Extend String to length of 14 characters
> private static final String extendStringTo14( String s )
> {
> if( null == s ) s = "";
> final String sFillStrWithWantLen = " ";
> final int iWantLen = sFillStrWithWantLen.length();
> final int iActLen = s.length();
> if( iActLen < iWantLen )
> return (s + sFillStrWithWantLen).substring( 0, iWantLen );
> if( iActLen > 2 * iWantLen )
> return s.substring( 0, 2 * iWantLen );
> return s;
> }
> }
> -----------------------end of working java program---
>
> --
> Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
> Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
_________________________________________________________________
Your PC, mobile phone, and online services work together like never before.
http://clk.atdmt.com/MRT/go/108587394/direct/01/
