Android-操作SQLite数据库

方法一：

用SQLiteDatabase的execSQL来执行传统的sql语句

package com.example.android_db.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.android_db.db.DbOpenHelper;
import com.example.android_db.service.PersonService;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
/**
 * 用SQLiteDatabase的execSQL来执行sql语句
 * @author 连波
 *
 */
public class PersonDao implements PersonService {

	private DbOpenHelper helper = null;
	
	public PersonDao(Context context) {
		// TODO Auto-generated constructor stub
		helper = new DbOpenHelper(context);
	}

	@Override
	public boolean addPerson(Object[] params) {
		// TODO Auto-generated method stub
		boolean flag = false;
		//实现对数据库的添加删除和修改查询功能
		SQLiteDatabase database = null;
		try {
			String sql = "insert into person(name,address,sex) values(?,?,?)";
			database = helper.getWritableDatabase();//实现打开对写的操作
			database.execSQL(sql,params);
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally
		{
			if(database!=null)
				database.close();
		}
		return flag;
	}

	@Override
	public boolean deletePerson(Object[] params) {
		// TODO Auto-generated method stub
		boolean flag = false;
		SQLiteDatabase database = null;
		try {
			String sql = "delete from person where id = ?";
			database = helper.getWritableDatabase();
			database.execSQL(sql,params);
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally
		{
			if(database!=null)
			{
				database.close();
			}
		}
		return flag;
	}

	@Override
	public boolean updatePerson(Object[] params) {
		// TODO Auto-generated method stub
		boolean flag = false;
		SQLiteDatabase database = null;
		try {
			String sql = "update person set name=?, address=?,sex=? where id=? ";
			database = helper.getWritableDatabase();
			database.execSQL(sql,params);
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			if(database!=null)
				database.close();
		}
		return flag;
	}

	@Override
	public Map<String, String> viewPerson(String[] selectionArgs) 
	{
		Map<String,String> map = new HashMap<String,String>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from person where id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, selectionArgs);
			//获得数据库的列的个数
			int colums = cursor.getColumnCount();
			while (cursor.moveToNext()) 
			{
				for (int i = 0; i < colums; i++)
				{
					String cols_name = cursor.getColumnName(i);
					String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
					if(cols_value==null)
					{
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			if(database!=null)
				database.close();
		}
		return map;
	}

	@Override
	public List<Map<String, String>> listPersonMaps(String[] selectionArgs) {
		// TODO Auto-generated method stub
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from person ";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, selectionArgs);
			int colums= cursor.getColumnCount();
			while (cursor.moveToNext())
			{
				Map<String,String> map = new HashMap<String,String>();
				for (int i = 0; i <colums; i++) 
				{
					String cols_name = cursor.getColumnName(i);
					String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
					if(cols_value==null)
					{
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			if(database!=null)
				database.close();
		}
		return list;
	}
}

package com.example.android_db.test;

import java.util.List;
import java.util.Map;
import com.example.android_db.dao.PersonDao;
import com.example.android_db.db.DbOpenHelper;
import com.example.android_db.service.PersonService;
import android.content.Context;
import android.test.AndroidTestCase;
import android.util.Log;

public class MyTest extends AndroidTestCase {

	private final String TAG = "MyTest";
	public MyTest() {
		// TODO Auto-generated constructor stub
	}
	
	public void createDb()
	{
		Context context = getContext();
		DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
		//只有调用getReadableDatabase或者getWritableDatabase时候数据库才被创建
		dbOpenHelper.getReadableDatabase();
		Log.i(TAG, "createDb");
	}
	
	public void insertDB()
	{
		PersonService personService = new PersonDao(getContext());
		Object[] params = new String[]{"李四","江苏","男"};
		boolean flag = personService.addPerson(params);
		Log.i(TAG, "insertDB "+flag+"");
	}
	
	public void deleteDB()
	{
		PersonService personService = new PersonDao(getContext());
		Object[] params = {1};
		boolean flag = personService.deletePerson(params);
		Log.i(TAG, "deleteDB "+flag+"");
	}
	
	public void  updataDB()
	{
		PersonService personService = new PersonDao(getContext());
		Object[] params = {"张三","杭州","女",3};
		boolean flag = personService.updatePerson(params);
		Log.i(TAG, "updataDB "+flag+"");
	}
	
	public void querySingleDB()
	{
		PersonService personService = new PersonDao(getContext());
		String[] params = {"3"};
		Map<String, String> map = personService.viewPerson(params);
		Log.i(TAG, "querySingleDB name:"+map.get("name")+" address:"+map.get("address")+" sex:"+map.get("sex"));
	}
	
	public void queryManyDB()
	{
		PersonService personService = new PersonDao(getContext());
		List<Map<String, String>> list = personService.listPersonMaps(null);
		for (Map<String, String> map : list) 
		{
			Log.i(TAG, "queryManyDB name:"+map.get("name")+" address:"+map.get("address")+" sex:"+map.get("sex"));
		}
	}
}

方法二：

用SQLiteDatabase的insert delete update来操作数据库

<pre name="code" class="html">package com.example.android_db.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.android_db.db.DbOpenHelper;
import com.example.android_db.service.PersonService2;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
/**
 * 用SQLiteDatabase的insert delete update来操作数据库
 * @author 连波
 *
 */
public class PersonDao2 implements PersonService2 {

	private DbOpenHelper helper = null;
	public PersonDao2(Context context) {
		// TODO Auto-generated constructor stub
		helper = new DbOpenHelper(context);
	}
	@Override
	public boolean addPerson(ContentValues values) {
		// TODO Auto-generated method stub
		boolean flag = false;
		SQLiteDatabase database = null;
		try {
			database = helper.getWritableDatabase();
			//如果第二个参数nullColumnHack，当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），
			//为了防止这种情况，我们要在这里指定一个 列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入。
			long rowid = database.insert("person", null, values);
			//返回新添记录的行号，该行号是一个内部直，与主键id无关，发生错误返回-1
			if(rowid!=-1)
			{
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			if(database!=null)
				database.close();
		}
		return flag;
	}

	@Override
	public boolean deletePerson(String[] whereArgs) {
		// TODO Auto-generated method stub
		boolean flag = false;
		SQLiteDatabase database = null;
		try {
			database = helper.getWritableDatabase();
			int num = database.delete("person", "name=?", whereArgs);
			System.out.println(num);
			if(num!=0)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(database!=null)
				database.close();
		}
		return flag;
	}

	@Override
	public boolean updatePerson(ContentValues values,String[] whereArgs) {
		// TODO Auto-generated method stub
		boolean flag = false;
		SQLiteDatabase database = null;
		try {
			database = helper.getWritableDatabase();
			int num = database.update("person", values, "id=?", whereArgs);
			if(num>0)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(database!=null)
				database.close();
		}
		return flag;
	}

	@Override
	public Map<String, String> viewPerson(String selection,String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase database = null;
		Map<String,String> map = new HashMap<String,String>();
		try {
			database = helper.getWritableDatabase();
			Cursor cursor = database.query(true, "person", null, selection, selectionArgs, null, null, null, null);
			int cols_len = cursor.getColumnCount();
			while (cursor.moveToNext()) {
				for (int i = 0; i < cols_len; i++) 
				{
					String cols_name = cursor.getColumnName(i);
					String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
					map.put(cols_name, cols_value);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(database!=null)
				database.close();
		}
		return map;
	}

	@Override
	public List<Map<String, String>> listPersonMaps(String selection,String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase database = null;
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		try {
			database = helper.getWritableDatabase();
			Cursor cursor = database.query(true, "person", null, selection, selectionArgs, null, null, null, null);
			int cols_len = cursor.getColumnCount();
			while (cursor.moveToNext()) {
				Map<String,String> map = new HashMap<String,String>();
				for (int i = 0; i < cols_len; i++) 
				{
					String cols_name = cursor.getColumnName(i);
					String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
					map.put(cols_name, cols_value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(database!=null)
				database.close();
		}
		return list;
	}


}

package com.example.android_db.test;

import java.util.List;
import java.util.Map;
import com.example.android_db.dao.PersonDao2;
import com.example.android_db.service.PersonService2;
import android.content.ContentValues;
import android.test.AndroidTestCase;
import android.util.Log;

public class MyTest2 extends AndroidTestCase {

	private final String TAG = "MyTest";
	public MyTest2() {
		// TODO Auto-generated constructor stub
		
	}
	
	public void insertDB()
	{
		PersonService2 personService = new PersonDao2(getContext());
		ContentValues values = new ContentValues();//类似map的属性
		values.put("name", "赵五");
		values.put("address", "连云港");
		values.put("sex", "女");
		boolean flag = personService.addPerson(values);
		Log.i(TAG, "insertDB "+flag+"");
	}

	public void deleteDB()
	{
		PersonService2 personService = new PersonDao2(getContext());
		String[] whereArgs = {"张三"};
		boolean flag = personService.deletePerson(whereArgs);
		Log.i(TAG, "deleteDB "+flag+"");
	}
	
	public void upDateDB()
	{
		PersonService2 personService = new PersonDao2(getContext());
		ContentValues values = new ContentValues();//类似map的属性
		values.put("name", "许连波");
		values.put("address", "连云港");
		values.put("sex", "男");
		String[] whereArgs = {"2"};
		boolean flag = personService.updatePerson(values, whereArgs);
		Log.i(TAG, "upDateDB "+flag+"");
	}
	
	public void querySingleDB()
	{
		PersonService2 personService = new PersonDao2(getContext());
		String selection = "name=? and sex=?";
		String[] selectionArgs = {"李四","男"};
		Map<String, String> map = personService.viewPerson(selection, selectionArgs);
		Log.i(TAG, "querySingleDB name:"+map.get("name")+" address:"+map.get("address")+" sex:"+map.get("sex"));
	}
	
	public void queryManyDB()
	{
		PersonService2 personService = new PersonDao2(getContext());
		String selection = "name=? and sex=?";
		String[] selectionArgs = {"李四","男"};
		List<Map<String, String>> list = personService.listPersonMaps(selection, selectionArgs);
		for (Map<String, String> map : list) 
		{
			Log.i(TAG, "querySingleDB name:"+map.get("name")+" address:"+map.get("address")+" sex:"+map.get("sex"));
		}
		
	}
}