SQLite 基本使用

//1基类
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class AbstractDB {

   public static final int DatabaseVersion = 1;
   public static final int INTERNAL_VERSION = 2;
   protected Context mContext;
   private  static SQLiteDatabase db;

   public Context getmContext() {
      return mContext;
   }

   protected void getDBInstance() {
      checkDbOpen();
   }

   public AbstractDB(Context context) {
      this.mContext = context;
      getDBInstance();
   }

   public synchronized void execute(String sql) throws SQLException {
      checkDbOpen();
      synchronized (this.getClass()) {
         db.execSQL(sql);
      }
   }

   public synchronized void execute(String sql, Object[] arg) throws SQLException {
      checkDbOpen();
      synchronized (this.getClass()) {
         db.execSQL(sql, arg);
      }
      close();
   }

   public synchronized int delete(String table, String whereClause, String[] whereArgs) {
      checkDbOpen();
      synchronized (this.getClass()) {
         return db.delete(table, whereClause, whereArgs);
      }
   }

   public synchronized long insert(String table, String nullColumnHack, ContentValues values)
         throws SQLException {
      checkDbOpen();
      synchronized (this.getClass()) {
         return db.insertOrThrow(table, nullColumnHack, values);
      }
   }

   public synchronized int update(String table, ContentValues values, String whereClause,
                                   String[] whereArgs) {
      checkDbOpen();
      synchronized (this.getClass()) {
         return db.update(table, values, whereClause, whereArgs);
      }
   }

   public synchronized Cursor query(String sql, String[] selectionArgs) {
      checkDbOpen();
      return db.rawQuery(sql, selectionArgs);
   }

   public synchronized Cursor query(String table, String[] columns, String selection,
                                     String[] selectionArgs) {
      checkDbOpen();
      return db.query(table, columns, selection, selectionArgs, null, null,
            null);
   }

   public int getScalarValue(String sql, String[] selectionArgs) {
      checkDbOpen();
      int ret = 0;
      Cursor c = query(sql, selectionArgs);

      if (c.getCount() > 0) {
         c.moveToFirst();
         ret = c.getInt(0);
      }

      c.close();
      return ret;
   }

   /**
    * This database to determine whether there
    * 
    * @param tableName
    * @return
    */
   public int isExistTable(String tableName) {
      checkDbOpen();
      int count = 0;
      String sql = "Select Count(*) From sqlite_master Where type='table' And name='"
            + tableName + "'";
      count = this.getScalarValue(sql, null);
      return count;
   }

   public void beginTransaction() {
      checkDbOpen();
      db.beginTransaction();
   }

   public void commit() {
      db.setTransactionSuccessful();
      db.endTransaction();
   }

   public void rollback() {
      db.endTransaction();
   }

   public synchronized void close() {
      try {
      
            if (db != null && db.isOpen()) {
            // db.close();
            // db = null;

            }
         
      } catch (Exception e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
   }

   public boolean isOpen() {
      boolean result = false;
      if (db != null) {
         result = db.isOpen();
      }
      return result;
   }
   public static Object lock=new Object();
   protected  void  checkDbOpen() {

         if (db == null) {
            DatabaseHelper dbHelper = new DatabaseHelper(mContext);
            db = dbHelper.getWritableDatabase();
            db.setLockingEnabled(true);
            

         } else if (!db.isOpen()) {    
            DatabaseHelper dbHelper = new DatabaseHelper(mContext);
            db = dbHelper.getWritableDatabase();
            db.setLockingEnabled(true);

         }

   }
}

//2 定义DBHelper

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


public  class DatabaseHelper extends SQLiteOpenHelper {
  
   private static final int DATABASE_VERSION =2;
   private static final String DATABASE_NAME = "videolicai.db";

   public DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      // TODO Auto-generated constructor stub
   }


   @Override
   public void onCreate(SQLiteDatabase db) {
      // TODO Auto-generated method stub
      try {

         db.execSQL(PlayHistoryDao.create_table);
         db.execSQL(OrderRecordDao.CREATE_TABLE);
            System.out.println("malonglong--create");

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


   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // TODO Auto-generated method stub
      try {        
         onCreate(db);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

}

//bean

public class MovieTypeBean {
    String name;//电影名称
    String src;//电影图片
    String url;//电影链接
    String date;//电影日期
    String id;//
    String tag;//电影类型
    String about;//电影简介
    String videoId;//电影id

    boolean needPay;//是否是VIP视频
    int amount;//金额
    int useCount;//浏览次数

    public boolean isNeedPay() {
        return needPay;
    }

    public void setNeedPay(boolean needPay) {
        this.needPay = needPay;
    }

    public int getAmount() {
        return amount;
    }

    public void setAmount(int amount) {
        this.amount = amount;
    }

    public int getUseCount() {
        return useCount;
    }

    public void setUseCount(int useCount) {
        this.useCount = useCount;
    }

    public String getVideoId() {
        return videoId;
    }

    public void setVideoId(String videoId) {
        this.videoId = videoId;
    }

    public String getAbout() {
        return about;
    }

    public void setAbout(String about) {
        this.about = about;
    }

    public String getTag() {
        return tag;
    }

    public void setTag(String tag) {
        this.tag = tag;
    }

    public String getId() {
        return id;
    }

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

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getName() {
        return name;
    }

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

    public String getSrc() {
        return src;
    }

    public void setSrc(String src) {
        this.src = src;
    }
}

//3定义表结构

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;


import com.ichile.licai.bean.MovieTypeBean;

import java.util.ArrayList;

/**
 * Created by RDUSER01 on 2018/3/8.
 */

public class PlayHistoryDao extends AbstractDB {

    private String currentTable;
    public static final String TABLE_NAME = "test";
    public static final String KEY_ID = "_id";
    public static final String KEY_URL= "url";
    public static final String KEY_NAME = "title";
    public static final String KEY_DATE = "date";
    public static final String KEY_TAG = "tag";
    public static final String KEY_DETAIL = "detail";
    public static final String KEY_IMAGE = "image";
    public static final String KEY_VIDEO_ID = "videoid";
    public static final String create_table = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + //
            KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + //
            KEY_URL + " TEXT," + //
            KEY_NAME + " TEXT UNIQUE," + //
            KEY_DATE + " TEXT," + //
            KEY_TAG + " TEXT," + //
            KEY_DETAIL + " TEXT," + //
            KEY_IMAGE + " TEXT," + //
            KEY_VIDEO_ID + " TEXT" + //
            ")";
    public PlayHistoryDao(Context context) {
        super(context);
    }

    public PlayHistoryDao(Context context, String table_name) {
        super(context);
        currentTable = table_name;
    }

    public boolean insertMessage(MovieTypeBean bean) {
        boolean success = false;
        synchronized(lock){
            try {
                String INSERT = "INSERT OR IGNORE INTO "
                        + currentTable
                        + "(url,title,date,tag,detail,image,videoid) values(?,?,?,?,?,?,?);";
                execute(INSERT,
                        new String[] { bean.getUrl(), bean.getName(), bean.getDate(),bean.getTag(),bean.getAbout(),bean.getSrc(),bean.getVideoId()});

                success = true;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close();
            }
        }
        return success;
    }


    public ArrayList<MovieTypeBean> getAll() {
        ArrayList<MovieTypeBean> statureInfos = new ArrayList<MovieTypeBean>();
        synchronized(lock){
            try {
                String QUERY = "SELECT * from " + currentTable;

                Cursor cursor = query(QUERY, null);
                while (cursor.moveToNext()) {
                    statureInfos.add( getInfo(cursor));
                }
                cursor.close();
                close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                close();
            }
        }
        return statureInfos;
    }


    private MovieTypeBean getInfo(Cursor cursor) {
        MovieTypeBean movie = new MovieTypeBean();
        for (int i = 0; i < cursor.getColumnCount(); i++) {

          if(i==1){
//              movie.setId(cursor.getString(i));
              movie.setUrl(cursor.getString(i));
          }
          if(i==2){
              movie.setName(cursor.getString(i));
            }
            if(i==3){
                movie.setDate(cursor.getString(i));
            }
            if(i==4){
                movie.setTag(cursor.getString(i));
            }
            if(i==5){
                movie.setAbout(cursor.getString(i));
            }
            if(i==6){
                movie.setSrc(cursor.getString(i));
            }
            if(i==7){
                movie.setVideoId(cursor.getString(i));
            }
        }
        return movie;
    }


    public boolean deleteAllMsg() {
        boolean success = false;
        synchronized(lock){
            try {

                String DELETE = "DELETE FROM " + currentTable + ";";
                execute(DELETE, new String[]{});
                success = true;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close();
            }
        }
        return success;
    }


}

//4 使用方式

PlayHistoryDao dao = new PlayHistoryDao(ClAppliction.instance(), PlayHistoryDao.TABLE_NAME);
dao.insertMessage(bean);