android Sqlite数据库事务处理*.sql 语句集文件

建库主DbHelpers2.java：

/*
 * Copyright 2009 eFANsoftware
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package net.shoxi.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import net.shoxi.www.view.R;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper2 extends SQLiteOpenHelper {
	private static final String VERSION = "1.00";
	private static final int DB_VERSION = (int) (Float.parseFloat(VERSION) * 100);
	private static final String DB_PATH = "medicine.db";

	private static DbHelper2 dbHelper;

	private Context ctx;

	public DbHelper2(Context ctx) {
		
		super(ctx, DB_PATH, null, DB_VERSION);
		this.ctx = ctx;
		
	}

	public static DbHelper2 getInstance(Context ctx) {
		if (dbHelper == null) {
			dbHelper = new DbHelper2(ctx);
		}

		return dbHelper;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			// 开始事务
			db.beginTransaction();

			// create db ZZ
			applySQLs(db, R.raw.db_create);

			// 设置事务成功的标志
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e(this.getClass().getName(), "", e);
			throw new RuntimeException(
					"Database create error! Please contact the support or developer.",
					e);
		} finally {
			// 关闭事务
			db.endTransaction();
		}

	}

	

	/**
	 * 批量执行sql语句
	 * 
	 * @param db
	 * @param sqlResourceId
	 * @throws IOException
	 */
	private void applySQLs(SQLiteDatabase db, int sqlResourceId)
			throws IOException {
		InputStream tmpIS = ctx.getResources().openRawResource(sqlResourceId);
		InputStreamReader tmpReader = new InputStreamReader(tmpIS);
		BufferedReader tmpBuf = new BufferedReader(tmpReader);

		StringBuffer sql = new StringBuffer();
		String tmpStr = null;
		while ((tmpStr = tmpBuf.readLine()) != null) {
			sql.append(tmpStr);
			sql.append('\n');
			if (tmpStr.trim().endsWith(";")) {
				db.execSQL(sql.toString());
				sql = new StringBuffer();
			}
		}

		tmpBuf.close();
		tmpReader.close();
		tmpIS.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			db.beginTransaction();
			applySQLs(db, R.raw.db_clean);
			onCreate(db);
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e(this.getClass().getName(), "", e);
			throw new RuntimeException(
					"Database upgrade error! Please contact the support or developer.",
					e);
		} finally {
			db.endTransaction();
		}
	}
}

 导出的.sql SQL语句集文件

db_create.sql文件：

create table th_cate_x
(
PK_ID integer primary key autoincrement,
VALUE text not null,
_ORDER integer not null
);
create table th_category
(
PK_ID integer primary key autoincrement,
VALUE text not null,
PARENT_ID integer not null
);
create table th_com_users
(
PK_ID integer primary key autoincrement,
USERNAME text not null,
PASSWORD text not null,
EMAIL text not null
);
create table th_content
(
PK_ID integer primary key autoincrement,
VALUE1 text not null,
VALUE2 text not null,
VALUE3 text not null,
VALUE4 text not null,
VALUE5 text not null
);
create table th_user
(
PK_ID integer primary key autoincrement,
USERNAME text not null,
PASSWORD text not null,
REALNAME text not null,
EMAIL text not null,
PARENT_ID integer not null
);

 db_clean.sql 文件:

DROP TABLE IF EXISTS `th_admin_users`;
DROP TABLE IF EXISTS `th_cate_x`;
DROP TABLE IF EXISTS `th_category`;
DROP TABLE IF EXISTS `th_com_users`;
DROP TABLE IF EXISTS `th_content`;
DROP TABLE IF EXISTS `th_user`;