Connection Pool for Tomcat in Eclipse for MS Sql Server

3.2k views Asked by At

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:

enter image description here

And I want to access a table in DB2 named Table1. So according to this I have to go into server.xml in ECLIPSE.

enter image description here

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.

enter image description here

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)
2

There are 2 answers

0
SwampDev On BEST ANSWER

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.

0
Chris M On

I believe the correct place to define your JNDI datasource is in the server.xml with an reference in the context.xml see this answer Tomcat JNDI resource name aliases