如何在Java中配置DataSource以连接到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.

我正在尝试按照Java的JDBC教程编写一个可以连接到SQL Server 2008的Java程序。我在建立连接时迷路了。

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.

没有解释comp / env / jdbc / myDB应该指向什么,我不知道应该如何选择端口。此外,对象ds似乎被定义了两次。

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

我正在使用JSQLDataSource驱动程序来记录。任何人都能指出我在正确的方向吗?

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

5 个解决方案

#1

Start with the JDBC tutorial or the Microsoft docs.

从JDBC教程或Microsoft文档开始。

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.

填写主机,数据库,用户名和密码的值。 SQL Server的默认端口是1433。

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

更新:下面的好点。可以从Microsoft和jTDS获得JDBC驱动程序。我更喜欢后者。

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.

JNDI查找与支持连接池的Java EE应用程序服务器有关。您可以要求应用服务器创建一个连接池,这可能是一件昂贵的事情,并根据需要将它们借给图书馆书籍等客户端。

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.

如果您不使用Java EE应用程序服务器或连接池,则必须自己创建连接。这就是手动流程和DriverManager的用武之地。

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.

解释:至于为什么Sun教程会显示DataSource两次,我会说这是一个编辑不好的情况。如果您查看代码示例上方,则表示您可以“通过查找或手动”获取DataSource。下面的代码片段显示了它们在一起时,它应该是一个或另一个。

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

你知道这是一个无意的错误,因为编写的代码无法编译。你有两次声明“ds”。

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

所以它应该是“... lookup”,然后是它的代码片段,然后是“... manual”,然后是它的代码片段。

#2

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,它基本上是一个配置对象 - 你使用它是这样的:

    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:

制作一些用于解雇sql的东西:

connection.prepareCall

for calling stored procedures.

用于调用存储过程。

#3

I like the jTDS driver for connecting to SQL Server.

A URL will look like this:

我喜欢用于连接SQL Server的jTDS驱动程序。 URL将如下所示:

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

Check this for jTDS Url Info.

检查以获取jTDS Url Info。

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

这也有一些有趣的信息,以帮助解决jtds sql表达的各种问题。

#4

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:

很久以前就已经回答过这个问题了。有人询问有关JNDI查找的问题。使用查找,您必须查看应用程序服务器日志以查看绑定的连接。例如在Jboss启动中,我可以看到:

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

Now that is the clue. That is exactly how I should do the 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.

注意服务器日志和代码如何指向JNDI名称java:myDB。

#5

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

#1