Sqlite3 command line management database for Android

SQLite is suitable for the data storage of mobile devices, which has the advantages of fast processing speed, less resource occupation, no need to install and deploy, embedded into the program as a part of it. http://www.sqlite.org/

The SQLite database file is located in the/data/your-app-name/databases directory.

The following example shows how to add, delete, modify, and query a database using SQLite.

We need a helper class to inherit the sqliteopenhelper class

Package COM. sumq; import android. content. context; import android. database. SQLite. sqlitedatabase; import android. database. SQLite. sqlitedatabase. cursorfactory; import android. database. SQLite. sqliteopenhelper; public class mysqlitehelper extends sqliteopenhelper {public mysqlitehelper (context, string name, cursorfactory factory, int version) {super (context, name, factory, version ); // todo auto-generated constructor stub}/** when the database is created, execute this method for the first time * usually place the initialization operations such as table creation in this method */@ overridepublic void oncreate (sqlitedatabase dB) {// todo auto-generated method stubdb.exe csql ("create table if not exists user_info (ID integer primary key, name varchar, VIP integer )");} /* call this function during version upgrade * parameter 1: database to be updated * parameter 2: Pass in Old Version Number * parameter 3: Pass in New Version Number */@ overridepublic void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {// todo auto-generated method stub, this method is called every time a database is opened */@ overridepublic void onopen (sqlitedatabase dB) {// todo auto-generated method stubsuper. onopen (db );}}

Package COM. sumq; import android. app. activity; import android. content. contentvalues; import android. database. cursor; import android. database. SQLite. sqlitedatabase; import android. OS. bundle; import android. widget. textview; public class uisqliteactivity extends activity {textview TV; mysqlitehelper;/** called when the activity is first created. * // @ override public void oncreate (bundle savedin Stancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); TV = (textview) findviewbyid (R. id. TV); mysqlitehelper = new mysqlitehelper (this, "test. DB ", null, 1); insertandupdatedata (mysqlitehelper); string result = querydata (mysqlitehelper); TV. settext ("Name \ t grade \ t" + result);} private void insertandupdatedata (mysqlitehelper) {// obtain the database object sqlitedatabase DB = mysqliteh Elper. getwritabledatabase (); // The first method for inserting a database directly writes the SQL statement db.exe csql ("insert into user_info (name, VIP) values ('user1', 1 )"); // second, call the insert method contentvalues values = new contentvalues (); values. put ("name", "user2"); values. put ("VIP", "2"); dB. insert ("user_info", null, values); // update the value of level = 2. clear (); values. put ("name", "user2"); values. put ("VIP", "3"); dB. update ("user_info", values, "VIP =? ", New string [] {" 2 "}); dB. close ();} private string querydata (mysqlitehelper) {string result = ""; sqlitedatabase DB = mysqlitehelper. getreadabledatabase (); cursor = dB. query ("user_info", null, "id ASC"); While (cursor. movetonext () {result = Result + cursor. getstring (cursor. getcolumnindex ("name") + ""; Result = Result + cursor. getint (cursor. getcolumnindex ("VIP") + "\ n";} cursor. close (); dB. close (); return result ;}@ override protected void ondestroy () {sqlitedatabase DB = mysqlitehelper. getwritabledatabase (); dB. delete ("user_info", "1", null); super. ondestroy ();}}

Execution Result Display

The storage location of our database is/data/COM. sumq/databases/test. DB.

In the following example, we use the ADB Shell Name to query the created database.

1. Open "start"-> "run" And Enter cmd to display the DOS window.

2. Run the name in sequence to enter the location of the database file.

The ADB shell command is that we have configured the environment variable. My own SDK path is D: \ Program Files \ Android-SDK-Windows \ tools so pay attention to whether your environment variables have been configured. Otherwise, this command cannot be recognized.

CD data is used to enter the corresponding folder

Ls: displays the display information of all files in the folder with the parameter-L.

Test. DB is the database created in our example. Enter the command to access the database.

Enter slqite3 test. dB to directly access our database. You can find SQLite> on the left to access the database operation command line.

. Table shows all tables in the database. android_metadata is a system table. It is not the table created in our example.

. Schema user_info displays the table structure.

. Dump user_info displays the table structure and content.

. Exit exit.

We can also use TXT to import data, as long as the format is required.

1. Create a TXT file

Note: Do not use any space in the text to save any file name. Here I will save user_info.

2.place user_info.txt in the corresponding databases directory.

Display directory.

3. execute commands

Data has been imported.

Of course, in addition to the command, there are also graphical client software, such as sqlitespy. or Firefox SQLite manager can view the database.