Benoit Goderre Benoit Goderre - 29 days ago 8
Java Question

Resultset always empty when doing executeQuery

I am currently writing a simple Java app that reads information from an XLS file and then enters it in the database. Since that XLS does have duplicated records, I do a simple check if the entry in the XLS file already exists in the database. Here is my code:

public static void addResult(ArrayList<ArrayList<String>> listResults)
{
try
{
openDatabase();
stmt = c.createStatement();

for (int i = 0; i < listResults.size(); i++)
{
PreparedStatement stm = c.prepareStatement("SELECT player_name FROM results WHERE player_name=?;");
stm.setString(1, listResults.get(i).get(ReadResultsFile.NAME));
System.out.println(stm);
ResultSet rs = stm.executeQuery();

if (rs.getRow() <= 0)
{
String typeOfPlay = new String();

if (listResults.get(i).get(ReadResultsFile.TYPE).equals("Simple"))
{
typeOfPlay = "single";
}
else if (listResults.get(i).get(ReadResultsFile.TYPE).equals("Double"))
{
typeOfPlay = "double";
}

stm = c.prepareStatement("INSERT INTO results (player_name, school_id, " + typeOfPlay + ", tournament_id) "
+ "VALUES(?,?,?,?);");

stm.setString(1, listResults.get(i).get(ReadResultsFile.NAME));
stm.setString(2, listResults.get(i).get(ReadResultsFile.SCHOOL_ID));
stm.setInt(3, Integer.parseInt(listResults.get(i).get(ReadResultsFile.SCORE)));
stm.setString(4, "1");
stm.executeUpdate();
}
else
{
String typeOfPlay = new String();

if (listResults.get(i).get(ReadResultsFile.TYPE).equals("Simple"))
{
typeOfPlay = "single";
}
else if (listResults.get(i).get(ReadResultsFile.TYPE).equals("Double"))
{
typeOfPlay = "double";
}

stm = c.prepareStatement("UPDATE results SET " + typeOfPlay + "=? WHERE player_name=?;");

stm.setString(1, typeOfPlay);
stm.setString(2, listResults.get(i).get(ReadResultsFile.SCORE));
stm.setString(1, listResults.get(i).get(ReadResultsFile.NAME));
System.out.println(stm);
stm.executeUpdate();
}
}

closeDatabase();
}
catch (Exception e)
{
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
}


The problem that arises is that the rs.getRow() function always returns -1. I tried running the SELECT query directly in the database tool and the query returns the player_name column if there is already a similar entry existing. It unfortunately do the same in Java.

I am unsure what to do at this point.

Thank you for any hint!

Answer

getRow will not work as per the javadocs

Retrieves the current row number. The first row is number 1, the second number 2, and so on.

and

A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row

Usually use

while (rs.next ()) {....
Comments