Java JDBC连接各种数据库实例

来源：互联网发布：打谱软件overture下载编辑：程序博客网时间：2024/06/09 14:57

在本文章中，我们来学习Java中不同类型数据库的JDBC驱动程序库的使用。

1- 介绍

下面文章将介绍下载和使用一些数据库的JDBC驱动程序库。在这里，有关数据库类型如下：

Oracle
MySQL
SQL Server.

2- 对于Oracle的JDBC驱动程序

Oracle数据库库驱动程序通常命名为：ojdbc14.jar, ojdbc6.jar, ... 所不同的是它打包的Java版本。

ojdbc14.jar: 由Java1.4版编译和打包
ojdbc6.jar: 由Java1.6版编译和打包

更多详细信息可以查看并从甲骨文网站下载：

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

可以下载 ojdbc6.jar 文件，它可以使用在不同版本(XE，10G，11G，12C)的Oracle数据库。目前几乎所有的 Java 应用程序使用 Java6 版本或更高版本。

要从甲骨文网站下载文件，必须有一个Oracle帐号(免费注册)。

为了节省时间，您可以通过以下链接下载：

https://code.google.com/p/afirs/downloads/detail?name=ojdbc6.jar

下载结果：

2.1- Maven关联Oracle JDBC驱动程序

   <repositories>       <!-- Repository for ORACLE ojdbc6. -->       <repository>           <id>codelds</id>           <url>https://code.lds.org/nexus/content/groups/main-repo</url>       </repository>   </repositories>    .......   <dependencies>       ......       <!-- Oracle database driver -->       <dependency>           <groupId>com.oracle</groupId>           <artifactId>ojdbc6</artifactId>           <version>11.2.0.3</version>       </dependency>       .......   </dependencies>

2.2- 如何使用 (ojdbc)？

// Driver class:oracle.jdbc.driver.OracleDriver// URL Connection String: (SID)String urlString ="jdbc:oracle:thin:@myhost:1521:mysid"// URL Connection String:  (Service Name)String urlString ="jdbc:oracle:thin:username/pass@//myhost:1521/myservicename"// Or:String urlString ="jdbc:oracle:thin:@myhost:1521/myservicename";

例如 JDBC 连接到 Oracle 数据库。

OracleConnUtils.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class OracleConnUtils {   public static Connection getOracleConnection()           throws ClassNotFoundException, SQLException {       String hostName = "localhost";       String sid = "db11g";       String userName = "learningsql";       String password = "1234";       return getOracleConnection(hostName, sid, userName, password);   }   public static Connection getOracleConnection(String hostName, String sid,           String userName, String password) throws ClassNotFoundException,           SQLException {      // Declare the class Driver for Oracle DB      // This is necessary with Java 5 (or older)      // Java6 (or newer) automatically find the appropriate driver.      // If you use Java> 6, then this line is not needed.       Class.forName("oracle.jdbc.driver.OracleDriver");       // Example: jdbc:oracle:thin:@localhost:1521:db11g       String connectionURL = "jdbc:oracle:thin:@" + hostName + ":1521:" + sid;       Connection conn = DriverManager.getConnection(connectionURL, userName,               password);       return conn;   }}

3- MySQL的JDBC驱动程序

也可以下载MySQL数据库JDBC库在：

http://mvnrepository.com/artifact/mysql/mysql-connector-java

下载结果：

3.1- 如何使用？

如何使用MySQL-connector-java-xxx.jar : (JDBC for MySQL)

// Driver class:com.mysql.jdbc.Driver// URL Connection String:String url = "jdbc:mysql://hostname:3306/dbname";// Example:String url = "jdbc:mysql://localhost:3306/simplehr";

例如，使用 JDBC 连接到 MySQL 数据库

MySQLConnUtils.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class MySQLConnUtils {  public static Connection getMySQLConnection()          throws ClassNotFoundException, SQLException {      String hostName = "localhost";      String dbName = "learningsql";      String userName = "root";      String password = "12345";      return getMySQLConnection(hostName, dbName, userName, password);  }  public static Connection getMySQLConnection(String hostName, String dbName,          String userName, String password) throws SQLException,          ClassNotFoundException {     // Declare the class Driver for Oracle DB     // This is necessary with Java 5 (or older)     // Java6 (or newer) automatically find the appropriate driver.     // If you use Java> 5, then this line is not needed.      Class.forName("com.mysql.jdbc.Driver");      // Ví dụ: jdbc:mysql://localhost:3306/simplehr      String connectionURL = "jdbc:mysql://" + hostName + ":3306/" + dbName;      Connection conn = DriverManager.getConnection(connectionURL, userName,              password);      return conn;  }}

3.2- 有些问题出现，如何解决它

在某些情况下，Java连接或另一台计算机链接MySQL会发生出错。其原因可能是没有配置MySQL服务器，以允许其他计算机的连接。

4- JDBC SQLServer (JTDS)

JTDS是另一个SQL Server管理数据库的JDBC库，它是一个开源库。

JTDS是一个开源的100%纯Java(4类)JDBC3.0驱动程序的Microsoft SQL Server(6.5，7，2000，2005，2008，2012)和Sybase ASE(10，11，12，15)。JTDS基于freetds的，目前是SQL Server和Sybase最快生产就绪的JDBC驱动程序。JTDS是100%与JDBC 3.0兼容，只向前和可滚动/可更新结果集支持并执行所有的DatabaseMetaData和ResultSetMetaData方法。

您可以下载JTDS在：

http://mvnrepository.com/artifact/net.sourceforge.jtds/jtds

结果下载：

4.1- 如何使用(jtds)

使用方法：(用于SQL Server JDBC驱动程序)

// Driver Classnet.sourceforge.jtds.jdbc.Driver// Connection URL String:jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]// Example 1:String url = "jdbc:jtds:sqlserver://MYPC:1433/simplehr;instance=SQLEXPRESS;user=sa;password=s3cr3t";getConnection(url);// Example 2:String url = "jdbc:jtds:sqlserver://MYPC:1433/simplehr;instance=SQLEXPRESS";getConnection(url, "sa", "s3cr3t"):

例如，使用JDBC连接到MySQL数据库(用JTDS库)

SQLServerConnUtils_JTDS.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class SQLServerConnUtils_JTDS { // Connect to SQLServer. // (Using JTDS library) public static Connection getSQLServerConnection_JTDS() throws SQLException,         ClassNotFoundException {     String hostName = "localhost";     String sqlInstanceName = "SQLEXPRESS";     String database = "simplehr";     String userName = "sa";     String password = "12345";     return getSQLServerConnection_JTDS(hostName, sqlInstanceName, database,             userName, password); } // JTDS & SQLServer. private static Connection getSQLServerConnection_JTDS(String hostName,         String sqlInstanceName, String database, String userName,         String password) throws ClassNotFoundException, SQLException {    // Declare the class Driver for Oracle DB    // This is necessary with Java 5 (or older)    // Java6 (or newer) automatically find the appropriate driver.    // If you use Java> 5, then this line is not needed.     Class.forName("net.sourceforge.jtds.jdbc.Driver");      // Example:     // jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS     String connectionURL = "jdbc:jtds:sqlserver://" + hostName + ":1433/"             + database + ";instance=" + sqlInstanceName;     Connection conn = DriverManager.getConnection(connectionURL, userName,             password);     return conn; }}

4.2- 有些问题，如何解决它？

在某些情况下，我们连接到SQL Server可能会发生一些错误情况：

Exception in thread "main" java.sql.SQLException: Server tran-vmware has no instance named SQLEXPRESS.   at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:301)   at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)   at java.sql.DriverManager.getConnection(DriverManager.java:571)   at java.sql.DriverManager.getConnection(DriverManager.java:215)   at com.yiibai.tutorial.jdbc.ConnectionUtils.getSQLServerConnection_JTDS(ConnectionUtils.java:189)   at com.yiibai.tutorial.jdbc.ConnectionUtils.getSQLServerConnection_JTDS(ConnectionUtils.java:72)   at com.yiibai.tutorial.jdbc.ConnectionUtils.getMyConnection(ConnectionUtils.java:31)   at com.yiibai.tutorial.jdbc.TestConnection.main(TestConnection.java:20)

存在上述错误，因为没有启用SQL Server的TCP/IP服务。

5- JDBC与 SQLServer (SQLJDBC)

SQLJDBC库是由微软提供的。

下载地址：

http://www.microsoft.com/en-us/download/details.aspx?id=11774

解压刚刚下载的文件。

解压后的结果如下：

5.1- 如何使用 (sqljdbc)

// Driver Class:com.microsoft.sqlserver.jdbc.SQLServerDriverClass.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// Url String:String url = "jdbc:sqlserver://ServerIp;Instance=SQLEXPRESS;databaseName=simplehr";// orString url = "jdbc:sqlserver://ServerIp:1433;Instance=SQLEXPRESS;databaseName=simplehr";String user = "dbUserID";String pass = "dbUserPassword";Connection connection = DriverManager.getConnection(url, user, pass);

例如，使用JDBC连接到SQLServer数据库(用SQLJDBC库)。

SQLServerConnUtils_SQLJDBC.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class SQLServerConnUtils_SQLJDBC {   // Connect to SQLServer.  // (Using SQLJDBC)  public static Connection getSQLServerConnection_SQLJDBC()          throws ClassNotFoundException, SQLException {      String hostName = "localhost";      String sqlInstanceName = "SQLEXPRESS";      String database = "learningsql";      String userName = "sa";      String password = "12345";      return getSQLServerConnection_SQLJDBC(hostName, sqlInstanceName,              database, userName, password);  }  // SQLServer & SQLJDBC.  private static Connection getSQLServerConnection_SQLJDBC(String hostName,          String sqlInstanceName, String database, String userName,          String password) throws ClassNotFoundException, SQLException {     // Declare the class Driver for Oracle DB     // This is necessary with Java 5 (or older)     // Java6 (or newer) automatically find the appropriate driver.     // If you use Java> 5, then this line is not needed.      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");      // Example:      // jdbc:sqlserver://ServerIp:1433/SQLEXPRESS;databaseName=simplehr      String connectionURL = "jdbc:sqlserver://" + hostName + ":1433"              + ";instance=" + sqlInstanceName + ";databaseName=" + database;      Connection conn = DriverManager.getConnection(connectionURL, userName,              password);      return conn;  }}

5.2- 出现一些麻烦，如何解决它

在某些情况下，我们连接到SQL Server会发生一些错误情况：

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)  at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)  at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)  at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)  at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)  at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)  at java.sql.DriverManager.getConnection(Unknown Source)  at java.sql.DriverManager.getConnection(Unknown Source)  ...

如存在上述错误，因为可能没有启用SQL Server的TCP/IP服务。需要启动SQLServer服务，如果仍然出现以下错误：

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host tran-vmware, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241) at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243) at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) ...

您应该考虑使用JTDS了

6- JDBC驱动和MongoDB

JDBC驱动程序MongoDB，你从下面的网址下载：

https://github.com/mongodb/mongo-java-driver/downloads

您也可以在Maven仓库下载：

http://mvnrepository.com/artifact/org.mongodb/mongo-java-driver

0 0