Android SQLite 创建多表及多表查询 事务处理
创建3张表:(不用设主外键)
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("同时创建3张数据库表");
db.execSQL("CREATE TABLE "+ "test1" +" (test1_id INTEGER PRIMARY KEY AUTOINCREMENT, test11desc VARCHAR(50), test12desc VARCHAR(50))");
db.execSQL("CREATE TABLE "+ "test2" +" (test2_id INTEGER PRIMARY KEY AUTOINCREMENT, test21desc VARCHAR(50), test22desc VARCHAR(50))");
db.execSQL("CREATE TABLE "+ "test3" +" (test3_id INTEGER PRIMARY KEY AUTOINCREMENT, test31desc VARCHAR(50), test32desc VARCHAR(50))");
}
多表查询:
public void query(){
db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT test1.test11desc , test2.test22desc FROM test3 INNER JOIN test1 on test3.test3_id = test1.test1_id " + "INNER JOIN test2 on test3.test3_id = test2.test2_id " ,new String[]{});
while(cursor.moveToNext()){
String str = cursor.getString(cursor.getColumnIndex("test11desc"));
String str2 = cursor.getString(cursor.getColumnIndex("test22desc"));
System.out.println("查询结果---->>"+str+"---"+str2); }
cursor.close();
db.close();
}
参考文章:http://blog.sina.com.cn/s/blog_4e32d6820100dvmc.html
源码下载地址:http://download.csdn.net/detail/liubin8095/6401491
事务处理:
SQLite数据库也使用了事务的处理方法,SQLiteDatabase类也提供了事务处理的API。
使用beginTransaction()方法开启一个事务,然后执行数据库操作,然后调用setTransactionSuccessful()方法设置事务成功标志。使用endTransaction结束提交事务。
还可以使用inTransaction()方法判断是否处于一个事务中。
public void payment() { SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); //开启事务 db.beginTransaction(); try { //执行数据库操作 db.execSQL("update person set amount=amount-10 where personid=?", new Object[]{1}); db.execSQL("update person set amount=amount+10 where personid=?", new Object[]{2}); //设置事务标志为成功,当结束事务时就会提交事务 db.setTransactionSuccessful(); } catch(Exception e){ throw(e); } finally { //结束事务 db.endTransaction(); } }