Android之SQLite

SQLite是一个开源的嵌入式关系数据库，它可以减少应用程序管理数据的开销，可移植性好，很容易使用，它本身很小，但却高校而且可靠。

笔者的例子是一个用数据库来保存音乐点击状态的DEMO。

package com.example.database;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class MyHelper extends SQLiteOpenHelper {//数据库名private static String DB_NAME = "isInMusic.db";public MyHelper(Context context) {//create  databasesuper(context, DB_NAME, null,1);//数据库版本号，如果版本号改变，会调用onUpgrade方法}//创建表public void onCreate(SQLiteDatabase db) {String sql ="create table music(_id integer primary key,isIn int(10))";//autoincrement default 0    db.execSQL(sql);}public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {/* * 这里填写版本改变后的语句 * */}}

package com.example.dbmange;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.database.MyHelper;import com.example.databasedemo.MusicList;public class DbManger implements DbMangerImp{private MyHelper helper;private SQLiteDatabase db;public DbManger(Context context){helper = new MyHelper(context);}public void connection(){//连接数据库db = helper.getWritableDatabase();}public void insert(String table, String nullColumnHack, ContentValues values) {connection();//这里使用的是自带的数据库APIdb.insert(table, nullColumnHack, values);/*也可以这样，用自己写的sql * String sql = "insert into music values(0,123)";db.execSQL(sql);*/db.close();}public void delete(String table, String whereClause, String[] whereArgs) {connection();db.delete(table, whereClause, whereArgs);db.close();}public void update(String table, ContentValues values, String whereClause, String[] whereArgs) {connection();db.update(table, values, whereClause, whereArgs);db.close();}public int query(String table, String[] columns, String selection,            String[] selectionArgs, String groupBy, String having,            String orderBy) {connection();//通过游标获取查询到的数据Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);int pic=0;while(cursor.moveToNext()){pic = cursor.getInt(cursor.getColumnIndex("isIn"));}db.close();return pic;}}

package com.example.databasedemo;import java.util.ArrayList;import java.util.List;import com.example.dbmange.DbManger;import android.support.v7.app.ActionBarActivity;import android.support.v7.app.ActionBar;import android.support.v4.app.Fragment;import android.content.ContentResolver;import android.database.Cursor;import android.os.Bundle;import android.view.LayoutInflater;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.ViewGroup;import android.widget.ListView;import android.os.Build;import android.provider.MediaStore;public class MainActivity extends ActionBarActivity {private ListView listView;private List<MusicList> list;private MusicList musicList;private MyAdapterList adapter;private DbManger db;private int pict[] = {R.drawable.icon_favourite_normal, R.drawable.icon_favourite_checked};    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);                listView = (ListView) findViewById(R.id.main_listview_id);                db = new DbManger(this.getApplicationContext());        if(db.query("music", null, "_id=0", null, null, null, null) == 0){        getMusicInfo();        }else{        getMusicInfo1();        }                adapter = new MyAdapterList(list, getApplicationContext());                listView.setAdapter(adapter);            }        public List<MusicList> getMusicInfo(){ContentResolver resolver = this.getContentResolver();Cursor cursor = resolver.query(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,null,null,null,null);list = new ArrayList<MusicList>();while (cursor.moveToNext()) {musicList = new MusicList();musicList.setAuthor(cursor.getString(cursor.getColumnIndex(MediaStore.Audio.Media.ARTIST)));musicList.setMusicName(cursor.getString(cursor.getColumnIndex(MediaStore.Audio.Media.TITLE)));musicList.setPicture(pict[0]);list.add(musicList);System.out.println("------"+musicList.getAuthor()+"---"+musicList.getMusicName());}return list;}public List<MusicList> getMusicInfo1(){ContentResolver resolver = this.getContentResolver();Cursor cursor = resolver.query(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,null,null,null,null);list = new ArrayList<MusicList>();int i = 0;while (cursor.moveToNext()) {musicList = new MusicList();musicList.setAuthor(cursor.getString(cursor.getColumnIndex(MediaStore.Audio.Media.ARTIST)));musicList.setMusicName(cursor.getString(cursor.getColumnIndex(MediaStore.Audio.Media.TITLE)));int picid = db.query("music", null, "_id="+i, null, null, null, null);musicList.setPicture(picid);list.add(musicList);i++;System.out.println("------"+musicList.getAuthor()+"---"+musicList.getMusicName());}return list;}}

package com.example.databasedemo;public class MusicList {private String author;private String musicName;private int musicId;private int picture;public String getAuthor() {return author;}public int getPicture() {return picture;}public void setPicture(int picture) {this.picture = picture;}public void setAuthor(String author) {this.author = author;}public String getMusicName() {return musicName;}public void setMusicName(String musicName) {this.musicName = musicName;}public int getMusicId() {return musicId;}public void setMusicId(int musicId) {this.musicId = musicId;}}

package com.example.databasedemo;import java.util.List;import com.example.dbmange.DbManger;import android.content.ContentValues;import android.content.Context;import android.view.LayoutInflater;import android.view.View;import android.view.View.OnClickListener;import android.view.ViewGroup;import android.widget.Adapter;import android.widget.BaseAdapter;import android.widget.ImageView;import android.widget.TextView;public class MyAdapterList extends BaseAdapter {private List<MusicList> list;private Context context;private DbManger manger;private ContentValues cv;private ViewHolder holder;public MyAdapterList(List<MusicList> list, Context context){this.list = list;this.context = context;manger = new DbManger(context);}public int getCount() {return list.size();}public Object getItem(int position) {return list.get(position);}public long getItemId(int position) {return position;}public View getView(final int position, View convertView, ViewGroup parent) {if(convertView == null){holder = new ViewHolder();convertView = LayoutInflater.from(context).inflate(R.layout.adapterview, null);holder.imageView = (ImageView) convertView.findViewById(R.id.adapter_imageview_id);holder.authorTextView = (TextView) convertView.findViewById(R.id.adapter_author_id);holder.musicNameTextView = (TextView) convertView.findViewById(R.id.adapter_musicname_id);convertView.setTag(holder);}else{holder = (ViewHolder) convertView.getTag();}cv = new ContentValues();cv.put("_id", position);cv.put("isIn", list.get(position).getPicture());//if(manger.query("music", null, "_id="+position, null, null, null, null)==0){//manger.insert("music", null, cv);//}manger.insert("music", null, cv);holder.imageView.setImageResource(list.get(position).getPicture());holder.authorTextView.setText(list.get(position).getAuthor());holder.musicNameTextView.setText(list.get(position).getMusicName());holder.imageView.setOnClickListener(new OnClickListener() {public void onClick(View v) {int id = manger.query("music", null, "_id="+position, null, null, null, null);if(id == R.drawable.icon_favourite_normal){list.get(position).setPicture(R.drawable.icon_favourite_checked);cv = new ContentValues();cv.put("isIn", R.drawable.icon_favourite_checked);holder.imageView.setImageResource(list.get(position).getPicture());manger.update("music", cv, null, null);}else{list.get(position).setPicture(R.drawable.icon_favourite_normal);cv = new ContentValues();cv.put("isIn", R.drawable.icon_favourite_normal);holder.imageView.setImageResource(list.get(position).getPicture());manger.update("music", cv, null, null);}}});notifyDataSetChanged();return convertView;}class ViewHolder{private ImageView imageView;private TextView authorTextView, musicNameTextView;}}

虽然逻辑有点捉急，但是可以用，代码下载：点击下载