数据库创建和增删改查

使用SQLit数据库存储数据，特点是：除整型主键必须存储64位整数，其余各种类型可任意保存各种数据类型字段，就像整型字段可存放字符串型

1、第一次使用软件，自动创建数据库

2、新建一个类存放在service包里面，让类继承SQLiteOpenHelper这个抽象类

添加一个父类的构造器

public class DBOperator extends SQLiteOpenHelper {
//"tennis.db"数据库名称,第三个产生游标对象，对查询信息随机访问,最后一个是数据库文件版本号，不能为0，null使用默认的游标工厂
public DBOperator(Context context) {//删除不需要传的参数
super(context, "tennis.db", null, 1);

}
@Override
public void onCreate(SQLiteDatabase db) {
//数据库第一次被创建时调用，数据库文件不存在的时候
//SQLiteDatabase db功能生成数据库表,SQLiteDatabase封装了针对数据库的所有操作，增删改查，DB为数据库操作实例
db.execSQL("CREATE TABLE game(id integer primary key,gamename varchar(22),gametime varchar(22),competitornum int(11),gamelocation        varchar(22),timelimit varchar(22),personnum int(11))");
//execSQL执行sql语句，integer primary key主键整型
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 在数据库文件版本号发生变化时被调用，版本变化时进行添加phone,"ALTER TABLE game ADD 改变表，添加一个属性，null允许为空
db.execSQL("ALTER TABLE game ADD phone varchar(12) NULL");
}
}

3、测试数据库是否创建，首先搭建测试环境

在manifest添加

 <uses-library  android:name="android.test.runner"/>//放在appliction里面

<instrumentation 
    android:name="android.test.InstrumentationTestRunner"
    android:targetPackage="com.example.bus"//这个包名称一定和manifest里面的包的名称相同
    android:label="Tests for My APP"//可写可不写
    ></instrumentation>

4、写一个测试类，还要继承SQLiteOpenHelper这个类

private static final String TAG = "GameServiceTest";
public void testCrateDB() throws Exception{
DBOperator db = new DBOperator(getContext());//传入上下文对象
db.getWritableDatabase();
}
public void testSave() throws Exception{
GameService gs = new GameService(this.getContext());
Game game = new Game(3, "2", "23",12, "23", "2", 12);
gs.save(game);
}
public void testDelete() throws Exception{
GameService service = new GameService(this.getContext());
service.delete(2);
}
public void testUpdate() throws Exception{
GameService service = new GameService(this.getContext());
Game game = service.search(3);
game.setGamename("fgf");
service.update(game);
}
public void testSearch() throws Exception{
GameService service = new GameService(this.getContext());
Game game = service.search(3);
Log.i(TAG, game.toString());//最好对game的toString进行复写
}
public void testScrollData() throws Exception{
GameService service = new GameService(this.getContext());
List<Game> gamelist = service.getScroll(1, 1);
for(Game game:gamelist)
{
Log.i(TAG, game.toString());
}
}
public void testCount() throws Exception{
GameService service = new GameService(this.getContext());
long result = service.getCount();
Log.i(TAG, result+" ");
}

5、对应的增删查修的方法,写这些方法之前，为面向对象，首先做的是创建java bin

   

private DBOperator dbOperator;
public GameService(Context context) {
super();
this.dbOperator = new DBOperator(context);
}

public void save(Game game)//添加
{//缓存数据库操作实例
SQLiteDatabase db = dbOperator.getWritableDatabase();
//dbOperator.getWritableDatabase().execSQL(sql);
db.execSQL("insert into game(id,gamename,gametime,competitornum,gamelocation,timelimit,personnum) values(?,?,?,?,?,?,?)",
new Object[]{game.getId(),game.getGamename(),game.getGametime(),game.getCompetitornum(),game.getGamelocation(),game.getTimelimit(),game.getPersonnum()} );
// db.close();//关闭数据库，只有一个地方用到数据库可以不关上
}
public void delete(int id)//删除
{
SQLiteDatabase db = dbOperator.getWritableDatabase();
db.execSQL("delete from game where id = ?",new Object[]{id});
}
public void update(Game game)//更新
{
SQLiteDatabase db = dbOperator.getWritableDatabase();
db.execSQL("update game set gamename=?,gametime=?,competitornum=?,gamelocation=?,timelimit=?,personnum=? where id = ?",
new Object[]{game.getGamename(),game.getGametime(),game.getCompetitornum(),game.getGamelocation(),game.getTimelimit(),game.getPersonnum(),game.getId()} );
}
public Game search(Integer gameid)//查找
{
SQLiteDatabase db = dbOperator.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from game where id = ?",new String[]{gameid.toString()});
if(cursor.moveToFirst())//查询到第一条就知道已经查询到了
{
int id = cursor.getInt(cursor.getColumnIndex("id"));
String gamename = cursor.getString(cursor.getColumnIndex("gamename"));
String gametime = cursor.getString(cursor.getColumnIndex("gametime"));
int competitornum = cursor.getInt(cursor.getColumnIndex("competitornum"));
String gamelocation = cursor.getString(cursor.getColumnIndex("gamelocation"));
String timelimit = cursor.getString(cursor.getColumnIndex("timelimit"));
int personnum = cursor.getInt(cursor.getColumnIndex("personnum"));
return new Game(id,gamename,gametime,competitornum,gamelocation,timelimit,personnum);
}
cursor.close();
return null;
}
public List<Game> getScroll(int offset,int maxResult)
{//分页获取记录offset跳过的记录，maxResult每页多少记录
List<Game> gamelist = new ArrayList<Game>();
SQLiteDatabase db = dbOperator.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from game order by id asc limit ?,?",//没有等于号
new String[]{String.valueOf(offset),String.valueOf(maxResult)});
while(cursor.moveToNext())//到下一个记录
{
int id = cursor.getInt(cursor.getColumnIndex("id"));
String gamename = cursor.getString(cursor.getColumnIndex("gamename"));
String gametime = cursor.getString(cursor.getColumnIndex("gametime"));
int competitornum = cursor.getInt(cursor.getColumnIndex("competitornum"));
String gamelocation = cursor.getString(cursor.getColumnIndex("gamelocation"));
String timelimit = cursor.getString(cursor.getColumnIndex("timelimit"));
int personnum = cursor.getInt(cursor.getColumnIndex("personnum"));
gamelist.add(new Game(id,gamename,gametime,competitornum,gamelocation,timelimit,personnum));
 
}
cursor.close();
return null;

}
public long getCount()
{//得到总数
SQLiteDatabase db = dbOperator.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*)from game ",null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return 0;
}
}

6、模式写0，表示私有操作模式

单引号在sql语句中是特殊字符，如果sql输入语句里面有单引号，要通过？？进行转义，可以进行new一个对象，进行赋值