Как с помощью методов query и rawQuery в SQLite выполнять запросы для связанных таблиц. Создадим простое приложение, которое будет делать запрос из двух таблиц и выводить результат в лог. Таблицы будет две — people и position. В первую (people) запишем список людей, во вторую (position) – список должностей. И для каждого человека из таблицы people будет прописан id должности из таблицы position.
Исходный код под видео:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
package info.fandroid.p0371_sqliteinnerjoin; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.util.Log; public class MainActivity extends AppCompatActivity { final String LOG_TAG = "myLogs"; int[] position_id = {1, 2, 3, 4}; String[] position_name = {"Директор", "Программист", "Бухгалтер", "Охранник"}; int[] position_salary = {80000, 60000, 40000, 20000}; String[] people_name = {"Максим", "Сергей", "Руслан", "Наталья", "Иван", "Мария", "Светлана", "Григорий"}; int[] people_posid = {2, 3, 2, 2, 3, 1, 2, 4}; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DbHelper dbHelper = new DbHelper(this); SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase(); Cursor cursor; Log.d(LOG_TAG, "---Table position---"); cursor = sqLiteDatabase.query("position", null, null, null, null, null, null); logCursor(cursor); cursor.close(); Log.d(LOG_TAG, "--- ---"); Log.d(LOG_TAG, "---Table people---"); cursor = sqLiteDatabase.query("people", null, null, null, null, null, null); logCursor(cursor); cursor.close(); Log.d(LOG_TAG, "--- ---"); Log.d(LOG_TAG, "---INNER JOIN with rawQuery---"); String sqlQuery = "select PL.name as Name, PS.name as Position, salary as Salary " + "from people as PL " + "inner join position as PS " + "on PL.posid = PS.id " + "where salary > ?"; cursor = sqLiteDatabase.rawQuery(sqlQuery, new String[] {"40000"}); logCursor(cursor); cursor.close(); Log.d(LOG_TAG, "--- ---"); Log.d(LOG_TAG, "---INNER JOIN with query---"); String table = "people as PL inner join position as PS on PL.posid = PS.id"; String[] columns = {"PL.name as Name", "PS.name as Position", "salary as Salary"}; String selection = "salary < ?"; String[] selectionArgs = {"40000"}; cursor = sqLiteDatabase.query(table, columns, selection, selectionArgs, null, null, null); logCursor(cursor); cursor.close(); Log.d(LOG_TAG, "--- ---"); dbHelper.close(); } void logCursor(Cursor cursor) { if (cursor != null) { if (cursor.moveToFirst()) { String str; do { str = ""; for (String cn : cursor.getColumnNames()) { str = str.concat(cn + " = " + cursor.getString(cursor.getColumnIndex(cn)) + "; "); } Log.d(LOG_TAG, str); } while (cursor.moveToNext()); } } else Log.d(LOG_TAG, "Cursor is null"); } class DbHelper extends SQLiteOpenHelper { public DbHelper(Context context) { super(context, "myDb", null, 1); } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { Log.d(LOG_TAG, "---onCreate database---"); ContentValues contentValues = new ContentValues(); sqLiteDatabase.execSQL("create table position (" + "id integer primary key, " + "name text, " + "salary integer" + ");"); for (int i = 0; i < position_id.length; i++) { contentValues.clear(); contentValues.put("id", position_id[i]); contentValues.put("name", position_name[i]); contentValues.put("salary", position_salary[i]); sqLiteDatabase.insert("position", null, contentValues); } sqLiteDatabase.execSQL("create table people (" + "id integer primary key autoincrement, " + "name text, " + "posid integer" + ");"); for (int i = 0; i < people_name.length; i++) { contentValues.clear(); contentValues.put("name", people_name[i]); contentValues.put("posid", people_posid[i]); sqLiteDatabase.insert("people", null, contentValues); } } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } } } |