01.Android数据库笔记

本人愚钝，想写一个大学课程表，之前在数据库方面各种不懂，近来再仔细观摩例程，竟然有些小小收获，特以学习笔记形式记录，方便日后复习，兼作备份代码之用，程序不合理之处敬请拍砖，


DbHelper 继承 SQLiteOpenHelper
注意：
1.SQLiteDatabase.query的用法之一
public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
当只查询部分行，通过 String[] columns 参数可选择
亦可通过selection，selectionArgs联合SQlite语句筛选
selection中占位符？要与selectionArgs中一一对应；
2.要掌握数据库，还需深入学习SQLite；

3.数据库中键值对插入行，修改行；

package com.example.helper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {
	private static final String table="course";
	private static final String DbName= "Mycourse";

	public DbHelper(Context context) {
		super(context,DbName, null, 1);
		// TODO Auto-generated constructor stub
		
	}
	
	public Cursor select(int weekday)
	{
		SQLiteDatabase db=this.getReadableDatabase();
		int value[]={1,2,3,4,5,6,7,8};
		String tochar[] = {"1","2","3","4","5","6","7","8"};
		//for (int i=0;i<8;i++) value[i]+=weekday*8;
		//for (int i=0;i<8;i++) tochar[i]=Integer.toString(value[i]);
		//String[] wherevalue={tochar[0],tochar[1],tochar[2],tochar[3],tochar[4],tochar[5],tochar[6],tochar[7]};
		String[] wherevalue={"12","0"};
		Cursor cursor=db.query("course", null,"_id<? and _id>?",wherevalue, null, null,null);
		return cursor;

	}
	
	public long insert(CourseData data)
	{
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues cv=new ContentValues();
		cv.put("name",data.getName());
		cv.put("time",data.getTime());
		long rowid=db.insert(table, null, cv);
		return rowid;
		
	}
	
	public void delete(CourseData data,int id)
	{
		SQLiteDatabase db=this.getReadableDatabase();
		String where="_id=?";
		String[] wherevalue={Integer.toString(id)};
		db.delete(table, where, wherevalue);
		
	}
	
	public void upgrade(CourseData data,int id)
	{
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues cv=new ContentValues();
		String where="_id=?";
		String[] wherevalue={Integer.toString(id)};
		cv.put("name",data.getName());
		cv.put("time",data.getTime());
		db.update(table,cv, where, wherevalue);
		
	}
	
	public void initial()
	{
		SQLiteDatabase db=this.getReadableDatabase();
		CourseData emptydata=new CourseData();
		for(int i=0;i<10;i++) 
			{
		     emptydata.setName(String.valueOf(i));
		     emptydata.setTime(i);
		     insert(emptydata);
			}
	}
	
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("create table course(_id integer primary key autoincrement,name varchar[20],time integer);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		
	}

}

 
        Activity类：package com.example.sqlite;

    注意：

    1.ListView 通过invalidateViews重画

    2.抛出异常

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteCursor;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

import com.example.helper.CourseData;
import com.example.helper.DbHelper;

public class MainActivity extends Activity {
	private static final String TAG = "MainActivity";
	private Button bn1;
	private Button bn2;
	private Button bn3;
	private ListView lv;
	private Cursor cursor;
	private SimpleCursorAdapter scAdapter;
	private DbHelper myDbHelper;
	private int _id;
	private EditText et1,et2;
	
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        lv=(ListView)findViewById(R.id.lv);
        
        myDbHelper=new DbHelper(this);
        myDbHelper.initial();
        cursor=myDbHelper.select(0);
        bn1=(Button)findViewById(R.id.bn1);
        bn2=(Button)findViewById(R.id.bn2);
        bn3=(Button)findViewById(R.id.bn3);
        et1=(EditText)findViewById(R.id.etname);
        et2=(EditText)findViewById(R.id.ettime);
        
        
        
        scAdapter=new SimpleCursorAdapter
        		(this, R.layout.listview, cursor, new String[]{"name","time"}, new int[]{R.id.CourseName,R.id.CourseTime});
        
        lv.setAdapter(scAdapter);
        Log.i(TAG, "Adapter OK");
        
        lv.setOnItemClickListener(new OnItemClickListener() {

		public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
					long arg3) {
				// TODO Auto-generated method stub
				Log.i(TAG, "CLICK1");
				cursor.moveToPosition(arg2);
				Log.i(TAG, "CLICK2");
				_id=cursor.getInt(0);
				Log.i(TAG, "CLICK3");
				et1.setText(cursor.getString(1));
				Log.i(TAG, "CLICK4");
				et2.setText(String.valueOf(cursor.getInt(2)));
				Log.i(TAG, cursor.getString(0));
			}
		});
        
        lv.setOnItemSelectedListener(new OnItemSelectedListener() {

			public void onItemSelected(AdapterView<?> arg0, View arg1,
					int arg2, long arg3) {
				// TODO Auto-generated method stub
				SQLiteCursor sc=(SQLiteCursor)arg0.getSelectedItem();
				_id=sc.getInt(0);
				et1.setText(cursor.getString(1));
				et2.setText(String.valueOf(cursor.getInt(2)));
				Log.i(TAG, sc.getString(0));
				
			}

			public void onNothingSelected(AdapterView<?> arg0) {
				// TODO Auto-generated method stub
				
				
			}
		});
        
        bn1.setOnClickListener(new OnClickListener() {
			
			public void onClick(View arg0) {
				// TODO Auto-generated method stub
				Log.i(TAG, "BN1 OK");
				String name;
				int time;
			try
			{
			name=et1.getText().toString();
	        time=Integer.parseInt(et2.getText().toString());
			}
			catch (Exception e)
			{
				return;
			}
           
			Log.i(TAG, "ADD?");
			add(name, time);
			Log.i(TAG, "BN1 FINISH");
			}
		});
        
        bn2.setOnClickListener(new OnClickListener() {
        	
        	
			public void onClick(View arg0) {
				Log.i(TAG, "BN2 OK");
				// TODO Auto-generated method stub
				String name;
				int time;
			try
			{
			name=et1.getText().toString();
	        time=Integer.parseInt(et2.getText().toString());
			}
			catch (Exception e)
			{
				return;
			}
            edit(name, time);
            Log.i(TAG, "BN2 FINISH");
			}
		});
        
        bn3.setOnClickListener(new OnClickListener() {
			
			public void onClick(View arg0) {
				Log.i(TAG, "BN3 OK");
				// TODO Auto-generated method stub
				String name;
				int time;
			try
			{
			name=et1.getText().toString();
	        time=Integer.parseInt(et2.getText().toString());
			}
			catch (Exception e)
			{
				return;
			}
			delete(name, time);
			Log.i(TAG, "BN3 FINISH");
			}
		});
    }
    
    
    private void add(String name,int time)
    {
    	if (name=="") return;
    	CourseData data=new CourseData();
    	data.setName(name);
    	data.setTime(time);
    	myDbHelper.insert(data);
    	cursor.requery();
    	lv.invalidateViews();
    	et1.setText("");
    	et2.setText("");
    	_id=0;
    	
    }
    
    private void edit(String name,int time)
    {
    	if (name=="") return;
    	CourseData data=new CourseData();
    	data.setName(name);
    	data.setTime(time);
    	myDbHelper.upgrade(data,_id);
    	cursor.requery();
    	lv.invalidateViews();
    	et1.setText("");
    	et2.setText("");
    	_id=0;
    	
    }
    
    private void delete(String name,int time)
    {
    	if (name=="") return;
    	CourseData data=new CourseData();
    	data.setName(name);
    	data.setTime(time);
    	myDbHelper.delete(data,_id);
    	cursor.requery();
    	lv.invalidateViews();
    	et1.setText("");
    	et2.setText("");
    	_id=0;
    }
    
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
        
    }

 最终运行效果如下：