Java Persistence Frameworks - 1.1

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.javainterviewpoint.Employee" table="EMPLOYEE">
        <id name="id" column="ID">
            <generator class="assigned" />
        </id>
        <property name="name" column="NAME" />
        <property name="age" column="AGE" />
        <property name="dept" column="DEPT" />
    </class>
</hibernate-mapping>

public void updateEmployeeById(int id,String name)
    {
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        session.beginTransaction();
        
        Employee employee = (Employee)session.get(Employee.class, id);
        employee.setName(name);
        
        session.update(employee);
        session.getTransaction().commit();
        System.out.println("Employee Updated!!!");
    }

@Entity
@Table(name = "cast_student", schema = "TEST")
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;

}

public void updateStudent(Integer id, String firstName, String lastName, String email) {
    Transaction transaction = null;
    Session session = null;

    try{
        session = HibernateUtil.getSessionFactory().openSession();
        transaction = session.beginTransaction();

        Student student = session.get(Student.class, id);

        if(firstName != null){
            student.setFirstName(firstName);
        }
        if(lastName != null){
            student.setLastName(lastName);
        }
        if(email != null){
            student.setEmail(email);
        }
        session.update(student);
        transaction.commit();

    }catch (Exception e){
        try{
            transaction.rollback();
        }catch(RuntimeException re){
            re.getMessage();
        }
        throw e;
    }finally {
        if(session != null){
            session.close();
        }
    }
}

@Table(name = "account_table")
@Entity(name = "Account")
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String owner;
    private double balance;
    private double interestRate;
}

@Entity(name="DebitAccount")
@Table(name = "debit_account_table")
public class CreditAccount extends Account {

    private double creditLimit;
}

public boolean getDebitAccountCRUD(Long theId){
        
        try {
            session = dataSourceFactory.getSessionFactory().openSession();
            beginTransactionIfAllowed(session);
            DebitAccount creditAccount = session.get(DebitAccount.class, theId);
            session.getTransaction().commit();
            
            return true;
        }
}

@Table(name = "author")
@Entity
@SecondaryTables({
    @SecondaryTable(name = "author_details"),
    @SecondaryTable( name = "author_data")
})
public abstract class Author{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private double lastName;
    private double category;
}

public static void main(String[] args) {
        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            // start a transaction
            transaction = session.beginTransaction();
            // save the author object
            Author author = new Author();
            author.setBalance(10000.0);
            author.setInterestRate(10.0);
            author.setOwner("Ramesh");
            author.setCreditLimit(10000.0);
            session.save(author);
        
        }   
    }

public void deleteByIds() {

    UserContext.set("fred", "ten_2");

    MtContent a = newContent("title a");
    MtContent b = newContent("title b");


    List<Long> ids = Arrays.asList(a.getId(), b.getId(), 99998L);

    LoggedSql.start();
    int rows = server.deleteAll(MtContent.class, ids);
    assertThat(rows).isEqualTo(2);

    List<String> sql = LoggedSql.stop();
    assertSql(sql.get(0)).contains("delete from mt_content where id=? and tenant_id=?");
  }

public boolean saveCompany() {
        try {
            final Company theCompany = new Company();
            session = dataSourceFactory.getSessionFactory().openSession();
            beginTransactionIfAllowed(session);
            session.saveOrUpdate(theCompany);
            session.getTransaction().commit();
            logging.setMessage("CompanyDaoImpl -> saving company...");
            return true;
            
        } catch (HibernateException ex) {
            session.getTransaction().rollback();
            logging.setMessage("CompanyDaoImpl Error -> "+ex.getLocalizedMessage());
            return false;
        } finally {
            if(session.isOpen()){session.close();}
        }
    }

@Test
public void whenNamedQuery_thenMultipleEntityResult() {
  final Query query = em.createNativeQuery("SELECT e.id, e.name, d.id, d.employeeId, d.dayOfWeek "
                                           + " FROM employee e, schedule_days d "
                                           + " WHERE e.id = d.employeeId", "EmployeeScheduleResults");
  List<Object[]> results = query.getResultList();
  assertEquals(4, results.size());
  assertTrue(results.get(0).length == 2);

  Employee emp = (Employee) results.get(1)[0];
  ScheduledDay day = (ScheduledDay) results.get(1)[1];

  assertTrue(day.getEmployeeId() == emp.getId());
}

public class HibernateCriteriaBuilderJoinQueryClient {

    public static void main(String[] args) {
        try(Session session = HibernateUtil.getSessionFactory().openSession()) {
            CriteriaBuilder builder = session.getCriteriaBuilder();
            
            CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
            Root<Employee> empRoot = criteriaQuery.from(Employee.class);
            Root<Department> deptRoot = criteriaQuery.from(Department.class);
            
            criteriaQuery.multiselect(empRoot, deptRoot);
            
            criteriaQuery.where(builder.equal(empRoot.get("department"), deptRoot.get("id")));
            
            // List<Object[]> list = session.createQuery(criteriaQuery).getResultList();

             TypedQuery<Employee> query  = session.createQuery(criteriaQuery);
            
             List<Object[]> list = query.getResultList();

            for (Object[] objects : list) {
                Employee emp = (Employee) objects[0];
                System.out.println(emp.getId() + "\t" + emp.getName());
                System.out.println("----------------------------------");
                Department dept = (Department) objects[1];
                System.out.println(dept.getId() + "\t" + dept.getName());
            }
        } catch(HibernateException e) {
            e.printStackTrace();
        }
    }

}

@Entity
@Table(name = "Address")
@NamedQueries({ @NamedQuery(name = "@HQL_GET_ALL_ADDRESS", 
            query = "from Address") })
@NamedNativeQueries({ @NamedNativeQuery(name = "@SQL_GET_ALL_ADDRESS", 
            query = "select emp_id, address_line1, city, zipcode from Address") })
public class Address {
    @Id
    @Column(name = "emp_id", unique = true, nullable = false)
    @GeneratedValue(generator = "gen")
    @GenericGenerator(name = "gen", strategy = "foreign", parameters = { @Parameter(name = "property", value = "employee") })
    private long id;

    @Column(name = "address_line1")
    private String addressLine1;

    @Column(name = "zipcode")
    private String zipcode;

    @Column(name = "city")
    private String city;    
}

@SuppressWarnings("unchecked")
    public static void main(String[] args) {

        // Prep work
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();

        query = session.getNamedQuery("@HQL_GET_ALL_ADDRESS");
        List<Address> addressList = query.list();
        for (Address addr : addressList) {
            System.out.println("List of Address::" + addr.getId() + "::"
                    + addr.getZipcode() + "::" + addr.getEmployee().getName());
        }
        
        //Native SQL Named Query Example
        Query query = session.getNamedQuery("@SQL_GET_ALL_ADDRESS");
        List<Object[]> addressObjArray = query.list();
        for(Object[] row : addressObjArray){
            for(Object obj : row){
                System.out.print(obj + "::");
            }
            System.out.println("\n");
        }
        
        query = session.getNamedQuery("SQL_GET_ALL_EMP_ADDRESS");
        addressObjArray = query.list();
        for(Object[] row : addressObjArray){
            Employee e = (Employee) row[0];
            System.out.println("Employee Info::"+e);
            Address a = (Address) row[1];
            System.out.println("Address Info::"+a);
        }
        // rolling back to save the test data
        tx.commit();

        // closing hibernate resources
        sessionFactory.close();
    }

@Entity
@Table(name = "CAR")
@NamedStoredProcedureQueries({@NamedStoredProcedureQuery(name = "findByYearProcedure", procedureName = "FIND_CAR_BY_YEAR", resultClasses = { Car.class }, parameters = { @StoredProcedureParameter(name = "p_year", type = Integer.class, mode = ParameterMode.IN) }) })
@NamedStoredProcedureQuery(name = "getTotalCardsbyModelEntity", procedureName = "GET_TOTAL_CARS_BY_MODEL", resultClasses = { Car.class }, parameters = {@StoredProcedureParameter(mode = ParameterMode.IN, name = "model_in", type = String.class),@StoredProcedureParameter(mode = ParameterMode.OUT, name = "count_out", type = Integer.class)})
public class Car {

    private long id;
    private String model;
    private Integer year;

    public Car(final String model, final Integer year) {
        this.model = model;
        this.year = year;
    }
}

public void findCarsByYearNoNamed() {
    final StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("FIND_CAR_BY_YEAR", Car.class)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .setParameter(1, 2015);
    storedProcedure.getResultList()
        .forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
}

public void findCarsByModelYearNoNamed() {
    final StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("FIND_CARS_AFTER_YEAR", Car.class)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .setParameter(1, 2015);
    storedProcedure.getResultList()
        .forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
}

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.javainterviewpoint.Employee" table="EMPLOYEE">
        <id name="id" column="ID">
            <generator class="assigned" />
        </id>
        <property name="name" column="NAME" />
        <property name="age" column="AGE" />
        <property name="dept" column="DEPT" />
    </class>
    
    <query name="HQL_GET_ALL_EMPLOYEE">from Employee</query>

    <query name="HQL_GET_EMPLOYEE_BY_ID">
           <![CDATA[select from Employee where emp_id = :id]]>
    </query>

    <query name="HQL_GET_EMPLOYEE_BY_SALARY">
        <![CDATA[delete from Employee where emp_salary > :salary]]>
    </query>
    
    <sql-query name="SQL_GET_ALL_EMPLOYEE">
        <![CDATA[select emp_id, emp_name, emp_salary from Employee]]>
    </sql-query>
    
    <sql-query name="SQL_GET_ALL_EMP_ADDRESS">
        <![CDATA[select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id]]>
        <return alias="e" class="com.journaldev.hibernate.model.Employee" />
        <return-join alias="a" property="e.address"></return-join>
    </sql-query>
</hibernate-mapping>

public static void main(String[] args)
    {
        
        // Prep work
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();
        
        //HQL Named Query Example
        Query query = session.getNamedQuery("HQL_GET_EMPLOYEE_BY_SALARY");
        List<Employee> empList = query.list();
        for (Employee emp : empList) {
            System.out.println("List of Employees::" + emp.getId() + ","
                    + emp.getSalary());
        }
}

@Entity
@Table(name = "Address")
@NamedQueries({ @NamedQuery(name = "@HQL_GET_ALL_ADDRESS", 
            query = "from Address") })
@NamedNativeQueries({ @NamedNativeQuery(name = "@SQL_GET_ALL_ADDRESS", 
            query = "select emp_id, address_line1, city, zipcode from Address") })
public class Address {
    @Id
    @Column(name = "emp_id", unique = true, nullable = false)
    @GeneratedValue(generator = "gen")
    @GenericGenerator(name = "gen", strategy = "foreign", parameters = { @Parameter(name = "property", value = "employee") })
    private long id;

    @Column(name = "address_line1")
    private String addressLine1;

    @Column(name = "zipcode")
    private String zipcode;

    @Column(name = "city")
    private String city;    
}

public static void main(String[] args) {

        // Prep work
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.getCurrentSession();
        Transaction tx = session.beginTransaction();

        Query query = session.getNamedQuery("@HQL_GET_ALL_ADDRESS");
        List<Address> addressList = query.list();
        for (Address addr : addressList) {
            System.out.println("List of Address::" + addr.getId() + "::"
                    + addr.getZipcode() + "::" + addr.getEmployee().getName());
        }
}

private static void displayEntities(EntityManagerFactory emf) {
        //loading all entities
        EntityManager em = emf.createEntityManager();
        Query query = em.createQuery("Select e from Employee e");
        List<Employee> list = (List<Employee>) query.getResultList();
        Employee employee = new Employee("Sara Dorsey", "Admin");
        for (Employee employee : list) {
            //sending to ui for display
            displayEntity(employee);
            print("refreshing employee", employee);
            //now refreshing entity before doing something with it
            em.refresh(employee);
            print("after refreshing employee", employee);
        }
    }

@SuppressWarnings("unused")
public Page<Author> search(List<SearchCriteria> paramsAuthor, List<SearchCriteria> paramsBooks, Pageable pageable) {
        OpenJPACriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        OpenJPACriteriaQuery<Author> criteriaQuery = criteriaBuilder.createQuery(Author.class);
        CriteriaDelete<Author> criteriaDelete = criteriaBuilder.createCriteriaDelete(Author.class);
        CriteriaUpdate<Author> criteriaUpdate = criteriaBuilder.createCriteriaUpdate(Author.class);
        Root<Author> author = criteriaQuery.from(Author.class);
        Root<Author> author_update = criteriaUpdate.from(Author.class);
        Root<Author> author_delete = criteriaDelete.from(Author.class);
        Join<Author, Books> books = author.join("books", JoinType.INNER);
 
        Predicate predicate = this.predicateRootBuilder(criteriaBuilder, paramsAuthor, author);
        
        if(!paramsBooks.isEmpty())
            predicate = this.predicateJoinBuilder(criteriaBuilder, paramsBooks, books);
        
        Sort sort = pageable.isPaged() ? pageable.getSort() : Sort.unsorted();
        
        if (sort.isSorted())
            criteriaQuery.orderBy(toOrders(sort, author, criteriaBuilder));
        
        criteriaQuery.distinct(true);
        
        criteriaQuery.where(predicate);
        criteriaDelete.where(author.get("first_name"));
        criteriaUpdate.set(author.get("first_name"));
        TypedQuery<Author> typedQuery = entityManager.createQuery(criteriaQuery);
        entityManager.createQuery(criteriaDelete).executeUpdate();
        entityManager.createQuery(criteriaUpdate).executeUpdate();
        if(pageable == null)
            return new PageImpl<Author>(typedQuery.getResultList());
        else {
            Long total = (long) typedQuery.getResultList().size();
            
            // Sets the offset position in the result set to start pagination
            typedQuery.setFirstResult((int) pageable.getOffset());
            
            // Sets the maximum number of entities that should be included in the page
            typedQuery.setMaxResults(pageable.getPageSize());

            List<Author> content = total > pageable.getOffset() ? typedQuery.getResultList() : Collections.<Author> emptyList();

            return new PageImpl<Author>(content, pageable, total);
        }

public static void queryCustomers() {
        
        Customer customer = Ebean.find(Customer.class)
            .select("name")
            .fetch("address", "city")
            .where()
            .eq("city", "San Jose")
            .findOne();

    }

public static void CheckUpdate() {
        // Assuming you have an EbeanServer instance
        // EbeanServer ebeanServer = Ebean.getDefaultServer();

        // Create a query
        Query<Customer> query = Ebean.createQuery(Customer.class);

        // Define the conditions for the delete operation
        query.where().eq("status", "INACTIVE");

        // You can add more conditions as needed

        // Create a transaction
        Transaction transaction = Ebean.beginTransaction();
        try {
            // Execute the delete query with the specified transaction
            int rowsDeleted = query.update(transaction);

            // Commit the transaction
            transaction.commit();

            System.out.println("Rows deleted: " + rowsDeleted);
        } catch (Exception e) {
            // Rollback the transaction in case of an exception
            transaction.rollback();
            e.printStackTrace();
        } finally {
            // End the transaction
            transaction.end();
        }
    }

public static void CheckDelete() {
        // Assuming you have an EbeanServer instance
        // EbeanServer ebeanServer = Ebean.getDefaultServer();

        // Create a query
        Query<Customer> query = Ebean.createQuery(Customer.class);

        // Define the conditions for the delete operation
        query.where().eq("status", "INACTIVE");

        // You can add more conditions as needed

        // Create a transaction
        Transaction transaction = Ebean.beginTransaction();
        try {
            // Execute the delete query with the specified transaction
            int rowsDeleted = query.delete(transaction);

            // Commit the transaction
            transaction.commit();

            System.out.println("Rows deleted: " + rowsDeleted);
        } catch (Exception e) {
            // Rollback the transaction in case of an exception
            transaction.rollback();
            e.printStackTrace();
        } finally {
            // End the transaction
            transaction.end();
        }
    }

public static void CheckCreateNamedQuery() {
        Query<Customer> query = DB.createNamedQuery(Customer.class, "findByName");
        query.setParameter("name", name);
        List<Customer> customers = query.findList();
    }

public static void CheckExecute() {   
        // Create a new Database instance
        Database database = DatabaseFactory.create();

        // Prepare the SQL statement for the stored procedure call
        CallableSql callableSql = new CallableSql("{call update_customer(?, ?)}")
                .setParameter(1, 123)        // Set the productId parameter to 123
                .setParameter(2, 99.99);     // Set the new Price parameter to 99.99

        // Execute the Callable SQL statement and get the update count
        int updateCount = database.execute(callableSql);

        // Process the result
        System.out.println("Rows Updated: " + updateCount);
    }

<?xml version="1.0" encoding="UTF-8"?>
<ejb-jar> 
    <display-name>TradeEJBs</display-name>
    <enterprise-beans>
        <entity>
            <display-name>OrderEJB</display-name>
            <ejb-name>OrderEJB</ejb-name>
            <local-home>org.apache.geronimo.samples.daytrader.ejb.LocalOrderHome</local-home>
            <local>org.apache.geronimo.samples.daytrader.ejb.LocalOrder</local>
            <ejb-class>org.apache.geronimo.samples.daytrader.ejb.OrderBean</ejb-class>
            <persistence-type>Container</persistence-type>
            <prim-key-class>java.lang.Integer</prim-key-class>
            <reentrant>false</reentrant>
            <cmp-version>2.x</cmp-version>
            <abstract-schema-name>Orders</abstract-schema-name>
            <cmp-field>
                <field-name>orderFee</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>completionDate</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>orderType</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>orderStatus</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>price</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>quantity</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>openDate</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>orderID</field-name>
            </cmp-field>
            <primkey-field>orderID</primkey-field>
            <security-identity>
                <description/>
                <use-caller-identity/>
            </security-identity>
            <query>
                <description/>
                <query-method>
                    <method-name>findAll</method-name>
                    <method-params/>
                </query-method>
                <result-type-mapping>Local</result-type-mapping>
                <ejb-ql>SELECT OBJECT(o) FROM Orders o</ejb-ql>
            </query>
            <query>
                <description/>
                <query-method>
                    <method-name>findClosedOrders</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <result-type-mapping>Local</result-type-mapping>
                <ejb-ql>SELECT OBJECT(o) FROM Orders o WHERE
                    o.orderStatus = 'closed' AND
                    o.account.profile.userID  = ?1</ejb-ql>
            </query>
            <query>
                <description/>
                <query-method>
                    <method-name>findByUserID</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <result-type-mapping>Local</result-type-mapping>
                <ejb-ql>SELECT OBJECT(o) FROM Orders o WHERE
                    o.account.profile.userID  = ?1 ORDER BY o.orderID DESC</ejb-ql>
            </query>
            <query>
                <query-method>
                    <method-name>findByPrimaryKeyForUpdate</method-name>
                    <method-params>
                        <method-param>java.lang.Integer</method-param>
                    </method-params>
                </query-method>
                <result-type-mapping>Local</result-type-mapping>
                <ejb-ql>SELECT OBJECT(o) FROM Orders o WHERE o.orderID = ?1</ejb-ql>
            </query>
            <query>
                <query-method>
                    <method-name>findClosedOrdersForUpdate</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <result-type-mapping>Local</result-type-mapping>
                <ejb-ql>SELECT OBJECT(o) FROM Orders o WHERE
                    o.orderStatus = 'closed' AND
                    o.account.profile.userID  = ?1</ejb-ql>
            </query>
        </entity>
    </enterprise-beans>
</ejb-jar>