javaandroidandroid-sqlitedatabase-management

Updating an entire row (containing multiple columns) within a sqlite database created


I want to create an update function which will change the entries of the columns (entire row) within the database based on the INTEGER PRIMARY KEY of the row - if at all possible.

I have a find function which will find and load the details of a database into an EditText field for editing/updating.

The find button (which calls the find handler), loads the contents into the EditText fields which can be altered/edited but when a button is pressed to update the edited entry, the entire database overwritten with the altered EditText entry.

Just to clarify, I want to change a single row containing the PORT, NAME and IP address within the database, update that single entry and put it back into the database without overwriting all my database entries- as is currently happening.

I have no way of seeing the database, in its raw unedited form, in the data/data/{package name}... folder as I am using an old Samsung phone (which doesn't contain the database.db for some unknown reason- stating the "run as: Could not set capabilities..." message) for debugging/running the app and the PC I am using does not have enough RAM to support a virtual device.

Any help, advise or examples will be greatly appreciated. I am very new to Android app development and java, so please forgive me if the problem is obvious or trivial.

This is how the Table is created:

 public static final String TABLE_USER = "User";
 public static final String COL_ID = "_id";
 public static final String COLUMN_ID = "UserIP";
 public static final String COLUMN_NAME = "UserName";
 public static final String COLUMN_PORT = "UserPort";

    public void onCreate(SQLiteDatabase db)
     {
            String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " +
            TABLE_USER + "(" + COL_ID + " INTEGER PRIMARY KEY, " + 
                           COLUMN_PORT + " INTEGER, " + COLUMN_NAME
            + " TEXT, " + COLUMN_ID + " TEXT " + ")";

    try
    {
        db.execSQL(CREATE_USER_TABLE);

    }catch (SQLException e)
    {
      e.printStackTrace();
    }

    }

The find function is as follows:

public User findHandler(String username, String IP)
{
    String query = "Select * FROM " + TABLE_USER + " WHERE " +
            COLUMN_NAME + " = '" + username + "'" + " and " + COLUMN_ID + " = 
          '" +  String.valueOf(IP) + "'";

    SQLiteDatabase db = this.getWritableDatabase();

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

    User user = new User();

    if (cursor.moveToFirst())
    {
        cursor.moveToFirst();

        user.setUserPort(Integer.parseInt(cursor.getString(1)));

        user.setUserName(cursor.getString(2));

        user.setID(cursor.getString(3));

        cursor.close();

       //Log.d("Message1", msg);

    }

    else
    {
        user = null;
    }

    db.close();

    return user;
}

In the MainActivity the find button press (onClick) is as follows:

 btnfind.setOnClickListener(new View.OnClickListener()
    {
        public void onClick(View view)
        {
            MyDBHandler dbHandler = new MyDBHandler(getApplicationContext());

            User user = dbHandler.findHandler(username.getText().toString(), 
              userid.getText().toString());


            if (user != null)
            {
                lst.setText(user.getID() + " " + user.getUserName()
                        + " " + String.valueOf(user.getUserPort()));

                //found_id =(user.getWhere());

    // Load into the EditText for editing

                userid.setText(user.getID());                            
                username.setText(user.getUserName());                    
                userport.setText(String.valueOf(user.getUserPort()));    
            }

            else
            {
                 lst.setText("No User Found");
            }
        }
    });

The update function is as follows:

 public boolean updateHandler(int Port, String username, String IP)
{
    SQLiteDatabase db = this.getWritableDatabase();


    ContentValues args = new ContentValues();

    args.put(COLUMN_ID, IP);
    args.put(COLUMN_NAME, username);
    args.put(COLUMN_PORT, Port);


    return db.update(TABLE_USER, args, COL_ID + " = _id " , null) >0;

}

In the MainActivity the update button press (onClick) is as follows:

       btnupdate.setOnClickListener(new View.OnClickListener()
       {
          public void onClick(View view)
          {
             MyDBHandler dbHandler = new             
              MyDBHandler(getApplicationContext());

            if (userport.length() >= 1 && userid.length() >= 1 && 
                                  username.length() >= 1)
            {
                userid.getText();
                username.getText();
                userport.getText();

            boolean result = 

     dbHandler.updateHandler(Integer.parseInt(userport.getText().toString()), 
                 username.getText().toString(), userid.getText().toString());

                if (result)
                {

     dbHandler.updateHandler(Integer.parseInt(userport.getText().toString()), 
                 username.getText().toString(), userid.getText().toString());

                    lst.setText("Record Updated.");

                }else
                {
                    lst.setText("No Match.");
                }

            }else
            {
                Toast.makeText(MainActivity.this, "Please enter a valid 
                                     user." , Toast.LENGTH_LONG).show();
            }

        }
    });

Solution

  • Change COL_ID + " = _id " to COLUMN_ID + " = ?" and change the null argument to an array with the value of IP.