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.

Hope you are familiar with ORM (object relationship mapping), you need to have your table as emailqueue as an entity and can apply JPA over the same , you can follow many good tutorials on JPA , one such example is at callicoder.com/… — Yugansh
– Yugansh, Commented May 14, 2019 at 3:53
So I don't really need to use resultset but just keep picking single record and have it in a while(true) loop? — Daredevil
– Daredevil, Commented May 14, 2019 at 3:54
If the operation on your queue table is kind of OLTP data keeps on email queue filling all the time, then you have to come up with a mechanism to read it frequently , Spring Batch is a wonderful option for the same — Yugansh
– Yugansh, Commented May 14, 2019 at 3:57
Spring Batch + Spring JPA should be a very good choice for your usecase — Yugansh
– Yugansh, Commented May 14, 2019 at 3:58
It's not really OLTP,I basically want to run it as a service 24/7. This means,everything a new record inserts to email queue,then the jpa will pick up and send the email,it goes on and on.... — Daredevil
– Daredevil, Commented May 14, 2019 at 3:59

Yugansh · Accepted Answer · 2019-05-15 05:07:26Z

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);
        }

    }

answered May 15, 2019 at 5:07

Yugansh

3953 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daredevil Over a year ago

Is there a way I could keep this running? After i insert a new email record with status pending,it doesn't do anything though

Yugansh · Accepted Answer · 2019-05-15 04:47:48Z

1

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

edited May 15, 2019 at 4:47

answered May 14, 2019 at 4:05

Yugansh

3953 silver badges9 bronze badges

13 Comments

Daredevil Over a year ago

I already implemented the persistence,how do i access the values i want in result set using spring?

Yugansh Over a year ago

sorry for being bit late , here you go this is the simplest yo need to do find All to fetch result set thejavageek.com/2014/01/12/jpa-crud-example each time inside your while true loop

Daredevil Over a year ago

Could you check my edited post and see if this would work?

Daredevil Over a year ago

The only problem with your link I see is it requires me to specify the database name in persistence.xml. I might give my program to someone else and I don't want them to specify the database name in config file.

Daredevil Over a year ago

I added a new post of my problem: stackoverflow.com/questions/56141425/… you can check it out

|

Collectives™ on Stack Overflow

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

Edit

Edit 2

2 Answers 2

1 Comment

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

Edit 2

2 Answers 2

1 Comment

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related