1 package com.xtremelabs.robolectric.util; 2 3 import android.content.ContentValues; 4 import android.database.sqlite.SQLiteException; 5 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.Iterator; 9 import java.util.List; 10 import java.util.Map.Entry; 11 12 /** 13 * SQL utility methods to support the database-related shadows, such as 14 * {@code ShadowSQLiteDatabase} and {@code ShadowSQLiteCursor}. 15 */ 16 public class SQLite { 17 private static final String[] CONFLICT_VALUES = {"", "OR ROLLBACK ", "OR ABORT ", "OR FAIL ", "OR IGNORE ", "OR REPLACE "}; 18 19 /** 20 * Create a SQL INSERT string. Returned values are then bound via 21 * JDBC to facilitate various data types. 22 * 23 * @param table table name 24 * @param values column name/value pairs 25 * @param conflictAlgorithm the conflict algorithm to use 26 * @return insert string 27 */ 28 public static SQLStringAndBindings buildInsertString(String table, ContentValues values, int conflictAlgorithm) throws SQLException { 29 StringBuilder sb = new StringBuilder(); 30 31 sb.append("INSERT "); 32 sb.append(CONFLICT_VALUES[conflictAlgorithm]); 33 sb.append("INTO "); 34 35 sb.append(table); 36 sb.append(" "); 37 38 SQLStringAndBindings columnsValueClause = buildColumnValuesClause(values); 39 sb.append(columnsValueClause.sql); 40 sb.append(";"); 41 42 String sql = DatabaseConfig.getScrubSQL(sb.toString()); 43 return new SQLStringAndBindings(sql, columnsValueClause.columnValues); 44 } 45 46 /** 47 * Create a SQL UPDATE string. Returned values are then bound via 48 * JDBC to facilitate various data types. 49 * 50 * @param table table name 51 * @param values column name/value pairs 52 * @param whereClause SQL where clause fragment 53 * @param whereArgs Array of substitutions for args in whereClause 54 * @return update string 55 */ 56 public static SQLStringAndBindings buildUpdateString(String table, ContentValues values, String whereClause, String[] whereArgs) { 57 StringBuilder sb = new StringBuilder(); 58 59 sb.append("UPDATE "); 60 sb.append(table); 61 sb.append(" SET "); 62 63 SQLStringAndBindings columnAssignmentsClause = buildColumnAssignmentsClause(values); 64 sb.append(columnAssignmentsClause.sql); 65 66 if (whereClause != null) { 67 String where = whereClause; 68 if (whereArgs != null) { 69 where = buildWhereClause(whereClause, whereArgs); 70 } 71 sb.append(" WHERE "); 72 sb.append(where); 73 } 74 sb.append(";"); 75 76 return new SQLStringAndBindings(sb.toString(), columnAssignmentsClause.columnValues); 77 } 78 79 /** 80 * Create a SQL DELETE string. 81 * 82 * @param table table name 83 * @param whereClause SQL where clause fragment 84 * @param whereArgs Array of substitutions for args in whereClause 85 * @return delete string 86 */ 87 public static String buildDeleteString(String table, String whereClause, String[] whereArgs) { 88 StringBuilder sb = new StringBuilder(); 89 90 sb.append("DELETE FROM "); 91 sb.append(table); 92 93 if (whereClause != null) { 94 String where = whereClause; 95 if (whereArgs != null) { 96 where = buildWhereClause(whereClause, whereArgs); 97 } 98 sb.append(" WHERE "); 99 sb.append(where); 100 } 101 sb.append(";"); 102 103 return sb.toString(); 104 } 105 106 /** 107 * Build a WHERE clause used in SELECT, UPDATE and DELETE statements. 108 * 109 * @param selection SQL where clause fragment 110 * @param selectionArgs Array of substitutions for args in selection 111 * @return where clause 112 */ 113 public static String buildWhereClause(String selection, String[] selectionArgs) throws SQLiteException { 114 String whereClause = selection; 115 int argsNeeded = 0; 116 int args = 0; 117 118 for (char c : selection.toCharArray()) { 119 if (c == '?') argsNeeded++; 120 } 121 if (selectionArgs != null) { 122 for (int x = 0; x < selectionArgs.length; x++) { 123 if (selectionArgs[x] == null) { 124 throw new IllegalArgumentException("the bind value at index " + x + " is null"); 125 } else { 126 args++; 127 } 128 whereClause = whereClause.replaceFirst("\\?", "'" + selectionArgs[x] + "'"); 129 } 130 } 131 if (argsNeeded != args) { 132 throw new SQLiteException("bind or column index out of range: count of selectionArgs does not match count of (?) placeholders for given sql statement!"); 133 } 134 135 return whereClause; 136 } 137 138 /** 139 * Build the '(columns...) VALUES (values...)' clause used in INSERT 140 * statements. 141 * 142 * @param values column name/value pairs 143 * @return SQLStringAndBindings 144 */ 145 public static SQLStringAndBindings buildColumnValuesClause(ContentValues values) { 146 StringBuilder clause = new StringBuilder("("); 147 List<Object> columnValues = new ArrayList<Object>(values.size()); 148 149 Iterator<Entry<String, Object>> itemEntries = values.valueSet().iterator(); 150 while (itemEntries.hasNext()) { 151 Entry<String, Object> entry = itemEntries.next(); 152 clause.append(entry.getKey()); 153 if (itemEntries.hasNext()) { 154 clause.append(", "); 155 } 156 columnValues.add(entry.getValue()); 157 } 158 159 clause.append(") VALUES ("); 160 for (int i = 0; i < values.size() - 1; i++) { 161 clause.append("?, "); 162 } 163 clause.append("?)"); 164 165 return new SQLStringAndBindings(clause.toString(), columnValues); 166 } 167 168 /** 169 * Build the '(col1=?, col2=? ... )' clause used in UPDATE statements. 170 * 171 * @param values column name/value pairs 172 * @return SQLStringAndBindings 173 */ 174 public static SQLStringAndBindings buildColumnAssignmentsClause(ContentValues values) { 175 StringBuilder clause = new StringBuilder(); 176 List<Object> columnValues = new ArrayList<Object>(values.size()); 177 178 Iterator<Entry<String, Object>> itemsEntries = values.valueSet().iterator(); 179 while (itemsEntries.hasNext()) { 180 Entry<String, Object> entry = itemsEntries.next(); 181 clause.append(entry.getKey()); 182 clause.append("=?"); 183 if (itemsEntries.hasNext()) { 184 clause.append(", "); 185 } 186 columnValues.add(entry.getValue()); 187 } 188 189 return new SQLStringAndBindings(clause.toString(), columnValues); 190 } 191 192 /** 193 * Container for a SQL fragment and the objects which are to be 194 * bound to the arguments in the fragment. 195 */ 196 public static class SQLStringAndBindings { 197 public String sql; 198 public List<Object> columnValues; 199 200 public SQLStringAndBindings(String sql, List<Object> columnValues) { 201 this.sql = sql; 202 this.columnValues = columnValues; 203 } 204 } 205 206 } 207
