将SQL计数设置为java中的变量
将SQL计数设置为java中的变量
提问于 2015-12-09 16:26:12
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]+"'");
}当我运行它时，它只会执行插入，在第二次运行时，它不会再次执行。我认为这与行没有正确地转换为int有关。我能做些什么来正确地设置它？
回答 4
Stack Overflow用户
发布于 2015-12-09 16:34:24
你的代码应该是：
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
}
}请(！)对INSERT和UPDATE语句也使用PreparedStatement。
Stack Overflow用户
发布于 2015-12-09 16:29:11
不知道为什么你要在那里做last()和getRow() -从你的计数(*)中得到一个也是唯一的结果的“默认”方法是这样的：
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);
}还请考虑关闭您的Statement%s和ResultSet%s。否则会造成资源泄漏。
此外，您应该检查PreparedStatement及其使用，以防止在您的代码中注入SQL。
Stack Overflow用户
发布于 2015-12-09 16:28:30
select count查询始终返回1行。
您需要获取计数的值，并测试该值是否大于0。
正确的代码应与以下代码类似：
long rows = 0;
try {
if (rs.next()) {
rows = rs.getLong(1);
}
if (rows > 0) {
// Query for update
} else {
// Query for insert
}
....附加提示：不要使用Statement。使用PreparedStament代替，它解决了与sql注入有关的问题，并且从程序员的角度看更容易阅读。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34173958
复制相关文章
相似问题
腾讯云开发者
