Android 中sqlite数据库的增删改查

Android 中sqlite数据库的增删改查

1. 创建sqlite的帮助类，继承SQLiteOpenHelper类。
2. 重写onCreate方法和onUpgrade方法
3. 重写父类的构造方法
   Dbhelp如下：

package net.hnjdzy.tinyaccount.db;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
 * 数据库帮助类
 * @author Administrator
 *
 */
public class DatabaseHelper extends SQLiteOpenHelper {
	private final static String DATABASE_NAME = "account.db"; //数据库名
	private final static int DATABASE_VERSION = 1; //版本
	//建数据库库
	public DatabaseHelper(Context ctx){
		super(ctx,DATABASE_NAME,null,DATABASE_VERSION);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 建表

		//建立收入类型
		String sql = "create table AccountIncomeType (id integer primary key autoincrement,category text)";
		db.execSQL(sql);
		
		//收入流水表
		sql = "create table AccountIncome(id integer primary key autoincrement,"+
				"category text,money float,date text,remark text)";
		db.execSQL(sql);
		

		//建立支出类型
		sql = "create table AccountOutlayType (id integer primary key autoincrement,category text)";
		db.execSQL(sql);
		
		//支出流水表
		sql = "create table AccountOutlay(id integer primary key autoincrement,"+
				"category text,money float,date text,remark text)";
		db.execSQL(sql);		
		
		//初始化数据
		//收入类型
		sql = "insert into AccountIncomeType(category) values('工资')";
		db.execSQL(sql);
		sql = "insert into AccountIncomeType(category) values('奖金')";
		db.execSQL(sql);
		sql = "insert into AccountIncomeType(category) values('津贴')";
		db.execSQL(sql);
		
		//支出类型
		sql = "insert into AccountOutlayType(category) values('交通费')";
		db.execSQL(sql);
		sql = "insert into AccountOutlayType(category) values('餐饮费')";
		db.execSQL(sql);
		sql = "insert into AccountOutlayType(category) values('资料费')";
		db.execSQL(sql);	
		
		//创建收入的样例数据
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
		String date = sdf.format(new Date());
		String sql0 = "insert into accountIncome(category,money,remark,date) values('%s',%d,'%s','%s')";		
		sql = String.format(sql0, "工资",1000,"",date);
		db.execSQL(sql);
		
		sql = String.format(sql0, "奖金",500,"",date);
		db.execSQL(sql);
		
		sql = String.format(sql0, "津贴",800,"",date);
		db.execSQL(sql);
		
		//支出的样例数据
		sql0 = "insert into accountOutlay(category,money,remark,date) values('%s',%d,'%s','%s')";	
		sql = String.format(sql0, "交通费",100,"",date);
		db.execSQL(sql);
		
		sql = String.format(sql0, "餐饮费",200,"",date);
		db.execSQL(sql);
		
		sql = String.format(sql0, "资料费",300,"",date);
		db.execSQL(sql);
	}

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

	}

}

建表：
create table AccountOutlayType (id integer primary key autoincrement,category text)
Primary key 设置主键，autoincrement 自动增长

增删改查

1． 声明数明数据库操作对象SQLiteDatabase的变量。添加，删，改数据用db2，查询用db1

private SQLiteDatabase db1 =this.dbHelper.getReadableDatabase();//读
	private SQLiteDatabase db2  = this.dbHelper.getWritableDatabase();//写

2. 添加数据

public boolean addIncomeType(String name){
		ContentValues values = new ContentValues();
		values.put("category", name);//category添加的字段名，name添加的数据
		long result = db2.insert("accountIncomeType", null, values);// accountIncomeType表名 
		return result>0;
	}

4. 删除数据

	public boolean deleteIncomeType(String name){
		//表名，条件，条件值
		//db2.delete(, whereClause, whereArgs)
long result = db2.delete("accountIncomeType", "category=?",new String[]{String.valueOf( name)});//返回受影响的行数
		return result>0;
	}

5. 修改数据

db2.update(table, values, whereClause, whereArgs);

table：表名
value：要被修改的新值
whereclause：sql语句的where的条件部分未知部分可用？号
wherearge：字符串数组表示未知的？号部分
列：SQL语句：update news set context = ? where title = “头部”

	ContentValues values = new ContentValues();
	values.put("context", context);
db2.update(“news”, values, “title = ?”,new String[]{“头部”} );

6. 查询数据

public List<String> queryIncomeType(){
	String sql = "select category from accountIncomeType";//sql查询语句
	Cursor c = db1.rawQuery(sql, null);//查询方法，返回浮标	
	List<String> result = new ArrayList<String>();
	while(c.moveToNext()){
		result.add(c.getString(0));
	}
	c.close();
	return result;		
}