Android SQLite 的简单实例

　　

　　1.前言：

　　　　今天再一次去蹭了一下某老师的android课，这一次讲的是Android的SQLite的使用，老师当场讲解了他自己做的例子。

　　回来之后，我春心萌动，不得不拿着参考资料再做了一个类似的例子，其实我已经过几遍SQLite的内容了，但是认识还是不深刻。

　   2.SQLite继承　　

　　　　要想使用SQLite,就必需设计一个相应类，并且继承SQLiteOpenHelper。

　　　　基本上要操作的是onCreate函数（注意自动生成，执行语句建议还是单独写），这个函数在数据库被提及时便会执行，所以添加的内容一般就是建表操作。

　　person.java

package Model;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class Person extends SQLiteOpenHelper {

    public static final String CREATE_BOOK = "create table person("    
    + "_id integer primary key autoincrement, "
    + "name text, "     
    + "tel text)";
    public Person(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(CREATE_BOOK); //建表
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }
}

　　3.使用

　　　　SQLite的使用其实套路都一样的。

personHelper = new Person(this, "personDB.db", null, 1);//new一个类，注意*.db不能跟类重名

　　　　这里因为不理解的原因，“personDB.db”我第一次海用了"person".....

　　增加：getWritableDatabase在这里我很不理解，getReadDatabase一般还用不到，看了一次百度的一些解答还是不太懂，那就一直用getWritableDatabase吧。

 SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                ContentValues cv = new ContentValues();
                cv.put("name", str_name);
                cv.put("tel", str_tel);
                db.insert("person",null,cv);

　　查询：

SQLiteDatabase db = personHelper.getWritableDatabase();
                String result = "";
                Cursor cursor = db.rawQuery("select * from person", null);
                cursor.moveToFirst();
                if (cursor.moveToFirst()){
                    do{
                        int id = cursor.getInt(0);
                        String nameString = cursor.getString(1);
                        String telString = cursor.getString(2);
                        result += "id="+id+"    name:"+nameString+"        tel:"+telString+"
";
                        Log.d("sql", nameString);
                        //Log.d("sql", id);
                        Log.d("sql", telString);
                    }while(cursor.moveToNext());                    
                }                
                cursor.close();

　　删除：

SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                db.delete("person", "name=? and tel=?", new String[]{str_name,str_tel});

　　　　修改：

SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                ContentValues cv = new ContentValues();
                cv.put("tel", str_tel);
                db.update("person", cv, "name=?", new String[]{str_name});

　　Mainactivity.java

package com.sqlitetest.app;

import Model.Person;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.method.ScrollingMovementMethod;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;


public class MainActivity extends Activity {
    
    Button btn_add = null;
    Button btn_delete = null;
    Button btn_update = null;
    Button btn_search = null;
    EditText edit_name = null;
    EditText edit_tel = null;
    TextView txt_result = null;
    Person personHelper = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        personHelper = new Person(this, "personDB.db", null, 1);//new一个类，注意*.db不能跟类重名
        btn_add = (Button) findViewById(R.id.btn_add);
        btn_delete = (Button) findViewById(R.id.btn_delete);
        btn_search = (Button) findViewById(R.id.btn_search);
        btn_update = (Button) findViewById(R.id.btn_update);
        edit_name = (EditText) findViewById(R.id.edit_name);
        edit_tel = (EditText) findViewById(R.id.edit_number);
        txt_result = (TextView) findViewById(R.id.txt_result);
        txt_result.setMovementMethod(new ScrollingMovementMethod());  //使得内容多时textview可以滚动
        
        btn_add.setOnClickListener(new OnClickListener() {
            
            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                ContentValues cv = new ContentValues();
                cv.put("name", str_name);
                cv.put("tel", str_tel);
                db.insert("person",null,cv);
                Log.d("sql", str_name);
                Log.d("sql", str_tel);
            }
        });
        
        
        btn_search.setOnClickListener(new OnClickListener() {
            
            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                SQLiteDatabase db = personHelper.getWritableDatabase();
                String result = "";
                Cursor cursor = db.rawQuery("select * from person", null);
                cursor.moveToFirst();
                if (cursor.moveToFirst()){
                    do{
                        int id = cursor.getInt(0);
                        String nameString = cursor.getString(1);
                        String telString = cursor.getString(2);
                        result += "id="+id+"    name:"+nameString+"        tel:"+telString+"
";
                        Log.d("sql", nameString);
                        //Log.d("sql", id);
                        Log.d("sql", telString);
                    }while(cursor.moveToNext());                    
                }                
                cursor.close();
                txt_result.setText(result);
            }
        });
        
        btn_delete.setOnClickListener(new OnClickListener() {
            
            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                db.delete("person", "name=? and tel=?", new String[]{str_name,str_tel});
            }
        });
        btn_update.setOnClickListener(new OnClickListener() {
            
            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                SQLiteDatabase db = personHelper.getWritableDatabase();
                String str_name = edit_name.getText().toString();
                String str_tel = edit_tel.getText().toString();
                ContentValues cv = new ContentValues();
                cv.put("tel", str_tel);
                db.update("person", cv, "name=?", new String[]{str_name});
            }
        });
    }
}

activity_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.sqlitetest.app.MainActivity" >

    <TextView
        android:id="@+id/txt_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="20dp"
        android:text="@string/name"
        android:textSize="20sp" />

    <EditText
        android:id="@+id/edit_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/txt_name"
        android:layout_alignBottom="@+id/txt_name"
        android:layout_marginLeft="21dp"
        android:layout_toRightOf="@+id/txt_name"
        android:ems="10"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/edit_number"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/txt_number"
        android:layout_alignLeft="@+id/edit_name"
        android:ems="10"
        android:inputType="number" />

    <TextView
        android:id="@+id/txt_number"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/edit_name"
        android:layout_marginTop="18dp"
        android:layout_toLeftOf="@+id/edit_name"
        android:text="@string/phonenumber"
        android:textSize="20sp" />

    <ScrollView
        android:id="@+id/scrollView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/btn_update"
        android:layout_below="@+id/btn_update" >
    </ScrollView>

    <Button
        android:id="@+id/btn_add"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/edit_number"
        android:layout_below="@+id/edit_number"
        android:layout_marginRight="47dp"
        android:text="@string/add" />

    <Button
        android:id="@+id/btn_delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/btn_update"
        android:layout_toRightOf="@+id/txt_number"
        android:text="@string/delete" />

    <TextView
        android:id="@+id/txt_result"
        android:layout_width="wrap_content"
        android:layout_height="match_parent"
        android:layout_alignTop="@+id/scrollView1"
        android:layout_toRightOf="@+id/txt_number"
        android:maxLines = "1000"//这个滚动要加上去，最大怎么表示不清楚
        android:scrollbars = "vertical"//这个也是滚动必须加的
        android:text="@string/result" />

    <Button
        android:id="@+id/btn_update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/btn_add"
        android:layout_below="@+id/btn_add"
        android:layout_marginTop="16dp"
        android:text="@string/update" />

    <Button
        android:id="@+id/btn_search"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/txt_result"
        android:layout_alignLeft="@+id/txt_result"
        android:text="@string/search" />

</RelativeLayout>