É o seguinte eu tenho um select que me dá vários valores.
public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String caminho = "C:/Programas/PTW/DiaSoft/DataBase/user.mdb";
Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + caminho);
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery ("SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = Id)))");
if (rs.next()){
String ia = dapa.getString("ResumeNr");
System.out.println("Resume"+ia);
stm.executeQuery("SELECT MAX(D1) AS DAP1 FROM MeasData WHERE ParamNr = "+ia+"");
stm.executeUpdate("SELECT MAX(D1)AS DAP1 FROM MeasData WHERE (ParamNr = "+ia+")");
stm.executeQuery("SELECT MAX(D2) AS DAP2 FROM MeasData WHERE ParamNr = "+ia+"");
stm.executeUpdate("SELECT MAX(D2) AS DAP2 FROM MeasData WHERE ParamNr = "+ia+"");
stm.executeQuery("SELECT MAX(D3) AS DAP3 FROM MeasData WHERE ParamNr = "+ia+"");
stm.executeUpdate("SELECT MAX(D3) AS DAP3 FROM MeasData WHERE ParamNr = "+ia+"");
Não se é a melhor maneira, ou mesmo se é possível, mas queria guardar o resultado do select do Resultset num vector, para entrar no if calcular os máximos do primeiro valor do select (ia), quando calcula-se o D3(ultimo) passava para o segundo e fazia o mesmo, até ao ultimo valor do select do Resultset.
Já agora outra questão como posso ver o valor de cada SELECT MAX???
O que pretendo era calcular os maximos de todos os parametros inseridos numa tabela, sendo o valor de todos esses parametros existentes retirados no primeiro select.
E queria ainda poder ver os resultados do calculo de todos os maximos.
Por exemplo, o primeiro select diz: 11, 12, 16, 23, 41
queria que calcula-se o maximo D1, D2 e D3 quando o Parametro é 11, e ver o resultado de D1, D2 e D3 calculado. Depois passava para o parametro 12 e fazia o mesmo, sempre assim…
Cara pelo que eu entendi sobre a sua duvida você quer pegar os resultados do ResultSet e jogar dentro de um vetor pra poder manipula-los mais facilmente, se for isso você pode fazer o seguinte:
ArrayList<ArrayList> resultados = new ArrayList();
....
ResultSet result = statement.executeQuery(sua_sql);
while (result.next()){
ArrayList linha = new ArrayList();
for (String campo : camposVet)
ArrayList.add(result.getString(campo));
resultados .add(linha);
}
public static void dos()throws SQLException{
String caminho = "C:/user.mdb";
try {
Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + caminho);
Statement stmt = con.createStatement();
String strSql = "SELECT Id FROM PatientData";
ResultSet rs = stmt.executeQuery(strSql);
while (rs.next()){
String resid = rs.getString("Id");
System.out.println("Id "+resid);
while(resid != null){
String DAP1 = "SELECT MAX(D1) AS V1 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d1 = stmt.executeQuery(DAP1);
String rd1 = d1.getString("V1");
System.out.println("M1 "+rd1);
String DAP2 = "SELECT MAX(D2) AS V2 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d2 = stmt.executeQuery(DAP2);
String rd2 = d1.getString("V2");
System.out.println("M2 "+rd2);
}
}
}
public static void main(String[] args) {
try {
dos();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
[/code]
Fiz uma pequena alteração ao código, mas estou com o mesmo problema e não estou conseguindo resolver…
Consigo visualizar todos os Ids presentes na tabela da bd, mas não estou conseguindo efectuar o cálculo do máximo…dá erro dizendo “estado de cursor inválido”
eu estava vendo uns executeUpdate…
com o uso de uns selects
acho que está errado, não… ?
é somente o executeQuery, para o select, certo?
e o proprio resutlado do select max, vai dar o máximo.
o erro nao esta no update… ?
Caso contrário poe para uma variavel e chama a variavel…
vou fazer uns testes aqui depois, mas por enquanto é isto
Eu já estou conseguindo calcular os máximos, mas apenas para o primeiro Id!!! Após isso dá erro de Resultset is closed :?
package msi;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
public class DAP {
public static void dosemetria()throws SQLException{
String caminho = "C:/user.mdb";
try {
Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + caminho);
Statement stmt = con.createStatement();
String strSql = "SELECT Id FROM PatientData";
ResultSet rs = null;
rs = stmt.executeQuery(strSql);
String [] vector = new String [16];
ArrayList<String> resultados = new ArrayList<String>();
while (rs.next()) {
String resid = rs.getString("Id");
if (resid != null){
resultados.add(resid);
}
for (String id : resultados) {
String DAP1 = "SELECT MAX(D1) AS V1 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d1 = stmt.executeQuery(DAP1);
if (d1.next()){
String rd1 = d1.getString("V1");
System.out.println("M1 "+rd1);
vector[0] = rd1;
System.out.println("1 "+vector[0]);
}
String DAP2 = "SELECT MAX(D2) AS V2 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d2 = stmt.executeQuery(DAP2);
if (d2.next()){
String rd2 = d2.getString("V2");
System.out.println("M2 "+rd2);
vector[1] = rd2;
System.out.println("2 "+vector[1]);
}
String DAP3 = "SELECT MAX(D3) AS V3 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d3 = stmt.executeQuery(DAP3);
if (d3.next()){
String rd3 = d3.getString("V3");
System.out.println("M3 "+rd3);
vector[2] = rd3;
System.out.println("3 "+vector[2]);
}
String DAP4 = "SELECT MAX(D4) AS V4 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d4 = stmt.executeQuery(DAP4);
if (d4.next()){
String rd4 = d4.getString("V4");
System.out.println("M4 "+rd4);
vector[3] = rd4;
System.out.println("4 "+vector[3]);
}
String DAP5 = "SELECT MAX(D5) AS V5 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d5 = stmt.executeQuery(DAP5);
if (d5.next()){
String rd5 = d5.getString("V5");
System.out.println("M5 "+rd5);
vector[4] = rd5;
System.out.println("5 "+vector[4]);
}
String DAP6 = "SELECT MAX(D6) AS V6 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d6 = stmt.executeQuery(DAP6);
if (d6.next()){
String rd6 = d6.getString("V6");
System.out.println("M6 "+rd6);
vector[5] = rd6;
System.out.println("6 "+vector[5]);
}
String DAP7 = "SELECT MAX(D7) AS V7 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d7 = stmt.executeQuery(DAP7);
if (d7.next()){
String rd7 = d7.getString("V7");
System.out.println("M7 "+rd7);
vector[6] = rd7;
System.out.println("7 "+vector[6]);
}
String DAP8 = "SELECT MAX(D8) AS V8 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d8 = stmt.executeQuery(DAP8);
if (d8.next()){
String rd8 = d8.getString("V8");
System.out.println("M8 "+rd8);
vector[7] = rd8;
System.out.println("8 "+vector[7]);
}
String DAP9 = "SELECT MAX(D9) AS V9 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d9 = stmt.executeQuery(DAP9);
if (d9.next()){
String rd9 = d9.getString("V9");
System.out.println("M9 "+rd9);
vector[8] = rd9;
System.out.println("9 "+vector[8]);
}
String DAP10 = "SELECT MAX(D10) AS V10 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d10 = stmt.executeQuery(DAP10);
if (d10.next()){
String rd10 = d10.getString("V10");
System.out.println("M10 "+rd10);
vector[9] = rd10;
System.out.println("10 "+vector[9]);
}
String DAP11 = "SELECT MAX(D11) AS V11 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d11 = stmt.executeQuery(DAP11);
if (d11.next()){
String rd11 = d11.getString("V11");
System.out.println("M11 "+rd11);
vector[10] = rd11;
System.out.println("11 "+vector[10]);
}
String DAP12 = "SELECT MAX(D12) AS V12 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d12 = stmt.executeQuery(DAP12);
if (d12.next()){
String rd12 = d12.getString("V12");
System.out.println("M12 "+rd12);
vector[11] = rd12;
System.out.println("12 "+vector[11]);
}
String DAP13 = "SELECT MAX(D13) AS V13 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d13 = stmt.executeQuery(DAP13);
if (d13.next()){
String rd13 = d13.getString("V13");
System.out.println("M13 "+rd13);
vector[12] = rd13;
System.out.println("13 "+vector[12]);
}
String DAP14 = "SELECT MAX(D14) AS V14 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d14 = stmt.executeQuery(DAP14);
if (d14.next()){
String rd14 = d14.getString("V14");
System.out.println("M14 "+rd14);
vector[13] = rd14;
System.out.println("14 "+vector[13]);
}
String DAP15 = "SELECT MAX(D15) AS V15 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d15 = stmt.executeQuery(DAP15);
if (d15.next()){
String rd15 = d15.getString("V15");
System.out.println("M15 "+rd15);
vector[14] = rd15;
System.out.println("15 "+vector[14]);
}
String DAP16 = "SELECT MAX(D16) AS V16 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d16 = stmt.executeQuery(DAP16);
if (d16.next()){
String rd16 = d16.getString("V16");
System.out.println("M16 "+rd16);
vector[15] = rd16;
System.out.println("16 "+vector[15]);
}
Arrays.sort(vector);
System.out.println("MAXIMO "+vector[15]);
stmt.executeUpdate("UPDATE Parameter SET DAP = ("+vector [15]+") WHERE (ParamNr = (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"'))))");
System.out.println("UPDATE Parameter SET DAP = ("+vector [15]+") WHERE (ParamNr = (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"'))))");
}
}
ResultSet df = stmt.executeQuery("SELECT DAP FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))");
if (df.next()){
String dfim = df.getString("DAP");
System.out.println("DAP "+dfim);
stmt.executeUpdate("UPDATE PatientData SET [P Dose] = ("+dfim+") WHERE Id = '"+resid+"'");
System.out.println("UPDATE PatientData SET [P Dose] = ("+dfim+") WHERE Id = '"+resid+"'");
System.out.println("Actualização concluída");
}
}
public static void main(String[] args) {
try {
dos();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}