我知道有很多关于这个问题的线程，但没有我自己的问题。我在数据库中连接了一个微调器，用于显示特定表的外键。插入时出现错误。
02-18 11:44:34.500: E/SQLiteDatabase(20811): Error inserting ConsumerName=android.database.sqlite.SQLiteCursor@4144fa58 kWh=801.0 _id=65324 Date=2013 -2-18 Previous=98 Current=899
02-18 11:44:34.500: E/SQLiteDatabase(20811): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
public class ElectricMeterReader extends Activity {
private long rowID;
private EditText meterNoEt;
private EditText currentEt;
private EditText previousEt;
private EditText kWhEt;
private TextView dateTv;
private Spinner spinner;
private int mSpinnerSpeciesId;
private ElectricMeterReader mContext;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.electric_meter_reader);
Bundle extras = getIntent().getExtras();
// rowID = extras.getLong(ConsumerList.ROW_ID);
dateTv = (TextView) findViewById(R.id.dateEmr);
meterNoEt = (EditText) findViewById(R.id.meterNumberEmr);
currentEt = (EditText) findViewById(R.id.currentReadingEmr);
previousEt = (EditText) findViewById(R.id.passReadingEmr);
kWhEt = (EditText) findViewById(R.id.kwhEmr);
spinner = (Spinner) findViewById(R.id.spinner);
// Loading spinner data from database
SQLiteDatabase db = new DatabaseOpenHelper(this).getWritableDatabase();
// Spinner Drop down elements
// List<String> lables = db.getAllLabels();
Cursor c = db.rawQuery(
"SELECT AccountID AS _id, ConsumerName FROM Consumers", null);
// Creating adapter for spinner
startManagingCursor(c);
String[] from = new String[] { "_id" };
int[] to = new int[] { android.R.id.text1 };
SimpleCursorAdapter dataAdapter = new SimpleCursorAdapter(this,
android.R.layout.simple_spinner_item, c, from, to);
// Drop down layout style - list view with radio button
dataAdapter
.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// attaching data adapter to spinner
spinner.setAdapter(dataAdapter);
// set spinner listener to display the selected item id
mContext = this;
spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
public void onItemSelected(AdapterView<?> parent, View view,
int position, long id) {
// On selecting a spinner item
//String label = parent.getItemAtPosition(position).toString();
Cursor c = (Cursor) parent.getItemAtPosition(position);
mSpinnerSpeciesId = c.getInt(c
.getColumnIndexOrThrow("ConsumerName"));
// Showing selected spinner item
/* Toast.makeText(parent.getContext(), "You selected: " + label,
Toast.LENGTH_LONG).show();*/
}
public void onNothingSelected(AdapterView<?> parent) {
// TODO Auto-generated method stub
}
});
final Calendar ca = Calendar.getInstance();
int yy = ca.get(Calendar.YEAR);
int mm = ca.get(Calendar.MONTH);
int dd = ca.get(Calendar.DAY_OF_MONTH);
dateTv.setText(new StringBuilder()
// Month is 0 based, just add 1
.append(yy).append(" ").append("-").append(mm + 1).append("-")
.append(dd));
if (extras != null) {
rowID = extras.getLong("_id");
// meterNoEt.setText(extras.getString("MeterNumber"));
// set current date into textview
// Spinner click listener
currentEt.setText(extras.getString("Current"));
previousEt.setText(extras.getString("Previous"));
kWhEt.setText(extras.getString("kWh"));
// dateTv.setText(extras.getString("Date"));
}
Button calButton = (Button) findViewById(R.id.calculateDef);
calButton.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
// TODO Auto-generated method stub
float result = Float.parseFloat(currentEt.getText().toString())
- Float.parseFloat(previousEt.getText().toString());
kWhEt.setText(Float.toString(result));
}
});
Button saveButton = (Button) findViewById(R.id.addConsumption);
saveButton.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
if (kWhEt.getText().length() != 0) {
AsyncTask<Object, Object, Object> saveContactTask = new AsyncTask<Object, Object, Object>() {
@Override
protected Object doInBackground(Object... params) {
saveContact();
return null;
}
@Override
protected void onPostExecute(Object result) {
/*
* Intent addContact = new Intent(
* ElectricMeterReader.this,
* ConsumerReadList.class);
* startActivity(addContact);
*/
finish();
}
};
saveContactTask.execute((Object[]) null);
}
else {
AlertDialog.Builder alert = new AlertDialog.Builder(
ElectricMeterReader.this);
alert.setTitle(R.string.errorTitle);
alert.setMessage(R.string.errorMessage);
alert.setPositiveButton(R.string.errorButton, null);
alert.show();
}
}
});
}
public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.consumer_menu, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case R.id.addConsumerItem:
Intent addContact = new Intent(ElectricMeterReader.this,
AddEditConsumer.class);
startActivity(addContact);
return true;
case R.id.consumerInfo:
Intent electricMeterReader = new Intent(ElectricMeterReader.this,
ViewConsumer.class);
startActivity(electricMeterReader);
return true;
default:
return super.onOptionsItemSelected(item);
}
}
private void saveContact() {
DatabaseConnector dbConnector = new DatabaseConnector(this);
if (getIntent().getExtras() == null) {
dbConnector.insertConsumption(meterNoEt.getText().toString(),
currentEt.getText().toString(), previousEt.getText()
.toString(), kWhEt.getText().toString(), dateTv
.getText().toString(),
spinner.getItemAtPosition(mSpinnerSpeciesId).toString()
);
} else {
dbConnector.updateConsumption(rowID,
currentEt.getText().toString(), previousEt.getText()
.toString(), kWhEt.getText().toString(), dateTv
.getText().toString(),
spinner.getItemAtPosition(mSpinnerSpeciesId).toString());
}
}
}
将方法编码插入我的数据库连接器
public void insertConsumption(String meter_number, String current,
String previous, String kWh, String date, String consumer_name) {
ContentValues newCons = new ContentValues();
newCons.put("_id", meter_number);
newCons.put("Current", current);
newCons.put("Previous", previous);
newCons.put("kWh", kWh);
newCons.put("Date", date);
newCons.put("ConsumerName", consumer_name);
open();
database.insert("Consumptions", null, newCons);
close();
}
我的数据库Openhelper
public class DatabaseOpenHelper extends SQLiteOpenHelper {
static final String dbName = "ElectricMeterDB";
static final String tableLabels = "labels";
static final String consumptionsTable = "Consumptions";
static final String colMeterNumber = "_id";
static final String colCurrent = "Current";
static final String colPrevious = "Previous";
static final String colkWh = "kWh";
static final String colDate = "Date";
static final String colConsumer = "ConsumerName";
static final String consumersTable = "Consumers";
static final String colAccountID = "AccountID";
static final String colName = "ConsumerName";
static final String colAddress = "Address";
public static final String viewReads = "ViewReading";
public DatabaseOpenHelper(Context context) {
super(context, dbName, null, 2);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + consumersTable + " (" + colAccountID
+ " INTEGER PRIMARY KEY NOT NULL," + colName + " TEXT NOT NULL, "
+ colAddress + " TEXT NOT NULL);");
db.execSQL("CREATE TABLE " + consumptionsTable + " (" + colMeterNumber
+ " INTEGER PRIMARY KEY NOT NULL," + colCurrent + " INTEGER, "
+ colPrevious + " INTEGER, " + colkWh + " INTEGER, " + colDate
+ " TEXT, " + colConsumer + " INTEGER NOT NULL, FOREIGN KEY ("
+ colConsumer + ") REFERENCES " + consumersTable + " ("
+ colAccountID + "));");
db.execSQL("CREATE TRIGGER fk_cmpcons_consid" + " BEFORE INSERT "
+ " ON " + consumptionsTable + " FOR EACH ROW BEGIN"
+ " SELECT CASE WHEN ((SELECT " + colAccountID + " FROM "
+ consumersTable + " WHERE " + colAccountID + " =new."
+ colConsumer + ") IS NULL)"
+ "THEN RAISE (ABORT, 'Foreign Key Violation') END;" + "END;");
db.execSQL("CREATE VIEW " + viewReads + " AS SELECT "
+ consumptionsTable + " . " + colMeterNumber + " AS _id, "
+ consumptionsTable + " . " + colCurrent + " ,"
+ consumptionsTable + " . " + colPrevious + " ,"
+ consumptionsTable + " . " + colkWh + ", " + consumptionsTable
+ " . " + colDate + "," + consumersTable + "." + colConsumer
+ " FROM " + consumptionsTable + " JOIN " + consumersTable
+ " ON " + consumptionsTable + " . " + colConsumer + " = "
+ consumersTable + "." + colAccountID);
// Inserts pre-defined consumers
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS" + consumptionsTable);
db.execSQL("DROP TABLE IF EXISTS" + consumersTable);
db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger");
db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger22");
db.execSQL("DROP TRIGGER IF EXISTS fk_cmpcons_consid");
db.execSQL("DROP VIEW IF EXISTS" + viewReads);
onCreate(db);
}
public void insertLabel(String label) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(colName, label);
// Inserting Row
db.insert(tableLabels, null, values);
db.close(); // Closing database connection
}
/**
* Getting all labels returns list of labels
* */
public List<String> getAllLabels() {
List<String> labels = new ArrayList<String>();
// Select All Query
String selectQuery = "SELECT * FROM " + consumersTable;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
labels.add(cursor.getString(1));
} while (cursor.moveToNext());
}
// closing connection
cursor.close();
db.close();
// returning lables
return labels;
}
}
我被这个问题卡住了。请帮帮我。谢谢你将来的回复。
log.d
02-18 13:49:59.870: D/saveContact(30881): _id: 36698521 ConsumerName: android.database.sqlite.SQLiteCursor@4144f700
我的阵列适配器；
public void arrayAdapterList() {
final String[] from = new String[] { DatabaseOpenHelper.colAccountID };
// int[] to = new int[] { android.R.id.text1 };
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, from);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner = (Spinner) findViewById(R.id.spinner);
// attaching data adapter to spinner
spinner.setAdapter(adapter);
spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
public void onItemSelected(AdapterView<?> parent, View view,
int position, long id) {
// On selecting a spinner item
// String col= c.getString(c.getColumnIndex("_id"));
int item = spinner.getSelectedItemPosition();
Toast.makeText(getBaseContext(), "You Selected: " + from[item],
Toast.LENGTH_SHORT).show();
}
public void onNothingSelected(AdapterView<?> parent) {
// TODO Auto-generated method stub
}
});
}
对于NOT NULL字段（在您的案例中为ConsumerName），您插入的某些值可能为NULL，或者PK（在您案例中为_id）已经存在。错误代码19表示在操作（INSERT等）过程中违反了表约束（NOT NULL、UNIQUE等）
android.database.sqlite.SQLiteCursor@4144fa58是分配给ConsumerName的垃圾值。所以，请做一些变通办法，从光标中为ConsumerName获取正确的FK。
SimpleCursorAdapter有问题。所以请看一下。如果你在微调器中得到了正确的值，那么请从像spinner.getSelectedItem().toString();而不是spinner.getItemAtPosition(mSpinnerSpeciesId).toString()这样的微调器中获得选定的值。
希望这对你有帮助。
约束失败通常表示您在创建表时执行了将null值传递到声明为非null的列之类的操作。
因此，在创建表时删除not null并放置null。
我得到了同样的错误，并通过在代码中设置ID NULL来解决它。由于ID是PK SqlLite自动递增其值，因此不需要对其进行设置。
public void insertConsumption(String meter_number, String current,
String previous, String kWh, String date, String consumer_name) {
ContentValues newCons = new ContentValues();
//newCons.put("_id", meter_number);
newCons.put("Current", current);
newCons.put("Previous", previous);
newCons.put("kWh", kWh);
newCons.put("Date", date);
newCons.put("ConsumerName", consumer_name);
open();
database.insert("Consumptions", null, newCons);
close();
}
您将列"colAccountID"声明为PRIMARY KEY，因此当您一次又一次尝试插入相同的id时，SQLite数据库会抛出错误。