the android data manipulation SQLite

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
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);
		}
	}
}

Posted by databasesql