Simple use of SQLite databases in Android

Simple use of SQLite databases in Android

File file = new File(“hah.txt ");

// Only an object file is created. The filepath refers to the hah.txtfile. The hah.txt file may or may not exist. If the file does not exist, it will not be created.

Only when a file output stream writes the file can the file be created.

 

Cursor: When you access a table structure in a database and want to access a row in the table, there is a quick Locating Method in the database. This positioning method is achieved through indexes. A cursor is equivalent to an array pointer. You can search for data by moving the cursor up or down.

To use the SQLite database in Android, you must inherit from SQLiteOpenHelper. This class does not provide default constructor. Therefore, the constructor of this class must be called as shown in the subclass.

Create a database

Package com. test. sqllitedemo;

 

Import android. content. Context;

Import android. database. sqlite. SQLiteDatabase;

Import android. database. sqlite. SQLiteOpenHelper;

 

Public class MyDBOpenHelper extends SQLiteOpenHelper {

// The parameters of the subclass constructor do not have to match the parameters of the parent constructor.

Public MyDBOpenHelper (Context context ){

/**

* @ Paramcontext Context

* @ Paramname: name of the database file

* @ Paramfactory is used to create a cursor object. If null is used, the default cursor factory is used.

* @ Paramversion the version number of the database, starting from 1. Used to update a database.

* The database suffix is not fixed and is written to. db for ease of viewing and identification.

*/

Super (context, "test. db", null, 1 );

}

 

@ Override

// Automatically executed when the database is created for the first time

Public void onCreate (SQLiteDatabase db ){

}

 

@ Override

// Automatically executed when the database version is updated

Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){

// TODO Auto-generated method stub

}

 

}

Create a database

Package com. test. sqllitedemo;

 

Import android. app. Activity;

Import android. OS. Bundle;

 

Public class MainActivity extends Activity {

 

@ Override

Protected void onCreate (Bundle savedInstanceState ){

Super. onCreate (savedInstanceState );

SetContentView (R. layout. activity_main );

// Execute this line of code. The database is not created, but a database object is created.

MyDBOpenHelper helper = new MyDBOpenHelper (this );

// Create or open a database with read and write permissions, which is located under the data/application package name/databases/folder

Helper. getWritableDatabase ();

}

 

}

 

Create a table in SQLite

All data types in SQLite are stored as strings by default, and there is no length limit. If the data length exceeds the table definition, no error is returned.

In Android, SQLite automatically creates a table named android_metadata. Used to store the current language environment. _ Id is recommended for IDs in SQLite.

The database version of SQLite can only be increased and cannot be reduced.

Package com. test. sqllitedemo;

 

Import android. content. Context;

Import android. database. sqlite. SQLiteDatabase;

Import android. database. sqlite. SQLiteOpenHelper;

 

Public class MyDBOpenHelper extends SQLiteOpenHelper {

// The parameters of the subclass constructor do not have to match the parameters of the parent constructor.

Public MyDBOpenHelper (Context context ){

/**

* @ Paramcontext Context

* @ Paramname: name of the database file

* @ Paramfactory is used to create a cursor object. If null is used, the default cursor factory is used.

* @ Paramversion the version number of the database, starting from 1. Used to update a database.

* The database suffix is not fixed and is written to. db for ease of viewing and identification.

*/

Super (context, "test. db", null, 1 );

}

 

@ Override

// This method is automatically executed when the database is created for the first time. If the database already exists, this method is not called again.

/**

* Create a table structure in this method and initialize the database.

* @ Param db indicates the created database.

*/

Public void onCreate (SQLiteDatabase db ){

// TODO Auto-generated method stub

// Execute the SQL statement

Db.exe cSQL ("create table info (_ id integer primary key autoincrement, name varchar (20), phone varchar (20 ))");

}

 

@ Override

// Automatically executed when the database version is updated, that is, when the database version number changes, the version of the executed database can only become larger, not smaller

// Int oldVersion, int newVersion is used to maintain data when the database is upgraded across versions

// If the data is valid in a monopoly industry, you can force the previous data to fail to be upgraded so that users can re-enter the data.

Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){

// TODO Auto-generated method stub

Db.exe cSQL ("alter table info add money varchar (10 )");

}

}

 

EL expressions can only be used on jsp pages.

 

Add, delete, modify, and query databases.

Xmlns: tools = "http://schemas.android.com/tools"

Android: layout_width = "match_parent"

Android: layout_height = "match_parent"

Android: orientation = "vertical"

Tools: context = "com. test. databaseworkflow. MainActivity">

Android: onClick = "add"

Android: layout_width = "wrap_content"

Android: layout_height = "match_parent"

Android: text = "add"/>

Android: onClick = "update"

Android: layout_width = "wrap_content"

Android: layout_height = "match_parent"

Android: text = "update"/>

Android: onClick = "delete"

Android: layout_width = "wrap_content"

Android: layout_height = "match_parent"

Android: text = "delete"/>

Android: onClick = "query"

Android: layout_width = "wrap_content"

Android: layout_height = "match_parent"

Android: text = "query"/>

 

 

 

Package com. test. database.pdf;

 

Import android. content. Context;

Import android. database. sqlite. SQLiteDatabase;

Import android. database. sqlite. SQLiteOpenHelper;

 

Public class MyDBOpenHelper extends SQLiteOpenHelper {

// The parameters of the subclass constructor do not have to match the parameters of the parent constructor.

Public MyDBOpenHelper (Context context ){

/**

* @ Param context

* @ Param name: name of the database file

* @ Param factory is used to create a cursor object. If null is used, the default cursor factory is used.

* @ Param version indicates the version number of the database, starting from 1. Used to update a database.

* The database suffix is not fixed and is written to. db for ease of viewing and identification.

*/

Super (context, "test. db", null, 1 );

 

}

 

@ Override

// This method is automatically executed when the database is created for the first time. If the database already exists, this method is not called again.

/**

* Create a table structure in this method and initialize the database.

* @ Param db indicates the created database.

*/

Public void onCreate (SQLiteDatabase db ){

// TODO Auto-generated method stub

// Execute the SQL statement

Db.exe cSQL ("create table info (_ id integer primary key autoincrement, name varchar (20), phone varch ar (20 ))");

}

 

@ Override

// Automatically executed when the database version is updated, that is, when the database version number changes, the version of the executed database can only become larger, not smaller

// Int oldVersion, int newVersion is used to maintain data when the database is upgraded across versions

// If the data is valid in a monopoly industry, you can force the previous data to fail to be upgraded so that users can re-enter the data.

Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){

// TODO Auto-generated method stub

Db.exe cSQL ("alter table info add money varchar (10 )");

}

 

}

 

 

Package com. test. database.pdf;

 

Import java. util. Random;

 

Import android. app. Activity;

Import android. database. Cursor;

Import android. database. sqlite. SQLiteDatabase;

Import android. OS. Bundle;

Import android. view. View;

 

Public class MainActivity extends Activity {

 

@ Override

Protected void onCreate (Bundle savedInstanceState ){

Super. onCreate (savedInstanceState );

SetContentView (R. layout. activity_main );

 

}

 

Public void add (View view ){

// Create a database

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

Random random = new Random ();

String SQL = "insert into info (name, phone) values (?,?) ";

// In SQLite, parameters can also be passed using placeholders.

Db.exe cSQL (SQL, new Object [] {"lisi" + random. nextInt (100), "453231 "});

// Release resources

Db. close ();

}

 

Public void delete (View view ){

 

}

 

Public void update (View view ){

 

}

 

Public void query (View view ){

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

String SQL = "select * from info ";

Cursor cursor = db. rawQuery (SQL, null );

While (cursor. moveToNext ()){

String id = cursor. getString (0 );

String name = cursor. getString (1 );

String phone = cursor. getString (2 );

System. out. println (id + "" + name + "" + phone );

}

// Release resources

Cursor. close ();

Db. close ();

}

}

 

In SQLite, all data is stored as strings.

Android Database supports Chinese display

 

Three ways to view data in SQLite tables

1. Write an SQL statement to check the data and display it in logcat.

2. In the independent data zone of the application, pull the database and view it in the tool.

3. view the table data in the SQLite database on the command line.

Change the default sequence set of the command line

Chcp (change current page) 65001 (UTF-8 encoding value)

When inserting duplicate data, you should be prompted to overwrite existing data.

When deleting data, you should be prompted whether you are sure to delete the data.

 

The program should be designed with good tips and operations.

 

The query operation does not change the content in the database. Generally, a readable database is obtained.

Multi-threaded database writing. The write sequence must be specified. And lock.

Read operations can be performed concurrently in multiple threads, and database write operations must be shackled.

 

Add, delete, modify, and query databases using googleAndroidapi

Package com. test. database.pdf;

 

Import java. util. Random;

 

Import android. app. Activity;

Import android. content. ContentValues;

Import android. database. Cursor;

Import android. database. sqlite. SQLiteDatabase;

Import android. OS. Bundle;

Import android. view. View;

Import android. widget. Toast;

 

Public class GoogleDataBaseOPR extends Activity {

 

@ Override

Protected void onCreate (Bundle savedInstanceState ){

Super. onCreate (savedInstanceState );

SetContentView (R. layout. activity_main );

}

 

Public void add (View view ){

// Create a database

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

Random random = new Random ();

ContentValues values = new ContentValues ();

Values. put ("name", "lisi" + random. nextInt (100 ));

Value. put ("phone", "12345678 ");

 

/*

* Db. insert (table, nullColumnHack, values)

* First parameter: Table Name

* Second parameter: column to be filled with null values

* Third parameter: The inserted value map set, ContentValues

*/

Long id = db. insert ("info", null, values );

If (id! =-1 ){

Toast. makeText (this, "added successfully, added in" + id + "row", Toast. LENGTH_SHORT)

. Show ();

} Else {

Toast. makeText (this, "failed to add", Toast. LENGTH_SHORT). show ();

}

// Release resources

Db. close ();

}

 

Public void delete (View view ){

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

/*

* Db. delete (table, whereClause, whereArgs)

* First parameter: Table Name

* Second parameter: select a condition

* Third parameter: select the condition parameter.

* Return value: number of rows deleted. If no value is deleted, 0 is returned;

*/

Int result = db. delete ("info", null, null );

Db. close ();

If (result! = 0 ){

Toast. makeText (this, "delete" + result + "row", Toast. LENGTH_SHORT)

. Show ();

} Else {

Toast. makeText (this, "failed to delete", Toast. LENGTH_SHORT). show ();

}

}

 

Public void update (View view ){

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

/*

* Db. update (table, values, whereClause, whereArgs)

* First parameter: Table Name

* Second parameter: The inserted value.

* Third parameter: select a condition. If no condition is selected, enter null.

* Fourth parameter: Enter null if no parameter is specified for the selection condition.

* Return value: the number of modified rows. If no modification is made, 0 is returned;

*/

ContentValues values = new ContentValues ();

Values. put ("name", "lisi ");

Value. put ("phone", "12345678 ");

Int result = db. update ("info", values, null, null );

If (result! = 0 ){

Toast. makeText (this, "modified the" + result + "row", Toast. LENGTH_SHORT)

. Show ();

} Else {

Toast. makeText (this, "failed to modify", Toast. LENGTH_SHORT). show ();
}

}

 

Public void query (View view ){

MyDBOpenHelper helper = new MyDBOpenHelper (this );

SQLiteDatabase db = helper. getWritableDatabase ();

/*

* Db. query (table, columns, selection, selectionArgs, groupBy, having, orderBy)

* First parameter: Table Name

* Second parameter: null indicates that all columns are returned.

* Third parameter: selection condition. If no selection condition is selected, null is written.

* Fourth parameter: select the condition parameter. If no condition parameter is selected, enter null.

* Fifth parameter: grouping condition. null is not written.

* Sixth parameter: grouping condition. null is not written.

* Seventh parameter: grouping condition. null is not written.

* Return value: number of records in the query results

*/

// Cursor cursor = db. query ("info", null, null );

Cursorcursor = db. query ("info", newString [] {"_ id", "name", "phone"}, null, null );

While (cursor. moveToNext ()){

String id = cursor. getString (0 );

String name = cursor. getString (1 );

String phone = cursor. getString (2 );

System. out. println (id + "" + name + "" + phone );

}

// Release resources

Cursor. close ();

Db. close ();

Db. close ();

}

}