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  database
		super(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();
		//这里使用的是自带的数据库API
		db.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;
	}
}

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