SQL Mapping with Java

SQL Mapping is an easier technique in which a normal user imagined columns(normally we say as metadata) are stored in the form of different tables. Each and every table information can be joined with other tables by means of special relationships like a Foreign key in a database. In each and every table, we will have Primary Key or Unique key to uniquely identify a column value in a table. There are subtle differences between Unique and Primary keys. 

• Unique key will uniquely identify a record but it can allow null values. 
• Primary key will uniquely identify a record but it will not allow null values.
• Additionally, we have Foreign keys to combine the details between 2 or more tables (Care must be taken during the time of SQL mapping)

For example, for an employee, personal information is stored on a table. official information is stored in another table. Personal details table: ’employeeId’ is the Primary Key for table A (Parent table). In table B (where official details are stored), table A Primary Key is maintained as a Foreign key. Child table). Only when a parent exists, the child will also exist. In the same concept, when an employee ‘E001’ is present in table A, the same employee ‘E0001’ can be present in table B. Otherwise it cannot exist. Similarly while deleting, we need to delete all the records of the child first and then only the parent one. For keeping all these kinds of relationships, several keys exist in databases like SQL Server. Now coming to the SQL mapping part, nowadays object-oriented programming languages like Java are all providing fantastic support for SQL Mapping

Java Datatype	Equivalent SQL Server Datatype
String	varchar, nvarchar
int, short, byte	Bit, tinyInt,SmallInt,Integer,BigInt etc.,
float, double	Real, Float, Decimal

In Java, if we are creating a class, then it is a plain old java object (POJO) class and it will have different attributes depending upon requirements. Let us see with an example for the ’employee’ POJO class in java

Java

public class Employee {

    // different attributes
    // for Employee class

    // This is required to
    // identify the employee uniquely
    private int id;

    // firstName of an employee
    private String firstName;

    // lastName of an employee
    private String lastName;

    // salary of an employee
    private int salary;

      // qualification of an employee
      private String qualification;

    //.....

}

We can map these employee attributes in SQL Server tables easily. Let us see how to do that

CREATE TABLE [dbo].[Employee] (
    [id]         INT          NOT NULL, -- Primary Key and hence not null
    [firstName] VARCHAR (20) NULL,
    [lastName]    VARCHAR (20) NULL,
    [salary]  INT          NULL,
    [qualification]  VARCHAR (50)  NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([id] ASC)
);

Whenever we need to save the data or need deal with the database. In an object-oriented programming language like Java, we need to create a POJO class and all the attribute names and their data type should match with the corresponding table in a database like SQL Server. 

Way to map the tables and relationships present in SQL to the objects and their relationships in Java

1. Property and
2. Relationship mapping

1. Property Mapping

In property mapping, the mapping involves the physical attributes and virtual attributes of the objects to one or more columns. Sometimes it is not mandatory and also not necessary to map all the attributes of objects to their respective columns in tables. The reason is, that we are using data(String datatype in java/varchar in SQL Server) abase for persistence. Few columns are mandatory and they are helpful to identify the records uniquely. All Primary key columns are mandatory. Hence in Java also it should be present and it is mapped to the Primary key of a table. In the above example, ID is the Primary Key column. Additionally, we may need to have the following in the employee class

status (String datatype in java/varchar in SQL Server)
emailId (String datatype in java/varchar in SQL Server)
mobileNumber  (String datatype in java/varchar in SQL Server)
gender (String datatype in java/varchar in SQL Server)
emergencyContactNumber (String datatype in java/varchar in SQL Server)
emergencyContactPerson (String datatype in java/varchar in SQL Server)
etc.,

Consider another example inventory. There for each and every item, the price is stored and the quantity of an item is given by the user hence because of this amount calculation for an item is handled at the application level and finally stored in the table. During this time calculation formula is simple and from time to time it may vary depending upon the discounts that got applied. Hence it is not necessary to store the calculation formula in the database. So, practically check whichever is necessary and map those attributes to the corresponding columns in the database. This way of doing the mapping is called property mapping

2. Relationship Mapping

There are three types of relationships namely one to one/one to many or many to one/many to many relationships to be considered while mapping the objects to SQL. In terms of SQL Server,

• one-to-one is straightforward: A student can have only one rank.
• one-to-many relationship: A single student can take multiple programming courses like Java, Python, etc., A student can participate in multiple sports like Cricket, Volleyball, etc.,
• many-to-many relationship: A single student can be assigned to multiple projects, and similarly a single project can be assigned to multiple students.

In objects, the relationships are classified depending on the directionality. It can be either unidirectional/bidirectional. In unidirectional, one object is not aware of the other one but the other one is aware of the first one. i.e. student object is aware of the rank object but the rank object is not aware of the student object. Bidirectional is just the opposite. Both the objects know about each other. The employee object is aware of the department head object and the department head object is aware of the employee.

UML Way of Representation

• Unidirectional is denoted by the single arrow
• Bidirectional is denoted by a single line

These are followed in UML diagrams.

• For one-to-one associativity 1…1 is denoted
• For one-to-many, 1..* is denoted
• For many-to-many, *..* is denoted

Care must be taken when there are foreign keys are present in the relationship. If they are available, it has to be handled differently. Otherwise, it is quite easier only. When there are foreign keys, referential integrity constraints will come into the picture.