2024/03/26（2024春季）

学习时长：2小时

代码行数：120行

博客数量：2篇

今天主要完成了个人作业有关的定制目标信息的安卓端的本地数据库.

package com.example.myapplication.dphelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.myapplication.pojo.Goals;

import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;

public class GoalsDbHelper extends SQLiteOpenHelper {
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "goal.db";
    private static final String TABLE_NAME = "goals";
    private static GoalsDbHelper goalsDbHelper=null;
    private SQLiteDatabase goalsRDB = null;//读操作
    private SQLiteDatabase goalsWDB = null;//写操作
    public boolean deleteDatabase(Context context) {//删除数据库
        return context.deleteDatabase(DB_NAME);
    }//删除操作

    private GoalsDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql= "create table if not exists " + TABLE_NAME + "(" +
                "id Integer primary key autoincrement not null ," +
                "username varchar(20) not null,"+
                "start_date date unique not null," +
                "end_date date unique not null," +
                "message varchar(200) not null)";
        db.execSQL(sql);
    }

    public static GoalsDbHelper getInstance(Context context) {
        if (goalsDbHelper == null) {
            goalsDbHelper = new GoalsDbHelper(context);
        }
        return goalsDbHelper;
    }

    public SQLiteDatabase openReadLink() {
        if (goalsRDB == null || !goalsRDB.isOpen()) {
            goalsRDB = goalsDbHelper.getReadableDatabase();
        }
        return goalsRDB;
    }

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


    public void close() {
        if (goalsRDB != null || goalsRDB.isOpen()) {
            goalsRDB.close();
            goalsRDB = null;
        }

        if (goalsWDB != null || goalsWDB.isOpen()) {
            goalsWDB.close();
            goalsWDB = null;
        }
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insert(Goals goals)
    {
        ContentValues values=new ContentValues();
        values.put("username",goals.getUsername());
        values.put("start_date", String.valueOf(goals.getStart_date()));
        values.put("end_date", String.valueOf(goals.getEnd_date()));
        values.put("message",goals.getMessage());
        return goalsWDB.insert(TABLE_NAME,null,values);
    }
//    public Goals select(Goals goals)
//    {
//        String condition="username=? and start_date=?";
//        Goals goals2=null;
//        Cursor cursor=goalsRDB.query(TABLE_NAME,null,condition,new String[]{goals.getUsername(), String.valueOf(goals.getStart_date())},null,null,null,null);
//        if(cursor.moveToNext())
//        {
//            Goals goals1=new Goals();
//            goals1.setId(cursor.getInt(0));
//            goals1.setUsername(cursor.getString(1));
//            goals1.setStart_date(LocalDate.parse(cursor.getString(2)));
//            goals1.setEnd_date(LocalDate.parse(cursor.getString(3)));
//            goals1.setMessage(cursor.getString(4));
//            goals2=goals1;
//        }
//        return goals2;
//    }
//    public Integer select(String username, LocalDate today)
//    {
//        String condition="username=? and start_date between ? and ? ";
//        LocalDate start_Date = today.with(TemporalAdjusters.firstDayOfMonth());
//        LocalDate end_Date = today.with(TemporalAdjusters.lastDayOfMonth());
//        System.out.println(start_Date + "       " + end_Date);
//        Cursor cursor=goalsRDB.query(TABLE_NAME, new String[]{"message"}, condition, new String[]{username, String.valueOf(start_Date), String.valueOf(end_Date)}, null, null, null, null);
//        Integer sum=0;
//        while (cursor.moveToNext()) {
//            String[] split = cursor.getString(0).split(";");
//            sum += (split.length);
//        }
//        return sum;
//    }
}