sqlite模板

DbOpenHelper

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

public class DbOpenHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "QuYi";
    private static DbOpenHelper instance;


    private static final String CLIENT_TABLE_CREATE = "CREATE TABLE " + BookDao.TABLE_NAME
            + " ("
            + BookDao.ID + " INTEGER " + " PRIMARY " + " KEY " + " autoincrement ,"
            + BookDao.COLUMN_NAME_BOOKID + " INTEGER " + "  ,"
            + BookDao.COLUMN_NAME_BOOKNAME + " TEXT, "
            + BookDao.COLUMN_NAME_BOOKIMG + " TEXT, "
            + BookDao.COLUMN_NAME_USERID + " TEXT, "
            + BookDao.COLUMN_NAME_TYPE + " TEXT, "
            + BookDao.COLUMN_NAME_BOOKPATH + " TEXT );";

    public DbOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static DbOpenHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DbOpenHelper(context.getApplicationContext());
        }
        return instance;
    }
    /**
     * 这个方法是在数据库第一次被创建的时候调用的
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //SQLiteDatabase这个类,封装了增删改查操作,也叫做数据库操作实例
        db.execSQL(CLIENT_TABLE_CREATE);
    }
    /**
     * 当数据库的版本号变更的时候被调用
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
    public void closeDB() {
        if (instance != null) {
            try {
                SQLiteDatabase db = instance.getWritableDatabase();
                db.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            instance = null;
        }
    }

}

BookDao

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.bsa.www.bsaAssociatorApp.bean.BookInfo;

import java.util.ArrayList;

public class BookDao {

    public static final String TABLE_NAME = "Bookself";
    public static final String ID = "Id";
    public static final String COLUMN_NAME_BOOKID = "BookId";
    public static final String COLUMN_NAME_BOOKNAME = "BookName";
    public static final String COLUMN_NAME_BOOKIMG = "Bookimg";
    public static final String COLUMN_NAME_BOOKPATH = "BookPath";

    public static final String COLUMN_NAME_USERID = "UserId";
    public static final String COLUMN_NAME_TYPE = "type";
//    public static final String COLUMN_NAME_WEIBO = "WeiBo";
//    public static final String COLUMN_NAME_STATE = "State";
//    public static final String COLUMN_NAME_Bookimg = "Bookimg";

    public DbOpenHelper dbHelper;

    public BookDao(Context context) {
        dbHelper = DbOpenHelper.getInstance(context);
    }

    //修改
    public void updateBook(String BookId, String BookName, String Bookimg, String BookPath) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {

            db.execSQL("update Bookself set BookName=?,Bookimg=?,BookPath=? where BookId=?",
                        new String[] { BookName,Bookimg,BookPath,BookId});
        } else {
            Log.i("tag", "数据库未打开");
        }
        dbHelper.closeDB();
    }

    //添加
    public void addBook(String BookId, String BookName, String Bookimg, String BookPath,String UserId,String type) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("insert into Bookself (BookId,BookName,Bookimg,BookPath,UserId,type) " +
                            "values(?,?,?,?,?,?)",
                    new Object[]{BookId,BookName, Bookimg, BookPath,UserId,type});
        } else {
            Log.i("tag", "数据库未打开");
        }
        dbHelper.closeDB();
    }
    // 查询
    public ArrayList<BookInfo> inquireBook(SQLiteDatabase db, ArrayList<BookInfo> list, String UserId,String type) {
        db = dbHelper.getReadableDatabase();
        if (db.isOpen()) {
            //            String inquireMyLocks = creatInquireMyLocks(BookId);
            //            Cursor c = db.rawQuery(inquireMyLocks,null);
            Cursor c = db.rawQuery("select * from Bookself where UserId = ? and type = ?",new String[] {UserId,type} );
            String Id ;
            String BookId;
            String BookName ;
            String Bookimg;
            String BookPath;

            while (c.moveToNext()) {
                Id=c.getString(c.getColumnIndex("Id"));
                BookId=c.getString(c.getColumnIndex("BookId"));
                BookName = c.getString(c.getColumnIndex("BookName"));
                Bookimg = c.getString(c.getColumnIndex("Bookimg"));
                BookPath = c.getString(c.getColumnIndex("BookPath"));
                BookInfo bean = new BookInfo(Id,BookId,BookName,Bookimg,BookPath);
                list.add(bean);
            }
            if (!c.isClosed()) {
                c.close();
            }
            dbHelper.closeDB();

        } else {
            Log.i("tag", "数据库未打开");
        }
        return list;
    }

}

使用

private ArrayList<BookInfo> bookList = new ArrayList<BookInfo>();
bookList = new BookDao(getActivity()).inquireBook(db, bookList, userId,"1");

adb查看sqlite数据库

adb是android sdk 中自带的一个调试工具，使用这个工具可以直接对连接再电脑上的手机或模拟器进行调试操作。
使用前记得检查是否配好了路径：

配好路径后，在cmd中输入：adb shell 就会进入到控制台入上图；

• cd命令进行到/data/data/包名/databases下 ls查看所有文件
• table.db就是我们创建的，table.db-journal是为了让数据库能支持事务儿产生的临时日志文件，通常情况下这个文件的大小都是0字节。
• sqlite3 table.db：打开数据库，可进行编辑
• .table：查看有哪些表
• .schema：查看他们的建表语句
• .exit/.quit退出数据库的编辑
• 可直接执行sql语句。eg：select * from table