Setting an SQL count as a variable in java

Question

ResultSet rs = stm.executeQuery("SELECT count(*) FROM `hs` WHERE `username` ='"+c.playerName+"' LIMIT 1");
int rows = 0;

try
{
    rs.last();
    rows = rs.getRow();
    rs.beforeFirst();
} 
catch(Exception ex)
{
    ex.printStackTrace();
    return false;
}

if(rows > 0)
{
    query("UPDATE `hs` SET `overall_lvl` = '"+overallLVL+"',`overall_xp` = '"+overallXP+"',`attack_lvl` = '"+c.playerLevel[0]+"',`attack_xp` = '"+c.playerXP[0]+"',`defence_lvl` = '"+c.playerLevel[1]+"',`defence_xp` = '"+c.playerXP[1]+"',`strength_lvl` = '"+c.playerLevel[2]+"',`strength_xp` = '"+c.playerXP[2]+"',`ranged_lvl` = '"+c.playerLevel[3]+"',`ranged_xp` = '"+c.playerXP[3]+"',`prayer_lvl` = '"+c.playerLevel[4]+"',`prayer_xp` = '"+c.playerXP[4]+"',`magic_lvl` = '"+c.playerLevel[5]+"',`magic_xp` = '"+c.playerXP[5]+"',`cooking_lvl` = '"+c.playerLevel[6]+"',`cooking_xp` = '"+c.playerXP[6]+"',`woodcutting_lvl` = '"+c.playerLevel[7]+"',`woodcutting_xp` = '"+c.playerXP[7]+"',`fletching_lvl` = '"+c.playerLevel[8]+"',`fletching_xp` = '"+c.playerXP[8]+"',`fishing_lvl` = '"+c.playerLevel[9]+"',`fishing_xp` = '"+c.playerXP[9]+"',`firemaking_lvl` = '"+c.playerLevel[10]+"',`firemaking_xp` = '"+c.playerXP[10]+"',`crafting_lvl` = '"+c.playerLevel[11]+"',`crafting_xp` = '"+c.playerXP[11]+"',`smithing_lvl` = '"+c.playerLevel[12]+"',`smithing_xp` = '"+c.playerXP[12]+"',`mining_lvl` = '"+c.playerLevel[13]+"',`mining_xp` = '"+c.playerXP[13]+"',`herblore_lvl` = '"+c.playerLevel[14]+"',`herblore_xp` = '"+c.playerXP[14]+"',`agility_lvl` = '"+c.playerLevel[15]+"',`agility_xp` = '"+c.playerXP[15]+"',`thieving_lvl` = '"+c.playerLevel[16]+"',`thieving_xp` = '"+c.playerXP[16]+"',`slayer_lvl` = '"+c.playerLevel[17]+"',`slayer_xp` = '"+c.playerXP[17]+"',`farming_lvl` = '"+c.playerLevel[18]+"',`farming_xp` = '"+c.playerXP[18]+"',`runecrafting_lvl` = '"+c.playerLevel[19]+"',`runecrafting_xp` = '"+c.playerXP[19]+"',WHERE `username` = '"+c.playerName+"'");
}
else
{
    query("INSERT INTO `hs` SET `username` = '"+c.playerName+"', `overall_lvl` = '"+overallLVL+"',`overall_xp` = '"+overallXP+"',`attack_lvl` = '"+c.playerLevel[0]+"',`attack_xp` = '"+c.playerXP[0]+"',`defence_lvl` = '"+c.playerLevel[1]+"',`defence_xp` = '"+c.playerXP[1]+"',`strength_lvl` = '"+c.playerLevel[2]+"',`strength_xp` = '"+c.playerXP[2]+"',`ranged_lvl` = '"+c.playerLevel[3]+"',`ranged_xp` = '"+c.playerXP[3]+"',`prayer_lvl` = '"+c.playerLevel[4]+"',`prayer_xp` = '"+c.playerXP[4]+"',`magic_lvl` = '"+c.playerLevel[5]+"',`magic_xp` = '"+c.playerXP[5]+"',`cooking_lvl` = '"+c.playerLevel[6]+"',`cooking_xp` = '"+c.playerXP[6]+"',`woodcutting_lvl` = '"+c.playerLevel[7]+"',`woodcutting_xp` = '"+c.playerXP[7]+"',`fletching_lvl` = '"+c.playerLevel[8]+"',`fletching_xp` = '"+c.playerXP[8]+"',`fishing_lvl` = '"+c.playerLevel[9]+"',`fishing_xp` = '"+c.playerXP[9]+"',`firemaking_lvl` = '"+c.playerLevel[10]+"',`firemaking_xp` = '"+c.playerXP[10]+"',`crafting_lvl` = '"+c.playerLevel[11]+"',`crafting_xp` = '"+c.playerXP[11]+"',`smithing_lvl` = '"+c.playerLevel[12]+"',`smithing_xp` = '"+c.playerXP[12]+"',`mining_lvl` = '"+c.playerLevel[13]+"',`mining_xp` = '"+c.playerXP[13]+"',`herblore_lvl` = '"+c.playerLevel[14]+"',`herblore_xp` = '"+c.playerXP[14]+"',`agility_lvl` = '"+c.playerLevel[15]+"',`agility_xp` = '"+c.playerXP[15]+"',`thieving_lvl` = '"+c.playerLevel[16]+"',`thieving_xp` = '"+c.playerXP[16]+"',`slayer_lvl` = '"+c.playerLevel[17]+"',`slayer_xp` = '"+c.playerXP[17]+"',`farming_lvl` = '"+c.playerLevel[18]+"',`farming_xp` = '"+c.playerXP[18]+"',`runecrafting_lvl` = '"+c.playerLevel[19]+"',`runecrafting_xp` = '"+c.playerXP[19]+"'");

}

When I run this, it only will execute the insert and on second run it won't execute again. I believe it's to do with the rows not correctly turning into an int. What can I do to set this up correctly?

Please, learn how to use parameters, do not use string concatenation because of en.wikipedia.org/wiki/SQL_injection — Betlista
– Betlista, Commented Dec 9, 2015 at 8:28

Andreas · Accepted Answer · 2015-12-09 08:34:23Z

2

Your code should be:

int rows; // No need to initialize
try (PreparedStatement stmt = conn.prepareStatement("SELECT count(*)" +
                                                     " FROM `hs`" +
                                                    " WHERE `username` = ?")) { // No need for LIMIT 1
    stmt.setString(1, c.playerName); // This prevents syntax errors and SQL Injection issues
    try (ResultSet rs = stmt.executeQuery()) {
        rs.next(); // SELECT count(*) always returns exactly 1 row
        rows = rs.getInt(1); // Get value of first column
    }
}

Please(!) use PreparedStatement for your INSERT and UPDATE statements too.

answered Dec 9, 2015 at 8:34

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan · Accepted Answer · 2015-12-09 08:43:38Z

1

not sure why you do the last() and getRow() there - the "default" way of getting the one and only result from your count(*) would be like this:

int rows = 0;
try {
  //Check if there is a row - the if you could leave out in this case.
  if(rs.next()) {
    //Get the int as position 1 in the result - that's your count(*)
    rows = rs.getInt(1);
  }

Please also consider closing your Statements and ResultSets. You create resource leaks otherwise.

Also you should check into PreparedStatement and it's use in order to prevent SQL Injection in your code.

edited Dec 9, 2015 at 8:43

answered Dec 9, 2015 at 8:29

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Comments

Betlista · Accepted Answer · 2015-12-09 08:32:01Z

0

I'd propose to change query to

SELECT count(*) cnt FROM

and then use

rs.getInt("cnt"); // do something

answered Dec 9, 2015 at 8:32

Betlista

10.6k14 gold badges76 silver badges115 bronze badges

Comments

Davide Lorenzo MARINO · Accepted Answer · 2015-12-09 08:44:05Z

0

A select count query returns always 1 row.

You need to get the value of the count and test if that value is > 0 or not.

The correct code should be similar to the following:

long rows = 0;
try {
   if (rs.next()) {
       rows = rs.getLong(1);
   }
   if (rows > 0) {
       // Query for update
   } else {
       // Query for insert
   }
   ....

Additional tip: don't use a Statement. Use instead a PreparedStament, it solve problems related to sql injection and are simpler to read from a programmer point of view

edited Dec 9, 2015 at 8:44

answered Dec 9, 2015 at 8:28

Davide Lorenzo MARINO

27k4 gold badges45 silver badges58 bronze badges

2 Comments

Gyro Gearless Over a year ago

So i suppose the use of getRow() is just an mistake here?

Davide Lorenzo MARINO Over a year ago

Yes sorry, it should be a long, with the related method getLong(1)

Collectives™ on Stack Overflow

Setting an SQL count as a variable in java

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related