[Android Develop_003]-Android Database

        Android 应用程序与其他应用程序一样都是要和数据库打交道的。Android使用的数据库一般是SQLite 或者 MySql。SQLite的应用非常广泛，从Android到IOS系统使用的都是SQLite数据库，Android 提供了操作SQLite的类库，而MySql则需要通过导入MySql connection 的jar包才能操作数据库。SQLite最大存储容量可以达到2T对于绝大多数的移动应用来说绝对足够了。

       今天使用SQLite数据库写了一个CRUD的Demo，这里贴出来供大家参考。

       

<span style="font-family:Microsoft YaHei;">package com.example.androidexample;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

public class DatabaseActivity extends Activity {

	private Button btnCreateDatabase;	
	private Button btnCreateDatatable;
	private Button btnInsertData;
	private Button btnQueryData;
	private SQLiteDatabase db;
	private static String queryResult;
	private static final String dbName = "AndroidDBExample.db";
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		super.setContentView(R.layout.activity_database);
		
		btnCreateDatabase = (Button)super.findViewById(R.id.btnCreateDatabase);
		btnCreateDatabase.setOnClickListener(crateDatabaseListener);
		
		btnCreateDatatable = (Button)super.findViewById(R.id.btnCreateDataTable);
		btnCreateDatatable.setOnClickListener(crateDatatableListener);
		
		btnInsertData = (Button)super.findViewById(R.id.btnInsertData);
		btnInsertData.setOnClickListener(insertDataListener);
		
		btnQueryData = (Button)super.findViewById(R.id.btnQueryData);
		btnQueryData.setOnClickListener(queryDataListener);
	}
	
	OnClickListener crateDatabaseListener = new OnClickListener(){

		@Override
		public void onClick(View view) {
			// TODO Auto-generated method stub
			db = openOrCreateDatabase(dbName,MODE_PRIVATE,null);
			Toast.makeText(DatabaseActivity.this,"Database has been created successfully.",Toast.LENGTH_LONG).show();
		}
	};
	
	OnClickListener crateDatatableListener = new OnClickListener(){

		@Override
		public void onClick(View arg0) {
			// TODO Auto-generated method stub
			if(db != null){
				String sql = "create table T_UserInfo (UserID int primary key,Username text,Password text)";
				try{
					db.execSQL("drop table if exists T_UserInfo"); 
					db.execSQL(sql);
					Toast.makeText(DatabaseActivity.this,"Datatable has been created successfully.",Toast.LENGTH_LONG).show();
				}catch(SQLException ex){
					ex.printStackTrace();
				}finally{
					//db.close();
				}
			}
		}
	};
	
	OnClickListener insertDataListener = new OnClickListener(){

		@Override
		public void onClick(View arg0) {
			// TODO Auto-generated method stub
			if(db != null){
				try{
					ContentValues cv = new ContentValues();
					cv.put("UserID", 1);
					cv.put("Username","Tom");
					cv.put("Password","1234abcd");
					db.insert("T_UserInfo", null, cv);
					
					//you can insert data by execute sql
					//to do
					
					Toast.makeText(DatabaseActivity.this,"Data has been inserted successfully.",Toast.LENGTH_LONG).show();
				}catch(SQLException ex){
					
				}finally{
					//db.close();
				}
			}
		}
	};
	
	OnClickListener queryDataListener = new OnClickListener(){

		@Override
		public void onClick(View arg0) {
			// TODO Auto-generated method stub
			if(db!=null){
				try{
					Cursor cursor = db.rawQuery("select * from T_UserInfo where Username = ?", new String[]{ "Tom" });
					 while (cursor.moveToNext()) { 
						 int id = cursor.getInt(cursor.getColumnIndex("UserID"));
						 String name = cursor.getString(cursor.getColumnIndex("Username"));
						 queryResult = "id=>" + id + ", name=>" + name;
						 Log.i("queryResult",queryResult );  
					 }
					 cursor.close();
					 Toast.makeText(DatabaseActivity.this,queryResult,Toast.LENGTH_LONG).show();
				}catch(SQLException ex){
					ex.printStackTrace();
				}finally{
					db.close();
				}
			}
		}
	};
}</span>

这个例子中包含了创建数据库的方法，创建表的方法，插入数据的方法及查询数据的方法。当然对数据库的操作还有删除表、删除数据库等等，这些操作跟常规的sqlserver,oracle数据库操作类似，不再赘述，这里只是抛砖引玉，供大家参考。SQLite其实也可以做触发器，索引等。今后有机会再详细介绍。下面是layotu文件：

<span style="font-family:Microsoft YaHei;"><?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >
    
    <Button 
            android:id="@+id/btnCreateDatabase"
          	android:layout_width="match_parent"
          	android:layout_height="wrap_content"
          	android:text="@string/CreateDatabase"/>
    <Button 
            android:id="@+id/btnCreateDataTable"
          	android:layout_width="match_parent"
          	android:layout_height="wrap_content"
          	android:text="@string/CreateDataTable"/>

    <Button 
            android:id="@+id/btnInsertData"
          	android:layout_width="match_parent"
          	android:layout_height="wrap_content"
          	android:text="@string/InsertData"/>
    
    <Button 
            android:id="@+id/btnQueryData"
          	android:layout_width="match_parent"
          	android:layout_height="wrap_content"
          	android:text="@string/QueryData"/>
</LinearLayout>
</span>

效果如下：

Toast显示的数据就是查询到的结果。