Month: October 2011

Dynamic Schema Creation In Hibernate

Scenario:

In real time,  you may need to generate database schema dynamically.

One solution to achieve this is with the usage of Hibernate as the provider for connecting to db.  The DB schema details are provided in a configuration file and can easily be modified based on the requirement. There is absolutely no need to write entity classes here. Those will be auto-generated!

Steps:

1. Add the following two additional lines to your hibernate.cfg.xml file:

<property name="default_entity_mode">dynamic-map</property>
<mapping resource="test.hbm.xml"/>

“test.hbm.xml” file is my mapping xml file where i mention the db schema details to be dynamically generated.

Setting the property “default_entity_mode” to “dynamic-map” tells hibernate to generate the schema dynamically by reading the mapping file. No entity classes are needed here!

2. Below is my sample mapping file (test.hbm.xml)

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class  entity-name="Student" table="Student">
<id name="id" column="STUDENT_ID" type="long">
<generator/>
</id>
<property  name="name" type= "string"  column="STUDENT_NAME"/>
<property name="lastName" type="string" column="STUDENT_LAST_NAME" />
</class>
</hibernate-mapping>

3. In my servlet’s init(), i  build
hibernate session factory which will create
the database dynamically. I call this servlet from the browser using the URL
“http://<server&gt;:8080/HibernateDynaMap-1.0/entity” which invokes it’s init().

A lightweight alternative to DB Visualizer and Oracle JDeveloper

Its been a while, i’m using hsqdb. Its simple, its light weight and it’s in-memory!

I thought I knew everything about it until I learnt today that it’s distribution has many tools to offer which are quite useful!

I’ll discuss one of such useful tool:

  • DatabaseManagerSwing

“DatabaseManagerSwing” tool is useful for connecting to almost all available DBs in the market (Oracle, DB2, MySQL, Informix, Pointbase, Postgresql, ofcourse hsqldb).

It’s very simple and an easy alternative of  heavy weight db client applications like DB Visualizer and Oracle JDeveloper. If you just intent to connect to DB and run some SQL commands and see results then this tool is definitely a very good alternative, especially when your PC’s performance already sucks!

You can download hsqldb here.

I’ll try to demo the usage of this tool by connecting to my “MySQL DB instance” called “webchat”.

  • Ofcourse, 1st step is to start your MySQL database server instance.
  • Download myql jdbc driver jar from here.
  • Copy the above jar to “”%HSQLDB_INSTALL_DIR%/lib” folder.
  • Go to “%HSQLDB_INSTALL_DIR” & Start “DatabaseManagerSwing” tool using the below command: java -cp lib/hsqldb.jar;lib/mysql-connector-java-5.0.8-bin.jar org.hsqldb.util.DatabaseManagerSwing
  • Below window opens up: 
  • Now enter the settings as below: (Remember my DB instance name is webchat and my db instance is running in localhost). Click OK.
  • Now the tool should connect to MySQL DB as below:

Apart from native LAF, there is also a Java and motif LAF of this tool as shown below:

Configuring MySQL in glassfish

Application Server Version: GlassFish Server Open Source Edition 3.1.1 (build 12)

Steps:

1. Download mysql JDBC driver here.

2. Extract the zip and copy the “mysql-connector-java-5.1.17-bin” jar to “%GLASSFISH_INSTAL_DIR%/lib” folder.

3. Re-start glassfish server.

4. Then create JDBC connection pool.

5.

6.

7.  Fill in atleast the below details in the property table.

  • DatabaseName (e.g. webchat)
  • Password (e.g. root)
  • URL (e.g. jdbc:mysql://localhost:3306/webchat)
  • Url (e.g. jdbc:mysql://localhost:3306/webchat)
  • ServerName (e.g. localhost)
  • User (e.g. root)

8. After the Step 7 is completed, the configuration of connection pool is done. After this “MySQL” should show as below:

9. Test the connectivity by clicking ping. Below snippet shows a successful connection :

10. Next step is to create a JNDI binding for this connection.

11.

12. With completion of this step, MySQL connection is bound with JNDI name “jdbc/mysqlwebchat”.