如何从SQLite Android数据库中加载与特定项目关联的数据?

huangapple go评论69阅读模式
英文:

How to load data associated with a specific item from SQLite Android Database?

问题

在点击X学期时,我想要我的应用程序加载与该特定学期相关联的课程,我尝试使用以下代码实现:

public void openSemestersActivity() {
    final Intent semester = new Intent(this, SemesterActivity.class);
    semesterListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            // 这个在没有删除任何内容时有效。如果删除了某些内容,我们需要在位置上再添加+1
            semester.putExtra("semester", db.getSemesterNameString(position + 1));
            Log.d("Semester Name", db.getSemesterNameString(position + 1));
            startActivity(semester);
        }
    });
}

现在,我想要在SemesterActivity中加载与该特定学期相关联的课程,我尝试使用以下代码实现:

// 检索附加信息并确定我们要加载的学期
Intent myIntent = getIntent();
String semester = myIntent.getStringExtra("semester");

// 创建数据库并加载ListView
db = new DataBaseHelperC(this);
myCourses.addAll(db.getAllCoursesForThisSemester(semester));
customCourseAdapter = new CourseAdapter(getApplicationContext(), R.layout.course_row, myCourses);
courseListView.setAdapter(customCourseAdapter);
customCourseAdapter.notifyDataSetChanged();

这是位于我的DatabaseHelper类上的方法,用于获取特定学期的所有课程:

public List<Course> getAllCoursesForThisSemester(String semester) {
    List<Course> courses = new ArrayList<>();

    // 选择所有查询
    String selectQuery = "SELECT * FROM " + Course.TABLE_NAME + " ORDER BY " + Course.COLUMN_ID + " ASC";

    SQLiteDatabase db = this.getWritableDatabase();
    @SuppressLint("Recycle") Cursor cursor = db.rawQuery(selectQuery, null);

    // 遍历所有行并添加到列表
    if (cursor.moveToFirst()) {
        do {
            Course course = new Course();
            course.setNameOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSE)));
            course.setCodeOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSECODE)));
            course.setCreditsOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSECREDITS)));
            course.setNameOfBackground(cursor.getString(cursor.getColumnIndex(Course.COLUMN_BACKGROUND)));
            course.setId(cursor.getInt(cursor.getColumnIndex(Course.COLUMN_ID)));

            courses.add(course);
        } while (cursor.moveToNext());
    }

    // 关闭数据库连接
    db.close();

    return courses;
}

我将一个String semester作为参数传递,但无法弄清如何实际使用此参数仅获取特定学期的课程。这是我第一次使用数据库,并且值得注意的是,我对此一直感到困惑,还没有完全掌握,因此我已经卡在这里大约两周了。

目前,使用我当前的代码,如果我在X学期中添加了一门课程,然后打开Y学期,X学期中添加的课程也会加载到Y学期中。我正试图通过接受String semester作为参数的方法来修复这个问题。

英文:

On the click of X semester I want my app to load the classes associated with that specific semester, which I'm trying to do with the following piece of code:

public void openSemestersActivity() {
    final Intent semester = new Intent(this, SemesterActivity.class);
    semesterListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView&lt;?&gt; parent, View view, int position, long id) {
            // This works if nothing is deleted. If something is deleted we would have to add another +1 to the position
            semester.putExtra(&quot;semester&quot;, db.getSemesterNameString(position + 1));
            Log.d(&quot;Semester Name&quot;, db.getSemesterNameString(position + 1));
            startActivity(semester);
        }
    });
}

Now, I want to load the classes associated with that specific semester on the SemesterActivity, which I'm trying to do with the following code:

// Retrieving the Extra and determining the semester we want to load
Intent myIntent = getIntent();
String semester = myIntent.getStringExtra(&quot;semester&quot;);

// Creating the Database and loading the ListView
db = new DataBaseHelperC(this);
myCourses.addAll(db.getAllCoursesForThisSemester(semester));
customCourseAdapter = new CourseAdapter(getApplicationContext(), R.layout.course_row, myCourses);
courseListView.setAdapter(customCourseAdapter);
customCourseAdapter.notifyDataSetChanged(); 

This is the method, located on my DatabaseHelper class, that's supposed to get all the courses for that specific semester:

public List&lt;Course&gt; getAllCoursesForThisSemester(String semester) {
        List&lt;Course&gt; courses = new ArrayList&lt;&gt;();

        // Select all query
        String selectQuery = &quot;SELECT * FROM &quot; + Course.TABLE_NAME + &quot; ORDER BY &quot; + Course.COLUMN_ID + &quot; ASC&quot;;

        SQLiteDatabase db = this.getWritableDatabase();
        @SuppressLint(&quot;Recycle&quot;) Cursor cursor = db.rawQuery(selectQuery, null);

        // Looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Course course = new Course();
                course.setNameOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSE)));
                course.setCodeOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSECODE)));
                course.setCreditsOfCourse(cursor.getString(cursor.getColumnIndex(Course.COLUMN_COURSECREDITS)));
                course.setNameOfBackground(cursor.getString(cursor.getColumnIndex(Course.COLUMN_BACKGROUND)));
                course.setId(cursor.getInt(cursor.getColumnIndex(Course.COLUMN_ID)));

                courses.add(course);
            }
            while (cursor.moveToNext());
        }

        // Close db connection
        db.close();

        return courses;
    }

I'm passing a String semester as parameter, but can't figure out how to actually use this parameter to only get the courses for that specific semester. This is my first time working with a Database and it is worth noting that I'm having a hard time with it and haven't gotten the hang of it just yet so any help would be really appreciated since I've been stuck here for around 2 weeks now.

Right now, by using the code that I currently have, if I add a course in X semester and then open Y semester, the courses added on X semester are loaded on the Y semester too. This is what I'm trying to fix with that method that receives a String semester as parameter.

答案1

得分: 2

以下是要翻译的内容:

The sql statement:

SELECT * FROM &quot; + Course.TABLE_NAME + &quot; ORDER BY &quot; + Course.COLUMN_ID + &quot; ASC&quot;

needs a WHERE clause.<br/>

If the column containing the semester has a name like semester, you can do it like this:

String selectQuery = &quot;SELECT * FROM &quot; + Course.TABLE_NAME + 
                     &quot; WHERE semester = ?&quot; + // replace with the actual column name
                     &quot; ORDER BY &quot; + Course.COLUMN_ID + &quot; ASC&quot;;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, new String[] {semester});
英文:

The sql statement:

SELECT * FROM &quot; + Course.TABLE_NAME + &quot; ORDER BY &quot; + Course.COLUMN_ID + &quot; ASC&quot;

needs a WHERE clause.<br/>

If the column containing the semester has a name like semester, you can do it like this:

String selectQuery = &quot;SELECT * FROM &quot; + Course.TABLE_NAME + 
                     &quot; WHERE semester = ?&quot; + // replace with the actual column name
                     &quot; ORDER BY &quot; + Course.COLUMN_ID + &quot; ASC&quot;;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, new String[] {semester});

huangapple
  • 本文由 发表于 2020年8月6日 02:59:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/63271860.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定