Spring Hibernate SQL Tutorial with HSQLDB



This assumes that you have Java setup on your machine as illustrated in the "simple" Java tutorial.

Q. Why use HSQLB?
A. It can be useful in a number of ways.
  • Easy to get started and if you are a beginner, you can acquire or sharpen your SQL and database skills.
  • It is open-sourced, hence you can use it for your self-taught projects as a database. An alternative to MySQL database.
  • It is used in commercial projects to write unit tests for your data access layer. Apache Derby database is another alternative.
  • It is used in the ensuing tutorials on JDBC and Hibernate as the database.

Step 1: Download the latest version of HSQLB from  http://hsqldb.org/. I downloaded hsqldb-2.2.9.zip at the time of writing and extracted zip files. The HSQLDB is unpacked under c:\Tools as as hown below.



Step 2: You can start the server with the following command.

c:\Tools\hsqldb-2.2.9>java -cp ./hsqldb/lib/hsqldb.jar org.hsqldb.Server

Step 3: Open up another command prompt and start the DatabaseManager, with which you can execute SQLs. If you are new to SQL, then this is handy to practice and gain some SQL skills. Note: You need to have the HSQLDB server running before you can open the DatabaseManager.

c:\Tools\hsqldb-2.2.9>java -cp ./hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager




Once you press "Ok", the next window will look like



Step 4: Here you can type and execute some DDL(i.e. Data Definition Language) to create new table schema, etc and DML (i.e. Data Manipulation Language) to insert new data, etc. Type in the following commands  as grouped and press the "Execute" button.

a. DDL to create a table named "Course"

create table Course (course_id integer, name varchar(50), course varchar(50), PRIMARY KEY (course_id));

b. DML to insert a few records

insert into Course values (1,'Sam', 'Java');  
insert into Course values (2,'peter', 'J2EE');  
insert into Course values (3,'paul', 'JSF');  
insert into Course values (4,'jonathan', 'Hibernate');  
insert into Course values (5,'james', 'Spring');

c. DML to select the records we just had created

select * from Course;

You can now see the in memory table and the data by selecting the menu options View --> Refresh Tree.



Note: The syntax for these files are .properties, .lck etc. Since we have not specified any database name, it defaults to “test”.

This was a quick overview. Now, let's look at creating a named database.


HSQLDB supports three types of persistent tables, MEMORY tables, CACHED tables and TEXT tables.
I will be using the default MEMORY tables where data is held entirely in memory but any change to their structure or contents is written to the .script file. The script file is read the next time the database is opened, and the MEMORY tables are recreated with all their contents. So, the MEMORY tables are persistent. It is important to remember that the data in memory is written to the .script file when you shutdown your database properly (i.e. naturally by executing SQL “SHUTDOWN  (COMPACT | IMMEDIATELY”), the saved file will load the data into memory the next time the SQLDB server starts up. But if you stop the HSQLDB server abruptly in the command line by pressing [Ctrl] + [C] the data will not be written to the script file and consequently lost. Refer documentation for CACHED & TEXT tables.


Step 1: Cretate a file named server.properties under c:\Tools\hsqldb-2.2.9\hsqldb and type in the following database file name (i.e. tutorialdb) and the database alias name (mytestdb)

server.database.0=file:mydatabases/tutorialdb
server.dbname.0=mytestdb

You can now start the database server with the following command

c:\Tools\hsqldb-2.2.9\hsqldb>java -cp ./lib/hsqldb.jar org.hsqldb.Server




Once the server is started, you an see the following folder and files being created.



Step 2: Once the server has started, start the DatabaseManager where you can manage your schemas and data.

c:\Tools\hsqldb-2.2.9\hsqldb>java -cp ./lib/hsqldb.jar org.hsqldb.util.DatabaseManager


Note that the protocol is "hsql" and not "mem".

You can type in the relevant DDL and DML SQL queries as you did earlier. This is still an in-memory database and the data will be lost in between invocations.


Note: To persist your data from memory, you need to execute the "SHUTDOWN" SQL command, which will delete the tutorialdb.lock and and tutorialdb.log files and if you open the tutorialdb.script file, you can see your SQL commands that get executed when you reconnect again.

CREATE MEMORY TABLE PUBLIC.COURSE(COURSE_ID INTEGER PRIMARY KEY,NAME VARCHAR(50),COURSE VARCHAR(50))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO COURSE VALUES(1,'Sam','Java')
INSERT INTO COURSE VALUES(2,'peter','J2EE')
INSERT INTO COURSE VALUES(3,'paul','JSF')
INSERT INTO COURSE VALUES(4,'jonathan','Hibernate')
INSERT INTO COURSE VALUES(5,'james','Spring')


If you want to persist it directly to a file without having an alias, you can try.



Once you know how HSQLDB works, you can work on JDBC and Hibernate tutorials.





Labels:

Labels

.equals = operator abstract class abstract method abstract window toolkit Access Modifiers accessing java beans accessing javabeans action events actionperformed active addition Advanced Advanced Overloading AdvJavaBooks Agile development ajax alive AMQP and Android anonymous class anonymous inner class Ant ant tutorials anti patterns antipatterns Apache Camel api for jsp api for servlet api for servlets api jsp application context application scope application session Apps Architecture are you eligible for the ocmjd certificaiton are you eligible for the scjd certification arithmetic operator arpanet array construction array declaration array initialization array list array to list conversion arraylist arraylist of strings arraylist of types arraylist questions arraylists Arrays arrays in java ask for help assert assert in java assertions assertions in java assignment assignment operator Atlassian attribute visibility authentication authorization autoboxing autounboxing awt AWT Event Handling awt interview questions AWT Layouts awt questions awt questions and answers backed collection backed collections Basic Basics of event handling bean attributes bean properties bean scope Beginner best practices BigData blocked books boxing buffer size bufferedreader bufferedwriter business delegate business delegate pattern calendar case statement casting in java casting interview questions chapter review choosing a java locking mechanism choosing a locking mechanism choosing a thread locking mechanism class inside a method class questions class with no name class without a name classes interview questions Clipboard closing jsp tags code snap coding cohesion collection generics collection interview questions collection methods collection of types collection questions collection searching collection types Collections Collections Framework collections interview questions collections sorting colors in java swings colors in swing command line arguments communication between threads comparable comparator comparison operators compiling java classes computers concurrency example and tutorial config Configuration ConnectionPooling constructor creation constructor interview questions constructor overloading constructors in java containers contents of deployment descriptor contents of web.xml context context scope converting array to list converting list to array core java core java interview core java interview question core java interview questions core java questions core java; core java; object oriented programming CoreJava CoreJavaBooks CORS coupling create threads creating 2 dimensional shapes creating 2D shapes creating a frame creating a jframe creating a thread creating an arraylist creating an inner class creating an interface creating java beans creating java threads creating javabeans creating threads creating threads in java CSS cURL currency current thread determination custom tag library custom taglib custom taglibs custom tags CVS dao dao design pattern dao factory pattern dao pattern data access object data access object pattern data structure and algorithm database date and time tutorial date format dateformat dates deadlock deadlocks debugging Declarations decorator pattern decrement default deleting sessions deploy web app deployment deployment descriptor deployment descriptor contents deployment of web application deserialization deserialize design pattern design pattern interview questions design patterns Designpatterns destory method destroy destroying sessions determining current thread determining the current thread Developer Differences different types of collections display stuff in a frame displaying images displaying images in java swings displaying images in swings displaying text in a component division do while loop doget dohead dopost doput DOS Downloads drawing a line drawing an ellipse drawing circles drawing ellipses drawing lines Drools tutorial eBooks Eclipse Eclipse Tutorial Encapsulation encapsulation in java enhanced for loop entity facade pattern enumerations enumerations in java enums equal to equals equals comparison error and exception error codes error handling in servlets error page event handling in swings event listeners exam prep tips example servlet Examples exception exception handling exception handling in servlets exception handling interview questions exception handling questions Exceptions exceptions in java exceptions in web applications explicit locking explicit locking of objects file file navigation filereader filewriter final class final method FireBug first servlet FIX protocol FIX Protocol interview questions FIX protocol tutorial font fonts for each loop for loop form parameters form values formatting forwarding requests frame frame creation frame positioning frame swings front controller front controller design pattern front controller pattern fundamental.Java FXML Games garbage collection garbage collection interview questions garbage collection questions garbage collector gc gc questions general generic Generics generics collections Geo get get set methods getattribute getting bean property getting form values getting form values in servlet getting scwcd certified getting servlet initialization parameters getting sun certified Google Graphics2D gregorian calendar handling strings in java hash hash map hash table hashcode hashmap hashset hashtable head head request HeadFirst heap heaps hibernate hibernate interview questions hibernate interview questions and answers hibernate questions hibernate questions and answers Hibernate Tutorial HibernateBooks homework How To HTML HTML and JavaScript html form http request http request handling http request header http request methods http request servlet http request type http session httprequest httprequest methods httpservlet httpservlet interview questions httpservlet interview questions with answers httpsession httpsession interview questions httpsession questions HttpSessionActivationListener HttpSessionAttributeListener HttpSessionBindingListener if if else if else block if else statement Image IO implementing an interface Implicit objects increment info inheritance inheritance in java init init method Initialization Blocks inner class inner class inside a method inner classes innerclass installation instanceof instanceof operator IntelliJ interaction between threads interface interface interview interface questions interfaces interfaces in java interfaces interview questions internet history interrupting a thread interrupting threads Interview interview questions interview questions on design patterns interview questions on exception handling interview questions on java collections interview questions on serialization introduction to java threads introduction to jsps introduction to threading introduction to threads invalidating session Investment Banking IO Package iscurrentthread iterator J2EE j2ee api j2ee design pattern j2ee design pattern interview questions j2ee design patterns j2ee hibernate interview questions j2ee history j2ee interview j2ee interview questions j2ee mvc j2ee mvc pattern j2ee programmer j2ee questions j2ee servlet api j2ee session j2ee struts interview questions java java 5 tutorial Java 8 java arrays java assertions java assignments java awt questions java bean java bean scope java beans java beginners tutorial Java career java certification Java Class java collection interview questions and answers java collection tutorial java collections java collections interview questions java constructors java currency Java CV java data base connectivity java database connectivity java database connectivity interview questions and answers java dates java design pattern java design patterns java developer certification Java EE java encapsulation java enums java event listeners java exceptions java formatting java garbage collection java garbage collector java gc java heap Java I/O java inheritance java input output Java Interface Java Interview Java Interview Answers Java Interview Questions Java Introduction java io java IO tutorial java iterator java jdbc Java JSON tutorial Java Key Areas java lists java literals java locks nested Java Media Framework java methods java multithreading Java multithreading Tutorials java nested locks java networking tutorial java numbers Java Objects java operators java overloading java parsing Java Programming Tutorials java race conditions java regex java regular expressions Java resume java scjp java searching java serialization java server pages java server pages api java server pages questions java spring interview questions. j2ee spring interview questions java stack java strings java swing java swing event listeners java swing frame java swing images java swings java swings images java thread explicit locking java thread lock scope java thread locking java thread locking mechanism java thread locking objects java threads java threads race condition java tips java tokenizing Java Tools Java Tutorial java ui questions Java Utilities java variables java wrappers Java xml tutorial java.lang java8 javabean javabean accessing javabean scope JavaBeans javac JavaEE JavaFX JavaFX 3D JavaFX 8 JavaOne JavaScript JavaTips JDBC jdbc driver jdbc example jdbc interview questions jdbc interview questions and answers jdbc interview questions with answers jdbc sample code JDBC Tutorial jdbc type 1 driver jdbc type 2 driver jdbc type 3 driver jdbc type 4 driver Jdeveloper JDK JDK8 JEE Tutorial jframe jframe creation jframe position jframe positioning JIRA JMeter JMS JMX join() joining threads JPA JQuery JS JSF JSF Tutorial JSONP JSP jsp and java beans jsp and servlets jsp and xml jsp api jsp code jsp compilation jsp conversion jsp directives jsp error page jsp error page directive jsp implicit objects jsp interview jsp interview questions jsp introduction jsp intvw questions jsp life jsp life cycle jsp life-cycle jsp lifecycle jsp page directive jsp questions jsp sample jsp scripting jsp scriptlets jsp servlets jsp summary jsp synopsis jsp tag libraries jsp tag library jsp taglib jsp tags jsp technology jsp to servlet jsp to servlet conversion jsp translation jsp usage jsp usebean jsp xml tags jsp xml tags usage jsp-servlet jsp:getProperty jsp:setProperty jsp:usebean jsps JSTL JUnit testing keyword synchronized keyword volatile Keywords Lambda Expressions Learning libraries life cycle life cycle of a jsp life cycle of a servlet life cycle of a thread life cycle of jsp life cycle of threads lifecycle of a thread linked list linkedhashmap linkedhashset linkedlist linux List listeners lists Literals locale lock manager pattern lock scope locking objects using threads log Logging logging errors logical and logical operators logical or loops loosely coupled making an arraylist making threads sleep making threads sleep for time MapReduce maps maps usage Maven Maven Tutorial max priority member access method arguments method local inner class method overloading method overriding method return types methods creating classes min priority Miscellaneous mobile mock exam model view controller model view controller design pattern model view controller pattern Multi Threading Multi-threading multiple threads multiplication multithreading multithreading in java multithreading interview questions multithreading questions mvc mvc design pattern mvc pattern MyEclipse mysql nested java lock nested java locks nested java thread locks nested locks nested thread locks NetBeans Networking new news nio NonAccess Modifiers norm priority normal inner class Normalization not equal to Notepad notify notifyall number format numberformat numbers object comparison object notify object orientation object oriented object oriented programming Object Oriented Programming in java objects interview questions ocmjd certification ocmjd certification eligibility OO OO Java oops OpenCSV OpenCV opening jsp tags OpenJDK OpenJFX Operators or Oracle Oracle ADF Mobile Oracle Certified Exams oracle certified master java developer oracle database ORM other topics out overloading overloading constructors overloading in java overriding page page directive page scope parsing passing variables passing variables to methods performance Platform Playing with Numbers points to remember polymorphism positioning a frame post practice exam Primitive Casting primitive variables printwriter priority queue priority queues priorityqueue priorityqueues private processing form values Products programming Projects protected public put questions questions on garbage collection questions on java strings queue quick recap quick review race conditions read objects from stream reading http request header RealTime_Tips redirecting to another servlet redirection reference reference variable casting reference variables Refreshing Java regex Regular Expressions regular inner class relational operators reminder request request dispatcher request forwarding request header request object. httpservletrequest request scope requestdispatcher response RESTClient RESTful retrieving values from session return error codes return types returning values runnable runnable interface running running java programs RUP sample jsp sample questions sample questions scwcd sample servlet scanner Scene Builder scjd certification scjd certification eligibility requirements scjp SCJP Certification scjp exam scjp exam questions scjp exam sample questions scjp questions scjp test scjp test questions scope scope of java locks scope of java thread locks scope of locks scripting in jsp scriptlet tags scriptlets scriptlets in jsp pages scwcd scwcd certification scwcd certification practice exam scwcd exam scwcd exam questions scwcd jsp summary scwcd mock exam scwcd mock exam answers scwcd practice exam scwcd practice test scwcd questions scwcd test SDLC searching searching arrays searching collections searching in java searching treemap searching treesets security self assement self assement scwcd self assessment scjp self test self test scjp self test scwcd send error method senderror method sending error code to browser serialization serialization in java serialization interview questions Serialization on Swing serialization questions service service method servlet servlet and forms servlet and jsp servlet api servlet attributes servlet code servlet container servlet context servlet error handling servlet exception handling servlet handling http request servlet initialization servlet initialization parameters servlet interview servlet interview questions servlet interview questions with answers servlet intvw questions servlet life cycle servlet lifecycle servlet questions servlet questions with answers servlet request servlet request dispatcher servlet request type servlet skeleton servletcontext servletcontextevent servletrequest Servlets servlets and jsps servlets api servlets details servlets request handling session session clean up session event listeners session facade pattern session interview questions session invalidation session listeners session management session questions session scope session timeout session tracking through url rewriting set collections set status method setattribute sets setstatus method setting bean property setting request type short circuit operators Singleton sleep sleeping threads soapUI Software Installation sorting sorting arraylist sorting arrays sorting collections special collections special inner classes split spring spring and hibernate interview questions spring batch Spring Core Spring Framework Spring Integration spring interview questions Spring JDBC Spring MVC Spring security Spring tutorial SQL SQL and database tutorial examples SQL Tutorial SSL stack stacks stacks and heaps static static class static declaration static imports static inner static inner class static method Static variable stopped stopping a thread stopping thread stopping threads Stored Procedure storing values in session Streams strictfp StrictMath string string buffer string builder string class string formatting String Handling string interview questions string manupulation string questions string tokenizer stringbuffer stringbuffer questions stringbuilder Strings strings in java struts Struts 1 Struts 1.2 Struts 2 struts framework interview questions struts interview questions struts interview questions with answers struts mvc interview questions struts questions Struts2 StrutsBooks submitting request subtraction Sun Certification sun certified java developer Sun Certified Java Programmer swing swing action performed swing and colors Swing Components swing event handling swing event listeners swing events Swing Hacks swing images Swing Look And Feels swings swings frame switch block switch case block switch case statement Sybase Sybase and SQL Server synchronization synchronized code synchronized keyword synchronized method System Properties tag lib tag library tag-lib taglibs tags TDD Technical Blogging ternary operator Test Driven Development test scjp Testing the context the session the volatile keyword thread thread class thread deadlocks thread interaction thread interruption thread life cycle thread lifecycle thread lock scope thread locks thread notify thread priorities thread race conditions race conditions in threads thread sleep thread states thread stoppage thread stopping thread synchronization thread syncing thread yield Threads threads in java threads interview questions threads life cycle threads questions tibco tightly coupled tips tips and tricks tips.FXML tokenizing Tomcat Tools toString transitions treemap treeset tricks Tricks Bag try catch try catch finally. finally block Tutorial type casting in java ui programming with java swings UML unboxing unit testing unix url rewriting use bean usebean using a arraylist using collections using colors using colours using command line arguments using different fonts using expressions using font using fonts using fonts in swings using hashmap using http session using httpsession using iterator using java beans in jsp using javabean in jsp using javabeans in jsp using javac using lists using maps using request dispatcher using scriptlets using session persistense using sets using special fonts using system properties using the jsp use bean using treeset using use bean Using Variables using volatile Using Wrappers using xml in jsp Util pack value object value object design pattern value object pattern var-args varargs Variable Arguments Variables vector vector questions vectors visibility vo pattern volatile volatile keyword volatile variables wait method waiting web app exception handling web app interview web application deployment web application exceptions web application scope web application security web component developer web component developer certification web context web context interfaces web context listeners web interview questions web security web server web servers Web services web.xml web.xml deployment webapp log weblogic website hacking website security what are threads what is a java thread what is a thread what is thread while loop windows windows 8 wrapper classes wrappers write objects to stream WSAD xml xml and jsp xml tags xslt yield()