Hello this is the correct way to create and use an SQLite database on Android.
You must use this system to avoid SQL injections, or that the user from your device can access the database, and enter false data:
link
STEP 1 you must create a contract class:
public class YourAppContract {
public static final String CONTENT_AUTHORITY = "com.example.yourapp";
public static final Uri BASE_CONTENT_URI = Uri.parse("content://" + CONTENT_AUTHORITY);
public static final String PATH_USER = "user";
public static final String PATH_USER_ID = "user_id";
public static final String PATH_USER_COUNT = "user_count";
public static final class YourAppEntry implements BaseColumns {
public static final Uri CONTENT_URI_USER = Uri.withAppendedPath(BASE_CONTENT_URI, PATH_USER);
public static final Uri CONTENT_URI_USER_ID = Uri.withAppendedPath(BASE_CONTENT_URI, PATH_USER_ID);
public static final Uri CONTENT_URI_USER_COUNT = Uri.withAppendedPath(BASE_CONTENT_URI, PATH_USER_COUNT);
public final static String TABLE_USER_NAME = "user";
public final static String COLUMN_USER_ID = "_id";
public final static String COLUMN_USER_USER = "user";
}
}
STEP 2 you must create the database:
public class YourAppDbHelper extends SQLiteOpenHelper {
public static final String LOG_TAG = YourAppDbHelper.class.getSimpleName();
private static final String DATABASE_NAME = "YourApp.db";
private static final int DATABASE_VERSION = 1;
private Context context;
private ContentValues values = new ContentValues();
private SQLiteDatabase db;
public YourAppDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
String SQL_CREATE_USER_TABLE = "CREATE TABLE " + YourAppContract.YourAppEntry.TABLE_USER_NAME + " ("
+ YourAppContract.YourAppEntry.COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ YourAppContract.YourAppEntry.COLUMN_USER_USER + " TEXT NOT NULL, "
+ YourAppContract.YourAppEntry.COLUMN_USER_PASSWORD + " TEXT NOT NULL);";
db.execSQL(SQL_CREATE_USER_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.setVersion(newVersion);
}
}
STEP 3 you must create a ContentProvider class:
public class YourAppProvider extends ContentProvider {
public static final int USER = 100;
public static final int USER_ID = 101;
public static final int USER_COUNT = 102;
public static final UriMatcher sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
static {
sUriMatcher.addURI(YourAppContract.CONTENT_AUTHORITY, YourAppContract.PATH_USER, USER);
sUriMatcher.addURI(YourAppContract.CONTENT_AUTHORITY, YourAppContract.PATH_USER_ID, USER_ID);
sUriMatcher.addURI(YourAppContract.CONTENT_AUTHORITY, YourAppContract.PATH_USER_COUNT, USER_COUNT);
}
public static final String LOG_TAG = YourDbHelper.class.getSimpleName();
private YourDbHelper yourAppHelper;
@Override
public boolean onCreate() {
yourAppHelper = new YourAppDbHelper(getContext());
return false;
}
@Nullable
@Override
public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
SQLiteDatabase db = yourAppHelper.getReadableDatabase();
int match = sUriMatcher.match(uri);
String table = null;
Cursor cursor;
switch (match) {
case USER:
table = YourAppContract.YourAppEntry.TABLE_USER_NAME;
break;
case USER_ID:
table = YourAppContract.YourAppEntry.TABLE_USER_NAME;
selection = YourAppContract.YourAppEntry.COLUMN_USER_ID + "=?";
//selectionArgs = new String[]{String.valueOf(ContentUris.parseId(uri))};
break;
case USER_COUNT:
cursor = db.rawQuery("SELECT COUNT(*) AS count FROM user", null);
cursor.setNotificationUri(getContext().getContentResolver(), uri);
return cursor;
}
cursor = db.query(table, projection, selection, selectionArgs, sortOrder, null, null);
cursor.setNotificationUri(getContext().getContentResolver(), uri);
return cursor;
}
//resto de metodos de content provider UPDATE, DELETE, INSERT...
//.....
}
STEP 4 you must configure the provider in the AndroidManifest file:
<provider
android:name=".database.YourAppProvider"
android:authorities="com.example.yourapp"
android:exported="false"
android:permission="permission"
android:protectionLevel="signature" />
<provider
android:name="android.support.v4.content.FileProvider"
android:authorities="com.example.yourapp.provider"
android:exported="false"
android:grantUriPermissions="true" >
<meta-data
android:name="android.support.FILE_PROVIDER_PATHS"
android:resource="@xml/provider_paths" />
</provider>
STEP 5 create custom queries and use your Content Provider:
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar_main);
setSupportActionBar(toolbar);
String[] projection = {YourAppContract.YourAppEntry.COLUMN_USER_ID, YourAppContract.YourAppEntry.COLUMN_USER_USER};
String clause = YourAppContract.YourAppEntry.COLUMN_USER_ID + "=? "+YourAppContract.YourAppEntry.COLUMN_USER_USER+"=? ";
String[] values = {"","Pepe"};
cursor = getContentResolver().query(YourAppContract.YourAppEntry.CONTENT_URI_USER, projection, clause, values, null);
if (cursor.getCount() == 0) {
//tu codigo
} else if(cursor.getCount()==1) {
cursor.moveToFirst();
} else if(cursor.getCount()>1){
//tu codigo
}
}