1、MainActivity
package com.example.sqlitetest;
import java.util.List;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;
public class MainActivity extends Activity {
private ListView personLV;
private List<Person> persons;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
PersonDao dao = new PersonDao(this);
persons = dao.queryAll1();
personLV = (ListView) findViewById(R.id.personLV);
personLV.setAdapter(new MyBaseAdapter());//把ListView显示出来
personLV.setOnItemClickListener(new MyOnItemClickListener());
}
private class MyOnItemClickListener implements OnItemClickListener{
@Override
public void onItemClick(AdapterView<?> parent, View view, int position,
long id) {
Person p = (Person) parent.getItemAtPosition(position);
Toast.makeText(getApplicationContext(), p.getName() , 1).show();
}
}
private class MyBaseAdapter extends BaseAdapter{//定义一个Adapter,每一个Person生成一个条目，所有条目都加载到ListView中
@Override
public int getCount() {//返回ListView要装入的条目的数量
return persons.size();
}
@Override
public Object getItem(int position) {//返回指定位置上的条目
return persons.get(position);
}
@Override
public long getItemId(int position) {//返回条目的ID
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
//之所以可以返回一个View,是因为R.layout.item的根节点是一个LinearLayout,它是View的一个子类
View item = View.inflate(getApplicationContext(), R.layout.item, null);
TextView idTV = (TextView) item.findViewById(R.id.idTV);
TextView nameTV = (TextView) item.findViewById(R.id.nameTV);
TextView balanceTV = (TextView) item.findViewById(R.id.balanceTV);
Person p = persons.get(position);
idTV.setText(p.getId() + "");//这里需要注意...如果不加"",会到R文件中去找
nameTV.setText(p.getName());
balanceTV.setText(p.getBalance() + "");
return item;
}
}
@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;
}
}
2、DBOpenHelper
package com.example.sqlitetest;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context){//父类没有无参构造函数,必须显示调用有参构造函数.
/*
* 由于父类没有无参构造函数, 必须显式调用有参的构造函数
* 参数1: 上下文环境, 用来确定数据库文件存储的目录..创建的数据库存在于/data/data/应用的报名/databases/xxx.db
* 参数2: 数据库文件的名字
* 参数3: 生成游标的工厂, 填null就是使用默认的
* 参数4: 数据库的版本, 从1开始
*/
super(context,"njupt.db",null,3);
}
/**
* 一般来说,创建表的代码放在onCreate()中
*/
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("----------->onCreate");
db.execSQL("CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))"); // 执行SQL语句, 创建表
}
/**
* 修改表的代码放在onUpdate()中...
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("---------->onUpdate");
db.execSQL("ALTER TABLE person ADD balance INTEGER");
}
}
3、PersonDao
package com.example.sqlitetest;
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;
public class PersonDao {
private Context context;
private DBOpenHelper helper;
public PersonDao(Context context) {
this.context = context;
helper = new DBOpenHelper(context);
}
public void insert(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)",
new Object[] { p.getName(), p.getBalance() }); // 执行SQL语句, 插入
db.close();
}
/**
* 某些情况之下程序会接受一个ContentValues,这时候采用这种存储方式比较方便...
* @param p
*/
public void insert1(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
/**
* ContentValues:类似于Map,键放列名,值放要插入的内容...
* 为什么说ContentValues类似于Map,其实看他的成员变量及put方法就知道了 Adds a value to the set.
*
* @param key
* the name of the value to put
* @param value
* the data for the value to put
*
* public void put(String key, String value) {
* mValues.put(key, value); }
*/
ContentValues values = new ContentValues();
values.put("name", p.getName());
values.put("balance", p.getName());
/**
* 第二个参数随便写一个列名,用于处理values值为空的情况..因为列名不能为null
*/
db.insert("person", "name", values);//其实它底层也是采用拼SQL语句的方式的.返回插入的id是多少
db.close();
}
public void delete(int id) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });
db.close();
}
public void delete1(int id){
SQLiteDatabase db = helper.getWritableDatabase();
db.delete("person", "id=?", new String[]{id + ""});
db.close();
}
public void update(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?",
new Object[] { p.getName(), p.getBalance(), p.getId() });
db.close();
}
public void update1(Person p){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", p.getName());
values.put("balance", p.getBalance());
db.update("person", values, "id=?", new String[]{p.getId() + ""});
db.close();
}
public Person query(int id) {
SQLiteDatabase db = helper.getReadableDatabase();// 获取数据库连接,可读的
Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?",
new String[] { id + "" });
Person p = null;
if (c.moveToNext()) {// 判断游标是否包含下一条记录,如果包含,将游标向后移一位
String name = c.getString(0);// 获取0号索引上的数据,转换为String类型
// String name = c.getString(c.getColumnIndex("name"));//这种方法也是极好的
int balance = c.getInt(1);
p = new Person(id, name, balance);
}
c.close();
db.close();
return p;
}
public Person query1(int id){
SQLiteDatabase db = helper.getReadableDatabase();// 获取数据库连接,可读的
// Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?",
// new String[] { id + "" });
/**
* db.query(表名,需要查询的列名,查询条件,查询条件的参数,group by,having,order by);
*/
Cursor c = db.query("person", new String[]{"name","balance"}, "id=?", new String[]{id + ""}, null, null, null);
Person p = null;
if (c.moveToNext()) {// 判断游标是否包含下一条记录,如果包含,将游标向后移一位
String name = c.getString(0);// 获取0号索引上的数据,转换为String类型
// String name = c.getString(c.getColumnIndex("name"));//这种方法也是极好的
int balance = c.getInt(1);
p = new Person(id, name, balance);
}
c.close();
db.close();
return p;
}
public List<Person> queryAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public List<Person> queryAll1(){
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);
Cursor c = db.query("person", null, null, null, null, null, "id DESC");
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public int queryCount() {
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
Cursor c = db.query("person", new String[]{"COUNT(*)"}, null, null, null, null, null);
c.moveToNext();
int count = c.getInt(0);
c.close();
db.close();
return count;
}
public int queryCount1(){
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
c.moveToNext();
int count = c.getInt(0);
c.close();
db.close();
return count;
}
public List<Person> queryPage(int pageNum, int capacity) {
String offset = (pageNum - 1) * capacity + "";
String len = capacity + "";
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery(
"SELECT id, name, balance FROM person LIMIT ?,?", new String[] {
offset, len });
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public List<Person> queryPage1(int pageNum,int capacity){
String offset = (pageNum - 1) * capacity + "";
String len = capacity + "";
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery(
// "SELECT id, name, balance FROM person LIMIT ?,?", new String[] {
// offset, len });
Cursor c = db.query("person",null, null, null, null, null, null,offset + "," + len);
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
/**
* 数据库的事务
*
* 从from这个账户汇款到to这个账户,汇amount这个多钱
*
* @param from
* @param to
* @param amount
*/
public void remit(int from, int to, int amount) {
SQLiteDatabase db = helper.getWritableDatabase();
try {
db.beginTransaction();// 开始事务
db.execSQL("UPDATE person SET balance=balance-? WHERE id=?",
new Object[] { amount, from });
db.execSQL("UPDATE person SET balance=balance+? WHERE id=?",
new Object[] { amount, to });
db.setTransactionSuccessful();// 设置事务成功点...结束事务的时候执行成功点之前的sql语句
} finally {
db.endTransaction();// 结束事务
db.close();
}
}
}
4、Person
package com.example.sqlitetest;
public class Person {
private Integer id;
private String name;
private Integer balance;
public Person() {
super();
}
public Person(Integer id, String name, Integer balance) {
super();
this.id = id;
this.name = name;
this.balance = balance;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", balance=" + balance
+ "]";
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getBalance() {
return balance;
}
public void setBalance(Integer balance) {
this.balance = balance;
}
}
5、DBTest
package com.example.sqlitetest;
import java.util.List;
import android.test.AndroidTestCase;
public class DBTest extends AndroidTestCase {
public void testCreateDB(){
DBOpenHelper helper = new DBOpenHelper(getContext());
helper.getWritableDatabase();
}
/**
* 需要注意的是，在这个例子中,person表中的id是自增的....不收Person中的id这个成员变量影响...
*/
public void testInsert(){
PersonDao dao = new PersonDao(getContext());
for(int i = 1 ; i < 100 ; ++i){
dao.insert(new Person(i,"hjd" + i , 45000+i));
}
// dao.insert(new Person(2, "hjd", 40000));
}
public void testInsert1(){
PersonDao dao = new PersonDao(getContext());
for(int i = 0 ; i < 100 ; ++i){
dao.insert1(new Person(i, "hjd" + i, 30000));
}
// dao.insert1(new Person(3, "dzdp", 10000));
}
public void testDelete(){
PersonDao dao = new PersonDao(getContext());
dao.delete(1);
}
public void testDelete1(){
PersonDao dao = new PersonDao(getContext());
dao.delete1(1);
}
public void testUpdate(){
PersonDao dao = new PersonDao(getContext());
Person p = new Person(2, "zzt", 10000);
dao.update(p);
}
public void testUPdate1(){
PersonDao dao = new PersonDao(getContext());
Person p = new Person(2, "hjd", 40000);
dao.update1(p);
}
public void testQuery(){
PersonDao dao = new PersonDao(getContext());
System.out.println(dao.query(2));
}
public void testQuery1(){
PersonDao dao = new PersonDao(getContext());
System.out.println("------------>" + dao.query1(2));
}
public void testQueryAll(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryAll();
for(Person p : persons){
System.out.println(p);
}
}
public void testQueryAll1(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryAll1();
for(Person p : persons){
System.out.println( "----------->" + p);
}
}
public void testQueryCount(){
PersonDao dao = new PersonDao(getContext());
System.out.println("--------->" + dao.queryCount());
}
public void testQueryCount1(){
PersonDao dao = new PersonDao(getContext());
System.out.println("------->queryCount1:" + dao.queryCount1());
}
public void testQueryPage(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryPage(5, 10);
for(Person p : persons){
System.out.println(p);
}
}
public void testQueryPage1(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryPage1(2, 10);
for(Person p : persons){
System.out.println("--------->" + p);
}
}
}
6、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="match_parent"
android:orientation="horizontal"
android:padding="10dp" >
<TextView
android:id="@+id/idTV"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="1"
android:textSize="20sp" />
<TextView
android:id="@+id/nameTV"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:text="张三"
android:textSize="20sp" />
<TextView
android:id="@+id/balanceTV"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:text="50000"
android:textSize="20sp" />
</LinearLayout>
7、main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp" >
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="id"
android:textSize="20sp" />
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:text="姓名"
android:textSize="20sp" />
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:text="薪金"
android:textSize="20sp" />
</LinearLayout>
<ListView
android:id="@+id/personLV"
android:layout_width="fill_parent"
android:layout_height="fill_parent" />
</LinearLayout>
源码下载：
http://download.csdn.net/detail/caihongshijie6/7624007