id 在
Sqlite 数据库中插入时不会生成唯一 id。有大学列表，大学列表和学校列表，我必须为此添加书签。当我为学校的第一项添加书签时，学院和大学的 ist 项目被添加为书签。如何解决这个问题？？
数据库帮助程序类
public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseUpdatedListener databaseUpdatedListener;
static final String DATABASE_NAME = "BookmarkDatabase";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME_BOOKMARK = "bookmark";
// Contact table columns name
private static final String ID = "Id";
private static final String NAME = "Name";
private static final String ADDRESS = "Address";
private static final String LOGO = "Logo";
private static final String COUNTRY = "country";
private static final String PHONE = "phone";
private static final String EMAIL = "email";
private static final String WEBSITE = "website";
private static final String INSTITUTION_TYPE = "institution_type";
private static final String ESTABLISHED_DATE = "establishment_date";
private static final String ADMISSION_OPEN_FROM = "admission_open_from";
private static final String ADMISSION_OPEN_TO = "admission_open_to";
private static final String LATITUDE = "latitude";
private static final String LONGITUDE = "longitude";
String createTableBookmark = "Create table if not exists `Bookmark` (" + "`name` TEXT," + "`location` TEXT," + "`logo` TEXT);";
String CREATE_SCHOOL_BOOKMARK_TABLE = "CREATE TABLE " + TABLE_NAME_BOOKMARK + "("
+ ID + " INTEGER PRIMARY KEY AUTOINCREMENT "
+ NAME + " TEXT, "
+ LOGO + " TEXT, "
+ ADDRESS + " TEXT, "
+ COUNTRY + " TEXT, "
+ PHONE + " TEXT, "
+ EMAIL + " TEXT, "
+ WEBSITE + " TEXT, "
+ INSTITUTION_TYPE + " TEXT, "
+ ESTABLISHED_DATE + " TEXT, "
+ ADMISSION_OPEN_FROM + " TEXT, "
+ ADMISSION_OPEN_TO + " TEXT, "
+ LATITUDE + " TEXT, "
+ LONGITUDE + " TEXT " + ")";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public void insertBookmarkData(Bookmarkitem bookmarkitem) {
SQLiteDatabase db = getWritableDatabase();
ContentValues content = new ContentValues();
content.put("name", bookmarkitem.name);
content.put("location", bookmarkitem.address);
content.put("logo", bookmarkitem.logo);
db.insert("Bookmark", null, content);
}
public ArrayList<Bookmarkitem> getBookmarkist() {
String sql = "select * from Bookmark ";
ArrayList<Bookmarkitem> bookmarklist = new ArrayList<Bookmarkitem>();
Cursor c = getWritableDatabase().rawQuery(sql, null);
while (c.moveToNext()) {
Bookmarkitem info = new Bookmarkitem();
info.name = c.getString(c.getColumnIndex("name"));
info.address = c.getString(c.getColumnIndex("location"));
info.logo = c.getString(c.getColumnIndex("logo"));
bookmarklist.add(info);
}
c.close();
return bookmarklist;
}
public Bookmarkitem getBookmarkData(String bookmarkName) {
String sql = "select * from Bookmark where id='" + bookmarkName + "'";
Cursor c = getWritableDatabase().rawQuery(sql, null);
while (c.moveToNext()) {
Bookmarkitem info = new Bookmarkitem();
info.name = c.getString(c.getColumnIndex("name"));
info.address = c.getString(c.getColumnIndex("location"));
info.logo = c.getString(c.getColumnIndex("logo"));
}
c.close();
Bookmarkitem info = null;
return info;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
// TODO Auto-generated method stub
sqLiteDatabase.execSQL(CREATE_SCHOOL_BOOKMARK_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int arg1, int arg2) {
// TODO Auto-generated method stub
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_BOOKMARK);
onCreate(sqLiteDatabase);
}
public void addSchoolBookmark(Bookmarkitem bookmarkitem, MenuItem menuItem) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(ID, bookmarkitem.getBookmarkID());
values.put(NAME, bookmarkitem.getName());
values.put(LOGO, bookmarkitem.getLogo());
values.put(ADDRESS, bookmarkitem.getAddress());
values.put(COUNTRY, bookmarkitem.getCountry());
values.put(PHONE, bookmarkitem.getPhone());
values.put(EMAIL, bookmarkitem.getEmail());
values.put(WEBSITE, bookmarkitem.getWebsite());
values.put(INSTITUTION_TYPE, bookmarkitem.getInstitution_type());
values.put(ESTABLISHED_DATE, bookmarkitem.getEstablishment_date());
values.put(ADMISSION_OPEN_FROM, bookmarkitem.getAdmission_open_from());
values.put(ADMISSION_OPEN_TO, bookmarkitem.getAdmission_open_to());
values.put(LATITUDE, bookmarkitem.getLatitude());
values.put(LONGITUDE, bookmarkitem.getLongitude());
//inserting row
if (db.insert(TABLE_NAME_BOOKMARK, null, values) != -1) {
//
databaseUpdatedListener.setDatabaseSuccess(bookmarkitem.getName(), menuItem);
}
/* else {
databaseUpdatedListener.setDatabaseError("Failed to Delete");
} */
// db.beginTransaction();
db.close();
}
public List<Bookmarkitem> getAllSchoolBookmark() {
SQLiteDatabase db = this.getWritableDatabase();
String selectQuery = "SELECT * FROM " + TABLE_NAME_BOOKMARK;
List<Bookmarkitem> bookmarkitems = new ArrayList<>();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Bookmarkitem bookmarkitem = new Bookmarkitem();
bookmarkitem.setBookmarkID(Integer.parseInt(cursor.getString(0)));
bookmarkitem.setName(cursor.getString(1));
bookmarkitem.setLogo(cursor.getString(2));
bookmarkitem.setAddress(cursor.getString(3));
bookmarkitem.setCountry(cursor.getString(4));
bookmarkitem.setPhone(cursor.getString(5));
bookmarkitem.setEmail(cursor.getString(6));
bookmarkitem.setWebsite(cursor.getString(7));
bookmarkitem.setInstitution_type(cursor.getString(8));
bookmarkitem.setEstablishment_date(cursor.getString(9));
bookmarkitem.setAdmission_open_from(cursor.getString(10));
bookmarkitem.setAdmission_open_to(cursor.getString(11));
bookmarkitem.setLatitude(cursor.getString(12));
bookmarkitem.setLongitude(cursor.getString(13));
bookmarkitems.add(bookmarkitem);
} while (cursor.moveToNext());
}
return bookmarkitems;
}
public boolean removeBookmarkItem(String id) {
SQLiteDatabase db = this.getWritableDatabase();
return db.delete(TABLE_NAME_BOOKMARK, ID + "=" + id, null) > 0;
}
public Bookmarkitem getBoomarkDetailByID(String id) {
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = null;
String empName = "";
try {
Bookmarkitem bookmarkitem = new Bookmarkitem();
cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME_BOOKMARK + " WHERE " + ID + "=?", new String[]{id + ""});
if (cursor.getCount() > 0) {
cursor.moveToFirst();
bookmarkitem.setBookmarkID(Integer.parseInt(cursor.getString(0)));
bookmarkitem.setName(cursor.getString(1));
bookmarkitem.setLogo(cursor.getString(2));
bookmarkitem.setAddress(cursor.getString(3));
bookmarkitem.setCountry(cursor.getString(4));
bookmarkitem.setPhone(cursor.getString(5));
bookmarkitem.setEmail(cursor.getString(6));
bookmarkitem.setWebsite(cursor.getString(7));
bookmarkitem.setInstitution_type(cursor.getString(8));
bookmarkitem.setEstablishment_date(cursor.getString(9));
bookmarkitem.setAdmission_open_from(cursor.getString(10));
bookmarkitem.setAdmission_open_to(cursor.getString(11));
bookmarkitem.setLatitude(cursor.getString(12));
bookmarkitem.setLongitude(cursor.getString(13));
}
return bookmarkitem;
} finally {
cursor.close();
}
}
}
书签适配器类
public class BookmarkAdapter extends BaseAdapter {
private Activity activity;
private LayoutInflater inflater;
private List<Bookmarkitem> ourBookmarkList;
ImageLoader imageLoader = AppController.getInstance().getImageLoader();
public BookmarkAdapter(Activity activity, List<Bookmarkitem> ourBookmarkList) {
this.activity = activity;
this.ourBookmarkList = ourBookmarkList;
}
@Override
public int getCount() {
return ourBookmarkList.size();
}
@Override
public Object getItem(int location) {
return ourBookmarkList.get(location);
}
@Override
public long getItemId(int position) {
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
if (inflater == null)
inflater = (LayoutInflater) activity
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
if (convertView == null)
convertView = inflater.inflate(R.layout.bookmark_list, null);
if (imageLoader == null)
imageLoader = AppController.getInstance().getImageLoader();
NetworkImageView bookmarkLogo = (NetworkImageView) convertView
.findViewById(R.id.bookmarklogo);
TextView bookmarkTitle = (TextView) convertView.findViewById(R.id.bookmarkTitle);
TextView bookmarkAddress = (TextView) convertView.findViewById(R.id.bookmarkAddress);
Bookmarkitem m = ourBookmarkList.get(position);
bookmarkLogo.setImageUrl(m.getLogo(), imageLoader);
bookmarkTitle.setText("Name: " + m.getName());
bookmarkAddress.setText("Address: " + String.valueOf(m.getAddress()));
return convertView;
}
}
数据库更新侦听器
public interface DatabaseUpdatedListener {
void setDatabaseSuccess(String schoolName, MenuItem item);
void setDatabaseError(String failureMessage);
}
数据库中插入时不会生成唯一 ID。如何解决这个问题？？
问题
不同的字段名称。
private static final String ID = "Id";
String sql = "select * from Bookmark where id='" + bookmarkName + "'"; // why id ? must be ID
- 纠正获取书签数据方法
演示示例 .
private static final String USER_ID= "user_id";
String sql = "select * from Bookmark where user_id='" + bookmarkName + "'";
cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME_BOOKMARK + " WHERE " + USER_ID+ "=?", new String[]{id + ""});
然后卸载旧应用程序并再次运行。
不需要显式添加ID列。请尝试如下。
首先为列名创建协定类
public static class ColumnNameEntry implements BaseColumns {
private static final String NAME = "Name";
private static final String ADDRESS = "Address";
private static final String LOGO = "Logo";
private static final String COUNTRY = "country";
private static final String PHONE = "phone";
private static final String EMAIL = "email";
private static final String WEBSITE = "website";
private static final String INSTITUTION_TYPE = "institution_type";
private static final String ESTABLISHED_DATE = "establishment_date";
private static final String ADMISSION_OPEN_FROM = "admission_open_from";
private static final String ADMISSION_OPEN_TO = "admission_open_to";
private static final String LATITUDE = "latitude";
private static final String LONGITUDE = "longitude";
}
实现BaseColumns类将自动提供列_id，该列将自动递增
现在创建如下表
String CREATE_SCHOOL_BOOKMARK_TABLE = "CREATE TABLE " + TABLE_NAME_BOOKMARK + "("
+ ColumnNameEntry._ID + " INTEGER PRIMARY KEY "....
请参阅本教程