Android之SQLite

古城微笑少年丶 2022-08-27 10:57 221阅读 0赞

> 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;
>     	}
>     }
> 
>   
> 虽然逻辑有点捉急，但是可以用，代码下载： [点击下载][Link 1]  
>

[Link 1]: http://download.csdn.net/detail/super_mimi/7429085