Connection Pooling Sql Server with Vaadin

Vaadin 6 / 7 / 8
1

I was trying to follow the notes from

this forum post

 but I couldn’t get it to work.

I’ve only just learned about connection pools so I have very little knowledge in setting it up, but I’m trying. Please don’t get too mad.

We have a MS SQL server like this:

I want to access table1 in DB2

According to a tutorial, I’m suppose to go to Eclipse’s
Server > Server.xml
 and add the following code.

<Resource name="jdbc/ServerName" auth="Container" factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" maxActive="50" maxIdle="10" maxWait="15000" username="username" password="password" url="jdbc:sqlserver://ServerName;instance=SQLEXPRESS;databaseName=DB2;" removeAbandoned="true" removeAbandonedTimeout="30" logAbandoned="true" /> However, I read

somewhere else

 it’s actually suppose to be in Context.xml? Which I can’t find in my Vaadin webapp. in

This tutorial,

 it says it should be under the meta-inf folder but Every time I create a context.xml file there, it gets eaten when I compile the program.

Next I add the drivers to my Tomcat library? I downloaded the microsoft ones from
here

And I add this code to my java program:

[code]
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Context ctx = (Context) new InitialContext().lookup(“java:comp/env”);
BasicDataSource ds = (BasicDataSource)ctx.lookup(“jdbc/ServerName”);
con = ds.getConnection();

        String sql = "select * from DB2.dbo.Table1 where [Field1]

IS NULL";
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
//Add in all info
while (rs.next()){
beanResultsList.addBean(new Bean(
rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getDate(4),
rs.getDate(5),
rs.getInt(6),
rs.getInt(7),
rs.getInt(8),
rs.getInt(9),
rs.getString(10),
rs.getString(11),
rs.getString(12),
rs.getString(13)
));
}
} catch (SQLException | NamingException e) {
e.printStackTrace();
}finally{
try { con.close(); } catch (SQLException e) {}
try { rs.close(); } catch (SQLException | NullPointerException e) {}
try { stmt.close(); } catch (SQLException e) {}
} // End finally (try catch)
[/code]And it doesn’t work, I get nullpointerexception on the Con.close() because it’s not reaching the sql server?

However, just using JTDS works fine, but it’s really slow.

[code]
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(“net.sourceforge.jtds.jdbc.Driver”);
con = DriverManager.getConnection(“jdbc:jtds:sqlserver://ServerName;instance=SQLEXPRESS”,“username”,“password”);

        String sql = "select * from DB2.dbo.Table1 where [Field1]

IS NULL";
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
//Add in all info
while (rs.next()){
beanResultsList.addBean(new Bean(
rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getDate(4),
rs.getDate(5),
rs.getInt(6),
rs.getInt(7),
rs.getInt(8),
rs.getInt(9),
rs.getString(10),
rs.getString(11),
rs.getString(12),
rs.getString(13)
));
}
} catch (SQLException | NamingException e) {
e.printStackTrace();
}finally{
try { con.close(); } catch (SQLException e) {}
try { rs.close(); } catch (SQLException | NullPointerException e) {}
try { stmt.close(); } catch (SQLException e) {}
} // End finally (try catch)
[/code]Does anybody who has gotten connection pooling to work know what’s wrong? And how do I get Context.xml in my vaadin app? And keep it there?

2

Ok well I managed to do this in the end with C3P0 and JTDS instead of using tomcat. I guess nobody knows how to do connection pooling on tomcat either so I’ll post what I did here.

I don’t think this is “best practice” but it worked for me really well. I’m not sure it will work for a larger environment though with heavy traffic.

In the UI, I just set up a ComboPooledDatasource and just passed them to each instance of the navigator.
It reduced the loadtime from 10-15 sec to 1-3 seconds! It also seems to reduce the memory it uses.

[code]
public class MyUI extends UI {
private Navigator nav = new Navigator(this,this);
private ComboPooledDataSource cpds;
@Override
protected void init(VaadinRequest vaadinRequest) {
//Pooled connection
cpds = new ComboPooledDataSource();
try {
cpds.setDriverClass(“net.sourceforge.jtds.jdbc.Driver”);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //loads the jdbc driver
cpds.setJdbcUrl( “jdbc:jtds:sqlserver://ServerName/DB1” );
cpds.setUser(“username”);
cpds.setPassword(“password”);

    nav.addView(ViewOne.NAME,new ViewOne(cpds));
    nav.addView(ViewTwo.NAME, new ViewTwo(cpds));

}
[/code]Then to use it in the Views, I did this:

[code]
public class ViewTwo extends Panel implements View {

public ViewTwo(ComboPooledDataSource cpds){
}
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

    try {
        con =  cpds.getConnection();
        String sql = "select * from DB1";
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);

//…etc etc fill in BeanItemContainer for grid etc
}
[/code]But well, if anybody has any clue how to do it in Tomcat, I’d still like to know.