Android关系型数据库应用——电话（短信）黑名单 .

1.打开数据库，创建数据表；

2.对数据进行crud（Create Read Update Delete）；

1.打开数据库，创建数据表；

           1.SQLiteOpenHelper

           2.SQLiteDatabase

                          .execSQL("create table blacknumber (_id integer primary key autoincrement, number varchar(20))");

2.对数据进行crud（Create Read Update Delete）；

           1.增：db.execSQL("insert into blacknumber (number) values(?)", new Object[]{number});

           2.删：db.execSQL("delete from blacknumber where number = ?", new Object[]{number});

           3.改：db.execSQL("update blacknumber set number = ? where number = ?", new Object[]{newnumber,oldnumber})

           4.查：Cursor cursor = db.rawQuery("select * from blacknumber where number =?", new String[]{number});

           5.查所有：Cursor cursor = db.rawQuery("select number from blacknumber", null);

1.打开数据库，创建数据表；

[java] view plain copy print ?

1. import android.content.Context;  
2. import android.database.sqlite.SQLiteDatabase;  
3. import android.database.sqlite.SQLiteOpenHelper;  
4.   
5. public class BlackNumberDBOpenHelper extends SQLiteOpenHelper {  
6.   
7.     public BlackNumberDBOpenHelper(Context context) {  
8.         super(context, "blacknumber.db", null, 1);  
9.           
10.     }  
11.   
12.     /** 
13.      * 数据库第一次被创建的时候 调用oncreate() 
14.      * 初始化数据库表结构的操作 
15.      */  
16.     @Override  
17.     public void onCreate(SQLiteDatabase db) {  
18.         db.execSQL("create table blacknumber (_id integer primary key autoincrement, number varchar(20))");  
19.     }  
20.   
21.     /** 
22.      * 数据库已经操作 并且 新的数据库的版本 大于 旧的数据库的版本 
23.      * 数据库的表结构信息的更新 
24.      */  
25.     @Override  
26.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
27.           
28.     }  
29.   
30. }  

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class BlackNumberDBOpenHelper extends SQLiteOpenHelper {

	public BlackNumberDBOpenHelper(Context context) {
		super(context, "blacknumber.db", null, 1);
		
	}

	/**
	 * 数据库第一次被创建的时候 调用oncreate()
	 * 初始化数据库表结构的操作
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table blacknumber (_id integer primary key autoincrement, number varchar(20))");
	}

	/**
	 * 数据库已经操作 并且 新的数据库的版本 大于 旧的数据库的版本
	 * 数据库的表结构信息的更新
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

}

2.对数据进行crud（Create Read Update Delete）；

[java] view plain copy print ?

1. public class BlackNumberDao {  
2.     BlackNumberDBOpenHelper helper ;  
3.   
4.     public BlackNumberDao(Context context) {  
5.         helper = new BlackNumberDBOpenHelper(context);  
6.     }  
7.       
8.     /** 
9.      * 添加一条黑名单号码 
10.      */  
11.     public void add(String number){  
12.         if(find(number)){  
13.             return ;  
14.         }  
15.         SQLiteDatabase db  = helper.getWritableDatabase();  
16.         if(db.isOpen()){  
17.             db.execSQL("insert into blacknumber (number) values(?)", new Object[]{number});  
18.             db.close();  
19.         }  
20.     }  
21.       
22.     /** 
23.      * 删除一条黑名单的数据 
24.      */  
25.     public void delete(String number){  
26.         SQLiteDatabase db  = helper.getWritableDatabase();  
27.         if(db.isOpen()){  
28.             db.execSQL("delete from blacknumber where number = ?", new Object[]{number});  
29.             db.close();  
30.         }  
31.     }  
32.   
33.     /** 
34.      * 更改一条黑名单的号码 
35.      */  
36.     public void update(String oldnumber,String newnumber){  
37.         SQLiteDatabase db  = helper.getWritableDatabase();  
38.         if(db.isOpen()){  
39.             db.execSQL("update blacknumber set number = ? where number = ?", new Object[]{newnumber,oldnumber});  
40.             db.close();  
41.         }  
42.     }  
43.   
44.     /** 
45.      * 查询一条黑名单号码 
46.      */  
47.     public boolean find(String number){  
48.         boolean result =false;  
49.         SQLiteDatabase db  = helper.getReadableDatabase();  
50.         if(db.isOpen()){  
51.             Cursor cursor = db.rawQuery("select * from blacknumber where number =?", new String[]{number});  
52.             if(cursor.moveToFirst()){  
53.                 result = true;  
54.             }  
55.             cursor.close();  
56.             db.close();  
57.         }  
58.         return result;  
59.     }  
60.       
61.     /** 
62.      * 查找全部黑名单号码 
63.      */  
64.     public List<String> findAll(){  
65.         List<String> numbers = new ArrayList<String>();  
66.         SQLiteDatabase db  = helper.getReadableDatabase();  
67.         if(db.isOpen()){  
68.             Cursor cursor = db.rawQuery("select number from blacknumber", null);  
69.             while (cursor.moveToNext()) {  
70.                 numbers.add( cursor.getString(0));  
71.             }  
72.             cursor.close();  
73.             db.close();  
74.         }  
75.         return numbers;  
76.     }  
77. }  

public class BlackNumberDao {
	BlackNumberDBOpenHelper helper ;

	public BlackNumberDao(Context context) {
		helper = new BlackNumberDBOpenHelper(context);
	}
	
	/**
	 * 添加一条黑名单号码
	 */
	public void add(String number){
		if(find(number)){
			return ;
		}
		SQLiteDatabase db  = helper.getWritableDatabase();
		if(db.isOpen()){
			db.execSQL("insert into blacknumber (number) values(?)", new Object[]{number});
			db.close();
		}
	}
	
	/**
	 * 删除一条黑名单的数据
	 */
	public void delete(String number){
		SQLiteDatabase db  = helper.getWritableDatabase();
		if(db.isOpen()){
			db.execSQL("delete from blacknumber where number = ?", new Object[]{number});
			db.close();
		}
	}

	/**
	 * 更改一条黑名单的号码
	 */
	public void update(String oldnumber,String newnumber){
		SQLiteDatabase db  = helper.getWritableDatabase();
		if(db.isOpen()){
			db.execSQL("update blacknumber set number = ? where number = ?", new Object[]{newnumber,oldnumber});
			db.close();
		}
	}

	/**
	 * 查询一条黑名单号码
	 */
	public boolean find(String number){
		boolean result =false;
		SQLiteDatabase db  = helper.getReadableDatabase();
		if(db.isOpen()){
			Cursor cursor = db.rawQuery("select * from blacknumber where number =?", new String[]{number});
			if(cursor.moveToFirst()){
				result = true;
			}
			cursor.close();
			db.close();
		}
		return result;
	}
	
	/**
	 * 查找全部黑名单号码
	 */
	public List<String> findAll(){
		List<String> numbers = new ArrayList<String>();
		SQLiteDatabase db  = helper.getReadableDatabase();
		if(db.isOpen()){
			Cursor cursor = db.rawQuery("select number from blacknumber", null);
			while (cursor.moveToNext()) {
				numbers.add( cursor.getString(0));
			}
			cursor.close();
			db.close();
		}
		return numbers;
	}
}