I have a small program that makes a query to the sql server from which it extracts data, but I can't get it to show me each corresponding data.
The output I get right now is this:
Id Student, Name and phone is fine but the name of the course is the one that I can't make it come out right, the correct output would be:
- 1, VICTOR, 2212134, FIRST THAT
- 2, PACO, 984747321, FIRST THAT
- 3, PEPE, 964321345, SECOND THAT
On the one hand I have a dataStudents.java:
public class datosAlumnos
{
Connection miconexion;
String cadsql;
public datosAlumnos() {
miconexion=MySQL_Util.Conectar("localhost","root","", "ciclosfp");
}
public ResultSet getResultado() {
cadsql ="select a.ID, a.NOMBRE, a.TELEFONO, cursos.NOMBRE from alumnos a, cursos "
+ "where cursos.id = a.CURSO;";
ResultSet rset=
MySQL_Util.Sel_Consulta(miconexion, cadsql);
return rset;
}
}
And a dataCourses.java:
public class datosCursos
{
Connection miconexion;
String cadsql;
private ResultSet rsIDAlum;
private ResultSet rsFCurso;
public datosCursos() {
miconexion=MySQL_Util.Conectar("localhost","root","", "ciclosfp");
}
public ResultSet getRsIDAlum() {
rsIDAlum=MySQL_Util.Sel_Consulta(miconexion, "select * from alumnos");
return rsIDAlum;
}
public void setRsIDAlum(ResultSet rsIDAlum) {
this.rsIDAlum = rsIDAlum;
}
public ResultSet getRsFCursos() {
try
{
String cadsql2 ="select cursos.nombre from cursos inner join alumnos on cursos.ID=alumnos.ID where alumnos.ID="+rsIDAlum.getString("ID");
rsFCurso=(MySQL_Util.Sel_Consulta(miconexion, cadsql2));
}
catch (Exception e)
{
rsFCurso=null;
}
return rsFCurso;
}
public void setRsFCursos(ResultSet rsFCurso) {
this.rsFCurso = rsFCurso;
}
}
and the index to display:
<h1><h:outputText value="Datos de los alumnos:"/></h1>
<h:dataTable border="1" value="#{datosAlumnos.resultado}"
var="fila">
<h:column>
<f:facet name="header">
<h:outputText value="Id Alumno"/>
</f:facet>
<h:outputText value="#{fila.ID}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Nombre Alumno"/>
</f:facet>
<h:outputText value="#{fila.NOMBRE}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Telefono"/>
</f:facet>
<h:outputText value="#{fila.TELEFONO}"/>
</h:column>
<h:column>
<f:facet name="header" >
<h:outputText value="Nombre Curso"/>
</f:facet>
<h:dataTable value="#{datosCursos.rsFCursos}" var="forma" >
<h:column>
<h:outputText value="#{forma.nombre}"/>
</h:column>
</h:dataTable>
</h:column>
</h:dataTable>
</body>
</html>
</f:view>
The thing is that the query of the courses shows the result in the 3 tables of the students and I have tried everything and I cannot make it work. I can't find information on whether aliases can be used in jsp for the tables, since there are two tables that have the same name (name[students] and name[courses]) I can't just put row.name because it takes the name of the students twice.
Update: I have modified the DataCurso.java file since I think this is the correct path but now in Course Name it does not show me any data.
Well trying things I found the solution and it was much simpler than what I had, the problem was that I was linking a wrong primary key between tables. It is not necessary to create two .java pages either, in a single file you can put the two instructions.
dataStudents.java:
index: