Detailed explanation of SQLite usage in Android

Detailed explanation of SQLite usage in Android

Now mainstream mobile devices such as Android and iPhone use SQLite as the storage engine of complex data. When we develop applications for mobile devices, we may use SQLite to store a large amount of data, so we need to master the SQLite development skills on mobile devices. For the Android platform, the system has built-in rich APIs for developers to operate SQLite, and we can easily access the data.

Let's introduce the common operation methods of SQLite. For convenience, I wrote the code in oncreate of activity:

After executing the above code, the system will generate a "test. DB" database file in the / data / data / [package_name] / databases directory, as shown in the figure:

The above code basically covers most of the database operations; For addition, update and deletion, we can use

In addition to the unified form, they also have their own operation methods:

The first parameter of the above three methods represents the table name to be operated; The second parameter in insert indicates that if each column of the inserted data is empty, the name of a column in this row needs to be specified. The system sets this column to null to avoid errors; The third parameter in insert is a variable of type contentvalues, which is a map composed of key value pairs. Key represents the column name and value represents the value to be inserted into the column; The second parameter of update is similar, except that it updates the field key to the latest value value. The third parameter whereclause represents the where expression, such as "age >? And age <?". The last whereargs parameter is the actual parameter value of the placeholder; The same is true for the parameters of the delete method.

Let's talk about query operations. The query operation is more complex than the above operations. Because we often face a variety of query conditions, the system also considers this complexity and provides us with rich query forms:

The above are common query methods. The first is the simplest. Organize all SQL statements into a string and use placeholders instead of actual parameters. Selectionargs is the set of placeholder actual parameters; The following parameters are similar: columns represents all name sets of columns to be queried, selection represents conditional statements after where, placeholders can be used, groupby specifies the column name of grouping, having specifies the grouping condition, which can be used in combination with groupby, orderby specifies the column name of sorting, limit specifies the paging parameter, and distinct can specify "true" or "false" Indicates whether to filter duplicate values. It should be noted that the parameters selection, groupby, having, orderby and limit do not include SQL keywords such as "where", "group by", "having", "order by" and "limit".

Finally, they return a cursor object at the same time, representing the cursor of the data set, which is somewhat similar to the resultset in Java se.

The following are common methods for cursor objects:

In the above code example, some of these common methods have been used. For more information, you can refer to the instructions in the official documents. Finally, when we finish the operation on the database, remember to call the close () method of sqlitedatabase to release the database connection, otherwise sqliteexception is easy to occur.

The above is the basic application of SQLite, but in actual development, in order to better manage and maintain the database, we will package a database operation class inherited from sqliteopenhelper class, and then package our business logic methods based on this class.

Next, we will explain the specific usage with an example. We will create a new project named dB with the following structure:

Dbhelper inherits sqliteopenhelper. As the base class for maintaining and managing databases, dbmanager is built on dbhelper and encapsulates common business methods. Person is the JavaBean corresponding to our person table, and mainactivity is the interface we display.

Let's take a look at dbhelper first:

As mentioned above, oncreate method will be called when the database is created for the first time. We can execute the statement to create a table. When the system finds a version change, it will call onupgrade method. We can execute statements such as modifying the table structure.

In order to facilitate our object-oriented use of data, we create a person class corresponding to the fields in the person table, as follows:

Then, we need a dbmanager to encapsulate all our business methods. The code is as follows:

We instantiate dbhelper in the dbmanager construction method and obtain a sqlitedatabase object as the database instance of the whole application; When adding multiple person information, we adopt transaction processing to ensure data integrity; Finally, we provide a closedb method to release database resources. This step is in

Our whole application is executed when it is closed. This link is easy to be forgotten, so friends should pay attention.

We use the getwritabledatabase () method to get the database instance. Maybe friends will have questions. Why do you choose the former as the database instance of the whole application in getwritabledatabase () and getreadabledatabase()? Here, I would like to focus on this point with you. Let's take a look at the getreadabledatabase () method in sqliteopenhelper:

In the getreadabledatabase () method, first judge whether a database instance exists and is open. If so, return the instance directly. Otherwise, try to obtain a database instance in read-write mode. If the acquisition fails when the disk space is full, open the database in read-only mode, obtain the database instance and return, Then assign a value to mdatabase as the newly opened database instance. Since it is possible to call the getwritabledatabase () method, let's take a look:

As you can see, several key steps are: first, judge whether the mdatabase is not empty, open it and return it directly if it is not in read-only mode; otherwise, lock it if it is not empty, then start to open or create the database, compare the versions, call the corresponding methods according to the version number, set the new version number for the database, and finally release the old mdatabase that is not empty and unlock it, Assign the newly opened database instance to mdatabase and return the latest instance.

After reading the above process, you may know a lot. If the disk space is not full, getreadabledatabase() will generally return the same database instance as getwritabledatabase(). Therefore, it is feasible to use getwritabledatabase() in the dbmanager construction method to obtain the database instance used by the whole application. Of course, if you are really worried that this will happen, you can use getwritabledatabase() to get the data instance first. If you encounter an exception, then try to use getreadabledatabase() to get the instance. Of course, the instance you get at this time can only be read but not written.

Finally, let's take a look at how to use these data operation methods to display data. The following is the layout file and code of mainactivity.java:

Here we need to pay attention to the application of simplecursoradapter. When we use this adapter, we must first get a cursor object. There are several problems: how to manage the life cycle of cursor, and what to pay attention to if packaging cursor and cursor result set.

If you manage the cursor manually, it will be very troublesome and there will be some risks. If you do not handle it properly, exceptions will occur during operation. Fortunately, activity provides us with the startmanagingcursor (cursor cursor) method, which will manage the current cursor object according to the life cycle of the activity. The following is the description of this method:

It is mentioned in the article that the startmanagingcursor method will manage the life cycle of the current cursor object according to the life cycle of the activity, that is, when the activity stops, it will automatically call the deactivate method of cursor and disable the cursor. When the activity returns to the screen, it will call the requery method of cursor to query again. When the activity is destroyed, All managed cursors will be automatically closed and released.

How to wrap cursor: we will use the cursorwrapper object to wrap our cursor object and realize the data conversion we need. This cursorwrapper actually implements the cursor interface. The cursor obtained by our query is actually a reference to cursor, and what the system actually returns to us must be an object instance of an implementation class of the cursor interface. We wrap this instance with cursorwrapper, and then use simplecursoradapter to display the results on the list.

What to pay attention to in the cursor result set: one of the most important things to pay attention to is that our result set must contain a column of "_id", otherwise the simplecursoradapter will turn its face and refuse to recognize people. Why? Because this comes from the specification of SQLite, the primary key is based on "_id". The solution is

3： First, when creating tables, do it according to the specifications; Second, use an alias when querying, for example, select id as_ id FROM person； Third, write in cursorwrapper:

If we try to get the column index corresponding to "_id" from cursorwrapper, we can return the column index corresponding to "Id" in the query result. Finally, let's look at the results:

Thank you for reading, hope to help you, thank you for your support to this site!