I have two tables Materias(discipline) and Grade(grid).
First I call the activity from the table Materias and insert some rows, then I call the Grade activity. It has one reference column from Materias, but when I inner join these tables, I get Fatal Exception saying this message:
SQLiteException: no such table: Materias (code 1): , while compiling: SELECT g.*, c.Descricao FROM Grades g INNER JOIN Materias m ON m.id = g.materia"
My MateriaDAO
public class MateriaDAO extends SQLiteOpenHelper {
public final String tabela = "Materias";
public MateriaDAO(Context context) {
super(context, "Materias", null, 2);
}
@Override
public void onCreate(SQLiteDatabase db) {
String comando = "CREATE TABLE " + tabela + " (";
comando += "id INTEGER PRIMARY KEY,";
comando += "Descricao TEXT,";
comando += "Comentario TEXT,";
comando += "Qtd_Aulas INTEGER,";
comando += "Qtd_Faltas INTEGER)";
db.execSQL(comando);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "DROP TABLE IF EXISTS " + tabela;
db.execSQL(sql);
onCreate(db);
}
public void insert(Materia o) {
try{
ContentValues cv = new ContentValues();
cv.put("Descricao", o.getDescricao());
cv.put("Comentario", o.getComentario());
cv.put("Qtd_Aulas", o.getQtdAula());
cv.put("Qtd_Faltas", o.getQtdAula());
getWritableDatabase().insert(tabela, null, cv);
} catch (Exception ex) {
throw ex;
}
}
public void update(Materia o) {
ContentValues cv = new ContentValues();
cv.put("Descricao", o.getDescricao());
cv.put("Comentario", o.getComentario());
cv.put("Qtd_Aulas", o.getQtdAula());
cv.put("Qtd_Faltas", o.getQtdAula());
String[] args = {o.getID() + ""};
getWritableDatabase().update(tabela, cv, "ID=?", args);
}
public void delete(Materia o) {
String[] args = {o.getID() + ""};
getWritableDatabase().delete(tabela, "ID = ?", args);
}
public Materia preencher(Materia o) {
String comando = "SELECT * FROM " + tabela + " WHERE ID " + o.getID();
Cursor c = getReadableDatabase().rawQuery(comando, null);
if(c.moveToFirst()) {
Materia m = new Materia();
m.setDescricao(c.getString(c.getColumnIndex("Descricao")));
m.setComentario(c.getString(c.getColumnIndex("Comentario")));
m.setQtdAula(Integer.parseInt(c.getString(c.getColumnIndex("Qtd_Aulas"))));
m.setQtd_faltas(Integer.parseInt(c.getString(c.getColumnIndex("Qtd_Faltas"))));
return m;
} else {
return null;
}
}
public List<Materia> listar() {
String comando = "SELECT * FROM " + tabela;
Cursor c = getReadableDatabase().rawQuery(comando, null);
List<Materia> list = new ArrayList<>();
Materia m;
while(c.moveToNext()) {
m = new Materia();
m.setDescricao(c.getString(c.getColumnIndex("Descricao")));
m.setComentario(c.getString(c.getColumnIndex("Comentario")));
m.setQtdAula(Integer.parseInt(c.getString(c.getColumnIndex("Qtd_Aulas"))));
m.setQtd_faltas(Integer.parseInt(c.getString(c.getColumnIndex("Qtd_Faltas"))));
list.add(m);
}
return list;
}
public boolean checarTabelaVazia() {
String comando = "SELECT * FROM " + tabela;
Cursor c = getReadableDatabase().rawQuery(comando, null);
return c.moveToFirst();
}
public void addFalta(Materia o) {
String comando = "SELECT * FROM " + tabela + " WHERE ID " + o.getID();
Cursor c = getReadableDatabase().rawQuery(comando, null);
if (c.moveToFirst()) {
int faltas = c.getInt(c.getColumnIndex("Qtd_Faltas"));
faltas++;
ContentValues cv = new ContentValues();
cv.put("Qtd_Faltas", faltas);
String[] args = {o.getID() + ""};
getWritableDatabase().update(tabela, cv, "ID=?", args);
}
}
}
My GradeDAO
public class GradeDAO extends SQLiteOpenHelper {
private final String tabela = "Grades";
Context context;
public GradeDAO(Context context) {
super(context, "Grades", null, 2); this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
String comando = "CREATE TABLE " + tabela + " (";
comando += "id INTEGER PRIMARY KEY,";
comando += "Materia INTEGER,";
comando += "Periodo TEXT,";
comando += "Dia_Semana INTEGER)";
db.execSQL(comando);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "DROP TABLE IF EXISTS " + tabela;
db.execSQL(sql);
onCreate(db);
}
public void insert(Grade o){
try{
ContentValues cv = new ContentValues();
cv.put("Materia", o.getMateria());
cv.put("Periodo", o.getPeriodo());
cv.put("Dia_Semana", o.getDiaSemana());
getWritableDatabase().insert(tabela, null, cv);
}catch (Exception ex){
throw ex;
}
}
public void update(Grade o){
ContentValues cv = new ContentValues();
cv.put("Materia", o.getMateria());
cv.put("Periodo", o.getPeriodo());
cv.put("Dia_Semana", o.getDiaSemana());
String[] args = {o.getID() + ""};
getWritableDatabase().update(tabela, cv, "ID=?", args);
}
public void delete(Grade o){
String[] args = {o.getID() + ""};
getWritableDatabase().delete(tabela, "ID = ?", args);
}
public boolean checarTabelaVazia(){
String comando = "SELECT * FROM " + tabela;
Cursor c = getReadableDatabase().rawQuery(comando, null);
return c.moveToFirst();
}
public Grade preencher(Grade gg){
String comando = "SELECT g.*, c.Descricao FROM " + tabela + " g ";
comando += " INNER JOIN Materia m ON m.id = g.materia ";
comando += " WHERE g.id = " + gg.getID();
Cursor c = getReadableDatabase().rawQuery(comando, null);
if(c.moveToFirst()){
Grade o = new Grade();
o.setID_Materia(c.getInt(c.getColumnIndex("Materia")));
o.setMateria(c.getString(c.getColumnIndex("Descricao")));
o.setPeriodo(c.getString(c.getColumnIndex("Periodo")));
o.setDiaSemana(c.getInt(c.getColumnIndex("Dia_Semana")));
return o;
}
else{
return null;
}
}
public List<Grade> listar(){
MateriaDAO dao = new MateriaDAO(context);
List<Materia> m = dao.listar();
String comando = "SELECT g.*, c.Descricao FROM " + tabela + " g ";
comando += " JOIN " + dao.tabela + " m ON m.id = g.materia ";
Cursor c = getReadableDatabase().rawQuery(comando, null);
List<Grade> list = new ArrayList<>();
Grade o;
while(c.moveToNext()){
o = new Grade();
o.setID_Materia(c.getInt(c.getColumnIndex("Materia")));
o.setMateria(c.getString(c.getColumnIndex("Descricao")));
o.setPeriodo(c.getString(c.getColumnIndex("Periodo")));
o.setDiaSemana(c.getInt(c.getColumnIndex("Dia_Semana")));
list.add(o);
}
return list;
}
}
Your issue is that have seperate/distinct connections/databases. That is GradeDao is one connection/database and MateriaDao is another. They are independent of each other and know nothing about each other.
As such GradeDao does not know that there is a table called Materias in MateriaDao.
You could attach the Materias database to the GradeDao database SQL As Understood By SQLite - ATTACH DATABASE
However, I would suggest that in all likiehood a single database with multiple tables would be simpler to work with (one of the things about a Relational Database Management System is that realtionships (tables that relate to each other) has relational in the name).