MySQL Download

sir can u pls tell me where the mysqlcmd prompt exists after installation of mysql

Posted by chidambarshastry on Monday, 02.12.07 @ 12:29pm | #7501

Hi,
I am investigating the configuration for the DB Connection Pooling described in
http://tomcat.apache.org/tomcat-4.1-doc/printer/jndi-datasource-examples-howto.html for MySQL database.

But I am not sure some parameters:

1. server.xml configuration
Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to $CATALINA_HOME/conf/server.xml.

Add this in between the </Context> tag of the examples context and the </Host> tag closing the localhost definition.

<Context path="/DBTest" docBase="DBTest" debug="5" reloadable="true"
crossContext="true"><!-- Here, I don't know the exact path for me and don't
understand the other parameters -->

<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_DBTest_log." suffix=".txt" timestamp="true"/><!-- This is probably not that important -->

<Resource name="jdbc/TestDB" auth="Container"
type="javax.sql.DataSource"/><!-- So it means that for every database I am using, I need to config it here. Is that right? -->

<ResourceParams name="jdbc/TestDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourc#*$!</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 --> <!-- This part, I can replace them with real username and password of MySQL -->
<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. -->
<!-- For this, I don't know what is the value for my purpose? -->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>

web.xml configuration
Now create a WEB-INF/web.xml for this test application.

<?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>I don't understand this. I have some many application programs.
How should I deal with this web.xml configuration?
To configure a DBCP DataSource so that abandoned dB connections are removed and recycled add the following paramater to the ResourceParams configuration for your DBCP DataSource Resource:
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
When available db connections run low DBCP will recover and recyle any
abandoned dB connections it finds. The default is false. Use the
removeAbandonedTimeout parameter to set the number of seconds a dB
connection has been idle before it is considered abandoned.
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
The default timeout for removing abandoned connections is 300 seconds.
The logAbandoned parameter can be set to true if you want DBCP to log a stack trace of the code which abandoned the dB connection resources.
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
The default is false. I probably need to configure this abandoned DB connection recycling mechanism as well.Finally, in order to use the following code:

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.InitialContext;
import javax.sql.DataSource;

public class MyServletJspOrEjb {

public void doSomething() throws Exception {
/*
* Create a JNDI Initial context to be able to
* lookup the DataSource
*
* In production-level code, this should be cached as
* an instance or static variable, as it can
* be quite expensive to create a JNDI context.
*
* Note: This code only works when you are using servlets
* or EJBs in a J2EE application server. If you are
* using connection pooling in standalone Java code, you
* will have to create/configure datasources using whatever
* mechanisms your particular connection pooling library
* provides.
*/

InitialContext ctx = new InitialContext();

/*
* Lookup the DataSource, which will be backed by a pool
* that the application server provides. DataSource instances
* are also a good candidate for caching as an instance
* variable, as JNDI lookups can be expensive as well.
*/

DataSource ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");

/*
* The following code is what would actually be in your
* Servlet, JSP or EJB 'service' method...where you need
* to work with a JDBC connection.
*/

Connection conn = null;
Statement stmt = null;

try {
conn = ds.getConnection();

/*
* Now, use normal JDBC programming to work with
* MySQL, making sure to close each resource when you're
* finished with it, which allows the connection pool
* resources to be recovered as quickly as possible
*/

stmt = conn.createStatement();
stmt.execute("SOME SQL QUERY");

stmt.close();
stmt = null;

conn.close();
conn = null;
} finally {
/*
* close any jdbc instances here that weren't
* explicitly closed during normal code path, so
* that we don't 'leak' resources...
*/

if (stmt!= null) {
try {
stmt.close();
} catch (sqlexception sqlex) {
// ignore -- as we can't do anything about it here
}

stmt = null;
}

if (conn!= null) {
try {
conn.close();
} catch (sqlexception sqlex) {
// ignore -- as we can't do anything about it here
}

conn = null;
}
}
}
}

Also, I notice that I need to download some jar files for this? what are them exactly?

I am using several databases for my website, do I need to configuration each database for this? like specify the max connection for each database?

Can anybody give me some ideas on this? Is ther any other method for connection pooling?can we edit server.xml stored in a remote server? Thanks very very much!

Regards
Abey

Posted by Abey on Wednesday, 01.17.07 @ 11:28am | #3656