Sqlite简单操作

1.student类

public class Student {
    
    int id;
    String name;
    String sex;
    String address;
    int money;
    
    
    
    public Student(int id, String name, String sex, String address, int money) {
        super();
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.address = address;
        this.money = money;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getMoney() {
        return money;
    }
    public void setMoney(int money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", sex=" + sex
                + ", address=" + address + ", money=" + money + "]";
    }
    
    
    
}

 

2.studentDao

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class StudentDao {
    
    /**
     * 插入数据
     */
    
    public boolean insert(Student stu) {
        SQLiteDatabase db = SQLiteDBHelper.getSqLiteDatabase();
        try {            
            db.execSQL("insert into student (name,sex,address,money) values(?,?,?,?)", 
                    new Object[]{stu.getName(),stu.getSex(),stu.getAddress(),stu.getMoney()});
            db.close();
            return true;
        } catch (Exception e) {
            // TODO: handle exception
        }
        
        return false;
    }
    /**
     * 删除数据
     * @param student
     */
    public void delete(int id) {
        SQLiteDatabase db = SQLiteDBHelper.getSqLiteDatabase();
        //db.execSQL("delete from student where _id = ?", new Object[]{student.getId()});        
        db.execSQL("delete from student where _id = "+id);
        db.close();
    }
    
    /**
     * 更新数据
     */
    
    public void update(Student stu) {
        SQLiteDatabase db = SQLiteDBHelper.getSqLiteDatabase();
        db.execSQL("update student set name = ?,sex=?,address=?,money=? where _id = ?",
                new Object[]{stu.getName(),stu.getSex(),stu.getAddress(),stu.getMoney(),stu.getId()});
        db.close();
    }
    
    /**
     * 根据Id查询数据
     * @param id
     * @return
     */
    public Student findById(int id) {
        SQLiteDatabase db = SQLiteDBHelper.getSqLiteDatabase();
        Cursor cursor = db.rawQuery("select * from student where _id = ?", new String[]{id+""});
        Student student = null;
        if (cursor.moveToNext()) {
            int _id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String sex = cursor.getString(cursor.getColumnIndex("sex"));
            String address = cursor.getString(cursor.getColumnIndex("address"));
            int money = cursor.getInt(cursor.getColumnIndex("money"));
            student = new Student(_id, name, sex, address, money);
        }
        return student;
    }
    
    /**
     * 查询全部数据
     * @return
     */
    public List<Student> findAll() {        
        List<Student> list = new ArrayList<Student>();
        SQLiteDatabase db = SQLiteDBHelper.getSqLiteDatabase();
        
        Cursor cursor = db.rawQuery("select * from student", null);
        Student student = null;
        while (cursor.moveToNext()) {
            int _id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String sex = cursor.getString(cursor.getColumnIndex("sex"));
            String address = cursor.getString(cursor.getColumnIndex("address"));
            int money = cursor.getInt(cursor.getColumnIndex("money"));
            student = new Student(_id, name, sex, address, money);
            list.add(student);
        }
        return list;
    }

}

3.SqliteDbHelper

import java.io.File;

import android.database.sqlite.SQLiteDatabase;

import com.qf.day14_sqlitedatabase02.SDcardHelper;

public class SQLiteDBHelper {
    private static String DB_PATH = SDcardHelper.getSDCardPath();
    private static String DB_NAME = "student.db";
    
    static {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH+File.separator+DB_NAME, null);
        db.execSQL("create table if not exists student(_id integer primary key autoincrement," +
                "name varchar(20)," +
                "sex varchar(4)," +
                "address varchar(100)," +
                "money integer)");
        db.close();
    }
    
    public static SQLiteDatabase getSqLiteDatabase() {
        return SQLiteDatabase.openOrCreateDatabase(DB_PATH+File.separator+DB_NAME, null);
    }
    
}

4.SDcardHelper

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import android.os.Environment;
import android.os.StatFs;

public class SDcardHelper {
    /**
     * 判断Sdcard是否可用
     */
    
    public static boolean isSDcardMounted() {
        boolean state = Environment.getExternalStorageState().
                equals(Environment.MEDIA_MOUNTED);        
        return state;
    }
    
    /**
     * 获取SDcard目录
     * @return
     */
    public static String getSDCardPath() {
        String path = Environment.getExternalStorageDirectory().getAbsolutePath();
        return path;
    }
    
    /**
     * 返回总大小
     * @return
     */
    public static long getSDCardSize() {
        if (isSDcardMounted()) {
            //
            StatFs sf = new StatFs(getSDCardPath());
            long count = sf.getBlockCount();
            long size = sf.getBlockSize();
            return count * size;
        }
        return 0;
    }
    
    /**
     * 返回剩余空间
     */
    
    public static long getSDCardFreeSize() {
        if (isSDcardMounted()) {
            StatFs sFs = new StatFs(getSDCardPath());
            long count = sFs.getFreeBlocks();
            long size = sFs.getBlockSize();
            return count * size;
        }
        return 0;
    }
    
    /**
     * 获取可用空间
     */
    
    public static long getSDCardAvailableSize() {
        if (isSDcardMounted()) {
            StatFs sFs = new StatFs(getSDCardPath());
            long count = sFs.getAvailableBlocks();
            long size = sFs.getBlockSize();
            return count * size;
        }
        return 0;
    }
    
    /**
     * 
     * @param data  保存的数据
     * @param dir  保存的文件或者目录
     * @param filename 保存的文件名
     * @return
     */
    public static boolean saveFileToSDCard(byte[] data, String dir,String filename) {
        if (isSDcardMounted()) {//判断Sdcard是否可用
            File filedir = new File(getSDCardPath() + File.separator + dir);
            if (!filedir.exists()) {//是否存在目录，不存在则创建
                filedir.mkdirs();
            }
            
            if (getSDCardAvailableSize() >= data.length) {//判断空间是否够用                
                FileOutputStream fos = null;
                try {
                    fos = new FileOutputStream(new File(filedir+File.separator+filename));
                    fos.write(data);
                    fos.flush();
                    return true;
                } catch (FileNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                finally {
                    if (fos != null) {
                        try {
                            fos.close();
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    
        return false;
    }

    public static byte[] readFileFromSDCard(String filepath)    {
        if (isSDcardMounted()) {
            File file = new File(filepath);
            ByteArrayOutputStream  byteArrayOutputStream = null;
            if (file.exists()) {
                FileInputStream fileInputStream = null;
                try {
                    fileInputStream = new FileInputStream(file);
                    byteArrayOutputStream = new ByteArrayOutputStream();
                    byte[] buffer = new byte[1024];
                    int length = 0;
                    while ((length = fileInputStream.read(buffer)) != -1) {
                        byteArrayOutputStream.write(buffer, 0, length);
                    }
                    
                    return byteArrayOutputStream.toByteArray();
                } catch (FileNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } finally {
                    if (fileInputStream != null) {
                        try {
                            fileInputStream.close();
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                
                
            }
        }
        return null;
    }
}

5.mainActivity

import java.util.List;

import com.qf.day14_sqlitedatabase02.bean.Student;
import com.qf.day14_sqlitedatabase02.dao.StudentDao;

import android.app.Activity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends Activity {
    StudentDao studentDao = new StudentDao();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }
    /**
     * 数据库的Id从1开始，不是下标
     * @param v
     */
    public void insert(View v) {
        studentDao.insert(new Student(2, "老冯", "男", "航海路阳光城", 30000000));
        studentDao.insert(new Student(2, "老魏", "男", "东建材", 30000));
        studentDao.insert(new Student(2, "老欧", "男", "西海岸", 900000));
        studentDao.insert(new Student(2, "萍姐", "女", "农业东路中州大道", 100000));
        studentDao.insert(new Student(2, "和珅", "男", "清朝紫荆城", 9000000));
        
    }
    public void delete(View v) {
        studentDao.delete(1);
       }
    public void update(View v) {
        studentDao.update(new Student(3, "标哥", "男", "南京", 10));
       }
    public void findId(View v) {
        Student student = studentDao.findById(4);
        Toast.makeText(this, student.toString(), Toast.LENGTH_SHORT).show();
       }
    public void findAll(View v) {
        List<Student> list = studentDao.findAll();
        Toast.makeText(this, list.toString(), Toast.LENGTH_SHORT).show();
       }
}

 6

public class SQLiteOpenHelperUtil extends SQLiteOpenHelper {

    private static final String DB_NAME = "data_student.db";
    private static final String TABLE_NAME = "student";
    private static final int DB_VERSION = 4;
    private SQLiteDatabase mReadDatBase = null;
    private SQLiteDatabase mWriteDataBase = null;
    private static SQLiteOpenHelperUtil singleton;


    private SQLiteOpenHelperUtil(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public static SQLiteOpenHelperUtil getSingleton(Context context) {
        if (singleton == null) {
            synchronized (SQLiteOpenHelperUtil.class) {
                if (singleton == null) {
                    singleton = new SQLiteOpenHelperUtil(context,DB_NAME,null,DB_VERSION);
                }
            }
        }
        return singleton;
    }

    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mWriteDataBase ==null || !mWriteDataBase.isOpen()) {
            mWriteDataBase = singleton.getReadableDatabase();
        }
        return mWriteDataBase;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mReadDatBase ==null || !mReadDatBase.isOpen()) {
            mReadDatBase = singleton.getWritableDatabase();
        }
        return mReadDatBase;
    }


    public void closeLink() {
        if (mReadDatBase != null && mReadDatBase.isOpen()) {
            mReadDatBase.close();
            mReadDatBase = null;
        }

        if (mWriteDataBase != null && mWriteDataBase.isOpen()) {
            mWriteDataBase.close();
            mWriteDataBase = null;
        }
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("打印日志: 创建数据库和表");
        db.execSQL("create table if not exists "+ TABLE_NAME + " (_id integer primary key autoincrement," +
                "name varchar(20)," +
                "sex varchar(4)," +
                "address varchar(100)," +
                "money integer)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.d("升级:", "onUpgrade:  "+ oldVersion +"-->" + newVersion);

        if(newVersion == 4){
            String sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN classname VARCHAR;";
            db.execSQL(sql);
            sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN password VARCHAR";
            db.execSQL(sql);

        }



    }
}

　　

public class StudentDao {

    private SQLiteDatabase sqLiteDatabaseWrite;
    private SQLiteDatabase sqLiteDatabaseRead;

    public StudentDao(SQLiteDatabase sqLiteDatabaseWrite,SQLiteDatabase readDataBase) {
        this.sqLiteDatabaseWrite = sqLiteDatabaseWrite;
        this.sqLiteDatabaseRead = readDataBase;
    }

    public boolean insert(Student stu) {
        SQLiteDatabase db = sqLiteDatabaseWrite;
        try {
            db.execSQL("insert into student (name,sex,address,money,classname,password) values(?,?,?,?,?,?)",
                    new Object[]{stu.getName(),stu.getSex(),stu.getAddress(),stu.getMoney(),stu.getClassName(),stu.getPassword()});
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return false;
    }


    public void delete(int id) {
        SQLiteDatabase db = sqLiteDatabaseWrite;
        db.execSQL("delete from student where _id = "+id);
    }

    public void update(Student stu) {
        SQLiteDatabase db = sqLiteDatabaseWrite;
        db.execSQL("update student set name = ?,sex=?,address=?,money=? where _id = ?",
                new Object[]{stu.getName(),stu.getSex(),stu.getAddress(),stu.getMoney(),stu.getId()});
    }

    public Student findById(int id) {
        SQLiteDatabase db = sqLiteDatabaseRead;
        Cursor cursor = db.rawQuery("select * from student where _id = ?", new String[]{id+""});
        Student student = null;
        if (cursor.moveToNext()) {
            int _id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String sex = cursor.getString(cursor.getColumnIndex("sex"));
            String address = cursor.getString(cursor.getColumnIndex("address"));
            int money = cursor.getInt(cursor.getColumnIndex("money"));
            student = new Student(_id, name, sex, address, money);
        }
        return student;
    }

    public List<Student> findAll() {
        List<Student> list = new ArrayList<Student>();
        SQLiteDatabase db = sqLiteDatabaseRead;
        Cursor cursor = db.rawQuery("select * from student", null);
        Student student = null;
        while (cursor.moveToNext()) {
            int _id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String sex = cursor.getString(cursor.getColumnIndex("sex"));
            String address = cursor.getString(cursor.getColumnIndex("address"));
            int money = cursor.getInt(cursor.getColumnIndex("money"));
            String className = cursor.getString(cursor.getColumnIndex("classname"));
            String password = cursor.getString(cursor.getColumnIndex("password"));
            student = new Student(_id, name, sex, address, money).setClassName(className).setPassword(password);
            list.add(student);
        }
        return list;
    }

}

　　

var sqLiteOpenHelperUtil = SQLiteOpenHelperUtil.getSingleton(this)
        var readDataBase = sqLiteOpenHelperUtil.openReadLink()
        var writeDataBase = sqLiteOpenHelperUtil.openWriteLink()
        var studentDao = StudentDao(writeDataBase, readDataBase)
        button5.setOnClickListener {
            studentDao.insert(Student(1, "gxw", "男", "四川", 1111).setClassName("一班"))
            studentDao.insert(Student(1, "gxw2", "女", "四川", 2222).setClassName("2班"))
        }

        button6.setOnClickListener {
            var list = studentDao.findAll()
            list.forEach { println(it.toString()) }
        }