数据库的创建和sql语句增删改查
1. 载入驱动.
2. 连接数据库.
3. 操作数据库.
创建表:
create table person(
_id integer primary key,
name varchar(20),
age integer
);
加入: insert into person(name, age) values('lisi', 19);
删除: delete from person where _id = 1;
改动: update person set name = '李四' where name = '王五';
查询全部: select * from person;
查询单条: select * from person where name = 'zhangsan';
SQLiteExpert3, pc上的工具软件。
package com.itheima28.sqlitedemo.dao; import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper; import com.itheima28.sqlitedemo.entities.Person; public class PersonDao { private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象 public PersonDao(Context context) { mOpenHelper = new PersonSQLiteOpenHelper(context); } /** * 加入到person表一条数据 * @param person */ public void insert(Person person) { SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { // 假设数据库打开, 运行加入的操作 // 运行加入到数据库的操作 db.execSQL("insert into person(name, age) values(?, ?);", new Object[]{person.getName(), person.getAge()}); db.close(); // 数据库关闭 } } /** * 更据id删除记录 * @param id */ public void delete(int id) { SQLiteDatabase db = mOpenHelper.getWritableDatabase(); // 获得可写的数据库对象 if(db.isOpen()) { // 假设数据库打开, 运行加入的操作 db.execSQL("delete from person where _id = ?;", new Integer[]{id}); db.close(); // 数据库关闭 } } /** * 依据id找到记录, 而且改动姓名 * @param id * @param name */ public void update(int id, String name) { SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { // 假设数据库打开, 运行加入的操作 db.execSQL("update person set name = ? where _id = ?;", new Object[]{name, id}); db.close(); // 数据库关闭 } } public List<Person> queryAll() { SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个仅仅读的数据库对象 if(db.isOpen()) { Cursor cursor = db.rawQuery("select _id, name, age from person;", null); if(cursor != null && cursor.getCount() > 0) { List<Person> personList = new ArrayList<Person>(); int id; String name; int age; while(cursor.moveToNext()) { id = cursor.getInt(0); // 取第0列的数据 id name = cursor.getString(1); // 取姓名 age = cursor.getInt(2); // 取年龄 personList.add(new Person(id, name, age)); } db.close(); return personList; } db.close(); } return null; } /** * 依据id查询人 * @param id * @return */ public Person queryItem(int id) { SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个仅仅读的数据库对象 if(db.isOpen()) { Cursor cursor = db.rawQuery("select _id, name, age from person where _id = ?
;", new String[]{id + ""}); if(cursor != null && cursor.moveToFirst()) { 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.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; /** * @author andong * 数据库帮助类, 用于创建和管理数据库的. */ public class PersonSQLiteOpenHelper extends SQLiteOpenHelper { private static final String TAG = "PersonSQLiteOpenHelper"; /** * 数据库的构造函数 * @param context * * name 数据库名称 * factory 游标project * version 数据库的版本 不能够小于1 */ public PersonSQLiteOpenHelper(Context context) { super(context, "itheima28.db", null, 2); } /** * 数据库第一次创建时回调此方法. * 初始化一些表 */ @Override public void onCreate(SQLiteDatabase db) { // 操作数据库 String sql = "create table person(_id integer primary key, name varchar(20), age integer);"; db.execSQL(sql); // 创建person表 } /** * 数据库的版本更新时回调此方法, * 更新数据库的内容(删除表, 加入表, 改动表) */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if(oldVersion == 1 && newVersion == 2) { Log.i(TAG, "数据库更新啦"); // 在person表中加入一个剩余金额列balance db.execSQL("alter table person add balance integer;"); } } }
package com.itheima28.sqlitedemo.entities; public class Person { private int id; private String name; private int age; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public Person() { super(); // TODO Auto-generated constructor stub } public Person(int id, String name, int age) { super(); this.id = id; this.name = name; this.age = age; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", age=" + age + "]"; } }
package com.itheima28.sqlitedemo.test; import java.util.List; import com.itheima28.sqlitedemo.dao.PersonDao; import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper; import com.itheima28.sqlitedemo.entities.Person; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.test.AndroidTestCase; import android.util.Log; public class TestCase extends AndroidTestCase { private static final String TAG = "TestCase"; public void test() { // 数据库什么时候创建 PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); // 第一次连接数据库时创建数据库文件. onCreate会被调用 openHelper.getReadableDatabase(); } public void testInsert() { PersonDao dao = new PersonDao(getContext()); dao.insert(new Person(0, "冠希", 28)); } public void testDelete() { PersonDao dao = new PersonDao(getContext()); dao.delete(1); } public void testUpdate() { PersonDao dao = new PersonDao(getContext()); dao.update(3, "凤姐"); } public void testQueryAll() { PersonDao dao = new PersonDao(getContext()); List<Person> personList = dao.queryAll(); for (Person person : personList) { Log.i(TAG, person.toString()); } } public void testQueryItem() { PersonDao dao = new PersonDao(getContext()); Person person = dao.queryItem(4); Log.i(TAG, person.toString()); } public void testTransaction() { PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db = openHelper.getWritableDatabase(); if(db.isOpen()) { try { // 开启事务 db.beginTransaction(); // 1. 从张三账户中扣1000块钱 db.execSQL("update person set balance = balance - 1000 where name = 'zhangsan';"); // ATM机, 挂掉了. // int result = 10 / 0; // 2. 向李四账户中加1000块钱 db.execSQL("update person set balance = balance + 1000 where name = 'lisi';"); // 标记事务成功 db.setTransactionSuccessful(); } finally { // 停止事务 db.endTransaction(); } db.close(); } } public void testTransactionInsert() { PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db = openHelper.getWritableDatabase(); if(db.isOpen()) { // 1. 记住当前的时间 long start = System.currentTimeMillis(); // 2. 開始加入数据 try { db.beginTransaction(); for (int i = 0; i < 10000; i++) { db.execSQL("insert into person(name, age, balance) values('wang" + i + "', " + (10 + i) + ", " + (10000 + i) + ")"); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } // 3. 记住结束时间, 计算耗时时间 long end = System.currentTimeMillis(); long diff = end - start; Log.i(TAG, "耗时: " + diff + "毫秒"); db.close(); } } }
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.itheima28.sqlitedemo" android:versionCode="1" android:versionName="1.0" > <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.itheima28.sqlitedemo" > </instrumentation> <uses-sdk android:minSdkVersion="8" android:targetSdkVersion="17" /> <application android:allowBackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme" > <uses-library android:name="android.test.runner" /> <activity android:name="com.itheima28.sqlitedemo.MainActivity" android:label="@string/app_name" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application> </manifest>
使用api方式查询数据库和sqlite3工具
adb shell
package com.itheima28.sqlitedemo.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 android.util.Log; import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper; import com.itheima28.sqlitedemo.entities.Person; public class PersonDao2 { private static final String TAG = "PersonDao2"; private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象 public PersonDao2(Context context) { mOpenHelper = new PersonSQLiteOpenHelper(context); } /** * 加入到person表一条数据 * @param person */ public void insert(Person person) { SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { // 假设数据库打开, 运行加入的操作 ContentValues values = new ContentValues(); values.put("name", person.getName()); // key作为要存储的列名, value对象列的值 values.put("age", person.getAge()); long id = db.insert("person", "name", values); Log.i(TAG, "id: " + id); db.close(); // 数据库关闭 } } /** * 更据id删除记录 * @param id */ 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(); // 数据库关闭 } } /** * 依据id找到记录, 而且改动姓名 * @param id * @param name */ public void update(int id, String name) { SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { // 假设数据库打开, 运行加入的操作 ContentValues values = new ContentValues(); 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.getReadableDatabase(); // 获得一个仅仅读的数据库对象 if(db.isOpen()) { String[] columns = {"_id", "name", "age"}; // 须要的列 String selection = null; // 选择条件, 给null查询全部 String[] selectionArgs = null; // 选择条件的參数, 会把选择条件中的? 替换成数据中的值 String groupBy = null; // 分组语句 group by name String having = null; // 过滤语句 String orderBy = null; // 排序 Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy); int id; String name; int age; if(cursor != null && cursor.getCount() > 0) { List<Person> personList = new ArrayList<Person>(); 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; } /** * 依据id查询人 * @param id * @return */ public Person queryItem(int id) { SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个仅仅读的数据库对象 if(db.isOpen()) { String[] columns = {"_id", "name", "age"}; // 须要的列 String selection = "_id = ?"; // 选择条件, 给null查询全部 String[] selectionArgs = {id + ""}; // 选择条件的參数, 会把选择条件中的? 替换成数据中的值 String groupBy = null; // 分组语句 group by name String having = null; // 过滤语句 String orderBy = null; // 排序 Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy); if(cursor != null && cursor.moveToFirst()) { // cursor对象不为null, 而且能够移动到第一行 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 android.test.AndroidTestCase; import android.util.Log; import com.itheima28.sqlitedemo.dao.PersonDao2; import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper; import com.itheima28.sqlitedemo.entities.Person; 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(0, "zhouqi", 88)); } public void testDelete() { PersonDao2 dao = new PersonDao2(getContext()); dao.delete(8); } public void testUpdate() { PersonDao2 dao = new PersonDao2(getContext()); dao.update(3, "fengjie"); } 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(4); Log.i(TAG, person.toString()); } }
事务的操作
事务:
update person set balance = balance - 100 where name = 'lisi';
update person set balance = balance + 100 where name = 'zhangsan';
// 开启事务
db.beginTransaction();
// 标记事务成功
db.setTransactionSuccessful();
// 停止事务
db.endTransaction();
public void testTransaction() { PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db = openHelper.getWritableDatabase(); if(db.isOpen()) { try { // 开启事务 db.beginTransaction(); // 1. 从张三账户中扣1000块钱 db.execSQL("update person set balance = balance - 1000 where name = 'zhangsan';"); // ATM机, 挂掉了. // int result = 10 / 0; // 2. 向李四账户中加1000块钱 db.execSQL("update person set balance = balance + 1000 where name = 'lisi';"); // 标记事务成功 db.setTransactionSuccessful(); } finally { // 停止事务 db.endTransaction(); } db.close(); } } public void testTransactionInsert() { PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db = openHelper.getWritableDatabase(); if(db.isOpen()) { // 1. 记住当前的时间 long start = System.currentTimeMillis(); // 2. 開始加入数据 try { db.beginTransaction(); for (int i = 0; i < 10000; i++) { db.execSQL("insert into person(name, age, balance) values('wang" + i + "', " + (10 + i) + ", " + (10000 + i) + ")"); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } // 3. 记住结束时间, 计算耗时时间 long end = System.currentTimeMillis(); long diff = end - start; Log.i(TAG, "耗时: " + diff + "毫秒"); db.close(); } }
LinearLayout展示列表数据
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" > <LinearLayout android:id="@+id/ll_list" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" > </LinearLayout> </ScrollView>
package com.itheima28.sqlitedemo; import java.util.List; import com.itheima28.sqlitedemo.dao.PersonDao; import com.itheima28.sqlitedemo.entities.Person; import android.os.Bundle; import android.app.Activity; import android.view.Menu; import android.view.View; import android.widget.LinearLayout; import android.widget.TextView; public class MainActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); LinearLayout llList = (LinearLayout) findViewById(R.id.ll_list); PersonDao dao = new PersonDao(this); List<Person> personList = dao.queryAll(); if(personList != null) { TextView tv; for (Person person : personList) { // 向线性布局中加入一个textview tv = new TextView(this); tv.setText(person.toString()); tv.setTextSize(18); llList.addView(tv); } } } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.main, menu); return true; } }
ListView的使用和BaseAdater
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" > <ListView android:id="@+id/listview" android:layout_width="match_parent" android:layout_height="match_parent" > </ListView> </LinearLayout>
package com.itheima28.sqlitedemo; import java.util.List; import com.itheima28.sqlitedemo.dao.PersonDao; import com.itheima28.sqlitedemo.entities.Person; import android.os.Bundle; import android.app.Activity; import android.util.Log; import android.view.Menu; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.LinearLayout; import android.widget.ListView; import android.widget.TextView; public class MainActivity extends Activity { private List<Person> personList; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); ListView mListView = (ListView) findViewById(R.id.listview); PersonDao dao = new PersonDao(this); personList = dao.queryAll(); // 把view层对象ListView和控制器BaseAdapter关联起来 mListView.setAdapter(new MyAdapter()); } /** * @author andong * 数据适配器 */ class MyAdapter extends BaseAdapter { private static final String TAG = "MyAdapter"; /** * 定义ListView的数据的长度 */ @Override public int getCount() { return personList.size(); } @Override public Object getItem(int position) { // TODO Auto-generated method stub return null; } @Override public long getItemId(int position) { // TODO Auto-generated method stub return 0; } /** * 此方法返回的是ListView的列表中某一行的View对象 * position 当前返回的view的索引位置 * convertView 缓存对象 * parent 就是ListView对象 */ @Override public View getView(int position, View convertView, ViewGroup parent) { TextView tv = null; if(convertView != null) { // 推断缓存对象是否为null, 不为null时已经缓存了对象 Log.i(TAG, "getView: 复用缓存" + position); tv = (TextView) convertView; } else { // 等于null, 说明第一次显示, 新创建 Log.i(TAG, "getView: 新建" + position); tv = new TextView(MainActivity.this); } tv.setTextSize(25); Person person = personList.get(position); // 获得指定位置的数据, 进行对TextView的绑定 tv.setText(person.toString()); return tv; } } }
package com.itheima28.sqlitedemo; import java.util.List; import com.itheima28.sqlitedemo.dao.PersonDao; import com.itheima28.sqlitedemo.entities.Person; import android.os.Bundle; import android.app.Activity; import android.util.Log; import android.view.LayoutInflater; import android.view.Menu; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.LinearLayout; import android.widget.ListView; import android.widget.TextView; public class MainActivity2 extends Activity { private List<Person> personList; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); ListView mListView = (ListView) findViewById(R.id.listview); PersonDao dao = new PersonDao(this); personList = dao.queryAll(); // 把view层对象ListView和控制器BaseAdapter关联起来 mListView.setAdapter(new MyAdapter()); } /** * @author andong * 数据适配器 */ class MyAdapter extends BaseAdapter { private static final String TAG = "MyAdapter"; /** * 定义ListView的数据的长度 */ @Override public int getCount() { return personList.size(); } @Override public Object getItem(int position) { // TODO Auto-generated method stub return null; } @Override public long getItemId(int position) { // TODO Auto-generated method stub return 0; } /** * 此方法返回的是ListView的列表中某一行的View对象 * position 当前返回的view的索引位置 * convertView 缓存对象 * parent 就是ListView对象 */ @Override public View getView(int position, View convertView, ViewGroup parent) { View view = null; if(convertView == null) { // 布局填充器对象, 用于把xml布局转换成view对象 LayoutInflater inflater = MainActivity2.this.getLayoutInflater(); view = inflater.inflate(R.layout.listview_item, null); } else { view = convertView; } // 给view中的姓名和年龄赋值 TextView tvName = (TextView) view.findViewById(R.id.tv_listview_item_name); TextView tvAge = (TextView) view.findViewById(R.id.tv_listview_item_age); Person person = personList.get(position); tvName.setText("姓名: " + person.getName()); tvAge.setText("年龄: " + person.getAge()); return view; } } }
listview_item.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal" android:padding="10dip" > <ImageView android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/f078" /> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_marginLeft="10dip" android:orientation="vertical" > <TextView android:id="@+id/tv_listview_item_name" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="张三" /> <TextView android:id="@+id/tv_listview_item_age" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="5dip" android:textColor="#FF0000" android:text="35" /> </LinearLayout> </LinearLayout>
其它两种绑定ListView数据的方式
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context=".MainActivity" > <ListView android:id="@+id/listview" android:layout_width="match_parent" android:layout_height="match_parent" /> </RelativeLayout>
listview_item.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="center_vertical" android:orientation="horizontal" android:padding="10dip" > <ImageView android:id="@+id/iv_icon" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/f007" /> <TextView android:id="@+id/tv_name" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginLeft="10dip" android:text="张三" android:textColor="#FF0000" android:textSize="23sp" /> </LinearLayout>
package com.itheima28.simpleadapterdemo; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import android.app.Activity; import android.os.Bundle; import android.view.Menu; import android.widget.ArrayAdapter; import android.widget.ListView; import android.widget.SimpleAdapter; public class MainActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); ListView mListView = (ListView) findViewById(R.id.listview); List<Map<String, Object>> data = new ArrayList<Map<String,Object>>(); Map<String, Object> map = new HashMap<String, Object>(); map.put("name", "张三1"); map.put("icon", R.drawable.f007); data.add(map); map = new HashMap<String, Object>(); map.put("name", "张三2"); map.put("icon", R.drawable.f007); data.add(map); map = new HashMap<String, Object>(); map.put("name", "张三3"); map.put("icon", R.drawable.f007); data.add(map); map = new HashMap<String, Object>(); map.put("name", "张三4"); map.put("icon", R.drawable.f007); data.add(map); map = new HashMap<String, Object>(); map.put("name", "张三5"); map.put("icon", R.drawable.f007); data.add(map); SimpleAdapter adapter = new SimpleAdapter( this, // 上下文 data, // listView绑定的数据 R.layout.listview_item, // listview的子条目的布局的id new String[]{"name", "icon"}, // data数据中的map集合里的key new int[]{R.id.tv_name, R.id.iv_icon}); // resource 中的id mListView.setAdapter(adapter); } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.main, menu); return true; } }
内容提供者创建
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.itheima28.sqlitedemo" android:versionCode="1" android:versionName="1.0" > <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.itheima28.sqlitedemo" > </instrumentation> <permission android:name="aa.bb.cc.read" ></permission> <permission android:name="aa.bb.cc.write" ></permission> <uses-sdk android:minSdkVersion="8" android:targetSdkVersion="17" /> <application android:allowBackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme" > <uses-library android:name="android.test.runner" /> <activity android:name="com.itheima28.sqlitedemo.MainActivity" android:label="@string/app_name" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <provider android:name=".providers.PersonContentProvider" android:authorities="com.itheima28.sqlitedemo.providers.PersonContentProvider" android:readPermission="aa.bb.cc.read" android:writePermission="aa.bb.cc.write" > </provider> </application> </manifest>
package com.itheima28.sqlitedemo.providers; import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper; import android.content.ContentProvider; import android.content.ContentUris; import android.content.ContentValues; import android.content.UriMatcher; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.net.Uri; public class PersonContentProvider extends ContentProvider { private static final String AUTHORITY = "com.itheima28.sqlitedemo.providers.PersonContentProvider"; private static final int PRESON_INSERT_CODE = 0; // 操作person表加入的操作的uri匹配码 private static final int PERSON_DELETE_CODE = 1; private static final int PERSON_UPDATE_CODE = 2; private static final int PERSON_QUERY_ALL_CODE = 3; private static final int PERSON_QUERY_ITEM_CODE = 4; private static UriMatcher uriMatcher; private PersonSQLiteOpenHelper mOpenHelper; // person表的数据库帮助对象 static { uriMatcher = new UriMatcher(UriMatcher.NO_MATCH); // 加入一些uri(分机号) // content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/insert uriMatcher.addURI(AUTHORITY, "person/insert", PRESON_INSERT_CODE); // content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/delete uriMatcher.addURI(AUTHORITY, "person/delete", PERSON_DELETE_CODE); // content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/update uriMatcher.addURI(AUTHORITY, "person/update", PERSON_UPDATE_CODE); // content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/queryAll uriMatcher.addURI(AUTHORITY, "person/queryAll", PERSON_QUERY_ALL_CODE); // content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/query/# uriMatcher.addURI(AUTHORITY, "person/query/#", PERSON_QUERY_ITEM_CODE); } @Override public boolean onCreate() { mOpenHelper = new PersonSQLiteOpenHelper(getContext()); return true; } @Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { SQLiteDatabase db = mOpenHelper.getReadableDatabase(); switch (uriMatcher.match(uri)) { case PERSON_QUERY_ALL_CODE: // 查询全部人的uri if(db.isOpen()) { Cursor cursor = db.query("person", projection, selection, selectionArgs, null, null, sortOrder); return cursor; // db.close(); 返回cursor结果集时, 不能够关闭数据库 } break; case PERSON_QUERY_ITEM_CODE: // 查询的是单条数据, uri末尾出有一个id if(db.isOpen()) { long id = ContentUris.parseId(uri); Cursor cursor = db.query("person", projection, "_id = ?", new String[]{id + ""}, null, null, sortOrder); return cursor; } break; default: throw new IllegalArgumentException("uri不匹配: " + uri); } return null; } @Override public String getType(Uri uri) { switch (uriMatcher.match(uri)) { case PERSON_QUERY_ALL_CODE: // 返回多条的MIME-type return "vnd.android.cursor.dir/person"; case PERSON_QUERY_ITEM_CODE: // 返回单条的MIME-TYPE return "vnd.android.cursor.item/person"; default: break; } return null; } @Override public Uri insert(Uri uri, ContentValues values) { switch (uriMatcher.match(uri)) { case PRESON_INSERT_CODE: // 加入人到person表中 SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { long id = db.insert("person", null, values); db.close(); return ContentUris.withAppendedId(uri, id); } break; default: throw new IllegalArgumentException("uri不匹配: " + uri); } return null; } @Override public int delete(Uri uri, String selection, String[] selectionArgs) { switch (uriMatcher.match(uri)) { case PERSON_DELETE_CODE: // 在person表中删除数据的操作 SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { int count = db.delete("person", selection, selectionArgs); db.close(); return count; } break; default: throw new IllegalArgumentException("uri不匹配: " + uri); } return 0; } @Override public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { switch (uriMatcher.match(uri)) { case PERSON_UPDATE_CODE: // 更新person表的操作 SQLiteDatabase db = mOpenHelper.getWritableDatabase(); if(db.isOpen()) { int count = db.update("person", values, selection, selectionArgs); db.close(); return count; } break; default: throw new IllegalArgumentException("uri不匹配: " + uri); } return 0; } }
内容提供者訪问和权限
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.itheima28.othercontentprovider" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:minSdkVersion="8" android:targetSdkVersion="17" /> <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.itheima28.othercontentprovider" > </instrumentation> <uses-permission android:name="aa.bb.cc.read"/> <uses-permission android:name="aa.bb.cc.write"/> <application android:allowBackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme" > <uses-library android:name="android.test.runner" /> <activity android:name="com.itheima28.othercontentprovider.MainActivity" android:label="@string/app_name" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application> </manifest>
package com.itheima28.othercontentprovider; import android.content.ContentResolver; import android.content.ContentUris; import android.content.ContentValues; import android.database.Cursor; import android.net.Uri; import android.test.AndroidTestCase; import android.util.Log; public class TextCase extends AndroidTestCase { private static final String TAG = "TextCase"; public void testInsert() { Uri uri = Uri.parse("content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/insert"); // 内容提供者訪问对象 ContentResolver resolver = getContext().getContentResolver(); ContentValues values = new ContentValues(); values.put("name", "fengjie"); values.put("age", 90); uri = resolver.insert(uri, values); Log.i(TAG, "uri: " + uri); long id = ContentUris.parseId(uri); Log.i(TAG, "加入到: " + id); } public void testDelete() { Uri uri = Uri.parse("content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/delete"); // 内容提供者訪问对象 ContentResolver resolver = getContext().getContentResolver(); String where = "_id = ?"; String[] selectionArgs = {"21"}; int count = resolver.delete(uri, where, selectionArgs); Log.i(TAG, "删除行: " + count); } public void testUpdate() { Uri uri = Uri.parse("content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/update"); // 内容提供者訪问对象 ContentResolver resolver = getContext().getContentResolver(); ContentValues values = new ContentValues(); values.put("name", "lisi"); int count = resolver.update(uri, values, "_id = ?", new String[]{"20"}); Log.i(TAG, "更新行: " + count); } public void testQueryAll() { Uri uri = Uri.parse("content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/queryAll"); // 内容提供者訪问对象 ContentResolver resolver = getContext().getContentResolver(); Cursor cursor = resolver.query(uri, new String[]{"_id", "name", "age"}, null, null, "_id desc"); if(cursor != null && cursor.getCount() > 0) { int id; String name; int age; while(cursor.moveToNext()) { id = cursor.getInt(0); name = cursor.getString(1); age = cursor.getInt(2); Log.i(TAG, "id: " + id + ", name: " + name + ", age: " + age); } cursor.close(); } } public void testQuerySingleItem() { Uri uri = Uri.parse("content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/query/#"); // 在uri的末尾加入一个id content://com.itheima28.sqlitedemo.providers.PersonContentProvider/person/query/20 uri = ContentUris.withAppendedId(uri, 20); // 内容提供者訪问对象 ContentResolver resolver = getContext().getContentResolver(); Cursor cursor = resolver.query(uri, new String[]{"_id", "name", "age"}, null, null, null); if(cursor != null && cursor.moveToFirst()) { int id = cursor.getInt(0); String name = cursor.getString(1); int age = cursor.getInt(2); cursor.close(); Log.i(TAG, "id: " + id + ", name: " + name + ", age: " + age); } } }