首页 > 代码库 > Android下利用SQLite数据库实现增删改查
Android下利用SQLite数据库实现增删改查
1： 首先介绍如何利用adb查看数据库
1： adb shell
2： cd /data/data/包名/databases
3: sqlite3 数据库
4 接下来就可以进行数据库的sql语法的使用了
bean对象：
public class Person { private int id; private String name; private String number; }
数据库的创建以及表的创建：
package com.example.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class BaseDao extends SQLiteOpenHelper{ /** * 数据库的构造方法， 用来定义数据库的名称， 数据库查询结果集， 数据库的版本号 * */ public BaseDao(Context context) { super(context, "person.db", null, 1); } /** *数据库别创建的时候调用的方法 * */ @Override public void onCreate(SQLiteDatabase db) { //初始化数据库的表结构 db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub }}
利用纯SQL语句实现增删改查
package com.example.dao;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.bean.Person;import com.example.db.BaseDao;public class PersonDaoSQL { private BaseDao helper; //helper /** * 在构造方法中实例化helper * @param context */ public PersonDaoSQL(Context context) { helper = new BaseDao(context); } /** * 增 * @param person */ public void add(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into person (name, number) values(?, ?)", new String[] {person.getName(), person.getNumber()}); db.close(); } /** * 按姓名查 * @param person * @return */ public boolean find(Person person) { SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person where name = ?", new String[] {person.getName()}); boolean result = cursor.moveToNext(); cursor.close(); db.close(); return result; } /** * 删除一条记录 */ public void delete(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from person where name = ?", new String[]{person.getName()}); db.close(); } /** * 更新 */ public void update(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("update person set number = ? where name = ?", new String[]{person.getNumber(),person.getName()}); db.close(); } public List<Person> findAll() { SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select id, name, number from person", null); List<Person> persons = new ArrayList<Person>(); Person person = null; while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String number = cursor.getString(cursor.getColumnIndex("number")); person = new Person(id, name, number); persons.add(person); } cursor.close(); db.close(); return persons; }}
利用API实现增删改查：
package com.example.dao;import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.bean.Person;import com.example.db.BaseDao;public class PersonDaoAPI { private BaseDao helper; //helper /** * 在构造方法中实例化helper * @param context */ public PersonDaoAPI(Context context) { helper = new BaseDao(context); } public long add(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("name", person.getName()); values.put("number", person.getNumber()); long result = db.insert("person", null, values); db.close(); return result; } public int delete(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); int result = db.delete("person", "name = ?", new String[] {person.getName()}); db.close(); return result; } public int update(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("number", person.getNumber()); int result = db.update("person", values, "name=?", new String[] {person.getName()}); db.close(); return result; } public boolean find(Person person) { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.query("person", null, "name = ?", new String[]{person.getName()}, null, null, null); boolean result = cursor.moveToNext(); db.close(); return result; } public List<Person> findAll() { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.query("person", null, null, null, null, null,null); List<Person> persons = new ArrayList<Person>(); Person person = null; while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String number = cursor.getString(cursor.getColumnIndex("number")); person = new Person(id, name, number); persons.add(person); } db.close(); return persons; } }
利用Junit进行测试，
注意一定要在AndroidManifest.xml中添加：
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.example.db" />
和
<uses-library android:name="android.test.runner" />
package com.example.test;import java.util.List;import android.test.AndroidTestCase;import com.example.bean.Person;import com.example.dao.PersonDaoAPI;import com.example.dao.PersonDaoSQL;import com.example.db.BaseDao;public class TestPersonDB extends AndroidTestCase { /** * 测试数据库的创建 * @throws Exception */ public void testCreateDB() throws Exception{ BaseDao helper = new BaseDao(getContext()); helper.getReadableDatabase(); } /** * 测试添加 */ public void testAdd() { //PersonDaoSQL dao = new PersonDaoSQL(getContext()); PersonDaoAPI dao = new PersonDaoAPI(getContext()); for (int i = 0; i < 10 ; ++i) { Person person = new Person("lisi" + i + 1, "1347515770" + i); dao.add(person); } } /** * 测试修改 */ public void testUpdate() { //PersonDaoSQL dao = new PersonDaoSQL(getContext()); PersonDaoAPI dao = new PersonDaoAPI(getContext()); Person person = new Person("lisi" + 11, "1347515770" + "x"); dao.update(person); } /** * 测试单个查询查询 */ public void testFind() { //PersonDaoSQL dao = new PersonDaoSQL(getContext()); PersonDaoAPI dao = new PersonDaoAPI(getContext()); Person person = new Person("lisi" + 21, "1347515770" + "x"); boolean result = dao.find(person); assertEquals(true, result); } /** * 查询所有 */ public void testFindAll() { //PersonDaoSQL dao = new PersonDaoSQL(getContext()); PersonDaoAPI dao = new PersonDaoAPI(getContext()); List<Person> persons = dao.findAll(); for (Person person : persons) { System.out.println(person.getName()); } } /** * 删除 */ public void testDelete() { PersonDaoSQL dao = new PersonDaoSQL(getContext()); Person person = new Person("lisi" + 21, "1347515770" + "x"); dao.delete(person); }}
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。