Android-SQLite使用总结

android中SQLiteDatabase的创建是通过SQLiteOpenHelper类的数据库获取方法完成的，其中包括getReadableDatabase()、getWritableDatabase()等。数据创建完成后，通过对数据库增、删、改、查等方法的重载，即可完成数据库操作的应用。

具体的SQLite使用的具体步骤如下：

1.新建一个继承自SQLiteOpenHelper的类DatabaseHelper；

2.通过DatabaseHelper的对象，调用其方法getReadableDatabase()，返回SQLiteDatabase数据库对象；

3.定义Button监听函数，再对应的监听函数中进行数据库增删改查操作。

下面以代码为主简单介绍起用法：

一、新建一个类DatabaseHelper，继承自SQLiteOpenHelper。类文件名DatabaseHelper.java，内容如下：

package com.example.sqlite.db;

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

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final int VERSION = 1;
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	public DatabaseHelper(Context context, String name){
		this(context, name, VERSION);
	}

	public DatabaseHelper(Context context, String name, int version) {
		// TODO Auto-generated constructor stub
		this(context, name, null, version);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "create table user(id int, name varchar(20), score int)";
		db.execSQL(sql);
		System.out.println("create a database.");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		// TODO Auto-generated method stub
		System.out.println("update a database version.");
		
	}

}

该类做了如下工作：1.重载了几个构造函数；2.重载了新建列表函数onCreate()。其中类的构造函数是为了传入数据库名称；列表创建函数重载是为了定义列表名称及结构。更多数据库的知识需另行学习。

二、主Activity文件

主Activity中主要定义了几个Button空间的监听函数，在对应的Button监听函数中完成数据库增删改查的操作。对数据库的操作一般有两种方式：1.通过SQLiteDatabase类的insert/delete/update/query等方法进行操作；2.通过SQLiteDatabase类的execSQL方法，使用数据库操作命令进行操作。

主Activity文件名MainActivity.java，具体内容如下：

package com.example.sqlite;

import android.R.integer;
import android.app.Activity;
import android.content.ContentValues;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.drawable.Drawable;
import android.os.Bundle;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

import com.example.sqlite.db.DatabaseHelper;

public class MainActivity extends Activity {
	//定义类的控件成员变量
	private Button btn_ist = null;
	private Button btn_dlt = null;
	private Button btn_mdf = null;
	private Button btn_qry = null;
	private Button btn_crt = null;
	private Button btn_upd = null;
	private EditText edt_id = null;
	private EditText edt_value = null;
	
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        
        //set background color
        Resources res = getResources();
        Drawable drawable = res.getDrawable(R.drawable.bkground);
        this.getWindow().setBackgroundDrawable(drawable);
        
        //通过id获取控件对象
        btn_ist = (Button)findViewById(R.id.insert);
        btn_dlt = (Button)findViewById(R.id.delete);
        btn_mdf = (Button)findViewById(R.id.modify);
        btn_qry = (Button)findViewById(R.id.query);
        btn_crt = (Button)findViewById(R.id.createdb);
        btn_upd = (Button)findViewById(R.id.updatedb);
        edt_id 	= (EditText)findViewById(R.id.id);
        edt_value=(EditText)findViewById(R.id.value);
        
        //设置button的点击消息响应函数
        //无名类定义的方式
        btn_ist.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View arg0) {
				// TODO Auto-generated method stub
				//获取edit text参数
				String str = edt_id.getText().toString();
				System.out.println("insert a data:"+str);
				//if( str !="" )
				{
					int id_ist = Integer.parseInt(str);
					String name_ist = edt_value.getText().toString();
					//增加一条记录
					ContentValues value = new ContentValues();
					value.put("id", id_ist);
					value.put("name", name_ist);
					value.put("score", 100);
					
					DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");
					SQLiteDatabase db = dbHelper.getWritableDatabase();
					db.insert("user", null, value);
					System.out.println("insert a data.");
				}
				
				//else
				//	System.out.println("Please input your record.");
				
			}
		});
        //有名类定义的方式
        btn_dlt.setOnClickListener(new deleteOnClickListener());
        btn_mdf.setOnClickListener(new modifyOnClickListener());
        btn_qry.setOnClickListener(new queryOnClickListener());
        btn_crt.setOnClickListener(new createOnClickListener());
        btn_upd.setOnClickListener(new updateOnClickListener());
        
        //创建数据库助手类对象
        
        
    }
    
    class deleteOnClickListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			System.out.println("delete a data.");
			
			String dlt_id = edt_id.getText().toString();
			
			DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			//db.delete("user", "id=?", new String[]{dlt_id});
			db.execSQL("delete from user where id="+dlt_id);//这句命令操作不能同时用多个条件限制
		}
    	
    }
    class modifyOnClickListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			System.out.println("modify a data.");
			
			String mdf_id = edt_id.getText().toString();
			String mdf_value = edt_value.getText().toString();			
						
			ContentValues value = new ContentValues();
			value.put("score", mdf_value);
			
			DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			db.update("user", value, "id=?", new String[]{mdf_id});
		}
    	
    }
    class queryOnClickListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			System.out.println("query a data.");
			
			//获取一条记录
			String id_qry = edt_id.getText().toString();
			
			//打开数据库
			DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query("user", new String[]{"id","name","score"}, "id=?", new String[]{ id_qry }, null, null, null);
			while (cursor.moveToNext())
			{
				String name = cursor.getString(cursor.getColumnIndex("name"));
				String score = cursor.getString(cursor.getColumnIndex("score"));
				System.out.println("query--->"+name+":"+score);
				edt_value.setText(name+":"+score);
			}
			
			cursor.close();
			
		}
    }
    class createOnClickListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub			
			//通过database helper创建数据库
			DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");
			SQLiteDatabase db = dbHelper.getReadableDatabase();
		}
    	
    }
    class updateOnClickListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			//更新数据库版本号
			DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx", 2);
			SQLiteDatabase db = dbHelper.getReadableDatabase();
		}
    	
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }
    
}

注意：此处在每个Button监听函数中都重新new一个数据库Helper的类，并根据读写需求获取了相应的SQLiteDatabase对象。

三、其他文件内容

其他相关文件包括：布局文件activity_main.xml和strings.xml。内容分别如下：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    
     >
	<LinearLayout 
	    android:orientation="horizontal"
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"   
	     >	
	   		<TextView
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"
	        android:textColor="#aaaaaa"
	        android:layout_weight="1"
	        android:gravity="left"
	        android:text="@string/id" />
	        <TextView
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"
	        android:textColor="#aaaaaa"
	        android:layout_weight="1"
	        android:gravity="left"
			android:text="@string/value"
	        /> 

	</LinearLayout>
	<LinearLayout 
		android:orientation="horizontal"
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"
	    android:layout_marginTop="5dip"
	    
	     >	
	   		<EditText
	   		android:id="@+id/id"
	   		android:hint="@string/id"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"
	        android:layout_marginRight="5dip"
	        android:background="#aaaaaa"
	        android:maxLines="1"
	        android:layout_weight="1" />
	        <EditText
	        android:id="@+id/value"
	        android:hint="@string/value"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"
	        android:layout_marginRight="5dip"
	        android:background="#aaaaaa"
	        android:maxLines="1"
	        android:layout_weight="1" />

	</LinearLayout>

	<LinearLayout
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:layout_marginTop="10dip"
	   
	    >
	        
		    <Button
	        android:id="@+id/insert"
	        android:layout_width="match_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/insert" 
	        android:clickable="true"
	        />	
			<Button
	        android:id="@+id/delete"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/delete" 
	        android:clickable="true"/>
			
			<Button
	        android:id="@+id/modify"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/modify"
	        android:clickable="true"/>
			<Button
	        android:id="@+id/query"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/query"
	        android:clickable="true"/>
	</LinearLayout>
	
		<LinearLayout
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:layout_marginTop="5dip"
	    
	    >
	        
		    <Button
	        android:id="@+id/createdb"
	        android:layout_width="match_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/createdb" 
	        android:clickable="true"
	        />	
			<Button
	        android:id="@+id/updatedb"
	        android:layout_width="fill_parent"
	        android:layout_height="wrap_content"      		
	        android:layout_weight="1"
	        android:text="@string/updatedb" 
	        android:clickable="true"/>
			

	</LinearLayout>


</LinearLayout>

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">sqlite</string>
    <string name="action_settings">Settings</string>
    <string name="hello_world">Hello world!</string>
    <string name="id">id:</string>
    <string name="value">value:</string>
    <string name="operation">operation:</string>
    <string name="insert">insert</string>
    <string name="delete">delete</string>
    <string name="modify">modifi</string>
    <string name="query">query</string>
    <string name="createdb">createdb</string>
    <string name="updatedb">updatedb</string>
    <drawable name="bkground">#000000</drawable>

</resources>