Android之SqliteSample

发表于 | 分类于 | 阅读次数：

原文：https://developer.android.com/training/basics/data-storage/databases.html

  • Define a Schema and Contract
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.xiaomolong.sqlitesample;
import android.provider.BaseColumns;
public final class FeedReaderContract {
	public FeedReaderContract() {
	}
	public static abstract class FeedEntry implements BaseColumns {
		public static final String TABLE_NAME = "entry";
		public static final String COLUMN_NAME_ENTRY_ID = "entryid";
		public static final String COLUMN_NAME_TITLE = "title";
		public static final String COLUMN_NAME_SUBTITLE = "subtitle";
	}
}
  • FeedEntryDb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.xiaomolong.sqlitesample;
public class FeedEntryDb {
	public String entryid;
	public String title;
	public String subtitle;
	public String getEntryid() {
		return entryid;
	}
	public void setEntryid(String entryid) {
		this.entryid = entryid;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getSubtitle() {
		return subtitle;
	}
	public void setSubtitle(String subtitle) {
		this.subtitle = subtitle;
	}
}
  • Create a Database Using a SQL Helper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.xiaomolong.sqlitesample;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import com.xiaomolong.sqlitesample.FeedReaderContract.FeedEntry;
public class FeedReaderDbHelper extends SQLiteOpenHelper {
	public static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "FeedReader.db";
	private static final String TEXT_TYPE = " TEXT";
	private static final String COMMA_SEP = ",";
	private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
			+ FeedEntry.TABLE_NAME + " (" + FeedEntry._ID
			+ " INTEGER PRIMARY KEY," + FeedEntry.COLUMN_NAME_ENTRY_ID
			+ TEXT_TYPE + COMMA_SEP + FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE
			+ COMMA_SEP + FeedEntry.COLUMN_NAME_SUBTITLE + TEXT_TYPE +
			// Any other options for the CREATE command
			" )";
	private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
			+ FeedEntry.TABLE_NAME;
	public FeedReaderDbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	public FeedReaderDbHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_ENTRIES);
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(SQL_DELETE_ENTRIES);
		onCreate(db);
	}
}

  • DatabaseManger，包括增删改查

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
     package com.xiaomolong.sqlitesample;
    import java.util.ArrayList;
    import java.util.List;
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import com.xiaomolong.sqlitesample.FeedReaderContract.FeedEntry;
    public class DatabaseManger {
    	private static FeedReaderDbHelper mFeedReaderDbHelper = null;
    	DatabaseManger(Context context) {
    		getInstance(context);
    	}
    	public static void getInstance(Context context) {
    		if (mFeedReaderDbHelper == null) {
    			mFeedReaderDbHelper = new FeedReaderDbHelper(context);
    		}
    	}
    	/**
    	 * 插入
    	 * 
    	 * @return the row ID of the newly inserted row, or -1 if an error occurred
    	 */
    	public long insert(FeedEntryDb feedEntry) {
    		// Gets the data repository in write mode
    		SQLiteDatabase db = mFeedReaderDbHelper.getWritableDatabase();
    		// Create a new map of values, where column names are the keys
    		ContentValues values = new ContentValues();
    		values.put(FeedEntry.COLUMN_NAME_ENTRY_ID, feedEntry.getEntryid());
    		values.put(FeedEntry.COLUMN_NAME_TITLE, feedEntry.getTitle());
    		values.put(FeedEntry.COLUMN_NAME_SUBTITLE, feedEntry.getSubtitle());
    		// Insert the new row, returning the primary key value of the new row
    		long newRowId;
    		newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);
    		db.close();
    		return newRowId;
    	}
    	/**
    	 * 查询或通过条件查询
    	 * 
    	 * @param rowId
    	 * @return
    	 */
    	public List<FeedEntryDb> query(int rowId) {
    		SQLiteDatabase db = mFeedReaderDbHelper.getReadableDatabase();
    		// Define a projection that specifies which columns from the database
    		// you will actually use after this query.
    		String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_ENTRY_ID,
    				FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE };
    		String selection = null;
    		String[] selectionArgs = { String.valueOf(rowId) };
    		if (rowId != -1) {// 通过条件查询
    			selection = FeedEntry._ID + " = ?";
    		} else {
    			selectionArgs = null;
    		}
    		// How you want the results sorted in the resulting Cursor
    		String sortOrder = FeedEntry.COLUMN_NAME_TITLE + " DESC";
    		Cursor cursor = db.query(FeedEntry.TABLE_NAME, // The table to query
    				projection, // The columns to return
    				selection, // The columns for the WHERE clause
    				selectionArgs, // The values for the WHERE clause
    				null, // don't group the rows
    				null, // don't filter by row groups
    				sortOrder // The sort order
    				);
    		List<FeedEntryDb> feedEntryDbList = new ArrayList<FeedEntryDb>();
    		while (cursor.moveToNext()) {
    			FeedEntryDb feedEntryDb = new FeedEntryDb();
    			long itemId = cursor.getLong(cursor
    					.getColumnIndexOrThrow(FeedEntry._ID));
    			feedEntryDb.setEntryid(cursor.getString(cursor
    					.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_ENTRY_ID)));
    			feedEntryDb.setTitle(cursor.getString(cursor
    					.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)));
    			feedEntryDb.setSubtitle(cursor.getString(cursor
    					.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)));
    			feedEntryDbList.add(feedEntryDb);
    		}
    		cursor.close();
    		db.close();
    		return feedEntryDbList;
    	}
    	/**
    	 * 更新
    	 * 
    	 * @param feedEntry
    	 * @param rowId
    	 * @return
    	 */
    	public int update(FeedEntryDb feedEntryDb, int rowId) {
    		SQLiteDatabase db = mFeedReaderDbHelper.getReadableDatabase();
    		// New value for one column
    		ContentValues values = new ContentValues();
    		values.put(FeedEntry.COLUMN_NAME_TITLE, feedEntryDb.getTitle()
    				.toString());
    		// Which row to update, based on the ID
    		String selection = FeedEntry._ID + " = ?";
    		String[] selectionArgs = { String.valueOf(rowId) };
    		int count = db.update(FeedEntry.TABLE_NAME, values, selection,
    				selectionArgs);
    		db.close();
    		return count;
    	}
    	/**
    	 * 删除
    	 * 
    	 * @param rowId
    	 * @return
    	 */
    	public int delete(int rowId) {
    		SQLiteDatabase db = mFeedReaderDbHelper.getReadableDatabase();
    		// Define 'where' part of query.
    		String selection = FeedEntry._ID + " = ?";
    		// Specify arguments in placeholder order.
    		String[] selectionArgs = { String.valueOf(rowId) };
    		// Issue SQL statement.
    		int count = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs);
    		db.close();
    		return count;
    	}
    }

  • 方法调用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
     package com.xiaomolong.sqlitesample;
    import java.util.List;
    import android.app.Activity;
    import android.os.Bundle;
    import android.util.Log;
    import android.view.Menu;
    import android.view.MenuItem;
    import android.view.View;
    import android.view.View.OnClickListener;
    import android.widget.TextView;
    public class MainActivity extends Activity implements OnClickListener {
    	DatabaseManger mDatabaseManger;
    	TextView textView;
    	@Override
    	protected void onCreate(Bundle savedInstanceState) {
    		super.onCreate(savedInstanceState);
    		setContentView(R.layout.activity_main);
    		textView = (TextView) findViewById(R.id.textView);
    		findViewById(R.id.btnAdd).setOnClickListener(this);
    		findViewById(R.id.btnQuery).setOnClickListener(this);
    		findViewById(R.id.btnUpdate).setOnClickListener(this);
    		findViewById(R.id.btnDelete).setOnClickListener(this);
    		mDatabaseManger = new DatabaseManger(this);
    	}
    	@Override
    	public void onClick(View v) {
    		switch (v.getId()) {
    		case R.id.btnAdd:
    			for (int i = 0; i < 5; i++) {
    				FeedEntryDb feedEntryDb = new FeedEntryDb();
    				feedEntryDb.setEntryid(i + "");
    				feedEntryDb.setTitle("title" + i + "");
    				feedEntryDb.setSubtitle("subtitle" + i + "");
    				mDatabaseManger.insert(feedEntryDb);
    			}
    			break;
    		case R.id.btnQuery:
    			List<FeedEntryDb> feedEntryDbList = mDatabaseManger.query(-1);
    			String t = "";
    			for (int i = 0; i < feedEntryDbList.size(); i++) {
    				t = t + feedEntryDbList.get(i).getTitle() + "\n"
    						+ feedEntryDbList.get(i).getSubtitle() + "\n\n";
    			}
    			textView.setText(t);
    			break;
    		case R.id.btnUpdate:
    			FeedEntryDb feedEntryDb = new FeedEntryDb();
    			feedEntryDb.setTitle("修改");
    			Log.d("wxl", "count=" + mDatabaseManger.update(feedEntryDb, 1));
    			break;
    		case R.id.btnDelete:
    			Log.d("wxl", "count=" + mDatabaseManger.delete(1));
    			break;
    		default:
    			break;
    		}
    	}
    	@Override
    	public boolean onCreateOptionsMenu(Menu menu) {
    		// Inflate the menu; this adds items to the action bar if it is present.
    		getMenuInflater().inflate(R.menu.main, menu);
    		return true;
    	}
    	@Override
    	public boolean onOptionsItemSelected(MenuItem item) {
    		// Handle action bar item clicks here. The action bar will
    		// automatically handle clicks on the Home/Up button, so long
    		// as you specify a parent activity in AndroidManifest.xml.
    		int id = item.getItemId();
    		if (id == R.id.action_settings) {
    			return true;
    		}
    		return super.onOptionsItemSelected(item);
    	}
    }


联系作者

我的微信公众号：吴小龙同学，欢迎关注交流，公号回复关键字「1024」有惊喜哦。