Databases Content Providers Add Update Delete Rows Content
Databases & Content Providers Add, Update & Delete Rows, Content Mobile Programming 1
Outline ADDING, UPDATING, AND REMOVING ROWS INSERTING NEW ROWS SYNTAX & EXAMPLE UPDATING ROWS SYNTAX & EXAMPLE DELETING ROWS SYNTAX & EXAMPLE ADDING, UPDATING, AND REMOVING CONTENTS 2
Adding, Updating, and Removing Rows TO PERFORM INSERT, DELETE, AND UPDATE TO SQLITE DB SQLITEDATABASE CLASS INCLUDES THREE CONVENIENCE METHODS: INSERT() , DELETE() , AND UPDATE() METHODS. THE METHODS ENCAPSULATE THE SQL STATEMENTS. THE EXECSQL METHOD LETS YOU EXECUTE ANY VALID QUERY. ALSO WITH SQLITE 3 COMMAND-LINE INTERFACE YOU CAN MANUALLY EXECUTE QUERY ON THE DATA. AFTER MODIFY THE DATABASE VALUES, YOU SHOULD CALL REFRESHQUERY, ON EACH CURSOR VIEW OF THE AFFECTED 3
Inserting New Rows 1 S T CREATE A NEW ROW CONSTRUCT CONTENTVALUES OBJECT ROW. CONTENTVALUES OBJECT REPRESENT SINGLE TABLE 2 N D PROVIDE A VALUE FOR EACH COLUMN USE PUT METHODS OF CONTENTVALUES OBJECT TO MAP OF COLUMN NAMES TO VALUES. 3 R D INSERT THE NEW ROW TO TABLE PASS THE CONTENTVALUES OBJECT INTO INSERT METHOD SPECIFY TARGET DATABASE ALONG WITH 4 THE TABLE NAME.
Inserting New Rows Syntax // CREATE A NEW ROW OF VALUES TO INSERT. CONTENTVALUES CONTVALUES = NEW CONTENTVALUES(); // ASSIGN VALUES FOR EACH ROW. CONTVALUES. PUT(COLUMN_NAME, COLUMN VALUE); [. . . REPEAT FOR EACH COLUMN. . . ] // INSERT THE ROW INTO YOUR TABLE MYDATABASE. INSERT(DATABASE_TABLE, NULL, CONTVALUES); 5
Inserting New Rows Eg. IMPORT ANDROID. CONTENTVALUES; . . . CONTENTVALUES CONTVALUES = NEW CONTENTVALUES(); CONTVALUES. PUT("FIRSTNAME", "J. K. "); CONTVALUES. PUT("LASTNAME", "ROWLING"); LONG NEWAUTHORID = MDATABASE. INSERT("TBLAUTHORS", NULL, CONTVALUES); THE INSERT() METHOD RETURNS THE IDENTIFIER OF THE NEWLY CREATED RECORD. THE INSERTORTHROW(), DOES THE SAME THING AS THE 6
Updating a Rows UPDATING ROWS IS ALSO DONE WITH CONTENT VALUES. 1 S T CREATE A NEW CONTENTVALUES OBJECT USE CONTENTVALUES OBJECT CONSTRUCT 2 N D PROVIDE A NEW VALUE FOR EACH COLUMN USE PUT METHODS OF CONTENTVALUES OBJECT. 3 R D UPDATE THE DATABASE CALL UPDATE ON THE DATABASE PASSING THE PARAMETERS. 7
Updating a Row Syntax // DEFINE THE UPDATED ROW CONTENTVALUES CONTVALUES = NEW CONTENTVALUES(); // ASSIGN VALUES FOR EACH ROW. CONTVALUES. PUT(COLUMN_NAME, NEW VALUE); [. . . REPEAT FOR EACH COLUMN. . . ] STRING WHERE = KEY_ID + "=" + ROWID; // UPDATE THE ROW WITH THE SPECIFIED INDEX WITH THE NEW VALUES. MYDATABASE. UPDATE(DATABASE_TABLE, CONTVALUES , 8 WHERE, NULL);
Updating a Row Syntax(cont’d) MYDATABASE. UPDATE(DATABASE_TABLE, CONTVALUES , “ID=? ”, NULL); ARGUMENT: THE TABLE TO UPDATE RECORDS A CONTENTVALUES OBJECT WITH THE MODIFIED FIELDS TO UPDATE AN OPTIONAL WHERE CLAUSE, IN WHICH ? IDENTIFIES A WHERE CLAUSE ARGUMENT AN ARRAY OF WHERE CLAUSE ARGUMENTS, EACH OF WHICH IS SUBSTITUTED IN PLACE OF THE ? S FROM THE SECOND PARAMETER PASSING NULL TO THE WHERE CLAUSE MODIFIES ALL 9
Updating a DB Eg. PUBLIC VOID UPDATEBOOKTITLE(INTEGER BOOKID, STRING NEWTITLE, STRING ISBN) { CONTENTVALUES = NEW CONTENTVALUES(); VALUES. PUT("TITLE", NEWTITLE); VALUES. PUT("ISBN", ISBN); MDATABASE. UPDATE("TBLBOOKS", VALUES, "ID=? ", NEW STRING[] { BOOKID. TOSTRING() }); } 10
Deleting Rows TO DELETE A ROW SIMPLY CALL DELETE METHOD ON A DATABASE. SPECIFYING THE TABLE NAME AND WHERE CLAUSE STRING WHERE = KEY_ID + "=" + ROWID; MYDATABASE. DELETE(DATABASE_TABLE, KEY_ID + "=" + ROWID, NULL); 11
Deleting Rows YOU CAN ALSO REMOVE RECORDS FROM THE DATABASE USING THE REMOVE() METHOD ARGUMENTS: THE TABLE TO DELETE THE RECORD FROM N AN OPTIONAL WHERE CLAUSE, IN WHICH ? IDENTIFIES A WHERE CLAUSE ARGUMENT AN ARRAY OF WHERE CLAUSE ARGUMENTS, EACH OF WHICH IS SUBSTITUTED IN PLACE OF THE ? S FROM THE SECOND PARAMETER PASSING NULL TO THE WHERE CLAUSE DELETES ALL RECORDS IN THE TABLE. 12
Deleting Rows Eg. TO DELETE INDIVIDUAL RECORDS BY THEIR UNIQUE IDENTIFIERS PUBLIC VOID DELETEBOOK(INTEGER BOOKID) { MDATABASE. DELETE ("TBL_BOOKS", "ID=? ", NEW STRING[] { BOOKID. TOSTRING() }); } YOU NEED NOT USE THE PRIMARY KEY (ID) TO DELETE RECORDS: PUBLIC VOID DELETEBOOKSBYAUTHOR(INTEGER AUTHORID) { INT NUMBOOKSDELETED = MDATABASE. DELETE("TBL_BOOKS", "AUTHORID=? ", NEW STRING[] { AUTHORID. TOSTRING ()}); 13
Adding, Updating, and Removing Content TO PERFORM TRANSACTIONS ON CONTENT PROVIDERS USE THE INSERT , UPDATE , AND DELETE METHODS OF CONTENTRESOLVER OBJECT. INSERTS THE CONTENT RESOLVER OFFERS TWO METHODS FOR INSERTING NEW RECORDS INTO YOUR CONTENT PROVIDER: INSERT AND BULKINSERT. BOTH ACCEPT THE URI OF THE ITEM-TYPE YOU’RE ADDING; URI REQUIRED TO ACCESS CONTENT PROVIDER. 14
Adding, Updating, and Removing Content INSERT : METHODS TAKES A SINGLE NEW CONTENTVALUES. BULKINSERT : TAKES AN ARRAYNEW CONTENTVALUES OBJECT THE SIMPLE INSERT METHOD WILL RETURN A URI TO THE NEWLY ADDED RECORD, BULKINSERT RETURNS THE NUMBER OF SUCCESSFULLY ADDED ROWS. 15
Adding Content Eg. // GET THE CONTENT RESOLVER CONTENTRESOLVER CR = GETCONTENTRESOLVER(); // CREATE A NEW ROW OF VALUES TO INSERT. CONTENTVALUES NEWVALUES = NEW CONTENTVALUES(); // ASSIGN VALUES FOR EACH ROW. NEWVALUES. PUT(COLUMN_NAME , NEWVALUE); [. . . REPEAT FOR EACH COLUMN. . . ] URI MYROWURI = CR. INSERT(MYPROVIDER. CONTENT_URI , NEWVALUES); // CREATE A NEW ROW OF VALUES TO INSERT. CONTENTVALUES[] VALUEARRAY = NEW CONTENTVALUES[5]; // TODO: CREATE AN ARRAY OF NEW ROWS INT COUNT = CR. BULKINSERT(MYPROVIDER. CONTENT_URI, 16 VALUEARRAY);
Update Content CONTENT PROVIDER ROW UPDATES ARE MADE WITH THE CONTENT RESOLVER UPDATE METHOD. THE UPDATE METHOD TAKES THE URI OF THE TARGET CONTENT PROVIDER, A CONTENTVALUES OBJECT THAT MAPS COLUMN NAMES TO UPDATED VALUES, AND A WHERE CLAUSE THAT INDICATES WHICH ROWS TO UPDATE. WHEN THE UPDATE IS EXECUTED, EVERY ROW MATCHED BY THE WHERE CLAUSE IS UPDATED USING THE SPECIFIED CONTENT VALUES, AND THE NUMBER OF SUCCESSFUL UPDATES IS RETURNED. 17
Update Content Eg. // CREATE A NEW ROW OF VALUES TO INSERT. CONTENTVALUES NEWVALUES = NEW CONTENTVALUES(); // CREATE A REPLACEMENT MAP, SPECIFYING WHICH COLUMNS YOU WANT TO // UPDATE, AND WHAT VALUES TO ASSIGN TO EACH OF THEM. NEWVALUES. PUT(COLUMN_NAME, NEWVALUE); // APPLY TO THE FIRST 5 ROWS. STRING WHERE = "_ID < 5"; GETCONTENTRESOLVER(). UPDATE(MYPROVIDER. CONTENT _URI, NEWVALUES, WHERE, NULL); 18
Delete Content TO DELETE A SINGLE RECORD, CALL DELETE ON THE CONTENT RESOLVER, PASSING IN THE URI OF THE ROW YOU WANT TO REMOVE. ALTERNATIVELY, YOU CAN SPECIFY A WHERE CLAUSE TO REMOVE MULTIPLE ROWS. CONTENTRESOLVER CR = GETCONTENTRESOLVER(); // REMOVE A SPECIFIC ROW. CR. DELETE(MYROWURI, NULL); // REMOVE THE FIRST FIVE ROWS. STRING WHERE = "_ID < 5"; CR. DELETE(MYPROVIDER. CONTENT_URI, WHERE, NULL); 19
- Slides: 19
