BasicDatasource

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.

Download
You can download the full source code of this example here: BasicDataSourceExample.zip

Arpit Gautam

Arpit has graduated from Computer Science and Engineering Department from the Institute of Technology and Management Gurgaon. He is working in enterprise product development since a decade and worked on desktop, mobile and server side applications using java. During his studies, he participated in various coding contests and technical paper presentations. He is working as a lead Software Engineer in Workforce Management domain where he is mainly involved with projects based on Java application and C++ system programming. He is curious about writing agile code which can adapt as business changes. He likes to experiment with open source technologies and java tech stack in his spare time.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button