1 package com.example.android.db01; 2 3 import android.app.Activity; 4 import android.content.ContentValues; 5 import android.content.Context; 6 import android.database.Cursor; 7 import android.database.sqlite.SQLiteDatabase; 8 import android.database.sqlite.SQLiteOpenHelper; 9 import android.os.Bundle; 10 import android.view.View; 11 import android.view.View.OnClickListener; 12 import android.widget.AdapterView; 13 import android.widget.ArrayAdapter; 14 import android.widget.Button; 15 import android.widget.EditText; 16 import android.widget.Spinner; 17 import android.widget.AdapterView.OnItemSelectedListener; 18 19 public class SQLiteTest extends Activity { 20 OnClickListener listener_add = null; 21 OnClickListener listener_update = null; 22 OnClickListener listener_delete = null; 23 OnClickListener listener_clear = null; 24 Button button_add; 25 Button button_update; 26 Button button_delete; 27 Button button_clear; 28 DBConnection helper; 29 public int id_this; 30 public interface UserSchema { 31 String TABLE_NAME = "Users"; //Table Name 32 String ID = "_id"; //ID 33 String USER_NAME = "user_name"; //User Name 34 String ADDRESS = "address"; //Address 35 String TELEPHONE = "telephone"; //Phone Number 36 String MAIL_ADDRESS = "mail_address"; //Mail Address 37 } 38 /** Called when the activity is first created. */ 39 //SQLiteTest 40 @Override 41 public void onCreate(final Bundle savedInstanceState) { 42 super.onCreate(savedInstanceState); 43 setContentView(R.layout.main); 44 final EditText mEditText01 = (EditText)findViewById(R.id.EditText01); 45 final EditText mEditText02 = (EditText)findViewById(R.id.EditText02); 46 final EditText mEditText03 = (EditText)findViewById(R.id.EditText03); 47 final EditText mEditText04 = (EditText)findViewById(R.id.EditText04); 48 49 helper = new DBConnection(this); 50 final SQLiteDatabase db = helper.getWritableDatabase(); 51 final String[] FROM = 52 { 53 UserSchema.ID, 54 UserSchema.USER_NAME, 55 UserSchema.TELEPHONE, 56 UserSchema.ADDRESS, 57 UserSchema.MAIL_ADDRESS 58 }; 59 //基本演算法,計算出db數數庫中總共有多少項數據列。 60 Cursor c = db.query(UserSchema.TABLE_NAME, new String[] {UserSchema.USER_NAME}, null, null, null, null, null); 61 c.moveToFirst(); 62 CharSequence[] list = new CharSequence[c.getCount()]; 63 for (int i = 0; i < list.length; i++) { 64 list[i] = c.getString(0); 65 c.moveToNext(); 66 } 67 c.close(); 68 // 69 Spinner spinner = (Spinner)findViewById(R.id.Spinner01); 70 spinner.setAdapter(new ArrayAdapter<CharSequence>(this, android.R.layout.simple_spinner_item, list)); 71 72 spinner.setOnItemSelectedListener(new OnItemSelectedListener(){ 73 public void onItemSelected(AdapterView<?> parent, View view, int position, long id) { 74 String user_name = ((Spinner)parent).getSelectedItem().toString(); 75 Cursor c = db.query("Users", FROM , "user_name='" + user_name + "'", null, null, null, null); 76 c.moveToFirst(); 77 id_this = Integer.parseInt(c.getString(0)); 78 String user_name_this = c.getString(1); 79 String telephone_this = c.getString(2); 80 String address_this = c.getString(3); 81 String mail_address_this = c.getString(4); 82 c.close(); 83 mEditText01.setText(user_name_this); 84 mEditText02.setText(telephone_this); 85 mEditText03.setText(address_this); 86 mEditText04.setText(mail_address_this); 87 } 88 public void onNothingSelected(AdapterView<?> parent) { 89 } 90 }); 91 //增加資料,定義項是ContentValues,獲取資料時使用put,然後用SQLiteDatanase的方法去開啟數據庫,最後將資料輸入database,用的方法是db.insert 92 listener_add = new OnClickListener() { 93 public void onClick(View v) { 94 ContentValues values = new ContentValues(); 95 values.put(UserSchema.USER_NAME, mEditText01.getText().toString()); 96 values.put(UserSchema.TELEPHONE, mEditText02.getText().toString()); 97 values.put(UserSchema.ADDRESS, mEditText03.getText().toString()); 98 values.put(UserSchema.MAIL_ADDRESS, mEditText04.getText().toString()); 99 SQLiteDatabase db = helper.getWritableDatabase(); 100 db.insert(UserSchema.TABLE_NAME, null, values); 101 db.close(); 102 onCreate(savedInstanceState); 103 } 104 }; 105 //更新資料,定義項是ContentValues,獲取資料時使用put,然後用SQLiteDatanase的方法去開啟數據庫,最後將資料輸入database,用的方法是db.update 106 listener_update = new OnClickListener() { 107 public void onClick(View v) { 108 ContentValues values = new ContentValues(); 109 values.put(UserSchema.USER_NAME, mEditText01.getText().toString()); 110 values.put(UserSchema.TELEPHONE, mEditText02.getText().toString()); 111 values.put(UserSchema.ADDRESS, mEditText03.getText().toString()); 112 values.put(UserSchema.MAIL_ADDRESS, mEditText04.getText().toString()); 113 String where = UserSchema.ID + " = " + id_this; 114 SQLiteDatabase db = helper.getWritableDatabase(); 115 db.update(UserSchema.TABLE_NAME, values, where ,null); 116 db.close(); 117 onCreate(savedInstanceState); 118 } 119 }; 120 //[Delete] 121 listener_delete = new OnClickListener() { 122 public void onClick(View v) { 123 String where = UserSchema.ID + " = " + id_this; 124 SQLiteDatabase db = helper.getWritableDatabase(); 125 db.delete(UserSchema.TABLE_NAME, where ,null); 126 db.close(); 127 onCreate(savedInstanceState); 128 } 129 }; 130 //[Clear] 131 listener_clear = new OnClickListener() { 132 public void onClick(View v) { 133 mEditText01.setText(""); 134 mEditText02.setText(""); 135 mEditText03.setText(""); 136 mEditText04.setText(""); 137 } 138 }; 139 //BUTTON0i,i=1,2,3,4 OnClickListener 140 button_add = (Button)findViewById(R.id.Button01); 141 button_add.setOnClickListener(listener_add); 142 button_update = (Button)findViewById(R.id.Button02); 143 button_update.setOnClickListener(listener_update); 144 button_delete = (Button)findViewById(R.id.Button03); 145 button_delete.setOnClickListener(listener_delete); 146 button_clear = (Button)findViewById(R.id.Button04); 147 button_clear.setOnClickListener(listener_clear); 148 } 149 //SQLiteOpenHelper,創建資料庫 150 public static class DBConnection extends SQLiteOpenHelper { 151 private static final String DATABASE_NAME = "PhoneBookDB"; 152 private static final int DATABASE_VERSION = 1; 153 private DBConnection(Context ctx) { 154 super(ctx, DATABASE_NAME, null, DATABASE_VERSION); 155 } 156 public void onCreate(SQLiteDatabase db) { 157 String sql = "CREATE TABLE " + UserSchema.TABLE_NAME + " (" 158 + UserSchema.ID + " INTEGER primary key autoincrement, " 159 + UserSchema.USER_NAME + " text not null, " 160 + UserSchema.TELEPHONE + " text not null, " 161 + UserSchema.ADDRESS + " text not null, " 162 + UserSchema.MAIL_ADDRESS + " text not null "+ ");"; 163 //Log.i("haiyang:createDB=", sql); 164 db.execSQL(sql); 165 } 166 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 167 // TODO Auto-generated method stub 168 } 169 } 170 } 171