Search code examples
androidsqlitearraylistauto-increment

how to make the first feild(id) in a table as autoincreament when the values are passed as an array list and stored using for loop?


I am creating an android app where i need to stor a very large chunck of data in the database.

As m storing the values using a for loop(because an arraylist is passed from my activity) so all the values are stored in order.

the problem is i want the first value to be as the id which will be auto increment in the database. if i give the id in the create statement as autoincreament this database code does not work and my table is empty. can anyone please tell me how do i go about and make the first feild in my database table as auto increament???? here is the insertAllValues function code

public void insertAllValues(ArrayList<String> data) {
    openOrCreateDatabase();
    createAllValuesTable();
    String insertString = "insert into " + AllValuesTable
            + " Values(";
    String inString = "";
    for (int i =0; i < data.size(); i++) {

        inString = inString + "\"" + data.get(i) + "\",";

    }
    inString = inString.substring(0, inString.length() - 1);
    insertString = insertString + inString + ");";
    System.out.println("** insert value ** " + insertString);
    db.execSQL(insertString);

    closeDatabase();
}

create statement

db.execSQL("CREATE TABLE IF NOT EXISTS "
            + AllValuesTable
            + "(appln_date VARCHAR,appln_no VARCHAR,rupees VARCHAR,tenure VARCHAR,asset VARCHAR," +
            "app_salutation VARCHAR,app_fname VARCHAR, app_mname VARCHAR, app_lname VARCHAR,app_door VARCHAR,app_street VARCHAR,app_building VARCHAR,app_area VARCHAR,app_post VARCHAR,app_tehsil VARCHAR,app_district VARCHAR,app_state VARCHAR,app_landmark VARCHAR,app_pincode VARCHAR,app_landline VARCHAR, app_mobileno VARCHAR,app_incorporation VARCHAR,app_date VARCHAR,app_constitution VARCHAR," +
            "cons_Salutation VARCHAR,cons_Signatoryname VARCHAR,cons_Addrs1 VARCHAR,cons_Tehsiltaluka VARCHAR,cons_District1 VARCHAR,cons_State1 VARCHAR,cons_Pincode VARCHAR,cons_Landmark1 VARCHAR,cons_Mobile1 VARCHAR,cons_Stdcode VARCHAR,cons_Landline1 VARCHAR,app_occupation VARCHAR,app_education VARCHAR,app_ccity VARCHAR,app_cyear VARCHAR,app_maritalstatus VARCHAR,app_gender VARCHAR,app_father_spouse VARCHAR,app_fsfname VARCHAR,app_fslname VARCHAR," +
            "app_idproof1 VARCHAR,app_idetail1 VARCHAR,app_idproof2 VARCHAR,app_idetail2 VARCHAR,app_addproof1 VARCHAR,app_addetail1 VARCHAR,app_addproof2 VARCHAR,app_addetail2 VARCHAR,app_email VARCHAR,app_propstatus VARCHAR,app_resistyle VARCHAR,app_propdetail VARCHAR,app_detail VARCHAR,app_rupees VARCHAR,app_type1 VARCHAR,app_amount1 VARCHAR,app_type2 VARCHAR,app_amount2 VARCHAR,app_type3 VARCHAR,app_amount3 VARCHAR,app_totalamt VARCHAR,app_permanent VARCHAR," +
            "apper_Addrsline1 VARCHAR,apper_Tehsiltal VARCHAR,apper_District VARCHAR,apper_State VARCHAR,apper_Pincode VARCHAR," +
            "guarantor VARCHAR, " +
            "gu_salutation VARCHAR,gu_fname VARCHAR, gu_mname VARCHAR, gu_lname VARCHAR,gu_door VARCHAR,gu_street VARCHAR,gu_building VARCHAR,gu_area VARCHAR,gu_post VARCHAR,gu_tehsil VARCHAR,gu_district VARCHAR,gu_state VARCHAR,gu_landmark VARCHAR,gu_pincode VARCHAR,gu_landline VARCHAR, gu_mobileno VARCHAR,gu_incorporation VARCHAR,gu_date VARCHAR,gu_constitution VARCHAR,gu_occupation VARCHAR,gu_education VARCHAR,gu_ccity VARCHAR,gu_cyear VARCHAR,gu_maritalstatus VARCHAR,gu_gender VARCHAR,gu_father_spouse VARCHAR,gu_fsfname VARCHAR,gu_fslname VARCHAR," +
            "gu_idproof1 VARCHAR,gu_idetail1 VARCHAR,gu_idproof2 VARCHAR,gu_idetail2 VARCHAR,gu_addproof1 VARCHAR,gu_addetail1 VARCHAR,gu_addproof2 VARCHAR,gu_addetail2 VARCHAR,gu_email VARCHAR,gu_propstatus VARCHAR,gu_resistyle VARCHAR,gu_propdetail VARCHAR,gu_detail VARCHAR,gu_rupees VARCHAR,gu_type1 VARCHAR,gu_amount1 VARCHAR,gu_type2 VARCHAR,gu_amount2 VARCHAR,gu_type3 VARCHAR,gu_amount3 VARCHAR,gu_totalamt VARCHAR,gu_permanent VARCHAR,"+
            "ref_cusagreeno VARCHAR,ref_refname VARCHAR,ref_refaddrs VARCHAR,ref_refcity VARCHAR,ref_refpin VARCHAR,ref_refmob VARCHAR,ref_refrelation VARCHAR,ref_cusagreeno2 VARCHAR,ref_refname2 VARCHAR,ref_refaddrs2 VARCHAR,ref_refcity2 VARCHAR,ref_refpin2 VARCHAR,ref_refmob2 VARCHAR,ref_refrelation2 VARCHAR," +
            "coapplicant VARCHAR,"+
            "co_salutation VARCHAR,co_fname VARCHAR, co_mname VARCHAR, co_lname VARCHAR,co_door VARCHAR,co_street VARCHAR,co_building VARCHAR,co_area VARCHAR,co_post VARCHAR,co_tehsil VARCHAR,co_district VARCHAR,co_state VARCHAR,co_landmark VARCHAR,co_pincode VARCHAR,co_landline VARCHAR, co_mobileno VARCHAR,co_incorporation VARCHAR,co_date VARCHAR,co_constitution VARCHAR,co_occupation VARCHAR,co_education VARCHAR,co_ccity VARCHAR,co_cyear VARCHAR,co_maritalstatus VARCHAR,co_gender VARCHAR,co_father_spouse VARCHAR,co_fsfname VARCHAR,co_fslname VARCHAR," +
            "co_idproof1 VARCHAR,co_idetail1 VARCHAR,co_idproof2 VARCHAR,co_idetail2 VARCHAR,co_addproof1 VARCHAR,co_addetail1 VARCHAR,co_addproof2 VARCHAR,co_addetail2 VARCHAR,co_email VARCHAR,co_propstatus VARCHAR,co_resistyle VARCHAR,co_propdetail VARCHAR,co_detail VARCHAR,co_rupees VARCHAR,co_type1 VARCHAR,co_amount1 VARCHAR,co_type2 VARCHAR,co_amount2 VARCHAR,co_type3 VARCHAR,co_amount3 VARCHAR,co_totalamt VARCHAR,co_permanent VARCHAR,"+
            "asset_categry VARCHAR,asset_description VARCHAR,asset_type VARCHAR,manufacturer VARCHAR,asset_make VARCHAR,asset_model VARCHAR,asset_suppliername VARCHAR,asset_cost VARCHAR,asset_attachmnt VARCHAR,asset_total VARCHAR,asset_discount VARCHAR,asset_margin VARCHAR,asset_margin_total VARCHAR,asset_finance VARCHAR,asset_fin_attachmnt VARCHAR,asset_fin_total VARCHAR," +
            "off_Applcname VARCHAR,off_Addrstype VARCHAR,off_Employee VARCHAR,off_Psnem VARCHAR,off_Branch VARCHAR,off_Displaydate VARCHAR,off_Newexisting VARCHAR,off_Clientid VARCHAR,off_Schmcode VARCHAR,off_Agreeno VARCHAR,off_Promoschm VARCHAR,off_Custcategry VARCHAR,off_Chanelcode VARCHAR,off_Leadid VARCHAR,off_Inchargnam VARCHAR,off_Inchargeps VARCHAR,off_Detailsource VARCHAR,off_Retailasonam VARCHAR,off_Retailasonem VARCHAR,off_Loanpurp VARCHAR,off_Accoffnam VARCHAR,off_Accoffps VARCHAR,off_Product VARCHAR,off_Enduse VARCHAR);");

Solution

  • Your table seems a tad large -- might want to break it up, but that is an aside. You can use default values for columns to make your insert statements less annoying to type as well, but I'd highly suggest rearchitecting your schema.

    SQLite has an internal rowId column in every table that can serve as your primary key, without being declared as such and several other strategies are outlined on the same page.