学会使用SQLite存储数据和删除数据等基本操作

"1.0" encoding="utf-8"?>
"http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">
    <EditText
        android:id="@+id/bookname"
        android:hint="输入名称"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"

        >
    
    <EditText
        android:id="@+id/author"
        android:hint="输入密码"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        >
    
    <ListView
        android:id="@+id/bookslist"

        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        >
    

  1 package com.example.zhoushasha.sqlitedatabasedemo;
  2 
  3 import android.support.v7.app.AppCompatActivity;
  4 import android.os.Bundle;
  5 import android.app.Activity;
  6 import android.content.Context;
  7 import android.database.Cursor;
  8 import android.os.Bundle;
  9 import android.view.Menu;
 10 import android.view.MenuItem;
 11 import android.view.View;
 12 import android.view.ViewGroup;
 13 import android.widget.AdapterView;
 14 import android.widget.BaseAdapter;
 15 import android.widget.EditText;
 16 import android.widget.ListView;
 17 import android.widget.TextView;
 18 import android.widget.Toast;
 19 public class  MainActivity extends AppCompatActivity implements AdapterView.OnItemClickListener {
 20     private BooksDB mBooksDB;
 21     private Cursor mCursor;
 22     private EditText BookName;
 23     private EditText BookAuthor;
 24     private ListView BooksList;
 25 
 26     private int BOOK_ID = 0;
 27     protected final static int MENU_ADD = Menu.FIRST;
 28     protected final static int MENU_DELETE = Menu.FIRST + 1;
 29     protected final static int MENU_UPDATE = Menu.FIRST + 2;
 30 
 31     public void onCreate(Bundle savedInstanceState) {
 32         super.onCreate(savedInstanceState);
 33         setContentView(R.layout.layout);
 34         setUpViews();
 35     }
 36 
 37     public void setUpViews() {
 38         mBooksDB = new BooksDB(this);
 39         mCursor = mBooksDB.select();
 40 
 41         BookName =(EditText)findViewById(R.id.bookname);
 42         BookAuthor = (EditText) findViewById(R.id.author);
 43         BooksList = (ListView) findViewById(R.id.bookslist);
 44 
 45         BooksList.setAdapter(new BooksListAdapter(this, mCursor));
 46         BooksList.setOnItemClickListener(this);
 47     }
 48 
 49     @Override
 50     public boolean onCreateOptionsMenu(Menu menu) {
 51         super.onCreateOptionsMenu(menu);
 52 
 53         menu.add(Menu.NONE, MENU_ADD, 0, "ADD");
 54         menu.add(Menu.NONE, MENU_DELETE, 0, "DELETE");
 55         menu.add(Menu.NONE, MENU_DELETE, 0, "UPDATE");
 56         return true;
 57     }
 58 
 59     public boolean onOptionsItemSelected(MenuItem item) {
 60         super.onOptionsItemSelected(item);
 61         switch (item.getItemId()) {
 62             case MENU_ADD:
 63                 add();
 64                 break;
 65             case MENU_DELETE:
 66                 delete();
 67                 break;
 68             case MENU_UPDATE:
 69                 update();
 70                 break;
 71         }
 72         return true;
 73     }
 74 
 75     public void add() {
 76         String bookname = BookName.getText().toString();
 77         String author = BookAuthor.getText().toString();
 78 //书名和作者都不能为空，或者退出
 79         if (bookname.equals("") || author.equals("")) {
 80             return;
 81         }
 82         mBooksDB.insert(bookname, author);
 83         mCursor.requery();
 84         BooksList.invalidateViews();
 85         BookName.setText("");
 86         BookAuthor.setText("");
 87         Toast.makeText(this, "Add Successed!", Toast.LENGTH_SHORT).show();
 88     }
 89 
 90     public void delete() {
 91         if (BOOK_ID == 0) {
 92             return;
 93         }
 94         mBooksDB.delete(BOOK_ID);
 95         mCursor.requery();
 96         BooksList.invalidateViews();
 97         BookName.setText("");
 98         BookAuthor.setText("");
 99         Toast.makeText(this, "Delete Successed!", Toast.LENGTH_SHORT).show();
100     }
101 
102     public void update() {
103         String bookname = BookName.getText().toString();
104         String author = BookAuthor.getText().toString();
105 //书名和作者都不能为空，或者退出
106         if (bookname.equals("") || author.equals("")) {
107             return;
108         }
109         mBooksDB.update(BOOK_ID, bookname, author);
110         mCursor.requery();
111         BooksList.invalidateViews();
112         BookName.setText("");
113         BookAuthor.setText("");
114         Toast.makeText(this, "Update Successed!", Toast.LENGTH_SHORT).show();
115     }
116 
117     @Override
118     public void onItemClick(AdapterView parent, View view, int position, long id) {
119 
120         mCursor.moveToPosition(position);
121         BOOK_ID = mCursor.getInt(0);
122         BookName.setText(mCursor.getString(1));
123         BookAuthor.setText(mCursor.getString(2));
124 
125     }
126 
127     public class BooksListAdapter extends BaseAdapter {
128         private Context mContext;
129         private Cursor mCursor;
130 
131         public BooksListAdapter(Context context, Cursor cursor) {
132 
133             mCOntext= context;
134             mCursor = cursor;
135         }
136 
137         @Override
138         public int getCount() {
139             return mCursor.getCount();
140         }
141 
142         @Override
143         public Object getItem(int position) {
144             return null;
145         }
146 
147         @Override
148         public long getItemId(int position) {
149             return 0;
150         }
151 
152         @Override
153         public View getView(int position, View convertView, ViewGroup parent) {
154             TextView mTextView = new TextView(mContext);
155             mCursor.moveToPosition(position);
156             mTextView.setText(mCursor.getString(1) + "___" + mCursor.getString(2));
157             return mTextView;
158         }
159 
160     }
161 }

package com.example.zhoushasha.sqlitedatabasedemo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class BooksDB extends SQLiteOpenHelper {
    private final static String DATABASE_NAME = "BOOKS.db";
    private final static int DATABASE_VERSION = 1;
    private final static String TABLE_NAME = "books_table";
    public final static String BOOK_ID = "book_id";
    public final static String BOOK_NAME = "book_name";
    public final static String BOOK_AUTHOR = "book_author";

    public BooksDB(Context context) {
// TODO Auto-generated constructor stub
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    //创建table
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_NAME + " (" + BOOK_ID
                + " INTEGER primary key autoincrement, " + BOOK_NAME + " text, "+ BOOK_AUTHOR +" text);";
        db.execSQL(sql);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
        db.execSQL(sql);
        onCreate(db);
    }

    public Cursor select() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db
                .query(TABLE_NAME, null, null, null, null, null, null);
        return cursor;
    }
    //增加操作
    public long insert(String bookname,String author)
    {
        SQLiteDatabase db = this.getWritableDatabase();
/* ContentValues */
        ContentValues cv = new ContentValues();
        cv.put(BOOK_NAME, bookname);
        cv.put(BOOK_AUTHOR, author);
        long row = db.insert(TABLE_NAME, null, cv);
        return row;
    }
    //删除操作
    public void delete(int id)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        String where = BOOK_ID + " = ?";
        String[] whereValue ={ Integer.toString(id) };
        db.delete(TABLE_NAME, where, whereValue);
    }
    //修改操作
    public void update(int id, String bookname,String author)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        String where = BOOK_ID + " = ?";
        String[] whereValue = { Integer.toString(id) };

        ContentValues cv = new ContentValues();
        cv.put(BOOK_NAME, bookname);
        cv.put(BOOK_AUTHOR, author);
        db.update(TABLE_NAME, cv, where, whereValue);
    }
}