对sqlite数据库进行操作
一.定义自己的类MyDBHelper继承SQLiteOpenHelper类
定义MyDBHelper类,实现onCreate(),onUpgrade()方法,onCreate()在数据库第一次创建时自动调用。可以在该方法中定义要创建表,onUpgrade()方法用于更新数据库版本。
二.主要代码:
1.MyDBHelper类:
public class MyDBHelper extends SQLiteOpenHelper {
//创建数据库类,重写oncreate方法以及更新数据库方法
public MyDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table person(" +
"id_person integer primary key autoincrement," +
"name varchar(10)," +
"phone varchar(11)," +
"money char(10))"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
//创建数据库类,重写oncreate方法以及更新数据库方法
public MyDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table person(" +
"id_person integer primary key autoincrement," +
"name varchar(10)," +
"phone varchar(11)," +
"money char(10))"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2.在MainActivity中对数据库的简单操作示例
public void createDatabase(View v){
//点击按钮创建数据库
MyDBHelper myDBHelper=new MyDBHelper(MainActivity.this,"people.db",null,1);
//打开数据库写入数据
SQLiteDatabase sqLiteDatabase=myDBHelper.getWritableDatabase();
//1.利用API插入数据,修改数据库
sqLiteDatabase.execSQL("insert into person(name,phone,money) values(?,?,?)", new Object[]{"鲁路修","13274512361", 5000});
//2.利用API修改数据,修改数据库
sqLiteDatabase.execSQL("update person set name=? where id_person=?", new Object[]{"鲁路修2", 1});
//3.利用API删除数据,修改数据库
sqLiteDatabase.execSQL("delete from person where id_person=?",new Object[]{1});
//4.利用API查询数据,修改数据库
sqLiteDatabase.execSQL("select * from person where id_person=?", new Object[]{1});
//5.利用API开始事务
/*********************************************
sqLiteDatabase.beginTransaction();
try {
sqLiteDatabase.execSQL("insert into person(name, age) values(?,?)", new Object[]{"林计钦", 4});
sqLiteDatabase.execSQL("update person set name=? where personid=?", new Object[]{"abc", 1});
//调用此方法会在执行到endTransaction()时提交当前事务,如果不调用此方法会回滚事务
sqLiteDatabase.setTransactionSuccessful();
} finally {
sqLiteDatabase.endTransaction();//由事务的标志决定是提交事务,还是回滚事务
}
**********************************************/
//列查询机制,游标Cursor类似与java的ResultSet
Cursor cursor = sqLiteDatabase.rawQuery("select * from person where name like ? ", new String[]{"%鲁路修%"});
while (cursor.moveToNext()) {
int id_person = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
String name = cursor.getString(1);//获取第二列的值
String phone= cursor.getString(2);//获取第三列的值
Toast.makeText(MainActivity.this,"id"+id_person+"名字:"+name+"电话:"+phone,Toast.LENGTH_LONG).show();
}
cursor.close();
sqLiteDatabase.close();
}