PartWOHotlineRepository.java

package com.eksad.masterdata.repository;

import com.eksad.masterdata.common.dto.WorkOrderSOHotlineDTO;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
@Transactional
public class PartWOHotlineRepository {

    @PersistenceContext
    EntityManager entityManager;

    public List<WorkOrderSOHotlineDTO> getSOHotline(String woID) {
        try {
            List<WorkOrderSOHotlineDTO> list = new ArrayList<>();
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection cnctn) throws SQLException {
                    String query = "SELECT * FROM get_soid_and_hotlineflag_by_woid(?)";
                    PreparedStatement ps = cnctn.prepareStatement(query);
                    ps.setString(1, woID);

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        WorkOrderSOHotlineDTO data = new WorkOrderSOHotlineDTO();
                        data.setSoID(rs.getString("soid"));
                        data.setHotlineFlag(rs.getBoolean("hotlineflag"));
                        list.add(data);
                    }
                }
            });
            return list;
        } catch (HibernateException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }
}