方法一:
用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"));
}
}
}