ABOUT HSQLDB AND HOW TO SET UP FOR YOUR PROJECT

This is very light weight database and very useful during project development. You can use any process to connect to this database like simple jdbc code, hibernate or even iBatis.
Please follow these steps:

1. Download the latest version of HSQLDB from here.
2. Choose package hsqldb and click on download lik and choose hsqldb_1_8_0_10.zip (version may be change in your case), size would be approx 3 to 4 MB..
2. Save your local pc any where and unzip say d:\hsqldb_1_8_0_10.
3. Then go to D:\hsqldb_1_8_0_10\hsqldb\demo and double click on runManager.bat
4. You will get new window then no need to any thing except URL. You can give any drive location of your pc like d:\mydatabase and you have to give one database name like demodatabase then your complete URL become jdbc:hsqldb:file:d:\mydatabase\demodatabase

Here is complete info for that window

Type: HSQL Database Engine Standalone
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:d:\mydatabase\demodatabase
User: sa
password: (Let it be blank)

Click on the OK button.
Here you will get one text box, where you put your SQL command and click on Execute button. For example you can write
create table student (sname varchar(100), roll int); and click on execute button, if you get message update count o means your command executed successfully.
If every thing is ok then you can insert data in that table like
insert into student values ('Binod',110);
insert into student values ('Ambani',120);
insert into student values ('Mittal',110);
insert into student values ('Birla',110);
commit;
and click on execute button.
Now, write sql for see all the data in student table using
select * from student.

Now if you check your d:\mydatabase folder, you will get two file one is demoDatabase.properties and another one is demoDatabase.script. The best thing is here that you can export your database only with these two files. :)

How to use Primary key and Foreign Key in HSQLDB
create table school (id integer primary key, name varchar(100), address varchar(200));
create table student(roll integer primary key, name varchar(200), school_id integer, FOREIGN KEY (school_id) REFERENCES SCHOOL(ID))

How to use HSQLDB using Java jdbc code
1. First of all add the hsqldb.jar (D:\hsqldb_1_8_0_10\hsqldb\lib) in your project class path.
2. Write simple java jdbc code like this say Database.java
import java.io.Reader;
import java.sql.Connection;import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Database {
Connection connection = null;
public static void main(String[] args) {
Database check = new Database();
check.getConnection();
}
public void getConnection(){
try {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:file:d:\\mydatabase\\demodatabase";
Connection con = DriverManager.getConnection(url, "sa", "");
Statement stmt = con.createStatement();
ResultSet resultSet = stmt.executeQuery("SELECT * from student");
while( resultSet.next() ) {
String name = resultSet.getString(1);
int roll = resultSet.getInt(2);
System.out.println("Name :: "+name+" ");
System.out.println("Roll :: "+roll);
System.out.println("***************************");
}
stmt.close();
} catch( Exception e ) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}

IMP 1:
You can use the normal SQL for data insertion into HSQLD, but at the end of the code you have to use stmt.execute("SHUTDOWN"); Otherwise data would not saved permanently.

IMP 2:
If you are using any web application server for your project and want to use HSQLDB then you have to copy hsqldb.jar in your server respective lib folder. In case of Webshpere Application server or RAD, you have to copy hsqldb.jar to this location C:\Program Files\IBM\Rational\SDP\6.0\runtimes\base_v6\lib (Path may be change in your case).

IMP 3:
Some time you will not get the desired data in database or in application server. Then please close the HSQLDB Console and do your operation in application server. After that you can open HSQLDB console and will get all updated data.

How to use HSQLDB with iBatis

I am giving step by step, it would be very useful for new learner.
Make an folder javasource\src
Put three file in that src
1. SqlMapConfig.xml
2. Student.xml
3. Student.java
4. Database.java

Put these two jar file in that folder (a) ibatis-2.3.0.677.jar (b) hsqldb.jar

SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd%22;
<sqlMapConfig>
<settings useStatementNamespaces="true"/>
<transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="org.hsqldb.jdbcDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:hsqldb:file:c:\\Hsqldb\\demoDatabase"/> <property name="JDBC.Username" value="sa"/> <property name="JDBC.Password" value=""/> <property name="JDBC.DefaultAutoCommit" value="true"/>
</dataSource> </transactionManager>
<sqlMap resource="com//target//Student.xml"/> </sqlMapConfig>

Student.xml

< ?xml version="1.0" encoding="UTF-8"? >
< !DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" http://ibatis.apache.org/dtd/sql-map-2.dtd >
<>
< id="insert" parameterclass="Student">
insert into student values (#sname#, #city#);
< /insert >
< id="getCount" resultclass="Integer">
select count(*) from student;
< /select >
< id="getAll" resultclass="com.target.Student">
select * from student
< /select >
< /sqlMap >

Student.java
public class Student {
private String sname;
private String city;
public String getCity() { return city; }
public void setCity(String city) { this.city = city; }
public String getSname() { return sname; }

public void setSname(String sname) { this.sname = sname; }

public Student() {} public Student(String sname, String city) {
this.sname = sname; this.city = city;
}
}

Database.java
import java.io.Reader;
import java.util.Iterator;
import java.util.List;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class Database {
public static void main(String[] args) {
Database check = new Database();
check.getDataUsingIbatis();
check.insertDataUsingIbatis();
}
public void getDataUsingIbatis(){
try{
Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
List students = (List)sqlMap.queryForList("getAll");
for(Iterator ii=students.iterator();ii.hasNext();){
Student c = (Student)ii.next();
System.out.print("Name :: " + c.getSname()+" ");
System.out.println("City :: " + c.getCity());
}
System.out.println"=======================");
}
catch(Exception e){
System.out.println("SOME PROBLEM :: "+e);
e.printStackTrace();
}
}

public void insertDataUsingIbatis(){
try{
Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
System.out.println("*------- Inserting information in Student Table -------*");
Student student=new Student("Binod Suman","UK");
sqlMap.insert("insert",student);
System.out.println("Record Inserted Successfully ");
sqlMap.getDataSource().getConnection().createStatement().execute("SHUTDOWN"); }
catch(Exception e){
System.out.println("SOME PROBLEM :: "+e); e.printStackTrace();
}
}
}

Now compile all java class and run Database.class

Hope you will get your required result. :)

How to use Sequence in HSQLDB

SQL for create sequence:

create sequence seq_account_id as integer start with 1 increment by 1;

Now you can use "NEXT VALUE FOR seq_account_id" for insert next value in any table. Like insert into account values (NEXT VALUE FOR seq_account_id,'Binod', 'USA');

If you want to see the next value from SQL then you have to put one trick here :)

Step 1: First create one dummy table like dual (copy form Oracle :) )

CREATE TABLE dual (test INTEGER NOT NULL);

Step 2: Insert one dummy data INSERT INTO dual VALUES (1);

Step 3: Use this SQL to see the next value of your sequence

select NEXT VALUE FOR seq_account_id FROM dual

How to use the HSQLDB Sequence in iBatis :

<insert id="insertAccount" parameterClass="com.target.Account">
insert into account values (NEXT VALUE FOR seq_account_id,#name#, #city#);
</insert>

You have to only set name and city propert in the Account POJO.

If you want to ues the current account_id value to insert in other table then sequence will not help you like oracle then you have play some game like

< select id="getMaxAccount" resultClass="Integer" >
select max(id) from account;
< /select >

and use this below code in java

int maxID = ((Integer)sqlMap.queryForObject("getMaxAccount")).intValue();

How to save batch data in HSQLDB using iBatis

<insert id="insert_batch_student" parameterClass="java.util.Map">
insert into student values (#sname#, #city#);
</insert>

Java Code

public int insertManyStudent(){

int result=1;

try{

Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");

SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

sqlMap.startTransaction();

sqlMap.startBatch();

Map map=new HashMap();

map.put("sname","Binod Satyam 1"); map.put("city","USA 1");

sqlMap.insert("insert2",map); map.clear();

map.put("sname","Binod Satyam 2"); map.put("city","USA 2");

sqlMap.insert("insert2",map); map.clear();

map.put("sname","Binod Satyam 3"); map.put("city","USA 3");

sqlMap.insert("insert2",map); map.clear();

map.put("sname","Binod Satyam 4"); map.put("city","USA 4");

sqlMap.insert("insert2",map); map.clear();

int rows=sqlMap.executeBatch();

System.out.println("Total record saved " + rows);

sqlMap.commitTransaction();

sqlMap.getDataSource().getConnection().createStatement().execute("SHUTDOWN");

}catch(Exception e){

System.out.println("SOME PROBLEM :: "+e); e.printStackTrace(); result=0; }

return result;

}

Wait for some thing new in my next blog :)