Android第十四天 SQLite数据库

SQLite数据库的创建步骤：

1.创建一个数据库打开帮助类：

public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "student_info.db";
private final static int VERSION = 1;


public DataBaseOpenHelper(Context context) {
super(context, DATABASE_NAME, null, VERSION);
}


@Override
/**
* 在第一次创建数据库的时候，会调用这个方法，如果不是第一次调用，则不会调用这个方法
*/
public void onCreate(SQLiteDatabase db) {
Log.i("DataBaseOpenHelper", "------onCreate----->>");
/*执行建表语句，在数据库中建立一个students表*/
String sql = "create table if not exists students(_id integer primary key autoincrement,name varchar(64),age varchar(64))";
db.execSQL(sql);
}

@Override
/**
*当不是第一次调用DataBaseOpenHelper来创建数据库的时候，而且newVersion>oldVersion,则会调用这个方法
*/
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(newVersion>oldVersion){
String sql="alter table students add sex varchar(8)";
db.execSQL(sql);
}
}

}

2.创建一个数据库管理类：

public class SqlitManager {
private DataBaseOpenHelper helper=null;

public SqlitManager(Context context) {
//1. 创建数据库
helper =new DataBaseOpenHelper(context);

//2. 必须要调用getWritableDatabase()或者getReadableDatabase()才会创建数据库
helper.getReadableDatabase();
}

public boolean onUpgrade(int oldVersion,int newVersion){
SQLiteDatabase database=null;
try {
database =helper.getWritableDatabase();
if(newVersion>oldVersion){
helper.onUpgrade(database, oldVersion, newVersion);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally{
if(database!=null){
database.close();
}
}
}
}

数据库查询第一种方法：

public class SqlitManager {
private DataBaseOpenHelper helper=null;

public SqlitManager(Context context) {
//1. 创建数据库
helper =new DataBaseOpenHelper(context);

//2. 必须要调用getWritableDatabase()或者getReadableDatabase()才会创建数据库
helper.getReadableDatabase();
}

public boolean onUpgrade(int oldVersion,int newVersion){
SQLiteDatabase database=null;
try {
database =helper.getWritableDatabase();
if(newVersion>oldVersion){
helper.onUpgrade(database, oldVersion, newVersion);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally{
if(database!=null){
database.close();
}
}
}

/**
*添加数据到数据库 ,包括了增加数据，删除数据，修改数据
*/
public boolean exectData(String sql,Object[] bindArgs){
SQLiteDatabase database=null;
try {
database =helper.getWritableDatabase();
database.execSQL(sql, bindArgs);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}

/**
*查询数据 
*/
public Map<String, String> selectInfoOfDatabase(String sql,String[] selectionArgs){
SQLiteDatabase database=null;
Map<String, String> map =new HashMap<String, String>();

try {
database =helper.getWritableDatabase();
Cursor cursor =database.rawQuery(sql, selectionArgs);
//获取具体的列数
int colums =cursor.getColumnCount();
while(cursor.moveToNext()){
for (int i = 0; i < colums; i++) {
map.put(cursor.getColumnName(i), cursor.getString(i));
}

}
//关闭Cursor
cursor.close();
return map;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally{
if(database!=null){
database.close();
}
}
}

/**
*查询所有的数据 
*/
public List<Map<String, String>> selectAllInfoOfDatabase(String sql,String[] selectionArgs){
SQLiteDatabase database=null;
List<Map<String, String>> list =new ArrayList<Map<String,String>>();
try {
database =helper.getWritableDatabase();
Cursor cursor =database.rawQuery(sql, selectionArgs);
//获取具体的列数
int colums =cursor.getColumnCount();
while(cursor.moveToNext()){
Map<String, String> map =new HashMap<String, String>();
for (int i = 0; i < colums; i++) {
map.put(cursor.getColumnName(i), cursor.getString(i));
}
list.add(map);
}
//关闭Cursor
cursor.close();
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally{
if(database!=null){
database.close();
}
}
}
}

数据库查询的第二种操作：

/**
* 利用数据库中的insert方法来实现添加数据
*/
public boolean insertInfoToDatabase(String table, ContentValues values) {
SQLiteDatabase database = null;
try {
database = helper.getWritableDatabase();


/**
* values不能为空
* ContentValues用来存储对应的键与值，用法与map一样
*/
long id = database.insert(table, null, values);
return (id > 0) ? true : false;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (database != null) {
database.close();
}
}
return false;
}


/**
* 利用数据库中的delete方法来实现删除
* whereClause删除条件的占位符
* whereArgs：删除的条件
*/
public boolean deleteInfoOfDatabase(String table, String whereClause,
String[] whereArgs) {
SQLiteDatabase database = null;
try {
database = helper.getWritableDatabase();
int number = database.delete(table, whereClause, whereArgs);
return (number > 0) ? true : false;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (database != null) {
database.close();
}
}
return false;
}


/**
* 利用数据库中的update方法实现修改数据
* ContentValues要修改的列的键值对
* whereClause：修改的添加占位符
* whereArgs：修改的条件
*/
public boolean updateInfoOfDatabase(String table, ContentValues values,
String whereClause, String[] whereArgs) {
SQLiteDatabase database = null;

try {
database = helper.getWritableDatabase();
int number = database.update(table, values, whereClause, whereArgs);
return (number > 0) ? true : false;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (database != null) {
database.close();
}
}
return false;
}


/**
* 利用数据库的query方法实现数据的查询
*/
public Map<String, String> queryInfoOfDatabase(String table,
String[] columns, String selection, String[] selectionArgs) {
SQLiteDatabase database = null;
Map<String, String> map = new HashMap<String, String>();
try {
database = helper.getWritableDatabase();
Cursor cursor = database.query(table, columns, selection, selectionArgs, null,
null, null, null);
// 获取具体的列数
int colums = cursor.getColumnCount();
while (cursor.moveToNext()) {
for (int i = 0; i < colums; i++) {
map.put(cursor.getColumnName(i), cursor.getString(i));
}
}
// 关闭Cursor
cursor.close();
return map;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}