第二阶段冲刺第五天

1， 昨天的成就：

　　a)       完成的任务：连接数据库

　　b)       所花时间：20h

　　c)        还剩余0h

2， 遇到的困难：

a)     加入课程时出现错误，加不进去

3， 今天的任务：将加入课程实现。

package com.mnnyang.gzuclassschedule.data.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.annotation.NonNull;

import com.mnnyang.gzuclassschedule.app.app;
import com.mnnyang.gzuclassschedule.data.bean.Course;
import com.mnnyang.gzuclassschedule.data.bean.CsItem;
import com.mnnyang.gzuclassschedule.data.bean.CsName;
import com.mnnyang.gzuclassschedule.utils.LogUtil;

import java.util.ArrayList;

/**
 * Created by mnnyang on 17-11-1.
 */

public class CourseDbDao {

    private CourseDbDao() {
    }

    public boolean removeByCsName(String csName) {
        return removeByCsNameId(getCsNameId(csName));
    }

    private static final class Holder {
        private static final CourseDbDao DAO = new CourseDbDao();
    }

    public static CourseDbDao instance() {
        return Holder.DAO;
    }

    /**
     * 添加课程<br>
     * 应检查课程信息的准确性后再调用该方法 <br>
     *
     * @return success return null or return conflict object
     */
    public Course addCourse(Course course) {
        Course conflictCourse = hasConflictCourse(course);
        if (null != conflictCourse) {
            LogUtil.e(this, "有冲突");
            return conflictCourse;
        }

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        ContentValues values = new ContentValues();

        putAllNotId(course, values);

        db.beginTransaction();
        try {
            long courseId = db.insert(CoursesPsc.CourseEntry.TABLE_NAME, null, values);

            for (Integer integer : course.getNodes()) {
                values.clear();
                values.put(CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID, courseId);
                values.put(CoursesPsc.NodeEntry.COLUMN_NAME_NODE_NUM, integer);
                db.insert(CoursesPsc.NodeEntry.TABLE_NAME, null, values);
            }
            db.setTransactionSuccessful();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }

        db.close();
        return null;
    }

    /**
     * 更新
     */
    public Course updateCourse(Course course) {

        Course conflictCourse = hasConflictCourse(course);
        if (null != conflictCourse) {
            return conflictCourse;
        }

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        db.beginTransaction();

        try {

//            course.setCsNameId()

            ContentValues values = new ContentValues();
            putAllNotId(course, values);
            db.update(CoursesPsc.CourseEntry.TABLE_NAME,
                    values,
                    CoursesPsc.CourseEntry.COLUMN_NAME_COURSE_ID + "=?",
                    new String[]{course.getCourseId() + ""});

            deleteNodeByCourseId(course.getCourseId(), db);
            for (Integer integer : course.getNodes()) {
                values.clear();
                values.put(CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID, course.getCourseId());
                values.put(CoursesPsc.NodeEntry.COLUMN_NAME_NODE_NUM, integer);
                db.insert(CoursesPsc.NodeEntry.TABLE_NAME, null, values);
            }

            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
            db.close();
        }
        return null;
    }

    public boolean removeByCsNameId(int id) {

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        try {
            db.beginTransaction();
            db.delete(CoursesPsc.CourseEntry.TABLE_NAME,
                    CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID + "=?"
                    , new String[]{String.valueOf(id)});

            db.delete(CoursesPsc.CsNameEntry.TABLE_NAME,
                    CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID + "=?",
                    new String[]{String.valueOf(id)});

            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
            db.close();
        }
        return false;
    }

    public void removeCourse(int courseId) {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        removeCourse(courseId, db);
        db.close();
    }

    //TODO ~~~~~~
    private void removeCourse(int courseId, SQLiteDatabase db) {
        db.delete(CoursesPsc.CourseEntry.TABLE_NAME,
                CoursesPsc.CourseEntry.COLUMN_NAME_COURSE_ID + "=?",
                new String[]{courseId + ""});
        deleteNodeByCourseId(courseId, db);
    }

    public boolean removeAllData() {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        try {
            db.beginTransaction();
            db.delete(CoursesPsc.NodeEntry.TABLE_NAME, null, null);
            db.delete(CoursesPsc.CsNameEntry.TABLE_NAME, null, null);
            db.delete(CoursesPsc.CourseEntry.TABLE_NAME, null, null);
            db.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            db.endTransaction();
            db.close();
        }
    }

    private void deleteNodeByCourseId(int courseId, SQLiteDatabase db) {
        db.delete(CoursesPsc.NodeEntry.TABLE_NAME,
                CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID + "=?",
                new String[]{courseId + ""});
    }

    /**
     * 课程冲突判断<br>
     *
     * @param course must be have csName
     */
    private Course hasConflictCourse(Course course) {

        LogUtil.e(this,"冲突检查:-->"+course.toString());
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();

        int csNameId = getCsNameId(course.getCsName(), db);
        course.setCsNameId(csNameId);

        String sql = "select * from " + CoursesPsc.CourseEntry.TABLE_NAME
                + " WHERE " + CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID + "='" + course.getCsNameId() + "'"
                + " AND " + CoursesPsc.CourseEntry.COLUMN_NAME_COURSE_ID + "!='" + course.getCourseId() + "'";
        Cursor cursor = db.rawQuery(sql, null);

        while (cursor.moveToNext()) {
            Course conflictCourse = parse(cursor);
            LogUtil.e(this,"疑是冲突:-->"+course.toString());

            sql = "select * from " + CoursesPsc.NodeEntry.TABLE_NAME
                    + " where " + CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID + "=" + conflictCourse.getCourseId();
            Cursor nodeCursor = db.rawQuery(sql, null);

            while (nodeCursor.moveToNext()) {
                conflictCourse.addNode(nodeCursor.getInt(nodeCursor.getColumnIndex(CoursesPsc.NodeEntry.COLUMN_NAME_NODE_NUM)));
            }

            nodeCursor.close();

            if (course.equals(conflictCourse)) {
                LogUtil.e(this, course.getName() + " 和 " + conflictCourse.toString() + "冲突!!");
                cursor.close();
                db.close();
                return conflictCourse;
            }
        }
        cursor.close();
        db.close();
        return null;
    }

    /**
     * 课程表名称冲突
     *
     * @param csName
     * @return
     */
    public boolean hasConflictCourseTableName(String csName) {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        String sql = "select * from " + CoursesPsc.CsNameEntry.TABLE_NAME
                + " where `" + CoursesPsc.CsNameEntry.COLUMN_NAME_NAME + "`='" + csName + "'";
        System.out.println(sql);
        Cursor cursor = db.rawQuery(sql, null);

        return cursor.moveToNext();
    }

    /**
     * 根据课程表名获取课程表名id 不存在则插入
     */
    public int getCsNameId(String csName) {
        LogUtil.w(this, "在获取课表名:" + csName + "的id");

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        int id = getCsNameId(csName, db);

        db.close();
        return id;
    }

    public int getCsNameId(String csName, SQLiteDatabase db) {
        String sql = "select * from " + CoursesPsc.CsNameEntry.TABLE_NAME
                + " where `" + CoursesPsc.CsNameEntry.COLUMN_NAME_NAME + "`='" + csName + "'";
        LogUtil.i(this, sql);
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID));
            cursor.close();
            return id;
        } else {
            ContentValues values = new ContentValues();
            values.put(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME, csName);
            return (int) db.insert(CoursesPsc.CsNameEntry.TABLE_NAME, null, values);
        }
    }

    /**
     * @param csNameId
     * @param newCsName
     * @return conflict return 0
     */
    public int updateCsName(int csNameId, String newCsName) {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();

        String sql = "select * from " + CoursesPsc.CsNameEntry.TABLE_NAME
                + " where `" + CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID + "`!=" + csNameId
                + " and `" + CoursesPsc.CsNameEntry.COLUMN_NAME_NAME + "`='" + newCsName + "'";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToNext()) {
            cursor.close();
            db.close();
            return 0;
        }
        cursor.close();

        ContentValues values = new ContentValues();
        values.put(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME, newCsName);
        int update = db.update(CoursesPsc.CsNameEntry.TABLE_NAME, values,
                CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID + "=?",
                new String[]{String.valueOf(csNameId)});

        db.close();
        return update;
    }

    public String getCsName(int csNameId) {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        String sql = "select * from " + CoursesPsc.CsNameEntry.TABLE_NAME
                + " where `" + CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID + "`='" + csNameId + "'";
        System.out.println(sql);
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToNext()) {
            String name = cursor.getString(cursor.getColumnIndex(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME));

            cursor.close();
            db.close();
            return name;
        }
        return "";
    }


    /**
     * 加载课程数据
     */
    public ArrayList<Course> loadCourses(String csName) {
        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();

        int csNameId = getCsNameId(csName, db);

        db.close();

        return loadCourses(csNameId);
    }

    @NonNull
    public ArrayList<Course> loadCourses(int csNameId) {

        String csName = getCsName(csNameId);

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();

        String sql = "select * from " + CoursesPsc.CourseEntry.TABLE_NAME + " where "
                + CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID + "='" + csNameId + "'";

        Cursor cursor = db.rawQuery(sql, null);

        ArrayList<Course> courses = new ArrayList<>();
        while (cursor.moveToNext()) {
            Course course = parse(cursor);
            course.setCsName(csName);
            courses.add(course);

            sql = "select * from " + CoursesPsc.NodeEntry.TABLE_NAME
                    + " where " + CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID + "=" + course.getCourseId();
            Cursor nodeCursor = db.rawQuery(sql, null);

            while (nodeCursor.moveToNext()) {
                course.addNode(nodeCursor.getInt(nodeCursor.getColumnIndex(CoursesPsc.NodeEntry.COLUMN_NAME_NODE_NUM)));
            }

            nodeCursor.close();
        }
        cursor.close();

        db.close();
        return courses;
    }

    public ArrayList<CsItem> loadCsNameList() {
        ArrayList<CsItem> csItems = new ArrayList<>();

        SQLiteDatabase db = new CourseDbHelper(app.mContext).getWritableDatabase();
        Cursor cursor = db.query(CoursesPsc.CsNameEntry.TABLE_NAME, null, null,
                null, null, null, null);
        while (cursor.moveToNext()) {
            int nameId = cursor.getInt(cursor.getColumnIndex(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID));
            String name = cursor.getString(cursor.getColumnIndex(CoursesPsc.CsNameEntry.COLUMN_NAME_NAME));

            //TODO 额外数据 例如数据的条数

            CsItem csItem = new CsItem();
            csItem.setCsName(new CsName().setName(name).setCsNameId(nameId));
            csItems.add(csItem);
        }
        cursor.close();
        db.close();

        return csItems;
    }


    private void putAllNotId(Course course, ContentValues values) {
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_NAME, course.getName());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_CLASS_ROOM, course.getClassRoom());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID, course.getCsNameId());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_WEEK, course.getWeek());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_START_WEEK, course.getStartWeek());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_END_WEEK, course.getEndWeek());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_TEACHER, course.getTeacher());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_SOURCE, course.getSource());
        values.put(CoursesPsc.CourseEntry.COLUMN_NAME_WEEK_TYPE,course.getWeekType());
    }


    private Course parse(Cursor cursor) {
        Course course = new Course();
        course.setName(cursor.getString(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_NAME)))
                .setClassRoom(cursor.getString(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_CLASS_ROOM)))
                .setTeacher(cursor.getString(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_TEACHER)))
                .setWeek(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_WEEK)))
                .setStartWeek(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_START_WEEK)))
                .setEndWeek(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_END_WEEK)))
                .setSource(cursor.getString(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_SOURCE)))
                .setCsNameId(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID)))
                .setWeekType(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_WEEK_TYPE)))
                .setCourseId(cursor.getInt(cursor.getColumnIndex(CoursesPsc.CourseEntry.COLUMN_NAME_COURSE_ID)));
        return course;
    }
}

　　

package com.mnnyang.gzuclassschedule.data.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.mnnyang.gzuclassschedule.R;
import com.mnnyang.gzuclassschedule.app.app;
import com.mnnyang.gzuclassschedule.utils.Preferences;

/**
 * Created by mnnyang on 17-10-23.
 */

public class CourseDbHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "course.db";

    private static final String TEXT_TYPE = " TEXT";

    private static final String INTEGER_TYPE = " INTEGER";

    private static final String COMMA_SEP = ",";

    private static final String SQL_CREATE_COURSES =
            "CREATE TABLE " + CoursesPsc.CourseEntry.TABLE_NAME + " (" +
                    CoursesPsc.CourseEntry.COLUMN_NAME_COURSE_ID + INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT" + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_CS_NAME_ID + INTEGER_TYPE + COMMA_SEP +

                    CoursesPsc.CourseEntry.COLUMN_NAME_NAME + TEXT_TYPE + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_CLASS_ROOM + TEXT_TYPE + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_TEACHER + TEXT_TYPE + COMMA_SEP +

                    CoursesPsc.CourseEntry.COLUMN_NAME_WEEK + INTEGER_TYPE + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_START_WEEK + INTEGER_TYPE + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_END_WEEK + INTEGER_TYPE + COMMA_SEP +
                    CoursesPsc.CourseEntry.COLUMN_NAME_WEEK_TYPE + INTEGER_TYPE + COMMA_SEP +

                    CoursesPsc.CourseEntry.COLUMN_NAME_SOURCE + TEXT_TYPE +
                    " )";

    private static final String SQL_CREATE_NODE =
            "CREATE TABLE " + CoursesPsc.NodeEntry.TABLE_NAME + " (" +
                    CoursesPsc.NodeEntry.COLUMN_NAME_COURSE_ID + INTEGER_TYPE + COMMA_SEP +
                    CoursesPsc.NodeEntry.COLUMN_NAME_NODE_NUM + INTEGER_TYPE +
                    " )";

    private static final String SQL_CREATE_CS_NAME =
            "CREATE TABLE " + CoursesPsc.CsNameEntry.TABLE_NAME + " (" +
                    CoursesPsc.CsNameEntry.COLUMN_NAME_NAME_ID + INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT" + COMMA_SEP +
                    CoursesPsc.CsNameEntry.COLUMN_NAME_NAME + TEXT_TYPE +
                    " )";