Anyways, getting back to work. I’ve started to realize the power of ANT after starting my database with it as well as creating all tables with it as well. And guess what I was as easy as putting knife on butter.
An Overview of Object-Relational Mapping (ORM)
Majority of software application uses database to store application related information. One way to perform this storage related task is writing EJBs for storage where we map object with our Bean class. But this will only be the choice if I’m asked to work in distributed environment or I need more secure way of storing data.
ORM basically map database field with simple POJO. Although I have to still provide mapping between POJO and database field. This is typically done out side in xml file. Hibernate is one such ORM, which will be used for this project.
HSQLDB
HSQLDB is lightweight java database engine.
I’ve installed db on my workstation and copied hsqldb.jar in my lib folder. Next think
1. I would be doing is to start server.
args="${hfile} -dbname.0 ${halias} -port ${hport}"/>
This is from where my view about ANT started getting credited. The execution of above script will start my server on port 9005, which is default port for hsqldb server engine.
2. Now let’s create DB and insert some records with ANT script. I’ve not covered or questioned about DB design as I’m just trying going through sam’s application. I’ll cover all things in my Next application that will be designed and developed by only me.
Below is how my executeddl script looks like,
url="jdbc:hsqldb:hsql://localhost:${hport}/${halias}"
userid="sa" password=""
print="yes">
-- SQL script for TimeX
-- Step 1: Drop objects if they exist
DROP TABLE Department IF EXISTS;
DROP TABLE Employee IF EXISTS;
DROP TABLE Timesheet IF EXISTS;
DROP INDEX TimesheetIndex IF EXISTS;
DROP INDEX DepartmentCodeIndex IF EXISTS;
DROP INDEX EmployeeIdIndex IF EXISTS;
-- Step 2: Create tables
CREATE TABLE Department
(
departmentCode CHAR(2) NOT NULL,
name VARCHAR(255) NOT NULL
);
CREATE TABLE Employee
(
employeeId INT NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
employeeCode CHAR(1) NOT NULL,
password VARCHAR(10) NOT NULL,
managerEmployeeId INT NULL
);
CREATE TABLE Timesheet
(
timesheetId IDENTITY NOT NULL,
employeeId INT NOT NULL,
statusCode CHAR(1) NOT NULL,
periodEndingDate DATE NOT NULL,
departmentCode CHAR(4) NOT NULL,
minutesMon INT NULL,
minutesTue INT NULL,
minutesWed INT NULL,
minutesThu INT NULL,
minutesFri INT NULL,
minutesSat INT NULL,
minutesSun INT NULL
);
-- Step 3: Create indexes
CREATE UNIQUE INDEX TimesheetIndex ON Timesheet (employeeId, periodEndingDate);
CREATE UNIQUE INDEX DepartmentCodeIndex ON Department (departmentCode);
CREATE UNIQUE INDEX EmployeeIdIndex ON Employee (employeeId);
-- Step 4: Insert some reference and test data
INSERT INTO Department (departmentCode, name)
VALUES ('AC', 'Accounting');
INSERT INTO Department (departmentCode, name)
VALUES ('CS', 'Customer Support');
INSERT INTO Department (departmentCode, name)
VALUES ('HR', 'Human Resources');
INSERT INTO Department (departmentCode, name) VALUES ('IT', 'Information Technology');
INSERT INTO Employee (employeeId, name, employeeCode,
password, email, managerEmployeeId)
VALUES (1, 'Mike Dover', 'H', 'rapidjava', 'mdover@acme.com', 3);
INSERT INTO Employee (employeeId, name, employeeCode,
password, email, managerEmployeeId)
VALUES (2, 'Ajay Kumar', 'H', 'visualpatterns', 'akumar@acme.com', 3);
INSERT INTO Employee (employeeId, name, employeeCode,
password, email, managerEmployeeId)
VALUES (3, 'Teresa Walker', 'M', 'agilestuff', 'twalker@acme.com', 4);
INSERT INTO Employee (employeeId, name, employeeCode,
password, email)
VALUES (4, 'Tom Brady', 'E', 'superbowl', 'tbrady@acme.com');
INSERT INTO Timesheet(timesheetId, employeeId, statusCode, periodEndingDate,
departmentCode, minutesMon, minutesTue, minutesWed,
minutesThu, minutesFri, minutesSat, minutesSun)
VALUES (1, 2, 'P', '2006-08-19', 'IT', 480, 480, 360, 480, 480, 0, 0);
INSERT INTO Timesheet(timesheetId, employeeId, statusCode, periodEndingDate,
departmentCode, minutesMon, minutesTue, minutesWed,
minutesThu, minutesFri, minutesSat, minutesSun)
VALUES (2, 1, 'A', '2006-08-19', 'HR', 0, 0, 480, 480, 480, 0, 0);
-- Step 5: Verify tables and test data look ok
SELECT * FROM Department;
SELECT * FROM Employee;
SELECT * FROM Timesheet;
Damn!!! Can you believe it all these steps were straight forward! I got hurdle going thought them. And ya my database is created successfully. How do I know? Good question and the answer are verifying it by opening database UI.
Ya but when I tried writing the above Ant script with some changes in DB name for my own student storage program. I went red.
Server started successfully with above target.
But
password=""
url="jdbc:hsqldb:hsql://localhost:${hport}/${halias}}"
userid="sa"
print="yes">
DROP TABLE student IF EXISTS;
CREATE TABLE student
(
rollNo INT NOT NULL,
name VARCHAR(255) NOT NULL
);
This was really a headache for me.
I was getting error below.
BUILD FAILED
C:\Users\ABhi\workspace\Practice\Agile\Hibernate\build.xml:26: java.sql.SQLException: Database does not exists in statement [hibernatedbalias}]
After making changes in above script my script looked like something below and now it was working. I don’t see any changes between both of these but just for the record I’m writing it below.
DROP TABLE student IF EXISTS;
CREATE TABLE student
(
rollNo INT NOT NULL,
name VARCHAR(255) NOT NULL
);
3. Open UI Navigator for our DB.
if you are some geek like our network admin(who actually loves to type everything manually rather than using ready made UI for the same task), I’ve other script for that.
If you are facing any problem till this point please mail me at
abhishek.gondalia@gmail.com
So that I can reproduce the same problem(Just for sake of solving it.)
