android基础--最基本的SQLite实例

下面是一个最简单的SQLite使用实例, 界面有5个按钮, 分别对应了SQLite的基本操作. 整个程序分成两个文件,  MainActivity.java实现主程序逻辑,DatabaseHelper.java中定义的DatabaseHelper类继承了SQLiteOpenHelper类.

以下是DatabaseHelper.java:

package com.sqlitedb;

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

public class DatabaseHelper extends SQLiteOpenHelper{
		
		private static final int Dversion = 1;
		public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
							int version)
		{
			super(context, name, factory, version);
		}
		
		public DatabaseHelper(Context context, String name, int version)
		{
			this(context, name, null, version);//调用自身的构造函数
		}
		
		public DatabaseHelper(Context context, String name)
		{
			this(context, name, Dversion);  //调用自身的构造函数
		}
		
		@Override
                //当首次创建数据库时被调用
                public void onCreate(SQLiteDatabase db)
		{
			System.out.println("Create database");
			db.execSQL("create table user(id int, name varchar(10))");
		}
		
		@Override
                //当升级数据库时被调用
                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			System.out.println("Upgrade database");
		}
		
}

以下是MainActivity.java:

import com.sqlitedb.DatabaseHelper;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
 
public class MainActivity extends Activity {
    
    private Button button_create,button_upgrade,button_insert;
    private Button button_delete,button_update,button_query;
    private final String db_name = "my_database";
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_1);
        initial();  //初始化界面按钮
    }
    
    private void initial(){
    	button_create = (Button)findViewById(R.id.create);
        button_create.setOnClickListener(new listener_create());
        
        button_upgrade = (Button)findViewById(R.id.upgrade);
        button_upgrade.setOnClickListener(new listener_upgrade());
        
        button_insert = (Button)findViewById(R.id.insert);
        button_insert.setOnClickListener(new listener_insert());
        
        button_delete = (Button)findViewById(R.id.delete);
        button_delete.setOnClickListener(new listener_delete());
        
        button_update = (Button)findViewById(R.id.update);
        button_update.setOnClickListener(new listener_update());
        
        button_query = (Button)findViewById(R.id.query);
        button_query.setOnClickListener(new listener_query());
    }
    
    class listener_create implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name);    		
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
        }
    }
    
    class listener_upgrade implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name, 2);
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
    	}
    }
    
    class listener_insert implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name);
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
    		ContentValues values = new ContentValues();
                //设置欲插入的列名和值
                values.put("id", 1);
    		values.put("name", "Jet");
                //当values参数为空或者里面没有内容的时候，
                //我们insert是会失败的（底层数据库不允许插入一个空行），
                //为了防止这种情况，我们要在这里指定一个列名，
                //如果发现将要插入的行为空行时,
                //就会将你指定的这个列名的值设为null，然后再向数据库中插入。       
                db.insert("user", null, values);
    	}
    }
    
    class listener_delete implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name);
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
                //"?"为通配符, 对应String[]中的各个值
                db.delete("user", "id=?", new String[]{"1"});		
    	}
    }
    
    class listener_update implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name);
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
    		ContentValues values = new ContentValues();
    		values.put("name", "Tom");
                //"?"为通配符, 对应String[]中的各个值
                db.update("user", values, "id=?", new String[]{"1"});
    	}
    }
    
    class listener_query implements OnClickListener
    {
    	@Override
    	public void onClick(View v)
    	{
    		DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, db_name);
    		SQLiteDatabase db = dbHelper.getWritableDatabase();
    		Cursor cursor;
    		cursor = db.query("user", new String[]{"id","name"}, null, null, null, null, null);
                //游标一开始会指向第一个元素前的位置, 即-1, 所以要移动到下一个位置, 即第一个元素
                cursor.moveToNext();
    		String name = cursor.getString(cursor.getColumnIndex("name"));
    		System.out.println(name);
    	}
    }
}