SELECT语句不返回MAX数 [英] SELECT statement not returning MAX number

问题描述

我在在以下code（这是我的数据库辅助类）SELECT语句的一个问题：

I'm having an issue with the SELECT statement in the following code (this is in my database helper class):

public Cursor selectMaxAreaNumber (long inspectionId) { 
String inspectionIdString = String.valueOf(inspectionId); 
String[] tableColumns = new String[] {  
        AREA_NUMBER,  
        "(SELECT max(" + AREA_NUMBER + ") FROM " + AREAS_TABLE + ") AS max"  
    };  
String whereClause = INSPECTION_LINK + " = ?"; 
String[] whereArgs = new String[] { 
        inspectionIdString           
    }; 
Cursor c = rmDb.query(AREAS_TABLE, tableColumns, whereClause, whereArgs,  
        null, null, null); 


if (c != null) { 
    c.moveToFirst(); 
} 
c.close(); 
return c; 

}

然后我在我的活动称之为如下：

I then call this in my activity as follows:

Cursor c = rmDbHelper.selectMaxAreaNumber(inspectionId); 
    startManagingCursor(c); 
    c.moveToFirst(); 
    nextAreaNumber = c.getInt(c.getColumnIndex("max")) + 1;

据拉从表的最大数量，因此它似乎在WHERE语句失败。不过，我看不出是什么问题。

It is pulling the maximum number from the table, so it seems the WHERE statement is failing. However, I can't see what the issue is.

由于这个问题，任何人都可以确认如果我通过ID转换成字符串，在查询中使用，而且我需要在这两个位写c.moveToFirst（）之前，做正确的事情的一部分？

As part of this question, can anyone confirm if I am doing the right thing by converting the ID into a String before it is used in the query and do I need to write c.moveToFirst() in both bits?

提前感谢！

推荐答案

试试这个：

public Cursor selectMaxAreaNumber (long inspectionId) { 
    String[] tableColumns = new String[] {  
        "Max(" + AREA_NUMBER + ") AS max"  
    };  
    String whereClause = INSPECTION_LINK + " = ?"; 
    String[] whereArgs = new String[] { 
        String.valueOf(inspectionId); 
    }; 
    return rmDb.query(AREAS_TABLE, tableColumns, whereClause, whereArgs,  
        null, null, null); 
} 

这将返回一个指针最大的 AREA_NUMBER 具有相应 inspectionId 。

This will return a Cursor with the largest AREA_NUMBER that has the appropriate inspectionId.

一对夫妇的注意事项：

• 当你使用像函数MAX（）你只有一次行作为一个结果，没有必要要求 AREA_NUMBERS 的和的 MAX（AREA_NUMBERS）


• A光标可能的空的，但它不会是空


• 请不要关闭游标您使用它之前

• When you use a function like Max() you only get one row as a result, there is no need to ask for AREA_NUMBERS and Max(AREA_NUMBERS)
• A Cursor might be empty, but it won't be null
• Don't close a Cursor before you have used it

所以，你不需要这样的：

So you don't need this:

if (c != null) { 
    c.moveToFirst(); 
} 
c.close(); 

明白，如果 C 某种程度上是空，你仍然会收到 NullPointerException异常c.close（）

Understand that if c somehow was null, you will still receive a NullPointerException on c.close()

（可选）您可以删除 whereArgs 键，只需使用：

(Optional) You could remove the whereArgs and just use:

String whereClause = INSPECTION_LINK + " = " + inspectionId;

（仅因为 inspectionId 是长数据类型，你需要一个字符串来执行注入攻击。）

(Only because inspectionId is long data type, you need a String to perform an injection attack.)

最后，你应该检查空光标位置：

Finally you should check for an empty Cursor here:

Cursor c = rmDbHelper.selectMaxAreaNumber(inspectionId); 
startManagingCursor(c); 
if(c.moveToFirst()) 
    nextAreaNumber = c.getInt(c.getColumnIndex("max")) + 1;
else //empty Cursor, return a default value
    nextAreaNumber = 0;

这篇关于SELECT语句不返回MAX数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！