Slide 14.14: CommentsDataSource.java (cont.)

CommentsDataSource.java (Cont.)

Cursor cursor = database.query( MySQLiteHelper.TABLE_COMMENTS, allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null );

The query(table,columns,selection,args,group,having,order) method queries the given table and returns a Cursor over the result set. The method has the following parameters:

table, which is the table name to compile the query against

columns, which is a list of which columns to return. Passing null will return all columns.

selection, which is a filter declaring which rows to return, formatted as an SQL WHERE clause. Passing null will return all rows for the given table.,

args: You may include ?s in selection, which will be replaced by the values from args, in order that they appear in the selection. The values will be bound as Strings.

group, which is a filter declaring how to group rows, formatted as an SQL GROUP BY clause. Passing null will cause the rows to not be grouped.

having, which is a filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause. Passing null will cause all row groups to be included, and is required when row grouping is not being used.

order, which is to order the rows, formatted as an SQL ORDER BY clause. Passing null will use the default sort order.

The result SQL command is as follows:

   select * from  comments  where  _id = id;

src/main/java/com/example/wenchen/sqlitedemo/CommentsDataSource.java

package com.example.wenchen.sqlitedemo;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CommentsDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  private String[ ] allColumns = { MySQLiteHelper.COLUMN_ID,
    MySQLiteHelper.COLUMN_COMMENT };

  public CommentsDataSource( Context context ) {
    dbHelper = new MySQLiteHelper( context );
  }

  public void open( ) throws SQLException {
    database = dbHelper.getWritableDatabase( );
  }

  public void close( ) {
    dbHelper.close( );
  }

  public Comment createComment( String comment ) {
    // insert into  comments  values( 'Very nice' );
    ContentValues values = new ContentValues( );
    values.put( MySQLiteHelper.COLUMN_COMMENT, comment );
    long insertId = database.insert( MySQLiteHelper.TABLE_COMMENTS, null, values );

    // select * from  comments  where  _id = insertId;
    Cursor cursor = database.query( MySQLiteHelper.TABLE_COMMENTS, allColumns,
      MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null );
    cursor.moveToFirst( );
    return cursorToComment( cursor );
  }

  public void deleteComment( Comment comment ) {
    // delete from  comments  where  _id = id;
    long id = comment.getId( );
    System.out.println( "Comment deleted with id: " + id );
    database.delete( MySQLiteHelper.TABLE_COMMENTS,
      MySQLiteHelper.COLUMN_ID + " = " + id, null );
  }

  public List<Comment> getAllComments( ) {
    // select  _id, comment  from  comments;
    List<Comment> comments = new ArrayList<Comment>( );
    Cursor cursor = database.query( MySQLiteHelper.TABLE_COMMENTS,
      allColumns, null, null, null, null, null );
    cursor.moveToFirst( );
    while ( !cursor.isAfterLast( ) ) {
      Comment comment = cursorToComment( cursor );
      comments.add( comment );
      cursor.moveToNext( );
    }
    // Make sure to close the cursor.
    cursor.close( );
    return comments;
  }

  private Comment cursorToComment( Cursor cursor ) {
    Comment comment = new Comment( );
    comment.setId( cursor.getLong( 0 ) );
    comment.setComment( cursor.getString( 1 ) );
    return comment;
  }
}