java - 使用 hibernate 连接到 Microsoft SQL 2008 服务器

Question

我正在尝试通过 hibernate 连接到 Microsoft SQL 2008 服务器。以下是我的 hibernate.cfg.xml 文件:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
        <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="connection.url">jdbc:sqlserver://127.0.0.1:1433;databaseName=myDBName;instanceName=myInstanceName;</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.connection.password">pass</property>

        <mapping resource="Obj.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

这是我用来尝试建立连接和进行查询的代码:

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class SessionsTest {
    private static SessionFactory sessionFactory;
    private static ServiceRegistry serviceRegistry;

    /**
     * @param args
     */
    @SuppressWarnings({ "unchecked"})
    public static void main(String[] args) {
        sessionFactory = configureSessionFactory();

        Session session = sessionFactory.openSession();
        session.beginTransaction();

        List<Obj> result = (List<Obj>) session.createQuery("FROM Obj").list();

        for (Obj obj : result ) {
            System.out.println(obj.getObjID());
        }

        session.getTransaction().commit();
        session.close();

        if ( sessionFactory != null ) {
            sessionFactory.close();
        }
    }

    private static SessionFactory configureSessionFactory() throws HibernateException {
        Configuration config = new Configuration();
        configuration.configure();
        serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();            
        sessionFactory = config.buildSessionFactory(serviceRegistry);
        return sessionFactory;
    }
}

我得到的堆栈跟踪:

2013-04-13 15:02:03,449 [main] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08S01
2013-04-13 15:02:03,449 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1425)
    at com.test.test.ObjTest.main(ObjTest.java:24)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:204)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
    ... 5 more

我试过使用不同的驱动程序 (JTDS)。我尝试过以各种方式更改 URL 字符串。我尝试将我的方言更改为 org.hibernate.dialect.SQLServerDialect。我还尝试通过在 url 字符串的末尾添加 ;IntegratedSecurity=true 来使用 Windows 身份验证。除此之外，我一直在服务器属性中四处寻找，以确保我提供的实例以及端口都是正确的。我已经尝试过:telnet localhost 1433 并且无法以这种方式连接，但我可以使用 SQL Server 连接管理工作室。此外，我在 cmd 和 TaskList/FI "PID eq 4072"/FO LIST/V 中使用了 NetStat -o 来尝试追踪 sql server 以确认端口。奇怪的是我无法通过这种方式追踪 sql server .它没有出现在 NetStat 列表中，但如果我直接使用服务器的 PID，它会显示有关它的详细信息，除了状态是未知的， session #是 0 和用户名是 N/A。

我使用 Hibernate 4.2.0 和 SQLJDBC4，当我使用 JTDS 时它是 1.2.7。java -version 的输出:Java 版本“1.6.0_30”Java(TM) SE 运行时环境(build 1.6.0_30-b12)Java HotSpot(TM) 64 位服务器 VM(构建 20.5-b03，混合模式)

如果需要任何其他信息，请告诉我，第一次在这里发布。

Answer 1

我认为您的 SQL Server 实例未在 1433 服务于 TCP 连接。识别端口:

• 转到 SQL Server 配置管理器。
• 选择 SQL Server 网络配置
  • 为您的实例寻找协议(protocol)
  • 点击TCP IP(如果没有启用，则客户端可以通过TCP/IP连接)
  • 在弹出的结果中，选择 IP 地址选项卡
  • 向下滚动
  • 您将在 IPAll 部分看到 TCP 动态端口
  • 获取该值，这就是您应该使用的端口