How do you configure a DataSource in Java to connect to MS SQL Server?

Question

I'm trying to follow Java's JDBC tutorials to write a Java program that can connect to SQL Server 2008. I'm getting lost at the point of making a connection.

The following snippet is from the tutorial:

InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
Connection con = ds.getConnection();
DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
ds.setPort(1527);
ds.setHost("localhost");
ds.setUser("APP")
ds.setPassword("APP");
Connection con = ds.getConnection();

There's no explanation of what comp/env/jdbc/myDB should point to, and I don't know how I should choose a port. Also, the object ds seems to be defined twice.

I'm using the JSQLDataSource driver, for the record. Can anyone point me in the right direction here?

http://java.sun.com/docs/books/tutorial/jdbc/basics/connecting.html

Answer 1

Start with the JDBC tutorial or the Microsoft docs.

and this:

String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
Class.forName(driver);
String url = "jdbc:microsoft:sqlserver://host:1433/database";
Connection conn = DriverManager.getConnection(url, "username", "password");

Fill in your values for host, database, username, and password. The default port for SQL server is 1433.

UPDATE: Good point below. JDBC drivers can be had from both Microsoft and jTDS. I prefer the latter.

JNDI lookups have to do with Java EE app servers that support connection pooling. You can ask the app server to create a pool of connections, which can be an expensive thing to do, and loan them out to clients like library books as needed.

If you aren't using a Java EE app server or connection pooling, you have to create the connection on your own. That's where manual processes and DriverManager come in.

EXPLANATION: As for why the Sun tutorial shows DataSource twice, I'd say it's a case of poor editing. If you look above the code sample it says you can get a DataSource "by lookup or manually". The code snippet below shows both together, when it should be one or the other.

You know it's an inadvertent error because there's no way the code as written could compile. You have "ds" declared twice.

So it should read "...lookup", followed by its code snippet, and then "...manually", followed by its code snippet.

Answer 2

I like the jTDS driver for connecting to SQL Server.

A URL will look like this:

jdbc:jtds:sqlserver://localhost/Finance;instance=sqlexpress

Check this for jTDS Url Info.

This also has some interesting information to help troubleshoot jtds to sql express sorts of problems.