CODEWITHSUNDEEP
javamysqlsqlweb-applicationsdao
Filipp
Filipp

Reputation: 133

Represent sql tables with java classes

i've a problem understanding how to represent a sql table into my java web app and ho to handle the various types of relationship.

Let's suppose to have a department table:

CREATE TABLE IF NOT EXISTS `department` (
  `name` varchar(15) NOT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`number`),
) ENGINE=InnoDB;

A department has a lot of employees and one employee can work for only one department, so there is a 1:N relationship between department and employees. This could be the employees table:

CREATE TABLE IF NOT EXISTS `employee` (
  `first_name` varchar(15) NOT NULL,
  `last_name` varchar(15) NOT NULL,
  `SSN` char(9) NOT NULL,
  `address` varchar(30) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `N_D` int(11) NOT NULL,
  PRIMARY KEY (`SSN`),
  FOREIGN KEY `N_D` REFERENCES `department`(`number`),
) ENGINE=InnoDB;

No problem so far but let's go to java now. My application will have a DAO to read the selected elements from the database, maybe two class called DepartmentDAO and EmployeeDAO. My question is how i represent correctly the department entity and employee entity? How should i handle the relationship? I've seen someone using the array for 1 to many relation and a single object for the opposite case:

public class Department{
    private String name;
    private Long number;

    /*1:N*/
    private Employee[] employees;

    /*getter and setter*/
}

The employee class:

public class Employee{
    private String firstName;
    private String lastName;
    private String SSN;
    private String address;
    private String gender;
    private int salary;

    /*N:1*/
    private Department department;

    /*getter and setter*/
}

It seems okay but how do i read nested objects? In the employee case i could use the join in my query for reading two object but in the other case i've got an array.

I want, for example, visualize the number of employees for a certain department. When i read the department object i should also read N employee objects and the by employees.length i could get the number. Isn't it a bit too expensive? It would be wrong put another attribute in my department class (private int numberOfEmployees;) and read it by using COUNT in sql?

Thanks in advance for the help.

Upvotes: 0

Views: 1430

Answers (1)

1stNox
1stNox

Reputation: 29

If I got it right, you want to count the employees foreach department, or not?
If so, I recommend using the dao pattern. In additon to the dao pattern, you can use a Key : Value List or Map like the Hashmap.
The Map should contain as Key the specific department and as value the employee.
Besides, you have to synchronize the database and the Java Objects to add pairs to the map.
Summarized, you need to create a Data Management Class which contains a List or Map with a key (department) and a value (employee).
Besides, it must be able to do transactions with the database to synchronize the data.
Therefore, you can use the dao pattern and a HashMap, references below.
I do not know if this is good practicse, but it will work.

Dao Pattern
HashMap

EDIT: As I read your question again, the HashMap should be in your department class, but you can still use the dao pattern for synchronizing and data management.

Upvotes: 1

Related Questions