Android¿ª·¢Éæ¼°µ½µÄÊý¾Ý¿â²ÉÓõÄÊÇÇáÁ¿¼¶µÄSQLite3£¬¶øÔÚʵ¼Ê¿ª·¢ÖУ¬Ôڴ洢һЩ¼òµ¥µÄÊý¾Ý£¬Ê¹ÓÃSharedPreferences¾Í×ã¹»ÁË£¬Ö»ÓÐÔÚ´æ´¢Êý¾Ý½á¹¹ÉÔ΢¸´ÔÓµÄʱºò£¬²Å»áʹÓÃÊý¾Ý¿âÀ´´æ´¢¡£¶øÊý¾Ý¿â±íµÄÉè¼ÆÍùÍù²»ÊÇÒ»¿ªÊ¼¾Í·Ç³£ÍêÃÀ£¬¿ÉÄÜÔÚÓ¦Óð汾¿ª·¢µü´úÖУ¬±íµÄ½á¹¹Ò²ÐèÒªµ÷Õû£¬Õâʱºò¾ÍÉæ¼°µ½Êý¾Ý¿âÉý¼¶µÄÎÊÌâÁË¡£
Êý¾Ý¿âÉý¼¶£¬Ö÷ÒªÓÐÒÔÏÂÕ⼸ÖÖÇé¿ö£º
Ôö¼Ó±í ɾ³ý±í Ð޸ıíÔö¼Ó±íºÍɾ³ý±íÎÊÌâ²»´ó£¬ÒòΪËüÃǶ¼Ã»ÓÐÉæ¼°µ½Êý¾ÝµÄÇ¨ÒÆÎÊÌ⣬Ôö¼Ó±íÖ»ÊÇÔÚÔÀ´µÄ»ù´¡ÉÏCRTATE TABLE£¬¶øÉ¾³ý±í¾ÍÊǶÔÀúÊ·Êý¾Ý²»ÐèÒªÁË£¬ÄÇÖ»ÒªDROP TABLE¼´¿É¡£ÄÇôÐ޸ıíÄØ£¿
Æäʵ£¬ºÜ¶àʱºò£¬³ÌÐòԱΪÁËͼ¸ö·½±ã£¬×î¼òµ¥×Á¦µÄ·½·¨¾ÍÊÇ£¬½«ÔÀ´µÄ±íɾ³ýÁËÈ»ºóÖØÐ´´½¨ÐÂµÄ±í£¬ÕâÑù¾Í²»Óÿ¼ÂÇÆäËûÒòËØÁË¡£µ«ÕâÑù¶ÔÓÚÓû§À´Ëµ£¬ÌåÑéÊǷdz£²»ºÃµÄ£¬±ÈÈ磺Óû§µ±Ç°ÏÂÔØÁбíÕýÔÚÏÂÔØÎļþ£¬´Ëʱ½øÐиüУ¬¶øÐ°汾Óиö¸üеãÊÇÉý¼¶ÁËÏÂÔØÁбíµÄÊý¾Ý¿â±í£¬ÄÇôÓû§¸üÐÂÍêÖ®ºó·¢ÏÖÏÂÔØÁбí±ä¿ÕÁË£¬ÄÇôÓû§¿´µ½ÐÁÐÁ¿à¿àÏÂÔØµÄ99%Îļþ.aviûÀ´£¬ÄDz»±ÀÀ£ÁË£¬ÕâÖÖÌåÑéÊǷdz£²»ºÃµÄ£¬·Ö·ÖÖÓ¾ÍÐ¶ÔØÄãµÄÓ¦Óá£
ÄÇôÊý¾Ý¿â±íÉý¼¶Ê±£¬Êý¾ÝÇ¨ÒÆ¾ÍÏԵ÷dz£ÖØÒªÁË£¬ÄÇôÈçºÎʵÏÖÄØ£¿
ÏÖÔÚ¿ª·¢£¬ÎªÁËЧÂÊ£¬¶¼»áʹÓõÚÈý·½£¬±¾ÎÄÊý¾Ý¿â·½ÃæÊÇ»ùÓÚORMLiteµÄ£¬ËùÒÔ½ÓÏÂÀ´ÌÖÂ۵ͼÊÇ»ùÓÚ´Ë¡£
1 -> | 2 -> | 3 |
---|---|---|
A | A+ | A |
B | B- | B |
C | C | C+ |
ÉϱíµÄÒâ˼ÊÇ£º°æ±¾Éý¼¶´Ó°æ±¾ºÅ1Éý¼¶µ½2ÔÙÉý¼¶µ½3£¬1->2->3£¬ÆÚ¼ä±íABCµÄ±ä»¯£¬‘+’±íʾ¸Ã±íÔö¼ÓÁË×ֶΣ¬‘-’±íʾ¸Ã±íɾ³ýÁË×ֶΣ¬ÀýÈç1Éý¼¶µ½2£¬±íAÔö¼ÓÁË×ֶΣ¬±íBɾ³ýÁË×ֶΣ¬±íCûÓз¢Éú±ä»¯¡£
Ê×ÏÈ£¬ÎÒÃÇÒªÏÈÀí½âSQLiteOpenHelperÖÐ
/**
* Called when the database is created for the first time. This is where the
* creation of tables and the initial population of the tables should happen.
*
* @param db The database.
*/
public abstract void onCreate(SQLiteDatabase db);
ºÍ
/**
* Called when the database needs to be upgraded. The implementation
* should use this method to drop tables, add tables, or do anything else it
* needs to upgrade to the new schema version.
* @param db The database.
* @param oldVersion The old database version.
* @param newVersion The new database version.
*/
public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
ʲôʱºòµ÷Óá£ÎĵµËµµÃºÜÇå³þÁË£¬onCreate()ÊÇÊý¾Ý¿âµÚÒ»´Î´´½¨µÄʱºòµ÷Ó㬶øonUpgrade()Êǵ±Êý¾Ý¿â°æ±¾Éý¼¶µÄʱºòµ÷Óá£
Ê×ÏÈ£¬Ïȼòµ¥µÄ´´½¨A¡¢B¡¢CÈý¸öÀ࣬²¢Ê¹ÓÃOrmLite×¢½âÀ´´´½¨±í
A.class
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName = tb_a)
public class A {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
}
B.class
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName = tb_b)
public class B {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
@DatabaseField
public String age;
}
C.class
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName = tb_c)
public class C {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
}
´´½¨×Ô¼ºµÄHelperµÄMySqliteHelper.class
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.sql.SQLException;
public class MySqliteHelper extends OrmLiteSqliteOpenHelper{
private final static String DATABASE_NAME=test.db;
private final static int DATABASE_VERSION = 1;
private static MySqliteHelper mInstance;
public MySqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public static MySqliteHelper getInstance(Context context) {
if (mInstance == null) {
mInstance= new MySqliteHelper(context);
}
return mInstance;
}
@Override
public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {
try {
TableUtils.createTableIfNotExists(connectionSource,A.class);
TableUtils.createTableIfNotExists(connectionSource,B.class);
TableUtils.createTableIfNotExists(connectionSource,C.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion) {
}
}
´´½¨Êý¾Ý²Ù×÷µÄDao
import android.content.Context;
import com.j256.ormlite.dao.Dao;
import java.sql.SQLException;
public class ADao {
private Dao dao;
public ADao(Context context){
try {
dao = MySqliteHelper.getInstance(context).getDao(A.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
BDao¡¢CDao£¬Ò²ÀàËÆ¡£
ÔËÐгÌÐò£¬½øÐÐDao²Ù×÷£¬´Ëʱ¾Í´´½¨Êý¾Ý¿âtest.db£¬½ø¶øÖ´ÐÐonCreate()´´½¨±í¡£
import android.app.Application;
import android.test.ApplicationTestCase;
import android.test.suitebuilder.annotation.MediumTest;
import com.helen.andbase.demolist.db.A;
import com.helen.andbase.demolist.db.ADao;
public class ApplicationTest extends ApplicationTestCase {
public ApplicationTest() {
super(Application.class);
}
@MediumTest
public void testDao(){
ADao aDao = new ADao(getContext());
A a = new A();
a.name=a;
aDao.add(a);
BDao bDao = new BDao(getContext());
B b = new B();
b.name=a;
b.age =18;
bDao.add(b);
}
}
½«Æä¿½³öÀ´£¬²é¿´Êý¾Ý¿â¡£ÕâÀïʹÓÃSQLiteExpertPers½øÐв鿴<†·Ÿ"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwPjxpbWcgYWx0PQ=="ÕâÀïдͼƬÃèÊö" src="/uploadfile/Collfiles/20150730/20150730085207122.jpg" title="\" />
ÈçÉÏͼ±íÒÑ´´½¨¡£½Ó×ÅÎÒÃǽøÐÐÊý¾Ý¿âÉý¼¶£¬½«°æ±¾ºÅDATABASE_VERSION±äΪ2£¬±íAÐÂÔö×Ö¶Îage£¬±íBɾ³ý×Ö¶Îage£¬C²»±ä
@DatabaseTable(tableName = tb_a)
public class A {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
@DatabaseField
public String age;
}
@DatabaseTable(tableName = tb_b)
public class B {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
}
@DatabaseTable(tableName = tb_c)
public class C {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
}
¼òµ¥±©Á¦µÄ½â¾ö·½·¨ÊÇ£º
@Override
public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
if(oldVersion < 2){//Ôݲ»ËµÃ÷ΪºÎÒªÕâôÅжÏ
try {
TableUtils.dropTable(connectionSource,A.class,true);
TableUtils.dropTable(connectionSource,B.class,true);
} catch (SQLException e) {
e.printStackTrace();
}
}
onCreate(db,connectionSource);
}
ÏȽ«¾ÉµÄ±íɾ³ýÔÙ´´½¨ÐÂµÄ±í£¬ÕâÊÇ×î¼òµ¥±©Á¦µÄ£¬µ«Ç°ÃæÌá¹ýÕâ²»ÊÇÎÒÃÇÏëÒªµÄ½á¹û¡£
½«´úÂë¸ÄÏ£¬
@Override
public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
if(oldVersion < 2){//Ôݲ»ËµÃ÷ΪºÎÒªÕâôÅжÏ
DatabaseUtil.upgradeTable(db,connectionSource,A.class,DatabaseUtil.OPERATION_TYPE.ADD);
}
onCreate(db,connectionSource);
}
Ö÷ÒªµÄ´úÂë¾ÍÊÇ·â×°µÄDatabaseUtil.classÕâ¸öÀà
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.j256.ormlite.misc.JavaxPersistence;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.DatabaseTable;
import com.j256.ormlite.table.TableUtils;
import java.util.Arrays;
public class DatabaseUtil {
public static final String TAG = DatabaseUtil.java;
/**Êý¾Ý¿â±í²Ù×÷ÀàÐÍ*/
public enum OPERATION_TYPE{
/**±íÐÂÔö×Ö¶Î*/
ADD,
/**±íɾ³ý×Ö¶Î*/
DELETE
}
/**
* Éý¼¶±í£¬Ôö¼Ó×Ö¶Î
* @param db
* @param clazz
*/
public static void upgradeTable(SQLiteDatabase db,ConnectionSource cs,Class clazz,OPERATION_TYPE type){
String tableName = extractTableName(clazz);
db.beginTransaction();
try {
//Rename table
String tempTableName = tableName + _temp;
String sql = ALTER TABLE +tableName+ RENAME TO +tempTableName;
db.execSQL(sql);
//Create table
try {
sql = TableUtils.getCreateTableStatements(cs, clazz).get(0);
db.execSQL(sql);
} catch (Exception e) {
e.printStackTrace();
TableUtils.createTable(cs, clazz);
}
//Load data
String columns;
if(type == OPERATION_TYPE.ADD){
columns = Arrays.toString(getColumnNames(db,tempTableName)).replace([,).replace(],);
}else if(type == OPERATION_TYPE.DELETE){
columns = Arrays.toString(getColumnNames(db,tableName)).replace([,).replace(], );
}else {
throw new IllegalArgumentException(OPERATION_TYPE error);
}
sql = INSERT INTO +tableName +
(+ columns+) +
SELECT + columns+ FROM +tempTableName;
db.execSQL(sql);
//Drop temp table
sql = DROP TABLE IF EXISTS +tempTableName;
db.execSQL(sql);
db.setTransactionSuccessful();
}catch (Exception e){
e.printStackTrace();
}finally {
db.endTransaction();
}
}
/**
* »ñÈ¡±íÃû(ormlite DatabaseTableConfig.java)
* @param clazz
* @param
* @return
*/
private static String extractTableName(Class clazz) {
DatabaseTable databaseTable = clazz.getAnnotation(DatabaseTable.class);
String name ;
if (databaseTable != null && databaseTable.tableName() != null && databaseTable.tableName().length() > 0) {
name = databaseTable.tableName();
} else {
/*
* NOTE: to remove javax.persistence usage, comment the following line out
*/
name = JavaxPersistence.getEntityName(clazz);
if (name == null) {
// if the name isn't specified, it is the class name lowercased
name = clazz.getSimpleName().toLowerCase();
}
}
return name;
}
/**
* »ñÈ¡±íµÄÁÐÃû
* @param db
* @param tableName
* @return
*/
private static String[] getColumnNames(SQLiteDatabase db,String tableName){
String[] columnNames = null;
Cursor cursor = null;
try {
cursor = db.rawQuery(PRAGMA table_info(+tableName+),null);
if(cursor != null){
int columnIndex = cursor.getColumnIndex(name);
if(columnIndex == -1){
return null;
}
int index = 0;
columnNames = new String[cursor.getCount()];
for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
columnNames[index] = cursor.getString(columnIndex);
index++;
}
}
}catch (Exception e){
e.printStackTrace();
}finally {
if(cursor != null) {
cursor.close();
}
}
return columnNames;
}
}
upgradeTable·½·¨Àï²ÉÓõÄÊÇÊý¾Ý¿âÊÂÎñ£¬ÀûÓÃÊÂÎñµÄÔ×ÓÌØÐÔ£¬±£Ö¤ËùÓеÄSQLÄÜÈ«²¿Ö´ÐÐÍê³É¡£Ö÷Ҫ˼·ÊÇ£ºÊ×ÏȽ«ÔÀ´µÄ±í½øÐиÄÃû³Ærename table£¨ÁÙʱ±í£©£¬½Ó×Å´´½¨Ðµıícreate table£¬ÔÙÕß½«¾É±íÄÚµÄÊý¾ÝÇ¨ÒÆµ½Ð±íÄÚ£¬×îºódrop tableɾ³ýÁÙʱ±í¡£
±íµÄÔö¼Ó×ֶκÍɾ³ý×ֶΣ¬ÔÚÇ¨ÒÆÊý¾ÝµÄʱºò£¬Ö÷񻂿±ðÔÚÓÚ×ֶεÄÀ´Ô´²»Í¬¡£±ÈÈ磺A±íÐÂÔöÁËage×ֶΣ¬Õâʱºòcolumns±äÁ¿µÄ»ñÈ¡ÊǸù¾Ý¾É±íÀ´µÄ£¬ÕâÊǹ¹ÔìµÄsqlÓï¾äÊÇ
sql = INSERT INTO tb_a (id,name) SELECT id,name FROM tb_a_temp;
¶øB±íÊÇɾ³ýage×ֶεģ¬columns±äÁ¿µÄ»ñÈ¡ÊǸù¾ÝбíÀ´µÄ£¬Æä¹¹ÔìµÄsqlÓï¾äÊÇ
sql = INSERT INTO tb_b (id) SELECT id FROM tb_b_temp;
ÔÙ´ÎÖ´ÐÐApplicationTest->testDao
@MediumTest
public void testDao(){
ADao aDao = new ADao(getContext());
A a = new A();
a.name=a;
a.age = 20;
aDao.add(a);
BDao bDao = new BDao(getContext());
B b = new B();
b.name=b;
bDao.add(b);
}
Ôٲ鿴ÏÂÊý¾Ý
¿ÉÒÔ¿´µ½±íA¡¢±íBµÄÀúÊ·Êý¾Ý»¹ÊÇ´æÔڵġ£
È»ºóÎÒÃÇÔÙ½«Êý¾Ý¿âÉý¼¶µ½°æ±¾ºÅΪ3£¬ÕâʱºòÒª¿¼Âǵ½Óû§µÄ¶àÖÖÇé¿ö£¬´Ó1->3,´Ó2->3ÕâÁ½ÖÖÇé¿ö£¬µ«²»ÊÇÿ´ÎÉý¼¶¶¼Öظ´Ö®Ç°µÄ²Ù×÷µÄ£¬±ÈÈçÓû§1֮ǰÒѾ´Ó1Éý¼¶µ½2ÁË£¬Õâ´ÎÊÇÒª´Ó2Éý¼¶µ½3£¬¶øÓû§2£¬Ò»Ö±ÓõÄÊÇÀϰ汾1£¬Ëû¾õµÃ£¬àÅ£¬Õâ´ÎÕâ¸ö°æ±¾Éý¼¶µÄÄÚÈݲ»´í£¬¾ö¶¨Éý¼¶ÁË£¬ÄÇôËûÊÇ´Ó1Ö±½ÓÉý¼¶µ½3µÄ£¬ËùÒÔËûÃÇÁ½Õß¾ÀúµÄ°æ±¾²»Ò»Ñù£¬Êý¾Ý¿âÉý¼¶µÄ²ßÂÔÒ²»áÓÐËù²»Óã¬ÄǾÍ񻂿·Ö¿ªÀ´¿¼ÂÇÁË¡£
Õâ´ÎµÄÉý¼¶Êǽ«±íCÌí¼ÓÁËsex×Ö¶Î
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName = tb_c)
public class C {
@DatabaseField(generatedId = true)
public int id;
@DatabaseField
public String name;
@DatabaseField
public String sex;
}
È»ºóÔÚonUpgrade½øÐÐÂß¼ÅжÏ
@Override
public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
if(oldVersion < 2){
DatabaseUtil.upgradeTable(db,connectionSource,A.class,DatabaseUtil.OPERATION_TYPE.ADD);
DatabaseUtil.upgradeTable(db,connectionSource,B.class,DatabaseUtil.OPERATION_TYPE.DELETE);
}
if(oldVersion < 3){
DatabaseUtil.upgradeTable(db,connectionSource,C.class,DatabaseUtil.OPERATION_TYPE.ADD);
}
onCreate(db,connectionSource);
}
ÕâÑù£¬Èç¹ûÄãÊÇ´Ó1Éý¼¶µ½3£¬ÄÇôÁ½¸öifÓï¾ä¶¼»áÖ´ÐУ¬¶øÈç¹ûÊÇ´Ó2Éý¼¶µ½3£¬ÄÇôֻÓÐif(oldVersion < 3)Õâ¸ö·ÖÖ§»áÖ´ÐС£×îºó£¬Èç¹ûÖ»ÊÇÐÂÔöȫеıíD£¬ÄÇôֻҪÔÚonCreateÄÚ¶àд¾äTableUtils.createTableIfNotExists(connectionSource, D.class);¾Í¿ÉÒÔÀ²£¬²»ÒªÍü¼Ç°æ±¾ºÅÒª+1~
±¾ÎÄÌÖÂÛµÄÊý¾ÝÇ¨ÒÆ£¬ÊÇ»ùÓÚоÉÁ½¸ö±íÖ®¼äÂß¼ÐÔ²»Ç¿£¬²»Ç£Éæµ½ÒµÎñÇé¾°µÄÇé¿öÏ¡£±ÈÈ磬±íAÐÂÔöµÄ×Ö¶Îuser_idΪÓû§id£¬Õâ¸ö×Ö¶ÎÊÇÓÃÀ´±ê¼ÇÊý¾ÝÀ´Ô´ÓÚÄĸöÓû§µÄ£¬¼ìË÷µÄʱºò£¬user_idÊÇÓÃÓÚ¼ìË÷Ìõ¼þµÄ£¬ÄÇôÓÉÓÚ¾ÉÊý¾Ý×ªÒÆµ½Ð±íÖÐuser_idĬÈÏÊǿյģ¬Õâʱºò¾ÉÊý¾Ý¿ÉÄÜÏ൱ÓÚ²»Æð×÷ÓÃÁË£¬ËäÈ»¿ÉÒÔͨ¹ýÉèÖÃĬÈÏÖµ£¬µ«ÆäÐèÒª¸ù¾Ý¾ßÌåÒµÎñ³¡¾°½øÐÐÉèÖã¬Òò´Ë¾ÍʧȥÆäÁé»îÐÔÁË¡£