Inserting Content Values in multiple tables in Sqlite Android Application

Question

I am brand new to multiple tables in an SQLite database and am trying to find out what the best practices are for inserting values into multiple tables. My main question is do I need to Create another ContentValues object for inserting the values into a second table? I am really stumped on how to perform the insert(). Here is what I am trying so far.

Here are the two tables and schema

/* Creating a common attributes table here. */
    private static final String CREATE_COMMON_ATTRIBUTES_TABLE = "create table "
            + COMMON_ATTRIBUTES_TABLE + "(" + DBColCons.UID_COMMON_ATTRIBUTES + " integer" +
            " primary key autoincrement, " + DBColCons.GPS_POINT+ " integer not null, "
            + DBColCons.EXISTING_GRADE_GPS_POINT+ " integer not null, "
            + DBColCons.COVER+ " real not null, "+ DBColCons.NOTES+ " text, "
            + DBColCons.DATE+ " text)";

    /* Creating a weld table here */
    private static final String CREATE_WELD_TABLE = " create table " +WELD_TABLE+ "("
            + DBColCons.UID_WELD + " integer primary key, " + DBColCons.WELD_TYPE +
            " text, " + DBColCons.WELD_ID + " text, " + DBColCons.DOWNSTREAM_JOINT +
            " text, " + DBColCons.UPSTREAM_JOINT + " text, " + DBColCons.HEAT_AHEAD +
            " text, " + DBColCons.LENGTH_AHEAD + " real, " + DBColCons.WALL_CHANGE +
            " text, " + DBColCons.WELD_WALL_THICKNESS + " text, "
            + DBColCons.WELDER_INITIALS + " text, foreign key("+DBColCons.WELD_ID+") references" +
            "("+DBColCons.GPS_POINT+"))";

Here is the method I am wanting to use for the insert() with some class getters() for the Weld class, which I am passing in as a parameter.

public boolean insertWeld(Weld weld) {
        /* Get a writable copy of the database */
        SQLiteDatabase db = this.getWritableDatabase();
        /* Content values to insert with Weld class setters */
        ContentValues contentValuesWeld = new ContentValues();
        try {
            contentValuesWeld.put(DBColCons.GPS_POINT, weld.getGpsPoint());
            contentValuesWeld.put(DBColCons.WELD_TYPE, weld.getWeldType());
            contentValuesWeld.put(DBColCons.WELD_ID, weld.getWeldId());
            contentValuesWeld.put(DBColCons.DOWNSTREAM_JOINT, weld.getDownstreamJoint());
            contentValuesWeld.put(DBColCons.UPSTREAM_JOINT, weld.getUpstreamJoint());
            contentValuesWeld.put(DBColCons.HEAT_AHEAD, weld.getHeatAhead());
            contentValuesWeld.put(DBColCons.LENGTH_AHEAD, weld.getLengthAhead());
            contentValuesWeld.put(DBColCons.EXISTING_GRADE_GPS_POINT, weld.getExistingGradePoint());
            contentValuesWeld.put(DBColCons.COVER, weld.getCover());
            contentValuesWeld.put(DBColCons.WALL_CHANGE, weld.getWallChange());
            contentValuesWeld.put(DBColCons.WELD_WALL_THICKNESS, weld.getWeldWallThickness());
            contentValuesWeld.put(DBColCons.WELDER_INITIALS, weld.getWelderInitials());
            contentValuesWeld.put(DBColCons.NOTES, weld.getNotes());
            /* adding the date in here to the row. */
            contentValuesWeld.put(DBColCons.DATE, String.valueOf(mStrDate));
            /* Inserting into the weld table */
            db.insertWithOnConflict(WELD_TABLE, DBColCons.WELDER_INITIALS, contentValuesWeld,
                    SQLiteDatabase.CONFLICT_NONE);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

The values for DBColCons.GPS_POINT,DBColCons.EXISTING_GRADE_GPS_POINT,DBColCons.GPS_COVER and DBColCons.NOTES are what I want to insert into the Common_Attributes_Table. This is where I am really confused. Do I need to create a separate ContentValues object for those specific values and insert them into the desired table with a separate db.insert() method along with the one I am already using with the insert on the WELD_TABLE?

Help I am lost in this train wreck. Ha. Thank you all.

Do you plan on inserting the exact same record into multiple tables? If so, you might want to rethink your database design. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 19, 2017 at 0:52
@falsify. I have not run anything yet so no error. I am wanting to get it correct before carrying on with more. Sorry I should have been more specific. — J2112O
– J2112O, Commented Jan 19, 2017 at 0:54
@ Tim Biegeleisen. I don't think so initially. I will be making more tables however and the Common_Attributes_Table will be a part of all the other tables as well, since those specific values are shared by all the different types of data I plan on collecting. If that makes sense. — J2112O
– J2112O, Commented Jan 19, 2017 at 0:56

Karakuri · Accepted Answer · 2017-01-19 01:44:42Z

1

You need to call insert() (or insertWithConflict()) for each table you are inserting values into. Unless the values are the same, this implies you will need another ContentValues per table.

If you intend for these inserts to be committed as a single atomic operation, consider using a transaction.

SQLiteDatabase db = ...;
db.beginTransaction();
try {
    // do your inserts/etc. here
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

answered Jan 19, 2017 at 1:44

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J2112O Over a year ago

Thank you for your suggestion. I will try doing this as you have suggested. Thanks.

Collectives™ on Stack Overflow

Inserting Content Values in multiple tables in Sqlite Android Application

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related