Android SQL

SQLite:

一、简介：

1. [访问应用内的数据]Android通过 SQLite 数据库引擎来实现结构化数据的存储。在一个数据库应用程序中，任何类都可以通过名字对已经创建的数据库进行访问，但是在应用程序之外就不可以。除非在sd卡中.

2. [小而快]SQLite 数据库是一种用C语言编写的嵌入式数据库，它是一个轻量级的数据库，最初为嵌入式设计的。它是在一些基础简单的语句处理上要比oracle / mysql快很多，而且其对内存的要求很低，在内存中只需要几百KB的存储空间。这是Android中采用 SQLite 数据库的主要原因。

3. SQLite 支持事务处理功能,Transaction

4. SQLite 处理速度比MySQL等著名的开源数据库系统更快;它没有服务器进程。 

5. [跨平台]SQLite 通过文件保存数据库，该文件是跨平台的，可以自由复制。一个文件就是一个数据库,数据库名即文件名。

[API]JDBC会消耗太多系统资源，所以JDBC对于手机并不合适，因此Android提供了新的API来使用 SQLite 数据库。

下面是数据库具体的实现：

第一步：创建SQLiteOpenHelper

package com.example.sqldatabasetextdemo;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.net.Uri;public class MySQLiteOpenHelper extends SQLiteOpenHelper {//数据库的版本private static final int DATABASE_VERSION = 1;//数据库的名称private static final String DATABASE_NAME = "my_update.db";//数据库的表名称【一张数据库可以有多张表】public static final String TABLE_STUDENT = "student";//数据库表的字段public static final String ID = "_id", NAME = "name", AGE = "age",SEX = "sex";public static final Uri STUDENT_URI = Uri.parse("content://"+"com.example.sqldatabasetextdemo/student");private static MySQLiteOpenHelper mSQL;public MySQLiteOpenHelper(Context context, String name,CursorFactory factory, int version) {super(context, name, factory, version);}public static MySQLiteOpenHelper getInstance(Context context){if (mSQL == null) {synchronized (MySQLiteOpenHelper.class) {if (mSQL == null) {mSQL = new MySQLiteOpenHelper(context);}}}return mSQL;}private MySQLiteOpenHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {String strSQL = "CREATE TABLE IF NOT EXISTS " + TABLE_STUDENT + " ("+ ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME+ " VARCHAR(10), " + AGE + " VARCHAR(10), " + SEX+ " VARCHAR(10) " + ")";db.execSQL(strSQL);}//数据库升级回调的方法@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {String strSQL = "DROP TABLE IF EXISTS"+TABLE_STUDENT;if(newVersion > oldVersion){db.execSQL(strSQL);onCreate(db);}}//数据库降低版本回调的方法【注意：这个方法不重写回导致降低版本程序挂掉 super.onDowngrade(db, oldVersion, newVersion)该方法抛出了一个异常】@Overridepublic void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {String strSQL = "DROP TABLE IF EXISTS"+TABLE_STUDENT;if(newVersion > oldVersion){db.execSQL(strSQL);onCreate(db);}}}

上面有几个坑一定要注意这个也是我在业务版本升级遇见的。

第二步：创建ContentProvider：

package com.example.sqldatabasetextdemo;import android.content.ContentProvider;import android.content.ContentValues;import android.content.UriMatcher;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.net.Uri;public class MyProvider extends ContentProvider {private final static String TAG = MyProvider.class.getSimpleName();//授权填写的是包名【androidManifest 也要写这个属性也是包名】private final static String AUTHORITY = "com.example.sqldatabasetextdemo";//匹配Uriprivate static UriMatcher sUriMatcher;private MySQLiteOpenHelper mySqlOpenHelper;private static final int STUDENT = 1;//添加Uri到UriMatcherstatic{sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);sUriMatcher.addURI(AUTHORITY, MySQLiteOpenHelper.TABLE_STUDENT, STUDENT);}@Overridepublic int delete(Uri uri, String whereClause, String[] whereArgs) {int rowNum = 0;SQLiteDatabase db = mySqlOpenHelper.getWritableDatabase();switch (sUriMatcher.match(uri)) {case STUDENT:rowNum = db.delete(MySQLiteOpenHelper.TABLE_STUDENT, whereClause, whereArgs);break;default:break;}return rowNum;}@Overridepublic String getType(Uri uri) {return null;}@Overridepublic Uri insert(Uri uri, ContentValues contentValues) {SQLiteDatabase db = mySqlOpenHelper.getWritableDatabase();switch (sUriMatcher.match(uri)) {case STUDENT:db.insert(MySQLiteOpenHelper.TABLE_STUDENT, null, contentValues);break;default:break;}return null;}@Overridepublic boolean onCreate() {mySqlOpenHelper = MySQLiteOpenHelper.getInstance(getContext());return false;}@Overridepublic Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,String sortOrder) {Cursor cursor = null;SQLiteDatabase db = mySqlOpenHelper.getWritableDatabase();switch (sUriMatcher.match(uri)) {case STUDENT:cursor = db.query(MySQLiteOpenHelper.TABLE_STUDENT, projection, selection, selectionArgs, null, null, sortOrder);break;default:break;}return cursor;}@Overridepublic int update(Uri uri, ContentValues contentValues, String selection, String[] selectionArgs) {int rownum = 0;SQLiteDatabase db= mySqlOpenHelper.getWritableDatabase();switch (sUriMatcher.match(uri)) {case STUDENT:rownum = db.update(MySQLiteOpenHelper.TABLE_STUDENT, contentValues, selection, selectionArgs);break;default:break;}return rownum;}}

此时你基本配置一个AndroidManifest就可以操作数据库了：

 <provider            android:name="com.example.sqldatabasetextdemo.MyProvider"            android:authorities="com.example.sqldatabasetextdemo"            />

数据库创建就是以上三步，很简单吧！

下面贴上我的测试代码：

package com.example.sqldatabasetextdemo;import android.app.Activity;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.os.Bundle;import android.view.Menu;import android.view.View;import android.widget.EditText;import android.widget.TextView;public class MainActivity extends Activity {private final Context mContext = this;private EditText name_editText;private EditText age_editText;private EditText sex_editText;private TextView mTextView;private String name;private String age;private String sex;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        initView();    }    @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;    }        private void initView(){    name_editText = (EditText)findViewById(R.id.name_editText);    age_editText = (EditText)findViewById(R.id.age_editText);    sex_editText = (EditText)findViewById(R.id.sex_editText);    mTextView = (TextView)findViewById(R.id.textView);    }        public void click(View view){    switch (view.getId()) {    //读取EditText中的内容存储于数据库中case R.id.button1:{name = name_editText.getText().toString();age = age_editText.getText().toString();sex = sex_editText.getText().toString();//类似与HashMap 一个储存类ContentValues values = new ContentValues();values.put(MySQLiteOpenHelper.NAME, name);values.put(MySQLiteOpenHelper.AGE, age);values.put(MySQLiteOpenHelper.SEX, sex);//插入数据库mContext.getContentResolver().insert(MySQLiteOpenHelper.STUDENT_URI, values);break;}//查询数据库中的内容case R.id.button2:{Cursor cursor = mContext.getContentResolver().query(MySQLiteOpenHelper.STUDENT_URI, null, null, null, null);if (cursor == null || cursor.getCount() <1) {return;}StringBuilder sb = new StringBuilder();while (cursor.moveToNext() ) {String name = cursor.getString(cursor.getColumnIndex(MySQLiteOpenHelper.NAME));String age = cursor.getString(cursor.getColumnIndex(MySQLiteOpenHelper.AGE));String sex = cursor.getString(cursor.getColumnIndex(MySQLiteOpenHelper.SEX));sb.append("name:"+name+",age:"+age+",sex:"+sex);}closeCursor(cursor);mTextView.setText(sb.toString());break;}//删除操作case R.id.delete_button:{//在项目中如果一个表中字段不是一样格式，建议如以下这么写，因为替换？这个有问题String strWhere = MySQLiteOpenHelper.ID+"= '1'";mContext.getContentResolver().delete(MySQLiteOpenHelper.STUDENT_URI,strWhere, null);break;}//更改操作case R.id.update_button:{ContentValues contextValues = new ContentValues();contextValues.put(MySQLiteOpenHelper.NAME, "王五");contextValues.put(MySQLiteOpenHelper.AGE, "13");contextValues.put(MySQLiteOpenHelper.SEX, "男");//在项目中如果一个表中字段不是一样格式，建议如以下这么写，因为替换？这个有问题String strWhere = MySQLiteOpenHelper.NAME+"= '张三'";mContext.getContentResolver().update(MySQLiteOpenHelper.STUDENT_URI, contextValues, strWhere, null);break;}default:break;}    }        private void closeCursor(Cursor cursor){    if (cursor != null && !cursor.isClosed()) {cursor.close();cursor = null;}    }    }

数据库简单使用上面这些操作应该对你会有帮助，后面我也会更新数据库监听者以及数据事务。希望我的博友一直关注下去，我会和你们一起成长努力！！！