ResultSet is closed

31 de março de 2010 8 respostas

anamilagaia 31 de março de 2010

Boa Tarde

É 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???

Obrigada

8 Respostas

ViniGodoy 31 de mar. de 2010

Não entendi direito. O que você precisa fazer?

ViniGodoy 31 de mar. de 2010

PS: O uso da classe Vector é desaconselhado desde o Java 1.2. No lugar dele prefira usar a classe ArrayList:
http://www.guj.com.br/posts/list/74068.java#389435

anamilagaia 31 de mar. de 2010

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…

Como posso fazer isso???

Obrigada

Bruck 31 de mar. de 2010

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);
}

anamilagaia 1 de abr. de 2010

ode=java]
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;

public class DAP {

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();
	}
}

}

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”

Alguém me dá uma ajudinha???

Obrigada, e desculpem minha ignorância…

raghy 1 de abr. de 2010

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

anamilagaia 2 de abr. de 2010

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();
		}
	}

}

Que há de errado ???

anamilagaia 7 de abr. de 2010

Ninguém me consegue ajudar???

:oops:

Criado 31 de março de 2010

Ultima resposta 7 de abr. de 2010

Respostas 8

Participantes 4

8 Respostas

Topicos relacionados