Configure SQL Server

[Roger F. Gay]

I've recently made a java connection to an SQL database for the first time via jdbc, but now I want to access via a web app on Tomcat. After a couple days with a No suitable driver found error, I've come to the conclusion that Tomcat isn't using the Windows env variables to find the driver. Googling has led me to the probable problem, but pretty sketchy and some very old commentary. I think I either have to include a reference in my web apps web.xml, or a Tomcat general configuration file or both. It would be great if I could get some clear and specific instructions. If you need to see my sample web app code, click on the "No suitable driver found" link in this paragraph above.

[Rajkamal Pillai]

You would have to package the driver (jar file) you need for database connectivity along with your application (in your app lib folder).

[Vanessa Danin]

Firstly, as mentioned by Raj, ensure the driver jar (e.g. sqljdbc4.jar) is in your WEB-INF lib folder.
Then you need to refer to it correctly. I don't think that "My Computer" is synonymous with the hostname of your computer. In your URL, rather use localhost if the application and driver are installed on the same machine.
e.g. jdbc:sqlserver://localhost:1433;databaseName=MyDB

I would also recommend using Datasource rather than DriverManager to connect to the database.
Your web.xml file would need to include:
<context-param>
<description>SQL Server db connection value</description>
<param-name>dbSqlSrv</param-name>
<param-value>java:comp/env/jdbc/myChosenReference</param-value>
</context-param>
The param name above is obviously available to the ServletContext by it's getInitParameter method.
And you should have a context resource reference (e.g. in a context.xml file in your META-INF folder) which looks like this:
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="jdbc/myChosenReference" password="myPassword" type="javax.sql.DataSource" url="jdbc:sqlserver://localhost:1433;databaseName=MyDB" username="myName"/>

Importantly, don't forget to ensure port 1433 is open for SQL Server

[Roger F. Gay]

Thanks. I changed the names of everything along with username and password when I posted. I think you identified the reason I couldn't get the configurations to work. I'm not packaging in a jar / war file. I'll work on that a bit more.

But I just found a work-around that's great for now. You see, as I read about version 4 of the SQL Server driver, I read something that very clearly said that it was no longer necessary to use Class.forName(driver) to identify the driver class. I did not in fact use it for my non-servlet db connections. It was in my classpath and just using java objects (no servlets, no server), it worked just fine. It stopped working when running under Tomcat, because as someone explained, Tomcat doesn't use classpath.

But including Class.forName(driver) in the object the servlet uses to actually make the connection solved the problem immediately.

I've spent the last couple hours googling and trying variations on context.xml and web.xml, so I'll test a bit more just to be sure that has nothing to do with the fact that using Class.forName() works.

[Tim Holloway]

True, Tomcat does not use an externally-defined classpath. The Tomcat control script creates its own classpath.

If you use a a database connection pool, you define that as part of the webapp's deployment description (Context). Because the database connection pool is constructed external to the webapp, you would NOT include the database driver in the webapp's WEB-INF/lib directory, you would include it in the Tomcat lib directory.

If you use brute-force database connections, you would put the database driver in WEB-INF/lib. However, J2EE is an expensive platform to develop for, so we only recommend brute-force connections for getting started. There's a lot of overhead in creating database connections, so a webapp that expects to pay for its costs with good performance would use a connection pool (DataSource), instead.

[Roger F. Gay]

OK, I feel like I'm starting to get used to the lingo now so I'm going to take a chance. I think this will make sense. Let me know how I'm doing.

I use the servlet's init() to check to see if the connection exists. When the servlet is first created, it's going to load my connection helper class, which will be filled with static variables and methods. My initial thought was to leave a connection open for multiple users. This could work because at least at first, we don't expect high traffic. It's going to be a far cry from Amazon.com level traffic and will be pretty low I expect unless / until I can convince them to use .. (well, that's business, not technical ... never mind). Now, there have been many people who've commented (as I googled around for the past few days) that it's bad practice to leave a connection open. It should be opened, used, and closed. So, I'm guessing that the huge overhead is not literally in making the connection. It's in all the stuff leading up to making the connection ... like loading the database driver. Is that right? So, as long as I can keep that stuff in initialization steps that are not repeated, it should still perform quite well if I'm opening and closing the connection to the database.

I've designed the static helper class to check to see if a connection exists. If it does, it just returns the connection to the servlet's do method. If there is no open connection, it opens one and returns that. I can easily adapt it to open and close every time ... and the extra logic will work as a retry. Just starting to think about this part. If I'm opening and closing a single connection, it might end up closing while another user is trying to use it. Needs a bit more thinking. I've done server stuff before, with synchronized methods ... but that doesn't strike me as the easiest or most appropriate thing to do here.

[Tim Holloway]

What you are doing sounds like a stock database connection pool. You might as well use the one that comes with Tomcat, since it's already debugged. Plus it uses the J2EE standard API, so reasonably well-trained people already know how to work with it.

[Roger F. Gay]

Do you mean DBCP using the Jakarta-Commons database connection pool, by first configuring the JNDI DataSource in Tomcat? reference1 reference2

[Tim Holloway]

Roger F. Gay wrote:Do you mean DBCP using the Jakarta-Commons database connection pool, by first configuring the JNDI DataSource in Tomcat? reference1 reference2

Almost. DBCP is what comes with it, but you can plug in any compatible database connection pool. What I usually do is have my app startup code do a JNDI lookup to get the DataSource object and then cache it for the application code's benefit.

Well, actually these days what I really do is use Spring to manage an ORM system such as JPA, but it's all basically the same in the end.

[Tim Holloway]

BTW, the reason that it's not good to leave a connection open is that there are only a finite number of channels available, so every idle channel is potentially starving out some other request. Connection pools DO keep connections open (because they are expensive to open), but because the connections are shared, and because the pool can throttle connections, you achieve a net gain.

Regardless, whether you get a Connection directly or from a pool, you should get it, use it, and then close it ASAP. Closing a pooled connection returns it to the pool. Otherwise you'd end up with orphan connections and an empty pool.

[Roger F. Gay]

I'm not quite ready to use my imagination yet. You said;

Connection pools DO keep connections open

And then you said:

Regardless, whether you get a Connection directly or from a pool, you should get it, use it, and then close it ASAP.

Am I to imagine (this is the hard part for me at this point) that "Closing a pooled connection" doesn't actually mean closing the connection (from a pool life guard's point of view), but only that it "returns it to the pool" or in other words is disconnected from the app (but can later be fetched again later from the pool).

[Tim Holloway]

Correct. The "connection" that you get from the pool is a façade. Its close() method doesn't close the underlying JDBC connection, it merely returns the connection to the pool.

[Roger F. Gay]

It's a little odd really. I have experience building my own server and connections are really really fast. For higher traffic, WebSockets (or "keep-alive" http) can reduce some overhead, but the biggest issue by a long-shot is data transport time. Have to watch fetching images from disk and data distributed on multiple computers in a slow LAN kinds of things. But connections ... nearly instantaneous.

Anyways, I think in this relatively low traffic situation that I'm facing right now, I could either leave one connection open or give every user their own connection. If making the connection really is very expensive, then keeping a connection open is a simple solution. Since there's only one, there's no confusion. (And frankly, I've handled much more complex tasks without leaking ...) If connections are as quick as my server, then the simplest thing would be to give every user their own connection and close it when done. In addition, I can AJAX to open the connection as soon as a new user accesses the site home page. Surely, they'll have a connection already by the time the app is actually trying to access the database.

[Tim Holloway]

Don't mistake database connections for HTTP connections. Database connections are more like what you get when you log in a remote terminal session. In other words, the socket overhead isn't the biggest consumer of time and work. That's why you want to minimize the creation of new sockets. A pre-opened socket is instantly available. That doesn't matter much if you're doing little personal projects, but when you carry a load the size of the JavaRanch/CodeRanch, it can make a major difference.

The primary advantages of using the J2EE standard DataSource mechanisms are that they're a well-documented, well-supported (and pre-debugged) standard. Typically the pools support a number of measurement and tuning options.