SQLite增删改查

2022-02-11 01:11:23

  在http://www.cnblogs.com/kingshow123/p/sqlitecreate.html中，实现了数据库的创建和升级。没有增加向数据库中添加、删除、修改和查询Data的代码，这里用一种方式实现这些功能。

一、在先前的工程中增加PersonService接口和PersonDao类。看下图：

SQLite增删改查    

二、PersonService接口代码

SQLite增删改查
package com.bruce.db_test.service;

import java.util.List;
import java.util.Map;

public interface PersonService {
    public boolean insertPerson(Object[] values);
    public boolean deletePerson(Object[] values);
    public boolean updatePerson(Object[] values);
    public Map<String, String> findPerson(String[] selectionArgs);
    public List<Map<String, String>> listPerson(String[] selectionArgs);
}
View Code

三、PersonDao实现PersonService接口，代码如下

SQLite增删改查
package com.bruce.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.bruce.db_test.db.DbOpenHelper;
import com.bruce.db_test.service.PersonService;

public class PersonDao implements PersonService {
    private String TAG = "MyTest";
    private DbOpenHelper helper = null;
    public PersonDao(Context context){
        helper = new DbOpenHelper(context);
    }
    @Override
    public boolean insertPerson(Object[] values) {
        Log.i(TAG, "-->insertPerson");
        boolean flag = false;
        SQLiteDatabase database = null;
        try {
            String sql = "insert into person(name,address,sex) values(?,?,?)";
            database = helper.getWritableDatabase();
            database.execSQL(sql, values);
            flag = true;
            Log.i(TAG, "-->insertPerson"+ flag);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(database != null){
                database.close();
            }
        }
        return flag;
    }

    @Override
    public boolean deletePerson(Object[] values) {
        Log.i(TAG, "-->deletePerson");
        boolean flag = false;
        SQLiteDatabase database = null;
        try {
            String sql = "delete from person where id = ? ";
            database = helper.getWritableDatabase();
            database.execSQL(sql, values);
            flag = true;
            Log.i(TAG, "-->deletePerson"+ flag);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(database != null){
                database.close();
            }
        }
        return flag;
    }

    @Override
    public boolean updatePerson(Object[] values) {
        Log.i(TAG, "-->updatePerson");
        boolean flag = false;
        SQLiteDatabase database = null;
        try {
            String sql = "update person set name = ?, address = ?, sex = ? where id = ? ";
            database = helper.getWritableDatabase();
            database.execSQL(sql, values);
            flag = true;
            Log.i(TAG, "-->updatePerson"+ flag);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(database != null){
                database.close();
            }
        }
        return flag;
    }

    @Override
    public Map<String, String> findPerson(String[] selectionArgs) {
        Log.i(TAG, "-->findPerson begin");
        Map<String, String> map = new HashMap<String, String>();
        SQLiteDatabase database = null;
        Cursor cursor = null;
        try {
            String sql = "select * from person where id = ? ";
            database = helper.getWritableDatabase();
            cursor = database.rawQuery(sql, selectionArgs);
            int count = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                for(int i=0; i<count; 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);
                }
            }
            
            Log.i(TAG, "-->findPerson over"+map.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(database != null){
                database.close();
            }
        }
        return map;
    }

    @Override
    public List<Map<String, String>> listPerson(String[] selectionArgs) {
        Log.i(TAG, "listPerson run");
        List<Map<String, String>> list = new ArrayList<Map<String,String>>();
        SQLiteDatabase database = null;
        Cursor cursor = null;
        try {
            String sql = "select * from person ";
            database = helper.getWritableDatabase();
            cursor = database.rawQuery(sql, selectionArgs);
            int count = cursor.getColumnCount();
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<String, String>();
                for(int i = 0 ;i < count; 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;
    }

}
View Code

四、在MyTest中添加测试代码，全部代码如下

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package com.bruce.db_test.test;
 
import java.util.List;
import java.util.Map;
 
import com.bruce.dao.PersonDao;
import com.bruce.db_test.db.DbOpenHelper;
import com.bruce.db_test.service.PersonService;
 
import android.test.AndroidTestCase;
import android.util.Log;
 
public class MyTest extends AndroidTestCase {
    private String TAG = "MyTest";
    public void dbCreateTest(){
        Log.i(TAG, "test begin");
        DbOpenHelper helper = new DbOpenHelper(getContext());
        helper.getWritableDatabase();
        Log.i(TAG, "test end");
    }
     
    public void addPersonTest(){
        PersonService service = new PersonDao(getContext());
        Object[] values = {"奥巴马","美国","男"};
        boolean flag = service.insertPerson(values);
        Log.i(TAG, "--->addPerson" + flag);
    }
     
    public void deletePersonTest(){
        PersonService service = new PersonDao(getContext());
        Object[] values = {"2"};
        boolean flag = service.deletePerson(values);
        Log.i(TAG, "--->test-deletePerson" + flag);
    }
     
    public void updatePersonTest(){
        PersonService service = new PersonDao(getContext());
        Object[] values = {"刘德华","北京","男","1"};
        boolean flag = service.updatePerson(values);
        Log.i(TAG, "--->test-updatePerson" + flag);
    }
     
    public void viewPersonTest(){
        PersonService service = new PersonDao(getContext());
        String[] selectionArgs = {"1"};
        Map<String, String> map = service.findPerson(selectionArgs);
        Log.i(TAG, "-->test "+ map.toString());
         
    }
     
    public void listPersonTest(){
        Log.i(TAG, "listPerosn begin");
        PersonService service = new PersonDao(getContext());
        //String[] selectionArgs = {null};
        List<Map<String, String>> list = service.listPerson(null);
        Log.i(TAG, "-->"+list.toString());
    }
}

 

      这是自己写SQL语句来实现的方法，后面介绍使用系统API来实现。

SQLite增删改查

上一篇：linux中重置服务器的mysql用户密码


下一篇：SqlHelper类的使用