Lecture
In this lesson:
- read data from related tables
- use rawQuery
We have analyzed the query method in some detail. But they did not consider how to use it to perform queries for related tables . Let's create a simple application that will make a query from two tables and output the result to the log. The tables will be people and position . In the first (people) we will write a list of people , in the second (position) - a list of posts . And for each person in people the position id from position will be registered.
Create a project:
Project name: P0371_SQLiteInnerJoin
Build Target: Android 2.3.3
Application name: SQLiteInnerJoin
Package name: ru.startandroid.develop.p0371sqliteinnerjoin
Create Activity: MainActivity
We will not use the screen at all, so we don’t even touch main.xml. Open MainActivity.java and write the code:
package ru.startandroid.develop.p0371sqliteinnerjoin;
import android.app.Activity;
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.util.Log;
public class MainActivity extends Activity {
final String LOG_TAG = "myLogs";
// данные для таблицы должностей
int[] position_id = { 1, 2, 3, 4 };
String[] position_name = { "Директор", "Программер", "Бухгалтер", "Охранник" };
int[] position_salary = { 15000, 13000, 10000, 8000 };
// данные для таблицы людей
String[] people_name = { "Иван", "Марья", "Петр", "Антон", "Даша", "Борис", "Костя", "Игорь" };
int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };
/** Called when the activity is first created. */
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
// Подключаемся к БД
DBHelper dbh = new DBHelper(this);
SQLiteDatabase db = dbh.getWritableDatabase();
// Описание курсора
Cursor c;
// выводим в лог данные по должностям
Log.d(LOG_TAG, "--- Table position ---");
c = db.query("position", null, null, null, null, null, null);
logCursor(c);
c.close();
Log.d(LOG_TAG, "--- ---");
// выводим в лог данные по людям
Log.d(LOG_TAG, "--- Table people ---");
c = db.query("people", null, null, null, null, null, null);
logCursor(c);
c.close();
Log.d(LOG_TAG, "--- ---");
// выводим результат объединения
// используем rawQuery
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 > ?";
c = db.rawQuery(sqlQuery, new String[] {"12000"});
logCursor(c);
c.close();
Log.d(LOG_TAG, "--- ---");
// выводим результат объединения
// используем query
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 = {"12000"};
c = db.query(table, columns, selection, selectionArgs, null, null, null);
logCursor(c);
c.close();
Log.d(LOG_TAG, "--- ---");
// закрываем БД
dbh.close();
}
// вывод в лог данных из курсора
void logCursor(Cursor c) {
if (c != null) {
if (c.moveToFirst()) {
String str;
do {
str = "";
for (String cn : c.getColumnNames()) {
str = str.concat(cn + " = " + c.getString(c.getColumnIndex(cn)) + "; ");
}
Log.d(LOG_TAG, str);
} while (c.moveToNext());
}
} else
Log.d(LOG_TAG, "Cursor is null");
}
// класс для работы с БД
class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, "myDB", null, 1);
}
public void onCreate(SQLiteDatabase db) {
Log.d(LOG_TAG, "--- onCreate database ---");
ContentValues cv = new ContentValues();
// создаем таблицу должностей
db.execSQL("create table position ("
+ "id integer primary key,"
+ "name text," + "salary integer"
+ ");");
// заполняем ее
for (int i = 0; i < position_id.length; i++) {
cv.clear();
cv.put("id", position_id[i]);
cv.put("name", position_name[i]);
cv.put("salary", position_salary[i]);
db.insert("position", null, cv);
}
// создаем таблицу людей
db.execSQL("create table people ("
+ "id integer primary key autoincrement,"
+ "name text,"
+ "posid integer"
+ ");");
// заполняем ее
for (int i = 0; i < people_name.length; i++) {
cv.clear();
cv.put("name", people_name[i]);
cv.put("posid", people_posid[i]);
db.insert("people", null, cv);
}
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
} Parse the code. First, there are several data arrays for the tables. Please note that for posts we will indicate id when filling in the tables. This is done so that we know these numbers and can use them in the table of people to indicate the id of the post.
In the Activity onCreate method, we create an object for managing the database and connect to the database. Next, using a query that is familiar to us, we output to the log data from the position and people tables.
To output table joins, use rawQuery. This is a simple method that accepts an SQL query and an argument list for the WHERE clause (if necessary). We formed a request to merge the two tables and display the name , position and salary of the person. Sampling condition: GP must be greater than 12000 . We used arguments to form a condition.
Next, we display the table join again, but we use the usual query . In the table we write all the tables, their aliases and the JOIN condition. In the columns - all the necessary fields using aliases. Well, in the selection and selectionArgs we write the condition of the selection - the RFP is less than 12000 .
Our logCursor method gets a Cursor at the input and logs all the content . Everything is familiar with past lessons.
In the DBHelper class, the creation of tables is coded and, this time, their data is filled here.
All save and run. We look at the log:
--- onCreate database ---
--- Table position ---
id = 1; name = Director; salary = 15000;
id = 2; name = Programmer; salary = 13000;
id = 3; name = Accountant; salary = 10,000;
id = 4; name = Security Guard; salary = 8000;
--- ---
--- Table people ---
id = 1; name = Ivan; posid = 2;
id = 2; name = Marya; posid = 3;
id = 3; name = Peter; posid = 2;
id = 4; name = Anton; posid = 2;
id = 5; name = Dasha; posid = 3;
id = 6; name = Boris; posid = 1;
id = 7; name = Kostya; posid = 2;
id = 8; name = Igor; posid = 4;
--- ---
Led the data on the tables separately.
--- INNER JOIN with rawQuery ---
Name = Ivan; Position = Programmer; Salary = 13,000;
Name = Peter; Position = Programmer; Salary = 13,000;
Name = Anton; Position = Programmer; Salary = 13,000;
Name = Boris; Position = Director; Salary = 15,000;
Name = Kostya; Position = Programmer; Salary = 13,000;
--- ---
Derived data from rawQuery. ZP> 12,000
--- INNER JOIN with query ---
Name = Marya; Position = Accountant; Salary = 10,000;
Name = Dasha; Position = Accountant; Salary = 10,000;
Name = Igor; Position = Security Guard; Salary = 8000;
--- ---
Output data from query. ZP <12000
As you can see, queries from related tables in SQLite are not a problem and are no different from ordinary databases.
What to use rawQuery or query is up to you and depends on the situation. Although, offhand, I can not think of the advantages of one over the other in a given situation. But probably they are.
The data on salaries and positions are fictional, any coincidence is random. And, of course, I have nothing against accountants and security guards))
In the next lesson:
- we use transactions when working with the database
Comments
To leave a comment
Mobile Programming (Android IOs)
Terms: Mobile Programming (Android IOs)