Check Sqlite column and modify the data

Question

Check Sqlite column and modify the data

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a Sqlite :

public class SqliteDatabase extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "DB_NOTAS";
    private static final String TABLE_NOTAS = "NOTAS";
    private static final String TABLE_CATEGORIAS = "CATEGORIAS";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_TITULO = "titulo";
    private static final String COLUMN_NOTAS = "notas";
    private static final String COLUMN_FECHA = "fecha";
    private static final String COLUMN_PERSISTENTE = "persistente";
    private static final String COLUMN_CATEGORIA = "categoria";
    private static final String COLUMN_FAVORITO = "favorito";
    private static final String COLUMN_IMAGEN = "imagen";
    private static final String COLUMN_CREARCATEGORIA = "crearcategoria";


    public SqliteDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_NOTAS_TABLE = "CREATE TABLE " + TABLE_NOTAS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_TITULO + " TEXT," + COLUMN_NOTAS + " TEXT," + COLUMN_FECHA + " TEXT," + COLUMN_PERSISTENTE + " TEXT," + COLUMN_CATEGORIA + " TEXT," + COLUMN_FAVORITO + " TEXT," + COLUMN_IMAGEN + " TEXT" + ")";
        String CREATE_CATEGORIAS_TABLE = "CREATE    TABLE " + TABLE_CATEGORIAS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_CREARCATEGORIA + " TEXT" + ")";

        db.execSQL(CREATE_NOTAS_TABLE);
        db.execSQL(CREATE_CATEGORIAS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTAS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIAS);
        onCreate(db);
    }

    public List<Notas> listNotas() {
        String sql = "select * from " + TABLE_NOTAS;
        SQLiteDatabase db = this.getReadableDatabase();
        List<Notas> storeNotas = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                int id = Integer.parseInt(cursor.getString(0));
                String titulo = cursor.getString(1);
                String notas = cursor.getString(2);
                String fecha = cursor.getString(3);
                String persistente = cursor.getString(4);
                String categoria = cursor.getString(5);
                String favorito = cursor.getString(6);
                String imagen = cursor.getString(7);
                storeNotas.add(new Notas(id, titulo, notas, fecha, persistente, categoria, favorito, imagen));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return storeNotas;
    }

    public void addNotas(Notas notas) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITULO, notas.getTitulo());
        values.put(COLUMN_NOTAS, notas.getNotas());
        values.put(COLUMN_FECHA, notas.getFecha());
        values.put(COLUMN_PERSISTENTE, notas.getPersistente());
        values.put(COLUMN_CATEGORIA, notas.getCategoria());
        values.put(COLUMN_FAVORITO, notas.getFavorito());
        values.put(COLUMN_IMAGEN, notas.getImagen());
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_NOTAS, null, values);
    }

    public void updateNotas(Notas notas) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITULO, notas.getTitulo());
        values.put(COLUMN_NOTAS, notas.getNotas());
        values.put(COLUMN_FECHA, notas.getFecha());
        values.put(COLUMN_PERSISTENTE, notas.getPersistente());
        values.put(COLUMN_CATEGORIA, notas.getCategoria());
        values.put(COLUMN_FAVORITO, notas.getFavorito());
        values.put(COLUMN_IMAGEN, notas.getImagen());
        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_NOTAS, values, COLUMN_ID + "    = ?", new String[]{String.valueOf(notas.getId())});
    }

    public List<Notas> findNotas() {
        List<Notas> lista_notas = new ArrayList<Notas>();
        String query = "Select * FROM " + TABLE_NOTAS + " WHERE " + COLUMN_NOTAS + " = " + "notas";
        SQLiteDatabase db = this.getWritableDatabase();
        Notas mNotas = null;
        Cursor cursor = db.rawQuery(query, null);
        cursor.moveToFirst();

        if (cursor != null && cursor.getCount() > 0) {
            do {
                int id = Integer.parseInt(cursor.getString(0));
                String titulo = cursor.getString(1);
                String notas = cursor.getString(2);
                String fecha = cursor.getString(3);
                String persistente = cursor.getString(4);
                String categoria = cursor.getString(5);
                String favorito = cursor.getString(6);
                String imagen = cursor.getString(7);
                mNotas = new Notas(id, titulo, notas, fecha, persistente, categoria, favorito, imagen);
                lista_notas.add(mNotas);
            } while (cursor.moveToNext());
            db.close();
            cursor.close();
            return lista_notas;
        } else {
            db.close();
            cursor.close();
            return null;
        }

    }

    public void deleteNotas(int id) {
        try {
            SQLiteDatabase db = this.getWritableDatabase();
            db.delete(TABLE_NOTAS, COLUMN_ID + "    = ?", new String[]{String.valueOf(id)});
        } catch (Exception e) {
        }
    }
}

And I need that when you click btn1 check in COLUMN_CATEGORIA the text that you type in et1 and replace all the words that match the ones you type in et2 . How can I do this?

   @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main2);

        ed1 = (EditText) findViewById(R.id.ed1);
        ed2 = (EditText) findViewById(R.id.ed2);
        btn1 = (Button) findViewById(R.id.btn1);
        mDatabase = new SqliteDatabase(Main2Activity.this);


        btn1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

            }
        });

I have tried to do the following:

Method in Sqlite

public void CambiarNotasCategorias(String notaAntigua, String NotaNueva) {
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT " + COLUMN_CATEGORIA + " FROM " + TABLE_NOTAS + " WHERE " + COLUMN_ID + " = ?";
        Cursor cursor = db.rawQuery(sql, new String[]{notaAntigua});
        cursor.moveToFirst();

        String val = notaAntigua;
        ContentValues values = new ContentValues();
        values.put(COLUMN_CATEGORIA, val);
        db.update(TABLE_NOTAS, values, COLUMN_ID + " = ?", new String[]{notaAntigua});
        if (!cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORIA)).equals(NotaNueva)) {
            val = NotaNueva;
            ContentValues values2 = new ContentValues();
            values2.put(COLUMN_CATEGORIA, val);
            db.update(TABLE_NOTAS, values2, COLUMN_ID + " = ?", new String[]{notaAntigua});
        }

        ContentValues values1 = new ContentValues();
        values1.put(COLUMN_CATEGORIA, val);
        db.update(TABLE_NOTAS, values1, COLUMN_ID + " = ?", new String[]{notaAntigua});
    }

And I call it that from Activity

mDatabase.CambiarNotasCategorias(myInt, "nuevanota");

//myInt = texto que tiene ahora
//nuevanota = texto que debe añadir

But I receive the following error:

android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0

java android sqlite

asked by UserNameYo 29.06.2017 в 21:04

source

2 answers

0

Some things:

You should not enter the cursor assuming that it will always or that always there will be something in it. Therefore, instead of:
```
cursor.moveToFirst();
```
you should do something like:
```
if (cursor != null && cursor.moveToFirst()) 
{
    ...
}
```
On the other hand, I would enter a block try ... catch to capture the possible exceptions.
Neither are you closing the resources. So I have implemented it using finally
A short comment on the naming convention . Although, this is less important, in that it does not result in a program error at compile or execution time, it is still important. Respect the naming convention :
- The methods must have the first letter in lowercase, so I have changed it: cambiarNotasCategorias per CambiarNotasCategorias .
- Regarding your variables, when you call the method you pass a variable called myInt ... and you say in comment that it is a string. I, when I saw the name of the variable, thought it was an integer (myInt = myInteger). The other variable that you pass also does not comply with the naming convention for variables. It would then be nuevaNota and not nuevanota . That does not give an error in the code, but it makes it more understandable.

I hope it helps you.

The method would look something like this:

public void cambiarNotasCategorias(String notaAntigua, String NotaNueva) 
{

    SQLiteDatabase db = null;
    Cursor cursor = null;

    try 
    {
        db = this.getReadableDatabase();
        String sql = "SELECT " + COLUMN_CATEGORIA + " FROM " + TABLE_NOTAS + " WHERE " + COLUMN_ID + " = ?";
        cursor = db.rawQuery(sql, new String[]{notaAntigua});

        if (cursor != null && cursor.moveToFirst()) 
        {
            String val = notaAntigua;
            ContentValues values = new ContentValues();
            values.put(COLUMN_CATEGORIA, val);
            db.update(TABLE_NOTAS, values, COLUMN_ID + " = ?", new String[]{notaAntigua});

            if (!cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORIA)).equals(NotaNueva)) 
            {
                val = NotaNueva;
                ContentValues values2 = new ContentValues();
                values2.put(COLUMN_CATEGORIA, val);
                db.update(TABLE_NOTAS, values2, COLUMN_ID + " = ?", new String[]{notaAntigua});
            }

            ContentValues values1 = new ContentValues();
            values1.put(COLUMN_CATEGORIA, val);
            db.update(TABLE_NOTAS, values1, COLUMN_ID + " = ?", new String[]{notaAntigua});

        }

    } 
    catch (final Exception e) 
    {
            // Hacer algo con la execepción (Log, mensaje de error...)
    } 
    finally 
    {
            cursor.close();
            db.close();
    }


}


mDatabase.cambiarNotasCategorias(myInt, nuevaNota);

//myInt = texto que tiene ahora ¿?
//nuevaNota = texto que debe añadir

answered by 30.06.2017 в 01:48

Why does not the function run on the first click in this case? Consume web service from c #

score 1 · Accepted Answer

I leave you one that will update all the rows where the text coincides:

public void CambiarNotasCategorias(String notaAntigua, String NotaNueva) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues data=new ContentValues();
    data.put(COLUMN_CATEGORIAS, NotaNueva);
    db.update(TABLE_NOTAS, data, COLUMN_CATEGORIAS + " = ?", new String[]{notaAntigua});
}