<span style="font-family: Arial, Helvetica, sans-serif;">package com.sqf.sql.db;</span>
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
* 数据库帮助类，用于创建和管理数据库
* */
public class PersonSQLiteOpenHelper extends SQLiteOpenHelper{
private static final String TAG = "PersonSQLiteOpenHelper";
/**
* 构造方法的四个参数
* 1.上下文
* 2.数据库的名称
* 3.游标工程
* 4.数据库的版本，从1开始
* */
public PersonSQLiteOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, "sql.db", null, 2);
}
public PersonSQLiteOpenHelper(Context context) {
super(context, "sql.db", null, 2);
}
/**
* 数据库第一次创建的时，回调此方法
* 初始化一些表
* */
@Override
public void onCreate(SQLiteDatabase db) {
//操作数据库
String sql = "create table person(_id integer primary key autoincrement,name varchar(20),age integer);";
db.execSQL(sql); //创建person表
}
/**
* 数据库版本号更新时回调此方法
* 更新数据库的内容(删除表，修改表，添加表)
* */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(oldVersion == 1 && newVersion == 2){ //在person表中添加余额列balance
Log.i(TAG, "数据库更新了");
//在person表添加一列
db.execSQL("alter table person add balance integer;");
}
}
}
package com.sqf.sql.dao;
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;
import android.database.sqlite.SQLiteStatement;
import android.text.TextUtils;
import android.util.Log;
import com.sqf.sql.db.PersonSQLiteOpenHelper;
import com.sqf.sql.entities.Person;
/**
* api查询方式
* */
public class PersonDao2 {
private static final String TAG = "PersonDao2";
PersonSQLiteOpenHelper mOpenHelper;//数据库的帮助类
public PersonDao2(Context context){
mOpenHelper = new PersonSQLiteOpenHelper(context);
}
/**
* 添加到person表一条数据
* */
public void insert(Person person){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()){ //如果数据库打开
ContentValues values = new ContentValues();
values.put("name", person.getName());//key作为要存储的列名,value代表对应的值
values.put("age", person.getAge());
long id = db.insert("person", null , values);
Log.i(TAG, id+"");
db.close(); //数据库关闭
}
}
public void delete(int id){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();//获得可写入数据库对象
if(db.isOpen()){
/*
public int delete(String table, String whereClause, String[] whereArgs) {
SQLiteStatement statement = new SQLiteStatement(this, "DELETE FROM " + table +
(!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
*/
/*
delete方法的参数
1.表名
2.删除语句where后面的字句
3.替换的？数组
*/
String whereClasuse = "_id = ?";
String [] whereArgs = {id+""};
int count = db.delete("person", whereClasuse, whereArgs);
//返回值代表删除行数
Log.i(TAG, "删除了:"+count);
db.close();
}
}
public void update(int id,String name){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()){
ContentValues values = new ContentValues();
values.put("name", name);
String whereClause = "_id = ?" ;
int count = db.update("person", values, whereClause, new String[]{id+""});
Log.i(TAG, "修改了:"+count + "行");
db.close();
}
}
public List<Person> queryAll(){
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()){
String[] columns = {"_id","name","age"}; //需要查询的列
String selection = null; // 选择条件，给null查询所有
String[] selectionArgs = null;//选择条件参数,会把选择条件中的？替换成这个数组中的值
String groupBy = null; // 分组语句 group by name 注意些的时候需要要group by 去掉
String having = null; // 过滤语句
String orderBy = null ; //排序
Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
if(cursor != null && cursor.getCount() > 0){
List<Person> personList = new ArrayList<Person>();
while(cursor.moveToNext()){ //向下移动一位，直到最后一位,不可以往下移动了，停止
int id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
personList.add(new Person(id,name,age));
}
db.close();
return personList;
}
db.close();
}
return null;
}
public Person queryItem(int id){
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()){
String[] columns = {"_id","name","age"}; //需要查询的列
String selection = "_id = ?"; // 选择条件，给null查询所有
String[] selectionArgs = {id+""};//选择条件参数,会把选择条件中的？替换成这个数组中的值
String groupBy = null; // 分组语句 group by name 注意些的时候需要要group by 去掉
String having = null; // 过滤语句
String orderBy = null ; //排序
Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
if(cursor != null && cursor.moveToFirst()){ //cursor不位空,可以移动到第一行
int _id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
db.close();
return new Person(_id,name,age);
}
db.close();
}
return null;
}
}
package com.sqf.sql.dao;
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;
import android.database.sqlite.SQLiteStatement;
import android.text.TextUtils;
import android.util.Log;
import com.sqf.sql.db.PersonSQLiteOpenHelper;
import com.sqf.sql.entities.Person;
/**
* api查询方式
* */
public class PersonDao2 {
private static final String TAG = "PersonDao2";
PersonSQLiteOpenHelper mOpenHelper;//数据库的帮助类
public PersonDao2(Context context){
mOpenHelper = new PersonSQLiteOpenHelper(context);
}
/**
* 添加到person表一条数据
* */
public void insert(Person person){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()){ //如果数据库打开
ContentValues values = new ContentValues();
values.put("name", person.getName());//key作为要存储的列名,value代表对应的值
values.put("age", person.getAge());
long id = db.insert("person", null , values);
Log.i(TAG, id+"");
db.close(); //数据库关闭
}
}
public void delete(int id){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();//获得可写入数据库对象
if(db.isOpen()){
/*
public int delete(String table, String whereClause, String[] whereArgs) {
SQLiteStatement statement = new SQLiteStatement(this, "DELETE FROM " + table +
(!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
*/
/*
delete方法的参数
1.表名
2.删除语句where后面的字句
3.替换的？数组
*/
String whereClasuse = "_id = ?";
String [] whereArgs = {id+""};
int count = db.delete("person", whereClasuse, whereArgs);
//返回值代表删除行数
Log.i(TAG, "删除了:"+count);
db.close();
}
}
public void update(int id,String name){
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()){
ContentValues values = new ContentValues();
values.put("name", name);
String whereClause = "_id = ?" ;
int count = db.update("person", values, whereClause, new String[]{id+""});
Log.i(TAG, "修改了:"+count + "行");
db.close();
}
}
public List<Person> queryAll(){
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()){
String[] columns = {"_id","name","age"}; //需要查询的列
String selection = null; // 选择条件，给null查询所有
String[] selectionArgs = null;//选择条件参数,会把选择条件中的？替换成这个数组中的值
String groupBy = null; // 分组语句 group by name 注意些的时候需要要group by 去掉
String having = null; // 过滤语句
String orderBy = null ; //排序
Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
if(cursor != null && cursor.getCount() > 0){
List<Person> personList = new ArrayList<Person>();
while(cursor.moveToNext()){ //向下移动一位，直到最后一位,不可以往下移动了，停止
int id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
personList.add(new Person(id,name,age));
}
db.close();
return personList;
}
db.close();
}
return null;
}
public Person queryItem(int id){
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()){
String[] columns = {"_id","name","age"}; //需要查询的列
String selection = "_id = ?"; // 选择条件，给null查询所有
String[] selectionArgs = {id+""};//选择条件参数,会把选择条件中的？替换成这个数组中的值
String groupBy = null; // 分组语句 group by name 注意些的时候需要要group by 去掉
String having = null; // 过滤语句
String orderBy = null ; //排序
Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
if(cursor != null && cursor.moveToFirst()){ //cursor不位空,可以移动到第一行
int _id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
db.close();
return new Person(_id,name,age);
}
db.close();
}
return null;
}
}
Junit:
package com.sqf.sql.test;
import java.util.List;
import com.sqf.sql.dao.PersonDao;
import com.sqf.sql.dao.PersonDao2;
import com.sqf.sql.db.PersonSQLiteOpenHelper;
import com.sqf.sql.entities.Person;
import android.test.AndroidTestCase;
import android.util.Log;
public class TestCase2 extends AndroidTestCase {
private static final String TAG = "TestCase2";
public void test(){
//数据库什么时候创建
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
// 第一次连接数据库时创建数据库文件，并调用onCreate
openHelper.getReadableDatabase();
}
public void testInsert(){
PersonDao2 dao = new PersonDao2(getContext());
dao.insert(new Person(1,"王五2",20));
}
public void testDelete(){
PersonDao2 dao = new PersonDao2(getContext());
dao.delete(2);
}
public void testUpdate(){
PersonDao2 dao = new PersonDao2(getContext());
dao.update(3, "哈哈");
}
public void testQueryAll(){
PersonDao2 dao = new PersonDao2(getContext());
List<Person> queryAll = dao.queryAll();
for(Person person:queryAll){
Log.i(TAG, person.toString());
}
}
public void testQuery(){
PersonDao2 dao = new PersonDao2(getContext());
Person person = dao.queryItem(3);
Log.i(TAG, person.toString());
}
}
215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
