How do i convert this SQL query into a criteriaBuilder\JPA method?

I am using Java 8. I have a SQL database with nodeId, name, ipaddress, and status. I have a class called ServerDao and a DTO class called ServersDto.

I want my method to ping the ipaddresses in my database, and for the status column to change to up or down whether they ping or not. I can get this working using SQL queries, but I would like to get this working using criteriaBuilder instead or JPQL.

The problem I have is that I cant map the ‘inetaddress’ to the ip column like I did in the SQL query. Is there anyone that can point me in the correct direction?

I successfully got it working using SQL queries below:

    public void pingServer(String ipAddr ) throws Exception{
        Connection myCon = null;
        PreparedStatement myStmt = null;
        PreparedStatement myStmt2 = null;
        ResultSet rs = null;
        
        InetAddress inet = null;
        String ip = ipAddr;
        
        int nodeId;
        

            myCon = dataSource.getConnection();
            
            
            String sql =  "select * from heartbeat.nodes";

            myStmt = myCon.prepareStatement(sql);
            Statement myStmt21 = myCon.createStatement();
            Statement myStmt3 = myCon.createStatement();
            
            rs = myStmt.executeQuery(sql);
                
            while(rs.next()) {
                ip = rs.getString("ipAddress");
                inet = InetAddress.getByName(ip);
                nodeId = rs.getInt("nodeId");
            

                if(inet.isReachable(100)) {
                    myStmt21.executeUpdate("UPDATE heartbeat.nodes SET status="SERVER_UP" WHERE nodeId = " + nodeId);
                    
                } else {
                    myStmt3.executeUpdate("UPDATE heartbeat.nodes SET status="SERVER_DOWN" WHERE nodeId = " + nodeId);
                }
            }

How do I get it working with criteriaBuilder Instead? Assigning the ‘inetaddress’ is vital for this to work, is it possible to map\assign the ‘ipaddress’ column back from a List to a variable assigned to the inetaddress like i did in the sql query?

Below is my ServersDao class (my ServersDto class is exactly the same):

    private int nodeId;
    private String ipAddress;
    private String name;
    private Status status;

You need to sign in to view this answers

Related Post