并且只能在表的末尾添加字段，比如,为 Subscription添加两个字段：
1 ALTER TABLE Subscription ADD COLUMN Activation BLOB;
2 ALTER TABLE Subscription ADD COLUMN Key BLOB;
另外，如果遇到复杂的修改操作，比如在修改的同时，需要进行数据的转移，那么可以采取在一个事务中执行如下语句来实现修改表的需求。
1. 将表名改为临时表
ALTER TABLE Subscription RENAME TO __temp__Subscription;
2. 创建新表
CREATE TABLE Subscription (OrderId VARCHAR(32) PRIMARY KEY ,UserName VARCHAR(32) NOT NULL ,ProductId VARCHAR(16) NOT NULL);
3. 导入数据
INSERT INTO Subscription SELECT OrderId, “”, ProductId FROM __temp__Subscription;
或者
INSERT INTO Subscription() SELECT OrderId, “”, ProductId FROM __temp__Subscription;
* 注意 双引号”” 是用来补充原来不存在的数据的
4. 删除临时表
DROP TABLE __temp__Subscription;
通过以上四个步骤，就可以完成旧数据库结构向新数据库结构的迁移，并且其中还可以保证数据不会应为升级而流失。
当然，如果遇到减少字段的情况，也可以通过创建临时表的方式来实现。
/**
*清空表，并自增长
*/
public void clearFeedTable(){
String sql = "DELETE FROM " + FEED_TABLE_NAME +";";
SQLiteDatabase db = dbHelper.getSQLiteDatabase();
db.execSQL(sql);
revertSeq();
dbHelper.free();
}
private void revertSeq() {
String sql = "update sqlite_sequence set seq=0 where name='"+FEED_TABLE_NAME+"'";
SQLiteDatabase db = dbHelper.getSQLiteDatabase();
db.execSQL(sql);
dbHelper.free();
}
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper {
static private DatabaseHelper mDbHelper;
static private SQLiteDatabase mDb;
private static final String DATABASE_NAME = "zhyy.db";
private static final int DATABASE_VERSION = 1;
private final Context mCtx;
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
public DBHelper(Context ctx) {
this.mCtx = ctx;
}
public DBHelper open() throws SQLException {
mDbHelper = new DatabaseHelper(mCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}
public void closeclose() {
mDb.close();
mDbHelper.close();
}
/**
* 插入数据
* 参数：tableName 表名
* initialValues 要插入的列对应值
* */
public long insert(String tableName,ContentValues initialValues) {
return mDb.insert(tableName, null, initialValues);
}
/**
* 删除数据
* 参数：tableName 表名
* deleteCondition 删除的条件
* deleteArgs 如果deleteCondition中有“？”号，将用此数组中的值替换
* */
public boolean delete(String tableName,String deleteCondition,String[] deleteArgs) {
return mDb.delete(tableName, deleteCondition, deleteArgs) > 0;
}
/**
* 更新数据
* 参数：tableName 表名
* initialValues 要更新的列
* selection 更新的条件
* selectArgs 如果selection中有“？”号，将用此数组中的值替换
* */
public boolean update(String tableName,ContentValues initialValues,String selection,String[] selectArgs) {
int returnValue = mDb.update(tableName, initialValues, selection, selectArgs);
return returnValue > 0;
}
/**
* 取得一个列表
* 参数：tableName 表名
* columns 返回的列
* selection 查询条件
* selectArgs 如果selection中有“？”号，将用此数组中的值替换
* */
public Cursor findList(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy) {
return mDb.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy);
}
/**
* 取得单行记录
* 参数：tableName 表名
* columns 返回的列
* selection 查询条件
* selectArgs 如果selection中有“？”号，将用此数组中的值替换
* */
public Cursor findInfo(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit,boolean distinct) throws SQLException {
Cursor mCursor = mDb.query(distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
/**
* 执行sql
* 参数：sql 要执行的sql
* */
public void execSQL(String sql){
mDb.execSQL(sql);
}
/**
* 判断某张表是否存在
* @param tabName 表名
* @return
*/
public boolean isTableExist(String tableName){
boolean result = false;
if(tableName == null){
return false;
}
try {
Cursor cursor = null;
String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' ";
cursor = mDb.rawQuery(sql, null);
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count>0){
result = true;
}
}
cursor.close();
} catch (Exception e) {
// TODO: handle exception
}
return result;
}
/**
* 判断某张表中是否存在某字段(注，该方法无法判断表是否存在，因此应与isTableExist一起使用)
*
* @param tabName 表名
* @return
*/
public boolean isColumnExist(String tableName,String columnName){
boolean result = false;
if(tableName == null){
return false;
}
try {
Cursor cursor = null;
String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' and sql like '%"+ columnName.trim() +"%'" ;
cursor = mDb.rawQuery(sql, null);
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count>0){
result = true;
}
}
cursor.close();
} catch (Exception e) {
// TODO: handle exception
}
return result;
}
}
package com.android.mission.test;
import com.android.mission.util.DBHelper;
import android.content.ContentValues;
import android.database.Cursor;
import android.test.AndroidTestCase;
import android.util.Log;
/**
* 单元测试操作sqlLite的各种sql
*/
public class testSqlLite extends AndroidTestCase{
/**
* 创建表
* @throws Exception
*/
public void createTable() throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
String deleteSql = "drop table if exists user ";
dbHelper.execSQL(deleteSql);
//id是自动增长的主键，username和 password为字段名， text为字段的类型
String sql = "CREATE TABLE user (id integer primary key autoincrement, username text, password text)";
dbHelper.execSQL(sql);
dbHelper.closeclose();
}
/**
* 插入数据
* @throws Exception
*/
public void insert() throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
ContentValues values = new ContentValues(); //相当于map
values.put("username", "test");
values.put("password", "123456");
dbHelper.insert("user", values);
dbHelper.closeclose();
}
/**
* 更新数据
* @throws Exception
*/
public void update() throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
ContentValues initialValues = new ContentValues();
initialValues.put("username", "changename"); //更新的字段和值
dbHelper.update("user", initialValues, "id = '1'", null); //第三个参数为 条件语句
dbHelper.closeclose();
}
/**
* 删除数据
* @throws Exception
*/
public void delete() throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
String testId = "1";
dbHelper.delete("user", "id = '"+ testId +"'", null);
dbHelper.closeclose();
}
/**
* 增加字段
* @throws Exception
*/
public void addColumn() throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
String updateSql = "alter table user add company text";
dbHelper.execSQL(updateSql);
}
/**
* 查询列表
* @throws Exception
*/
public void selectList()throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"}, "username = 'test'", null,null, null, "id desc");
while(returnCursor.moveToNext()){
String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));
String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));
String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));
}
}
/**
* 某条信息
* @throws Exception
*/
public void selectInfo()throws Exception{
DBHelper dbHelper = new DBHelper(this.getContext());
dbHelper.open();
Cursor returnCursor = dbHelper.findList("user",new String[] {"id","username", "password"}, "id = '1'", null,null, null, "id desc");
if (returnCursor.getCount() > 0) {
returnCursor.moveToFirst();
String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));
String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));
String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));
}
}
}
扫一扫
692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
