ORMLite操作數(shù)據(jù)庫(kù)

最近在寫個(gè)簡(jiǎn)單的“我的智能客服”功能?；赟ocket長(zhǎng)鏈接，需要存儲(chǔ)聊天信息。按消息順序插入本地?cái)?shù)據(jù)庫(kù)。ORMLite就兩個(gè)jar放進(jìn)去即可，集成方便，簡(jiǎn)單易用，網(wǎng)上博客也比較多，所以就拿來(lái)集成到項(xiàng)目中。
參考：
https://blog.csdn.net/da_caoyuan/article/details/61414626
https://blog.csdn.net/da_caoyuan/article/details/60876336
https://blog.csdn.net/u013501637/article/details/52388861

一.下載ORMLite的jar包

官網(wǎng)地址：http://ormlite.com/releases/
包括核心包和Android包

ormlite-core-5.1.jar
ormlite-android-5.1.jar

并add as library

二.開始寫代碼

創(chuàng)建實(shí)體類

消息的實(shí)體類：
注意兩點(diǎn)：
1.標(biāo)明要?jiǎng)?chuàng)建的表名：@DatabaseTable(tableName = "ChatMessage")
2.標(biāo)明數(shù)據(jù)庫(kù)中id和字段的名字：@DatabaseField(generatedId = true)，@DatabaseField(columnName = "name")

package com.app.ormlite;

import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

@DatabaseTable(tableName = "ChatMessage")
public class ChatMessage {
    @DatabaseField(generatedId = true)
    private long id;
    @DatabaseField(columnName = "name")
    public String name;
    @DatabaseField(columnName = "msg")
    public String msg;
    @DatabaseField(columnName = "time")
    public long time;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public long getTime() {
        return time;
    }

    public void setTime(long time) {
        this.time = time;
    }

    @Override
    public String toString() {
        return "ChatMessage{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", msg='" + msg + '\'' +
                ", time=" + time +
                '}';
    }
}

創(chuàng)建數(shù)據(jù)庫(kù)的Helper：（和SqlLite真像）

在Helper中可能創(chuàng)建有多張表，想獲取某一張表的Dao時(shí)，直接在getDao(Class clazz)；方法中傳入關(guān)聯(lián)實(shí)體類的類型即可。最后注意釋放資源。

package com.app.ormlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class AppDatabaseHelper extends OrmLiteSqliteOpenHelper {
    public static final String DB_NAME = "App.db";
    public static final int DB_VERSION = 1;

    public AppDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, ChatMessage.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int oldVersion, int newVersion) {
        System.out.println("MyDatabaseHelper.onUpgrade oldVersion=" + oldVersion + "  newVersion=" + newVersion);

        try {

            switch (oldVersion) {
                case 1:
                    //如果現(xiàn)在是第一版本，想在下一版本更新數(shù)據(jù)庫(kù)添加字段
                    getDao(ChatMessage.class).executeRaw("alter table ChatMessage add column xxx varchar(20)");

                case 2:
                    // TableUtils.createTable(connectionSource, MyBean2.class);
                    //在數(shù)據(jù)庫(kù)版本2的下一版本，新增加了一張表
                default:
                    break;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static AppDatabaseHelper instance;

    public static AppDatabaseHelper getHelper(Context context) {
        if (instance == null) {
            synchronized (AppDatabaseHelper.class) {
                if (instance == null)
                    instance = new AppDatabaseHelper(context);
            }
        }
        return instance;
    }

    private Map<String, Dao> daos = new HashMap<String, Dao>();

    public synchronized Dao getDao(Class clazz) throws SQLException {
        Dao dao = null;
        String className = clazz.getSimpleName();
        if (daos.containsKey(className)) {
            dao = daos.get(clazz);
        }
        if (dao == null) {
            dao = super.getDao(clazz);
            daos.put(className, dao);
        }
        return dao;
    }

    /**
     * 釋放資源
     */
    @Override
    public void close() {
        super.close();
        for (String key : daos.keySet()) {
            Dao dao = daos.get(key);
            dao = null;
        }

    }


}

下面輪到Dao層了

業(yè)務(wù)主要涉及到：
1.添加單條消息。
2.批量刪除消息
3.分組查詢
4.查詢所有

定義接口
package com.app.ormlite.dao;

import com.app.ormlite.ChatMessage;

import java.util.Collection;
import java.util.List;

public interface ChatMessageDao {
    /**
     * 添加單條消息
     * @param chatMessage
     * @return
     */
    int insert(ChatMessage chatMessage);

    /**
     * 刪除單條消息
     * @param id
     * @return
     */
    int delete(long id);

    /**
     * 批量刪除指定id的消息
     * @param ids
     * @return
     */
    int delete(Collection<Long> ids);

    /**
     * 刪除所有消息
     * @return
     */
    int deleteAll();

    /**
     * 查詢單條消息
     * @param id
     * @return
     */
    ChatMessage query(long id);

    /**
     * 分組查詢消息
     * @param start
     * @param end
     * @return
     */
    List<ChatMessage> queryLimit(long start, long end);

    /**
     * 查詢所有消息
     * @return
     */
    List<ChatMessage> queryAll();

}
實(shí)現(xiàn)接口方法

在構(gòu)造方法里通過(guò)AppDatabaseHelper獲取關(guān)于ChatMessage的Dao。

package com.app.ormlite.dao;

import android.content.Context;

import com.app.ormlite.AppDatabaseHelper;
import com.app.ormlite.ChatMessage;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.QueryBuilder;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;


public class ChatMessageDaoImpl implements ChatMessageDao {

    private Dao<ChatMessage, Long> dao;

    public ChatMessageDaoImpl(Context context) {
        AppDatabaseHelper helper = AppDatabaseHelper.getHelper(context);
        try {
            dao = helper.getDao(ChatMessage.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int insert(ChatMessage chatMessage) {
        try {
            int i = dao.create(chatMessage);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }


    @Override
    public int delete(long id) {
        try {
            int i = dao.deleteById(id);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public int delete(Collection<Long> ids) {
        try {
            int i = dao.deleteIds(ids);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public int deleteAll() {
        DeleteBuilder<ChatMessage, Long> builder = dao.deleteBuilder();
        try {
            int i = builder.delete();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public ChatMessage query(long id) {
        try {
            ChatMessage chatMessage = dao.queryForId(id);
            return chatMessage;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<ChatMessage> queryLimit(long offset, long limit) {
        QueryBuilder<ChatMessage, Long> builder = dao.queryBuilder();
        try {
            builder.offset(offset);
            builder.limit(limit);
//            builder.where().between("id", offset, limit);
            List<ChatMessage> list = builder.query();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<ChatMessage> queryAll() {
        try {
            List<ChatMessage> list = dao.queryForAll();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

queryLimit(long start, long limit)方法中我剛開始犯了個(gè)錯(cuò)誤：
之前以為批量分組查詢是

builder.where().between("id", offset, limit);

between方法是查詢?cè)趇d處于兩個(gè)值之間的數(shù)據(jù)。比如id在10-19之間，如果id=15的數(shù)據(jù)沒(méi)有了就，就只能查到9條。
應(yīng)該是下面這樣的寫法：

builder.offset(offset);
builder.limit(limit);

offset：從第一條存在的記錄偏移幾條開始查，注意是實(shí)際存在的第一條，而不是從id=1開始偏移幾條。
如：第一條數(shù)據(jù)id=11，想查id在12-15的4條數(shù)據(jù)，
應(yīng)該這樣寫

builder.offset(1);
builder.limit(4);

我之前一直查不到數(shù)據(jù)，就是這點(diǎn)理解錯(cuò)了。

終于到測(cè)試代碼階段了

布局文件

代碼很簡(jiǎn)單，我就不粘貼了


QQ截圖20181219191420.png
下面是MainActivity的方法

public class MainActivity extends AppCompatActivity {
    private static final String TAG = "ORMLiteDemo_";

    private EditText mEtQId;
    private EditText mEtQOffset;
    private EditText mEtQLimit;
    private ChatMessageDaoImpl dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mEtQId = ((EditText) findViewById(R.id.et_query_id));
        mEtQOffset = ((EditText) findViewById(R.id.et_query_offset));
        mEtQLimit = ((EditText) findViewById(R.id.et_query_limit));
        dao = new ChatMessageDaoImpl(this);
    }

    //添加一條記錄，并打印影響的行數(shù)，這里insert應(yīng)該=1
    public void add(View v) {
        ChatMessage chatMessage = new ChatMessage();
        chatMessage.setName("張三");
        chatMessage.setMsg("消息2");
        chatMessage.setTime(new Date().getTime());
        int insert = dao.insert(chatMessage);
        Log.e(TAG, "insert: " + insert);
    }

    //刪除一條記錄，并打印影響的行數(shù)，
    //delete=1說(shuō)明刪除成功，delete=0說(shuō)明沒(méi)有這條記錄
    public void delete(View v) {
        int delete = dao.delete(1);
        Log.e(TAG, "delete: " + delete);
    }

    //刪除指定多條記錄，并打印影響的行數(shù)，
    //第一次打印delete=2，說(shuō)明刪除id為2、3的記錄成功。第二次delete=0，說(shuō)明已經(jīng)刪除了
    public void deletes(View v) {
        ArrayList<Long> ids = new ArrayList<>();
        ids.add(2l);
        ids.add(3l);
        int delete = dao.delete(ids);
        Log.e(TAG, "delete: " + delete);
    }

    //刪除全部記錄，并打印影響的行數(shù)
    public void deleteAll(View v) {
        int i = dao.deleteAll();
        Log.e(TAG, "deleteAll: " + i);
    }

    //查詢指定id的記錄，沒(méi)有則返回null
    public void query(View v) {
        String s = mEtQId.getText().toString().trim();
        int i = Integer.parseInt(s);
        ChatMessage query = dao.query(i);
        if (query != null) {
            Log.e(TAG, query.toString());
        } else {
            Log.e(TAG, "null");
        }
    }

    //分組查詢，分別輸入1和4，打印id從12-15的數(shù)據(jù)
    public void queryLimit(View v) {
        String offsetStr = mEtQOffset.getText().toString().trim();
        String limitStr = mEtQLimit.getText().toString().trim();
        int offset = Integer.parseInt(offsetStr);
        int limit = Integer.parseInt(limitStr);
        List<ChatMessage> query = dao.queryLimit(offset, limit);
        Log.e(TAG, "queryLimit: "+(query == null));
        Log.e(TAG, "queryLimit: "+query.size());
        for (ChatMessage c :
                query) {
            Log.e(TAG, "q_limit: " + c.toString());
        }

    }

    //獲取全部數(shù)據(jù)
    public void getAll(View v) {
        List<ChatMessage> chatMessages = dao.queryAll();
        for (ChatMessage c :
                chatMessages) {
            Log.e("item: ", c.toString());
        }
    }
}

至此，一個(gè)簡(jiǎn)單的demo就完成了。
再次感謝：
https://blog.csdn.net/da_caoyuan
https://blog.csdn.net/u013501637/article/details/52388861

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容