jdbc 数据源_Java数据源，JDBC数据源示例

最新推荐文章于 2024-08-19 11:44:41 发布

cunchi4221

最新推荐文章于 2024-08-19 11:44:41 发布

阅读量551

点赞数

文章标签：数据库 java mysql jdbc spring

原文链接：https://www.journaldev.com/2509/java-datasource-jdbc-datasource-example

版权

jdbc 数据源

Java DataSource and JDBC DataSource programming is the way to work with database in our java programs. We have already seen that JDBC DriverManager can be used to get relational database connections. But when it comes to actual programming, we want more than just connections.

Java DataSource和JDBC DataSource编程是在我们的Java程序中使用数据库的方式。我们已经看到JDBC DriverManager可用于获取关系数据库连接。但是，在实际编程中，我们不仅需要连接。

Java数据源 (Java DataSource)

Most of the times we are looking for loose coupling for connectivity so that we can switch databases easily, connection pooling for transaction management and distributed systems support. JDBC DataSource is the preferred approach if you are looking for any of these features in your application. Java DataSource interface is present in javax.sql package and it only declare two overloaded methods getConnection() and getConnection(String str1,String str2).

大多数时候，我们一直在寻找连接的松散耦合，以便我们可以轻松地切换数据库，为事务管理提供连接池和为分布式系统提供支持。如果要在应用程序中寻找任何这些功能，则JDBC DataSource是首选方法。 Java DataSource接口存在于javax.sql包中，它仅声明两个重载方法getConnection()和getConnection(String str1,String str2) 。

JDBC数据源 (JDBC DataSource)

It is the responsibility of different Database vendors to provide different kinds of implementation of DataSource interface. For example MySQL JDBC Driver provides basic implementation of DataSource interface with com.mysql.jdbc.jdbc2.optional.MysqlDataSource class and Oracle database driver implements it with oracle.jdbc.pool.OracleDataSource class.

不同的数据库供应商有责任提供不同类型的DataSource接口实现。例如，MySQL JDBC驱动程序通过com.mysql.jdbc.jdbc2.optional.MysqlDataSource类提供了DataSource接口的基本实现，而Oracle数据库驱动程序通过oracle.jdbc.pool.OracleDataSource类实现了它。

These implementation classes provide methods through which we can provide database server details with user credentials. Some of the other common features provided by these JDBC DataSource implementation classes are;

这些实现类提供了一些方法，通过这些方法，我们可以为数据库服务器详细信息提供用户凭据。这些JDBC DataSource实现类提供的其他一些常见功能是：

Caching of PreparedStatement for faster processing
缓存PreparedStatement以进行更快的处理
Connection timeout settings
连接超时设置
Logging features
记录功能
ResultSet maximum size threshold
ResultSet最大大小阈值

JDBC数据源示例 (JDBC DataSource Example)

Let’s create a simple JDBC DataSource example project and learn how to use MySQL and Oracle DataSource basic implementation classes to get the database connection. Our final project will look like below image.

让我们创建一个简单的JDBC DataSource示例项目，并学习如何使用MySQL和Oracle DataSource基本实现类来获取数据库连接。我们的最终项目将如下图所示。

Java JDBC数据源–数据库设置 (Java JDBC DataSource – Database Setup)

Before we get into our example programs, we need some database setup with table and sample data. Installation of MySQL or Oracle database is out of scope of this tutorial, so I will just go ahead and setup table with sample data.

在进入示例程序之前，我们需要使用表和样本数据进行一些数据库设置。 MySQL或Oracle数据库的安装不在本教程的讨论范围之内，因此我将继续设置示例数据表。

--Create Employee table
CREATE TABLE `Employee` (
  `empId` int(10) unsigned NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- insert some sample data
INSERT INTO `Employee` (`empId`, `name`)
VALUES
	(1, 'Pankaj'),
	(2, 'David');

commit;

CREATE TABLE "EMPLOYEE"
  (
    "EMPID"   NUMBER NOT NULL ENABLE,
    "NAME"    VARCHAR2(10 BYTE) DEFAULT NULL,
    PRIMARY KEY ("EMPID")
  );

Insert into EMPLOYEE (EMPID,NAME) values (10,'Pankaj');
Insert into EMPLOYEE (EMPID,NAME) values (5,'Kumar');
Insert into EMPLOYEE (EMPID,NAME) values (1,'Pankaj');
commit;

Now let’s move on to our java programs. For having database configuration loosely coupled, I will read them from property file.

现在让我们继续我们的Java程序。为了使数据库配置松散耦合，我将从属性文件中读取它们。

db.properties file:

db.properties文件：

#mysql DB properties
MYSQL_DB_DRIVER_CLASS=com.mysql.jdbc.Driver
MYSQL_DB_URL=jdbc:mysql://localhost:3306/UserDB
MYSQL_DB_USERNAME=pankaj
MYSQL_DB_PASSWORD=pankaj123

#Oracle DB Properties
ORACLE_DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
ORACLE_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl
ORACLE_DB_USERNAME=hr
ORACLE_DB_PASSWORD=oracle

Make sure that above configurations match with your local setup. Also make sure you have MySQL and Oracle DB JDBC jars included in the build path of the project.

确保以上配置与您的本地设置匹配。还要确保在项目的构建路径中包含MySQL和Oracle DB JDBC jar。

Java JDBC数据源– MySQL，Oracle示例 (Java JDBC DataSource – MySQL, Oracle Example)

Let’s write a factory class that we can use to get MySQL or Oracle DataSource.

让我们编写一个工厂类，我们可以使用它来获取MySQL或Oracle DataSource。

package com.journaldev.jdbc.datasource;

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

import javax.sql.DataSource;

import oracle.jdbc.pool.OracleDataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class MyDataSourceFactory {

	public static DataSource getMySQLDataSource() {
		Properties props = new Properties();
		FileInputStream fis = null;
		MysqlDataSource mysqlDS = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);
			mysqlDS = new MysqlDataSource();
			mysqlDS.setURL(props.getProperty("MYSQL_DB_URL"));
			mysqlDS.setUser(props.getProperty("MYSQL_DB_USERNAME"));
			mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		return mysqlDS;
	}
	
	public static DataSource getOracleDataSource(){
		Properties props = new Properties();
		FileInputStream fis = null;
		OracleDataSource oracleDS = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);
			oracleDS = new OracleDataSource();
			oracleDS.setURL(props.getProperty("ORACLE_DB_URL"));
			oracleDS.setUser(props.getProperty("ORACLE_DB_USERNAME"));
			oracleDS.setPassword(props.getProperty("ORACLE_DB_PASSWORD"));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return oracleDS;
	}
		
}

Notice that both Oracle and MySQL DataSource implementation classes are very similar, let’s write a simple test program to use these methods and run some test.

注意，Oracle和MySQL DataSource实现类非常相似，让我们编写一个简单的测试程序来使用这些方法并运行一些测试。

package com.journaldev.jdbc.datasource;

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

import javax.sql.DataSource;

public class DataSourceTest {

	public static void main(String[] args) {
		
		testDataSource("mysql");
		System.out.println("**********");
		testDataSource("oracle");

	}

	private static void testDataSource(String dbType) {
		DataSource ds = null;
		if("mysql".equals(dbType)){
			ds = MyDataSourceFactory.getMySQLDataSource();
		}else if("oracle".equals(dbType)){
			ds = MyDataSourceFactory.getOracleDataSource();
		}else{
			System.out.println("invalid db type");
			return;
		}
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select empid, name from Employee");
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
				try {
					if(rs != null) rs.close();
					if(stmt != null) stmt.close();
					if(con != null) con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}

Notice that the client class is totally independent of any Database specific classes. This helps us in hiding the underlying implementation details from client program and achieve loose coupling and abstraction benefits.

请注意，客户端类完全独立于任何数据库特定的类。这有助于我们从客户端程序中隐藏底层的实现细节，并实现松散的耦合和抽象优势。

When we run above test program, we will get below output.

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

Employee ID=1, Name=Pankaj
Employee ID=2, Name=David
**********
Employee ID=10, Name=Pankaj
Employee ID=5, Name=Kumar
Employee ID=1, Name=Pankaj

Apache Commons DBCP示例 (Apache Commons DBCP Example)

If you look at above Java DataSource factory class, there are two major issues with it.

如果您查看上述Java DataSource工厂类，则有两个主要问题。

The factory class methods to create the MySQL and Oracle DataSource are tightly coupled with respective driver API. If we want to remove support for Oracle database in future or want to add some other database support, it will require code change.
创建MySQL和Oracle DataSource的工厂类方法与相应的驱动程序API紧密结合。如果我们将来想要删除对Oracle数据库的支持或要添加其他一些数据库支持，则需要更改代码。
Most of the code to get the MySQL and Oracle DataSource is similar, the only different is the implementation class that we are using.
获取MySQL和Oracle DataSource的大多数代码是相似的，唯一的不同是我们正在使用的实现类。

Apache Commons DBCP API helps us in getting rid of these issues by providing Java DataSource implementation that works as an abstraction layer between our program and different JDBC drivers.

Apache Commons DBCP API通过提供Java DataSource实现来帮助我们摆脱这些问题，该实现充当程序和不同JDBC驱动程序之间的抽象层。

Apache DBCP library depends on Commons Pool library, so make sure they both are in the build path as shown in the image.

Apache DBCP库取决于Commons Pool库，因此请确保它们都在构建路径中，如图所示。

Here is the DataSource factory class using BasicDataSource that is the simple implementation of DataSource.

这是使用BasicDataSource的DataSource工厂类，它是DataSource的简单实现。

package com.journaldev.jdbc.datasource;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class DBCPDataSourceFactory {

	public static DataSource getDataSource(String dbType){
		Properties props = new Properties();
		FileInputStream fis = null;
		BasicDataSource ds = new BasicDataSource();
		
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);
		}catch(IOException e){
			e.printStackTrace();
			return null;
		}
		if("mysql".equals(dbType)){
			ds.setDriverClassName(props.getProperty("MYSQL_DB_DRIVER_CLASS"));
            ds.setUrl(props.getProperty("MYSQL_DB_URL"));
            ds.setUsername(props.getProperty("MYSQL_DB_USERNAME"));
            ds.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));
		}else if("oracle".equals(dbType)){
			ds.setDriverClassName(props.getProperty("ORACLE_DB_DRIVER_CLASS"));
            ds.setUrl(props.getProperty("ORACLE_DB_URL"));
            ds.setUsername(props.getProperty("ORACLE_DB_USERNAME"));
            ds.setPassword(props.getProperty("ORACLE_DB_PASSWORD"));
		}else{
			return null;
		}
		
		return ds;
	}
}

As you can see that depending on user input, either MySQL or Oracle DataSource is created. If you are supporting only one database in the application then you don’t even need these logic. Just change the properties and you can switch from one database server to another. The key point through which Apache DBCP provide abstraction is setDriverClassName() method.

如您所见，根据用户输入，将创建MySQL或Oracle DataSource。如果您在应用程序中仅支持一个数据库，那么您甚至不需要这些逻辑。只需更改属性，就可以从一台数据库服务器切换到另一台。 Apache DBCP提供抽象的关键是setDriverClassName（）方法。

Here is the client program using above factory method to get different types of connection.

这是使用上述工厂方法来获得不同类型连接的客户端程序。

package com.journaldev.jdbc.datasource;

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

import javax.sql.DataSource;

public class ApacheCommonsDBCPTest {

	public static void main(String[] args) {
		testDBCPDataSource("mysql");
		System.out.println("**********");
		testDBCPDataSource("oracle");
	}

	private static void testDBCPDataSource(String dbType) {
		DataSource ds = DBCPDataSourceFactory.getDataSource(dbType);
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select empid, name from Employee");
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
				try {
					if(rs != null) rs.close();
					if(stmt != null) stmt.close();
					if(con != null) con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}

When you run above program, the output will be same as earlier program.

当您运行上述程序时，输出将与早期程序相同。

If you look at the Java JDBC DataSource and above usage, it can be done with normal DriverManager too. The major benefit of Java DataSource is when it’s used within a Context and with JNDI.

如果您查看Java JDBC数据源及以上用法，则也可以使用常规DriverManager来完成。 Java DataSource的主要优点是在Context内和JNDI一起使用时。

With simple configurations we can create a Database Connection Pool that is maintained by the Container itself. Most of the servlet containers such as Tomcat and JBoss provide it’s own Java DataSource implementation and all we need is to configure it through simple XML based configurations and then use JNDI context lookup to get the Java DataSource and work with it. This helps us by taking care of connection pooling and management from our application side to server side and thus giving us more time to write business logic for the application.

通过简单的配置，我们可以创建一个由容器本身维护的数据库连接池。大多数servlet容器（例如Tomcat和JBoss）提供了它自己的Java DataSource实现，我们所需要的只是通过基于XML的简单配置对其进行配置，然后使用JNDI上下文查找来获取Java DataSource并对其进行处理。通过照顾从应用程序端到服务器端的连接池和管理，这可以为我们提供帮助，从而使我们有更多时间为应用程序编写业务逻辑。

In next tutorial, we will learn how we can configure DataSource in Tomcat Container and use it in Web Application.

在下一个教程中，我们将学习如何在Tomcat容器中配置DataSource并在Web应用程序中使用它。