Android

Sqlite Database in Android Studio Creating CRUD Application

Description:

Sqlite Database in Android Studio Creating CRUD System: in this article, I am going to show how to create and connect SQLite database in android.


SQLite database in Android Studio:

SQLite database is a lightweight relational database, its computing speed is very fast, It occupies very little resources, usually, only a few hundred K of memory is enough, so it is especially suitable for use on mobile devices. SQLite database Not only supports standard SQL syntax but also follows the ACID transaction of the database, so as long as you have used other With relational databases, you can quickly get started with SQLite database. And SQLite is much simpler than ordinary databases, it is very It can be used without setting a user name and password. Android embeds this extremely powerful database into In the system, the local persistence function has made a qualitative leap.

When you need to store a large amount of complex relational data, For example, there may be many conversations in the SMS program of our mobile phone, and each conversation contains many pieces of information. Content and most conversations may also correspond to a contact in the phone book. It’s hard to imagine how to use files or SharedPreferences to store these large amounts of data and complex structure data? But you can do it with a database To. So let’s quickly take a look at how to use SQLite database in Android Studio.



Create SQLite database in Android Studio:

In order for us to manage the database more conveniently, Android provides an SQLiteOpenHelper helper. The helper class, with the help of this class, the database can be created and upgraded very simply. Since there are good things that can be used directly, Then we naturally have to try it, and I will introduce the basic usage of SQLiteOpenHelper below. First of all, you need to know that SQLiteOpenHelper is an abstract class, which means that if we want to use it, You need to create your own helper class to inherit it. There are two abstract methods in SQLiteOpenHelper, namely onCreate() and onUpgrade(), we must rewrite these two methods in our own helper class, and then in these two In this method, the logic of creating and upgrading the database is realized.

There are two very important instance methods in SQLiteOpenHelper, getReadableDatabase() and getWritableDatabase(). Both methods can create or open an existing database (if the database already exists Open directly, otherwise create a new database), and return an object that can read and write to the database. Do not The same is that when the database is not writable (such as the disk space is full) the pair returned by the getReadableDatabase() method The image will open the database in read-only mode, and the getWritableDatabase() method will be abnormal.

There are two construction methods in SQLiteOpenHelper that can be rewritten, generally the construction method with fewer parameters is used can. This construction method receives four parameters, the first parameter is Context, there is nothing to say about this, it must have It can operate on the database. The second parameter is the database name, the name specified here is used when creating the database Weighed. The third parameter allows us to return a custom Cursor when querying data, usually null is passed in. The fourth parameter represents the version number of the current database, which can be used to upgrade the database. Build out After the instance of SQLiteOpenHelper, call its getReadableDatabase() or getWritableDatabase() method Then the database can be created, and the database files will be stored in the /data/data/<package name>/databases/ directory. At this time, the overridden onCreate() method will also be executed, so it is usually here to deal with some of the logic of creating a table.

Next, let us use examples to more intuitively experience the usage of SQLiteOpenHelper. First, Create a new DatabaseTest project first. We want to create a database named BookStore.db, and then create a new Book in this database Table. The table has columns such as id (primary key), author, price, number of pages, and book title. To create a database table, of course, you still need to build a table The statement, here is also to test your basic SQL skills, the table building statement of the Book table is as follows:

As long as you have a little knowledge of SQL database, the above table creation statement should not be difficult for you. Unlike other databases, SQLite database has many complicated data types. Its data type is very simple. Integer represents an integer type.

real represents a floating-point type, text represents a text type, and blob represents a binary type. In addition, in the above table building statement, we also The primary key is used to set the id column as the primary key, and the autoincrement keyword is used to indicate that the id column is self-increasing. Then you need to execute this SQL statement in the code to complete the table creation operation. New MyDatabaseHelper The class inherits from SQLiteOpenHelper, the code is as follows:


As you can see, we define the table building statement as a string constant, and then call it in the onCreate() method. The execSQL() method of SQLiteDatabase is used to execute this table creation statement, and a Toast pops up to indicate that the creation is successful. This will ensure that the Book table can be successfully created when the database is created. Now modify the code in activity_main.xml as follows:

Sqlite Database in Android Studio

The layout file is very simple, that is, a button is added to create a database. Finally, modify the MainActivity.

The code is as follows:

Here we construct a MyDatabaseHelper object in the onCreate() method, and through the constructor, The parameter specifies the database name as BookStore.db and the version number as 1, and then click on the Create database button The getWritableDatabase() method is called in the event. So when the Create database button is clicked for the first time, it will detect There is no BookStore.db database in the current program, so the database will be created and called MyDatabaseHelper

In the onCreate() method, the Book table will be created, and then a Toast will pop up to indicate that the creation is successful. When you click the Create database button again, you will find that the BookStore.db database already exists at this time, so there will be no more Create it once. Now you can run the code, click the Create database button on the main interface of the program, and the result is shown in below Figure.

Sqlite Database in Android Studio

At this point BookStore.db database and Book table should have been created successfully, because when you click Create again Toast will no longer pop up when the database button is pressed. But back to the old question before, how can we verify them Is the creation successful? If you still use File Explorer, then at most you can only see that appears under the databases directory A BookStore.db file is created, and the Book table cannot be seen through File Explorer. So this time we are going to change one A way to view, use adb shell to check the creation of databases and tables.

ADB is a debugging tool that comes with the Android SDK. Using this tool, you can directly Mobile phone or simulator for debugging operation. It is stored in the platform-tools directory of the SDK, if you want to use it on the command line To use this tool, you need to configure its path to environment variables first. If you are using a Windows system, you can right-click My Computer→Properties→Advanced→Environment Variables, and then click Find Path in the system variables and click Edit to configure the platform-tools directory, as shown in below Figure .

Sqlite Database in Android Studio


After configuring the environment variables, you can use the adb tool. Before Open the command line interface, copy the platform tool folder address

Sqlite Database in Android Studio

And enter in the command prompt and press enter button.

Sqlite Database in Android Studio

 

After setting the platform-tools path then enter the ADB shell, You will enter the console of the device, as shown in below Figure.

Sqlite Database in Android Studio

Then set your app address,  for that simply write run-as and your application package name as you can see in the below figure.

Sqlite Database in Android Studio

checking the files inside the application package directory simply write ls and press enter

Sqlite Database in Android Studio

As you can see there is a directory named databases. Now enter in this directory simply write cd databases and press enter.

Sqlite Database in Android Studio

Now write ls to check the database file in the directory

Sqlite Database in Android Studio

As you can see the database is created successfully.

Make sure All These steps only for ADB tool users,  but in my case, I am using my cell phone as an emulator therefore I am using the SQLite manager for records checking.

For checking your android sqlite database file in sqlite manager simply download your database file from android studio device file explore,

Sqlite Database in Android Studio

as you can see my file is downloaded

Sqlite Database in Android Studio


Now open this db file in SQLite manager. Simply open SQLite manager and select open a database and press continue

Sqlite Database in Android Studio

Then select the database file and press open

Sqlite Database in Android Studio

Sqlite Database in Android Studio

As you can see the database is loaded in SQLite manager .

Upgrade SQLite Database in Android Studio:

If you are careful enough, you will definitely find that there is an empty method in MyDatabaseHelper too! Yes, onUpgrade() The method is used to upgrade the database. It plays a very important role in the management of the entire database. It must not be ignored.

At present, there is already a Book table in the DatabaseTest project for storing various detailed data of the book, if we want What should I do if I add another Category table to record the classification of books?

For example, in the Category table, there are columns of id (primary key), category name, and category code, then the table statement can be Written as:



Next, we add this table building statement to MyDatabaseHelper, the code is as follows:

It all seems to be right, now let’s run the program again and click the Create database button? There is no prompt for successful creation. Of course, you can also check it in the database through the adb tool, In this way, you will be more sure that the Category table was not created successfully!

In fact, it’s not difficult to think about the reason why the creation is not successful, because the BookStore.db database already exists at this time. No matter how we click the Create database button, the onCreate() method in MyDatabaseHelper will not be executed again OK, so the newly added table cannot be created.

The solution to this problem is also quite simple, just uninstall the program first, and then re-run, then The BookStore.db database no longer exists. If you click the Create database button again, the MyDatabaseHelper The onCreate() method will be executed, and the Category table can be created successfully.

However, it is undoubtedly an extreme way to add a table by uninstalling the program. In fact, we only need to be clever This problem can be easily solved by using the upgrade function of SQLiteOpenHelper. Modify MyDatabaseHelper The code in is as follows:

As you can see, we have executed two DROP statements in the onUpgrade() method. If it is found that the database has been If there is a Book table or a Category table, delete these two tables, and then call the onCreate() method to recreate build. Here first delete the existing table, because if you find that this table already exists when you create the table, it will be straight Receive an error.

The next question is how to make the onUpgrade() method execute, remember the structure of SQLiteOpenHelper Is the fourth parameter received in the method? It represents the version number of the current database, what we passed in before was 1, but now only To pass in a number greater than 1, you can let the onUpgrade() method be executed. Modify the code in MainActivity, As follows:

Here, the database version number is designated as 2, which means that we have upgraded the database. Now re-run the program and Click the Create database button, then a prompt of successful creation will pop up again. To verify that the Category table is created successfully, we open the BookStore.db database in SQLite manager as shown in below Figure .

Sqlite Database in Android Studio

As you can see the category table is successfully created.


CRUD system using SQLite database in Android studio:

adding data using  SQLite database in Android Studio:

Now that you have mastered the method to create and upgrade the database, then you should learn how to enter the data in the table Okay. In fact, there are only four operations we can perform on data, namely CRUD. Where C stands for add (Create), R stands for Retrieve, U stands for Update, D stands for Delete. Every kind of exercise Each command corresponds to a SQL command. If you are familiar with the SQL language, you will know how to add data. Use insert, select when querying data, update when updating data, and delete when deleting data. But open The level of developers will always be uneven, not everyone can be very familiar with the use of the SQL language, so Android It also provides a series of auxiliary methods, making it easy to complete in Android even without writing SQL statements All CRUD operations.

We already know before, call SQLiteOpenHelper’s getReadableDatabase() or g etWritableDatabase()The method can be used to create and upgrade the database, not only that, these two methods will also return a SQLiteDatabase Object, with the help of this object, CRUD operations can be performed on the data.

So let’s look at each function one by one, first learn how to add data to the database table. SQLiteDatabase provides an insert() method, which is specifically used to add data. It receives three Parameters. The first parameter is the name of the table. In which table we want to add data to, the name of the table is passed in here. the second The parameter is used to automatically assign NULL to some nullable columns when adding data is not specified. Generally, we don’t use this A function, just pass in null directly. The third parameter is a ContentValues ​​object, which provides a series of put() Method overload, used to add data to ContentValues, you only need to add each column name in the table and the corresponding to be added Just input the data.

Modify the code in activity_main.xml as follows:

As you can see, we have added a new button to the layout file, which will be edited in the click event of this button later. Write logic to add data. Then modify the code in MainActivity as follows:

In the click event of the add data button, we first get the SQLiteDatabase object and then use ContentValues ​​to assemble the data to be added. If you are more careful, you should find that this is only for Book The data in the four columns in the table are assembled, and the id column is not assigned a value. This is because when the table was created earlier We set the id column to self-increment, and its value will be automatically generated when entering the library, so there is no need to manually assign it Worth it. Next, the insert() method is called to add data to the table. Note that we actually added two pieces of data here. The above code uses ContentValues ​​to assemble two different contents respectively and calls the insert() method twice. Ok, now you can re-run the program, the interface is shown in the below Figure.

Sqlite Database in Android Studio

Click the Add data button. At this time, both pieces of data should have been added successfully, but in order to confirm, I Let’s open the BookStore.db database and take a look. Enter the SQL query select * from Book, the result is shown in the figure As shown in below figure.

Sqlite Database in Android Studio

As you can see our data is successfully added in our SQLite database in android studio.


updating data using  SQLite database in Android Studio:

After learning how to add data to the table, let’s see how to modify the existing data in the table. SQLiteDatabase also provides a very useful update () method to update data, this method Receives four parameters. The first parameter is the same as the insert() method and is also the table name. Here you specify which table to update the data according to. The second parameter is the ContentValues ​​object, where the update data should be assembled. The third and fourth parameters are Used to constrain to update the data in a row or a few rows, if not specified, the default is to update all rows.

So next we are still modifying on the basis of the DatabaseTest project, take a look at the specific usage of the updated data. For example, the first book that was added to the database just now has not sold very well after the best-selling season, and now it needs to be approved How should we reduce prices to attract more customers? First modify the activity_main.xml in the code is as follows:

The code in the layout file is very simple, that is, a button for updating the data is added. Then modify The code in MainActivity is as follows:

Here a ContentValues ​​object is constructed in the click event of the update data button and only assigned A set of data shows that we just want to update the data in the price column to 10.99. Then called SQLiteDatabase The update() method to perform specific update operations, you can see that the third and fourth parameters are used here to specify Which rows are updated in the body? The third parameter corresponds to the where part of the SQL statement, which means to update all names equal to? Line, and? If a placeholder, you can pass a string array provided by the fourth parameter for each of the third parameter Placeholders specify the corresponding content. Therefore, the intention of the above code is to change the name to ABC Code The price of this book was changed to 100.99.

Now run the program again, the interface is shown in the below Figure.

Sqlite Database in Android Studio

After clicking the Update data button, the data will be updated as you can see in the below figure the price is updated from 16.96 to 100.99.

Sqlite Database in Android Studio


deleting data from the SQLite database in Android Studio:

The functions of adding and updating data are quite simple, there is not much code, and it is easy to understand, then We have to start learning the next operation, that is, how to delete data from the table from sqlite database in android studio.

Deleting data should be easier for you because you have learned all the knowledge points it needs. SQLiteDatabase provides a delete() method specifically for deleting data. This method receives three parameters. The first parameter is still the name of the table. There is nothing to say about this. The second and third parameters are used to restrict the deletion of a certain data of a row or a few rows, if not specified, the default is to delete all rows. Is it easy to understand? Then we continue to practice, modify the code in activity_main.xml, As follows:


A button is added to the layout file to delete data. Then modify the code in MainActivity, As follows:

As you can see, we specify in the click event of the delete button to delete the data in the Book table, and through the second, The third parameter specifies to delete only those books with more than 800 pages. Of course, this demand is very strange, and here it is just To be a test. You can first check the data in the current Book table, among which the pages of The XYZ book The number exceeds 800 pages, which means that when we click the delete button, this record should be deleted. Now run the program again, the interface is shown in below Figure.

Sqlite Database in Android Studio

After clicking the delete data button, the data will be deleted as you can see in the below figure.

Sqlite Database in Android Studio

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button