connection pool

Vaadin 6 / 7 / 8 Framework Data Binding
1

May I get some recommendation how to do connection pool for Mysql?

Tapani

2

Use Spring framework

3

Are you using Hibernate for JPA based persistence?

4

I have this Eclipse:
eclipse-jee-galileo-SR1-macosx-cocoa-x86_64,
and Vaadin plugin 6.1.5.

I have not learned to use Hibernate or JPA and code all SQL quesies manually. Application goes like this:

public class DataConn {

	private Connection con;
	private Statement stmt;
	private ResultSet rs = null;

	public DataConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/tulliauto?jdbcCompliantTruncation=false";
			con = DriverManager.getConnection(url, "abcdef", "ghijklm");
			stmt = con.createStatement();
		} catch (Exception e) {
			System.out.println(e.getMessage() + "\n Class not found Exception.");
			System.out.println("getConnection poikkeus:" + " " + e);
		}
	}

	public Vector<Object[]> palautaRivitDcObjectArray(String sqlL) {

		Object[] COLS = new Object[17]
;
		Vector<Object[]> palautaVector = new Vector<Object[]
>();

		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sqlL); // ok
			while (rs.next()) {
				COLS = new Object[17]
;
				String sMerkki = rs.getString("merkki");
...
5

Using a Java EE 6 container, such as Glassfish

6

You can also use the Apache Commons Database Connection Pool. If you’re using Tomcat it even comes bundled with the installation package (
$CATALINA_HOME/lib/tomcat-dbcp.jar
).


Apache Tomcat 6 - Database Connection Pool (DBCP) Configurations


Apache Commons - DBCP

7

Hey!

I also want to do SQL queries with Class.forName(“com.mysql.jdbc.Driver”);

I added the mysql.jar to the build path of my project. But when I want to debug with Tomcat Server - there is an error:
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Is this a Tomcat issue or is my implementation wrong?

My code is very similar to the following one.

Please help me :slight_smile:

Tobi

//EDIT:
LOL few minutes after writing a request I added a classpath node to the tomcat configuration and … it works :slight_smile: GREAT

8

I have added “mysql-connector-java-5.1.10-bin.jar” to WebContent/WEB-INF/lib

9

The following are notes I made while setting up a connection pool which would ultimately be accessed from a Vaadin project:



Connection Pooling/Tomcat/Eclipse Notes

This not only is more efficient (the server handles the pooling) but it separates the datasource connection code from the app-- it doesn’t even have to have the connector driver (that sits in the server environment).

/context.xml

<Resource name="jdbc/worldschema" auth="Container"
   type="javax.sql.DataSource" 
   username="root" password=""
   driverClassName="com.mysql.jdbc.Driver"
   url="jdbc:mysql://localhost:3306/world" 
   maxActive="8"    />

  • The above supposedly tells Tomcat to provide connection pooling to the MySql schema “world” with a JNDI name “jdbc/worldschema”.

  • eclipse tomcat runtime server has its own set of xml config files even though it uses the tomcat server installed on c:/ (which has its own set of xml config files).

  • There are other attributes that could be set controlling the pool.


Connector driver

/lib/
mysql-connector-java-5.0.8-bin.jar


Quick and dirty code to test database access

This is a Vaadin web app hello world project which I just tacked on some data access code.

Notes

  • If the JNDI name changes, we have to change code so it would be best to put “jdbc/worldschema” in a properties file-- that way code doesn’t have to change.
  • Tomcat forces me to use BasicDataSource while all the other code samples used DataSource-- so this code may not be portable to another server as is.

[font=Courier New]
package com.example.mydbtest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import com.vaadin.Application;
import com.vaadin.ui.*;

public class MydbtestApplication extends Application {
@Override
public void init() {
Window mainWindow = new Window(“Mydbtest Application”);
Label label = new Label(“Hello Vaadin user”);
mainWindow.addComponent(label);
setMainWindow(mainWindow);
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
Context ctx = (Context) new InitialContext().lookup(“java:comp/env”);
BasicDataSource ds = (BasicDataSource)ctx.lookup(“jdbc/worldschema”);
con = ds.getConnection();
con.setAutoCommit(false);
ps = con.prepareStatement(“SELECT * from country”);
rs = ps.executeQuery();
while (rs.next()) {
String name = rs.getString(“Name”);
System.out.println(name);
}
} catch( Exception e) {
e.printStackTrace();
}
finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
}
[/font]