数据库的创建和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);
}
}
}
【黑马Android】(04)数据库的创建和sql语句增删改查/LinearLayout展示列表数据/ListView的使用和BaseAdater/内容提供者创建的更多相关文章
-
安卓开发之sql语句增删改查2(利用谷歌封装好的API进行增删改查)
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android=&quo ...
-
安卓开发之sql语句增删改查
package com.lidaochen.phonecall; import android.content.Context; import android.database.sqlite.SQLi ...
-
sql语句增删改查（转）
一.增:有4种方法 1.使用insert插入单行数据: 语法:insert [into] <表名> [列名] values <列值> 例 ...
-
sql语句增删改查（方便你我Ta）
又自学,把SQL的一些常用语句复习了一遍. 整理如下: 1增 1.1[插入单行]insert [into] <表名> (列名) values (列值)例:insert into Strde ...
-
SQL 语句(增删改查)
一.增:有4种方法1.使用insert插入单行数据: --语法:insert [into] <表名> [列名] values <列值> 例:insert into Strden ...
-
详解数据库引擎与SQL语句增删改查（非常详细，带例）
数据库系统(DBMS): 专门负责数据管理的工具.增加数据.创建索引.建立索引之间的关联关系.更新索引...... 连接器:PHP要访问MySQL,可以通过API访问,也可以通过PHP的驱动,而那个驱 ...
-
Android sqlite 数据库在java代码中的增删改查
private void queryPerson(PersonSQLiteOpenHelper personSQLiteOpenHelper) { SQLiteDatabase sqLiteDatab ...
-
sql语句增删改查与子查询
修改表 修改表 语法: Alter table <旧表名> rename [ TO] <新表名>; 例子:Alter table `demo01` rename `demo02 ...
-
数据库开发基础-SQl Server 控制数据库的服务+数据库的创建与管理（增删改查）
控制数据库的服务: 方法一: 1.Windows+R 打开运行 打开cmd 2.输入net start MSSQLserver 启动数据库服务 输入net stop MSSQLserver 关闭数据 ...
随机推荐
-
QDEZ集训笔记【更新中】
这是一个绝妙的比喻,如果青岛二中的台阶上每级站一只平度一中的猫,差不多站满了吧 自己的理解 [2016-12-31] [主席树] http://www.cnblogs.com/candy99/p/61 ...
-
常用eclipse 快捷键
Ctrl+1 快速修复(最经典的快捷键,就不用多说了)Ctrl+D: 删除当前行 Ctrl+Alt+↓ 复制当前行到下一行(复制增加)Ctrl+Alt+↑ 复制当前行到上一行(复制增加)Alt+↓ 当 ...
-
Scala Tail Recursion (尾递归)
Scala对尾递归进行了优化,甚至提供了专门的标注告诉编译器需要进行尾递归优化.不过这种优化仅限于严格的尾递归,间接递归等情况,不会被优化. 尾递归的概念 递归,大家都不陌生,一个函数直接或间接的调用 ...
-
php错误级别
E_ERROR 致命的运行错误.错误无法恢复,暂停执行脚本. E_WARNING 运行时警告(非致命性错误).非致命的运行错误,脚本执行不会停止. E_PARSE 编译时解析错误.解析错误只由分析器产 ...
-
使用ThinkPHP框架高速开发站点(多图)
使用ThinkPHP框架高速搭建站点 这一周一直忙于做实验室的站点,基本功能算是完毕了.比較有收获的是大概了解了ThinkPHP框架.写一些东西留作纪念吧.假设对于相同是Web方面新手的你有一丝丝帮助 ...
-
Mysql 权限修改何时生效
首先权限是记录在表中的,所以如果我们要修改权限只要修改表中的数据就可以了! 方法 1 grant ,revoke,set password,rename user .......等等 2 insert ...
-
unity3d 之本地推送
1. 本地推送主要包括在android和ios上,下面所有的代码都是本人写的,经过测试是没有问题的,已经运用到项目中了.首先是接口INotification: using System; public ...
-
全网首发！ Odoo 订单分解执行及供应链管理免费开源解决方案
引言 前一篇介绍了佛山王朝家具的案例背景.佛山王朝家具公司在全国有30多家门店,三个生产工厂.王朝家具有六大痛点问题: 订单迫切需要实现电子化管理及在线签名 总部分单工作量大,供应链效率低 配送和售后 ...
-
https搭建实例
:(用的)https://www.coderecord.cn/lets-encrypt-wildcard-certificates.html :acme.shvim .acme.sh/account. ...
-
linux_vim编辑文件无法高亮显示解决方法
打开/etc/vimrc(/etc/vim/vimrc不同版本,可能不一样) 在文档最后添加下面内容 syntax on 如图所示:我添加了高亮显示和显示行数 syntax on set nu 修改完 ...