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