DatabaseUtils.java revision 22f711423e66750cd44a64e75372ea66304fb9a1
1/* 2 * Copyright (C) 2006 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17package android.database; 18 19import org.apache.commons.codec.binary.Hex; 20 21import android.content.ContentValues; 22import android.content.Context; 23import android.database.sqlite.SQLiteAbortException; 24import android.database.sqlite.SQLiteConstraintException; 25import android.database.sqlite.SQLiteDatabase; 26import android.database.sqlite.SQLiteDatabaseCorruptException; 27import android.database.sqlite.SQLiteDiskIOException; 28import android.database.sqlite.SQLiteException; 29import android.database.sqlite.SQLiteFullException; 30import android.database.sqlite.SQLiteProgram; 31import android.database.sqlite.SQLiteStatement; 32import android.os.Parcel; 33import android.text.TextUtils; 34import android.util.Config; 35import android.util.Log; 36 37import java.io.FileNotFoundException; 38import java.io.PrintStream; 39import java.text.Collator; 40import java.util.HashMap; 41import java.util.Map; 42 43/** 44 * Static utility methods for dealing with databases and {@link Cursor}s. 45 */ 46public class DatabaseUtils { 47 private static final String TAG = "DatabaseUtils"; 48 49 private static final boolean DEBUG = false; 50 private static final boolean LOCAL_LOGV = DEBUG ? Config.LOGD : Config.LOGV; 51 52 private static final String[] countProjection = new String[]{"count(*)"}; 53 54 /** 55 * Special function for writing an exception result at the header of 56 * a parcel, to be used when returning an exception from a transaction. 57 * exception will be re-thrown by the function in another process 58 * @param reply Parcel to write to 59 * @param e The Exception to be written. 60 * @see Parcel#writeNoException 61 * @see Parcel#writeException 62 */ 63 public static final void writeExceptionToParcel(Parcel reply, Exception e) { 64 int code = 0; 65 boolean logException = true; 66 if (e instanceof FileNotFoundException) { 67 code = 1; 68 logException = false; 69 } else if (e instanceof IllegalArgumentException) { 70 code = 2; 71 } else if (e instanceof UnsupportedOperationException) { 72 code = 3; 73 } else if (e instanceof SQLiteAbortException) { 74 code = 4; 75 } else if (e instanceof SQLiteConstraintException) { 76 code = 5; 77 } else if (e instanceof SQLiteDatabaseCorruptException) { 78 code = 6; 79 } else if (e instanceof SQLiteFullException) { 80 code = 7; 81 } else if (e instanceof SQLiteDiskIOException) { 82 code = 8; 83 } else if (e instanceof SQLiteException) { 84 code = 9; 85 } else { 86 reply.writeException(e); 87 Log.e(TAG, "Writing exception to parcel", e); 88 return; 89 } 90 reply.writeInt(code); 91 reply.writeString(e.getMessage()); 92 93 if (logException) { 94 Log.e(TAG, "Writing exception to parcel", e); 95 } 96 } 97 98 /** 99 * Special function for reading an exception result from the header of 100 * a parcel, to be used after receiving the result of a transaction. This 101 * will throw the exception for you if it had been written to the Parcel, 102 * otherwise return and let you read the normal result data from the Parcel. 103 * @param reply Parcel to read from 104 * @see Parcel#writeNoException 105 * @see Parcel#readException 106 */ 107 public static final void readExceptionFromParcel(Parcel reply) { 108 int code = reply.readInt(); 109 if (code == 0) return; 110 String msg = reply.readString(); 111 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 112 } 113 114 public static void readExceptionWithFileNotFoundExceptionFromParcel( 115 Parcel reply) throws FileNotFoundException { 116 int code = reply.readInt(); 117 if (code == 0) return; 118 String msg = reply.readString(); 119 if (code == 1) { 120 throw new FileNotFoundException(msg); 121 } else { 122 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 123 } 124 } 125 126 private static final void readExceptionFromParcel(Parcel reply, String msg, int code) { 127 switch (code) { 128 case 2: 129 throw new IllegalArgumentException(msg); 130 case 3: 131 throw new UnsupportedOperationException(msg); 132 case 4: 133 throw new SQLiteAbortException(msg); 134 case 5: 135 throw new SQLiteConstraintException(msg); 136 case 6: 137 throw new SQLiteDatabaseCorruptException(msg); 138 case 7: 139 throw new SQLiteFullException(msg); 140 case 8: 141 throw new SQLiteDiskIOException(msg); 142 case 9: 143 throw new SQLiteException(msg); 144 default: 145 reply.readException(code, msg); 146 } 147 } 148 149 /** 150 * Binds the given Object to the given SQLiteProgram using the proper 151 * typing. For example, bind numbers as longs/doubles, and everything else 152 * as a string by call toString() on it. 153 * 154 * @param prog the program to bind the object to 155 * @param index the 1-based index to bind at 156 * @param value the value to bind 157 */ 158 public static void bindObjectToProgram(SQLiteProgram prog, int index, 159 Object value) { 160 if (value == null) { 161 prog.bindNull(index); 162 } else if (value instanceof Double || value instanceof Float) { 163 prog.bindDouble(index, ((Number)value).doubleValue()); 164 } else if (value instanceof Number) { 165 prog.bindLong(index, ((Number)value).longValue()); 166 } else if (value instanceof Boolean) { 167 Boolean bool = (Boolean)value; 168 if (bool) { 169 prog.bindLong(index, 1); 170 } else { 171 prog.bindLong(index, 0); 172 } 173 } else if (value instanceof byte[]){ 174 prog.bindBlob(index, (byte[]) value); 175 } else { 176 prog.bindString(index, value.toString()); 177 } 178 } 179 180 /** 181 * Appends an SQL string to the given StringBuilder, including the opening 182 * and closing single quotes. Any single quotes internal to sqlString will 183 * be escaped. 184 * 185 * This method is deprecated because we want to encourage everyone 186 * to use the "?" binding form. However, when implementing a 187 * ContentProvider, one may want to add WHERE clauses that were 188 * not provided by the caller. Since "?" is a positional form, 189 * using it in this case could break the caller because the 190 * indexes would be shifted to accomodate the ContentProvider's 191 * internal bindings. In that case, it may be necessary to 192 * construct a WHERE clause manually. This method is useful for 193 * those cases. 194 * 195 * @param sb the StringBuilder that the SQL string will be appended to 196 * @param sqlString the raw string to be appended, which may contain single 197 * quotes 198 */ 199 public static void appendEscapedSQLString(StringBuilder sb, String sqlString) { 200 sb.append('\''); 201 if (sqlString.indexOf('\'') != -1) { 202 int length = sqlString.length(); 203 for (int i = 0; i < length; i++) { 204 char c = sqlString.charAt(i); 205 if (c == '\'') { 206 sb.append('\''); 207 } 208 sb.append(c); 209 } 210 } else 211 sb.append(sqlString); 212 sb.append('\''); 213 } 214 215 /** 216 * SQL-escape a string. 217 */ 218 public static String sqlEscapeString(String value) { 219 StringBuilder escaper = new StringBuilder(); 220 221 DatabaseUtils.appendEscapedSQLString(escaper, value); 222 223 return escaper.toString(); 224 } 225 226 /** 227 * Appends an Object to an SQL string with the proper escaping, etc. 228 */ 229 public static final void appendValueToSql(StringBuilder sql, Object value) { 230 if (value == null) { 231 sql.append("NULL"); 232 } else if (value instanceof Boolean) { 233 Boolean bool = (Boolean)value; 234 if (bool) { 235 sql.append('1'); 236 } else { 237 sql.append('0'); 238 } 239 } else { 240 appendEscapedSQLString(sql, value.toString()); 241 } 242 } 243 244 /** 245 * Concatenates two SQL WHERE clauses, handling empty or null values. 246 * @hide 247 */ 248 public static String concatenateWhere(String a, String b) { 249 if (TextUtils.isEmpty(a)) { 250 return b; 251 } 252 if (TextUtils.isEmpty(b)) { 253 return a; 254 } 255 256 return "(" + a + ") AND (" + b + ")"; 257 } 258 259 /** 260 * return the collation key 261 * @param name 262 * @return the collation key 263 */ 264 public static String getCollationKey(String name) { 265 byte [] arr = getCollationKeyInBytes(name); 266 try { 267 return new String(arr, 0, getKeyLen(arr), "ISO8859_1"); 268 } catch (Exception ex) { 269 return ""; 270 } 271 } 272 273 /** 274 * return the collation key in hex format 275 * @param name 276 * @return the collation key in hex format 277 */ 278 public static String getHexCollationKey(String name) { 279 byte [] arr = getCollationKeyInBytes(name); 280 char[] keys = Hex.encodeHex(arr); 281 return new String(keys, 0, getKeyLen(arr) * 2); 282 } 283 284 private static int getKeyLen(byte[] arr) { 285 if (arr[arr.length - 1] != 0) { 286 return arr.length; 287 } else { 288 // remove zero "termination" 289 return arr.length-1; 290 } 291 } 292 293 private static byte[] getCollationKeyInBytes(String name) { 294 if (mColl == null) { 295 mColl = Collator.getInstance(); 296 mColl.setStrength(Collator.PRIMARY); 297 } 298 return mColl.getCollationKey(name).toByteArray(); 299 } 300 301 private static Collator mColl = null; 302 /** 303 * Prints the contents of a Cursor to System.out. The position is restored 304 * after printing. 305 * 306 * @param cursor the cursor to print 307 */ 308 public static void dumpCursor(Cursor cursor) { 309 dumpCursor(cursor, System.out); 310 } 311 312 /** 313 * Prints the contents of a Cursor to a PrintSteam. The position is restored 314 * after printing. 315 * 316 * @param cursor the cursor to print 317 * @param stream the stream to print to 318 */ 319 public static void dumpCursor(Cursor cursor, PrintStream stream) { 320 stream.println(">>>>> Dumping cursor " + cursor); 321 if (cursor != null) { 322 int startPos = cursor.getPosition(); 323 324 cursor.moveToPosition(-1); 325 while (cursor.moveToNext()) { 326 dumpCurrentRow(cursor, stream); 327 } 328 cursor.moveToPosition(startPos); 329 } 330 stream.println("<<<<<"); 331 } 332 333 /** 334 * Prints the contents of a Cursor to a StringBuilder. The position 335 * is restored after printing. 336 * 337 * @param cursor the cursor to print 338 * @param sb the StringBuilder to print to 339 */ 340 public static void dumpCursor(Cursor cursor, StringBuilder sb) { 341 sb.append(">>>>> Dumping cursor " + cursor + "\n"); 342 if (cursor != null) { 343 int startPos = cursor.getPosition(); 344 345 cursor.moveToPosition(-1); 346 while (cursor.moveToNext()) { 347 dumpCurrentRow(cursor, sb); 348 } 349 cursor.moveToPosition(startPos); 350 } 351 sb.append("<<<<<\n"); 352 } 353 354 /** 355 * Prints the contents of a Cursor to a String. The position is restored 356 * after printing. 357 * 358 * @param cursor the cursor to print 359 * @return a String that contains the dumped cursor 360 */ 361 public static String dumpCursorToString(Cursor cursor) { 362 StringBuilder sb = new StringBuilder(); 363 dumpCursor(cursor, sb); 364 return sb.toString(); 365 } 366 367 /** 368 * Prints the contents of a Cursor's current row to System.out. 369 * 370 * @param cursor the cursor to print from 371 */ 372 public static void dumpCurrentRow(Cursor cursor) { 373 dumpCurrentRow(cursor, System.out); 374 } 375 376 /** 377 * Prints the contents of a Cursor's current row to a PrintSteam. 378 * 379 * @param cursor the cursor to print 380 * @param stream the stream to print to 381 */ 382 public static void dumpCurrentRow(Cursor cursor, PrintStream stream) { 383 String[] cols = cursor.getColumnNames(); 384 stream.println("" + cursor.getPosition() + " {"); 385 int length = cols.length; 386 for (int i = 0; i< length; i++) { 387 String value; 388 try { 389 value = cursor.getString(i); 390 } catch (SQLiteException e) { 391 // assume that if the getString threw this exception then the column is not 392 // representable by a string, e.g. it is a BLOB. 393 value = "<unprintable>"; 394 } 395 stream.println(" " + cols[i] + '=' + value); 396 } 397 stream.println("}"); 398 } 399 400 /** 401 * Prints the contents of a Cursor's current row to a StringBuilder. 402 * 403 * @param cursor the cursor to print 404 * @param sb the StringBuilder to print to 405 */ 406 public static void dumpCurrentRow(Cursor cursor, StringBuilder sb) { 407 String[] cols = cursor.getColumnNames(); 408 sb.append("" + cursor.getPosition() + " {\n"); 409 int length = cols.length; 410 for (int i = 0; i < length; i++) { 411 String value; 412 try { 413 value = cursor.getString(i); 414 } catch (SQLiteException e) { 415 // assume that if the getString threw this exception then the column is not 416 // representable by a string, e.g. it is a BLOB. 417 value = "<unprintable>"; 418 } 419 sb.append(" " + cols[i] + '=' + value + "\n"); 420 } 421 sb.append("}\n"); 422 } 423 424 /** 425 * Dump the contents of a Cursor's current row to a String. 426 * 427 * @param cursor the cursor to print 428 * @return a String that contains the dumped cursor row 429 */ 430 public static String dumpCurrentRowToString(Cursor cursor) { 431 StringBuilder sb = new StringBuilder(); 432 dumpCurrentRow(cursor, sb); 433 return sb.toString(); 434 } 435 436 /** 437 * Reads a String out of a field in a Cursor and writes it to a Map. 438 * 439 * @param cursor The cursor to read from 440 * @param field The TEXT field to read 441 * @param values The {@link ContentValues} to put the value into, with the field as the key 442 */ 443 public static void cursorStringToContentValues(Cursor cursor, String field, 444 ContentValues values) { 445 cursorStringToContentValues(cursor, field, values, field); 446 } 447 448 /** 449 * Reads a String out of a field in a Cursor and writes it to an InsertHelper. 450 * 451 * @param cursor The cursor to read from 452 * @param field The TEXT field to read 453 * @param inserter The InsertHelper to bind into 454 * @param index the index of the bind entry in the InsertHelper 455 */ 456 public static void cursorStringToInsertHelper(Cursor cursor, String field, 457 InsertHelper inserter, int index) { 458 inserter.bind(index, cursor.getString(cursor.getColumnIndexOrThrow(field))); 459 } 460 461 /** 462 * Reads a String out of a field in a Cursor and writes it to a Map. 463 * 464 * @param cursor The cursor to read from 465 * @param field The TEXT field to read 466 * @param values The {@link ContentValues} to put the value into, with the field as the key 467 * @param key The key to store the value with in the map 468 */ 469 public static void cursorStringToContentValues(Cursor cursor, String field, 470 ContentValues values, String key) { 471 values.put(key, cursor.getString(cursor.getColumnIndexOrThrow(field))); 472 } 473 474 /** 475 * Reads an Integer out of a field in a Cursor and writes it to a Map. 476 * 477 * @param cursor The cursor to read from 478 * @param field The INTEGER field to read 479 * @param values The {@link ContentValues} to put the value into, with the field as the key 480 */ 481 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values) { 482 cursorIntToContentValues(cursor, field, values, field); 483 } 484 485 /** 486 * Reads a Integer out of a field in a Cursor and writes it to a Map. 487 * 488 * @param cursor The cursor to read from 489 * @param field The INTEGER field to read 490 * @param values The {@link ContentValues} to put the value into, with the field as the key 491 * @param key The key to store the value with in the map 492 */ 493 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values, 494 String key) { 495 int colIndex = cursor.getColumnIndex(field); 496 if (!cursor.isNull(colIndex)) { 497 values.put(key, cursor.getInt(colIndex)); 498 } else { 499 values.put(key, (Integer) null); 500 } 501 } 502 503 /** 504 * Reads a Long out of a field in a Cursor and writes it to a Map. 505 * 506 * @param cursor The cursor to read from 507 * @param field The INTEGER field to read 508 * @param values The {@link ContentValues} to put the value into, with the field as the key 509 */ 510 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values) 511 { 512 cursorLongToContentValues(cursor, field, values, field); 513 } 514 515 /** 516 * Reads a Long out of a field in a Cursor and writes it to a Map. 517 * 518 * @param cursor The cursor to read from 519 * @param field The INTEGER field to read 520 * @param values The {@link ContentValues} to put the value into 521 * @param key The key to store the value with in the map 522 */ 523 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values, 524 String key) { 525 int colIndex = cursor.getColumnIndex(field); 526 if (!cursor.isNull(colIndex)) { 527 Long value = Long.valueOf(cursor.getLong(colIndex)); 528 values.put(key, value); 529 } else { 530 values.put(key, (Long) null); 531 } 532 } 533 534 /** 535 * Reads a Double out of a field in a Cursor and writes it to a Map. 536 * 537 * @param cursor The cursor to read from 538 * @param field The REAL field to read 539 * @param values The {@link ContentValues} to put the value into 540 */ 541 public static void cursorDoubleToCursorValues(Cursor cursor, String field, ContentValues values) 542 { 543 cursorDoubleToContentValues(cursor, field, values, field); 544 } 545 546 /** 547 * Reads a Double out of a field in a Cursor and writes it to a Map. 548 * 549 * @param cursor The cursor to read from 550 * @param field The REAL field to read 551 * @param values The {@link ContentValues} to put the value into 552 * @param key The key to store the value with in the map 553 */ 554 public static void cursorDoubleToContentValues(Cursor cursor, String field, 555 ContentValues values, String key) { 556 int colIndex = cursor.getColumnIndex(field); 557 if (!cursor.isNull(colIndex)) { 558 values.put(key, cursor.getDouble(colIndex)); 559 } else { 560 values.put(key, (Double) null); 561 } 562 } 563 564 /** 565 * Read the entire contents of a cursor row and store them in a ContentValues. 566 * 567 * @param cursor the cursor to read from. 568 * @param values the {@link ContentValues} to put the row into. 569 */ 570 public static void cursorRowToContentValues(Cursor cursor, ContentValues values) { 571 AbstractWindowedCursor awc = 572 (cursor instanceof AbstractWindowedCursor) ? (AbstractWindowedCursor) cursor : null; 573 574 String[] columns = cursor.getColumnNames(); 575 int length = columns.length; 576 for (int i = 0; i < length; i++) { 577 if (awc != null && awc.isBlob(i)) { 578 values.put(columns[i], cursor.getBlob(i)); 579 } else { 580 values.put(columns[i], cursor.getString(i)); 581 } 582 } 583 } 584 585 /** 586 * Query the table for the number of rows in the table. 587 * @param db the database the table is in 588 * @param table the name of the table to query 589 * @return the number of rows in the table 590 */ 591 public static long queryNumEntries(SQLiteDatabase db, String table) { 592 Cursor cursor = db.query(table, countProjection, 593 null, null, null, null, null); 594 try { 595 cursor.moveToFirst(); 596 return cursor.getLong(0); 597 } finally { 598 cursor.close(); 599 } 600 } 601 602 /** 603 * Utility method to run the query on the db and return the value in the 604 * first column of the first row. 605 */ 606 public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 607 SQLiteStatement prog = db.compileStatement(query); 608 try { 609 return longForQuery(prog, selectionArgs); 610 } finally { 611 prog.close(); 612 } 613 } 614 615 /** 616 * Utility method to run the pre-compiled query and return the value in the 617 * first column of the first row. 618 */ 619 public static long longForQuery(SQLiteStatement prog, String[] selectionArgs) { 620 if (selectionArgs != null) { 621 int size = selectionArgs.length; 622 for (int i = 0; i < size; i++) { 623 bindObjectToProgram(prog, i + 1, selectionArgs[i]); 624 } 625 } 626 long value = prog.simpleQueryForLong(); 627 return value; 628 } 629 630 /** 631 * Utility method to run the query on the db and return the value in the 632 * first column of the first row. 633 */ 634 public static String stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 635 SQLiteStatement prog = db.compileStatement(query); 636 try { 637 return stringForQuery(prog, selectionArgs); 638 } finally { 639 prog.close(); 640 } 641 } 642 643 /** 644 * Utility method to run the pre-compiled query and return the value in the 645 * first column of the first row. 646 */ 647 public static String stringForQuery(SQLiteStatement prog, String[] selectionArgs) { 648 if (selectionArgs != null) { 649 int size = selectionArgs.length; 650 for (int i = 0; i < size; i++) { 651 bindObjectToProgram(prog, i + 1, selectionArgs[i]); 652 } 653 } 654 String value = prog.simpleQueryForString(); 655 return value; 656 } 657 658 /** 659 * This class allows users to do multiple inserts into a table but 660 * compile the SQL insert statement only once, which may increase 661 * performance. 662 */ 663 public static class InsertHelper { 664 private final SQLiteDatabase mDb; 665 private final String mTableName; 666 private HashMap<String, Integer> mColumns; 667 private String mInsertSQL = null; 668 private SQLiteStatement mInsertStatement = null; 669 private SQLiteStatement mReplaceStatement = null; 670 private SQLiteStatement mPreparedStatement = null; 671 672 /** 673 * {@hide} 674 * 675 * These are the columns returned by sqlite's "PRAGMA 676 * table_info(...)" command that we depend on. 677 */ 678 public static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1; 679 public static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4; 680 681 /** 682 * @param db the SQLiteDatabase to insert into 683 * @param tableName the name of the table to insert into 684 */ 685 public InsertHelper(SQLiteDatabase db, String tableName) { 686 mDb = db; 687 mTableName = tableName; 688 } 689 690 private void buildSQL() throws SQLException { 691 StringBuilder sb = new StringBuilder(128); 692 sb.append("INSERT INTO "); 693 sb.append(mTableName); 694 sb.append(" ("); 695 696 StringBuilder sbv = new StringBuilder(128); 697 sbv.append("VALUES ("); 698 699 int i = 1; 700 Cursor cur = null; 701 try { 702 cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null); 703 mColumns = new HashMap<String, Integer>(cur.getCount()); 704 while (cur.moveToNext()) { 705 String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX); 706 String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX); 707 708 mColumns.put(columnName, i); 709 sb.append("'"); 710 sb.append(columnName); 711 sb.append("'"); 712 713 if (defaultValue == null) { 714 sbv.append("?"); 715 } else { 716 sbv.append("COALESCE(?, "); 717 sbv.append(defaultValue); 718 sbv.append(")"); 719 } 720 721 sb.append(i == cur.getCount() ? ") " : ", "); 722 sbv.append(i == cur.getCount() ? ");" : ", "); 723 ++i; 724 } 725 } finally { 726 if (cur != null) cur.close(); 727 } 728 729 sb.append(sbv); 730 731 mInsertSQL = sb.toString(); 732 if (LOCAL_LOGV) Log.v(TAG, "insert statement is " + mInsertSQL); 733 } 734 735 private SQLiteStatement getStatement(boolean allowReplace) throws SQLException { 736 if (allowReplace) { 737 if (mReplaceStatement == null) { 738 if (mInsertSQL == null) buildSQL(); 739 // chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead. 740 String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6); 741 mReplaceStatement = mDb.compileStatement(replaceSQL); 742 } 743 return mReplaceStatement; 744 } else { 745 if (mInsertStatement == null) { 746 if (mInsertSQL == null) buildSQL(); 747 mInsertStatement = mDb.compileStatement(mInsertSQL); 748 } 749 return mInsertStatement; 750 } 751 } 752 753 /** 754 * Performs an insert, adding a new row with the given values. 755 * 756 * @param values the set of values with which to populate the 757 * new row 758 * @param allowReplace if true, the statement does "INSERT OR 759 * REPLACE" instead of "INSERT", silently deleting any 760 * previously existing rows that would cause a conflict 761 * 762 * @return the row ID of the newly inserted row, or -1 if an 763 * error occurred 764 */ 765 private synchronized long insertInternal(ContentValues values, boolean allowReplace) { 766 try { 767 SQLiteStatement stmt = getStatement(allowReplace); 768 stmt.clearBindings(); 769 if (LOCAL_LOGV) Log.v(TAG, "--- inserting in table " + mTableName); 770 for (Map.Entry<String, Object> e: values.valueSet()) { 771 final String key = e.getKey(); 772 int i = getColumnIndex(key); 773 DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue()); 774 if (LOCAL_LOGV) { 775 Log.v(TAG, "binding " + e.getValue() + " to column " + 776 i + " (" + key + ")"); 777 } 778 } 779 return stmt.executeInsert(); 780 } catch (SQLException e) { 781 Log.e(TAG, "Error inserting " + values + " into table " + mTableName, e); 782 return -1; 783 } 784 } 785 786 /** 787 * Returns the index of the specified column. This is index is suitagble for use 788 * in calls to bind(). 789 * @param key the column name 790 * @return the index of the column 791 */ 792 public int getColumnIndex(String key) { 793 getStatement(false); 794 final Integer index = mColumns.get(key); 795 if (index == null) { 796 throw new IllegalArgumentException("column '" + key + "' is invalid"); 797 } 798 return index; 799 } 800 801 /** 802 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 803 * without a matching execute() must have already have been called. 804 * @param index the index of the slot to which to bind 805 * @param value the value to bind 806 */ 807 public void bind(int index, double value) { 808 mPreparedStatement.bindDouble(index, value); 809 } 810 811 /** 812 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 813 * without a matching execute() must have already have been called. 814 * @param index the index of the slot to which to bind 815 * @param value the value to bind 816 */ 817 public void bind(int index, float value) { 818 mPreparedStatement.bindDouble(index, value); 819 } 820 821 /** 822 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 823 * without a matching execute() must have already have been called. 824 * @param index the index of the slot to which to bind 825 * @param value the value to bind 826 */ 827 public void bind(int index, long value) { 828 mPreparedStatement.bindLong(index, value); 829 } 830 831 /** 832 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 833 * without a matching execute() must have already have been called. 834 * @param index the index of the slot to which to bind 835 * @param value the value to bind 836 */ 837 public void bind(int index, int value) { 838 mPreparedStatement.bindLong(index, value); 839 } 840 841 /** 842 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 843 * without a matching execute() must have already have been called. 844 * @param index the index of the slot to which to bind 845 * @param value the value to bind 846 */ 847 public void bind(int index, boolean value) { 848 mPreparedStatement.bindLong(index, value ? 1 : 0); 849 } 850 851 /** 852 * Bind null to an index. A prepareForInsert() or prepareForReplace() 853 * without a matching execute() must have already have been called. 854 * @param index the index of the slot to which to bind 855 */ 856 public void bindNull(int index) { 857 mPreparedStatement.bindNull(index); 858 } 859 860 /** 861 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 862 * without a matching execute() must have already have been called. 863 * @param index the index of the slot to which to bind 864 * @param value the value to bind 865 */ 866 public void bind(int index, byte[] value) { 867 if (value == null) { 868 mPreparedStatement.bindNull(index); 869 } else { 870 mPreparedStatement.bindBlob(index, value); 871 } 872 } 873 874 /** 875 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 876 * without a matching execute() must have already have been called. 877 * @param index the index of the slot to which to bind 878 * @param value the value to bind 879 */ 880 public void bind(int index, String value) { 881 if (value == null) { 882 mPreparedStatement.bindNull(index); 883 } else { 884 mPreparedStatement.bindString(index, value); 885 } 886 } 887 888 /** 889 * Performs an insert, adding a new row with the given values. 890 * If the table contains conflicting rows, an error is 891 * returned. 892 * 893 * @param values the set of values with which to populate the 894 * new row 895 * 896 * @return the row ID of the newly inserted row, or -1 if an 897 * error occurred 898 */ 899 public long insert(ContentValues values) { 900 return insertInternal(values, false); 901 } 902 903 /** 904 * Execute the previously prepared insert or replace using the bound values 905 * since the last call to prepareForInsert or prepareForReplace. 906 * 907 * <p>Note that calling bind() and then execute() is not thread-safe. The only thread-safe 908 * way to use this class is to call insert() or replace(). 909 * 910 * @return the row ID of the newly inserted row, or -1 if an 911 * error occurred 912 */ 913 public long execute() { 914 if (mPreparedStatement == null) { 915 throw new IllegalStateException("you must prepare this inserter before calling " 916 + "execute"); 917 } 918 try { 919 if (LOCAL_LOGV) Log.v(TAG, "--- doing insert or replace in table " + mTableName); 920 return mPreparedStatement.executeInsert(); 921 } catch (SQLException e) { 922 Log.e(TAG, "Error executing InsertHelper with table " + mTableName, e); 923 return -1; 924 } finally { 925 // you can only call this once per prepare 926 mPreparedStatement = null; 927 } 928 } 929 930 /** 931 * Prepare the InsertHelper for an insert. The pattern for this is: 932 * <ul> 933 * <li>prepareForInsert() 934 * <li>bind(index, value); 935 * <li>bind(index, value); 936 * <li>... 937 * <li>bind(index, value); 938 * <li>execute(); 939 * </ul> 940 */ 941 public void prepareForInsert() { 942 mPreparedStatement = getStatement(false); 943 mPreparedStatement.clearBindings(); 944 } 945 946 /** 947 * Prepare the InsertHelper for a replace. The pattern for this is: 948 * <ul> 949 * <li>prepareForReplace() 950 * <li>bind(index, value); 951 * <li>bind(index, value); 952 * <li>... 953 * <li>bind(index, value); 954 * <li>execute(); 955 * </ul> 956 */ 957 public void prepareForReplace() { 958 mPreparedStatement = getStatement(true); 959 mPreparedStatement.clearBindings(); 960 } 961 962 /** 963 * Performs an insert, adding a new row with the given values. 964 * If the table contains conflicting rows, they are deleted 965 * and replaced with the new row. 966 * 967 * @param values the set of values with which to populate the 968 * new row 969 * 970 * @return the row ID of the newly inserted row, or -1 if an 971 * error occurred 972 */ 973 public long replace(ContentValues values) { 974 return insertInternal(values, true); 975 } 976 977 /** 978 * Close this object and release any resources associated with 979 * it. The behavior of calling <code>insert()</code> after 980 * calling this method is undefined. 981 */ 982 public void close() { 983 if (mInsertStatement != null) { 984 mInsertStatement.close(); 985 mInsertStatement = null; 986 } 987 if (mReplaceStatement != null) { 988 mReplaceStatement.close(); 989 mReplaceStatement = null; 990 } 991 mInsertSQL = null; 992 mColumns = null; 993 } 994 } 995 996 /** 997 * Creates a db and populates it with the sql statements in sqlStatements. 998 * 999 * @param context the context to use to create the db 1000 * @param dbName the name of the db to create 1001 * @param dbVersion the version to set on the db 1002 * @param sqlStatements the statements to use to populate the db. This should be a single string 1003 * of the form returned by sqlite3's <tt>.dump</tt> command (statements separated by 1004 * semicolons) 1005 */ 1006 static public void createDbFromSqlStatements( 1007 Context context, String dbName, int dbVersion, String sqlStatements) { 1008 SQLiteDatabase db = context.openOrCreateDatabase(dbName, 0, null); 1009 // TODO: this is not quite safe since it assumes that all semicolons at the end of a line 1010 // terminate statements. It is possible that a text field contains ;\n. We will have to fix 1011 // this if that turns out to be a problem. 1012 String[] statements = TextUtils.split(sqlStatements, ";\n"); 1013 for (String statement : statements) { 1014 if (TextUtils.isEmpty(statement)) continue; 1015 db.execSQL(statement); 1016 } 1017 db.setVersion(dbVersion); 1018 db.close(); 1019 } 1020} 1021