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