SQLiteDataBase是android中自带的微型数据库,与其它的数据库操作没有很大的差别,下面是对android操作数据库简单的介绍。
SQLiteOpenHelper是一个抽象类,是管理操作数据库的一个助手,我们要创建一个数据库,有三步:
首先,要先创建一个继承SQLiteOpenHelper类的子类。
如:
DbOpenHelper就是继承了SQLiteOpenHelper的类,并且要实现以下两个方法:
方法一:
在oncreate中实现数据库表格创建的方法,这个oncreate方法只会在定义DbOpenHelper这个对象时就首先调用,且只会调用一次。
方法二:
SQLiteOpenHelper是new一个DbOenHelper的构造函数.这个构造函数负责创建一个创建、打开或管理数据的助手,即DbOpenHelper对象。记住,这个数据库并不会在new后立刻创建或打开数据库,直到getWritableDatabase()或者getReadableDatabase()方法才会创建或打开数据库。
然后,通过DbOenHelper定义一个对象,
如:
最后,通过调用getWritableDatabase()创建或打开数据库,放回数据库对象,
如:
获得数据库对象后database后,就可以通过这个对象对数据进行增删改除基本操作。
SQLiteDataBase提供了两套进行增删改除基本操作语句,一套是数据库sql基本语句,另一套是android封装好了的语句,如下是封装的插入,删除,更新语句:
注意:
selection SQL语句的where子句
selectionArgs selection的参数,如果包含?,?号将会被参数所替换
sortOrder SQL的ORDER BY排序子句。
下面是对sql语句对操作数据库的实例:
在DbOenHelper.java文件中:
1 public class DbOpenHelper extends SQLiteOpenHelper { 2 3 private static String name = "mydb.db";// 表示数据库的名称 4 private static int version = 2;// 表示数据库的版本号码 5 6 public DbOpenHelper(Context context) { 7 super(context, name, null, version); 8 } 9 10 // 当数据库创建的时候,是第一次被执行,完成对数据库的表的创建 11 @Override 12 public void onCreate(SQLiteDatabase db) { 13 // TODO Auto-generated method stub 14 //支持的数据类型:整型数据,字符串类型,日期类型,二进制的数据类型, 15 String sql = "create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))"; 16 db.execSQL(sql); 17 } 18 19 @Override 20 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 21 // TODO Auto-generated method stub 22 String sql = "alter table person add sex varchar(8)"; 23 db.execSQL(sql); 24 }
在PersonService.java文件中:
1 public interface PersonService { 2 3 public boolean addPerson(Object[] params); 4 5 public boolean deletePerson(Object[] params); 6 7 public boolean updatePerson(Object[] params); 8 9 public Map<String,String> viewPerson(String[] selectionArgs); 10 11 public List<Map<String,String>> listPersonMaps(String[] selectionArgs); 12 }
在PersonDao.java文件中:
1 public class PersonDao implements PersonService { 2 3 private DbOpenHelper helper = null; 4 public PersonDao(Context context) { 5 // TODO Auto-generated constructor stub 6 helper = new DbOpenHelper(context); 7 } 8 9 @Override 10 public boolean addPerson(Object[] params) { 11 // TODO Auto-generated method stub 12 boolean flag = false; 13 //实现对数据库的添加删除和修改查询的功能 14 SQLiteDatabase database = null; 15 try { 16 String sql = "insert into person(name,address,sex) values(?,?,?)"; 17 database = helper.getWritableDatabase();//实现对数据库的写的操作 18 database.execSQL(sql, params); 19 flag = true; 20 } catch (Exception e) { 21 // TODO: handle exception 22 e.printStackTrace(); 23 }finally{ 24 if(database!=null){ 25 database.close(); 26 } 27 } 28 return flag; 29 } 30 31 @Override 32 public boolean deletePerson(Object[] params) { 33 // TODO Auto-generated method stub 34 boolean flag = false; 35 SQLiteDatabase database = null; 36 try { 37 String sql = "delete from person where id = ? "; 38 database = helper.getWritableDatabase(); 39 database.execSQL(sql, params); 40 flag = true; 41 } catch (Exception e) { 42 // TODO: handle exception 43 }finally{ 44 if(database!=null){ 45 database.close(); 46 } 47 } 48 return flag; 49 } 50 51 @Override 52 public boolean updatePerson(Object[] params) { 53 // TODO Auto-generated method stub 54 boolean flag = false; 55 SQLiteDatabase database = null; 56 try { 57 String sql = "update person set name = ? ,address = ?, sex = ? where id = ? "; 58 database = helper.getWritableDatabase(); 59 database.execSQL(sql, params); 60 flag = true; 61 } catch (Exception e) { 62 // TODO: handle exception 63 }finally{ 64 if(database!=null){ 65 database.close(); 66 } 67 } 68 return flag; 69 } 70 71 @Override 72 public Map<String, String> viewPerson(String[] selectionArgs) { 73 Map<String,String> map = new HashMap<String, String>(); 74 SQLiteDatabase database = null; 75 try { 76 String sql = "select * from person where id = ? "; 77 database = helper.getReadableDatabase(); 78 Cursor cursor = database.rawQuery(sql, selectionArgs); 79 //获得数据库的列的个数 80 int colums = cursor.getColumnCount(); 81 while(cursor.moveToNext()){ 82 for(int i=0;i<colums;i++){ 83 String cols_name = cursor.getColumnName(i); 84 String cols_value = cursor.getString(cursor.getColumnIndex(cols_name)); 85 if(cols_value==null){ 86 cols_value = ""; 87 } 88 map.put(cols_name, cols_value); 89 } 90 } 91 } catch (Exception e) { 92 // TODO: handle exception 93 }finally{ 94 if(database!=null){ 95 database.close(); 96 } 97 } 98 return map; 99 } 100 101 @Override 102 public List<Map<String, String>> listPersonMaps(String[] selectionArgs) { 103 // TODO Auto-generated method stub 104 List<Map<String,String>> list = new ArrayList<Map<String,String>>(); 105 String sql = "select * from person "; 106 SQLiteDatabase database = null; 107 try { 108 database = helper.getReadableDatabase(); 109 Cursor cursor = database.rawQuery(sql, selectionArgs); 110 int colums = cursor.getColumnCount(); 111 while(cursor.moveToNext()){ 112 Map<String,String> map = new HashMap<String, String>(); 113 for(int i=0;i<colums;i++){ 114 String cols_name = cursor.getColumnName(i); 115 String cols_value = cursor.getString(cursor.getColumnIndex(cols_name)); 116 if(cols_value==null){ 117 cols_value=""; 118 } 119 map.put(cols_name, cols_value); 120 } 121 list.add(map); 122 } 123 } catch (Exception e) { 124 // TODO: handle exception 125 }finally{ 126 if(database!=null){ 127 database.close(); 128 } 129 } 130 return list; 131 } 132 133 }
在AndroidTestCase.java文件中:
1 public class MyTest extends AndroidTestCase { 2 3 public MyTest() { 4 // TODO Auto-generated constructor stub 5 } 6 7 public void createDb(){ 8 DbOpenHelper helper = new DbOpenHelper(getContext()); 9 helper.getWritableDatabase(); 10 } 11 12 public void insertDB(){ 13 PersonService service = new PersonDao(getContext()); 14 Object[] params = {"李斯","广西","女"}; 15 boolean flag = service.addPerson(params); 16 System.out.println("--->>"+flag); 17 } 18 19 public void deleteDB(){ 20 PersonService service = new PersonDao(getContext()); 21 Object[] params = {1}; 22 boolean flag = service.deletePerson(params); 23 System.out.println("--->>"+flag); 24 } 25 26 public void updateDB(){ 27 PersonService service = new PersonDao(getContext()); 28 Object[] params = {"王五","上海","不祥","3"}; 29 service.updatePerson(params); 30 } 31 32 public void viewDB(){ 33 PersonService service = new PersonDao(getContext()); 34 String[] selectionArgs = {"3"}; 35 Map<String, String> map = service.viewPerson(selectionArgs); 36 Log.i("Test", "-->>"+map.toString()); 37 } 38 39 public void listDB(){ 40 PersonService service = new PersonDao(getContext()); 41 42 List<Map<String,String>> list = service.listPersonMaps(null); 43 Log.i("Test", "-->>"+list.toString()); 44 } 45 }