oracle jdbc_JDBC示例– MySQL，Oracle

oracle jdbc

Welcome to the JDBC Example. Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the java applications and relational database servers. Just like java programs that we can “write once and run everywhere”, JDBC provides framework to connect to relational databases from java programs.

欢迎使用JDBC示例。 Java数据库连接或JDBC API在Java应用程序和关系数据库服务器之间提供了行业标准的和数据库独立的连接。 就像我们可以“编写一次并在任何地方运行”的Java程序一样，JDBC提供了从Java程序连接到关系数据库的框架。

JDBC示例 (JDBC Example)

JDBC API is used to achieve following tasks:

JDBC API用于完成以下任务：

• Establishing a connection to relational Database servers like Oracle, MySQL etc. JDBC API doesn’t provide framework to connect to NoSQL databases like MongoDB.
  建立与关系数据库服务器（如Oracle，MySQL等）的连接。JDBCAPI不提供用于连接至NoSQL数据库（如MongoDB）的框架。
• Send SQL queries to the Connection to be executed at database server.
  将SQL查询发送到要在数据库服务器上执行的Connection。
• Process the results returned by the execution of the query.
  处理查询执行返回的结果。

We will look into JDBC MySQL Example as well as JDBC Oracle Example. We will read database configuration from property file to make our code loosely coupled from database drivers.

我们将研究JDBC MySQL示例和JDBC Oracle示例。 我们将从属性文件中读取数据库配置，以使我们的代码与数据库驱动程序松散耦合。

JDBC驱动程序 (JDBC Driver)

JDBC API consists of two parts – first part is the JDBC API to be used by the application programmers. Second part is the low-level API to connect to database server. First part of JDBC API is part of standard java packages in java.sql package.

JDBC API由两部分组成-第一部分是应用程序程序员将使用的JDBC API。 第二部分是连接到数据库服务器的低级API。 JDBC API的第一部分是java.sql软件包中标准Java软件包的一部分。

For second part there are four different types of JDBC drivers:

对于第二部分，有四种不同类型的JDBC驱动程序：

1. JDBC-ODBC Bridge plus ODBC Driver (Type 1): This driver uses ODBC driver to connect to database servers. We should have ODBC drivers installed in the machines from where we want to connect to database, that’s why this driver is almost obsolete and should be used only when other options are not available.
   JDBC-ODBC桥加上ODBC驱动程序 （类型1）：此驱动程序使用ODBC驱动程序连接到数据库服务器。 我们应该在要连接数据库的计算机上安装ODBC驱动程序，这就是为什么该驱动程序几乎已过时，并且仅在其他选项不可用时才应使用的原因。
2. Native API partly Java technology-enabled driver (Type 2): This type of driver converts JDBC class to the client API for the RDBMS servers. We should have database client API installed at the machine from which we want to make database connection. Because of extra dependency on database client API drivers, this is also not preferred driver.
   本机API部分支持Java技术的驱动程序 （类型2）：这种类型的驱动程序将JDBC类转换为RDBMS服务器的客户端API。 我们应该在要建立数据库连接的机器上安装数据库客户端API。 由于额外依赖数据库客户端API驱动程序，因此也不推荐使用该驱动程序。
3. Pure Java Driver for Database Middleware (Type 3): This type of driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this kind of driver. This adds to extra network calls and slow performance. Hence this is also not widely used JDBC driver.
   用于数据库中间件的纯Java驱动程序 （类型3）：这种类型的驱动程序将JDBC调用发送到可以连接到不同类型数据库的中间件服务器。 我们应该安装一个中间件服务器来使用这种驱动程序。 这增加了额外的网络呼叫并降低了性能。 因此，这也不是广泛使用的JDBC驱动程序。
4. Direct-to-Database Pure Java Driver (Type 4): This is the preferred driver because it converts the JDBC calls to the network protocol understood by the database server. This solution doesn’t require any extra APIs at the client side and suitable for database connectivity over the network. However for this solution, we should use database specific drivers, for example OJDBC jars provided by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.
   直接数据库纯Java驱动程序 （类型4）：这是首选的驱动程序，因为它将JDBC调用转换为数据库服务器可以理解的网络协议。 该解决方案在客户端不需要任何其他API，并且适合通过网络进行数据库连接。 但是，对于此解决方案，我们应使用数据库特定的驱动程序，例如Oracle为Oracle DB提供的OJDBC jar和MySQL数据库为MySQL Connector / J。

Let’s create a simple JDBC Example Project and see how JDBC API helps us in writing loosely-coupled code for database connectivity.

让我们创建一个简单的JDBC示例项目，看看JDBC API如何帮助我们编写用于数据库连接的松耦合代码。

Before starting with the jdbc example, we need to do some prep work to have some data in the database servers to query.

在开始使用jdbc示例之前，我们需要做一些准备工作以使数据库服务器中的一些数据可以查询。

Installing the database servers is not in the scope of this tutorial, so I will assume that you have database servers installed.

安装数据库服务器不在本教程的讨论范围之内，因此我假设您已安装数据库服务器。

We will write program to connect to database server and run a simple jdbc query and process the results. For showing how we can achieve loose-coupling in connecting to databases using JDBC API, I will use Oracle and MySQL database systems.

我们将编写程序以连接到数据库服务器，并运行一个简单的jdbc查询并处理结果。 为了说明如何在使用JDBC API连接数据库时实现松耦合，我将使用Oracle和MySQL数据库系统。

Run below SQL scripts to create the table and insert some dummy values in the table.

在SQL脚本下面运行，以创建表并在表中插入一些虚拟值。

--mysql create table
create table Users(
  id  int(3) primary key,
  name varchar(20),
  email varchar(20),
  country varchar(20),
  password varchar(20)
  );
  
--oracle create table
create table Users(
  id  number(3) primary key,
  name varchar2(20),
  email varchar2(20),
  country varchar2(20),
  password varchar2(20)
  );
    
--insert rows
INSERT INTO Users (id, name, email, country, password) 
VALUES (1, 'Pankaj', 'pankaj@apple.com', 'India', 'pankaj123');
INSERT INTO Users (id, name, email, country, password) 
VALUES (4, 'David', 'david@gmail.com', 'USA', 'david123');
INSERT INTO Users (id, name, email, country, password) 
VALUES	(5, 'Raman', 'raman@google.com', 'UK', 'raman123');
commit;

Notice that datatypes in Oracle and MySQL databases are different, that’s why I have provided two different SQL DDL queries to create Users table. However both the databases confirms to SQL language, so insert queries are same for both the database tables.

注意，Oracle和MySQL数据库中的数据类型不同，这就是为什么我提供了两个不同SQL DDL查询来创建Users表的原因。 但是，两个数据库都使用SQL语言进行确认，因此两个数据库表的插入查询都相同。

JDBC示例–数据库驱动程序 (JDBC Example – Database Drivers)

As you can see in the project image, I have both MySQL (mysql-connector-java-5.0.5.jar) and Oracle (ojdbc6-11.2.0.1.0.jar) type-4 drivers in the lib directory and added to the project build path. Make sure you are using the correct version of the java drivers according to your database server installation version. Usually these jars shipped with the installer, so you can find them in the installation package. If you have maven based application, you can use below dependencies too.

从项目映像中可以看到，我在lib目录中同时具有MySQL（mysql-connector-java-5.0.5.jar）和Oracle（ojdbc6-11.2.0.1.0.jar）type-4驱动程序，并将其添加到项目构建路径。 确保根据数据库服务器安装版本使用的Java驱动程序版本正确。 通常，这些jar随安装程序一起提供，因此您可以在安装包中找到它们。 如果您有基于Maven的应用程序，则也可以使用以下依赖项。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.0.5</version>
</dependency>

<!-- You need to install ojdbc6 jar manually to your maven repository -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

JDBC数据库配置属性文件 (JDBC Database Configuration Property File)

We will read the database configuration details from the property files. This will help us in switching from Oracle to MySQL database easily and vice versa. All we would need is to change the property details.

我们将从属性文件中读取数据库配置详细信息。 这将帮助我们轻松地从Oracle切换到MySQL数据库，反之亦然。 我们所需要做的就是更改属性详细信息。

#mysql DB properties
#DB_DRIVER_CLASS=com.mysql.jdbc.Driver
#DB_URL=jdbc:mysql://localhost:3306/UserDB
#DB_USERNAME=pankaj
#DB_PASSWORD=pankaj123

#Oracle DB Properties
DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
DB_URL=jdbc:oracle:thin:@localhost:1571:MyDBSID
DB_USERNAME=scott
DB_PASSWORD=tiger

Database configurations are the most important details when using JDBC API. The first thing we should know is the Driver class to use. For Oracle database, driver class is oracle.jdbc.driver.OracleDriver. For MySQL database, driver class is com.mysql.jdbc.Driver. You will find these driver classes in their respective driver jar files. Both of these implement JDBC java.sql.Driver interface.

使用JDBC API时，数据库配置是最重要的细节。 我们应该知道的第一件事是要使用的Driver类。 对于Oracle数据库，驱动程序类为oracle.jdbc.driver.OracleDriver 。 对于MySQL数据库，驱动程序类为com.mysql.jdbc.Driver 。 您将在它们各自的驱动程序jar文件中找到这些驱动程序类。 这两个都实现JDBC java.sql.Driver接口。

The second important part is the database connection URL string. Every database driver has it’s own way to configure the database URL but all of them have host, port and Schema details in the connection URL.

第二个重要部分是数据库连接URL字符串。 每个数据库驱动程序都有其自己的配置数据库URL的方式，但是所有驱动程序都在连接URL中具有主机，端口和架构详细信息。

MySQL database connection String format is jdbc:mysql://<HOST>:<PORT>/<SCHEMA>.

MySQL数据库连接字符串格式为jdbc:mysql://<HOST>:<PORT>/<SCHEMA> 。

Oracle database connection string format is jdbc:oracle:thin:@<HOST>:<PORT>:<SID>.

Oracle数据库连接字符串格式为jdbc:oracle:thin:@<HOST>:<PORT>:<SID> 。

The other important details are database username and password details to be used for connecting to the database server.

其他重要的详细信息是用于连接数据库服务器的数据库用户名和密码详细信息。

JDBC示例程序 (JDBC Example Program)

Let’s see a simple jdbc example program to see how we can read above properties and create database connection.

让我们看一个简单的jdbc示例程序，以了解如何读取上述属性并创建数据库连接。

package com.journaldev.jdbc;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {

	public static Connection getConnection() {
		Properties props = new Properties();
		FileInputStream fis = null;
		Connection con = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);

			// load the Driver Class
			Class.forName(props.getProperty("DB_DRIVER_CLASS"));

			// create the connection now
			con = DriverManager.getConnection(props.getProperty("DB_URL"),
					props.getProperty("DB_USERNAME"),
					props.getProperty("DB_PASSWORD"));
		} catch (IOException | ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
}

Above jdbc example program is really simple. First we are reading database configuration details from the property file and then loading the JDBC driver and using DriverManager to create the connection. Notice that this code use only Java JDBC API classes and there is no way to know that it’s connecting to which type of database. This is also a great example of writing code for interfaces methodology.

上面的jdbc示例程序确实很简单。 首先，我们从属性文件中读取数据库配置详细信息，然后加载JDBC驱动程序，并使用DriverManager创建连接。 请注意，此代码仅使用Java JDBC API类，无法知道它连接到哪种类型的数据库。 这也是为接口方法编写代码的一个很好的例子。

The important code to notice is the Class.forName() method call, this is the Java Reflection method to create the instance of the given class. You might wonder why we are using Reflection and not new operator to create the object and why we are just creating the object and not using it.

注意的重要代码是Class.forName()方法调用，这是创建给定类实例的Java Reflection方法。 您可能想知道为什么我们使用反射而不是new运算符来创建对象，以及为什么我们只是创建对象而不使用它。

The first reason is that using reflection to create instance helps us in writing loosely-coupled code that we can’t achieve if we are using new operator. In that case, we could not switch to different database without making corresponding code changes.

第一个原因是使用反射创建实例有助于我们编写松耦合的代码，而如果使用new运算符则无法实现。 在这种情况下，如果不进行相应的代码更改，就无法切换到其他数据库。

The reason for not using the object is because we are not interested in creating the object. The main motive is to load the class into memory, so that the driver class can register itself to the DriverManager. If you will look into the Driver classes implementation, you will find that they have static block where they are registering themselves to DriverManager.

不使用对象的原因是因为我们对创建对象不感兴趣。 主要目的是将类加载到内存中，以便驱动程序类可以将其自身注册到DriverManager。 如果研究一下Driver类的实现，您会发现它们在将自身注册到DriverManager的位置具有静态块。

oracle.jdbc.driver.OracleDriver.java snippet:

oracle.jdbc.driver.OracleDriver.java代码段：

static
  {
    try
    {
      if (defaultDriver == null)
      {
        defaultDriver = new oracle.jdbc.OracleDriver();
        DriverManager.registerDriver(defaultDriver);
      }
	//some code omitted for clarity
	}
}

com.mysql.jdbc.Driver.java snippet:

com.mysql.jdbc.Driver.java代码段：

static
  {
    try
    {
      DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
      throw new RuntimeException("Can't register driver!");
    }
  }

This is a great example where we are making our code loosely-coupled with the use of reflection API. So basically we are doing following things using Class.forName() method call.

这是一个很好的示例，其中我们通过使用反射API使代码松散耦合。 因此，基本上，我们正在使用Class.forName（）方法调用进行以下操作。

Driver driver = new OracleDriver();
DriverManager.registerDriver(driver);

DriverManager.getConnection() method uses the registered JDBC drivers to create the database connection. This method throws java.sql.SQLException if there is any problem in getting the database connection.

DriverManager.getConnection（）方法使用已注册的JDBC驱动程序来创建数据库连接。 如果获取数据库连接有任何问题，则此方法将引发java.sql.SQLException 。

Now let’s write a simple jdbc example test program to use the database connection and run simple query.

现在，让我们编写一个简单的jdbc示例测试程序，以使用数据库连接并运行简单的查询。

JDBC语句和ResultSet (JDBC Statement and ResultSet)

Here is a simple jdbc example program where we are using the JDBC Connection to execute SQL query against the database and then processing the result set.

这是一个简单的jdbc示例程序，我们在其中使用JDBC连接对数据库执行SQL查询，然后处理结果集。

package com.journaldev.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnectionTest {
	
	private static final String QUERY = "select id,name,email,country,password from Users";

	public static void main(String[] args) {
				
		//using try-with-resources to avoid closing resources (boiler plate code)
		try(Connection con = DBConnection.getConnection();
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(QUERY)) {	
			
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				String country = rs.getString("country");
				String password = rs.getString("password");
				System.out.println(id + "," +name+ "," +email+ "," +country+ "," +password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

}

Notice that we are using Java 7 try-with-resources feature to make sure that resources are closed as soon as we are out of try-catch block.

注意，我们正在使用Java 7 try-with-resources功能来确保一旦我们退出try-catch块，就关闭资源。

JDBC Connection, Statement and ResultSet are expensive resources and we should close them as soon as we are finished using them.

JDBC Connection，Statement和ResultSet是昂贵的资源，我们在使用完它们后应立即关闭它们。

Connection.createStatement() is used to create the Statement object and then executeQuery() method is used to run the query and get the result set object.

Connection.createStatement()用于创建Statement对象，然后executeQuery()方法用于运行查询并获取结果集对象。

First call to ResultSet next() method call moves the cursor to the first row and subsequent calls moves the cursor to next rows in the result set. If there are no more rows then it returns false and come out of the while loop. We are using result set getXXX() method to get the columns value and then writing them to the console.

第一次调用ResultSet next()方法时，将光标移到第一行，随后的调用将光标移到结果集中的下一行。 如果没有更多行，则返回false并退出while循环。 我们使用结果集getXXX()方法获取列的值，然后将其写入控制台。

When we run above jdbc example test program, we get following output.

当我们在jdbc示例测试程序上面运行时，将得到以下输出。

1,Pankaj,pankaj@apple.com,India,pankaj123
4,David,david@gmail.com,USA,david123
5,Raman,raman@google.com,UK,raman123

Just uncomment the MySQL database configuration properties from db.properties file and comment the Oracle database configuration details to switch to MySQL database. Since the data is same in both Oracle and MySQL database Users table, you will get the same output.

只需从db.properties文件中取消注释MySQL数据库配置属性，然后注释Oracle数据库配置详细信息即可切换到MySQL数据库。 由于Oracle和MySQL数据库“用户”表中的数据相同，因此您将获得相同的输出。

That’s all for the JDBC example. You can see that JDBC API helps us in writing database driver independent code and makes it easier to switch to other relational databases.

这就是JDBC示例的全部内容。 您可以看到JDBC API可以帮助我们编写独立于数据库驱动程序的代码，并使切换到其他关系数据库更加容易。

Download the project from below link and run different scenarios for better understanding.

从下面的链接下载项目，并运行不同的场景以更好地理解。

Download JDBC Example Project 下载JDBC示例项目

翻译自: https://www.journaldev.com/2471/jdbc-example-mysql-oracle

oracle jdbc