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;
}
}
}
}
10-12
08-25
01-23