Posted In: JDBC

How to configure HSQLDB DataSource in Tomcat 9

1. HyperSQL DB datasource setup

1. Download HyperSQL DB JDBC driver
Location to download HyperSQL DB JDBC CONNECTOR (hsqldb-2.3.4.zip->hsqldb.jar)
Copy connector jar to either TOMCAT/LIB or application’s WEB-INF/lib
Note
In an enterprise application avoid adding it in application’s WEB-INF/lib. Mostly this connector will be configured at server level so that same version will be shared among multiple applications. It helps in upgrading jar at server without touching the application.

or use maven entry in pom.xml

<!-- https://mvnrepository.com/artifact/org.hsqldb/hsqldb-j5 -->
<dependency>
	<groupId>org.hsqldb</groupId>
	<artifactId>hsqldb</artifactId>
	<version>2.3.4</version>
</dependency>

2. Change context.xml to create HSQL DB datasource in TOMCAT
a. Add Server to Eclipse. Open /Servers/Tomcat v9.0 Server at XXXXXX/context.xml
b. or if you are using direct tomcat without eclipse then change /tomcat_install_dir/context.xml
c. or create META-INF/context.xml in your application

Then add ‘Resource’ to context.xml

Tomcat is installed as same server as HSQL DB
Tomcat 8 and 9 uses org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory which uses maxTotal, maxWaitMillis

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.hsqldb.jdbc.JDBCDriver" 
url="jdbc:hsqldb:hsql://localhost:9001/javausecase_db"/>

Tomcat is not installed as same server as HSQL DB

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.hsqldb.jdbc.JDBCDriver" 
url="jdbc:hsqldb:hsql://DB_SERVER:9001/javausecase_db"/>

Note
maxTotal – Maximum number of database connections in pool. Make sure you configure your max_connections large enough to handle all of your db connections. Set to -1 for no limit.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 total connections.

maxIdle: Maximum number of idle database connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

maxWaitMillis: Maximum time to wait for a database connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.

Tomcat 7 and below uses org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory which uses maxActive, maxWait

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxActive="50" maxIdle="30" maxWait="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.hsqldb.jdbc.JDBCDriver" 
url="jdbc:hsqldb:hsql://localhost:9001/javausecase_db"/>

Note
maxActive – (int) The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 active connections.

maxIdle – (int) The maximum number of connections that should be kept in the pool at all times. Default value is maxActive:100 Idle connections are checked periodically (if enabled) and connections that been idle for longer than minEvictableIdleTimeMillis will be released. (also see testWhileIdle)
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

initialSize – (int)The initial number of connections that are created when the pool is started. Default value is 10
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 initial connections.

3. Change web.xml, This is optional step. Rather if you are going to deploy it to Websphere in Production environment do not add this entry.

<resource-ref>
<description>HSQL Datasource example</description>
<res-ref-name>jdbc/javausecasedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

4. You can even create HSQL DB datasource through spring DBCP configuration. This is useful for testing application without any server.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver"/>
   <property name="url" value="jdbc:hsqldb:hsql://localhost:9001/javausecase_db"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create HSQL DB datasource through spring DBCP2
Apache Commons DBCP 2.1.1 for JDBC 4.1 (Java 7.0+)

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver"/>
   <property name="url" value="jdbc:hsqldb:hsql://localhost:9001/javausecase_db"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create HSQL DB datasource through java code

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("org.hsqldb.jdbc.JDBCDriver");
bds.setUrl("jdbc:hsqldb:hsql://localhost:9001/javausecase_db");
bds.setUsername("javausecasedbuser");
bds.setPassword("javausecasedbpassword");
bds.setInitialSize(5);

2. Use it in your application

1. Once datasource is configured on tomcat access it through Spring configuration

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/javausecasedb" />

2. To access HSQL DB through initial context

import javax.naming.Context;
import javax.naming.InitialContext;
..
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/javausecasedb");

Notice java:comp/env
The InitialContext is configured as a web application is initially deployed, and is made available to web application components (for read-only access). All configured entries and resources are placed in the java:comp/env portion of the JNDI namespace.

3. To access HSQL DB through @Resource (javax.annotation.Resource)

package com.javausecase;

import java.io.IOException;
import java.sql.Connection;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/Servlet2")
public class Servlet2 extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Resource(name = "jdbc/javausecasedb")
	private DataSource ds;

	public Servlet2() {
		super();
		System.out.println("Servlet2()");
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		Connection conn = null;
		try {
			// check datasource
			System.out.println("ds=" + ds);
			conn = ds.getConnection();
			System.out.println("ds.getConnection()=" + conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

3. Common issues

a. Incorrect parameters warning
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxActive is not used in DBCP2, use maxTotal instead. maxTotal default value is 8. You have set value of “50” for “maxActive” property, which is being ignored.
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxWait is not used in DBCP2 , use maxWaitMillis instead. maxWaitMillis default value is -1. You have set value of “10000” for “maxWait” property, which is being ignored.

Solution – Check tomcat version and dbcp version

b. org.hsqldb.jdbc.JDBCDriver Class not found

Solution – Check connector jar is in classpath

c. invalid authorization specification

Solution – Check use id / password in datasource configuration

Reference
1. JNDI Datasource HOW-TO

Tags: , , , , ,

by , on January 22nd, 2017

  • Categories