If you are using Java EE, i.e. EJB3/JPA for persistence, don't design a database. Design a domain model.
In other words think about the objects you need in Java, add the annotations for relationships and let EJB3/JPA work out the database design in terms of tables, columns and foreign keys. You can always tweak it later for performance.
So if you had the classic employee department relationship (this is untested and parts are missing, e.g. setter methods, equals, hashCode):
Employee.java
Code:
@Entity
public class Employee {
private long id;
private String name;
...
private Department department;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public long getId() {
return id;
}
public long getName() {
return Name;
}
@ManyToOne(optional=false)
public Department getDepartment() {
return department;
}
...
Department.java
Code:
@Entity
public class Department {
private long id;
private String name;
private Set<Employee> employees = new HashSet<>();
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public long getId() {
return id;
}
public long getName() {
return name;
}
@OneToMany(mappedBy="department")
public Set<Employee> getEmployees() {
return employees;
}
...
The beauty of designing the domain model is that you work with the Java objects (including inheritance) and JPA does the right thing (well, most of the time anyway). It's more fluid than creating a database and trying to map your domain model to it.
You'd configure the JPA persistence unit so that it auto-generates the database (this example is for the Apache Derby instance that comes embedded in Glassfish and it's OK to play around with):
persistence.xml
Code:
<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
<persistence-unit name="MyApplication" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/__default</jta-data-source>
<class>com.mycompany.Employee</class>
<class>com.mycompany.Department</class>
<properties>
<property name="eclipselink.logging.level" value="CONFIG"/>
<property name="eclipselink.ddl-generation" value="create-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="database"/>
<property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="database"/>
</properties>
</persistence-unit>
</persistence>
DAOs are a well-established pattern for Java and databases but with JPA they almost become redundant. You can have a stateless session EJB that uses a JPA entity manager which behaves like a generic DAO. Something like:
EmployeeServiceBean.java
Code:
@Stateless
public class EmployeeServiceBean implements EmployeeService {
@PersistenceContext(unitName="Sales")
EntityManager em;
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public Employee createEmployee(String name, Department department) {
Employee employee = new Employee();
employee.name = name;
employee.department = department
em.persist(employee);
return employee;
}
...
Then you can inject your service bean into web service and web applications, e.g. this is a servlet that takes a name and department id submitted from an HTML form and creates an employee. You'd handle data types and validation better than this and you'd probably use JSF rather than servlets:
Code:
public class MyServlet extends HttpServlet {
@EJB EmployeeService employeeService;
public String doPost(HttpServletRequest req, HttServletResponse res) {
String name = request.getParameter("name");
String dept = request.getParameter("departmentId");
Department department = employeeService.findDepartment(dept);
employeeService.createEmployee(name, department);
...
// Create response here ...
}
...
Part of the reason Java EE is so powerful (and such an monster to start with) is what happens with that createEmployee method. It manages the creation of a new employee, auto-generates the primary key and establishes the link to the department. All in the scope of a transaction such that if any part of it fails, it all fails.
If you wanted to get fancy, you could have employee as a subclass of Person so that Person works for Customer, etc. The Java EE tutorial has a good section on mapping inheritance.
And yes, I know it's a lot to learn but that's a large part of the Java EE stack in one post. 
Adv Reply
Re: DB design question
Bookmarks