Skip to main content

HSQLDB - Handling Database Programatically (In-Process and Server Mode at a time )


HSQLDB database it a very small database that can be controlled from you application. you can start and stop the database by writing the programming statement. This feature enable to use this database in your application with programmatic control. You can handle the database by writing some java codes.

When you use the database with In-Process mode, you will not able to access it other ways. In the In-Process mode, database become the part of your application and it does not run as server on other port number. So using the database In-Process mode is good when you are going to deploy the webapplication on the live server but at the time of development you have to avoid it. At the time of development, you need to look into database table so queckly to see the changes after executing any functionalityies. So you require the way to access the same dtabase from datbase client. But, if the database is running in the in-process mode, it doesn't open any port to access via database client. To access same database from database client, you need to run the database in Server mode.

But, if you are handling the daabase programmaticlly, you can access it from database client outside from you application.

Here is a example that shows how to use the HSQLDB programmaticlly
 Download Source Code

DatabaseSetup.java :
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.hsqldb.server.Server;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DatabaseSetup extends HttpServlet {
    Connection con;
    Server server;

    public void init() throws ServletException {
        server = new Server();
        server.setAddress("localhost");
        server.setDatabaseName(0"mydb1");
        server.setDatabasePath(0"file:E:/hsqldb_databases/db");
        server.setPort(1234);
        server.setTrace(true);
        server.setLogWriter(new PrintWriter(System.out));
        server.start();
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace(System.out);
        }
        try {
            con = DriverManager.getConnection(
                    "jdbc:hsqldb:hsql://localhost:1234/mydb1""SA""");
            con.createStatement()
                    .executeUpdate(
                            "create table contacts (name varchar(45),email varchar(45),phone varchar(45))");
            ServletContext context = getServletContext();
            context.setAttribute("con", con);
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();
        String reqCommand = req.getParameter("command");
        if (reqCommand.equalsIgnoreCase("info")) {
            out.write("<br/>Address : " + server.getAddress());
            out.write("<br/>Database Name : " + server.getDatabaseName(0true));
            out.write("<br/>DatabasePath : " + server.getDatabasePath(0true));
            out.write("<br/>Port : " + server.getPort());
            out.write("<br/>DefaultWebPage : " + server.getDefaultWebPage());
            out.write("<br/>ProductName : " + server.getProductName());
            out.write("<br/>ProductVersion : " + server.getProductVersion());
            out.write("<br/>Protocol : " + server.getProtocol());
            out.write("<br/>ServerId : " + server.getServerId());
            out.write("<br/>State : " + server.getState());
            out.write("<br/>StateDescriptor : " + server.getStateDescriptor());
            out.write("<br/>WebRoot : " + server.getWebRoot());
        }
        if (reqCommand.equalsIgnoreCase("stop")) {
            server.setLogWriter(out);
            server.shutdown();
            out.write("Database has been stopped.");
        } if (reqCommand.equalsIgnoreCase("start")) {
            server.setLogWriter(out);
            server.start();
            out.write("Database has been started.");
        }else {
            out.write("No command matched");
        }
        out.write("<br/><a href='ViewRecords'>View Records</a>");
        out.write("<br/><a href='index.html'>Home</a> ");
    }
}

Register.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class Register extends HttpServlet {
    Connection con;
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
        String name=request.getParameter("name");
        String email=request.getParameter("email");
        String phone=request.getParameter("phone");
        try {
            ServletContext context=getServletContext();
            con=(Connection)context.getAttribute("con");
            PreparedStatement pst=con.prepareStatement("insert into contacts values(?,?,?)");
            pst.clearParameters();
            pst.setString(1, name);
            pst.setString(2, email);
            pst.setString(3, phone);
            int i=pst.executeUpdate();
            out.write(i+" records inserted, <a href='ViewRecords'>View Records</a>");
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
    }
}


ViewRecords.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ViewRecords extends HttpServlet {
    Connection con;
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
        try {
            ServletContext context=getServletContext();
            con=(Connection)context.getAttribute("con");
            PreparedStatement pst=con.prepareStatement("select * from contacts");
            pst.clearParameters();
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                out.write("<br/>"+rs.getString(1));
                out.write(", "+rs.getString(2));
                out.write(", "+rs.getString(3));
            }
            out.write("<hr/><a href='index.html'>Home</a> ");
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
    }

}
web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.5"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
 <display-name>UsingHsqldb1</display-name>
 <servlet>
  <display-name>Register</display-name>
  <servlet-name>Register</servlet-name>
  <servlet-class>Register</servlet-class>
 </servlet>
 <servlet>
  <display-name>ViewRecords</display-name>
  <servlet-name>ViewRecords</servlet-name>
  <servlet-class>ViewRecords</servlet-class>
 </servlet>
 <servlet>
  <display-name>DatabaseSetup</display-name>
  <servlet-name>DatabaseSetup</servlet-name>
  <servlet-class>DatabaseSetup</servlet-class>
  <load-on-startup>1</load-on-startup>
 </servlet>
 <servlet-mapping>
  <servlet-name>Register</servlet-name>
  <url-pattern>/Register</url-pattern>
 </servlet-mapping>
 <servlet-mapping>
  <servlet-name>ViewRecords</servlet-name>
  <url-pattern>/ViewRecords</url-pattern>
 </servlet-mapping>
 <servlet-mapping>
  <servlet-name>DatabaseSetup</servlet-name>
  <url-pattern>/DatabaseSetup</url-pattern>
 </servlet-mapping>
</web-app>

index.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
  <head>
    <title>Using Hsqldb</title>
  </head>
  <body>
  <p>
  
  </p>
  
  <h4><a href='DatabaseSetup?command=info'>Database Information</a></h4>
  <h4><a href='DatabaseSetup?command=start'>Start Database</a></h4>
  <h4><a href='DatabaseSetup?command=stop'>Stop Database</a></h4>
  <hr/>
  <h2>Add records:</h2>
   <form method = "post" action = "Register">
    User Name<br/>
    <input type = "text" name = "name"/>
    <br/>
    Email<br/>
    <input type = "text" name = "email"/>
    <br/>
    Phone no<br/>
    <input type = "text" name = "phone"/>
    <br/>
    <input type = "submit" values = "Submit"/>
    </form>
  </body>
</html>

See also : 

In Process Mode of HSQLDB in web application.

Using HyperSQL (HSQLDB)

 


Comments

Anonymous said…
Very interesting article.

Thanks
Anonymous said…
too hard for me... i need a dummy example of web use of HSQLDB!!
This has helped me a lot. Thank you!
Anonymous said…
Good example about how exactly to use the HSQLDB in app dev and finally migrate it to the in-process mode. Nice work !!!

Popular posts from this blog

Using HyperSQL (HSQLDB)

HSQLDB is a portable RDBMS implemented in pure java. It can be embedded with your application as well as can be used separately. It is very a small database that supports almost all features of the standard database system. It comes with small jar file that can be found in lib folder. The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs. Core components of jar file are : HyperSQL RDBMS Engine (HSQLDB), HyperSQL JDBC Driver, Database Manager, and Sql Tool. Installing and Using Download: download latest release of HyperSQL database from http://hsqldb.org website and extract it. You will see following contents. Here "bin" directory contains some batch files those can be used to run a swing based GUI tool. You can use runManagerSwing.bat to connect to database, but database must be on before running it. Directory lib contains File hsqldb.jar . It is the database to be used by you. Running database First

How to handle values from dynamically generated elements in web page using struts2

Some time you will see the form containing the button " Add More " . This facility is provided for the user to get the values for unknown number of repeating for some information. for example when you are asking to get the projects details from user, you need to put the option to add the more project for the user since you don't known how many projects user have. In the HTML form, you repeat the particular section to get the multiple values for those elements. In Html page , you can put the option to add new row of elements or text fields by writing the java script or using JQuery API. Now, the question is that how to capture the values of dynamically generated text fields on the server. Using the servlet programming you can get the values by using getParameters() method that resultants the array of the parameter having the same name. But this limit you to naming the text fields in the HTML form. To ally this approach, you have to take the same name for t

In Process Mode of HSQLDB in web application.

If you want to use the database into your web application, you can use the HSQLDB in In_Process mode. In this mode, you can embed the HSQLDB into your web application and it runs as a part of your web application programm in the same JVM. In this mode, the database does not open any port to connect to the application on the hosing machine and you don't need to configure anything to access it. Database is not expposed to other application and can not be accessed from any dabase tools like dbVisualizer etc. In this mode ,database will be unknown from any other person except you. But in the 1.8.0 version, you can use Server intance for external as well as in process access.  To close the databse, you can issue SHUTDOWN command as an SQL query.   In the in-process mode, database starts from JDBC with the associated databse file provided through  connection URL. for example   DriverManager.getConnection("jdbc:hsqldb:mydatabase","SA","");   Here myd