我已经完成了下面一些人所说的，但现在我得到了错误代码：2067(SQLITE_CONSTRAINT_UNIQUE)原因：由于违反约束而中止。(UNIQUE约束失败：employees.Employee_number(代码2067))
我在的EMployeeDBHandler上收到了这个错误
database.insertWithOnConflict(TABLE_EMPLOYEE, null, values, 0);
我正在解析web服务响应并将其存储到SQLite数据库中。一切都很好地加载到数据库中。我遇到的问题是，我希望能够删除与另一行相同的一行。我制作了uniqueID，这样它就可以存储到数据库中。
我如何才能删除其中一条与另一条相同的记录？然后把仍然存在的那个删除到另一个列表中？我正在与一个employeeNumber进行网络通话，并希望将与该employeeNumber绑定的人放在自己的列表视图中，而将响应中的其他内容放在另一个列表中。
public class MainActivity extends AppCompatActivity {
private ListView mTopList, mDirectReportList;
private ProgressBar mProgressBar;
private ArrayList<Employee> mEmployees = new ArrayList<>();
private BottomListViewAdapter mBottomListViewAdapter;
EmployeeDBHandler dbHandler;
SQLiteDatabase db;
SimpleCursorAdapter simpleCursorAdapter;
private String startingEmployeeID = startingEmployeeNumber;
private String table = "employees";
private static final String KEY_ID = "Employee_number";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHandler = new EmployeeDBHandler(getApplicationContext());
mProgressBar = (ProgressBar) findViewById(R.id.progressBar);
mProgressBar.setVisibility(View.VISIBLE);
// mBottomListViewAdapter = new BottomListViewAdapter(this, mEmployees);
// directReportListView.setAdapter(mBottomListViewAdapter);
getXMLData();
displayBottomList();
// displayTopList(startingEmployeeID);
//displayTopList();
//GUI for seeing android SQLite Database in Chrome Dev Tools
Stetho.InitializerBuilder inBuilder = Stetho.newInitializerBuilder(this);
inBuilder.enableWebKitInspector(Stetho.defaultInspectorModulesProvider(this));
Stetho.Initializer in = inBuilder.build();
Stetho.initialize(in);
}
public void getXMLData() {
OkHttpClient client = getUnsafeOkHttpClient();
Request request = new Request.Builder()
.url(getString(R.string.API_FULL_URL))
.build();
client.newCall(request).enqueue(new Callback() {
@Override
public void onFailure(Call call, IOException e) {
e.printStackTrace();
}
@Override
public void onResponse(Call call, final Response response) throws IOException {
final String responseData = response.body().string();
final InputStream stream = new ByteArrayInputStream(responseData.getBytes());
final XMLPullParserHandler parserHandler = new XMLPullParserHandler();
final ArrayList<Employee> employees = (ArrayList<Employee>) parserHandler.parse(stream);
for (Employee e : employees) {
dbHandler.addEmployee(e);
}
/*mEmployees.clear();
mEmployees.addAll(employees);*/
//tell adapter on the UI thread its data changed
runOnUiThread(new Runnable() {
@Override
public void run() {
/*mBottomListViewAdapter.notifyDataSetChanged();
directReportListView.setVisibility(View.VISIBLE);*/
mProgressBar.setVisibility(View.GONE);
}
});
}
});
}
public void displayBottomList() {
EmployeeDBHandler handler = new EmployeeDBHandler(this);
SQLiteDatabase db = handler.getWritableDatabase();
db.rawQuery("DELETE FROM " + table + " WHERE " + "Employee_number" + " EQUALS " + "Employee_number" + ");", null);
Cursor mBottomListCursor = db.rawQuery("SELECT * FROM employees", null);
ListView mBottomListView = (ListView) findViewById(R.id.mDirectReportList);
BottomListViewAdapter bottomAdapter = new BottomListViewAdapter(this, mBottomListCursor);
mBottomListView.setAdapter(bottomAdapter);
}
}
现在，如果我不尝试执行删除查询，displayBottomList方法可以工作，但问题是startingEmpoyee有两条记录，我删除了一条，然后删除了数据库中的1，并将其移动到自己的listView中。
这是我在displayBottomList 中删除查询时收到的错误
错误代码：1(SQLITE_Error)原因：SQL(查询)错误或缺少数据库。(接近"EQUALS"：语法错误(代码1)：，编译时：DELETE FROM employees WHERE Employee_number EQUALS Employee_number)；)
是否可以删除重复项并删除另一行以填充新的列表视图？
public class EmployeeDBHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "OneTeam";
private static final String TABLE_EMPLOYEE = "employees";
//Employee table columns names
private static final String KEY_ID = "Employee_number";
private static final String KEY_FIRST_NAME = "First_name";
private static final String KEY_LAST_NAME = "Last_name";
private static final String KEY_PHONE_NUMBER_MOBILE = "Phone_mobile";
private static final String KEY_PHONE_NUMBER_OFFICE = "Phone_office";
private static final String KEY_PAYROLL_TITLE = "Payroll_title";
private static final String KEY_HAS_DIRECT_REPORTS = "Has_direct_reports";
private static final String KEY_EMAIL = "Email";
private static final String KEY_COST_CENTER = "Cost_center_id";
private static final String KEY_THUMBNAIL_IMAGE = "ThumbnailData";
private final static String DB_CLIENTS_ID = "_id";
public EmployeeDBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_EMPLOYEE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_EMPLOYEE + "("
+ DB_CLIENTS_ID + " INTEGER PRIMARY KEY,"
+ KEY_ID + " TEXT,"
+ KEY_FIRST_NAME + " TEXT,"
+ KEY_LAST_NAME + " TEXT,"
+ KEY_PHONE_NUMBER_MOBILE + " TEXT,"
+ KEY_PHONE_NUMBER_OFFICE + " TEXT,"
+ KEY_PAYROLL_TITLE + " TEXT,"
+ KEY_HAS_DIRECT_REPORTS + " TEXT,"
+ KEY_EMAIL + " TEXT,"
+ KEY_THUMBNAIL_IMAGE + " TEXT,"
+ KEY_COST_CENTER + " TEXT"
+ "UNIQUE(" + KEY_ID + ")"
+ ")";
db.execSQL(CREATE_EMPLOYEE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//drop old table if existence
db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEE);
//Create table again
onCreate(db);
}
//Add new employee
public boolean addEmployee(Employee employee) {
SQLiteDatabase database = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ID, employee.getEmployee_number());
values.put(KEY_FIRST_NAME, employee.getFirst_name());
values.put(KEY_LAST_NAME, employee.getLast_name());
values.put(KEY_PHONE_NUMBER_MOBILE, employee.getPhone_mobile());
values.put(KEY_PHONE_NUMBER_OFFICE, employee.getPhone_office());
values.put(KEY_HAS_DIRECT_REPORTS, employee.getHas_direct_reports());
values.put(KEY_EMAIL, employee.getEmail());
values.put(KEY_COST_CENTER, employee.getCost_center_id());
values.put(KEY_PAYROLL_TITLE, employee.getPayroll_title());
values.put(KEY_THUMBNAIL_IMAGE, employee.getThumbnailData());
//Inserting Row
database.insertWithOnConflict(TABLE_EMPLOYEE, null, values, 0);
database.close();
return true;
}
//Get single employee
public Employee getEmployee(int employeeNumber) {
SQLiteDatabase database = this.getReadableDatabase();
Employee employee = null;
Cursor cursor = database.query(TABLE_EMPLOYEE, new String[] {
KEY_ID, KEY_FIRST_NAME, KEY_LAST_NAME, KEY_PHONE_NUMBER_OFFICE, KEY_PHONE_NUMBER_MOBILE,
KEY_HAS_DIRECT_REPORTS, KEY_EMAIL, KEY_COST_CENTER, KEY_PAYROLL_TITLE, KEY_THUMBNAIL_IMAGE}, KEY_ID + "=?",
new String[]{ String.valueOf(employeeNumber)}, null, null, null, null);
if(cursor != null) {
if(cursor.moveToFirst()) {
employee = new Employee(cursor.getString(0),
cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8),
cursor.getString(9), cursor.getString(10), cursor.getString(11), cursor.getString(12),
cursor.getString(12), cursor.getString(14), cursor.getString(15), cursor.getString(16),
cursor.getString(17), cursor.getString(18), cursor.getString(19), cursor.getString(20),
cursor.getString(21), cursor.getString(22), cursor.getString(23), cursor.getString(24),
cursor.getString(24), cursor.getString(25), cursor.getString(26));
}
}
cursor.close();
database.close();
return employee;
}
//Get All Employees
public ArrayList<Employee> getAllEmployees() {
ArrayList<Employee> employeeList = new ArrayList<>();
//Select all query
String selectQuery = "SELECT * FROM " + TABLE_EMPLOYEE;
SQLiteDatabase database = this.getWritableDatabase();
Cursor cursor = database.rawQuery(selectQuery, null);
//looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Employee employee = new Employee();
employee.setEmployee_number(cursor.getString(cursor.getColumnIndex(KEY_ID)));
employee.setFirst_name(cursor.getString(cursor.getColumnIndex(KEY_FIRST_NAME)));
employee.setLast_name(cursor.getString(cursor.getColumnIndex(KEY_LAST_NAME)));
employee.setPhone_office(cursor.getString(cursor.getColumnIndex(KEY_PHONE_NUMBER_MOBILE)));
employee.setPhone_mobile(cursor.getString(cursor.getColumnIndex(KEY_PHONE_NUMBER_OFFICE)));
employee.setHas_direct_reports(cursor.getString(cursor.getColumnIndex(KEY_HAS_DIRECT_REPORTS)));
employee.setEmail(cursor.getString(cursor.getColumnIndex(KEY_EMAIL)));
employee.setCost_center_id(cursor.getString(cursor.getColumnIndex(KEY_COST_CENTER)));
employee.setPayroll_title(cursor.getString(cursor.getColumnIndex(KEY_PAYROLL_TITLE)));
employee.setThumbnailData(cursor.getString(cursor.getColumnIndex(KEY_THUMBNAIL_IMAGE)));
} while (cursor.moveToNext());
}
//return employees list
return employeeList;
}
//Get Employee Count
public int getEmployeeCount() {
String countQuery = "SELECT * FROM " + TABLE_EMPLOYEE;
SQLiteDatabase database = this.getReadableDatabase();
Cursor cursor = database.rawQuery(countQuery, null);
cursor.close();
return cursor.getCount();
}
//Updating single employee
public int updateEmployee(Employee employee){
SQLiteDatabase database = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FIRST_NAME, employee.getFirst_name());
values.put(KEY_LAST_NAME, employee.getLast_name());
values.put(KEY_PHONE_NUMBER_MOBILE, employee.getPhone_mobile());
values.put(KEY_PHONE_NUMBER_OFFICE, employee.getPhone_office());
values.put(KEY_HAS_DIRECT_REPORTS, employee.getHas_direct_reports());
values.put(KEY_EMAIL, employee.getEmail());
values.put(KEY_COST_CENTER, employee.getCost_center_id());
values.put(KEY_PAYROLL_TITLE, employee.getPayroll_title());
values.put(KEY_THUMBNAIL_IMAGE, employee.getThumbnailData());
return database.update(TABLE_EMPLOYEE, values, KEY_ID + " = ?",
new String[] {String.valueOf(employee.getEmployee_number())});
}
//Delete single employee
public void deleteEmployee(Employee employee) {
SQLiteDatabase database = this.getWritableDatabase();
database.delete(TABLE_EMPLOYEE, KEY_ID + " = ?",
new String[] {String.valueOf(employee.getEmployee_number())});
database.close();
}
//delete row
public void delete(int id) {
SQLiteDatabase db = this.getWritableDatabase();
if (db == null) {
return;
}
db.delete(TABLE_EMPLOYEE, "Employee+number = ?", new String[] { String.valueOf(id) });
db.close();
}
}
首先，如果您不想在表中插入重复的条目，请像这样向表添加unique约束。
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_EMPLOYEE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_EMPLOYEE + "("
+ DB_CLIENTS_ID + " INTEGER PRIMARY KEY,"
+ KEY_ID + " TEXT,"
+ KEY_FIRST_NAME + " TEXT,"
+ KEY_LAST_NAME + " TEXT,"
+ KEY_PHONE_NUMBER_MOBILE + " TEXT,"
+ KEY_PHONE_NUMBER_OFFICE + " TEXT,"
+ KEY_PAYROLL_TITLE + " TEXT,"
+ KEY_HAS_DIRECT_REPORTS + " TEXT,"
+ KEY_EMAIL + " TEXT,"
+ KEY_THUMBNAIL_IMAGE + " TEXT,"
+ KEY_COST_CENTER + " TEXT" +
+ "UNIQUE("+KEY_ID+")"+
")";
db.execSQL(CREATE_EMPLOYEE_TABLE);
}
然后在插入时使用insertWithOnConflict而不是insert。
这将忽略要插入表中的重复条目。
其次，对于您当前的实现，存在以下问题：
- 您应该在
onResponse中调用displayBottomList()方法，因为响应会有延迟，并且您应该只在响应到来后填充 - 查询"DELETE FROM"+table+"WHERE"+"Employee_number"+"EQUALS"+"Employee_number"+")；"将删除该表的所有记录。我不知道你为什么要那样做
您的代码中有一些无关的问题。首先，您在UI线程上进行数据库查询，这是一个很大的问题。尝试卸载到后台线程。此外，一旦web服务调用完成(由其他答案之一指示)，您的UI数据可能不会得到更新。这可以通过多种方式来处理。现在，您可以通过多种方式处理重复行的问题。
-
插入时忽略重复项。前面的一个答案涉及到了这一点。通过这个链接-https://sqlite.org/lang_conflict.html基本上，您可以在表创建命令或INSERT命令中使用ON CONFLICT子句。不管怎样，它都会起作用。由于看起来您想要忽略重复项，请使用ignore解析算法。
-
另一种不用实际删除重复项就可以使用的方法是在查询中使用GROUP BY子句。这对您来说会更容易，因为您不必对当前的模式和INSERT命令进行任何更改。只需在查询中使用GROUP BY。查看此链接了解如何使用GROUP BY-https://www.sqlite.org/lang_select.html
您必须对您的唯一密钥(在您的情况下为员工id)进行GROUP BY。