SQLite-SQLiteDatabase 数据库实例练习

今天趁着有时间，自己在网上找了相关的数据库操作代码，进行了一下练习，先上代码

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 + "\n" + " name:"

                    + username + "\n" + " number:" + password + "\n");

        }

        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）当我们删除一个数据的时候，我们查看数据库，会发现表格内容如下

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

|xiaoming|

|zhangsan|

另外，数据库id永远是从1开始，而不是0，这是问题2报错的原因

巴特西

SQLite-SQLiteDatabase 数据库实例练习

最新文章

热门文章