文章/答案/技术大牛

发布

社区首页 >问答首页 >sqlite存储第一条记录，但不在此之后。

问sqlite存储第一条记录，但不在此之后。
EN

Stack Overflow用户

提问于 2014-07-18 20:05:24

回答 3查看 99关注 0票数 0

我已经创建了一个数据库并将其添加到资产文件夹中。表名orders有三列id、orderItems和orderDate。在orderItems中，我将数组列表转换为字符串，然后保存成功存储的第一条记录，但当我试图存储第二条记录时，它会显示一个错误。

下面是函数，我使用它来添加orders Table中的记录。

void addOrders(Order ord) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues orderValues = new ContentValues();
    orderValues.put(ORDER_ID, ord.get_orderId());
    orderValues.put(ORDER_ITEMS, ord.get_orderItems()); 
    orderValues.put(ORDER_DATE, ord.get_orderDate()); 

    // Inserting Row
    db.insert(TABLE_ORDERS_NEW, null, orderValues);
    db.close(); // Closing database connection

}

下面是函数，用于将arraylist (selectedProducts)转换为string并保存到表中。

public void saveOrders(){

     Gson gson = new Gson();
     String arrayList = gson.toJson(selectedProducts);

     db.addOrders(new Order(arrayList, formattedDate));

}

DatabaseHandler.class

公共类DatabaseHandler扩展SQLiteAssetHelper {

// All Static variables

private static String TAG = DatabaseHandler.class.getName();

// Database Path
 private static String DB_PATH = "/data/data/com.WaqasAsgharBhalli.za_traders/databases/";
  private static String DB_NAME = "myDatabase.sqlite";
  private SQLiteDatabase myDataBase = null; 
  private final Context myContext;

// Database Version
private static final int DATABASE_VERSION = 1;                                   

// Contacts table name
private static final String TABLE_CONTACTS_NEW = "customer";
// Medicine table name
private static final String TABLE_PRODUCT_NEW = "products";
// Medicine table name
private static final String TABLE_USERLOGIN_NEW = "userlogin";
// ORDERS table name
private static final String TABLE_ORDERS_NEW = "orders";                                                 

// Contacts Table Columns name
private static final String CUSTOMER_ID = "id";
private static final String CUSTOMER_CODE = "code";
private static final String CUSTOMER_NAME = "name";
private static final String CUSTOMER_ADDRESS = "address";
private static final String CUSTOMER_LICENCECENO = "licenceNo";

// Product Table Columns name
private static final String PRODUCT_ID = "id";
private static final String PRODUCT_CODE = "pcode";
private static final String PRODUCT_NAME = "pname";
private static final String PRODUCT_PRICE = "price";
private static final String PRODUCT_QUANTITY = "quantity";

// UserLogin Table Columns name
private static final String USERLOGIN_ID = "id";
private static final String USERLOGIN_USERCODE = "usercode";
private static final String USERLOGIN_NAME = "uname";
private static final String USERLOGIN_PASSWORD = "password";

// Order Table Columns name
private static final String ORDER_ID = "id";
private static final String ORDER_ITEMS = "orderItems";
private static final String ORDER_DATE = "orderDate";


// CREATING CUSTOMER CONTACT TABLE
private static final  String CREATE_CONTACTS_CUSTOMER = 
        "CREATE TABLE " + TABLE_CONTACTS_NEW + "("
        + CUSTOMER_ID + " INTEGER PRIMARY KEY," + CUSTOMER_CODE + " TEXT,"
        + CUSTOMER_NAME + " TEXT," + CUSTOMER_LICENCECENO + " TEXT," 
        + CUSTOMER_ADDRESS + " TEXT"+");";

// CREATING PRODUCT TABLE
private static final  String CREATE_CONTACTS_PRODUCTS = 
        "CREATE TABLE " + TABLE_PRODUCT_NEW + "("
        + PRODUCT_ID + " INTEGER PRIMARY KEY,"+ PRODUCT_CODE + " TEXT," 
        + PRODUCT_NAME + " TEXT,"  + PRODUCT_PRICE + " DOUBLE," 
        + PRODUCT_QUANTITY + " INTEGER" +");";

// CREATING USERLOGIN TABLE
private static final  String CREATE_CONTACTS_USERLOGIN = 
        "CREATE TABLE " + TABLE_USERLOGIN_NEW + "("
        + USERLOGIN_ID + " INTEGER PRIMARY KEY," + USERLOGIN_USERCODE + " TEXT,"
        + USERLOGIN_NAME + " TEXT,"  + USERLOGIN_PASSWORD + " TEXT"+");";

// CREATING ORDERS TABLE
private static final  String CREATE_CONTACTS_ORDERS = 
        "CREATE TABLE " + TABLE_ORDERS_NEW + "("
        + ORDER_ID + " INTEGER PRIMARY KEY," 
        + ORDER_ITEMS + " TEXT,"  + ORDER_DATE + " DATETIME"+");                                             

public DatabaseHandler (Context context) throws IOException  {
    super(context,DB_NAME,null,1);
    this.myContext=context;
    boolean dbexist = checkDatabase();
    if(dbexist)
    {
        //System.out.println("Database exists");
        openDatabase(); 
    }
    else
    {
        System.out.println("Database doesn't exist");
    createDatabase();
    }

}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * */

public void createDatabase() throws IOException{
    boolean dbexist = checkDatabase();
    if(dbexist)
    {
        //System.out.println(" Database exists.");
    }
    else{
        this.getReadableDatabase();
    try{
            copyDatabase();
        }
        catch(IOException e){
            throw new Error("Error copying database");
        }
    }
}

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
public boolean checkDatabase() {
    //SQLiteDatabase checkdb = null;
    boolean checkdb = false;
    try{
        String myPath = DB_PATH + DB_NAME;
        File dbfile = new File(myPath);
        //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
        checkdb = dbfile.exists();
    }
    catch(SQLiteException e){
        System.out.println("Database doesn't exist");
    }

    return checkdb;
}

private void copyDatabase() throws IOException {

    //Open your local db as the input stream
    InputStream myinput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outfilename = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myoutput = new FileOutputStream("/data/data/com.WaqasAsgharBhalli.za_traders/databases/myDatabase.sqlite");

    // transfer byte to inputfile to outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myinput.read(buffer))>0)
    {
        myoutput.write(buffer,0,length);
    }

    //Close the streams
    myoutput.flush();
    myoutput.close();
    myinput.close();

}

public void openDatabase() throws SQLException
{
    //Open the database
    String mypath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);

}

@Override
public synchronized void close(){
    if(myDataBase != null){
        myDataBase.close();
    }
    super.close();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCT_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERLOGIN_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS_NEW);

    // Create tables again
    onCreate(db);
}

当我试图输入第二条记录时，我所得到的错误。

07-19 00:43:03.198: I/SQLiteAssetHelper(1741): successfully opened database myDatabase.sqlite
07-19 00:43:03.386: E/SQLiteDatabase(1741): Error inserting id=0 orderItems=[{"_product_name":"BETNESOL TAB","_product_Code":"0343","_price":21.6,"_id":6,"_quantity":29},{"_product_name":"Q.PIN TAB 100MG","_product_Code":"2132","_price":221.0,"_id":10,"_quantity":11}] orderDate=19-Jul-2014
07-19 00:43:03.386: E/SQLiteDatabase(1741): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1591)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.DatabaseHandler.addOrders(DatabaseHandler.java:584)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.SelectedMedicineList.saveOrders(SelectedMedicineList.java:139)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.SelectedMedicineList.onOptionsItemSelected(SelectedMedicineList.java:117)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.app.Activity.onMenuItemSelected(Activity.java:2502)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:950)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuBuilder.dispatchMenuItemSelected(MenuBuilder.java:735)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:149)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:874)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.ListMenuPresenter.onItemClick(ListMenuPresenter.java:163)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AdapterView.performItemClick(AdapterView.java:292)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView.performItemClick(AbsListView.java:1058)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView$PerformClick.run(AbsListView.java:2514)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView$1.run(AbsListView.java:3168)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Handler.handleCallback(Handler.java:605)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Handler.dispatchMessage(Handler.java:92)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Looper.loop(Looper.java:137)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.app.ActivityThread.main(ActivityThread.java:4340)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at java.lang.reflect.Method.invokeNative(Native Method)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at java.lang.reflect.Method.invoke(Method.java:511)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at dalvik.system.NativeStart.main(Native Method)

java

android

sqlite

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-07-18 20:13:11

在传递给ContentValues的db.insert中显式设置ID，而不是让数据库自动为您生成ID。当ID设置为自动增量时，数据库将生成ID，除非您显式地提供ID，在这种情况下，数据库将尝试使用您指定的ID。

从代码中删除这一行：

orderValues.put(ORDER_ID, ord.get_orderId());

票数 1

Stack Overflow用户

发布于 2014-07-18 20:19:28

您有一个约束错误。这意味着您必须检查数据库的设置。

例如，如果尝试使用相同的值插入主键两次，则会出现此错误。

您能否张贴您的db助手，看看您的输入是否符合您的db设置。

可以是唯一值、主键值等。

票数 0

Stack Overflow用户

发布于 2014-07-18 20:27:13

如果将id设置为autoincrement，则不能将其具体设置(在本例中为ord.get_orderId() )。

您应该删除该orderValues.put(ORDER_ID, ord.get_orderId());行。或者，在表中创建另一个字段来存储该ord.get_orderId()。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/24832956

复制

相似问题

问sqlite存储第一条记录，但不在此之后。
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sqlite存储第一条记录，但不在此之后。EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sqlite存储第一条记录，但不在此之后。
EN