Click here to Skip to main content
15,911,848 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This is my first project in android and i have been struggling for the query and connecting the two tables and i want to get the user information along with the note..



here is the code..

public class NotebookDbAdapter {

    private static final String DATABASE_NAME = "notebook.db";
    private static final int DATABASE_VERSION = 3;


    public static final String USER_TABLE = "users";
    public static final String COLUMN_ID_USER = "_id";
    public static final String COLUMN_EMAIL = "email";
    public static final String COLUMN_PASS = "password";

    public static final String NOTE_TABLE = "note";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_MESSAGE = "message";
    public static final String COLUMN_CATEGORY = "category";
    public static final String COLUMN_DATE = "date";

    private String[] allColumns = {COLUMN_ID, COLUMN_TITLE, COLUMN_MESSAGE, COLUMN_CATEGORY,
            COLUMN_DATE};

    public static final String CREATE_TABLE_NOTE = " create table " + NOTE_TABLE + " ( "
            + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_TITLE + " text not null, "
            + COLUMN_MESSAGE + " text not null, "
            + COLUMN_CATEGORY + " text not null, "
            + COLUMN_DATE + ");";

    public static final String CREATE_TABLE_USERS = " create table " + USER_TABLE + " ( "
            + COLUMN_ID_USER + " integer primary key autoincrement, "
            + COLUMN_EMAIL + " TEXT , "
            + COLUMN_PASS + " TEXT );";

    private SQLiteDatabase sqlDB;
    private Context context;

    private NotebookDbHelper notebookDbHelper;

    public NotebookDbAdapter(Context ctx) {
        context = ctx;
    }

    public NotebookDbAdapter open() throws android.database.SQLException {
        notebookDbHelper = new NotebookDbHelper(context);
        sqlDB = notebookDbHelper.getWritableDatabase();
        return this;
    }

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

    public Note createNote(String title, String message, Note.Category category) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, title);
        values.put(COLUMN_MESSAGE, message);
        values.put(COLUMN_CATEGORY, category.name());
        values.put(COLUMN_DATE, Calendar.getInstance().getTimeInMillis() + "");

        long insertId = sqlDB.insert(NOTE_TABLE, null, values);

        Cursor cursor = sqlDB.query(NOTE_TABLE,
                allColumns, COLUMN_ID + " = " + insertId, null, null, null, null);

        cursor.moveToFirst();
        Note newNote = cursorToNote(cursor);
        cursor.close();
        return newNote;
    }

    public long deleteNote(long idToDelete) {
        return sqlDB.delete(NOTE_TABLE, COLUMN_ID + " = " + idToDelete, null);
    }

    public long updateNote(long idToUpdate, String newTitle, String newMessage, Note.Category newCategory) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, newTitle);
        values.put(COLUMN_MESSAGE, newMessage);
        values.put(COLUMN_CATEGORY, newCategory.name());
        values.put(COLUMN_DATE, Calendar.getInstance().getTimeInMillis() + "");

        return sqlDB.update(NOTE_TABLE, values, COLUMN_ID + " = " + idToUpdate, null);

    }


What I have tried:

I been trying it and still get nothing and get errors... can someone guide to the right path for making my project successful? thank you!
Posted
Updated 17-Sep-16 21:37pm
v5

1 solution

It seems that in the create statement of NOTE_TABLE you're missing the data type for COLUMN_DATE. So you probably should have something like

Java
public static final String CREATE_TABLE_NOTE = " create table " + NOTE_TABLE + " ( "
        + COLUMN_ID + " integer primary key autoincrement, "
        + COLUMN_TITLE + " text not null, "
        + COLUMN_MESSAGE + " text not null, "
        + COLUMN_CATEGORY + " text not null, "
        + COLUMN_DATE + " integer);";



For more information about dates in SQLite, see Datatypes In SQLite Version 3[^]
 
Share this answer
 
Comments
Member 12745794 18-Sep-16 4:34am    
@Mika Wendelius thanks for response.. i have a question.. do you know how to query it by getting the notes along with the user inputted?
Wendelius 18-Sep-16 4:42am    
As far as I can see you don't have a column in note table which tells who has created the note. Say that this column would be named "CreatedById" and would contain the id of the person who created the note, then the SQL could look something like

SELECT *
FROM note n
INNER JOIN users u
ON n.CreatedById = u._id

For more information about joins, have a look at SQLite JOINS[^]
Member 12745794 18-Sep-16 5:26am    
thanks. should i also edit the code for viewing the note? or just query it in SQL?
Wendelius 18-Sep-16 7:35am    
I'm afraid I can't answer that. As far as I can see there is no code related to viewing notes in your question.
Member 12745794 18-Sep-16 4:59am    
using rawQuery?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900