Last week I have written a blog post on **Orman ** – a new lightweight Java Object-Relational Mapping Framework I’m coding. [hacker news story] Now I will demonstrate how easy is to design and create a database using only Java classes. Let the proof-of-concept begin!

The scenario we will use is a very primitive airline company management system project I have from my Databases course this year, I will reuse it here even though it is very complicated for a simple app that Orman Framework is aimed for, but Orman can handle this.

Requirements: Basic knowledge of Java and databases, how to add Orman framework to a Java project.

Step 1. Create your environment

Create a new Java project, add orman.jar to your path, download SQLite binaries. Create a package to put your entity classes in them. Then let’s start to create entities.

Step 2. Create entities (boring part)

Recommended read: Making your old Java classes Orman Entities Now we need to design our entity classes. I have an ER (entity-relationship) diagram that introduces how our database is going to be like from my course project. Just give 1 or 2 minutes and inspect it to understand rest of the tutorial:

[caption id="" align=“aligncenter” width=“500” caption=“ER Diagram (click on it to resize)”][/caption]

Now we are beginning to design our Java classes one by one. Easy entities first. (I will omit imports and package names in the code).

City.java

@Entity
public class City extends Model{
        @PrimaryKey(autoIncrement=true)
        public long id;
        public String name;
}

Airport.java (has reference to city)

@Entity
public class Airport extends Model{
        @PrimaryKey(autoIncrement=true)
        public long id;
        public String name;
        public String code;
        public City city;
}

Route.java (has two references to airport )

@Entity
public class Route extends Model{
        @PrimaryKey(autoIncrement=true)
        public int id;
        public Airport departure;
        public Airport destination;
}

AirStaff.java

@Entity
public class AirStaff extends Model {
        @PrimaryKey(autoIncrement=true)
        public int id;
        public String name;
        public String role;
}

Flight.java (has references to a route and many air staff)

@Entity
public class Flight extends Model{
        @PrimaryKey(autoIncrement=true)
        public long id;
        public Date departure;
        public float fare = 0;
        public Route route;

        @ManyToMany(toType = AirStaff.class)
        public EntityList crew = new EntityList(Flight.class, AirStaff.class, this);
}

Luggage.java (here we practiced private fields, has a reference to Booking ticket)

@Entity
public class Luggage extends Model{
        @PrimaryKey(autoIncrement=true)
        private int luggageId;

        private float weight = 0;

        @ManyToOne
        private Booking toTicket;

        public void setLuggageId(int luggageId) {
                this.luggageId = luggageId;
        }

        public int getLuggageId() {
                return luggageId;
        }

        public void setWeight(float weight) {
                this.weight = weight;
        }

        public float getWeight() {
                return weight;
        }

        public void setToTicket(Booking toTicket) {
                this.toTicket = toTicket;
        }

        public Booking getToTicket() {
                return toTicket;
        }
}

Booking.java (has references to Flight, Customer, Payment and has a field to query all luggage records belongs to this ticket)

@Entity
public class Booking extends Model{
        @PrimaryKey(autoIncrement=true)
        public int id;

        @Index
        public Flight flight;

        @OneToOne
        public Payment payment;

        @ManyToOne
        public Customer owner;

        @OneToMany(onField = "toTicket", toType = Luggage.class)
        public EntityList luggage = new EntityList(Booking.class, Luggage.class, this);
}

Customer.java (has a reference to City)

@Entity
public class Customer {
        @PrimaryKey(autoIncrement=true)
        public int id;

        public String name;
        public String phone;
        public int age;
        public City city;
}

Payment.java (you can use date fields in Orman! this is referenced from Booking)

@Entity
public class Payment {
        @PrimaryKey(autoIncrement=true)
        public long id;

        @Index
        public float amount;

        public Date transactionDate;
}

GroundStaff.java (has a reference to city)

@Entity
public class GroundStaff extends Model{
        @PrimaryKey
        public String ssn;

        public Airport office;
}

Step 3. Write your driver class

We will write a main method to just to create our database in a SQLite file and we will set logger to see detailed information about what’s going on inside the framework and under the hoods!

Main.java

public class Main {
        public static void main(String[] args) {
                Database db = new SQLite("airline.db");

                Log.setLogger(new StandardLogger());
                Log.setLevel(LoggingLevel.TRACE);

                MappingSession.registerDatabase(db);
                MappingSession.start();
        }
}

Pretty straightforward. First we configured and initialized our database instance. Then we set our standard logger that puts results on standard output stream.

Step 4. Watch the magic

Compile and run. Here’s the output.

13  [INFO] Auto package registration enabled
59  [INFO] Registering entity Flight.
59  [INFO] Registering entity GroundStaff.
60  [INFO] Registering entity City.
64  [INFO] Registering entity Luggage.
64  [INFO] Registering entity AirStaff.
67  [INFO] Registering entity Payment.
67  [INFO] Registering entity Airport.
68  [INFO] Registering entity Customer.
68  [INFO] Registering entity Route.
74  [INFO] Registering entity Booking.
74  [TRACE] Synthetic entity (Flight, AirStaff) created, registering.
75  [INFO] Registering synthetic entity FlightAirStaff.
75  [INFO] Mapping session starting...
75  [INFO] Preparing to make physical bindings for entities.
76  [DEBUG] Field 'id'(long) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
77  [TRACE] Field 'id' index name binded as 'FlightIndex'
77  [DEBUG] Field 'departure'(java.util.Date) mapped to 'departure'(TEXT) using type of @PrimaryKey field.
78  [DEBUG] Field 'fare'(float) mapped to 'fare'(REAL) using type of @PrimaryKey field.
78  [TRACE] Direct entity mapping inferred on field Flight.route.
78  [DEBUG] Field 'route'(Route) mapped to 'route'(INTEGER) using type of @PrimaryKey field.
79  [TRACE] Direct entity mapping inferred on field Flight.crew.
79  [DEBUG] Field 'crew'(AirStaff) mapped to 'crew'(INTEGER) using type of @PrimaryKey field.
79  [TRACE] No conflicting field names found on entity Flight.
80  [DEBUG] Field 'ssn'(java.lang.String) mapped to 'ssn'(TEXT) using type of @PrimaryKey field.
80  [TRACE] Field 'ssn' index name binded as 'Ground_staffIndex'
83  [TRACE] Direct entity mapping inferred on field GroundStaff.office.
83  [DEBUG] Field 'office'(Airport) mapped to 'office'(INTEGER) using type of @PrimaryKey field.
83  [TRACE] No conflicting field names found on entity GroundStaff.
84  [DEBUG] Field 'id'(long) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
84  [TRACE] Field 'id' index name binded as 'CityIndex'
84  [DEBUG] Field 'name'(java.lang.String) mapped to 'name'(TEXT) using type of @PrimaryKey field.
84  [TRACE] No conflicting field names found on entity City.
85  [DEBUG] Field 'luggageId'(int) mapped to 'luggage_id'(INTEGER) using type of @PrimaryKey field.
85  [TRACE] Field 'luggageId' index name binded as 'LuggageIndex'
85  [DEBUG] Field 'weight'(float) mapped to 'weight'(REAL) using type of @PrimaryKey field.
86  [TRACE] Direct entity mapping inferred on field Luggage.toTicket.
86  [DEBUG] Field 'toTicket'(Booking) mapped to 'to_ticket'(INTEGER) using type of @PrimaryKey field.
86  [TRACE] Field 'toTicket' index name binded as 'LuggageTo_ticketIndex'
86  [TRACE] No conflicting field names found on entity Luggage.
87  [DEBUG] Field 'id'(int) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
87  [TRACE] Field 'id' index name binded as 'Air_staffIndex'
87  [DEBUG] Field 'name'(java.lang.String) mapped to 'name'(TEXT) using type of @PrimaryKey field.
88  [DEBUG] Field 'role'(java.lang.String) mapped to 'role'(TEXT) using type of @PrimaryKey field.
88  [TRACE] No conflicting field names found on entity AirStaff.
88  [DEBUG] Field 'id'(long) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
89  [TRACE] Field 'id' index name binded as 'PaymentIndex'
89  [DEBUG] Field 'amount'(float) mapped to 'amount'(REAL) using type of @PrimaryKey field.
89  [TRACE] Field 'amount' index name binded as 'PaymentAmountIndex'
90  [DEBUG] Field 'transactionDate'(java.util.Date) mapped to 'transaction_date'(TEXT) using type of @PrimaryKey field.
90  [TRACE] No conflicting field names found on entity Payment.
90  [DEBUG] Field 'id'(long) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
90  [TRACE] Field 'id' index name binded as 'AirportIndex'
91  [DEBUG] Field 'name'(java.lang.String) mapped to 'name'(TEXT) using type of @PrimaryKey field.
91  [DEBUG] Field 'code'(java.lang.String) mapped to 'code'(TEXT) using type of @PrimaryKey field.
92  [TRACE] Direct entity mapping inferred on field Airport.city.
92  [DEBUG] Field 'city'(City) mapped to 'city'(INTEGER) using type of @PrimaryKey field.
92  [TRACE] No conflicting field names found on entity Airport.
92  [DEBUG] Field 'id'(int) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
93  [TRACE] Field 'id' index name binded as 'CustomerIndex'
93  [DEBUG] Field 'name'(java.lang.String) mapped to 'name'(TEXT) using type of @PrimaryKey field.
93  [DEBUG] Field 'phone'(java.lang.String) mapped to 'phone'(TEXT) using type of @PrimaryKey field.
94  [DEBUG] Field 'age'(int) mapped to 'age'(INTEGER) using type of @PrimaryKey field.
94  [TRACE] Direct entity mapping inferred on field Customer.city.
94  [DEBUG] Field 'city'(City) mapped to 'city'(INTEGER) using type of @PrimaryKey field.
94  [TRACE] No conflicting field names found on entity Customer.
95  [DEBUG] Field 'id'(int) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
95  [TRACE] Field 'id' index name binded as 'RouteIndex'
95  [TRACE] Direct entity mapping inferred on field Route.departure.
96  [DEBUG] Field 'departure'(Airport) mapped to 'departure'(INTEGER) using type of @PrimaryKey field.
96  [TRACE] Direct entity mapping inferred on field Route.destination.
96  [DEBUG] Field 'destination'(Airport) mapped to 'destination'(INTEGER) using type of @PrimaryKey field.
96  [TRACE] No conflicting field names found on entity Route.
97  [DEBUG] Field 'id'(int) mapped to 'id'(INTEGER) using type of @PrimaryKey field.
97  [TRACE] Field 'id' index name binded as 'BookingIndex'
97  [TRACE] Direct entity mapping inferred on field Booking.flight.
97  [DEBUG] Field 'flight'(Flight) mapped to 'flight'(INTEGER) using type of @PrimaryKey field.
98  [TRACE] Field 'flight' index name binded as 'BookingFlightIndex'
98  [TRACE] OneToOne mapping detected on field Booking.payment.
98  [DEBUG] Field 'payment'(Payment) mapped to 'payment'(INTEGER) using type of @PrimaryKey field.
99  [TRACE] Field 'payment' index name binded as 'BookingPaymentIndex'
99  [TRACE] Direct entity mapping inferred on field Booking.owner.
99  [DEBUG] Field 'owner'(Customer) mapped to 'owner'(INTEGER) using type of @PrimaryKey field.
100  [TRACE] Field 'owner' index name binded as 'BookingOwnerIndex'
100  [TRACE] Direct entity mapping inferred on field Booking.luggage.
101  [DEBUG] Field 'luggage'(Luggage) mapped to 'luggage'(INTEGER) using type of @PrimaryKey field.
101  [TRACE] No conflicting field names found on entity Booking.
101  [TRACE] Direct entity mapping inferred on field FlightAirStaff.FlightId.
102  [DEBUG] Field 'FlightId'(Flight) mapped to 'flight_id'(INTEGER) using type of @PrimaryKey field.
102  [TRACE] Direct entity mapping inferred on field FlightAirStaff.AirStaffId.
103  [DEBUG] Field 'AirStaffId'(AirStaff) mapped to 'air_staff_id'(INTEGER) using type of @PrimaryKey field.
103  [TRACE] No conflicting field names found on entity FlightAirStaff.
103  [INFO] Custom SQL grammar found: org.orman.dbms.sqlite.generic.SQLiteGrammar
103  [INFO] Schema creation policy is CREATE_IF_NOT_EXISTS.
106  [DEBUG] Arranged serial schedule for entity construction:
107  [DEBUG] Serial schedule: City, AirStaff, Payment, Airport, GroundStaff, Customer, Route, Flight, FightAirStaff, Booking, Luggage
118  [INFO] Executing DDL construction queries.
119  [TRACE] Executing: CREATE TABLE IF NOT EXISTS city (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)
170  [TRACE] Executing: CREATE TABLE IF NOT EXISTS air_staff (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, role TEXT)
172  [TRACE] Executing: CREATE TABLE IF NOT EXISTS payment (id INTEGER PRIMARY KEY AUTOINCREMENT, amount REAL NOT NULL, transaction_date TEXT)
174  [TRACE] Executing: CREATE INDEX IF NOT EXISTS payment_amount_index ON payment (amount)
176  [TRACE] Executing: CREATE TABLE IF NOT EXISTS airport (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, code TEXT, city INTEGER)
177  [TRACE] Executing: CREATE TABLE IF NOT EXISTS ground_staff (ssn TEXT, office INTEGER)
178  [TRACE] Executing: CREATE UNIQUE INDEX IF NOT EXISTS ground_staff_index ON ground_staff (ssn)
180  [TRACE] Executing: CREATE TABLE IF NOT EXISTS customer (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, age INTEGER, city INTEGER)
181  [TRACE] Executing: CREATE TABLE IF NOT EXISTS route (id INTEGER PRIMARY KEY AUTOINCREMENT, departure INTEGER, destination INTEGER)
182  [TRACE] Executing: CREATE TABLE IF NOT EXISTS flight (id INTEGER PRIMARY KEY AUTOINCREMENT, departure TEXT, fare REAL, route INTEGER)
183  [TRACE] Executing: CREATE TABLE IF NOT EXISTS flight_air_staff (flight_id INTEGER NOT NULL, air_staff_id INTEGER NOT NULL, FOREIGN KEY (flight_id) REFERENCES flight (id), FOREIGN KEY (air_staff_id) REFERENCES air_staff (id))
184  [TRACE] Executing: CREATE TABLE IF NOT EXISTS booking (id INTEGER PRIMARY KEY AUTOINCREMENT, flight INTEGER NOT NULL, payment INTEGER, owner INTEGER, FOREIGN KEY (payment) REFERENCES payment (id), FOREIGN KEY (owner) REFERENCES customer (id))
185  [TRACE] Executing: CREATE INDEX IF NOT EXISTS booking_flight_index ON booking (flight)
186  [TRACE] Executing: CREATE UNIQUE INDEX IF NOT EXISTS booking_payment_index ON booking (payment)
188  [TRACE] Executing: CREATE INDEX IF NOT EXISTS booking_owner_index ON booking (owner)
189  [TRACE] Executing: CREATE TABLE IF NOT EXISTS luggage (luggage_id INTEGER PRIMARY KEY AUTOINCREMENT, weight REAL, to_ticket INTEGER, FOREIGN KEY (to_ticket) REFERENCES booking (id))
191  [TRACE] Executing: CREATE INDEX IF NOT EXISTS luggage_to_ticket_index ON luggage (to_ticket)
193  [INFO] DDL constructed successfully.

Until 107 ms, you see physical name and column type mappings done by the framework. At 107, framework comes up with a serial schedule that is safe to create tables in this order without breaking foreign key dependencies. After that, all you see is DDL queries to generate tables, indexes, foreign keys and all this process takes around 193 miliseconds on a Linux machine.

You can even see that some intermediate tables (FlightAirStaff) are created to handle many-to-many relationship. Orman has just created a complicated database schema for you! Give applauses to the magician. It feels really good after writing >10,000 lines of code and seeing it really works.

Thanks for reading, comments and feedbacks are appreciated.