Android development path 09 (talking about SQLite database 01) and androidsqlite

Android development path 09 (talking about SQLite database 01) and androidsqlite

1. SQLite database:

SQLite is an open-source embedded Relational Database Engine for SQL database with self-contained, zero configuration, and transaction support. It is highly portable and enables

 

Easy to use, compact, efficient, and reliable. Unlike other database management systems, SQLite is easy to install and run. In most cases

 

SQLite binary files can be used to create, connect to, and use databases.

For the SDK, The SQLite program has been built in, so we do not need to download it on the SQLite official website. Generally, databases use static data classes.

 

While our SQLite database uses a dynamic database, which is automatically determined based on the stored value. The biggest feature of SQLite databases over other databases is that

 

You can save various types of data to any field without worrying about the type declared by the field. There is a special case that is defined as INTEGER PRIMARY

 

The KEY field can only store 64-bit integers. SQLite has the following types:

1. NULL: NULL;

2. INTEGER: signed INTEGER, depending on the size of the number to be saved;

3. REAL: floating point number;

4. TEXT: String TEXT;

5. BLOB: binary object;

2. Related Operations

① Create a database: sqlite3 test. db;

② Create table: create table userInfo (userId integer primary key autoincrement, name varchar (20 ));

The SQLite database can parse most of the standard SQL statements:

① Query statement: select * from table name where Condition Clause group by grouping clause having... order by sorting clause

Example: select * from userInfo order by id desc;

Select * from userInfo group by name having count (*)> 1

② Insert statement: insert into Table Name (Field List) values (Value List)

Example: insert into userInfo (name, age) values ('zhang san', 20)

③ Update statement: update table name set field name = value where Condition Clause

For example, update person set name = 'zhangsan' where id = 10

④ Delete statement: delete from table name where Condition Clause

Example: delete from userInfo where id = 10

 

Instance code:

1. Create a JavaBean

Public class Student {

Private int sid;

Private String name;

Private short age;

 

Public Student (){

Super ();

}

Public Student (int sid, String name, short age ){

Super ();

This. sid = sid;

This. name = name;

This. age = age;

}

 

Public int getSid (){

Return sid;

}

Public void setSid (int sid ){

This. sid = sid;

}

Public String getName (){

Return name;

}

Public void setName (String name ){

This. name = name;

}

Public short getAge (){

Return age;

}

 

Public void setAge (short age ){

This. age = age;

}

@ Override

Public String toString (){

Return "Student [sid =" + sid + ", name =" + name + ", age =" + age + "]";

}

 

 

 

}

 

2. Create a class DBOpenHelper. java used to create the SQLite Database

 

Public class DBOpenHelper extends SQLiteOpenHelper {

 

Private static final String DBNAME = "data. db ";

Private static CursorFactory factory = null;

Private static final int VERSION = 1;

Public DBOpenHelper (Context context ){

Super (context, DBNAME, factory, VERSION );

}

 

// Execute this method when the database is created for the first time

@ Override

Public void onCreate (SQLiteDatabase db ){

// The field name in the SQL statement corresponds to the member variable of Student. java

String SQL = "create table t_student (sid integer primary key ,"

+ "Name varchar (20), age integer )";

Db.exe cSQL (SQL );

}

@ Override

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

 

}

 

}

 

3. Create a database class StudentDAO. java

 

Public class StudentDAO {

Private DBOpenHelper helper;

Private SQLiteDatabase db;

 

Public StudentDAO (Context context ){

// Create a DBOpenHelper instance

Helper = new DBOpenHelper (context );

}

 

Public void add (Student student ){

/**

* Android uses the getWritableDatabase () and getReadableDatabase () methods to obtain a SQLiteDatabase instance for database operations.

* (The getWritableDatabase () method is called in the getReadableDatabase () method)

* 1. The getWritableDatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write.

* 2. The getReadableDatabase () method first opens the database in read/write mode. If the disk space of the database is full, the opening fails.

* If the database fails to be opened, the database will continue to be opened in read-only mode. If the problem is solved successfully, the read-only database object is closed and a read/write database object is returned.

*/

Db = helper. getWritableDatabase ();

String SQL = "insert into t_student (sid, name, age) values (?,?,?) ";

Db.exe cSQL (SQL, new Object [] {student. getSid (), student. getName (),

Student. getAge ()});

}

 

Public void update (Student student ){

Db = helper. getWritableDatabase ();

String SQL = "update t_student set name = ?, Age =? Where sid =? ";

Db.exe cSQL (SQL, new Object [] {student. getName (), student. getAge (),

Student. getSid ()});

 

}

 

Public Student query (int sid ){

Db = helper. getWritableDatabase ();

String SQL = "select sid, name, age from t_student where sid =? ";

// Cursor is actually a collection of stored data.

Cursor cursor = db. rawQuery (SQL, new String [] {String. valueOf (sid )});

// Move the cursor to the next row in the result set found by cursor, that is, point the cursor to the next result.

If (cursor. moveToNext ()){

// You can use the getColumnIndex () method to obtain the name of the specified column. If no Column exists,-1 is returned.

Return new Student (cursor. getInt (cursor. getColumnIndex ("sid ")),

Cursor. getString (cursor. getColumnIndex ("name ")),

Cursor. getShort (cursor. getColumnIndex ("age ")));

}

Return null;

}

 

// Variable parameters are used here to meet the condition that the number of parameters is not fixed.

Public void delete (Integer... sids ){

If (sids. length> 0 ){

// The StringBuffer thread is safe and mostly used in multithreading to perform complex operations on strings.

StringBuffer sb = new StringBuffer ();

For (int I = 0; I <sids. length; I ++ ){

Sb. append ('? '). Append (',');

}

Sb. deleteCharAt (sb. length ()-1 );

SQLiteDatabase database = helper. getWritableDatabase ();

String SQL = "delete from t_student where sid in (" + sb + ")";

Database.exe cSQL (SQL, (Object []) sids );

}

}

 

Public List <Student> getScrollData (int start, int count ){

List <Student> students = new ArrayList <Student> ();

Db = helper. getWritableDatabase ();

String SQL = "select * from t_student limit ?,? ";

Cursor cursor = db. rawQuery (SQL, new String [] {String. valueOf (start ),

String. valueOf (count )});

While (cursor. moveToNext ()){

Students. add (new Student (

Cursor. getInt (cursor. getColumnIndex ("sid ")),

Cursor. getString (cursor. getColumnIndex ("name ")),

Cursor. getShort (cursor. getColumnIndex ("age "))));

}

Return students;

 

}

 

Public long getCount (){

Db = helper. getWritableDatabase ();

String SQL = "select count (sid) from t_student ";

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

If (cursor. moveToNext ()){

Return cursor. getLong (0 );

}

Return 0;

}

 

}