Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to use the SQLite database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Brief introduction

SQLite is an open source embedded relational database, which implements a SQL database engine with self-containment, zero configuration and transaction support. The utility model is characterized by high portability, convenient use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is simple to install and run, and in most cases you can start creating, connecting, and using databases as long as you make sure the binaries for SQLite exist. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.

General databases use fixed static data types, SQLite uses dynamic data types, when creating a table, you can specify the data type of a column in the CREATE TABLE statement, but you can put any data type into any column. When a value is inserted into the database, SQLite will check its type, and if the type does not match the associated column, SQLite will try to convert the value to the type of the column, and if it cannot be converted, the value will be stored as its own type, such as putting a string (String) into the INTEGER column. SQLite calls this a "weak type". However, in one special case, if it is INTEGER PRIMARY KEY, other types will not be converted and a "datatype missmatch" error will be reported.

In terms of transaction processing, SQLite implements independent transaction processing through database-level exclusivity and shared locks. This means that multiple processes can read data from the same database at the same time, but only one can write data. An exclusive lock must be acquired before a process or thread can write to the database. After the exclusive lock is acquired, other read or write operations will not occur again.

In addition, SQLite does not support some standard SQL features, especially foreign key constraints (FOREIGN KEY constrains), nested transcaction and RIGHT OUTER JOIN and FULL OUTER JOIN, and some ALTER TABLE functions.

In addition to the above functions, SQLite is a complete SQL system with complete triggers, transactions, and so on.

2.Android integrates SQLite database

Android integrates with SQLite at run-time, so every Android application can use the SQLite database. For developers familiar with SQL, using SQLite in Android development is fairly simple. However, because JDBC consumes too many system resources, JDBC is not suitable for memory-constrained devices like mobile phones. As a result, Android provides some new API to use SQLite databases, and programmers need to learn to use these API in Android development.

The database is stored under data/data//databases/.

a. Data type

1.NULL null value

Signed integers of 2.INTEGER

3.REAL floating point type

4.TEXT string text

5.BLOB binary object

In fact, however, sqlite3 also accepts the following data types:

A 16-bit integer for smallint.

A 32-bit integer for interger.

The exact value p is a decimal integer of decimal (digits s) p and the size of s. The exact value p is the number of digits after the decimal point. If not specifically specified, the system is set to pumped 5; slots 0.

The real number of float 32 bits.

The real number of double 64 bits.

A string of char (n) n length, n cannot exceed 254.

A string whose varchar (n) length is not fixed and its maximum length is n cannot exceed 4000.

Graphic (n) is the same as char (n), but the unit is two characters double-bytes, n cannot exceed 127s. This form is designed to support two-character fonts, such as Chinese characters.

Vargraphic (n) A double-character string of variable length with a maximum length of n, n cannot exceed 2000

Date contains the year, month, and date.

Time contains hours, minutes, and seconds.

Timestamp includes year, month, day, hour, minute, second, and 1/1000 seconds.

Datetime contains date and time format, which must be written as' 2010-08-05', not '2010-8-5', otherwise an error will occur when reading!

b. Easy to use

Database related classes under Android platform:

SQLiteOpenHelper abstract class: implements the user class by inheriting from this class to provide operation functions such as database opening and closing.

SQLiteDatabase database access class: perform operations such as inserting records and querying records to the database.

SQLiteCursor query structure operation class: used to access records in query results.

Activites can access a database through Content Provider or Service.

c. Create a database

Android does not automatically provide the database. To use SQLite in an Android application, you must create your own database, then create tables, indexes, and populate the data. Android provides SQLiteOpenHelper to help you create a database, as long as you inherit the SQLiteOpenHelper class, you can easily create a database.

The SQLiteOpenHelper class encapsulates the logic used to create and update databases according to the needs of developing applications. A subclass of SQLiteOpenHelper, you need to implement at least three methods:

Package com.dandan.fm.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper / * you can obtain SQLiteDatabase objects through the following two methods of SQLiteOpenHelper: * getReadableDatabase () create or open a query database * getWritableDatabase () create or open a writable database * / public class DBHelper extends SQLiteOpenHelper {/ * constructor The * @ param context context path * @ param name database name * @ param factory optional cursor factory must be implemented, usually NULL * @ param version current database version number * / public DBHelper (Context context, String name, CursorFactory factory,int version) {super (context, name, factory, version) } / / the database is called when it is created for the first time Generally create a database table @ Override public void onCreate (SQLiteDatabase db) {/ / use the execSQL () method to execute the DDL statement. If there is no exception, this method does not return the value db.execSQL ("create table user (id INTEGER PRIMARY KEY AUTOINCREMENT," + "name varchar (20), address TEXT")). } / / when the database needs to be modified The Android system will actively call this method. @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {/ / database version upgrade Update database operation} / / callback function when opening the database Generally, @ Override public void onOpen (SQLiteDatabase db) {super.onOpen (db) is not used. } @ Override public synchronized void close () {super.close () }}

Description:

Create tables and indexes

Db.execSQL ("create table user (id INTEGER PRIMARY KEY AUTOINCREMENT," + "name varchar (20), address TEXT)")

Add data to a table

There are two ways to add data:

1. Just like creating a table above, you can use the execSQL method to execute statements such as INSERT, UPDATE, DELETE, and so on to update the data of the table. ExecSQL applies to all SQL statements that do not need to return results. Such as:

Db.execSQL ("INSERT INTO user (name, address) VALUES ('woniu'," + "' http://http://smallwoniu.blog.51cto.com/')");")

The insert (), update (), delete () methods of the 2.SQLiteDatabase object. These methods take part of the SQL statement as parameters. Such as:

Private SQLiteDatabase mDataBase = null;private static final String USER_TABLE = "user" Public void getDB () {/ / create DBHelper object DBHelper db = new DBHelper (this, USER_TABLE, null, 1) / / get writable SQLiteDatabase mDataBase = db.getWritableDatabase () } public void insert () {/ / ContentValues object into which key-value pairs are inserted, and the key is the database column name The value is the value ContentValues cv = new ContentValues () inserted into this column Cv.put ("name", "LiuMing"); cv.put ("address", "ShangHai"); mDataBase.insert (USER_TABLE, null, cv) } public int update () {ContentValues cv = new ContentValues (); cv.put ("name", "MaLi") / / the third parameter where statement is equivalent to the statement after where in the sql statement,? Is the placeholder / / the fourth parameter is the placeholder's value return mDataBase.update (USER_TABLE, cv, "id =?", new String [] {"1"}) } public int delete () {/ / and udate () are similar to return mDataBase.delete (USER_TABLE, "id =? And name =? ", new String [] {" 1 "," MaLi "});}

Query database

Similar to INSERT,UPDATE,DELETE, there are two ways to query data from a database.

/ * use rawQuery () to call select statement directly. The simplest query method is * if the query is dynamic, using rawQuery will be very complex * @ return Cursor can iterate the query results * / public Cursor rawQuery () {return mDataBase.rawQuery ("select * from user where id =? And name =? ", new String [] {" 1 "," LiuMing "});} / / use the SELECT statement segment to build the query, and the content of the SELECT statement is taken as the parameter public Cursor query () {String [] columns = {" name "," address "} of the query () method. / / second parameter: column name to be queried / / third parameter: where statement / / third parameter: placeholder value in where statement / / fourth parameter: grouping query results / / fifth parameter: restricting grouping results / / sixth parameter: sorting query results return mDataBase.query (USER_TABLE, columns, "id =?" New String [] {"1"}, null, null, null) }

Finally, it is highlighted that using cursors, no matter how the query is executed, will return a Cursor,Curosr. The main methods are as follows:

GetCount (): gets how many records are in the result set. MoveToFirst (): move to the first line. MoveToNext (): move to the next line to traverse all records. IsAfterLast (): determines whether it is the last line. GetColumnNames (): returns the field name. GetColumnCount (): returns the field number. GetString (), getInt (): gets the value of the current record in the given field. Requery (): re-execute the query to get the cursor. Close (): releases the cursor resource. / * * query all records of the notepad table * * / public ArrayList getWriteAllCount () {/ / Open database openDatabase (); ArrayList data = null; / / receive data / / Cursor----ResultSet: used to receive database data list Cursor cursor = db.query (DBInfo.Table.TB_WRITE_NAME, null,null, null,null, null,null) If (null! = cursor & & cursor.getCount () > 0) {data = new ArrayList (); while (cursor.moveToNext ()) {HashMap map = new HashMap (); map.put ("title", cursor.getString (cursor.getColumnIndex ("title")); map.put ("content", cursor.getString (cursor.getColumnIndex ("content") Map.put ("p_w_picpathPath", cursor.getString (cursor.getColumnIndex ("p_w_picpathPath")); map.put ("time", cursor.getString (cursor.getColumnIndex ("time"); map.put ("color", cursor.getString (cursor.getColumnIndex ("color")); map.put ("background", cursor.getInt (cursor.getColumnIndex ("background") Map.put ("lock", cursor.getInt (cursor.getColumnIndex ("lock")); data.add (map);}} cursor.close (); db.close (); / / remember to turn off return data after use } Thank you for reading this article carefully. I hope the article "how to use SQLite Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.