数据存储与访问
1.布局
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="fill_parent" android:orientation="vertical" > <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" > <TextView android:id="@+id/textView1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="姓名：" /> <EditText android:id="@+id/edit_name" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:ems="10" > <requestFocus /> </EditText> <TextView android:id="@+id/textView2" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="性别：" /> <EditText android:id="@+id/edit_sex" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:ems="10" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" > <TextView android:id="@+id/textView3" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="部门：" /> <EditText android:id="@+id/edit_department" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:ems="10" > <requestFocus /> </EditText> <TextView android:id="@+id/textView4" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="工资：" /> <EditText android:id="@+id/edit_salary" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:ems="10" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" > <Button android:id="@+id/btn_insert" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="添加" /> <Button android:id="@+id/btn_clear" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="清除" /> <Button android:id="@+id/btn_showall" android:layout_width="100dp" android:layout_height="wrap_content" android:text="显示全部" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" > <TextView android:id="@+id/textView5" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text=" ID： " /> <EditText android:id="@+id/secachID" android:layout_width="79dp" android:layout_height="wrap_content" android:ems="10" /> <Button android:id="@+id/btn_search" android:layout_width="100dp" android:layout_height="wrap_content" android:text="显示" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical" > <TextView android:id="@+id/text_show" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="TextView" /> </LinearLayout> <ScrollView android:layout_width="fill_parent" android:layout_height="fill_parent"> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:orientation="vertical"> <TextView android:id="@+id/display" android:text="" android:layout_width="wrap_content" android:layout_height="wrap_content"> </TextView> </LinearLayout> </ScrollView> </LinearLayout>
2.src中的People类
1 package com.example.sqltest; 2 3 public class People { 4 public int ID = -1; 5 public String Name ; 6 public String Sex; 7 public String Department; 8 public float Salary; 9 @Override 10 public String toString( ){ 11 String result = ""; 12 result += "ID: " + this.ID + "," ; 13 result += "姓名：" + this.Name + ","; 14 result += "性别：" + this.Sex + ","; 15 result += "部门：" + this.Department + "," ; 16 result += "薪水：" + this.Salary + "," ; 17 return result; 18 } 19 20 }
3.DBAdapter.java
1 package com.example.sqltest; 2 3 import android.content.ContentValues; 4 import android.content.Context; 5 import android.database.Cursor; 6 import android.database.sqlite.SQLiteDatabase; 7 import android.database.sqlite.SQLiteDatabase.CursorFactory; 8 import android.database.sqlite.SQLiteException; 9 import android.database.sqlite.SQLiteOpenHelper; 10 11 12 13 public class DBAdapter { 14 private static final String DB_NAME = "test.db"; 15 private static final String DB_TABLE = "peopleinfo"; 16 private static final int DB_VERSION = 1; 17 18 public static final String KEY_ID = "id"; 19 public static final String KEY_NAME = "name"; 20 public static final String KEY_SEX = "sex"; 21 public static final String KEY_DEPARTMENT = "department"; 22 public static final String KEY_SALARY = "salary"; 23 24 private SQLiteDatabase db; 25 private final Context context; 26 private DBOpenHelper dbOpenHelper; 27 28 public DBAdapter(Context _context) { 29 context = _context; 30 } 31 public void close() { 32 if (db != null) { 33 db.close(); 34 db = null; 35 } 36 } 37 public void open() throws SQLiteException { 38 dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION); 39 try { 40 db = dbOpenHelper.getWritableDatabase(); 41 } catch (SQLiteException ex) { 42 db = dbOpenHelper.getReadableDatabase(); 43 } 44 } 45 public long insert(People people) { 46 ContentValues newValues = new ContentValues(); 47 48 newValues.put(KEY_NAME, people.Name); 49 newValues.put(KEY_SEX, people.Sex); 50 newValues.put(KEY_DEPARTMENT, people.Department); 51 newValues.put(KEY_SALARY, people.Salary); 52 return db.insert(DB_TABLE, null, newValues); 53 } 54 public People[] queryAllData() { 55 Cursor results = db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, 56 KEY_SEX,KEY_DEPARTMENT,KEY_SALARY }, null, null, null, null, null); 57 return ConvertToPeople(results); 58 } 59 public People[] queryOneData(long id) { 60 Cursor results = db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, 61 KEY_SEX,KEY_DEPARTMENT,KEY_SALARY }, KEY_ID + "=" + id, null, null, null, 62 null); 63 return ConvertToPeople(results); 64 } 65 private People[] ConvertToPeople(Cursor cursor) { 66 int resultCounts = cursor.getCount(); 67 if (resultCounts == 0 || !cursor.moveToFirst()) { 68 return null; 69 } 70 People[] peoples = new People[resultCounts]; 71 for (int i = 0; i < resultCounts; i++) { 72 peoples[i] = new People(); 73 peoples[i].ID = cursor.getInt(0); 74 peoples[i].Name = cursor.getString(cursor.getColumnIndex(KEY_NAME)); 75 peoples[i].Sex = cursor.getString(cursor.getColumnIndex(KEY_SEX)); 76 peoples[i].Department = cursor.getString(cursor.getColumnIndex(KEY_DEPARTMENT)); 77 peoples[i].Salary = cursor.getFloat(cursor.getColumnIndex(KEY_SALARY)); 78 cursor.moveToNext(); 79 } 80 return peoples; 81 } 82 public long deleteAllData() { 83 return db.delete(DB_TABLE, null, null); 84 } 85 86 public long deleteOneData(long id) { 87 return db.delete(DB_TABLE, KEY_ID + "=" + id, null); 88 } 89 public long updateOneData(long id , People people){ 90 ContentValues updateValues = new ContentValues(); 91 updateValues.put(KEY_NAME, people.Name); 92 updateValues.put(KEY_SEX,people.Sex); 93 updateValues.put(KEY_DEPARTMENT, people.Department); 94 updateValues.put(KEY_SALARY, people.Salary); 95 96 return db.update(DB_TABLE, updateValues, KEY_ID + "=" + id, null); 97 } 98 private static class DBOpenHelper extends SQLiteOpenHelper { 99 100 public DBOpenHelper(Context context, String name, 101 CursorFactory factory, int version) { 102 super(context, name, factory, version); 103 } // 构造方法 104 105 private static final String DB_CREATE = "create table " + DB_TABLE 106 + " (" + KEY_ID + " integer primary key autoincrement, " 107 + KEY_NAME + " text not null," + KEY_SEX + " text," + KEY_DEPARTMENT 108 + " text," + KEY_SALARY + " float);"; 109 110 @Override 111 public void onCreate(SQLiteDatabase _db) { 112 _db.execSQL(DB_CREATE); 113 } 114 115 @Override 116 public void onUpgrade(SQLiteDatabase _db, int _oldVersion, 117 int _newVersion) { 118 _db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE); 119 onCreate(_db); 120 } 121 122 } 123 }
4.编写MainActivity.java
1 package com.example.sqltest; 2 3 import android.app.Activity; 4 import android.os.Bundle; 5 import android.view.Menu; 6 import android.view.View; 7 import android.view.View.OnClickListener; 8 import android.widget.Button; 9 import android.widget.EditText; 10 import android.widget.TextView; 11 12 public class MainActivity extends Activity { 13 14 private EditText name; 15 private EditText salary; 16 private EditText department; 17 private EditText sex; 18 private EditText secachID; 19 private Button btn_insert; 20 private Button btn_clear; 21 private Button btn_showall; 22 private Button btn_search; 23 private TextView text; 24 private TextView display; 25 private DBAdapter dbAdepter; 26 27 @Override 28 protected void onCreate(Bundle savedInstanceState) { 29 super.onCreate(savedInstanceState); 30 setContentView(R.layout.activity_main); 31 32 name = (EditText)findViewById(R.id.edit_name); 33 salary = (EditText)findViewById(R.id.edit_salary); 34 department = (EditText)findViewById(R.id.edit_department); 35 sex = (EditText)findViewById(R.id.edit_sex); 36 secachID = (EditText)findViewById(R.id.secachID); 37 btn_insert = (Button)findViewById(R.id.btn_insert); 38 btn_clear = (Button)findViewById(R.id.btn_clear); 39 btn_showall = (Button)findViewById(R.id.btn_showall); 40 btn_search = (Button)findViewById(R.id.btn_search); 41 text = (TextView)findViewById(R.id.text_show); 42 display = (TextView) findViewById(R.id.display); 43 44 dbAdepter = new DBAdapter(this); 45 dbAdepter.open(); 46 47 btn_insert.setOnClickListener(new OnClickListener() { 48 49 @Override 50 public void onClick(View v) { 51 // TODO Auto-generated method stub 52 People people = new People(); 53 people.Name = name.getText().toString(); 54 people.Sex = sex.getText().toString(); 55 people.Department = department.getText().toString(); 56 people.Salary = Float.parseFloat(salary.getText().toString()); 57 long colunm = dbAdepter.insert(people); 58 59 if (colunm == -1 ){ 60 text.setText("添加过程错误！"); 61 } else { 62 text.setText("成功添加数据，ID："+String.valueOf(colunm)); 63 64 } 65 } 66 }); 67 btn_clear.setOnClickListener(new OnClickListener() { 68 69 @Override 70 public void onClick(View v) { 71 // TODO Auto-generated method stub 72 display.setText(""); 73 } 74 }); 75 btn_showall.setOnClickListener(new OnClickListener() { 76 77 @Override 78 public void onClick(View v) { 79 // TODO Auto-generated method stub 80 People[] peoples = dbAdepter.queryAllData(); 81 if (peoples == null){ 82 text.setText("数据库中没有数据"); 83 return; 84 } 85 text.setText("数据库："); 86 String msg = ""; 87 for (int i = 0 ; i<peoples.length; i++){ 88 msg += peoples[i].toString()+"\n"; 89 } 90 display.setText(msg); 91 } 92 }); 93 btn_search.setOnClickListener(new OnClickListener() { 94 95 @Override 96 public void onClick(View v) { 97 // TODO Auto-generated method stub 98 int id = Integer.parseInt(secachID.getText().toString()); 99 People[] peoples = dbAdepter.queryOneData(id); 100 101 if (peoples == null){ 102 text.setText("数据库中没有ID为"+String.valueOf(id)+"的数据"); 103 return; 104 } 105 text.setText("数据库："); 106 display.setText(peoples[0].toString()); 107 } 108 }); 109 110 } 111 112 @Override 113 public boolean onCreateOptionsMenu(Menu menu) { 114 // Inflate the menu; this adds items to the action bar if it is present. 115 getMenuInflater().inflate(R.menu.main, menu); 116 return true; 117 } 118 119 }
