BRHSM BRHSM - 4 years ago 137
Java Question

`createSQLException` when trying to get all rows in MySQL table

I have a simple table consisting of 4 strings and 2 integers (of which one is an ID).

enter image description here

I use a special function to get all rows inside that table:

public Booking[] displayAllBookings() throws ClassNotFoundException, SQLException{
Connection con = connect.getConnection();

PreparedStatement counter = (PreparedStatement) con.prepareStatement("select count(*) from bookings");
ResultSet count = counter.executeQuery();

Booking[] array = new Booking[count.getInt(1)];

String name = null,surname = null,begindate = null,enddate = null;
int persons = 0,i=0;

PreparedStatement posted = (PreparedStatement) con.prepareStatement("SELECT * FROM bookings");
result = posted.executeQuery();
while (result.next()){
begindate = result.getString("begindate");
enddate = result.getString("enddate");
name = result.getString("Name");
surname = result.getString("Surname");
persons = result.getInt("persons");
Booking temp = new Booking(begindate,enddate,name,surname,persons);
array[i++]=temp;
}
return array;
}


But when I execute it I get this exception:

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2472)
at DataBase.DataCommunicatorBooking.displayAllBookings(DataCommunicatorBooking.java:59)
at DataBase.main.displayAll(main.java:125)
at DataBase.main.main(main.java:71)


I've tried looking up this exception but I can't find anything that corresponds to my problem.

Answer Source

The problem was with the

PreparedStatement counter = (PreparedStatement) con.prepareStatement("select count(*) from bookings");
ResultSet count = counter.executeQuery();

so I used an ArrayList like so:

public Booking[] displayAllBookings() throws ClassNotFoundException, SQLException{
        Connection con = connect.getConnection();

        ArrayList<Booking> bookings = new ArrayList<Booking>(); 

        String name = null,surname = null,begindate = null,enddate = null;
        int persons = 0,i=0;

        PreparedStatement posted = (PreparedStatement) con.prepareStatement("SELECT * FROM bookings");
        result = posted.executeQuery();
        while (result.next()){
            begindate = result.getString("begindate");
            enddate = result.getString("enddate");
            name = result.getString("Name");
            surname = result.getString("Surname");
            persons = result.getInt("persons");
            Booking temp =  new Booking(begindate,enddate,name,surname,persons);
            bookings.add(temp);
        }
        return bookings.toArray(new Booking[bookings.size()]);
    }

and that solved the problem;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download