Android sqlite update all

Полный список

— используем методы update и delete с указанием условия

На прошлом уроке мы разобрали, как вставить запись, считать все записи из таблицы и очистить таблицу. Теперь посмотрим, как обновить и удалить конкретную запись.

Новый проект создавать не будем, используем P0341_SimpleSQLite с прошлого урока. Немного поменяем экран, добавим поле для ввода ID и кнопки для обновления и удаления.

По нажатию кнопки Update мы будем читать содержимое полей Name и Email, и обновлять запись в таблице, для которой id = значению из поля ID. По нажатию кнопки Delete будем удалять запись из таблицы по id = значению из поля ID. Экран получился, конечно, не самый лучший с точки зрения эргономики и юзабилити, но у нас тут не кружок юных дизайнеров, а серьезное изучение БД.

Подредактируем MainActivity.java. Добавим описание и определение новых экранных элементов, присвоение обработчиков для кнопок.

(Обращайте внимание только на выделенные строки)

Теперь дополним реализацию onClick:

(Добавляете только выделенный текст)

Мы добавляем переменную id, пишем в нее значение поля etID. В switch добавляем две новые ветки:

btnUpd – обновление записи в mytable. Проверяем, что значение id не пустое, заполняем cv данными для апдейта и обновляем запись. Для этого используется метод update. На вход ему подается имя таблицы, заполненный ContentValues с значениями для обновления, строка условия (Where) и массив аргументов для строки условия. В строке условия я использовал знак ?. При запросе к БД вместо этого знака будет подставлено значение из массива аргументов, в нашем случае это – значение переменной id. Если знаков ? в строке условия несколько, то им будут сопоставлены значения из массива по порядку. Метод update возвращает нам кол-во обновленных записей, которое мы выводим в лог.

btnDel – удаление записи из mytable. Проверяем, что id не пустое и вызываем метод delete. На вход передаем имя таблицы, строку условия и массив аргументов для условия. Метод delete возвращает кол-во удаленных строк, которое мы выводим в лог.

Обратите внимание, что условия и для update и для delete у меня одинаковые, а именно id = значение из поля etID. Но реализовал я их немного по-разному. Для update использовал символ ? в строке условия и массив аргументов. А для delete вставил значение сразу в строку условия. Таким образом, я просто показал способы формирования условия. А вы уже используйте тот, что больше нравится или лучше в конкретной ситуации.

Все сохраним и запустим. На прошлом уроке мы добавляли пару записей, но потом их удалили. Так что я буду добавлять снова. Добавлю пару записей, нажму Read, вижу в логе:

ID = 3, name = Ivan Petrov, email = ipetrov @abc.com
ID = 4, name = Anton Sidorov, email = asidorov @def.com

Теперь попробуем обновить запись с ID=3. Для этого вводим 3 в поле ID и новые данные в поля Name и Email:

Жмем Update, смотрим лог:

— Update mytable: —
updated rows count = 1

обновилась одна запись, все верно.

Нажмем Read и убедимся в этом. Лог:

ID = 3, name = Petr Ivanov, email = pivanov @abc.com
ID = 4, name = Anton Sidorov, email = asidorov @def.com

Запись действительно обновилась.

Теперь давайте удалим запись с ID = 4. Вводим 4 в поле ID

Жмем Delete, смотрим лог:

— Delete from mytable: —
deleted rows count = 1

одна запись удалена.

Жмем Read, смотрим лог:

— Rows in mytable: —
ID = 3, name = Petr Ivanov, email = pivanov @abc.com

осталась одна запись.

Если попробовать удалить запись с пустым полем ID, то ничего не будет, т.к. мы реализовали проверку. Если же попробовать удалить запись с несуществующим ID, то метод delete вернет 0. Т.е. ничего не было удалено, т.к. не нашлось записей для удаления.

Теперь вы умеете читать, вставлять, удалять и изменять записи в SQLite.

Полный код MainActivity.java:

На следующем уроке:

— подробно разбираем метод чтения данных query
— используем сортировку, группировку, условия, having

Присоединяйтесь к нам в Telegram:

— в канале StartAndroid публикуются ссылки на новые статьи с сайта startandroid.ru и интересные материалы с хабра, medium.com и т.п.

— в чатах решаем возникающие вопросы и проблемы по различным темам: Android, Kotlin, RxJava, Dagger, Тестирование

— ну и если просто хочется поговорить с коллегами по разработке, то есть чат Флудильня

— новый чат Performance для обсуждения проблем производительности и для ваших пожеланий по содержанию курса по этой теме

Источник

How to Update Data to SQLite Database in Android?

We have seen How to Create and Add Data to SQLite Database in Android as well as How to Read Data from SQLite Database in Android. We have performed different SQL queries for reading and writing our data to SQLite database. In this article, we will take a look at updating data to SQLite database in Android.

What we are going to build in this article?

We will be building a simple application in which we were already adding as well as reading the data. Now we will simply update our data in a new activity and we can get to see the updated data. A sample video is given below to get an idea about what we are going to do in this article. Note that we are going to implement this project using the Java language.

Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more, please refer Complete Interview Preparation Course.

Step by Step Implementation

Step 1: Creating a new activity for updating our course

As we want to update our course, so for this process we will be creating a new activity where we will be able to update our courses in the SQLite database. To create a new Activity we have to navigate to the app > java > your app’s package name > Right click on package name > New > Empty Activity and name your activity as UpdateCourseActivity and create new Activity. Make sure to select the empty activity.

Источник

SQLite Database Update in Android

Please follow the below steps in order to update data in SQLite database using our Android app:

Step 1) First add one more button and this button we are going to use to update the data, so I’m going to change the name of the button as an update.

Step 2) And one more thing we want to do is, if you remember our database table contains four columns. First one was id, the second one was name, surname and the marks scored by the students. And to update the data we need a unique reference of the row so that we can tell SQLite that we want to delete the data from this row.

Because names can be same so if you want to change the name of one partner which shares the name with the other person then the data can be changed for both the names if you don’t know what is the unique you know row or unique column in this, So id we have defined as a unique column for this database table.

Step 3) Now we need to create a function in our database helper .javaclass which we have created in the last tutorial so go to the database helper .javafile and in here till now we have created insert data and get all data function right. Now we will create one more function here and we will call it as updateData.

So it will be public and it’ll return boolean and It’s going to take four arguments. The First argument we are going to pass here will be string id, second is string name, third is string surname and fourth is string marks.

basically, we want to pass all the four column data to it. Because our table will contain four columns, id, name, surname and marks right.

Step 4) Now in here what we’re going to do is, as we have done inserting data function. First of all, we will create SQLite database instance and then we are going to create an instance of content value.

Step 5) And then as we have done in this insert data function that we’re putting whatever data is passed from the argument to this content value using put. So let’s do it and one more column we need to add here and this will be for column 1 and its id in our case right. Because this contains extra id here. The only difference in update data functions and insert data function is, the method we are going to use now.

Step 6) So just take your database instance now and then call update here. And this update will update whatever argument you pass here. The first argument it takes is the name of your table okay. So give the name if you remember. we have declared table name here right so first argument is the table name right. Second is the content value. So it takes the second argument as a content value so we take this instance of content value and pass it here. The third argument is the condition which we want to pass here. So what we want to do, we want to update data on the basis of for example id because id is our unique primary key here and on the basis of primary key we can differentiate data. So in here we will write So we will you know ask whatever id is passed using this id parameter we are going to query this and fourth is the value on the basis of which we want to update our data so this is the column name on the basis of which we want to update the data and our column name is id so you can just write here column name, id is equal to and then in here, fourth argument. Lets go inside this update, so hover over control and click and see it takes the fourth argument as the string array right. So we will go back and pass string array here right.

And if everything goes well, we want to return, return to know that if the data is really updated or not.

Step 7) Now this is done, what we are going to do is we are going to move to our main activity .javafile and in here we will declare one more button variable which will be our button for update so button update and then we are going to take this button instance and we will cast it as a button using the id.

So go to the onCreate method where you have type casted all other tools or widgets and cast this button.

Источник

Android SQLite Insert or Update

as can be seen in the documentation the syntax to make insert or update is : INSERT OR REPLACE INTO

( ) VALUES ( ) , my question is there any function that merge the following ?

or it has to be done with a prepared SQL statement and rawQuery?

What’s the best practices to do an insert or update in Android?

9 Answers 9

I believe that you are asking how to INSERT new rows or UPDATE your existing rows in one step. While that is possible in a single raw SQL as discussed in this answer, I found that it easier to do this in two steps in Android using SQLiteDatabase.insertWithOnConflict() using CONFLICT_IGNORE for conflictAlgorithm.

This example assumes that the table key is set for column «_id», that you know the record _id, and that there is already row #1 (_id=1, columnA = «valueA», columnB = «valueB»). Here is the difference using insertWithOnConflict with CONFLICT_REPLACE and CONFLICT_IGNORE

• CONFLICT_REPLACE will overwrite existing values in other columns to null (ie. columnB will become NULL and the result will be _id=1, columnA = «valueNEW», columnB = NULL). You lose existing data as result and I do not use it in my code.
• CONFLICT_IGNORE will skip the SQL INSERT for your existing row #1 and you will SQL UPDATE this row in the next step preserving the content of all other columns (ie. the result will be _id=1, columnA = «valueNEW», columnB = «valueB»).

When you attempt to insert new row #2 which does not exist yet, the code will only execute the SQL INSERT in the first statement insertWithOnConflict (ie. the result will be _id=2, columnA = «valueNEW», columnB = NULL).

Beware of this bug which is causing SQLiteDatabase.CONFLICT_IGNORE to malfunction on API10 (and probably API11). The query is returning 0 instead of -1 when I test on Android 2.2.

If you do not know the record key _id or you have a condition that will not create a conflict, you can reverse the logic to UPDATE or INSERT. This will keep your record key _id during UPDATE or create a new record _id during INSERT.

The above example assumes that your just want to UPDATE timestamp value in the record for example. If you call insertWithOnConflict first, INSERT will create new record _id due to the difference in the timestamp condition.

Источник

SQLite (Android) : UPDATE query with ORDER BY

Android, SQLite : I want to insert rows in between other rows in myTable using SQLite in android. For this, I am trying to increment ids of the all rows starting say row 3. So that I can insert a new row at position 3.

The primary key of myTable is column id. There are no other constraints in the table.

I have tried using the query mentioned in https://stackoverflow.com/a/9177264/6671004. This query does work in mySQL but not in Android (SQLite)

Here’s the line of code :

Here’s the error I’m getting on Android Studio (Compile time) : https://imgur.com/a/9r0iyAa

This is the exception I’m getting if I remove ‘ORDER BY id DESC’ from the query :

java.lang.RuntimeException: Unable to start activity ComponentInfo<. >: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: myTable.id (code 1555)

Is this the correct way to do this? Or is there a better way?

2 Answers 2

As pointed out by many, this is definitely not the correct way to go.

But I found workaround in case someone else is looking for a similar implementation.

This is a hack which I found here.

Again, this is not the correct way to go. But just posting a working solution I found.

I have tried using the query mentioned in https://stackoverflow.com/a/9177264/6671004. This query does work in mySQL but not in Android (SQLite)

That question is tagged MYSQL. MYSQL has many differences from SQLite.

database.execSQL(«UPDATE myTable SET + 1) where id > 2 ORDER BY id desc»);

The SQLite UPDATE SQL takes the form of :-

• i.e. there is no ORDER BY clause and hence the error saying that if you’re going to use any KEYWORD then it must be a BETWEEN or IN or a ; (of course you could also extend the condition using AND OR and so on).

This is the exception I’m getting if I remove ‘ORDER BY id DESC’ from the query :

The reason being is that the rowid (id being an alias of rowid) has an implied UNIQUE constraint and that the rows will be updated according to the id column in order. So if there are more than 3 rows (or have been and the last row has never been deleted) then when the id is 3, a row with 4 will exist and 3 + 1 = 4 so the row already exists and hence the UNIQUE constraint being encountered.

I want to insert rows in between other rows in myTable using SQLite in android. For this, I am trying to increment ids of the all rows starting say row 3. So that I can insert a new row at position 3.

In short that is not a good idea and is virtually definitely not needed.

Is this the correct way to do this? Or is there a better way?

Definitely no

At a guess you want a nice humanly understandable value so you can know what’s going on. For example you may present a list with the sequence so you can then say delete the row that has a sequence of 3 and thus equate that to the id column. Fine until you present the list in a different order which may be more user friendly. Suddenly your sequence becomes complicated or even useless and if displayed confusing.

identifiers are intended to identify a row and allow fast access to that row as a numerical index will be more efficient (than a human easily readable non-numeric index) to process. They also cater for reducing unnecessary additional processing (shifting data).

An efficient methodology is presenting the list with tying the id to the position in the list (so position could be the nth element of an array that holds the respective id, regenerating the list (the underlying array) according to a different order so the nth element will still have the respective id).

Embarking on a process of shifting rows will impose additional resource usage for example extra disk IO whose cost is relatively expense. This will be compounded by the fact that you will have to process the rows in reverse order to get around the UNIQUE constraint, that in itself is going to require using even costlier methods because SQLite will by default try to perform the updates efficiently rather than cater for the efficiencies required to digress from recognised/accepted techniques that utilise the underlying efficiencies.

Источник