Android SQLite 语法错误导致应用程序崩溃

Question

我一直在努力获得一个列表视图来填充来自 android studio 中 SQLite 的数据。当用户单击按钮以查看酒店列表时，列表视图应该填充。但是，每次应用程序崩溃时，我都会收到相同的错误。

例外情况：

java.lang.RuntimeException： 无法启动活动 ComponentInfo{com.example.aids.a09application/com.example.aids.a09application.HotelsList}： android.database.sqlite.SQLiteException： near "to"： syntax error (code 1(：，编译时：插入酒店 (hotel_id，hotel_name，电话，纬度，经度，价格，近到(值 (1，'Curragh B&B'，'[phone]'，53.124905，-6.789059，'A'，'蒙德罗 公园'(;

下面是 SQLHelper 类的代码：

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_HOTEL = "CREATE TABLE IF NOT EXISTS " + TABLE_HOTEL + "("
+ HOTEL_ID + " INTEGER PRIMARY KEY NOT NULL,"
+ HOTEL_NAME + " TEXT,"
+ PHONE + " TEXT," + LATITUDE + " REAL,"
+ LONGITUDE + " REAL," + PRICE + " TEXT,"
+ NEAR_TO + "TEXT" +");";
db.execSQL(CREATE_HOTEL);
db.execSQL(insertHotel(1,"Curragh B&B", "[phone]", 53.124905, -6.789059, "A", "Mondello Park"));
db.execSQL(insertHotel(2,"No. 5 Bed and Breakfast", "[phone]", 53.130330, -6.755625, "A", "Mondello Park"));
db.execSQL(insertHotel(3,"Maynooth Campus", "[phone]", 53.379794, -6.595520, "A", "Mondello Park"));
db.execSQL(insertHotel(4,"Tulloch","[phone]", 53.171297, -6.973274, "A", "Mondello Park"));
db.execSQL(insertHotel(5,"Kilcullen Home Stay","[phone]", 53.127930, -6.746819, "A", "Mondello Park"));
db.execSQL(insertHotel(6,"Cherryville House B&B","[phone]", 53.155753,-6.963233, "A", "Mondello Park"));
db.execSQL(insertHotel(7,"Athgarvan B&B","[phone]", 53.153120, -6.779494, "A", "Mondello Park"));
db.execSQL(insertHotel(8,"Naas Court Hotel","[phone]", 53.216508, -6.664741, "A", "Mondello Park"));
db.execSQL(insertHotel(9,"Gardeners Cottage","[phone]", 53.371688, -6.715289, "A", "Mondello Park"));
db.execSQL(insertHotel(10,"Bridge B&B","[phone]", 53.214835, -6.678269, "A", "Mondello Park"));
public String insertHotel(int hotelId, String hotelName, String phone, double latitude, double longitude, String price, String nearTo) {
String insertHotelToDB = "INSERT INTO " + TABLE_HOTEL
+ " (" + HOTEL_ID + "," + HOTEL_NAME + "," + PHONE + "," + LATITUDE + "," + LONGITUDE + "," + PRICE + "," + NEAR_TO
+ ") VALUES (" + hotelId + ",'" + hotelName + "','" + phone + "'," + latitude + "," + longitude + ",'" + price + "','" + nearTo + "')" + ";";
return insertHotelToDB;
}`

以下是酒店列表类别：

public class HotelsList extends AppCompatActivity {
ListView hotelList;
//SQLHelper db = new SQLHelper(this);
List<Hotel> getAllHotels;
ArrayAdapter adapter;
SQLHelper db = new SQLHelper(this);
@Override
public void onCreate (Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.hotel_list);
hotelList = (ListView)findViewById(R.id.hotel_listView);
getAllHotels = db.getAllHotels();
db.close();
// set up ArrayList for buildingNames
final List<String> hotelNames = new ArrayList<>();
// loop through getAllBuilding List and add all building_name 's to buildingNames list
for (int i = 0; i < getAllHotels.size(); i++) {
hotelNames.add(getAllHotels.get(i).getHotelName());
//sort alphabetically
Collections.sort(hotelNames);
}
// set up adapter for searchBar listView
adapter = new ArrayAdapter<>(HotelsList.this, android.R.layout.simple_list_item_1, hotelNames);
hotelList.setAdapter(adapter);
}
}

以下是包含打开酒店列表的按钮的片段：

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
// Inflate the layout for this fragment
View view = inflater.inflate(R.layout.fragment_open_hotels, container, false);
Button openHotel = (Button) view.findViewById(R.id.hotelsButton);
openHotel.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent intentOpenHotelPage = new Intent(getActivity(), HotelsList.class);
startActivity(intentOpenHotelPage);
}
});
return view;
}

Answer 1

你应该添加SPACE和删除;

不要

+ NEAR_TO + "TEXT" +");";

做

+ NEAR_TO + " TEXT" + ")";

然后卸载应用程序并运行。

Answer 2

我建议以略微不同的方式插入数据库以避免与语法相关的问题。SQLiteDatabase类能够通过使用ContentValues执行基本的 CRUD 操作。 一个简单的插入示例可能如下所示：

SQLiteDatabase database = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_1, "your col 1 value here");
values.put(COLUMN_2, "your col 2 value here");
values.put(COLUMN_3, "your col 3 value here");
...
database.insert(TABLE_1, null, values);

通过遵循这样的结构，您不再需要将查询生成为字符串并执行它们。 这将使您的生活更轻松，您将遵守一般的SQLite准则，并使代码更具可读性。

另外作为另一个提示/旁注：我注意到您基本上使用数据库类的 onCreate(( 中的一些值初始化表。 当我之前需要做类似的事情时，我创建了一个 initTable(( 函数，其中我遵循与上述相同的模式。 但是，请注意，插入必须在方法中多次出现才能完成插入多行，因此该函数最终将如下所示：

private void initPeople(SQLiteDatabase db){
ContentValues values = new ContentValues();
values.put(FIRST_NAME, "Foo");
values.put(LAST_NAME, "Bar");
db.insert(TABLE_PEOPLE, null, values);
values.put(FIRST_NAME, "John");
values.put(LAST_NAME, "Smith");
db.insert(TABLE_PEOPLE, null, values);
}

Answer 3

在查询中执行此操作，

+ NEAR_TO + "TEXT" + ")";

您的代码+ NEAR_TO + "TEXT" +");";错误