SQLite数据库

来源：互联网发布：淘宝虚假广告怎么处罚编辑：程序博客网时间：2024/06/07 04:17

SQLite数据库:

StudentDao继承SQLiteOpenHelper类，建库建表

SQLiteDatabase类，操作数据库 db.insert,delete,update,query;db.execSQL(sql);

ContentValues增加时候用

一、SQLiteDatabase类

用于对数据表的增删改查操作。

1、execSQL方法执行指定的sql命令-对数据表增删改操作。

2、rawQuery方法：执行查询操作。返回一个Cursor对象。

3、insert方法，用于标准化实现数据的添加操作。

4、delete方法，实现删除数据的操作。

5、update方法，实现修改数据的操作。

6、query方法，实现查询操作。

二、SQLiteOpenHelper类

数据库管理的工具类，通过继承该类，可以对数据库实现：创建、版本升级操作。

通过以下两个方法获得SQLiteDatabase对象

getWritableDatabase、getReadableDatabase

public class StudentDBHelper extends SQLiteOpenHelper {

private static final String DB_NAME="students.db";

static final String TABLE_NAME="student";

static final String ID="_id";

static final String NAME="name";

static final String SEX="sex";

static final String BIRTHDAY="birthday";

static final String HEIGHT="height";

/**

* 构造器

* @param context

* @param version：数据库版本

public StudentDBHelper(Context context,int version) {

super(context,DB_NAME, null, version);

}

@Override

public void onCreate(SQLiteDatabasedb) {

//创建表

String sql="create table if notexists "+TABLE_NAME+"("

+ID+" integer primary keyautoincrement,"

+NAME+" varchar(50),"

+SEX+" varchar(2),"

+BIRTHDAY+" datetext,"

+HEIGHT+" real)";

db.execSQL(sql);

insertRecords(db);//插入数据

}

//插入数据

private voidinsertRecords(SQLiteDatabase db) {

ContentValues values=new ContentValues();

values.put(NAME, "张飞");

values.put(SEX, "男");

values.put(BIRTHDAY,"1990-5-5");

values.put(HEIGHT, 1.99);

db.insert(TABLE_NAME,null, values);

values=new ContentValues();

values.put(NAME, "田菲");

values.put(SEX, "男");

values.put(BIRTHDAY,"1993-5-5");

values.put(HEIGHT, 1.78);

db.insert(TABLE_NAME,null, values);

}

@Override

public void onUpgrade(SQLiteDatabasedb, int oldVersion,int newVersion) {

//创建表

String sql="create table if notexists "+TABLE_NAME+"("

+ID+" integer primary keyautoincrement,"

+NAME+" varchar(50),"

+SEX+" varchar(2),"

+BIRTHDAY+" datetext,"

+HEIGHT+" real)";

db.execSQL(sql);

insertRecords(db);//插入数据

ContentValues values=new ContentValues();

values.put(NAME, "吗啡");

values.put(SEX, "男");

values.put(BIRTHDAY,"1992-9-5");

values.put(HEIGHT, 1.89);

db.insert(TABLE_NAME,null, values);

}

/**

* 向表中增加一条记录

* @param bean

*@return

public int insertRecored(StudentBeanbean){

ContentValues values=new ContentValues();

values.put(NAME, bean.getName());

values.put(SEX, bean.getSex());

values.put(BIRTHDAY, bean.getBirthday());

values.put(HEIGHT, bean.getHeight());

SQLiteDatabase db = getWritableDatabase();

long count = db.insert(TABLE_NAME,null, values);

return (int) count;

}

/**

* 删除指定id的记录

* @param id

* @return

public int deleteRecord(int id){

SQLiteDatabase db = getWritableDatabase();

int count = db.delete(TABLE_NAME,ID+"=?",new String[]{""+id});

return count;

}

/**

* 修改指定id的记录

* @param id

* @return

public int updateRecord(StudentBeanbean){

ContentValues values=new ContentValues();

values.put(NAME, bean.getName());

values.put(SEX, bean.getSex());

values.put(BIRTHDAY, bean.getBirthday());

values.put(HEIGHT, bean.getHeight());

SQLiteDatabase db = getWritableDatabase();

int count = db.update(TABLE_NAME, values,ID+"=?",new String[]{bean.getId()+""});

return count;

}

//查询所有记录

public ArrayList<StudentBean> queryAll(){

SQLiteDatabase db = getReadableDatabase();

Cursor c = db.query(TABLE_NAME,null, null,null, null,null, null);

ArrayList<StudentBean> students=new ArrayList<StudentBean>();

while(c.moveToNext()){

int id=c.getInt(0);

Stringname=c.getString(c.getColumnIndex(NAME));

Stringsex=c.getString(c.getColumnIndex(SEX));

Stringbirthday=c.getString(c.getColumnIndex(BIRTHDAY));

double height=c.getDouble(c.getColumnIndex(HEIGHT));

StudentBean bean=new StudentBean(id, name, sex, birthday, height);

students.add(bean);

}

return students;

}

//查询指定id的记录

public StudentBean queryRecord(int id){

SQLiteDatabase db = getReadableDatabase();

Cursor c = db.query(TABLE_NAME,null, ID+"=?",new String[]{""+id},null,null,null);

if(c.moveToNext()){

Stringname=c.getString(c.getColumnIndex(NAME));

Stringsex=c.getString(c.getColumnIndex(SEX));

Stringbirthday=c.getString(c.getColumnIndex(BIRTHDAY));

double height=c.getDouble(c.getColumnIndex(HEIGHT));

StudentBean bean=new StudentBean(id, name, sex, birthday, height);

return bean;

}

return null;

}

多表查询：

String[]columns={"kind","textnum","region"};//你要的数据

String 条件字段="NUMWEEK=? andYEAR=?",

String[] selectionArgs={”星期一"，"2013"}；//具体的条件,注意要对应条件字段

Cursor cursor=db.query(表名, columns, 条件字段，selectionArgs,null, null, null, null);

String strSql = "updateappsort set number=number+1 whereactivityname='"+appSortInfo.getActivityName()+"' andpackagename='"+appSortInfo.getPackageName()+"'";

select packagename from appsort where number >0 order by numberdesc limit 0,20

String sql = "select * from appsort where activityname=? andpackagename=?";

Cursor mCursor =db.rawQuery(sql,new String[]{"com.android.calendar.AllInOneActivity","com.google.android.calendar"}); mCursor.moveToNext();

0 0