SQLite数据库创建及增删改查

插入语句：insert into person (name,number) values(‘zhangsan’,20)

查询语句：

(1)    select * from person

(2)    select * from person wherename=’zhangsan’

更新语句：update person set number='200' where name='zhangsan'

删除语句：delete from person where name='zhangsan'

 

使用数据库

一共要有四个类

（1）      PersonSQLiteOpenHelper（数据库类）

（2）      PersonDB（操作数据库的类）

（3）      PersonDao（对数据库进行增删改查的操作工具类）

（4）      Person对象类

PersonSQLiteOpenHelper代码：

package com.example.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

	/*
	 * 数据库的构造方法，用来定义数据库的名称，数据库查询的结果集，数据库的版本
	 * */
	public PersonSQLiteOpenHelper(Context context) {
		super(context, "persondb", null, 1);
		// TODO Auto-generated constructor stub
	}

	/*
	 * 数据库第一次被创建调用的方法
	 * */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		//初始化数据库的表结构
		db.execSQL("create table person (id integer primary key autoincrement,name varchar(20),number varchar(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}

 

PersonDB代码：

package com.example.test;

import java.util.List;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

import com.example.db.PersonSQLiteOpenHelper;
import com.example.db.dao.PersonDao;
import com.example.db.dao.domain.Person;

public class TestPersonDB extends AndroidTestCase {

	public void testCreateDB() throws Exception{
		PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
		SQLiteDatabase db = helper.getWritableDatabase();
	}
	
	public void testAdd() throws Exception{
		PersonDao dao = new PersonDao(getContext());
		dao.add("jim", "0181");
		dao.add("peter", "0007");
	}
	public void testfind() throws Exception{
		PersonDao dao = new PersonDao(getContext());
		boolean result = dao.find("jim");
	}
	public void testDel() throws Exception{
		PersonDao dao = new PersonDao(getContext());
		dao.delete("jim");
	}
	public void testUpdate() throws Exception{
		PersonDao dao = new PersonDao(getContext());
		dao.update("jim", "9999");
	}
	public void findAll() throws Exception{
		PersonDao dao = new PersonDao(getContext());
		List<Person> persons = dao.findAll();
		for(Person per:persons){
			System.out.println(per.toString());
		}
	}
}

 

PersonDao代码：

package com.example.db.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.db.PersonSQLiteOpenHelper;
import com.example.db.dao.domain.Person;

public class PersonDao {
	private PersonSQLiteOpenHelper helper;
	//在构造函数中进行初始化
	public PersonDao(Context context) {
		helper = new PersonSQLiteOpenHelper(context);
	}
	
	/*
	 * 添加一条数据到数据库
	 * name，number
	 * 
	 * */
	public void add(String name,String number){
		SQLiteDatabase db = helper.getWritableDatabase();
		
		db.execSQL("insert into person (name,number) values (?,?)",new Object[]{"zhangsan",50});
		db.close();
	}
	//查询记录是否存在
	//返回boolean
	public boolean find(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}
	//更新数据
	public void update(String name,String newnumber){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set number=? where name=?",new Object[]{name,newnumber});
		
		db.close();
	}
	//删除数据
	public void delete(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("delete from person where name=?", new String[]{name});
		db.close();
	}
	//查询所有的数据
	public List<Person> findAll(){
		SQLiteDatabase db = helper.getWritableDatabase();
		List<Person> persons = new ArrayList<Person>();
		
		Cursor cursor = db.rawQuery("select * from person", null);
		//Cursor cursor = db.rawQuery("select id,name,number from 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 per = new Person(id,name,number);
			//把对象添加进集合中
			persons.add(per);
		}
		//关闭数据库的连接
		db.close();
		return persons;
	}
}

 

附：PersonDao代码中的db.execSQL()直接执行SQL语句可能会引起某些不可预知的错误，并且大部分人不熟SQL语句，所以google工程师把增删改查的四个语句封装成了API，供直接使用，说以上面的红色部分的语句可改如下

public long add(String name,String number){
		SQLiteDatabase db = helper.getWritableDatabase();
		
		//db.execSQL("insert into person (name,number) values (?,?)",new Object[]{"zhangsan",50});
		ContentValues values = new ContentValues();
		values.put("name", "zhangsan");
		values.put("number", 50);
		long num = db.insert("person", null, values);
		db.close();
		return num;
	}
	//查询记录是否存在
	//返回boolean
	public boolean find(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
		//Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
		Cursor cursor = db.query("person", null, "nume=?", new String[]{name}, null, null, null);
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}
	//更新数据
	public int update(String name,String newnumber){
		SQLiteDatabase db = helper.getWritableDatabase();
		//db.execSQL("update person set number=? where name=?",new Object[]{name,newnumber});
		ContentValues values = new ContentValues();
		values.put("number", newnumber);
		int number = db.update("person", values, "name=?", new String[]{name});
		db.close();
		return number;
	}
	//删除数据
	public int  delete(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
		//db.execSQL("delete from person where name=?", new String[]{name});
		int number = db.delete("person", "name=?", new String[]{name});
		db.close();
		return number;
	}

Person代码：
package com.example.db.dao.domain;

public class Person {
	private int id;
	private String name;
	private String number;
	public Person(){}
	public Person(int id, String name, String number) {
		super();
		this.id = id;
		this.name = name;
		this.number = number;
	}
	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 String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String toString(){
		return this.id+" "+this.name+" "+this.number;
	}
}