Ok I'm trying to set up a Connection Pool in a Vaadin webapp, and I hear that it's better to make Tomcat do the connection pooling. So I've been going through some Youtube videos and a lot of tutorials and I'm too bad at this to get it to work. I really need someone to help.
Does the SQL server have to be on my computer? Can it be on a different server?
Let's say I have a db:
And I want to access a table in DB2 named Table1. So according to this I have to go into server.xml in ECLIPSE.
and add
<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" />
But according to this guy, It should be in the Context.xml (I've tried just context.xml, I've tried just Server.xml and I've tried with this resource code in both.) I THINK it is asking for a META-INF > Context.xml in my Vaadin webapp, which I cannot find. This is the only Meta-Inf folder I have, and when I create a context.xml file here, it just disappears when I try to compile it.
Anyway, trying to continue with the tutorial, I need to add the drivers to my Tomcat>Lib folder so I download all of these drivers and add them.
Just for good measure because some other people are using the JDBC driver I download that and add it to my Tomcat > Lib too.
Following these notes from a guy that got Connection pooling to work in his Vaadin program, I add this in my java 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)
When I run, I get NullPointerException at the con.close() in the finally block because it's not pulling the table. Or well, I don't have a connection in the first place.
I honestly have no idea what I'm doing. I don't know if this is possible to connect to the sql server if it's not running off my computer? Every single example I see uses Localhost.
Edit: For reference, the following code works 100% ZERO PROBLEMS, but I can't get the connection pool above to work.
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)
I'd advise you to slow down. This question is a combination of several questions. Focus on just one question at a time, find your answer, and put the pieces together when you can see how they fit.
First, "Does the SQL server have to be on my computer? Can it be on a different server?". It can be on a different server, as long as you have network access to it.
Second, "Anyway, trying to continue with the tutorial, I need to add the drivers to my Tomcat>Lib folder so I download all of these drivers and add them." "These drivers" that you are referring to are JDBC drivers. If you are connecting to a Microsoft SQL Server, then the extra 'JDBC' drivers you are referring to are useless to you because they are MySQL JDBC drivers (which is a totally different database than SQL Server).
Third, you are clearly using Maven, and when you add a META-INF folder and a context.xml, it gets erased because everything in the 'target' folder in a Maven project gets rebuilt when you run a Maven build. You need to add the META-INF folder to the /src/main/webapp/ folder. Then the context.xml file will be copied over at build time to where you originally tried to put it and expected it to stay.