JBoss AS and JDBC DataSource tutorial

In this post I’d like to share my own expericence of creating of out-of-the-box database solution using JBoss AS, JPA and EJB 3.1

Here is short overview of our steps in this tutorial:

  1. We will deploy DDL onto the database server
  2. JBoss AS DataSource will be configured and tested against pure JDBC
  3. JPA entities will be created (using hibernate mapping files / annotations) [next post]
  4. JPA package will be configured and deployed onto the JBoss AS [next post]
  5. EntityManager in action will be show [next post]
Let’s start from database schema:

Database schema

We’ve got four potential entities: Category, Item, Parameter and Value. We’ll touch this question later and for now let’s review DDL. I’ll be using Apache Derby (running in network server mode)  in this tutorial. I like to use this database for tutorials due to the balance between SQL-99/2003 compatibility and memory footprint. Feel free to use your own favourite RDBMS if needed.

CREATE TABLE USERS.categories
(
	id           INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	name         VARCHAR(50) NOT NULL,
	description  VARCHAR(256)
);

CREATE TABLE USERS.items
(
	id           INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	name         VARCHAR(50) NOT NULL,
	category_id  INTEGER NOT NULL
);

CREATE TABLE USERS.parameters
(
	id           INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	name         VARCHAR(50) NOT NULL,
	description  VARCHAR(256)
);

CREATE TABLE USERS.pvalues
(
	item_id       INTEGER NOT NULL,
	parameter_id  INTEGER NOT NULL,
	pvalue         VARCHAR(128)
);

ALTER TABLE USERS.categories
	ADD CONSTRAINT UQ_categories_name UNIQUE (name);

ALTER TABLE USERS.parameters
	ADD CONSTRAINT UQ_parameters_name UNIQUE (name);

ALTER TABLE USERS.items ADD CONSTRAINT FK_items_categories
	FOREIGN KEY (category_id) REFERENCES USERS.categories (id);

ALTER TABLE USERS.pvalues ADD CONSTRAINT FK_values_items
	FOREIGN KEY (item_id) REFERENCES USERS.items (id);

ALTER TABLE USERS.pvalues ADD CONSTRAINT FK_values_parameters
	FOREIGN KEY (parameter_id) REFERENCES USERS.parameters (id);

Now lets deploy this schema onto the Deby. I’ll be using JBoss Developer Studio. Switch to the Database Development perspective and create connection (here is the step by step tutorial about this). Using this connection run our DDL script.

At this point we’ve deployed database schema and we’re ready to create JDBC DataSource. For full description of how to do it and how it works please refer to this resource. In few words we need to accomplish following steps:

1. Copy derbyclient.jar (from [$DERBY_HOME]/lib) to the [$JBOSS_HOME]/server/[$CONFIGURATION]/lib folder. In my case I copied jar from C:\…\db-derby-10.8.2.2-bin\lib to C:\…\jboss-6.0.0.Final\server\default\lib

2. Go to the [$JBOSS_HOME]/server/[$CONFIGURATION]/deploy (e.g. C:\…\jboss-6.0.0.Final\server\default\deploy) and create derby_ds.xml:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
  <jndi-name>DerbyDS</jndi-name>
    <connection-url>jdbc:derby://localhost:1527/sample;create=true;upgrade=true</connection-url>
    <driver-class>org.apache.derby.jdbc.ClientDriver</driver-class>
    <pool>
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
    </pool>
    <security>
      <user-name>tillias</user-name>
      <password>qwerty</password>
    </security>
    <timeout>
      <idle-timeout-minutes>5</idle-timeout-minutes>
    </timeout>
    <statement>
      <track-statements>true</track-statements>
    </statement>
  </local-tx-datasource>
</datasources>

3. Start JBoss AS server and validate that DataSource is caught. One way to do this is to open JBoss AS Administrative console (in my case http://127.0.0.1:8080/admin-console/) and locate DerbyDS there:

JBoss AS Admin Console

For additional certainty click on DerbyDS in the right pane and click “Test connection”:

Test DataSource

If you’re green with test then your DataSource is up and running. Now lets create simple EJB that will test our shema and show example of usage of pure JDBC stack inside bean:

@Stateless
@LocalBean
public class EjbJdbcBean {

	public EjbJdbcBean() {
	}

	@Schedule(hour = "*", minute = "*", second = "*/20", persistent = false)
	public void foo() throws SQLException, NamingException {
		InitialContext ctx = null;
		Connection conn = null;
		PreparedStatement ps = null;
		try {

			ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:/DerbyDS");

			conn = ds.getConnection();

			ps = conn
					.prepareStatement("insert into USERS.items(name,category_id) values(?,?)");
			Date ts = new Date();
			ps.setString(1, ts.toString());
			ps.setInt(2, 1 /* General category */);

			ps.execute();
			System.out.println("Inserted db record");
		} catch (Exception e) {
			System.out.println(e);
		} finally {
			cleanup(conn, ps);
			cleanupContext(ctx);
		}
	}

	private void cleanup(Connection conn, PreparedStatement ps) {
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				// we need silence here
			}

		if (ps != null)
			try {
				ps.close();
			} catch (SQLException e) {
				// we need silence here
			}
	}

	private void cleanupContext(InitialContext ctx) throws NamingException {
		if (ctx != null)
			ctx.close();
	}
}

As you can see we’re inserting records into items table every 20 seconds. Let’s deploy this bean, wait for a minute and validate that everything works like a charm:

SQL scrapbook queries

If you need more information of how to deploy this bean onto the JBoss AS you can use those screencasts. Also take a look at this awesome article named “How to Close JDBC Resources Properly – Every Time”.

Next time I’ll post additional cheat-sheet of how to create JPA entities, package them and create simple solid running database application for JBoss AS

Advertisements

3 Responses to “JBoss AS and JDBC DataSource tutorial”

  1. www.faces.com.tw Says:

    Very nice post. I just stumbled upon your blog and wanted to mention that I’ve truly enjoyed surfing around your blog posts. After all I will be subscribing for your rss feed and I’m hoping you write again
    very soon!

  2. Frances Says:

    I really like it when folks come together and share thoughts.
    Great blog, keep it up!

  3. http://lovelypartythings.com/ Says:

    I read this post completely on the topic of the comparison of latest and earlier technologies, it’s remarkable article.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: