org.apache.commons.dbcp.BasicDataSource Example

In this example, we shall show you how to use BasicDataSource of Apache commons dbcp library. DBCP is a part of apache common components and intended for database connection pooling.

BasicDataSource is a basic implementation of javax.sql.DataSource

Using BasicDataSource, one can easily connect to a Relational Database as we will show in following example.

For this example, we will be using SqLite database. We will be creating a file called test.db in project directory. This file will contain a full fledged database which can be queried via sql.
Advantages of using SqLite are as follows:

• It is light weight and does not require any system services to be installed on targeted system.
• There will be no need to install any RDBMS in your machine to run this example.
• Its database will be a single file and therefore easy to wipe clean.

1. BasicDataSource Constructors

BasicDataSource only supports default constructor

• BasicDataSource()

2. BasicDataSource common methods

Following are some commonly used methods from BasicDataSource

• void close()
  This method closes all idle connections stored in connection pool.

• Connection getConnection()
  Returns a connection to the database. This connection will be further utilised for interacting with underlying database.

• int getInitialSize()
  This method returns initial size of created connection pool.

• int getMaxActive()
  This method returns maximum number of active connections that can be allocated at same time.

• int getMaxIdle()
  This method returns maximum number of connections that can lie idle in the connection pool.

• int getNumIdle()
  This method returns number of idle connections in the pool.

• int getNumActive
  This method returns number of active connections in the pool.

• void setUrl(String url)
  This method sets the url from where database is to be accessed.

• void setDriverClassName(String driverClassName)
  This method sets the className for database driver. This method must be used before connection pool initialization.

• void setUsername(String username)
  This method sets the user name to be used for accessing underlying database.

• void setPassword(String password)
  This method sets the password to be used for accessing underlying database.

3. Example of using BasicDataSource in java

BasicDataSourceExample.java

package com.javacodegeeks;

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

import org.apache.commons.dbcp.BasicDataSource;

public class BasicDataSourceExample {


	public static void main(String args[]) throws SQLException {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("org.sqlite.JDBC");
		dataSource.setUrl("jdbc:sqlite:test.db");
		createTable(dataSource);
		insertQuery(dataSource);
		selectQuery(dataSource);
		dropTable(dataSource);

	}

	private static void insertQuery(BasicDataSource dataSource) throws SQLException {
		Statement stmt = dataSource.getConnection().createStatement();
		String sql = "INSERT INTO Users VALUES('1','tom','chasing jerry')";
		stmt.executeUpdate(sql);
		sql = "INSERT INTO Users VALUES('2','jerry','eating chesse')";
		stmt.executeUpdate(sql);
		stmt.close();

	}

	private static void dropTable(BasicDataSource dataSource)
			throws SQLException {
		Statement stmt = dataSource.getConnection().createStatement();
		String sql = "DROP TABLE Users";
		stmt.executeUpdate(sql);
		stmt.close();
		System.out.println("Table dropped");

	}

	private static void createTable(BasicDataSource dataSource)
			throws SQLException {
		Statement stmt = dataSource.getConnection().createStatement();
		String sql = "CREATE TABLE 'Users' ('id'	TEXT,'name'	TEXT,'passion'	TEXT,PRIMARY KEY(id));";
		stmt.executeUpdate(sql);
		stmt.close();
		System.out.println("Table created");

	}

	private static void selectQuery(BasicDataSource dataSource)
			throws SQLException {
		Connection connection = null;
		PreparedStatement statement = null;
		System.out.println("*********************Selecting data************************");
		System.out.println();

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement("SELECT * FROM Users");
			ResultSet rs = statement.executeQuery();

			while (rs.next()) {
				String s = String.format("ID:%s Username:%s Passion:%s",
						rs.getString("id"), rs.getString("name"),
						rs.getString("passion"));
				System.out.println(s);
				System.out.println();
			}
			System.out.println("");
			System.out.println("*******************************************************");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (statement != null)
				statement.close();
			if (connection != null)
				connection.close();
		}
	}
}

4. Output

Table created
*********************Selecting data************************

ID:1 Username:tom Passion:chasing jerry

ID:2 Username:jerry Passion:eating chesse


*******************************************************
Table dropped

5. Download source code

This was an example of BasicDataSource of Apache commons dbcp module.