今天趁着有时间，自己在网上找了相关的数据库操作代码，进行了一下练习，先上代码
main.xml文件
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.example.sqlittest.MainActivity" > <TextView android:id="@+id/tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="@string/hello_world" android:textColor="#0000ff" /> <LinearLayout android:id="@+id/l1" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentBottom="true" android:orientation="horizontal" > <Button android:id="@+id/bt1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="创建数库" android:textColor="#0000ff" /> <Button android:id="@+id/bt2" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="创建表格" android:textColor="#0000ff" /> <Button android:id="@+id/bt3" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="插入数据" android:textColor="#0000ff" /> </LinearLayout> <LinearLayout android:id="@+id/l2" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_above="@id/l1" android:orientation="horizontal" > > <Button android:id="@+id/bt4" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="删除数据" android:textColor="#0000ff" /> <Button android:id="@+id/bt5" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="修改数据" android:textColor="#0000ff" /> <Button android:id="@+id/bt6" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="查看表格" android:textColor="#0000ff" /> </LinearLayout> </RelativeLayout>
然后看练习代码
package com.example.sqlittest; import android.R.string; import android.support.v4.widget.SimpleCursorAdapter.ViewBinder; import android.support.v7.app.ActionBarActivity; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.util.Log; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.TextView; public class MainActivity extends ActionBarActivity implements OnClickListener { TextView tv; Button bt1, bt2, bt3, bt4, bt5, bt6, bt7; SQLiteDatabase mDatabase; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); mDatabase = SQLiteDatabase.openOrCreateDatabase( "/data/data/com.example.sqlittest/test.db", null); initButton(); } private void initButton() { tv = (TextView) findViewById(R.id.tv); bt1 = (Button) findViewById(R.id.bt1); bt2 = (Button) findViewById(R.id.bt2); bt3 = (Button) findViewById(R.id.bt3); bt4 = (Button) findViewById(R.id.bt4); bt5 = (Button) findViewById(R.id.bt5); bt6 = (Button) findViewById(R.id.bt6); bt1.setOnClickListener(this); bt2.setOnClickListener(this); bt3.setOnClickListener(this); bt4.setOnClickListener(this); bt5.setOnClickListener(this); bt6.setOnClickListener(this); } @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; } @Override public boolean onOptionsItemSelected(MenuItem item) { // Handle action bar item clicks here. The action bar will // automatically handle clicks on the Home/Up button, so long // as you specify a parent activity in AndroidManifest.xml. int id = item.getItemId(); if (id == R.id.action_settings) { return true; } return super.onOptionsItemSelected(item); } // 创建数据库 private void createdb() { mDatabase = SQLiteDatabase.openOrCreateDatabase( "/data/data/com.example.sqlittest/test.db", null); } // 创建表格 private void createtabe() { String stu_table = "create table testtable(_id integer primary key autoincrement,sname text,snumber text)"; mDatabase.execSQL(stu_table); } // 插入数据方式1 private void insert1() { ContentValues contentValues = new ContentValues(); contentValues.put("sname", "zhangsan"); contentValues.put("snumber", "13166111082"); mDatabase.insert("testtable", null, contentValues); } // 插入数据方式2 private void insert2() { String stu_sql = "insert into testtable(sname,snumber) values('xiaoming','01005')"; mDatabase.execSQL(stu_sql); } // 删除数据方式1 private void delete1() { String string = "_id=?"; String[] string2 = { String.valueOf(1) }; mDatabase.delete("testtable", string, string2); } // 删除数据方式2 private void delete2() { String sql = "delete from testtable where _id = 2"; mDatabase.execSQL(sql); } // 修改数据方式1 private void change1() { String string = "_id=?"; String[] string2 = { String.valueOf(1) }; ContentValues contentValues = new ContentValues(); contentValues.put("sname", "zhangsi"); mDatabase.update("testtable", contentValues, string, string2); } // 修改数据方式2 private void change2() { String sql = "update testtable set snumber = 654321 where _id = 1"; mDatabase.execSQL(sql); } // 数据库查询 private String squre() { StringBuffer stringBuffer = new StringBuffer(); Cursor cursor = mDatabase.query("testtable", null, null, null, null, null, null); // 判断游标是否为空 if (!cursor.moveToFirst()) { return null; } for (int i = 0; i < cursor.getCount() - 1; i++) { // 获得ID cursor.moveToNext(); int id = cursor.getInt(0); // 获得用户名 int name_id = cursor.getColumnIndexOrThrow("sname"); String username = cursor.getString(name_id); // 获得密码 int number_id = cursor.getColumnIndexOrThrow("snumber"); Log.e("123", "" + name_id + " " + number_id + " count" + cursor.getCount()); String password = cursor.getString(number_id); stringBuffer.append("name_id:" + name_id + " " + " name:" + username + " " + " number:" + password + " "); } return stringBuffer.toString(); } @Override public void onClick(View arg0) { // TODO Auto-generated method stub switch (arg0.getId()) { case R.id.bt1: // createdb(); break; case R.id.bt2: // createtabe(); break; case R.id.bt3: insert1(); insert2(); break; case R.id.bt4: delete1(); delete2(); break; case R.id.bt5: change1(); change2(); break; case R.id.bt6: tv.setText(squre()); break; default: break; } } }
结果如下
在做练习的时候，遇到了以下几个问题：
1）当创建数据库的时候，刚开始使用的是
mDatabase = SQLiteDatabase.openOrCreateDatabase("test.db",null);
结果报错了。所以我们可以得出的结论是，这个数据库创建，必须是指定的应用路径
2）当进行遍历的时候，开始使用的是
for (int i = 0; i < cursor.getCount() - 1; i++) { cursor.move(i); }
报错，后来发现问题了，原来是数据库并不会自动更新id，也就是说，如果你删除了id=3的行，那么这一行就永久消失了，id只会增加，不会自动重新排序
3）当我们删除一个数据的时候，我们查看数据库，会发现表格内容如下
3|zhangsan|13166111082 4|xiaoming|01005 5|zhangsan|13166111082 6|xiaoming|01005 7|zhangsan|13166111082 8|xiaoming|01005 9|zhangsan|13166111082 10|xiaoming|01005 11|zhangsan|13166111082 12|xiaoming|01005 13|zhangsan|13166111082 14|xiaoming|01005 15|zhangsan|13166111082 16|xiaoming|01005 17|zhangsan|13166111082 18|xiaoming|01005 19|zhangsan|13166111082 20|xiaoming|01005 21|zhangsan|13166111082 22|xiaoming|01005 23|zhangsan|13166111082 24|xiaoming|01005 25|zhangsan|13166111082
另外，数据库id永远是从1开始，而不是0，这是问题2报错的原因