Can't print information from SQL in Java

Question

The user must choose a Resort ID from the table that is displayed and the make a booking. I can't seem to find my problem, I want to print the name of the Resort that they are making a booking at.

String x = jTextFieldID.getText();
    Integer Resort = Integer.valueOf(x);
    int resort = Integer.parseInt(x);
    String sql = "SELECT RESORT_NAME FROM LouwDataBase.Resorts WHERE ID = "+Resort;  
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, resort);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                    String resortName = rs.getString("RESORT_NAME");
                    JOptionPane.showMessageDialog(null,
                    "You want to book at " + resortName);
    }

Are you getting an error? If so, what is it? The issue may be that you haven't formatted your table reference properly. The format is database.schema.table. Maybe try using LouwDatabase.dbo.Resorts (or the actual schema if it is differnt). — Jacob H, May 11 '17 at 19:32
May 11, 2017 9:36:37 PM it.pat.Booking jButton1ActionPerformed SEVERE: null java.sql.SQLException: Method 'executeQuery(String)' not allowed on prepared statement. at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.executeQuery(Unknown Source) — Louw, May 11 '17 at 19:37
The error message is quite clear: use `pstmt.executeQuery()` instead, without the string parameter. — Mick Mnemonic, May 11 '17 at 19:58

Youcef LAIDANI · Answer 1 · 2017-05-11T19:41:55.500

3

You have to use rs.next() :

ResultSet rs = pstmt.executeQuery(sql);
String resortName = "";
if(rs.next()){//<<-----------------------------
  resortName = rs.getString("RESORT_NAME");
}

JOptionPane.showMessageDialog(null, "You want to book at "+resortName);

If you want to get many results you can use while(rs.next){...} instead.

Note? for a good practice, don't use upper letter in beginning for the name of your variables ResortName use this instead resortName

edited May 11 '17 at 19:41

answered May 11 '17 at 19:32

Youcef LAIDANI

55,661
15
90
140

It's `rs.next()` and not `rs.next`, fix the typo :) – cнŝdk May 11 '17 at 19:41
Sorry I tried using a rs.next, but got the same error. Thanks for the help anyway. – Louw May 11 '17 at 19:41
@Swiper my mistake try usinf `rs.next()` with parenthesis instead – Youcef LAIDANI May 11 '17 at 19:42
@Swiper what DBMS you are using? – Youcef LAIDANI May 11 '17 at 19:47
I am using the JavaDB in NetBeans. – Louw May 11 '17 at 19:54
can you please share your last modification @Swiper ? – Youcef LAIDANI May 11 '17 at 19:55
I went back to my original, I kept getting the same error with each modification. – Louw May 11 '17 at 19:57

score 2 · Answer 2 · edited May 11 '17 at 19:43

2

You need to test over the ResultSet result before trying to read from it:

if(rs.next()) {
    String ResortName = rs.getString(1);
    JOptionPane.showMessageDialog(null, "You want to book at "+ResortName);
}

And you can use getString(1) to get the RESORT_NAME, check ResultSet .getString(int index) method for further details.

edited May 11 '17 at 19:43

Youcef LAIDANI

55,661
15
90
140

answered May 11 '17 at 19:40

cнŝdk

31,391
7
56
78

Just tried it, don't think I need a loop, I just want to display the Resort Name of the ID the users enters – Louw May 11 '17 at 19:52
@Swiper where did I mentioned `loop` in my answer? I don't get it. – cнŝdk May 11 '17 at 19:53
Sorry I mean the next() – Louw May 11 '17 at 19:56
Yes the test is just there to avoid getting `NullPointerException` if there's no matching result from DB. – cнŝdk May 11 '17 at 19:58
@Swiper **`rs.next()`, it shifts the cursor to the next row of the result set from the database and returns true if there is any row, otherwise false** for that you have to use `rs.next()` read this answer here http://stackoverflow.com/a/8199458/5558072 – Youcef LAIDANI May 11 '17 at 19:58

Joop Eggen · Accepted Answer · 2017-05-11T20:42:11.327

1

The error is that sql is passed to Statement.executeQuery(String) too, instead of the PreparedStatement.executeQuery().

int resort = Integer.parseInt(x);
//String sql = "SELECT RESORT_NAME FROM LouwDataBase.Resorts WHERE ID = ?";  
String sql = "SELECT RESORT_NAME FROM LouwDataBase.Resorts WHERE ID = " + resort;  
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    //pstmt.setInt(1, resort);
    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) {
            String resortName = rs.getString("RESORT_NAME");
            JOptionPane.showMessageDialog(null,
                "You want to book at " + resortName);
        }
    }
} catch (SQLException ex) {
    Logger.getLogger(Booking.class.getName()).log(Level.SEVERE, null, ex);
}

Commented is the alternative usage of a prepared statement (as normally used).

Also you should close statement and result set, which can be done automatically with try-with-resources as above.

Oh, oversaw almost, that rs.next() must be called. (The others already mentioned.)

edited May 11 '17 at 20:42

answered May 11 '17 at 19:58

Joop Eggen

107,315
7
83
138

java.sql.SQLException: The column position '1' is out of range. The number of columns for this ResultSet is '0'. – Louw May 11 '17 at 20:09
I Changed the WHERE ID = ?" to WHERE ID = "+Resort and got this error above – Louw May 11 '17 at 20:10
@Swiper if you don't share your update we can't help you, so please share your code please – Youcef LAIDANI May 11 '17 at 20:18
Either placeholder `?` and `setInt(1,...)` or no setInt. Prepared statements are intended to be used with placeholders, https://xkcd.com/327/ – Joop Eggen May 11 '17 at 20:28

Can't print information from SQL in Java

3 Answers3