Java/JPA/ResultSet Mappings
Содержание
Cast Result List To Generic Collection
<source lang="java">
File: Department.java
import java.util.ArrayList; import java.util.Collection; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.OneToMany; @Entity public class Department {
@Id @GeneratedValue(strategy=GenerationType.IDENTITY) private int id; private String name; @OneToMany(targetEntity=Professor.class, mappedBy="department") private Collection employees; public Department() { employees = new ArrayList<Professor>(); } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public void addProfessor(Professor employee) { if (!getProfessors().contains(employee)) { getProfessors().add(employee); if (employee.getDepartment() != null) { employee.getDepartment().getProfessors().remove(employee); } employee.setDepartment(this); } } public Collection getProfessors() { return employees; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java
import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class Professor {
@Id @GeneratedValue(strategy=GenerationType.IDENTITY) private int id; private String name; private long salary; @ManyToOne private Department department; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with " + getDepartment(); }
}
File: ProfessorService.java import java.util.Collection; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public Department createDepartment(String name) { Department dept = new Department(); dept.setName(name); em.persist(dept); return dept; } public Collection<Department> findAllDepartments() { Query query = em.createQuery("SELECT d FROM Department d"); return (Collection<Department>) query.getResultList(); } public Professor createProfessor(String name, long salary) { Professor emp = new Professor(); emp.setName(name); emp.setSalary(salary); em.persist(emp); return emp; } public Professor setProfessorDepartment(int empId, int deptId) { Professor emp = em.find(Professor.class, empId); Department dept = em.find(Department.class, deptId); dept.addProfessor(emp); return emp; } public Collection<Professor> findAllProfessors() { Query query = em.createQuery("SELECT e FROM Professor e"); return (Collection<Professor>) query.getResultList(); }
}
File: Main.java import java.util.Collection; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin(); Professor emp = service.createProfessor("empName",100); Department dept = service.createDepartment("deptName"); emp = service.setProfessorDepartment(emp.getId(),dept.getId()); System.out.println(emp.getDepartment() + " with Professors:"); System.out.println(emp.getDepartment().getProfessors()); Collection<Professor> emps = service.findAllProfessors(); if (emps.isEmpty()) { System.out.println("No Professors found "); } else { System.out.println("Found Professors:"); for (Professor emp1 : emps) { System.out.println(emp1); } } Collection<Department> depts = service.findAllDepartments(); if (depts.isEmpty()) { System.out.println("No Departments found "); } else { System.out.println("Found Departments:"); for (Department dept1 : depts) { System.out.println(dept1 + " with " + dept1.getProfessors().size() + " employees"); } }
util.checkData("select * from Professor"); util.checkData("select * from Department"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
Inheritance Result Mapping
<source lang="java">
File: ContractProfessor.java import javax.persistence.Column; import javax.persistence.Entity; @Entity public class ContractProfessor extends Professor {
@Column(name = "DAILY_RATE") private int dailyRate; private int term; public int getDailyRate() { return dailyRate; } public void setDailyRate(int dailyRate) { this.dailyRate = dailyRate; } public int getTerm() { return term; } public void setTerm(int term) { this.term = term; } public String toString() { return "ContractProfessor id: " + getId() + " name: " + getName(); }
}
File: Professor.java
import java.util.Date; import javax.persistence.Column; import javax.persistence.DiscriminatorColumn; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.FieldResult; import javax.persistence.Id; import javax.persistence.Inheritance; import javax.persistence.SqlResultSetMapping; import javax.persistence.Table; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity @Table(name="EMPLOYEE") @Inheritance @DiscriminatorColumn(name="EMP_TYPE") @SqlResultSetMapping(
name="ProfessorStageMapping", entities= @EntityResult( entityClass=Professor.class, discriminatorColumn="TYPE", fields={ @FieldResult(name="startDate", column="START_DATE"), @FieldResult(name="dailyRate", column="DAILY_RATE"), @FieldResult(name="hourlyRate", column="HOURLY_RATE") } )
) public abstract class Professor {
@Id private int id; private String name; @Temporal(TemporalType.DATE) @Column(name="START_DATE") private Date startDate; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Date getStartDate() { return startDate; } public void setStartDate(Date startDate) { this.startDate = startDate; } public String toString() { return "Professor id: " + getId() + " name: " + getName(); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findAllProfessors() { Query query = em.createNativeQuery("SELECT id, name, start_date, daily_rate, term " + " FROM employee", "ProfessorStageMapping"); return query.getResultList(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findAllProfessors(); util.checkData("select * from Professor"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: persistence.xml
<persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
Inheritance Result Mapping Two Subclasses
<source lang="java">
File: FullTimeProfessor.java
import javax.persistence.DiscriminatorValue; import javax.persistence.Entity; @Entity @DiscriminatorValue("FTEmp") public class FullTimeProfessor extends Professor {
private long salary; private long pension; public long getPension() { return pension; } public void setPension(long pension) { this.pension = pension; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public String toString() { return "FullTimeProfessor id: " + getId() + " name: " + getName(); }
}
File: PartTimeProfessor.java
import javax.persistence.Column; import javax.persistence.DiscriminatorValue; import javax.persistence.Entity; @Entity(name="PTEmp") @DiscriminatorValue("PTEmp") public class PartTimeProfessor extends Professor {
@Column(name="HOURLY_RATE") private float hourlyRate; public float getHourlyRate() { return hourlyRate; } public void setHourlyRate(float hourlyRate) { this.hourlyRate = hourlyRate; } public String toString() { return "PartTimeProfessor id: " + getId() + " name: " + getName(); }
}
File: Professor.java
import java.util.Date; import javax.persistence.Column; import javax.persistence.DiscriminatorColumn; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.FieldResult; import javax.persistence.Id; import javax.persistence.Inheritance; import javax.persistence.MappedSuperclass; import javax.persistence.SqlResultSetMapping; import javax.persistence.Table; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity @Table(name="EMPLOYEE") @Inheritance @DiscriminatorColumn(name="EMP_TYPE") @MappedSuperclass @SqlResultSetMapping(
name="ProfessorStageMapping", entities= @EntityResult( entityClass=Professor.class, discriminatorColumn="TYPE", fields={ @FieldResult(name="startDate", column="START_DATE"), @FieldResult(name="dailyRate", column="DAILY_RATE"), @FieldResult(name="hourlyRate", column="HOURLY_RATE") } )
) public abstract class Professor {
@Id private int id; private String name; @Temporal(TemporalType.DATE) @Column(name="START_DATE") private Date startDate; private int vacation;
public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Date getStartDate() { return startDate; } public void setStartDate(Date startDate) { this.startDate = startDate; } public int getVacation() { return vacation; } public void setVacation(int vacation) { this.vacation = vacation; } public String toString() { return "Professor id: " + getId() + " name: " + getName(); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findAllProfessors() { Query query = em.createNativeQuery("SELECT id, name, start_date, vacation " + " FROM employee", "ProfessorStageMapping"); return query.getResultList(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findAllProfessors(); util.checkData("select * from Professor"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
Sql Resultset Mapping: Column Result
<source lang="java">
File: Address.java import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}
File: Department.java import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity public class Department {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.ColumnResult; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.SqlResultSetMapping; import javax.persistence.SqlResultSetMappings; import javax.persistence.Table; @Entity @Table(name = "EMP") @SqlResultSetMappings( { @SqlResultSetMapping(name = "ProfessorAndManager", columns = { @ColumnResult(name = "EMP_NAME"),
@ColumnResult(name = "MANAGER_NAME") })
}) public class Professor {
@Id @Column(name = "EMP_ID") private int id; private String name; private long salary; @OneToOne private Address address; @ManyToOne @JoinColumn(name = "DEPT_ID") private Department department; @ManyToOne @JoinColumn(name = "MANAGER_ID") private Professor manager; @OneToMany(mappedBy = "manager") private Collection<Professor> directs = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with MgrId: " + (getManager() == null ? null : getManager().getId()); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findProfessorWithManager() { Query query = em.createNativeQuery("SELECT e.name AS emp_name, m.name AS manager_name " + "FROM emp e, emp m " + "WHERE e.manager_id = m.emp_id", "ProfessorAndManager"); return query.getResultList(); }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findProfessorWithManager(); util.checkData("select * from EMP"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
SQL Resultset Mapping: One Entity
<source lang="java">
File: Address.java import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}
File: Department.java import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity public class Department {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.ColumnResult; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.FieldResult; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.SqlResultSetMapping; import javax.persistence.SqlResultSetMappings; import javax.persistence.Table; @Entity @Table(name = "EMP") @SqlResultSetMappings( {
@SqlResultSetMapping(name = "employeeResult", entities = @EntityResult(entityClass = Professor.class)) })
public class Professor {
@Id @Column(name = "EMP_ID") private int id; private String name; private long salary; @OneToOne private Address address; @ManyToOne @JoinColumn(name = "DEPT_ID") private Department department; @ManyToOne @JoinColumn(name = "MANAGER_ID") private Professor manager; @OneToMany(mappedBy = "manager") private Collection<Professor> directs = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with MgrId: " + (getManager() == null ? null : getManager().getId()); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findAllProfessors() { Query query = em.createNativeQuery("SELECT emp_id, name, salary, manager_id, " + "dept_id, address_id " + "FROM EMP ", "employeeResult"); return query.getResultList(); }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findAllProfessors(); util.checkData("select * from EMP"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
SQL Resultset Mapping: Two Entities
<source lang="java">
File: Address.java import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}
File: Department.java import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity public class Department {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.SqlResultSetMapping; import javax.persistence.SqlResultSetMappings; import javax.persistence.Table; @Entity @Table(name = "EMP") @SqlResultSetMappings( { @SqlResultSetMapping(name = "ProfessorWithAddress", entities = {
@EntityResult(entityClass = Professor.class), @EntityResult(entityClass = Address.class) })
}) public class Professor {
@Id @Column(name = "EMP_ID") private int id; private String name; private long salary; @OneToOne private Address address; @ManyToOne @JoinColumn(name = "DEPT_ID") private Department department; @ManyToOne @JoinColumn(name = "MANAGER_ID") private Professor manager; @OneToMany(mappedBy = "manager") private Collection<Professor> directs = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with MgrId: " + (getManager() == null ? null : getManager().getId()); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findProfessorWithAddress() { Query query = em.createNativeQuery( "SELECT emp_id, name, salary, manager_id, dept_id, address_id, " + "id, street, city, state, zip " + "FROM emp, address " + "WHERE address_id = id", "ProfessorWithAddress"); return query.getResultList();
} }
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findProfessorWithAddress(); util.checkData("select * from EMP"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
Sql Resultset Mapping With Alias
<source lang="java">
File: Address.java import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}
File: Department.java import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity public class Department {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.FieldResult; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.SqlResultSetMapping; import javax.persistence.SqlResultSetMappings; import javax.persistence.Table; @Entity @Table(name = "EMP") @SqlResultSetMappings( {
@SqlResultSetMapping( name="ProfessorWithAddressColumnAlias", entities={@EntityResult(entityClass=Professor.class, fields=@FieldResult(name="id", column="EMP_ID")), @EntityResult(entityClass=Address.class)} )
}) public class Professor {
@Id @Column(name = "EMP_ID") private int id; private String name; private long salary; @OneToOne private Address address; @ManyToOne @JoinColumn(name = "DEPT_ID") private Department department; @ManyToOne @JoinColumn(name = "MANAGER_ID") private Professor manager; @OneToMany(mappedBy = "manager") private Collection<Professor> directs = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with MgrId: " + (getManager() == null ? null : getManager().getId()); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findWithAlias() { Query query = em.createNativeQuery( "SELECT emp.emp_id AS emp_id, name, salary, manager_id, dept_id, address_id, " + "address.id, street, city, state, zip " + "FROM emp, address " + "WHERE address_id = id", "ProfessorWithAddressColumnAlias"); return query.getResultList(); }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findWithAlias(); util.checkData("select * from EMP"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: persistence.xml <persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>
Using Entity Result
<source lang="java">
File: Department.java import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity public class Department {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public String toString() { return "Department id: " + getId() + ", name: " + getName(); }
}
File: Professor.java import java.util.ArrayList; import java.util.Collection; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.EntityResult; import javax.persistence.FieldResult; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.SqlResultSetMapping; import javax.persistence.SqlResultSetMappings; import javax.persistence.Table; @Entity @Table(name = "EMP") @SqlResultSetMappings( {
@SqlResultSetMapping( name="ProfessorWithAddressColumnAlias", entities={@EntityResult(entityClass=Professor.class, fields=@FieldResult(name="id", column="EMP_ID")), @EntityResult(entityClass=Address.class)} )
}) public class Professor {
@Id @Column(name = "EMP_ID") private int id; private String name; private long salary; @OneToOne private Address address; @ManyToOne @JoinColumn(name = "DEPT_ID") private Department department; @ManyToOne @JoinColumn(name = "MANAGER_ID") private Professor manager; @OneToMany(mappedBy = "manager") private Collection<Professor> directs = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public String toString() { return "Professor id: " + getId() + " name: " + getName() + " with MgrId: " + (getManager() == null ? null : getManager().getId()); }
}
File: ProfessorService.java import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; public class ProfessorService {
protected EntityManager em; public ProfessorService(EntityManager em) { this.em = em; } public List findWithAlias() { Query query = em.createNativeQuery( "SELECT emp.emp_id AS emp_id, name, salary, manager_id, dept_id, address_id, " + "address.id, street, city, state, zip " + "FROM emp, address " + "WHERE address_id = id", "ProfessorWithAddressColumnAlias"); return query.getResultList(); }
}
File: Address.java import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}
File: JPAUtil.java import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class JPAUtil {
Statement st; public JPAUtil() throws Exception{ Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; Connection conn = DriverManager.getConnection(url, "sa", ""); System.out.println("Got Connection."); st = conn.createStatement(); } public void executeSQLCommand(String sql) throws Exception { st.executeUpdate(sql); } public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t"+ metadata.getColumnLabel(i + 1)); } System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t"+value.toString().trim()); } } System.out.println(""); } }
}
File: Main.java import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
public static void main(String[] a) throws Exception { JPAUtil util = new JPAUtil(); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ProfessorService"); EntityManager em = emf.createEntityManager(); ProfessorService service = new ProfessorService(em); em.getTransaction().begin();
service.findWithAlias(); util.checkData("select * from EMP"); em.getTransaction().rumit(); em.close(); emf.close(); }
}
File: persistence.xml
<persistence xmlns="http://java.sun.ru/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.ru/xml/ns/persistence http://java.sun.ru/xml/ns/persistence/persistence" version="1.0"> <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="update"/> <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/> <property name="hibernate.connection.username" value="sa"/> <property name="hibernate.connection.password" value=""/> <property name="hibernate.connection.url" value="jdbc:hsqldb:data/tutorial"/> </properties> </persistence-unit>
</persistence>
</source>