使用api方式查询数据库和sqlite3工具

用API方式查询数据库：

DAO：

package com.itheima28.sqlitedemo.dao;import java.util.ArrayList;import java.util.List;import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;import com.itheima28.sqlitedemo.entities.Person;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;public class PersonDao2 {private static final String TAG = "TestCase";private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助对象public PersonDao2(Context context) {mOpenHelper = new PersonSQLiteOpenHelper(context);}/* * 添加到person表一条数据 */public void insert(Person person) {SQLiteDatabase db = mOpenHelper.getWritableDatabase();if (db.isOpen()) { // 判断数据库是否打开 ContentValues values=new ContentValues(); values.put("name",person.getName()); values.put("age",person.getAge());     long id=db.insert("person", null, values); Log.i(TAG,"id:  "+id); db.close(); // 数据库关闭}}public void delete(int id) {// 获得可写的数据库对象SQLiteDatabase db = mOpenHelper.getWritableDatabase();if (db.isOpen()) {String whereClause="_id=?";String[] whereArgs={id+" "};int count=db.delete("person", whereClause, whereArgs);Log.i(TAG,"删除了:   "+count+"行");db.close(); // 数据库关闭}}public void update(int id, String name) {SQLiteDatabase db = mOpenHelper.getWritableDatabase();if (db.isOpen()) {if (db.isOpen()) {   ContentValues values=new ContentValues();               values.put("name", name);               values.put("name", name);               int count=db.update("person", values, "_id=?", new String[]{id+" "});               Log.i(TAG,"修改了:   "+count);db.close(); // 数据库关闭}}}public List<Person> queryAll() {SQLiteDatabase db = mOpenHelper.getWritableDatabase();if (db.isOpen()) {String[] columns={"_id","name","age"};String selection=null;String[] selectionArgs=null;String groupBy=null;String having=null;String orderBy=null;Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);List<Person> personList = new ArrayList();if (cursor != null && cursor.getCount() >= 0) {int id;int age;String name;while (cursor.moveToNext()) {id = cursor.getInt(0); // 取第零列的数据name = cursor.getString(1);age = cursor.getInt(2);personList.add(new Person(id, name, age));}db.close();return personList;}db.close();}return null;}public Person queryItem(int id) {SQLiteDatabase db = mOpenHelper.getWritableDatabase();if (db.isOpen()) {String[] columns={"_id","name","age"};String selection="_id=?";String[] selectionArgs={id+" "};String groupBy=null;String having=null;String orderBy=null;Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);if (cursor != null && cursor.moveToFirst()) {//如果cursor不为空,且可以移动到第一行int _id = cursor.getInt(0);String name = cursor.getString(1);int age = cursor.getInt(2);db.close();return new Person(_id, name, age);}db.close();}return null;}}

测试：

package com.itheima28.sqlitedemo.test;import java.util.List;import com.itheima28.sqlitedemo.dao.PersonDao2;import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;import com.itheima28.sqlitedemo.entities.Person;import android.test.AndroidTestCase;import android.util.Log;public class TestCase2 extends AndroidTestCase {private static final String TAG = "TestCase";public void test(){//数据库什么时候创建PersonSQLiteOpenHelper openHelper=new PersonSQLiteOpenHelper(getContext());//第一次连接数据库是创建数据库文件,会执行onCreate方法openHelper.getReadableDatabase();}public void testInsert(){PersonDao2 dao=new PersonDao2(getContext());    dao.insert(new Person(99,"q",20));   }public void testDelete(){PersonDao2 dao=new PersonDao2(getContext());        dao.delete(1);}public void testUpdate(){PersonDao2 dao=new PersonDao2(getContext());        dao.update(1, "凤姐");}public void testQueryAll(){PersonDao2 dao=new PersonDao2(getContext());        List<Person> personList=dao.queryAll();        for(Person person:personList){        Log.i(TAG,person.toString());        }}public void testQueryItem(){PersonDao2 dao=new PersonDao2(getContext());        Person person=dao.queryItem(2);        Log.i(TAG,person.toString());}}

sqlite3的使用：

可以利用Android自带工具，在dos下通过shell命令操作数据库

进入数据库所在目录：

sqlite + 数据库全名进入sqlite>命令模式

.mode column更改排列样式

接下来就可以通过sql语句使用查询该数据库了。