SqlLite

package com.example.zjq.sql_11_5;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;

import java.io.File;

public class MainActivity extends AppCompatActivity {

    private String path="/data/data/com.example.zjq.sql_11_5/database";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate( savedInstanceState );
        setContentView( R.layout.activity_main );

        Button btnCreateDB=findViewById( R.id.btn_creat_db );
        Button btnCreateTable=findViewById( R.id.btn_creat_table );
        Button btnInsert=findViewById( R.id.btn_insert );
        Button btnInsertSQL=findViewById( R.id.btn_insert_sql );
        Button btnDelete=findViewById( R.id.btn_delete );
        Button btnDeleteSQL=findViewById( R.id.btn_delete_sql );
        Button btnUpdate=findViewById( R.id.btn_update );
        Button btnUpdateSQL=findViewById( R.id.btn_update_sql );
        Button btnSelect=findViewById( R.id.btn_select );
        Button btnSelectSQL=findViewById( R.id.btn_select_sql );
        Button btnTransaction=findViewById( R.id.btn_transaction );

        ButtonListener listener=new ButtonListener();
        btnCreateDB.setOnClickListener( listener );
        btnCreateTable.setOnClickListener( listener );
        btnInsert.setOnClickListener( listener );
        btnInsertSQL.setOnClickListener( listener );
        btnDelete.setOnClickListener( listener );
        btnDeleteSQL.setOnClickListener( listener );
        btnUpdate.setOnClickListener( listener );
        btnUpdateSQL.setOnClickListener( listener );
        btnSelect.setOnClickListener( listener );
        btnSelectSQL.setOnClickListener( listener );
        btnTransaction.setOnClickListener( listener );
    }
    //自定义监听器类
    public  class ButtonListener implements View.OnClickListener{

        @Override
        public void onClick(View v) {
            switch (v.getId()){
                case R.id.btn_creat_db:
                    {
//                        127|generic_x86:/data/data/com.example.zjq.sql_11_5 # cd database
//                        generic_x86:/data/data/com.example.zjq.sql_11_5/database # sqlite3 my.db
//                        SQLite version 3.18.2 2017-07-21 07:56:09
//                        Enter ".help" for usage hints.
//                        sqlite>

                        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                        db.close();
                    }
                    break;
                case R.id.btn_creat_table:
                {

//                    sqlite> .tables  显示当前表

//                    sqlite> .schema company  查看表结构

//                    sqlite>.help   帮助


                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    String sql="CREATE TABLE COMPANY (" +
                            "ID INTEGER PRIMARY KEY AUTOINCREMENT,"
                            +"NAME TEXT NOT NULL,"
                            +"AGE INT NOT NULL," +
                            "ADDRESS CHAR(50)," +
                            "SALARY REAL)";
                    db.execSQL( sql );
                    db.close();

                }


                    break;
                case R.id.btn_insert:
                {

                    //                    sqlite> select * from company;  查看表数据  注意分号

                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    ContentValues values=new ContentValues(  );
                    //set 只能设置一个 add是加
                    values.put( "NAME","HanMeiMei" );
                    values.put( "AGE",19 );
                    values.put( "ADDRESS","HeBtu" );
                    values.put( "SALARY",3500);
                    db.insert("COMPANY",null, values );  //强制为空的字段
                    db.close();
                }
                    break;
                case R.id.btn_insert_sql: {
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path + "/my.db", null );
                    String sql = "INSERT INTO COMPANY(" +
                            "NAME,AGE,ADDRESS,SALARY)" +
                            "VALUES(" +
                            "'SunWuKong',500,'HuaGuoShan',13.48)";
                    db.execSQL( sql );
                    db.close();
                }

                    break;
                case R.id.btn_delete: {
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path + "/my.db", null );

                    String whereClause = "NAME=? ";
                    String[] whereArgs = {"SunWuKong"};
                    db.delete( "COMPANY",whereClause,whereArgs );
                    db.close();
                }

                    break;
                case R.id.btn_delete_sql:
                {
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    String sql="DELETE FROM COMPANY WHERE NAME = 'HanMeiMei'";
                    db.execSQL( sql );
                    db.close();
                }
                    break;
                case R.id.btn_update:{
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    ContentValues values=new ContentValues(  );
                    values.put( "NAME","WuKong" );
                    String whereClause = "NAME=? ";
                    String[] whereArgs = {"SunWuKong"};
                    db.update( "COMPANY",values, whereClause,whereArgs);
                    db.close();
                }
                    break;
                case R.id.btn_update_sql:{
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    String sql="UPDATE  COMPANY SET NAME='HanMei' WHERE NAME='HanMeiMei'";
                    db.execSQL( sql );
                    db.close();
                }

                    break;
                case R.id.btn_select: //需要返回数据
                {

                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    Cursor cursor=db.query(
                            "COMPANY",null,null,
                            null,null,null,null );//select *
                    if (cursor.moveToFirst()){

                        do {
                            //取数据
                            int id=cursor.getInt( cursor.getColumnIndex( "ID" ) );
                            String name=cursor.getString( cursor.getColumnIndex( "NAME" ) );
                            int age=cursor.getInt(cursor.getColumnIndex( "AGE" ) );
                            String address=cursor.getString( cursor.getColumnIndex( "ADDRESS" ) );
                            Double salary= cursor.getDouble( cursor.getColumnIndex( "SALARY" ) );

                            Log.e( "text",id+"|"+name+"|"+age+"|"+address+"|"+salary );

                        }while (cursor.moveToNext());//没有数值时 返回假

                    }

                    db.close();
                }

                    break;
                case R.id.btn_select_sql:{
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    String sql="SELECT * FROM COMPANY "; //where id=? name=?
                    Cursor cursor=db.rawQuery( sql,null ) ; //查询需要的 rawQurey
                    if (cursor.moveToFirst()){

                        do {
                            //取数据
                            int id=cursor.getInt( cursor.getColumnIndex( "ID" ) );
                            String name=cursor.getString( cursor.getColumnIndex( "NAME" ) );
                            int age=cursor.getInt(cursor.getColumnIndex( "AGE" ) );
                            String address=cursor.getString( cursor.getColumnIndex( "ADDRESS" ) );
                            Double salary= cursor.getDouble( cursor.getColumnIndex( "SALARY" ) );

                            Log.e( "text",id+"|"+name+"|"+age+"|"+address+"|"+salary );

                        }while (cursor.moveToNext());//没有数值时 返回假

                    }
                    db.close();

                }
                    break;

                case R.id.btn_transaction:{
                    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase( path+"/my.db", null );
                    db.beginTransaction();
                    try {
                        Cursor cursor=db.query( "COMPANY",new String[]{"SALARY"},"NAME=?",new String[]{"HanMeiMei"},null,null,null );
                        double salary=0.0;
                        if (cursor.moveToFirst()){ //假设只有一条语句
                            salary =cursor.getDouble( cursor.getColumnIndex( "SALARY" ) );
                        }
                        ContentValues values=new ContentValues(  );
                        values.put( "SALARY", salary+2.5);
                        String whereClause = "NAME=? ";
                        String[] whereArgs = {"HanMeiMei"};
                        db.update( "COMPANY",values,whereClause,whereArgs );

                        //事务执行成功,才真正更新
                        db.setTransactionSuccessful();

                    }catch (Exception e){
                        Log.e( "test",e.toString() );
                    }finally {
                        db.endTransaction();
                    }
                    db.close();
                }
                    break;

            }
        }

    }


}