Data retrieving - JDBC with Eclipse

Question

I'm trying to retrieve data from my Database. It's PHP MyAdmin Database ( MySQL ). Here are the codes I wrote :

    package dto;

import java.sql.Timestamp;

public class Pictures {

int idPicture;
String longitude;
String latitude;
int status;
String path;
Timestamp timestamp;

/*
 * Constructor for a picture
 */
public Pictures(int idPicture,
String longitude,
String latitude,
int status,
String path,
Timestamp timestamp){

    this.idPicture = idPicture;
    this.longitude = longitude;
    this.latitude = latitude;
    this.status = status;
    this.path = path;
    this.timestamp = timestamp;


}

public int getIdPicture() {
    return idPicture;
}

public void setIdPicture(int idPicture) {
    this.idPicture = idPicture;
}

public String getLongitude() {
    return longitude;
}

public void setLongitude(String longitude) {
    this.longitude = longitude;
}

public String getLatitude() {
    return latitude;
}

public void setLatitude(String latitude) {
    this.latitude = latitude;
}

public int getStatus() {
    return status;
}

public void setStatus(int status) {
    this.status = status;
}

public String getPath() {
    return path;
}

public void setPath(String path) {
    this.path = path;
}

public Timestamp getTimestamp() {
    return timestamp;
}

public void setTimestamp(Timestamp timestamp) {
    this.timestamp = timestamp;
}

}

Above is the class where I have my pictures.

    package dao;

import java.sql.*;
import java.util.ArrayList;

import dto.Pictures;

public class Storingdb {

private static final String url = "jdbc:mysql://localhost/internship";
private static final String username = "root";
private static final String password = "";


public Storingdb() {

}
/*
 * Connection method
 */
private Connection connect() {

    Connection connexion;

    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException cnfe) {
        System.err.println("Error loading driver: " + cnfe);
    }
    // Connection to the database
    try {
        connexion = DriverManager.getConnection(url, username, password);
        return connexion;
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return null;
}

public Pictures retrieveData() {

    Pictures pic = null;

    int idPicture = 0;
    String longitude = null;
    String latitude = null;
    int status = 0;
    String path = null;
    Timestamp timestamp = null;

    Connection connexion = connect();
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        ps = connexion.prepareStatement("SELECT * FROM storing WHERE id=1");
        rs = ps.executeQuery();
        pic = new Pictures(idPicture, longitude, latitude, status, path, timestamp);


        while (rs.next()) {
            pic.setIdPicture(rs.getInt(1));
            longitude = rs.getString(2);
            latitude = rs.getString(3);
            status = rs.getInt(4);
            path = rs.getString(5);
            timestamp = rs.getTimestamp(6);



        }
    } catch (SQLException e) {

        e.printStackTrace();

    } finally {
        try {
            ps.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        try {
            connexion.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return pic;
}
}

Above is the DAO class.

<div>
    <%
    Storingdb dao = new Storingdb();
    //ArrayList<Pictures> file = new ArrayList<Pictures>();
    //file = dao.retrieveData(); 

    Pictures pic;
    pic = dao.retrieveData();


    String empty = "";
    if(pic.getStatus() == 0) {
        empty = "Empty";
    } else {
        empty = "Taken";
    }



    %>

    <table class="table table-bordered">
        <thead>
            <tr>
                <th>Parking Space ID</th>
                <th>Longitude</th>
                <th>Latitude</th>
                <th>Status :</th>
                <th>Update time :</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td><%= pic.getIdPicture() %></td>
                <td><%= pic.getLongitude() %></td>
                <td><%= pic.getLatitude() %></td>
                <td><%= empty %></td>
                <td><%= pic.getTimestamp() %></td>
            </tr>
        </tbody>


    </table>
</div>

And here is the HTML code of my JSP.

My table is returning null for everything but the iD / status. I don't understand why.

In my Database, the iD is an int(11), Auto Incrementing. longitude is VARCHAR(32) latitude is VARCHAR(32) status is int(1) path is VARCHAR(32) timestamp is TIMESTAMP that updates when Adding/Updating data.

Any tips on why it is null ?

I accept Tips instead of Direct answers so I can try to fix alone. Feel free to give me advice, but I accept the answer if you do want to give it.

Thanks in advance, I've been looking for the answer for 3 hours and I'm desperate haha !

Answer 1

You just selected the records from database but not retrieved it.

So try to iterate over ResultSet and get records from database like below in method retrieveData

rs = ps.executeQuery();
// retrieve records from database here
if(rs.next()) {
    idPicture = rs.getInt(1); // here 1 is column index of column pictureId
    longitude = rs.getString(2); // here 2 is column index of string longitude
    latitude = rs.getString(3);
    status = rs.getInt(4);
    path = rs.getString(5);
    timestamp = rs.getTimestamp(6);
    pic = new Pictures(idPicture, longitude, latitude, status, path, timestamp);
} else {
    // there is no record with id 1.
}

Hope this will help.