java - Getting an Integer From MySQL Select Statement

Question

I would like to get an integer saved in my MySql DB into an Integer in Java. I have a Table, that includes PlayerName and Level. I would like to get The Level (Integer) From a Specific Player. And then Add Integer "Value" to it. Then put it back in the DB. My Code up to now is:

public void addinputPData(String loc, int value, Player player, String playername){
    //add input Player Data
    try{
        logm("Putting Kill Death Int Data into  " +player.getName() + "'s Profile!");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/WebCom", "root", "MyPW");

            int ovalue = -1;    
        Statement stmt = (Statement) con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT "+loc+" FROM PlayerData WHERE PlayerName='"+playername+"'");
        if(rs.next()){
            ovalue= rs.getInt(loc);
        }
        if(ovalue == -1){
            logm("Error Occured");

        }
        int nvalue = value + ovalue;

        String insert = "UPDATE PlayerData SET "+ loc + "='" + nvalue + "' WHERE PlayerName='" + playername + "'";

        stmt.executeUpdate(insert);

        con.close();

    }catch(Exception e){

        logm("Could Not Send Data To MYSQL DATABASE SERVER s: "+ e.getMessage());
    }
}

I don't know why this won't work, Is there anything obvious that i am missing? Thank you in advance.

Answer 1

So first what you must understand is that when you won't use parametrized statements, there is big danger of SQL Injection. So your code is very dirty written. So anyway, use PreparedStatement with parametrized SQL statements for much more better performace. Now rewrite your code like this:

final String SELECT_QUERY = "SELECT level FROM PlayerData WHERE PlayerName = ?";
final String UPDATE_QUERY = "UPDATE PlayerData SET level = ? WHERE PlayerName = ?";

public boolean dataMethod(String playerName) {
   Connection con = null;
   PreparedStatement ps = null;
   PreparedStatement ps1 = null;
   ResultSet rs = null;
   int dataLevel = 0;

   try {

   // getConnection etc...
   ps = con.prepareStatement(SELECT_QUERY);
   ps.setString(1, playerName) // first param is order of ? param, starts with 1(not 0)
   rs = ps.executeQuery();
   while (rs.next()) {
      dataLevel = rs.getInt();
   }
   if (dataLevel > 0) {
       ps1 = con.prepareStatement(UPDATE_QUERY);
       ps1.setInt(1, dataLevel);
       ps1.setString(2, playerName);
       ps1.executeUpdate();   
   }
   return true;
   }
   catch (SQLExcetion ex) {
      Logger.getLogger(YourClass.class.getName()).log(Level.SEVERE, null, ex);
      return false;
   }
   finally {
      if (con != null) {
         con.close();
      }
   }
}

Step by step, first init your statement, sets parameters if you have then when you use select, you will retrieve data in ResultSet that is table of data generated with query. imlicitly cursor in ResultSet is position before first row so you have to use next() method to go on current row and with the help of getter method you add data from ResultSet to your variable. Then you check if it's correct, if do, init second statement and execute it. And that's all.

But you should consider when you use more that 1 operation, sets autoCommit on false and all operations will do in one Transaction, because implicitly in JDBC is one operation = one transaction. And second, you should consider to use SQL stored procedures for add any data, update data or delete. It's more safer yet and less code. So let database working when it able to do it and also it's faster of course. At the last, really you should think about this approach and makes your code more safer, faster and cleaner. Not have look on simplicity but on efficiency, compability and security.

More about SQL Injection

And when you decided right to use stored procedure, you can use it like this:

CREATE OR REPLACE PROCEDURE SOME_NAME(VARCHAR v_name PlayerData.name%type)
AS
BEGIN
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   // body
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;

So now you have to create String for call procedure.

final String CALL_SOMENAME = "{call SOME_NAME(?)}";

Then intead of PreparedStatement you have to use CallableStatement that is interface used to execute SQL stored procedures.

cs.prepareCall(CALL_SOMENAME); // Creates a cs object for calling db stored procedures
cs.setString(1, playerName);
cs.execute();

I don't know why many people searching the easiest way to do something and don't look at performance and readability of code.

Regards