Important Notice - Forums is archived
To simplify things and help our users to be more productive, we have archived the current forum and focus our efforts on helping developers on Stack Overflow. You can post new questions on Stack Overflow or join our Discord channel.

Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.
Connection Pooling Sql Server with Vaadin
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:
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)
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.
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)
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?
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.
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));
}
Then to use it in the Views, I did this:
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
}
But well, if anybody has any clue how to do it in Tomcat, I'd still like to know.