从MySQL选择语句获取整数 [英] Getting an Integer From MySQL Select Statement

问题描述

我想获得一个整数保存在我的MySql数据库中的一个Integer在Java。我有一个表，包括PlayerName和Level。我想从特定玩家获得级别（整数）。然后向其中添加整数Value。然后把它放回DB。我的代码到现在是：

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.

推荐答案

首先你必须明白的是，当你不使用参数化语句，存在 SQL注入的巨大风险。所以你的代码很脏写。无论如何，使用 PreparedStatement 和参数化 SQL语句更好的性能。现在重写代码如下：

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();
      }
   }
}

你的语句，设置参数，如果你有，那么当你使用select，你将检索在 ResultSet 中的数据，这是使用查询生成的数据表。在 ResultSet 中的位置是第一行之前的位置，因此您必须使用 next（）方法继续当前行和在getter方法的帮助下，将 ResultSet 中的数据添加到您的变量。然后你检查它是否正确，如果做，init第二个语句并执行它。

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.

但你应该考虑当你使用更多的1操作，设置 autoCommit 为false和所有操作将在一个 Transaction 中执行，因为隐式地在 JDBC 中是一个操作=一个事务。其次，您应该考虑使用 SQL存储过程添加任何数据，更新数据或删除。它更安全，更少的代码。所以让数据库工作时，它能够做到，它的速度当然更快。
最后，真的你应该考虑这种方法，并使你的代码更安全，更快捷，更清洁。

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.

有关 SQL注入

当您决定正确使用存储过程时，可以使用它像这样：

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;

现在您必须为调用过程创建String。

So now you have to create String for call procedure.

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

那么 PreparedStatement 的intead必须使用 CallableStatement 是 interface 用于执行SQL存储过程。

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.

尊敬

这篇关于从MySQL选择语句获取整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！