首先关于SQLite的介绍百度上看看就大致了解的差不多了。
Android 操作数据库的关键步骤就在于实现API SQLiteOpenHelper,通常这个库辅助类来创建或打开数据库。
废话不多说直接上代码解释吧:
1 public class PersonDBOpenHelper extends SQLiteOpenHelper { 2 //context 上下文 3 //第二个是名字,如果名字空 为内存数据库 数据库名字 4 //CursorFactory 游标工厂 5 //version 数据库的版本号 最小为1 6 public PersonDBOpenHelper(Context context) { 7 super(context, "Person.db", null, 3); 8 // TODO Auto-generated constructor stub 9 //数据量存放再data/data/包名/database 下 10 } 11 /** 12 * 数据量在<b>第一次创建的时候</b>使用的方法 13 * 仅仅是在数据库第一次被创建的时候执行 14 */ 15 @Override 16 public void onCreate(SQLiteDatabase db) { 17 // TODO Auto-generated method stub 18 Log.i("MyTag", "数据库被创建了"); 19 String sql = "create table person (id integer primary key autoincrement," + 20 " name varchar(100), phone varchar(20))"; 21 22 //db.beginTransaction();开始事务 23 db.execSQL(sql); 24 } 25 //数据库升级的时候被使用 26 //这里面常常添加跟新数据库的sq语句 27 @Override 28 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 29 // TODO Auto-generated method stub 30 Log.i("MyTag", "数据库更新了"); 31 String sql = "insert into person (name,phone)values(‘zhangsan‘,‘110‘)"; 32 String sql2 = "insert into person (name,phone)values(‘liss‘,‘1520345‘)"; 33 String sql3 = "insert into person (name,phone)values(‘nuli‘,‘100‘)"; 34 String sql4 = "insert into person (name,phone)values(‘fengfou‘,‘11001578‘)"; 35 db.execSQL(sql4); 36 db.execSQL(sql3); 37 db.execSQL(sql2); 38 db.execSQL(sql); 39 Log.i("MyTag", "已经成功的插入了几行数据"); 40 } 41 42 }
再使用的时候如果只new出来上面的实现类oncreate()方法是不会执行的就是说数据库是不会创建的,通常都是执行了
getWritableDatabase()或者getReadableDatabase() 之后才会而执行。这点很重要。
关于数据库的操作有多种实现方法,可以像在javaweb那样操作数据库如下:
package com.person.dao; import com.tai.db.PersonDBOpenHelper; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; public class PersonDao { private PersonDBOpenHelper helper; public PersonDao(Context cont) { helper = new PersonDBOpenHelper(cont); } public void add(String name,String phone) { SQLiteDatabase Database = helper.getWritableDatabase(); String sql = "insert into person(name,phone)values(?,?)"; Database.execSQL(sql, new Object[]{name,phone}); Database.close();//关闭数据库是为了避免过多的连接 } public boolean find(String name) { SQLiteDatabase Database = helper.getReadableDatabase(); Cursor cursor = Database.rawQuery("select * from person where name = ?", new String[] {name}); boolean result = cursor.moveToFirst(); cursor.close();//游标也是要关闭的 return result; } public void update(String newphone,String name) { SQLiteDatabase Database = helper.getWritableDatabase(); String sql = "update person set phone = ? where name=?;)"; Database.execSQL(sql, new Object[]{newphone,name}); Database.close(); } public void delete(String name) { SQLiteDatabase Database = helper.getWritableDatabase(); String sql = "delete from person where name = ?;)"; Database.execSQL(sql, new Object[]{name}); Database.close(); } }
要记得再使用过SQLiteDatabase以及游标Cursor后把它关闭。
上面是通过执行SQL语句来操作数据库,android也给我们提供了方便的API使用方法了可以参照下面的方法
1 package com.person.dao; 2 3 import java.util.ArrayList; 4 import java.util.List; 5 6 import android.content.ContentValues; 7 import android.content.Context; 8 import android.database.Cursor; 9 import android.database.sqlite.SQLiteDatabase; 10 import android.util.Log; 11 12 import com.person.bean.Person; 13 import com.tai.db.PersonDBOpenHelper; 14 15 public class PersonDao2 { 16 17 private PersonDBOpenHelper helper; 18 19 public PersonDao2(Context cont) { 20 helper = new PersonDBOpenHelper(cont); 21 } 22 23 public boolean add(String name,String phone) 24 { 25 SQLiteDatabase Database = helper.getWritableDatabase(); 26 ContentValues contentValues = new ContentValues(); 27 contentValues.put("name", name); 28 contentValues.put("phone", phone); 29 long id = Database.insert("person", null, contentValues); 30 Database.close();//关闭数据库是为了避免过多的连接 31 return id!=-1; 32 } 33 34 35 public boolean find(String name) 36 { 37 SQLiteDatabase Database = helper.getReadableDatabase(); 38 Cursor cursor = Database.query("person", null, "name = ?", new String[]{name}, null, null, null); 39 boolean result = cursor.moveToFirst(); 40 cursor.close();//游标也是要关闭的 41 return result; 42 } 43 44 public boolean update(String newphone,String name) 45 { 46 SQLiteDatabase Database = helper.getWritableDatabase(); 47 // String sql = "update person set phone = ? where name=?;)"; 48 // Database.execSQL(sql, new Object[]{newphone,name}); 49 ContentValues contentValues = new ContentValues(); 50 contentValues.put("phone", newphone); 51 int row = Database.update("person", contentValues, "name = ?", new String[] {name}); 52 Database.close(); 53 return row!=-1; 54 } 55 56 public boolean delete(String name) 57 { 58 SQLiteDatabase Database = helper.getWritableDatabase(); 59 // String sql = "delete from person where name = ?;)"; 60 // Database.execSQL(sql, new Object[]{name}); 61 int row = Database.delete("person", "name = ?", new String[]{name}); 62 Database.close(); 63 Log.i("MyTag", row+""); 64 return row!=-1; 65 } 66 67 public List<Person> findAll() 68 { 69 SQLiteDatabase Database = helper.getReadableDatabase(); 70 List<Person> list = new ArrayList<Person>(); 71 Cursor cursor = Database.query("person", null, null, null, null, null, null); 72 while (cursor.moveToNext()) { 73 int id = cursor.getInt(cursor.getColumnIndex("id")); 74 String name = cursor.getString(cursor.getColumnIndex("name")); 75 String phone = cursor.getString(cursor.getColumnIndex("phone")); 76 Person person = new Person(id,name,phone); 77 list.add(person); 78 } 79 cursor.close(); 80 Database.close(); 81 return list; 82 } 83 }
这里各个方法的参数都差不多,就是把平时我们用得SQL语句拆分开来,也就query的多一点别的都差不多。
android.database.sqlite.SQLiteDatabase.query(String
table, String[]
columns, String
selection, String[]
selectionArgs, String
groupBy, String
having, String
orderBy)
String table,表名 String[] columns,要查询列表的列。传递null将返回所有列。String selection,查询条件String[] selectionArgs,查询条件对应的值
tring groupBy, String having, String orderBy 这些简要易懂和sql一样。