Sqlite的使用（二）

一、创建数据库

新建一个DBOpenHelper类 继承 SQLiteOpenHelper 实现数据库的创建和表的创建。

package com.example.testsqlite;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBOpenHelper extends SQLiteOpenHelper{

    public DBOpenHelper(Context context) {
         super(context, "rr.db", null, 1);  
        // TODO Auto-generated constructor stub
    }
    //初次创建
    @Override
    public void onCreate(SQLiteDatabase db) {
        try{
        // TODO Auto-generated method stub
        String sql="CREATE TABLE ContactInfo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT, ContactID varchar(20) NULL, NAMEPY varchar(20) NULL, Name nvarchar(20) NOT NULL, Sex INT NULL, Nationality nvarchar(10) NULL)";
        db.execSQL(sql);
            }
        catch(SQLException e){
             Log.e("gj", e.getMessage());
        }
    }

    //版本更新
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
         db.execSQL("DROP TABLE IF EXISTS ContactInfo");

            onCreate(db);
    }

}

实现对表的增删查改

public class ContactInfoServise {

        Context context = null;  
        DBOpenHelper dbOpenHelper = null;  
        private final int State_Initialization = 0; //初始化

public ContactInfoServise(Context context) {
    this.context = context;  
    dbOpenHelper = new DBOpenHelper( context );  
    // TODO Auto-generated constructor stub
}


/**
 * 新建联系人
 */
public Boolean AddContact(ContactInfoBySqlite coninfo){
    try{
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

        String sql="insert intoContactInfo(ContactID,"123",Name,Sex,Nationality)values(?,?,?,?,?)";
        Object[] val={coninfo.get_ContactID(),"123",coninfo.get_Name(),coninfo.get_Sex(),coninfo.get_Nationality()};
        db.execSQL(sql,val);
        return true;
    }
    catch(Exception ex){
        Log.i("sql", "error-->"+"AddContact"+ex.getMessage());
        return false;
    }
    }

/**
 * 查询联系人（单人）
 */
public  List<ContactInfoBySqlite> SelectContactByID(String contactID){
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
    String sql="select  distinct  *  from  ContactInfo where ContactID = ? ";
    String[] param={contactID};
    Cursor cursor = db.rawQuery(sql, param);
    List<ContactInfoBySqlite> list=new ArrayList<ContactInfoBySqlite>();
     while( cursor.moveToNext() ){
         ContactInfoBySqlite coninfo = new ContactInfoBySqlite();
            coninfo.set_id(cursor.getInt(cursor.getColumnIndex("id")));
            coninfo.set_NAMEPY(cursor.getString(cursor.getColumnIndex("NAMEPY")));
      coninfo.set_ContactID(cursor.getString(cursor.getColumnIndex("ContactID")));
            coninfo.set_Name(cursor.getString(cursor.getColumnIndex("Name")));             coninfo.set_Nationality(cursor.getString(cursor.getColumnIndex("Nationality")));
            coninfo.set_Sex(cursor.getInt(cursor.getColumnIndex("Sex")));
            list.add(coninfo);
    }
    cursor.close();
    return list;




}
/**
 * 删除联系人,直接从数据库删除
 */
public Boolean DelContactByIDTrue(String id){
try{

        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        String sql="delete from ContactInfo where id = ?";  
        Object[] val={id};
        db.execSQL(sql, val);
        return true;
    }catch(Exception ex){
        Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
        return false;
    }
}
/**
 * 修改联系人,
 */
public Boolean UpdataContact(ContactInfoBySqlite coninfo){
    try{


        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        String sql="update ContactInfo set ContactID = ?, NAMEPY = ?,Name = ?,Sex = ?,Nationality = ? where id = ?";
        Object[] val={coninfo.get_ContactID(),"123",coninfo.get_Name(),coninfo.get_Sex(),coninfo.get_Nationality(),coninfo.get_id()};
        db.execSQL(sql, val);
        return true;
    }catch(Exception ex){
        Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
        return false;
    }
}
}

测试

package com.example.testsqlite;

import java.util.ArrayList;
import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

public class ContactServiceTest extends AndroidTestCase {
     private static final String TAG = " ContactServiceTest";  

        public void testInsert()  
        {  
            ContactInfoServise pService = new  ContactInfoServise(mContext);
            ContactInfoBySqlite contacts=new ContactInfoBySqlite();
            contacts.set_ContactID("22");
            contacts.set_Address("43");
            contacts.set_Mobile("12434535");
            contacts.set_Name("323");
            contacts.set_Birthday("1996-05-3");
           pService.AddContact( contacts);

        }

        public void testFind()  
        {  
            List<ContactInfoBySqlite> info=new ArrayList<ContactInfoBySqlite>();             ContactInfoServise pService = new  ContactInfoServise(getContext());
             ContactInfoBySqlite contacts=new ContactInfoBySqlite();
              info= pService.SelectContactByID("22");
            Log.i("gj","_id="+info.get(0).get_ContactID()+" name="+info.get(0).get_Name());  
        }  
}

注意：测试的时候要在AndroidManifest.xml中加权限

<uses-library android:name="android.test.runner"/>

写在aplication标签内。

    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:targetPackage="com.example.testsqlite" android:label="aaa" />

    写在<manifest></manifest>内