java- how to retrieve resultset from database without using JDBC?

Question

I am writing a program that acts as a service and picks up emails from the email queue table, processes them and sends them out. Here is something along how I did it, and it does work fine.

MySqlConnect con = new MySqlConnect();
public PreparedStatement preparedStatement = null;    
public Connection con1 = con.connect();

//pick up queue and send email
public void email() throws Exception {

    try {

        while(true) {

            String sql = "SELECT id,user,subject,recipient,content FROM emailqueue WHERE status='Pending' ";
            PreparedStatement statement = con1.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {

                String subject = rs.getString("subject");    
                String recipient = rs.getString("recipient");   
                String content = rs.getString("content");
                String id = rs.getString("id");
                String username = rs.getString("user");
                String emailStatus = "DONE";
                String errormsg = sendEmail(recipient, subject, content, id,username);

                if (!errormsg.equals("")) {
                    emailStatus = "FAILED";
                }

                TerminalLogger.printMsg("Status  : " + emailStatus);    
            }

            statement.close();
            rs.close();
        }

    } catch(Exception e) {
        e.printStackTrace();
        TerminalLogger.printMsg("Exception: "+e.toString());
    }

    con1.close();
    Thread.sleep(2000);    
}

Now, I am clearly using JDBC to obtain the result set in the loop and process them as shown. Of course, I also need to specify my database connection in MySqlConnect.java properties. While all this works perfectly fine, I was wondering is there another way of achieving the same goal without using JDBC, i.e. specifying the connection properties?

I was thinking of Java Persistence. I am new to this.

Edit

I have been told to use JPA to achieve this and I have written it in this way:

public void email() throws Exception {

    try {

        while(true) {

            String sql = "select p.id,p.user,p.subject,p.recipient,p.content from Emailqueue p where " +
                "status='Pending'";

            List<Object[]> list = em.createQuery(sql).getResultList();
            for (Object[] obj : list) {
                System.out.println(obj[0]);
                System.out.println(obj[1]);
                System.out.println(obj[2]);
                System.out.println(obj[3]);
                System.out.println(obj[4]);
            }         
        }

    } catch(Exception e) {
        e.printStackTrace();
        TerminalLogger.printMsg("Exception: " + e.toString());
    }

From here, I would pass the parameters I want to the method. Is this way feasible?

Edit 2

Did it a bit different like below:

String id = ejbCon.getSettingsFacade().getid();
String username = ejbCon.getSettingsFacade().getUser();
String subject = ejbCon.getSettingsFacade().getSubject();
String recipient = ejbCon.getSettingsFacade().getRecipient();
String content = ejbCon.getSettingsFacade().getContent();
String errormsg = sendEmail(recipient, subject, content, id,username);    

public String getContent() {
    try {
        String sql="Select content FROM emailqueue WHERE status='Pending'";
        if (em == null) {
            throw new Exception("could not found subject");
        }

        return (String) em.createNativeQuery(sql).getSingleResult();        

    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

Just a bit idea of how the method looks like, the other methods follow the same concept.

Answer 1

   List<EmailQueue> emailList = em.createQuery(sql).getResultList();
        for (EmailQueue obj : emailList) {
              String emailStatus = "DONE";
              String errormsg=sendEmail(obj.getRecipient(), obj.getSubject, obj.getContent(),obj.getId(),obj.getUsername());
                    if (!errormsg.equals("")) {
                        emailStatus = "FAILED"
                    }
                    TerminalLogger.printMsg("Status  : " + emailStatus);
        }

    }

Answer 2

Before using JPA ,you must read about it WHY JPA

As discussed in the comments above, Spring Batch and Spring JPA is a good choice for your use-case, you can follow and study about on the internet and follow the official document

Spring JPA tutorial link

Spring Batch tutorial link

Happy Learning, Hope more users would suggest other good options that you can choose from and apply to your use-case post evaluating their pros and cons