Android 中 SQLite 的应用总结

声明：文章原创，转载需注明出处。由于文章大多是学习过程中的记录内容，技术能力有限，希望大家指出其中错误，共同交流进步。由此原因，文章会不定期改善，看原文最新内容，请到：http://blog.chinaunix.net/uid/29454152.html
通过一个demo程序测试了SQLite的语句应用。里面涉及到，数据库建立，表的建立，表中数据的添加，更改，删除，查询
1. 数据库的建立
    Android中集成了SQLite数据库，但是不回自动提供数据库，所以我们想应用的话第一步是建立自己的数据库。
    首先引入包   import android.database.sqlite.SQLiteDatabase;
    下面一句话初始化数据库，也就是建立自己命名的数据句 
    db = openOrCreateDatabase(dbName,MODE_PRIVATE,null);
2. 建立表
    执行语句是
    db.execSQL("CREATE TABLE IF NOT EXISTS mytable(id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(30), score INTEGER);");
    此语句是通过调用SQLiteDatabase的execSQL()方法，在里面书写SQL语句完成的。
3. 数据的添加、更改、删除
    添加
    db.execSQL("INSERT INTO mytable VALUES('61', 'zhang', '5');");
     更改              
    db.execSQL("UPDATE mytable SET name='pei' WHERE id='61';");
     删除             
     db.execSQL("DELETE FROM mytable WHERE id='61';");
4. 数据的查询
    查询可以通过Android的query方法，此方法会返回一个Cursor对象，通过对Cursor对象的各种操作，可以获得所需数据。
    获取对象方法
     Cursor cursor = db.query(tableName,null,null,null,null,null,null);//后面这一堆参数可缺省
    具体实现在下面JAVA代码的90-108行
5. 代码
    .XML

<RelativeLayout xmlns:android=" />
    xmlns:tools=" android:layout_width="match_parent"
    android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:background="@drawable/b2"
    tools:context=".MainActivity">
    //title
    <TextView android:text="Hello SQLite!!"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/titleTextView"/>
    //create SQLite
    <Button
        android:text="创建数据库"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/initSQL"
        android:layout_below="@+id/titleTextView"
        android:layout_marginTop="20dp"
        android:layout_alignLeft="@+id/titleTextView"
        />
    //create table
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建表"
        android:id="@+id/createTable"
        android:layout_below="@+id/initSQL"
        android:layout_marginTop="20dp"
        android:layout_alignLeft="@+id/titleTextView"
        />

    //Android modification data
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Android修改数据"
        android:id="@+id/AndroidModData"
        android:layout_below="@+id/createTable"
        android:layout_marginTop="20dp"
        android:layout_alignLeft="@+id/titleTextView"
        />

    //query data
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询数据"
        android:id="@+id/queryData"
        android:layout_below="@+id/AndroidModData"
        android:layout_marginTop="20dp"
        android:layout_alignLeft="@+id/initSQL"
        />

    //SQL modification data
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="SQL 修改数据"
        android:id="@+id/SQLModData"
        android:layout_below="@+id/queryData"
        android:layout_marginTop="20dp"
        android:layout_alignLeft="@+id/initSQL"
        />

</RelativeLayout>

    .JAVA

package com.example.warrior.sqliteoperator;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;


public class MainActivity extends Activity {

    private Button initButton;
    private Button tableButton;
    private Button btn_SqlMod;
    private Button btn_AndroidMod;
    private Button btn_Query;
    private final String dbName = "SQLdb";
    private final String tableName = "mytable";
    private SQLiteDatabase db = null;
    private int i = 1;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initButton = (Button)findViewById(R.id.initSQL);
        initButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db = openOrCreateDatabase(dbName,MODE_PRIVATE,null);
                Toast.makeText(getApplicationContext(),"create SQLite Success!!", 1000).show();
            }
        });

        //create table
        tableButton = (Button)findViewById(R.id.createTable);
        tableButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(db != null){
                    db.execSQL("CREATE TABLE IF NOT EXISTS mytable(id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(30), score INTEGER);");
                    Toast.makeText(getApplicationContext(),"create table success!!", 1000).show();
                }else {
                    Toast.makeText(getApplicationContext(),"None SQL", 1000).show();
                }
            }
        });

        //sql modification data
        btn_SqlMod = (Button)findViewById(R.id.SQLModData);
        btn_SqlMod.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(db != null){
                    db.execSQL("INSERT INTO mytable VALUES('61', 'zhang', '5');");
                    Toast.makeText(getApplicationContext(),"ADD SUCCESS!!",1000).show();
                    db.execSQL("UPDATE mytable SET name='pei' WHERE id='61';");
                    Toast.makeText(getApplicationContext(), "UPDATE SUCCESS!!", 1000).show();
                    db.execSQL("DELETE FROM mytable WHERE id='61';");
                    Toast.makeText(getApplicationContext(), "DELETE SUCCESS!!", 1000).show();
                }else {
                    Toast.makeText(getApplicationContext(), "NO SQL", 2000).show();
                }
            }
        });


        btn_Query = (Button)findViewById(R.id.queryData);
        btn_Query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(db != null){
                    queryData();
                    Toast.makeText(getApplicationContext(), "SELECT SUCCESS!!", 2000).show();
                }else {
                    Toast.makeText(getApplicationContext(), "NO SQL", 2000).show();
                }
            }
        });


    }

    public void queryData(){
        Cursor cursor = db.query(tableName,null,null,null,null,null,null);
        String str = "";
        if(cursor.getCount() != 0){
            cursor.moveToFirst();//跳到第一个游标
            for(int i=0;i<cursor.getCount();i= i+1){
                str = str + cursor.getString(0)+""+cursor.getString(1)+""+cursor.getString(2)+"\n";
                cursor.moveToNext();//下一个游标
            }
        }
        new AlertDialog.Builder(MainActivity.this).setTitle("query message!!")
                .setMessage(str)
                .setNegativeButton("yes", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialog, int which) {

                    }
                }).show();
    }


    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.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();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

                        de