When I create two tables in Android Sqlite, only one works

Question

I’m trying to develop a test app that requires two tables (aluno and disciplina) in class DataHelper. I create two tables, but when testing, only the table Aluno works. In summary: the application has some functionalities (insert student, insert discipline, delete student, among others) and all methods work for the student table, but no method works for the table of the discipline. What can it be?

Below, the class in which tables are created (DataHelper) followed by the classes that manipulate each table, are these tables aluno (AlunoDao) and discipline (DisciplinaDao) respectively and a class that opens and closes the database (Dao):

public class DataHelper extends SQLiteOpenHelper {

    public DataHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      try{
            StringBuilder sbAluno = new StringBuilder();
            StringBuilder sbDisciplina = new StringBuilder();
            sbAluno.append( "CREATE TABLE IF NOT EXISTS [disciplina](\n" +
                    "    [iddisciplina] INT PRIMARY KEY NOT NULL, \n" +
                    "    [nomedisc] TEXT NOT NULL, \n" +
                    "    [tutor] TEXT NOT NULL);");
            db.execSQL(String.valueOf(sbAluno));

            sbDisciplina.append("CREATE TABLE IF NOT EXISTS [aluno](\n" +
                    "    [idaluno] INT PRIMARY KEY NOT NULL, \n" +
                    "    [nome] TEXT NOT NULL, \n" +
                    "    [curso] TEXT NOT NULL);" );
            db.execSQL(String.valueOf(sbDisciplina));

        }catch (Exception e){

        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try{

            StringBuilder sb= new StringBuilder();

            sb.append("DROP TABLE IF EXISTS [aluno];" + "DROP TABLE IF EXISTS [disciplina];" );

            String [] comando = sb.toString().split(";");

            for (int i = 0; i < comando.length ; i++) {
                db.execSQL(comando[i].toLowerCase()); //toLowerCase torna todas as letras minusculas
            }
        }catch (Exception e){
        }

        onCreate(db);

    }

public class DisciplinaDao extends Dao {

    private static final String TABELA = "disciplina";
    private static final String IDDISCIPLINA = "iddisciplina";
    private static final String NOMEDISC = "nomedisc";
    private static final String TUTOR = "tutor";


    public DisciplinaDao(Context context) {
        super(context);
    }

    public void inserirDisciplina(Disciplina disciplina){
        AbrirBanco();
        //
        ContentValues cv = new ContentValues();
        cv.put(IDDISCIPLINA, disciplina.getIddisciplina());
        cv.put(NOMEDISC, disciplina.getNomedisc());
        cv.put(TUTOR, disciplina.getTutor());
        //
        db.insert(TABELA, null, cv);
        //
        FecharBanco();

    }

    public void atualizarDisciplina(Disciplina disciplina){
        AbrirBanco();
        //
        ContentValues cv = new ContentValues();
        //
        String Filtro = "iddisciplina = ?";
        String [] argumentos = { String.valueOf(disciplina.getIddisciplina()) };
        //
        cv.put(NOMEDISC, disciplina.getNomedisc());
        cv.put(TUTOR, disciplina.getTutor());
        //
        db.update(TABELA, cv, Filtro, argumentos);
        //
        FecharBanco();
    }

    public void apagarDisciplina(int iddisciplina){
        AbrirBanco();
        //
        String Filtro = "iddisciplina = ?";
        String [] argumentos = { String.valueOf(iddisciplina) };
        //
        db.delete(TABELA, Filtro, argumentos);
        //
        FecharBanco();
    }

    public Disciplina obterContatoByID(int iddisciplina){
        Disciplina cAux = null;
        //
        AbrirBanco();
        //
        Cursor cursor = null;
        //
        try{
            String [] argumentos = { String.valueOf(iddisciplina) };

            StringBuilder comando = new StringBuilder();
            comando.append(" select * from disciplina where iddisciplina = ? ");

            cursor = db.rawQuery(comando.toString(), argumentos);

            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                cAux = new Disciplina();

                cAux.setIddisciplina(cursor.getInt(cursor.getColumnIndex(IDDISCIPLINA)));
                cAux.setNomedisc(cursor.getString(cursor.getColumnIndex(NOMEDISC)));
                cAux.setTutor(cursor.getString(cursor.getColumnIndex(TUTOR)));
            }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        //
        FecharBanco();
        //
        return cAux;
    }

    public ArrayList<HMAux> obterListaDisciplina(){
        ArrayList<HMAux> dados = new ArrayList<>();
        //
        AbrirBanco();
        //

        Cursor cursor = null;
        //
        try{

            StringBuilder comando = new StringBuilder();
            comando.append(" select iddisciplina, nomedisc from disciplina order by nomedisc ");

            cursor = db.rawQuery(comando.toString(), null);

            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                HMAux hmAux = new HMAux();

                hmAux.put(HMAux.id_disciplina, String.valueOf(cursor.getLong(cursor.getColumnIndex(IDDISCIPLINA))));
                hmAux.put(HMAux.TEXTO_02, cursor.getString(cursor.getColumnIndex(NOMEDISC)));

                dados.add(hmAux);

                }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        //
        FecharBanco();
        //
        return dados;
    }

    public int proximoID(){
        int proId = 0;
        //
        AbrirBanco();
        //
        Cursor cursor = null;
        //
        try{

            StringBuilder comando = new StringBuilder();
            comando.append("select max(iddisciplina)+1 as id from disciplina");

            cursor = db.rawQuery(comando.toString(), null);
            int x = 10;
            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                proId = cursor.getInt(cursor.getColumnIndex("id"));
            }
            if(proId == 0){
                proId = 1 ;
            }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }

        //
        FecharBanco();
        //
        return proId;
    }

}

public class AlunoDao extends Dao {

    private static final String TABELA = "aluno";
    private static final String IDALUNO = "idaluno";
    private static final String NOME = "nome";
    private static final String CURSO = "curso";


    public AlunoDao(Context context) {
        super(context);
    }

    public void inserirAluno(Aluno aluno){
        AbrirBanco();
        //
        ContentValues cv = new ContentValues();
        cv.put(IDALUNO, aluno.getIdaluno());
        cv.put(NOME, aluno.getNome());
        cv.put(CURSO, aluno.getCurso());
        //
        db.insert(TABELA, null, cv);
        //
        FecharBanco();
    }

    public void atualizarAluno(Aluno aluno){
        AbrirBanco();
        //
        ContentValues cv = new ContentValues();
        //
        String Filtro = "idaluno = ?";
        String [] argumentos = { String.valueOf(aluno.getIdaluno()) };
        //
        //cv.put(IDCONTATO, contato.getIdcontato());
        cv.put(NOME, aluno.getNome());
        cv.put(CURSO, aluno.getCurso());
        //
        db.update(TABELA, cv, Filtro, argumentos);
        //
        FecharBanco();
    }

    public void apagarAluno(int idaluno){
        AbrirBanco();
        //
        String Filtro = "idaluno = ?";
        String [] argumentos = { String.valueOf(idaluno) };
        //
        db.delete(TABELA, Filtro, argumentos);
        //
        FecharBanco();
    }

    public Aluno obterAlunoByID(long idaluno){
        Aluno cAux = null;
        //
        AbrirBanco();
        //
        Cursor cursor = null;
        //
        try{
            String [] argumentos = { String.valueOf(idaluno) };

            StringBuilder comando = new StringBuilder();
            comando.append(" select * from aluno where idaluno = ? ");

            cursor = db.rawQuery(comando.toString(), argumentos);

            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                cAux = new Aluno();

                cAux.setIdaluno(cursor.getInt(cursor.getColumnIndex(IDALUNO)));
                cAux.setNome(cursor.getString(cursor.getColumnIndex(NOME)));
                cAux.setCurso(cursor.getString(cursor.getColumnIndex(CURSO)));

            }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        //
        FecharBanco();
        //
        return cAux;
    }

    public ArrayList<HMAux> obterListaAluno(){
        ArrayList<HMAux> dados = new ArrayList<>();
        //
        AbrirBanco();
        //

        Cursor cursor = null;
        //
        try{

            StringBuilder comando = new StringBuilder();
            comando.append(" select idaluno, nome from aluno order by nome ");

            cursor = db.rawQuery(comando.toString(), null);

            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                HMAux hmAux = new HMAux();

                hmAux.put(HMAux.id, String.valueOf(cursor.getLong(cursor.getColumnIndex(IDALUNO))));
                hmAux.put(HMAux.TEXTO_01, cursor.getString(cursor.getColumnIndex(NOME)));

                dados.add(hmAux);

                }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        //
        FecharBanco();
        //
        return dados;
    }

    public int proximoID(){
        int proId = 0;
        //
        AbrirBanco();
        //
        Cursor cursor = null;
        //
        try{

            StringBuilder comando = new StringBuilder();
            comando.append(" select max(idaluno)+1 as id from aluno ");

            cursor = db.rawQuery(comando.toString(), null);

            //avançar os dados se nao conseguir passar pro proximo ele sai do while
            while (cursor.moveToNext()){
                proId = cursor.getInt(cursor.getColumnIndex("id"));
            }
            if(proId == 0){
                proId = 1 ;
            }

        }catch (Exception e){

        }finally {
            if (cursor != null){
                cursor.close();
                cursor = null;
            }
        }

        //
        FecharBanco();
        //
        return proId;
    }

}

public class Dao {

    private Context context;
    protected SQLiteDatabase db;

    public Dao(Context context) {
        this.context = context;
    }

    public void AbrirBanco(){
        DataHelper dataHelper = new DataHelper(
            context,
            Constantes.BANCO,
            null,
            Constantes.VERSAO
        );

        this.db = dataHelper.getWritableDatabase();

    }

    public void FecharBanco(){
        if (db != null){
            db.close();
        }
    }
}

When using class methods AlunoDao everything is normal. However, when using class methods DisciplinaDao, do not work, but also no error. I created the two in the same way and with the same methods. I do not know what can be done. I thank you already.

Answer 1

The problem is in the database version, the solution was to uninstall and install apk, this solved my problem. Link from where the solution was found: stackoverflow.com/questions/21069532/...

Answer 2

Looking at their classes AlunoDao and DisciplinaDao, They are quite equivalent. The very few differences of something that exists in one and not in the other should have no effect (commented lines, blank lines, a int x = 10; which is never used and the name of the method obterContatoByID instead of obterDisciplinaByID).

However, there is a different point that is worth further research:

                hmAux.put(HMAux.id, String.valueOf(cursor.getLong(cursor.getColumnIndex(IDALUNO))));
                hmAux.put(HMAux.TEXTO_01, cursor.getString(cursor.getColumnIndex(NOME)));

                hmAux.put(HMAux.id_disciplina, String.valueOf(cursor.getLong(cursor.getColumnIndex(IDDISCIPLINA))));
                hmAux.put(HMAux.TEXTO_02, cursor.getString(cursor.getColumnIndex(NOMEDISC)));

Is there anything wrong with that TEXTO_01 and TEXTO_02? Or maybe the fact that one wears id and the other id_disciplina?

By the way, why do you return List<HMAux> in both cases instead of List<Aluno> and List<Disciplina>? I’m going to assume this is a mistake and it should be the last two.

The more your code has serious quality issues. The first is that you should use the Try-with-Resources (see in this link how and why).

If you can’t use the Try-with-Resources, at least make sure to close everything in one block finally. However, once you are already using the diamond syntax (<> in the constructor), so you are already using at least Java 7, so you can use Try-with-Resources.

Another problem is that you use StringBuilders without need. For example:

            StringBuilder comando = new StringBuilder();
            comando.append(" select * from disciplina where iddisciplina = ? ");

            cursor = db.rawQuery(comando.toString(), argumentos);

Gee, it would be a lot easier, simpler, more obvious, more direct and more logical if you did that:

            String comando = " select * from disciplina where iddisciplina = ? ";
            cursor = db.rawQuery(comando, argumentos);

This gets even worse in your class DataHelper, where you concatenate the strings to then separate them again and even use a for needlessly.

Another thing I notice is that all of your Daos methods begin with AbrirBanco() and end with FecharBanco(). This is expected, but it also means that the reference db has no use outside the execution of any of these methods. More than that, the db which is used by one of the methods of the Daos has a scope that is born, lives and dies entirely within a single method at a time. This is a strong indication that the use of local variables would be more appropriate.

And please stick to the Java language nomenclature rules. That is, unless you have a very strong and very well justified reason to do different, method names and local variables MUST start with lower case letters.

Eating exceptions (capturing and not doing anything with it afterwards) is bad programming practice. NEVER do this:

        }catch (Exception e){

        }

Since the exception is android.database.SQLException inherits from java.lang.RuntimeException, you do not need to treat it here. However, the code that will make use of your Daos should be able to treat this.

Also, in a class called Aluno, a method getIdaluno() has a somewhat redundant name. The name is getId() is enough. After all, if I am in the class Aluno, a method getId() obviously will only be able to refer to the student id, and therefore it does not need to be called getIdaluno(). The same can be said about other methods and also columns in the database such as nomedisc and iddisciplina for example.

There is also an object-oriented programming principle that says you should rely on abstractions, not implementations. A direct effect of this is that you should always prefer, if possible, to reference interfaces rather than classes that implement these interfaces. Therefore, avoid defining types that are ArrayList<AlgumaCoisa> when List<AlgumaCoisa> already serve.

Another principle of construction says composition is better than inheritance. We can eliminate your heritage in the Daos by separating the contents of the superclass into a new class that is used by Daos instead of being inherited for them. This approach provides a weaker coupling, better encapsulation and better code organization. I put in your superclass’s hatchback Dao in a class ConnectionFactory.

Simplifying all your code, it looks like this:

public class DataHelper extends SQLiteOpenHelper {

    private static final String CREATE_ALUNO = ""
            + "CREATE TABLE IF NOT EXISTS [aluno] (\n" +
            + "    [id] INT PRIMARY KEY NOT NULL, \n" +
            + "    [nome] TEXT NOT NULL, \n" +
            + "    [curso] TEXT NOT NULL);";

    private static final String CREATE_DISCIPLINA = ""
            + "CREATE TABLE IF NOT EXISTS [disciplina] (\n" +
            + "    [id] INT PRIMARY KEY NOT NULL, \n" +
            + "    [nome] TEXT NOT NULL, \n" +
            + "    [tutor] TEXT NOT NULL);";

    private static final String DROP_ALUNO =
            "DROP TABLE IF EXISTS [aluno];";

    private static final String DROP_DISCIPLINA =
            "DROP TABLE IF EXISTS [disciplina];";

    public DataHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_ALUNO);
        db.execSQL(CREATE_DISCIPLINA);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_ALUNO);
        db.execSQL(DROP_DISCIPLINA);
        onCreate(db);
    }
}

public final class ConnectionFactory {

    private final DataHelper dataHelper;

    public ConnectionFactory(Context context) {
        this.dataHelper = new DataHelper(context, Constantes.BANCO, null, Constantes.VERSAO);
    }

    public SQLiteDatabase conectar() throws SQLiteException {
        return dataHelper.getWritableDatabase();
    }
}

public class AlunoDao {

    private static final String TABELA = "aluno";
    private static final String ID = "id";
    private static final String NOME = "nome";
    private static final String CURSO = "curso";

    private static final String FILTRO_ID = "id = ?";

    private static final String SELECT_BY_ID_SQL =
            "SELECT * FROM aluno WHERE id = ?";

    private static final String SELECT_LIST_SQL =
            "SELECT id, nome FROM aluno ORDER BY nome";

    private static final String SELECT_PROXIMO_ID_SQL =
            "SELECT MAX(id) + 1 AS max_id FROM aluno";

    private final ConnectionFactory factory;

    public AlunoDao(ConnectionFactory factory) {
        this.factory = factory;
    }

    public void inserirAluno(Aluno aluno) {
        try (SQLiteDatabase db = factory.conectar()) {
            ContentValues cv = new ContentValues();
            cv.put(IDALUNO, aluno.getId());
            cv.put(NOME, aluno.getNome());
            cv.put(CURSO, aluno.getCurso());
            db.insert(TABELA, null, cv);
        }
    }

    public void atualizarAluno(Aluno aluno) {
        try (SQLiteDatabase db = factory.conectar()) {
            String[] argumentos = { String.valueOf(aluno.getId()) };
            ContentValues cv = new ContentValues();
            cv.put(NOME, aluno.getNome());
            cv.put(CURSO, aluno.getCurso());
            db.update(TABELA, cv, FILTRO_ID, argumentos);
        }
    }

    public void apagarAluno(int idAluno) {
        try (SQLiteDatabase db = factory.conectar()) {
            String[] argumentos = { String.valueOf(idAluno) };
            db.delete(TABELA, FILTRO_ID, argumentos);
        }
    }

    public Aluno obterAlunoByID(long idAluno) {
        String[] argumentos = { String.valueOf(idAluno) };

        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_BY_ID_SQL, argumentos);
        ) {
            if (!cursor.moveToNext()) return null;
            Aluno aux = new Aluno();
            aux.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            aux.setNome(cursor.getString(cursor.getColumnIndex(NOME)));
            aux.setCurso(cursor.getString(cursor.getColumnIndex(CURSO)));
            return aux;
        }
    }

    public List<Aluno> obterListaAluno() {
        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_LIST_SQL, null);
        ) {
            List<Aluno> lista = new ArrayList<>();

            while (cursor.moveToNext()) {
                Aluno aux = new Aluno();
                aux.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                aux.setNome(cursor.getString(cursor.getColumnIndex(NOME)));
                aux.setCurso(cursor.getString(cursor.getColumnIndex(CURSO)));
                lista.add(aux);
            }

            return lista;
        }
    }

    public int proximoID() {
        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_PROXIMO_ID_SQL, null);
        ) {
            if (!cursor.moveToNext()) return 1;
            return cursor.getInt(cursor.getColumnIndex("id_max"));
        }
    }
}

public class DisciplinaDao {

    private static final String TABELA = "disciplina";
    private static final String ID = "id";
    private static final String NOME = "nome";
    private static final String TUTOR = "tutor";

    private static final String FILTRO_ID = "id = ?";

    private static final String SELECT_BY_ID_SQL =
            "SELECT * FROM disciplina WHERE id = ?";

    private static final String SELECT_LIST_HMAUX_SQL =
            "SELECT id, nome FROM disciplina ORDER BY nome";

    private static final String SELECT_PROXIMO_ID_SQL =
            "SELECT MAX(id) + 1 AS max_id FROM disciplina";

    private final ConnectionFactory factory;

    public AlunoDao(ConnectionFactory factory) {
        this.factory = factory;
    }

    public void inserirDisciplina(Disciplina disciplina) {
        try (SQLiteDatabase db = factory.conectar()) {
            ContentValues cv = new ContentValues();
            cv.put(ID, disciplina.getId());
            cv.put(NOME, disciplina.getNome());
            cv.put(TUTOR, disciplina.getTutor());
            db.insert(TABELA, null, cv);
        }
    }

    public void atualizarDisciplina(Disciplina disciplina) {
        try (SQLiteDatabase db = factory.conectar()) {
            String[] argumentos = { String.valueOf(disciplina.getId()) };
            ContentValues cv = new ContentValues();
            cv.put(NOME, disciplina.getNome());
            cv.put(TUTOR, disciplina.getTutor());
            db.update(TABELA, cv, FILTRO_ID, argumentos);
        }
    }

    public void apagarDisciplina(int idDisciplina) {
        try (SQLiteDatabase db = factory.conectar()) {
            String[] argumentos = { String.valueOf(idDisciplina) };
            db.delete(TABELA, FILTRO_ID, argumentos);
        }
    }

    public Disciplina obterDisciplinaByID(long idDisciplina) {
        String[] argumentos = { String.valueOf(idDisciplina) };

        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_BY_ID_SQL, argumentos);
        ) {
            if (!cursor.moveToNext()) return null;
            Disciplina aux = new Disciplina();
            aux.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            aux.setNome(cursor.getString(cursor.getColumnIndex(NOME)));
            aux.setTutor(cursor.getString(cursor.getColumnIndex(TUTOR)));
            return aux;
        }
    }

    public List<Disciplina> obterListaDisciplina() {
        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_LIST_SQL, null);
        ) {
            List<Disciplina> lista = new ArrayList<>();

            while (cursor.moveToNext()) {
                Disciplina aux = new Disciplina();
                aux.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                aux.setNome(cursor.getString(cursor.getColumnIndex(NOME)));
                aux.setTutor(cursor.getString(cursor.getColumnIndex(TUTOR)));
                lista.add(aux);
            }

            return lista;
        }
    }

    public int proximoID() {
        try (
            SQLiteDatabase db = factory.conectar();
            Cursor cursor = db.rawQuery(SELECT_PROXIMO_ID_SQL, null);
        ) {
            if (!cursor.moveToNext()) return 1;
            return cursor.getInt(cursor.getColumnIndex("id_max"));
        }
    }
}

You didn’t specify where you’re using your Daos, but I suspect that might be your big problem. The way you used it, every time you opened a connection, a new instance of DataHelper and a new instance of SQLiteDatabase was created. The way I did, if you use the same instance of ConnectionFactory and (re)use it to instantiate all Daos, this problem will be solved, since a single DataHelper will be created. Note that this solution would not be possible without the elimination of the inheritance.