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
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(0, true));
out.write("<br/>DatabasePath : " + server.getDatabasePath(0, true));
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 :
Comments
Thanks