Урок 37. Запросы из связанных таблиц. INNER JOIN в SQLite. Метод rawQuery.

[damager82]

В этом уроке:

- читаем данные из связанных таблиц
- используем rawQuery

Click here to read this article!

[pantomimo]

Здравствуйте!
такой вопрос.

Вы закрываете базу dbh.close(); а нужно ли закрывать db.close(); ???

Спасибо!

[damager82]

Вы закрываете базу dbh.close(); а нужно ли закрывать db.close(); ???

Добрый день!
В dbh.close() уже выполняется db.close(), поэтому не нужно. Посмотрите следующий урок №38, там в конце я это расписал с примером.

[pantomimo]

Вы закрываете базу dbh.close(); а нужно ли закрывать db.close(); ???

Добрый день!
В dbh.close() уже выполняется db.close(), поэтому не нужно. Посмотрите следующий урок №38, там в конце я это расписал с примером.

спасибо! все отлично расписано! чуть поспешил с вопросом...)

[irukanji]

Спасибо огромное за уроки... Если есть возможность у вас, не могли бы вы поподробнее остановиться на обьяснении как составлять SQL-запросы, а именно что все это значит --->

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 > ?";
или где можно об этом почитать? Спасибо.

[яфпкк]

ссылка на официальный ман
ну а вообще то, что ты спрашиваешь, не имеет прямого отношения к андроиду. Берешь любую документацию по sql на русском языке и читаешь, синтаксис очень похож

[damager82]

На Java, SQL и прочие используемые технологии я стараюсь не отвлекаться. Все таки сайт посвящен андроиду, по которому мало ресурсов в рунете.

Вам, действительно, имеет смысл поискать в сети SQL учебник какой-нибудь. Там для первого знакомства хватит одного дня. Например можно использовать этот: http://www.cs.ifmo.ru/education/documen ... ndex.shtml

Если разбирать текущий фрагмент:

"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 > ?";

Здесь мы таблице people даем псевдоним PL (2-я строка), а таблице position - псевдоним PS (3-я строка). Это нужно просто для уменьшения кол-ва букв.
Во второй и третьей строке мы говорим, что хотим связать две таблицы и использовать данные из обоих (from и inner join). Связывать будем по условию в 4-й строке. И должно учитываться, что salary (5-я строка) больше параметра, который мы укажем далее. В итоге на выходе мы получим имя человека (PL.name), наименование должности (PS.name) и зарплату (salary) - это все мы указываем в первой строке (select).
В отличие от name здесь salary используется без PL. или PS., потому что оно только в одной из таблиц и система сама его найдет.

[KamiSempai]

...синтаксис очень похож

Как человек продолжительное время программировавший под MS SQL, могу сказать, что похож он не везде. Я был приятно удивлен, когда SELECT TOP 1... почему-то не захотел работать.
А вот ссылка на оф.сайт действительно полезная штука. Только боюсь поможет она, если есть хоть какие то познания в SQL.

[irukanji]

На Java, SQL и прочие используемые технологии я стараюсь не отвлекаться. Все таки сайт посвящен андроиду, по которому мало ресурсов в рунете.

Вам, действительно, имеет смысл поискать в сети SQL учебник какой-нибудь. Там для первого знакомства хватит одного дня. Например можно использовать этот: http://www.cs.ifmo.ru/education/documen ... ndex.shtml

Если разбирать текущий фрагмент:

"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 > ?";

Здесь мы таблице people даем псевдоним PL (2-я строка), а таблице position - псевдоним PS (3-я строка). Это нужно просто для уменьшения кол-ва букв.
Во второй и третьей строке мы говорим, что хотим связать две таблицы и использовать данные из обоих (from и inner join). Связывать будем по условию в 4-й строке. И должно учитываться, что salary (5-я строка) больше параметра, который мы укажем далее. В итоге на выходе мы получим имя человека (PL.name), наименование должности (PS.name) и зарплату (salary) - это все мы указываем в первой строке (select).
В отличие от name здесь salary используется без PL. или PS., потому что оно только в одной из таблиц и система сама его найдет.

Спасибо за ответ... Был далек от SQL теперь пришлось нырнуть...

[яфпкк]

SELECT TOP 1... почему-то не захотел работать.

это наверное единственное отличие в синтаксисе (имею ввиду обычные селекты), не знаю как в sqlite, но в mysql это записывается так
select * from table1 limit 10, например

[icamys]

Сам столкнулся с трудностями в изучении последних глав, связанных с БД. После прочтения пятой главы книги "Using SQLite 2010" Jay Kreibich все разъяснилось. У кого тоже были трудности, очень советую почитать вышеупомянутую главу из книги.

[Andy_NTG]

Как в rawQuery использовать множественные условия в WHERE?

String selection = "SELECT * FROM TABLE WHERE id=? AND data=? AND text=?
String[] selectionArgs = { id, data, text};

Cursor c = db.rawQuery(selection, selectionArgs);

Вот так не работает почему-то. Даже если selection заменить явно на строку

SELECT * FROM TABLE WHERE id='123' AND data='123'

а вторым агрументом указать null, то происходит то же самое. В SQLite нельзя использовать AND разве?

[damager82]

В SQLite нельзя использовать AND разве?

Должно работать. Скорее всего выборка по таким условиям действительно ничего не возвращает. Проверьте данные в таблице.
Если указываете where с одним аргументом то работает?

[KamiSempai]

id и data какого типа? Если числового, кавычки нужно убрать.

[Andy_NTG]

Да, всё работает, где-то в синтаксисе была ошибка.

Но вот есть новая проблема. SQLite не поддерживает поля календарного, так сказать, типа. То есть сортировку по дате не сделать, получается. Пробую сохранать в базу значения типа YYYY-MM-DD HH:MM:SS.SSS, но по ним почему-то не происходит корректная сортировка.

P.S. Перевёл в поле типа int и записал туда время в миллисекундах. Теперь сортировка работает.

[KamiSempai]

Но вот есть новая проблема. SQLite не поддерживает поля календарного, так сказать, типа. То есть сортировку по дате не сделать, получается. Пробую сохранать в базу значения типа YYYY-MM-DD HH:MM:SS.SSS, но по ним почему-то не происходит корректная сортировка.

P.S. Перевёл в поле типа int и записал туда время в миллисекундах. Теперь сортировка работает.

Не может быть такого. Сам использую вышеописанный формат даты, только без милисекунд, и все отлично сортируется. Каким образом Вы переводите дату в строку?

[beeline09]

Добрый день всем! Есть проблема. Бьюсь уже три дня и не могу понять в чем дело...
В общем, есть база у меня data.db. В ней есть куча таблиц (база не моя, а готовая с инета).
Мне нужна таблица food, в которой есть столбцы "id, title, proteins, fats, carbohydrates, water, energy". Это не все столбцы, но мне нужны только эти.
Мне нужно организовать поиск по строкам столбца title. Идея такая. Ввожу в edittext часть слова или слово, которое хочу найти. Нажимаю на баттон и нижу мой listview должен заполниться строками из "title, proteins, fats, carbohydrates, water, energy", в которых titlе равен или содержит текст из edittext-а.
Делал такой запрос:

Код: Выделить всё

EditText search_prod = (EditText) findViewById(R.id.et_dob_prod);
				lv_product = (ListView) findViewById(R.id.lv_dob_prod);
				String[] arrayOfString = new String[1];
		        arrayOfString[0] = String.valueOf(search_prod.getText().toString());
				try {
					
					String [] selectionArgs = {search_prod.getText().toString() + "%"};
					Cursor cursor;
					// Wrap the next line in try-catch
					cursor = database.rawQuery("select title, proteins, fats, carbohydrates, water, energy from food where food.title = ?", arrayOfString);
					
				if (cursor.moveToFirst()) {
					do {
						Toast.makeText(getApplicationContext(), cursor.getString(0), Toast.LENGTH_LONG).show();
						// list1.add(cursor.getString(0));
						RowItemProduct item2 = new RowItemProduct(
								R.drawable.dieta, cursor.getString(0), cursor
										.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(5));

						rowItems_product.add(item2);

					} while (cursor.moveToNext());
				}
				if (cursor != null && !cursor.isClosed()) {
					cursor.close();
				}
				}catch (Exception e) {
					// TODO: handle exception
					Log.d(LOG_TAG, e.toString());
				}

				rowItems_product = new ArrayList<RowItemProduct>();

				CustomLVAdapterProduct adapter1 = new CustomLVAdapterProduct(
						context, R.layout.list_item_product, rowItems_product);
				lv_product.setAdapter(adapter1);

			}

Но он не дает результатов... Уже не знаю, что и делать
В БД я вообще нуб, но есть необходимость реализовать. Когда-то в университете имел дело с access в делфи, но там другое и уже много забыл...
Вот скрин структуры базы:

Скрин базы на компе

ScreenShot2222.png (68.26 КБ) 26441 просмотр

Пожалуйста помогите мне. Я перерыл весь интернет, перепробовал кучу вариантов, но ничего не выходит

[Foenix]

Знак процента можно с обоих сторон добавлять.
Второе, тут поиск с русскими буквами будет глючить. Я видела такую реализацию. В таблицу заносят данные, и еще делают столбик с данными только большими буквами. И ищут по нему, предварительно приведя и строку в большие буквы.
Другой способ - ищи в интернете, сильно заморочистый, чтоб подружить sqlite с русскими буквами.
Хотя что значит у тебя "не ищет". Вообще ничего не выдает, что ли?
распечатй запрос в лог, возьми базу, какой-нибудь редактор, открой ее там, внеси запрос из лога и посмотри что выдается. Тестировать запросы луше так.

[Foenix]

и кстати, нужно писать не знак равенства в запросе, а оператор LIKE.
Равенство нормально работает с любыми буквами вроде, но так в результатах запроса у тебя вряд ли что-то будет.

[beeline09]

Знак процента можно с обоих сторон добавлять.
Второе, тут поиск с русскими буквами будет глючить. Я видела такую реализацию. В таблицу заносят данные, и еще делают столбик с данными только большими буквами. И ищут по нему, предварительно приведя и строку в большие буквы.
Другой способ - ищи в интернете, сильно заморочистый, чтоб подружить sqlite с русскими буквами.
Хотя что значит у тебя "не ищет". Вообще ничего не выдает, что ли?
распечатй запрос в лог, возьми базу, какой-нибудь редактор, открой ее там, внеси запрос из лога и посмотри что выдается. Тестировать запросы луше так.

и кстати, нужно писать не знак равенства в запросе, а оператор LIKE.
Равенство нормально работает с любыми буквами вроде, но так в результатах запроса у тебя вряд ли что-то будет.

Сделал вот так:

Код: Выделить всё

cursor = database.rawQuery("SELECT title, proteins, fats, carbohydrates, water, energy FROM food WHERE food.title LIKE ?", arrayOfString);

Когда ввожу с большой буквы, то в listview у меня появляется один результат, в котором title четко совпадает с текстом из edittext. Ну с регистром букв, я думаю, уже реальнее разобраться. Но вот я хочу, чтоб поиск был не по началу названия, а по всей строке из title чтоб искалось наличие текста из edittext. Такое реально вообще сделать? Я видел подобную реализацию в приложении Диетический калькулятор, но к сожалению исходников нет нигде... Вы мне можете подсказать, что сделать? Как сделать такой запрос, который выдаст не строго строчку, а строчку в которой присутствует нужный текст?

UPD1: Проблема решена! Спасибо за подсказку на счет процента! Сделал вот так :

Код: Выделить всё

arrayOfString[0] = String.valueOf("%"+search_prod.getText().toString()+"%");

и начало искать все совпадения! Круто! ))) Теперь буду искать на счет регистронезависимого поиска. Если есть идеи, то и тут попрошу помощи

UPD2: Поиск по интернету не дал результатов. Решил сам попробовать. В итоге решил сделать два запроса к базе. Один начинается с большой буквы, второй - с маленькой. База достаточно большая, но на нагрузке устройства это не сказывается. Может кому пригодится в будущем. Вот реализация:

Код: Выделить всё

EditText search_prod = (EditText) findViewById(R.id.et_dob_prod);
lv_product = (ListView) findViewById(R.id.lv_dob_prod);
String[] arrayOfString = new String[1];
String[] arrayOfString1 = new String[1];
String verh_registr = search_prod.getText().toString();
verh_registr = verh_registr.substring(0, 1).toUpperCase()+verh_registr.substring(1);
String nizh_registr = verh_registr;
nizh_registr = nizh_registr.substring(0, 1).toLowerCase()+nizh_registr.substring(1);
arrayOfString[0] = String.valueOf("%"+verh_registr+"%");
arrayOfString1[0] = String.valueOf("%"+nizh_registr+"%");
Cursor cursor;
Cursor cursor1;
rowItems_product = new ArrayList<RowItemProduct>();	
cursor = database.rawQuery("SELECT title, proteins, fats, carbohydrates, water, energy FROM food WHERE food.title LIKE ?", arrayOfString);
cursor1 = database.rawQuery("SELECT title, proteins, fats, carbohydrates, water, energy FROM food WHERE food.title LIKE ?", arrayOfString1);

cursor.moveToFirst();
while (!cursor.isAfterLast()) {
       	RowItemProduct item2 = new RowItemProduct(R.drawable.dieta, cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(5));
       	rowItems_product.add(item2);
	cursor.moveToNext();
					}
cursor1.moveToFirst();
while (!cursor1.isAfterLast()) {
	RowItemProduct item21 = new RowItemProduct(R.drawable.dieta, cursor1.getString(0), cursor1.getString(1), cursor1.getString(2), cursor1.getString(3), cursor1.getString(5));
	rowItems_product.add(item21);
	cursor1.moveToNext();
					}
CustomLVAdapterProduct adapter1 = new CustomLVAdapterProduct(context, R.layout.list_item_product, rowItems_product);
lv_product.setAdapter(adapter1);

Всем, кто помогал большое спасибо! Надеюсь данная реализация пригодится в будущем таким же постигающим данный путь как и я