Android之Sqlite，SQLiteOpenHelper的使用

基本定义：

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

基本命令

1.创建一个数据库

sqlite test.db

2.创建表

sqlite>create table mytable(id integer primary key,value text);

3.添加数据

sqlite>insert into mytable(id,value) values(1,'jack');
sqlite>insert into mytable(value) values('Tom');

4.查询数据库

sqlite>select * from test;

5.删除语句

delete from test where id=1

6.排序语句

select * from person 

select * from person order by id desc

7.选择语句
(获取五条记录，从第三条开始)

select  * from test limit 5 offset 3 

8.更新表中字段值

update person set value='小明' where id=1

9.增加一列

alter table test add phone varchar(12)

public class SimpleActivity extends AppCompatActivity {


    @Bind(R.id.button2)
    Button button2;
    @Bind(R.id.button3)
    Button button3;
    @Bind(R.id.button4)
    Button button4;
    @Bind(R.id.button5)
    Button button5;
    @Bind(R.id.button6)
    Button button6;
    SQLiteDatabase sld;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_simple);
        ButterKnife.bind(this);


    }


    @OnClick({R.id.button2, R.id.button3, R.id.button4, R.id.button5, R.id.button6})
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.button2:
                createOrOpenDatabase();
                break;
            case R.id.button3:
                try{
                    sld.close();
                    Toast.makeText(this, "成功关闭数据库", Toast.LENGTH_SHORT).show();
                }catch (Exception e){
                    e.printStackTrace();

                }
                break;
            case R.id.button4:
                insert();

                break;
            case R.id.button5:
                delete();
                break;
            case R.id.button6:
                query();
                break;
        }
    }

    private void query() {
      try{
          String sql = "select * from student where sage>?";
          Cursor cursor = sld.rawQuery(sql, new String[]{"20"});

          while(cursor.moveToNext()){
              String sno = cursor.getString(0);
              String sname = cursor.getString(1);
              int sage = cursor.getInt(2);
              String sclass = cursor.getString(3);

              Toast.makeText(this, "查询记录为："+sno+"\t"+sname+"\t"+sage+"\t"+sclass, Toast.LENGTH_SHORT).show();

          }

          cursor.close();

      }catch (Exception e){
          e.printStackTrace();
      }
    }

    private void delete() {
        try {
            String sql = "delete from student;";
            sld.execSQL(sql);
            Toast.makeText(this, "删除数据库", Toast.LENGTH_SHORT).show();

        }catch (Exception e){
            e.printStackTrace();
        }


    }

    private void insert() {
        try {
            String sql = "insert into student values" + "('001','Android',22,283) ";
            sld.execSQL(sql);
            Toast.makeText(this, "插入数据", Toast.LENGTH_SHORT).show();
        }catch (Exception e){
            e.printStackTrace();
        }

    }



    /**
     * 创建和打开数据库
     */
    private void createOrOpenDatabase() {
        try{
            DatabaseHelper databaseHelper = new DatabaseHelper(this);
            sld = databaseHelper.getWritableDatabase();
            Toast.makeText(this, "成功创建数据库", Toast.LENGTH_SHORT).show();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    class DatabaseHelper extends SQLiteOpenHelper{

        public static final int DB_VERSION = 1;
        public static final String DB_NAME="my.db";

        public DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase sqLiteDatabase) {
            sqLiteDatabase.execSQL("create table if not exists student"+"(sno char(5),stuname varchar(20),sage integer,sclass char(5))");
        }

        @Override
        public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

        }
    }

}

<?xml version="1.0" encoding="utf-8"?>
<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.yang.yin.yinyangapplication.SimpleActivity">


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/create_open"
        android:id="@+id/button2"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/close"
        android:id="@+id/button3"
        android:layout_below="@+id/button2"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/add_data"
        android:id="@+id/button4"
        android:layout_below="@+id/button3"
        android:layout_alignRight="@+id/button3"
        android:layout_alignEnd="@+id/button3" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/del_data"
        android:id="@+id/button5"
        android:layout_below="@+id/button4"
        android:layout_alignRight="@+id/button4"
        android:layout_alignEnd="@+id/button4" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/que_data"
        android:id="@+id/button6"
        android:layout_below="@+id/button5"
        android:layout_alignLeft="@+id/button5"
        android:layout_alignStart="@+id/button5" />
</RelativeLayout>