I work in Android Studio. For my project, I need to use the update() method to select the first item in my database (pts). How to do this I do not understand. Tried it through:

 mSqLiteDatabase.update( "pts", values, DatabaseHelper._ID + " = ?", new String[] { Integer.toString(0) } );

It does not work. Likely because the first element with _id=0 is not necessary at all. But how to choose the first element?

  • Updating a string with ID = 0 should look something like this: mSqLiteDatabase.update( "pts", values, DatabaseHelper._ID + " = ?", new String[] { 0 } ); - pavlofff
  • I tried. Same. Perhaps my mistake is in incorrect data retrieval from the database, since when all cells in the column change, it works .. Ps mSqLiteDatabase.update( "pts", values, DatabaseHelper._ID + " = ?", new String[] { Integer.toString(0) } ); - Mr Klonwar
  • In general, yes, ID = 0 will not necessarily be the first entry in the sample, it is better to receive directly by ID / By the way, if the text displays, which one? - pavlofff
  • And how is it done?) With the last two parameters, null mSqLiteDatabase.update( "pts", values, null, null ); Displays exactly what I wrote down in values, but if you write _id = 0 , or something, then for some reason it shows another DB cell - Mr Klonwar
  • Here is an example of a query with a selection by a specific ID. You write that the text is displayed, but not the one, but which one? - pavlofff

1 answer 1

The DB device is such that it does not bind to ordinal rows, such as an array, and you cannot get write access to the first row in the table in the usual way. This is the architecture of the database and this may seem to be a problem, but in reality this is not the case, since the database works somewhat differently.

The identifier, although it is an auto-incrementing value, cannot serve as a sequential index, since when deleting records from a table, they are simply deleted and the identifiers of the rows following the deleted record do not change. That is, when the third record is deleted, the identifiers will have the following order: 0, 1, 3, 4 ... When adding an entry, the next in order identifier is assigned.
A separate column will not solve this problem either, since deleting it will happen the same as with ID, and rewriting all the values ​​of this column with each deletion is extremely inefficient.

What to do. And the solution is simple - to update the record in the table, you first had to read it, the identifier of this record will be obtained accordingly, you need to save it, and when you update the record you just need to specify the same identifier.

If you need to get access to the first record by all means (although if you work correctly with the database, it is difficult for me to think of why this may be necessary and the way suffers from disadvantages), then you can use a crutch, which consists of the following:

  1. Make a selection in the cursor over the entire table.
  2. Set the cursor to the first position.
  3. Read this post ID
  4. Use the received ID to update the first entry.

It is worth noting that working with the database is a rather complicated discipline, which was singled out as a separate science - the DBMS and with a swoop you can only spoil everything. In order not to experience problems with this science and work with data stored in the database, this science should be studied.
As a start, I can offer a wonderful book that is very easy and interesting to read and gives a complete initial understanding of the discipline of the database. After reading it, you will discover a lot of interesting and not quite obvious when working with data in the database. The book is called: Bailey L. - Exploring SQL (O'Reilly Bestsellers) - 2012. - You can find it in the public domain. The book is about pure SQL and the organization of work with the database and the creation of its structure, but the application of the stated knowledge to a specific database, like SQLite, will not make any problems.

  • Tried to do so, gives that _ID = null cursor.moveToFirst(); Toast.makeText(findViewById(R.id.chart).getContext(), ""+cursor.getString(cursor.getColumnIndex(DatabaseHelper._ID)), Toast.LENGTH_LONG).show(); cursor.moveToFirst(); Toast.makeText(findViewById(R.id.chart).getContext(), ""+cursor.getString(cursor.getColumnIndex(DatabaseHelper._ID)), Toast.LENGTH_LONG).show(); - Mr Klonwar
  • What's happening? I tried to display all id ces commas, only one was printed. With another field as well. Do I only have one field? But why _id = null ? Ps Thanks for the books, I read - Mr Klonwar
  • Iii .. Yes! I managed! Thanks you! - Mr Klonwar
  • What was the problem then? - pavlofff
  • Problem - I have not correctly recorded DATABASE_CREATE_SCRIPT in DatabaseHelper . It turned out that _id was a String type. I rewrote the database correctly and everything worked according to your algorithm. - Mr Klonwar