sqlite数据库学习

2022-10-26 22:03:22

1.0版代码:

package com.swust.sqlitedatabase.test;

import com.swust.sqlitedatabase.myOpenHelper;

import android.database.sqlite.SQLiteDatabase;

import android.test.AndroidTestCase;

public class TestCase extends AndroidTestCase {

    public void test(){

        //第一个参数：this->现在应用没启动，没有，真正的时候用this。所以获取虚拟上下文getContext()

        //第二个参数：数据库文件

        //第四个参数：版本号

        //recNO字段是系统的，无视

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        //如果数据库不存在，先创建数据库，再获取可读可写数据库对象，如果数据存在，就直接打开

        SQLiteDatabase db=moh.getWritableDatabase();

        //创建对象也可读可写,如果存储空间满了，那么返回只读数据库对象

        //SQLiteDatabase db=moh.getReadableDatabase();

    }

    public void insert(){

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        SQLiteDatabase db=moh.getWritableDatabase();

        //数据库char ,double等都是字符串，因为这个轻量级数据不检测数据

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大学",25.34,"12.345"});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大学",25.34,"12.345"});

        db.close();

    }

}

插入后：

sqlite数据库学习

删除：

public void delete(){

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        SQLiteDatabase db=moh.getWritableDatabase();

        db.execSQL("delete from person where name =?",new Object[]{"王**"});

    }

之后，字段名字为“王**”的被删除

效果：

sqlite数据库学习

-------------------------------------

代码未优化：

package com.swust.sqlitedatabase.test;

import com.swust.sqlitedatabase.myOpenHelper;

import android.database.sqlite.SQLiteDatabase;

import android.test.AndroidTestCase;

public class TestCase extends AndroidTestCase {

    public void test(){

        //第一个参数：this->现在应用没启动，没有，真正的时候用this。所以获取虚拟上下文getContext()

        //第二个参数：数据库文件

        //第四个参数：版本号

        //recNO字段是系统的，无视

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        //如果数据库不存在，先创建数据库，再获取可读可写数据库对象，如果数据存在，就直接打开

        SQLiteDatabase db=moh.getWritableDatabase();

        //创建对象也可读可写,如果存储空间满了，那么返回只读数据库对象

        //SQLiteDatabase db=moh.getReadableDatabase();

    }

    public void insert(){

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        SQLiteDatabase db=moh.getWritableDatabase();

        //数据库char ,double等都是字符串，因为这个轻量级数据不检测数据

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**大学",25.34,"12.345"});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王**","25.34",12.345});

        db.execSQL("insert into person(name,salary,phone)values(?,?,?)",new Object[]{"王树青大学",25.34,"12.345"});

        db.close();

    }

    public void delete(){

        myOpenHelper moh = new myOpenHelper(getContext(), "people.db", null, 1);

        SQLiteDatabase db=moh.getWritableDatabase();

        db.execSQL("delete from person where name =?",new Object[]{"王**"});

    }

}

优化后：

package com.itheima.sqlitedatabase.test;

import com.itheima.sqlitedatabase.MyOpenHelper;

import android.content.ContentValues;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.test.AndroidTestCase;

public class TestCase extends AndroidTestCase {

    //此时测试框架还没有初始化完毕，没有虚拟上下文对象

//    private MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);

    private MyOpenHelper oh;

    private SQLiteDatabase db;

    public void test(){

        //getContext():获取一个虚拟的上下文

        MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);

        //如果数据库不存在，先创建数据库，再获取可读可写的数据库对象，如果数据库存在，就直接打开

        SQLiteDatabase db = oh.getWritableDatabase();

        //如果存储空间满了，那么返回只读数据库对象

//        SQLiteDatabase db = oh.getReadableDatabase();

    }

    //测试框架初始化完毕之后，在测试方法执行之前，此方法调用

    @Override

    protected void setUp() throws Exception {

        super.setUp();

        oh = new MyOpenHelper(getContext(), "people.db", null, 1);

        db = oh.getWritableDatabase();

    }

    //测试方法执行完毕之后，此方法调用

    @Override

    protected void tearDown() throws Exception {

        // TODO Auto-generated method stub

        super.tearDown();

        db.close();

    }

    public void insert(){

//        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的老婆[1]", "13000", 138438});

//        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的儿子", 14000, "13888"});

        db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志", 14000, "13888"});

    }

    public void delete(){

        db.execSQL("delete from person where name = ?", new Object[]{"小志"});

    }

    public void update(){

        db.execSQL("update person set phone = ? where name = ?", new Object[]{186666, "小志的儿子"});

    }

    public void select(){

        Cursor cursor = db.rawQuery("select name, salary from person", null);

        while(cursor.moveToNext()){

            //通过列索引获取列的值

            String name = cursor.getString(cursor.getColumnIndex("name"));

            String salary = cursor.getString(1);

            System.out.println(name + ";" + salary);

        }

    }

    public void insertApi(){

        //把要插入的数据全部封装至ContentValues对象

        ContentValues values = new ContentValues();

        values.put("name", "游天龙");

        values.put("phone", "15999");

        values.put("salary", 16000);

        db.insert("person", null, values);

    }

    public void deleteApi(){

        int i = db.delete("person", "name = ? and _id = ?", new String[]{"小志的儿子", "3"});

        System.out.println(i);

    }

    public void updateApi(){

        ContentValues values = new ContentValues();

        values.put("salary", 26000);

        int i = db.update("person", values, "name = ?", new String[]{"游天龙"});

        System.out.println(i);

    }

    public void selectApi(){

        Cursor cursor = db.query("person", null, null, null, null, null, null, null);

        while(cursor.moveToNext()){

            String name = cursor.getString(cursor.getColumnIndex("name"));

            String phone = cursor.getString(cursor.getColumnIndex("phone"));

            String salary = cursor.getString(cursor.getColumnIndex("salary"));

            System.out.println(name + ";" + phone + ";" + salary);

        }

    }

    public void transaction(){

        try{

            //开启事务

            db.beginTransaction();

            ContentValues values = new ContentValues();

            values.put("salary", 12000);

            db.update("person", values, "name = ?", new String[]{"小志"});

            values.clear();

            values.put("salary", 16000);

            db.update("person", values, "name = ?", new String[]{"小志的儿子"});

            int i = 3/0;

            //设置  事务执行成功

            db.setTransactionSuccessful();

        }

        finally{

            //关闭事务，同时提交，如果已经设置事务执行成功，那么sql语句就生效了，反之，sql语句回滚

            db.endTransaction();

        }

    }

}
上一篇：Python之str()与repr()的区别


下一篇：Linux 网络编程 高级套接字