CODEWITHSUNDEEP
javasql-serverdatasource
Eric Wilson
Eric Wilson

Reputation: 59355

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

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

Upvotes: 8

Views: 55068

Answers (5)

Mukus
Mukus

Reputation: 5033

This question has already been answered long time ago. The question was asked about JNDI lookup. With lookup you have to see the application server log to see what the connection is bound to. For example in Jboss startup, I can see:

[ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=myDB' to JNDI name 'java:myDB'

Using that name=myDB you lookup

InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("java:myDB");

Notice how the server log and the code both point to the JNDI name java:myDB.

Upvotes: 1

JonnyRaa
JonnyRaa

Reputation: 8038

I'm not sure anyone above has really answered the question.

I found this microsoft sample useful.

The key information in there is really that the class you need is SQLServerDataSource that is basically a configuration object - you use it something like this:

    SQLServerDataSource dataSource = new SQLServerDataSource();
    dataSource.setUser("aUser");
    dataSource.setPassword("password");
    dataSource.setServerName("hostname");
    dataSource.setDatabaseName("db");

You would then call 

dataSource.getConnection();

to get a connection object which is basically the thing you use to talk to the database.

Use 

connection.prepareStatement("some sql with ? substitutions");

to make something for firing off sql and:

connection.prepareCall

for calling stored procedures.

Upvotes: 14

user3203060
user3203060

Reputation: 11

DataSource ds = new SimpleDriverDataSource(new com.mysql.jdbc.Driver(),
    "jdbc:mysql://database:1433;databaseName=name", "username", "password");
JdbcTemplate jdbc = new JdbcTemplate(ds);

Upvotes: 1

duffymo
duffymo

Reputation: 308763

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.

Upvotes: 11

eric.christensen
eric.christensen

Reputation: 3231

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.

Upvotes: 4

Related Questions