Java get datasource from connection

A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property for the server can be changed. The benefit is that because the data source’s properties can be changed, any code accessing that data source does not need to be changed.

A driver that is accessed via a DataSource object does not register itself with the DriverManager . Rather, a DataSource object is retrieved through a lookup operation and then used to create a Connection object. With a basic implementation, the connection obtained through a DataSource object is identical to a connection obtained through the DriverManager facility.

An implementation of DataSource must include a public no-arg constructor.

Method Summary

Gets the maximum time in seconds that this data source can wait while attempting to connect to a database.

Sets the maximum time in seconds that this data source will wait while attempting to connect to a database.

Methods declared in interface javax.sql.CommonDataSource

Methods declared in interface java.sql.Wrapper

Method Detail

getConnection

Connection getConnection() throws SQLException

getConnection

Connection getConnection(String username, String password) throws SQLException

getLogWriter

PrintWriter getLogWriter() throws SQLException

Retrieves the log writer for this DataSource object. The log writer is a character output stream to which all logging and tracing messages for this data source will be printed. This includes messages printed by the methods of this object, messages printed by methods of other objects manufactured by this object, and so on. Messages printed to a data source specific log writer are not printed to the log writer associated with the java.sql.DriverManager class. When a DataSource object is created, the log writer is initially null; in other words, the default is for logging to be disabled.

setLogWriter

Sets the log writer for this DataSource object to the given java.io.PrintWriter object. The log writer is a character output stream to which all logging and tracing messages for this data source will be printed. This includes messages printed by the methods of this object, messages printed by methods of other objects manufactured by this object, and so on. Messages printed to a data source- specific log writer are not printed to the log writer associated with the java.sql.DriverManager class. When a DataSource object is created the log writer is initially null; in other words, the default is for logging to be disabled.

setLoginTimeout

Sets the maximum time in seconds that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout. When a DataSource object is created, the login timeout is initially zero.

getLoginTimeout

Gets the maximum time in seconds that this data source can wait while attempting to connect to a database. A value of zero means that the timeout is the default system timeout if there is one; otherwise, it means that there is no timeout. When a DataSource object is created, the login timeout is initially zero.

createConnectionBuilder

default ConnectionBuilder createConnectionBuilder() throws SQLException

Report a bug or suggest an enhancement
For further API reference and developer documentation see the Java SE Documentation, which contains more detailed, developer-targeted descriptions with conceptual overviews, definitions of terms, workarounds, and working code examples.
Java is a trademark or registered trademark of Oracle and/or its affiliates in the US and other countries.
Copyright © 1993, 2023, Oracle and/or its affiliates, 500 Oracle Parkway, Redwood Shores, CA 94065 USA.
All rights reserved. Use is subject to license terms and the documentation redistribution policy.

Источник

Java DataSource, JDBC DataSource Example

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

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

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

Caching of PreparedStatement for faster processing
Connection timeout settings
Logging features
ResultSet maximum size threshold

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.

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.

--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. db.properties file:

#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.

Java JDBC DataSource — MySQL, Oracle Example

Let’s write a factory class that we can use to get MySQL or 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.

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 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 Name=Pankaj Employee Name=David ********** Employee Name=Pankaj Employee Name=Kumar Employee Name=Pankaj

Apache Commons DBCP Example

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

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.
Most of the code to get the MySQL and Oracle DataSource is similar, the only different is the implementation class that we are using.

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 DBCP library depends on Commons Pool library, so make sure they both are in the build path as shown in the image. Here is the DataSource factory class using BasicDataSource that is the simple implementation of 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. 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 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. 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. In next tutorial, we will learn how we can configure DataSource in Tomcat Container and use it in Web Application.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Источник