SQLite Android embedded database, is a typical relational database to support our common data type supports standard SQL statement.
Often use SQLite embedded database. In sqlite3.dll4, is a single file, no installation, can be used directly.
Android system, we need extends an abstract class the SQLiteOpenHelper it default.
- public void onCreate (SQLiteDatabase db) {} applied to the first use to create a database and table structure
- public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {} is called when the software version of the database update method, note that the data should not be lost.
Android project SQLiter database Step 1: Write an entity class to inherit SQLiteOpenHelper
Step 2: Add a constructor to this entity class (required)
The third step: Rewrite the onCreate (SQLiteDatabase db); created the first time you use the database table structure.
Rewrite onUpgrade (SQLiteDatabase db, in oldVersion, int newVersion); recreate the database when the database upgrade.
Step four: common code.
SQLiteDatabase db = this.getReadableDatabase (); obtain a readable database.
SQLiteDatabase db = this.getWriteableDatabase (); Get a writable database.
Cursor cursor = db.query (tableName, …);
ContentValues ??cv = new ContentValues ??(); / / the contents of a set of values
Step 2: Add a constructor to this entity class (required)
The third step: Rewrite the onCreate (SQLiteDatabase db); created the first time you use the database table structure.
Rewrite onUpgrade (SQLiteDatabase db, in oldVersion, int newVersion); recreate the database when the database upgrade.
Step four: common code.
SQLiteDatabase db = this.getReadableDatabase (); obtain a readable database.
SQLiteDatabase db = this.getWriteableDatabase (); Get a writable database.
Cursor cursor = db.query (tableName, …);
ContentValues ??cv = new ContentValues ??(); / / the contents of a set of values
Specific classes (CRUD) to achieve the following:
package com.hkrt.server; import java.util.ArrayList; import java.util.List; import com.hkrt.domain.Person; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class PersonHelper extends SQLiteOpenHelper { private static final String DBNAME="hkrt.db"; private static final String TABLENAME="person"; private static final String ID="id"; private static final String NAME="name"; private static final Integer VERSION=1; public PersonHelper(Context context) { super(context, DBNAME, null, VERSION); } @Override public void onCreate(SQLiteDatabase db) { String sql="create table "+TABLENAME+" ("+ID+" INTEGER primary key autoincrement,"+NAME+" varchar(20))"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } // public long insert(String text){ SQLiteDatabase db =this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(NAME, text); long row = db.insert(TABLENAME, null, values); return row; } // public long update(int id){ SQLiteDatabase db =this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(NAME, ""); String where = ID + " = ?"; String[] whereValue = { Integer.toString(id) }; long row= db.update(TABLENAME, values, where, whereValue);// return row; } // public long delete(int id){ SQLiteDatabase db = this.getWritableDatabase(); String whereClause=ID+"=?"; String[] whereArgs = { Integer.toString(id) }; long row = db.delete(TABLENAME, whereClause, whereArgs); return row; } // public List<Person> find(){ List<Person> list = new ArrayList<Person>(); Person person; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.query(TABLENAME, null, null, null, null, null, null); while(cursor.moveToNext()){ person= new Person(); int id = cursor.getInt(0); String name = cursor.getString(1); person.setId(id); person.setName(name); if(person!=null){ list.add(person); } } cursor.close(); db.close(); return list; } }
Test code:
package com.hkrt.db; import java.util.List; import android.test.AndroidTestCase; import android.util.Log; import com.hkrt.domain.Person; import com.hkrt.server.PersonHelper; public class PersonTest extends AndroidTestCase { String TAG="PersonTest"; public void testCreateDB(){ PersonHelper helper = new PersonHelper(this.getContext()); // helper.getWritableDatabase();// System.out.println(helper.insert("")); Log.i(TAG,String.valueOf(helper.insert("google")));// // System.err.println(helper.update(1));// // System.err.println(helper.delete(1));// List<Person> persons = helper.find();// for(Person p:persons){ System.err.println(p.toString()); } // mysql ?limit } }
Usually we do CUD, to do the processing of the transaction:
// public long insert(String text){ SQLiteDatabase db =this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(NAME, text); db.beginTransaction();// long row; try{ row = db.insert(TABLENAME, null, values); db.setTransactionSuccessful(); }finally{ db.endTransaction(); } return row; }
Note: To add the test permissions.
Generated database \ data \ data \ <package.name> \ databases \.
Can export the generated database to view the structure and data, here recommend a sqlite sqlite develoption
Add a query result data chart:
Dos command into the the linux control background. Query sqlite3 Data.
Step 1: We need the android-sdk-windows directory. Re-entry tools or Platform-tools query adb.exe
Note: Due to different version of adb.exe in a different directory.
Specific shown below:
If the database in Chinese, there will be problems is the issue of data encoding.
android sqlite3 Tools class can directly modify and use.
package com.hkrt.sql; import java.util.ArrayList; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; import com.hkrt.domain.User; import com.hkrt.domain.UserEnum; /** * :</br> * dbHelper = new DBHelper(this);</br> * dbHelper.open();</br> * </br> * dbHelper.close()</br> * @data 2010-11-12 */ public class DBHelper { //sqliteNULL Integer real text blob, private Context context; private SQLiteDatabase dbInstance; private DBCreator dbCreator; private static final String DB_NAME = "db_user"; private static final int DB_VERSION = 1; public static final String TABLE_NAME="user"; public static final String COLUMN_ID = "user_id"; public static final String COLUMN_NAME="user_name"; public static final String COLUMN_AGE="user_age"; public static final String COLUMN_SEX="user_sex"; public static final String COLUMN_TYPE = "user_type"; private static final String CREATE_TABLE= new StringBuffer().append("Create table ").append(TABLE_NAME) .append(" (") .append(COLUMN_ID).append(" integer primary key,") .append(COLUMN_NAME).append(" varchar(20) not null,") .append(COLUMN_AGE).append(" text not null,") .append(COLUMN_SEX).append(" text not null,") .append(COLUMN_TYPE).append(" integer not null)") .toString(); public DBHelper(Context context) { this.context = context; } public void open() { //?android?onCreate? dbCreator = new DBCreator(context,DB_NAME,null,DB_VERSION,CREATE_TABLE,TABLE_NAME); dbInstance = dbCreator.getWritableDatabase(); } public void close() { dbCreator.close(); } public void insert(ContentValues values) { dbInstance.insert(TABLE_NAME, null, values); } public void update(ContentValues values,String whereClause,String[] whereArgs) { dbInstance.update(TABLE_NAME, values, whereClause, whereArgs); } public void delete(String whereClause, String[] whereArgs) { dbInstance.delete(TABLE_NAME, whereClause, whereArgs); } /** * * @param sql SQL?? * @param selectionArgs ? * @return Cursor??JDBCResultSet???close() */ public Cursor query(String sql, String[] selectionArgs) { return dbInstance.rawQuery(sql, selectionArgs); } public ArrayList<User> getQueryAccountList(String sql,String[] args) { ArrayList<User> accoutList = new ArrayList<User>(); open(); Cursor cursor = query(sql, args); while (cursor.moveToNext()) { User user = new User(); user.setId(cursor.getInt(0)); user.setName(cursor.getString(1)); user.setAge(cursor.getString(2)); user.setSex(cursor.getString(3)); user.setType(UserEnum.getUserEnum(cursor.getInt(4))); accoutList.add(user); } //? if(!cursor.isClosed()) { cursor.close(); } close(); return accoutList; } /**Iduser*/ public User getUserById(int id){ User user =null; String sql="select * from "+TABLE_NAME +" where " + COLUMN_ID +" = ?"; ArrayList<User> users = getQueryAccountList(sql,new String[]{String.valueOf(id)}); if(users!=null){ user= new User(); user.setId(users.get(0).getId()); user.setName(users.get(0).getName()); user.setAge(users.get(0).getAge()); user.setSex(users.get(0).getSex()); user.setType(users.get(0).getType()); } return user; } /** * */ public ArrayList<User> getQueryAccountListLike(String str){ ArrayList<User> accoutList = new ArrayList<User>(); open(); StringBuffer sb = new StringBuffer(); sb.append("select * from ").append(TABLE_NAME).append(" where ").append(COLUMN_NAME).append(" like ").append("'"+str +"%'"); Cursor cursor = dbInstance.rawQuery(sb.toString(), new String[]{}); while (cursor.moveToNext()) { User user = new User(); user.setId(cursor.getInt(0)); user.setName(cursor.getString(1)); user.setAge(cursor.getString(2)); user.setSex(cursor.getString(3)); user.setType(UserEnum.getUserEnum(cursor.getInt(4))); accoutList.add(user); } //? if(!cursor.isClosed()) { cursor.close(); } close(); return accoutList; } private class DBCreator extends SQLiteOpenHelper { private Context context; private String createTableSql; private String tableName; public DBCreator(Context context, String dbname, CursorFactory factory, int version,String createTableSql,String tableName) { super(context, dbname, factory, version); this.context = context; this.createTableSql = createTableSql; this.tableName = tableName; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(createTableSql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("drop table if exists "+tableName); onCreate(db); } } }