androdi SQLite的SQL语法错误

Question

public ArrayList<String> getCitiesFromCountry(int countryCode){

     ArrayList<String> cityNames = new ArrayList<String>(); 

     Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM " + COUNTRY_TABLE +
     " LEFT JOIN " + CITY_TABLE + " ON " + COUNTRY_TABLE + "." + _ID + " = " +
     CITY_TABLE + "." +  _ID2 + " WHERE " + COUNTRY_TABLE + "." + _ID + " = ?",
     new String{"1"});

     if (cursor != null){

        while(cursor.moveToNext()){
        cityNames.add(cursor.getString(cursor.getColumnIndex(CITY_NAME)));                   
     }
  }
  return cityNames;
  }

数据库包含：

  public static final String WORLD_DATABASE = "world_database";
  public static final String COUNTRY_TABLE = "country_table";
  public static final String CITY_TABLE = "city_table";
  public static final int DATABASE_VERSION = 1;
  public static final String _ID = "_id";
  public static final String _ID2 = "_id2";
  public static final String COUNTRY_NAME = "country_name";
  public static final String CITY_NAME = "city_name";

运行不起来，有SQL语法编译错误。

Answer 1

这个方法看起来是用来在一个数据库中查询某个国家的城市的名称的。

---

错误的原因可能是 SQL 语法的一些问题。这里有一些可能的问题：

• new String{"1"} 应改为 new String[]{"1"}。

• LEFT JOIN 应改为 INNER JOIN。

• WHERE 后应该指定国家代码的列名，而不是 _ID。

修改后的代码可能是这样的：

Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM " + COUNTRY_TABLE +
 " INNER JOIN " + CITY_TABLE + " ON " + COUNTRY_TABLE + "." + _ID + " = " +
 CITY_TABLE + "." +  _ID2 + " WHERE " + COUNTRY_TABLE + "." + COUNTRY_CODE + " = ?",
 new String[]{String.valueOf(countryCode)});