Ajuda com Illegal operation on empty result set

SperoCoin · Agosto 29, 2021, 8:38pm

Olá galera, sou novo aqui no fórum e em JAVA.
Tenho uma aplicação que faz duas consultas distintas ao banco de dados MySQL e tenho as seguintes preocupações nos retornos:

A consulta resultQuery retorna os dados depósitos realizados pelo usuário identificado por obj_Id;
A consulta resultQueryWallet pesquisa entre as tabelas os dados do usuários tendo como ponto de ligação ou chave o endereço de depósito(address na tabela walletnotify e speroAddress_dep na tabela characters);

Tendo isso em mente, quando o usuário enviar o comando correspondente a classe getWallet, deve ser observado:

Se o usuário possui o speroAddress_dep(characters) registrado na coluna address(walletnotify ), caso positivo, ele vai retornar os dados presentes na tabela walletnotify, caso negativo ele vai retornar apenas os dados que estão presentes na tabela characters.
Se ele não possuir um speroAddress_dep(characters), deverá retornar uma mensagem ao usuário(não implementei ainda esta parte).

O código seria esse:

package com.l2jfrozen.gameserver.handler.voicedcommandhandlers;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;

import org.apache.log4j.Logger;

import javolution.text.TextBuilder;

import com.l2jfrozen.Config;
import com.l2jfrozen.gameserver.network.serverpackets.NpcHtmlMessage;
import com.l2jfrozen.gameserver.model.actor.instance.L2PcInstance;

import com.l2jfrozen.sperocoin.SperocoindApi;
import com.l2jfrozen.sperocoin.SperocoindApiFactory;
import com.l2jfrozen.sperocoin.core.SperocoindConnector4JException;
import com.l2jfrozen.sperocoin.response.BlockchainInfo;
import com.l2jfrozen.sperocoin.response.NetworkInfo;

import com.l2jfrozen.util.database.L2DatabaseFactory;


public class SperoCoin
{

	protected static final Logger LOGGER = Logger.getLogger(SperoCoin.class);
	public NpcHtmlMessage nhm = new NpcHtmlMessage(5);
	public TextBuilder replyMSG = new TextBuilder("");
	
		public String address = Config.RPC_IP;
		public int port = Config.RPC_PORT;
		public String protocol = "http";
		public String user = Config.RPC_USER;
		public String password = Config.RPC_PASS;
	
	SperocoindApi api = SperocoindApiFactory.createConnection(address, port, protocol, user, password);

public void getWallet(String[] args, L2PcInstance activeChar) throws SperocoindConnector4JException
{
	
	Connection con = null;
	
	try {
		NumberFormat formatter = new DecimalFormat("#0.00000000");
		con = L2DatabaseFactory.getInstance().getConnection(false);
		
		PreparedStatement statement = con.prepareStatement("SELECT *, SUM(tot_amt) AS TOTALBALANCE FROM walletnotify AS w INNER JOIN characters AS c ON w.address = c.speroAddress_dep WHERE c.obj_Id=?");
		statement.setInt(1, activeChar.getObjectId());
		ResultSet resultQuery = statement.executeQuery();
		
		PreparedStatement statementwallet = con.prepareStatement("SELECT * FROM characters AS c INNER JOIN walletnotify AS w ON c.speroAddress_dep = w.address WHERE c.obj_Id=?");
		statementwallet.setInt(1, activeChar.getObjectId());
		ResultSet resultQueryWallet = statementwallet.executeQuery();
		
		resultQuery.next();
		resultQueryWallet.next();
		
		String nameActiveChar = activeChar.getName();
		String speroAddress_dep = resultQueryWallet.getString("speroAddress_dep");
		String speroWalletAddress = resultQuery.getString("address");
		Double speroBalance = resultQuery.getDouble("TOTALBALANCE");
		
		if(speroWalletAddress != null) {
			replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
			replyMSG.append("<br><br>");
			replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>" + formatter.format(speroBalance) + "</center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("</body></html>");
		}else{
			replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
			replyMSG.append("<br><br>");
			replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
			replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>No Balance</center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
			replyMSG.append("<br><br>");
			replyMSG.append("</body></html>");
		}

	}catch (Exception e) {
		e.printStackTrace();
		LOGGER.info("GETWALLET - Error!");
	}
}	
}

No código atual, recebo erro ao consultar um usuário que não possui registros na tabela walletnotify, quando o usuário possui registro o valor é retornado perfeitamente:

java.sql.SQLException: Illegal operation on empty result set.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:492)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:845)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:863)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
	at com.l2jfrozen.gameserver.handler.voicedcommandhandlers.SperoCoin.getWallet(SperoCoin.java:156)
	at com.l2jfrozen.gameserver.handler.voicedcommandhandlers.SperoCoinCommands.useVoicedCommand(SperoCoinCommands.java:44)
	at com.l2jfrozen.gameserver.network.clientpackets.Say2.runImpl(Say2.java:535)
	at com.l2jfrozen.gameserver.network.clientpackets.L2GameClientPacket.run(L2GameClientPacket.java:77)
	at com.l2jfrozen.gameserver.network.L2GameClient.run(L2GameClient.java:1213)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

Lucas_Camara · Agosto 30, 2021, 12:57am

Antes de “andar” no RestulSet, verifique se ele tem alguma coisa:

if (resultQuery.next()) {
    String speroWalletAddress = resultQuery.getString("address");
}

Faça o mesmo para o resultQueryWallet.

SperoCoin · Agosto 30, 2021, 4:18am

Mudei para isso e deu outro erro:

	public void getWallet(String[] args, L2PcInstance activeChar) throws SperocoindConnector4JException
	{
		
		Connection con = null;
		
		try {
			NumberFormat formatter = new DecimalFormat("#0.00000000");
			con = L2DatabaseFactory.getInstance().getConnection(false);
			
			PreparedStatement statement = con.prepareStatement("SELECT *, SUM(tot_amt) AS TOTALBALANCE FROM walletnotify AS w INNER JOIN characters AS c ON w.address = c.speroAddress_dep WHERE c.obj_Id=?");
			statement.setInt(1, activeChar.getObjectId());
			ResultSet resultQuery = statement.executeQuery();
			
			PreparedStatement statementwallet = con.prepareStatement("SELECT * FROM characters AS c INNER JOIN walletnotify AS w ON c.speroAddress_dep = w.address WHERE c.obj_Id=?");
			statementwallet.setInt(1, activeChar.getObjectId());
			ResultSet resultQueryWallet = statementwallet.executeQuery();
			
			resultQuery.next();
			resultQueryWallet.next();
			
			if (resultQuery.next() || resultQueryWallet.next() ) {
				String speroWalletAddress = resultQuery.getString("address");
				Double speroBalance = resultQuery.getDouble("TOTALBALANCE");
				String nameActiveChar = activeChar.getName();
				String speroAddress_dep = resultQueryWallet.getString("speroAddress_dep");
				
				if(speroWalletAddress != null) {
					replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
					replyMSG.append("<br><br>");
					replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>" + formatter.format(speroBalance) + "</center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("</body></html>");
				}else{
					replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
					replyMSG.append("<br><br>");
					replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
					replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>No Balance</center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
					replyMSG.append("<br><br>");
					replyMSG.append("</body></html>");
				}
			}

		}catch (Exception e) {
			e.printStackTrace();
			LOGGER.info("GETWALLET - Error!");
		}
	}

Erro:

java.sql.SQLException: After end of result set
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:492)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:845)
	at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:863)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
	at com.l2jfrozen.gameserver.handler.voicedcommandhandlers.SperoCoin.getWallet(SperoCoin.java:159)
	at com.l2jfrozen.gameserver.handler.voicedcommandhandlers.SperoCoinCommands.useVoicedCommand(SperoCoinCommands.java:44)
	at com.l2jfrozen.gameserver.network.clientpackets.Say2.runImpl(Say2.java:535)
	at com.l2jfrozen.gameserver.network.clientpackets.L2GameClientPacket.run(L2GameClientPacket.java:77)
	at com.l2jfrozen.gameserver.network.L2GameClient.run(L2GameClient.java:1213)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
INFO  GETWALLET - Error!

Lucas_Camara · Agosto 30, 2021, 10:59am

Pq vc chamou o next() duas vezes.

resultQuery.next(); // com o IF, não precisa mais dessa linha
resultQueryWallet.next(); // com o IF, não precisa mais dessa linha
			
if (resultQuery.next() || resultQueryWallet.next() ) {

SperoCoin · Agosto 30, 2021, 1:33pm

Consegui fazer assim, não sei se é a melhor forma rsrsrsrs…
Mudei a consulta da resultQueryWallet e nomes dos statement’s.

public void getWallet(String[] args, L2PcInstance activeChar) throws SperocoindConnector4JException
	{
		
		Connection con = null;
		
		try {
			NumberFormat formatter = new DecimalFormat("#0.00000000");
			con = L2DatabaseFactory.getInstance().getConnection(false);
			
			PreparedStatement statementDB = con.prepareStatement("SELECT *, SUM(tot_amt) AS TOTALBALANCE FROM walletnotify AS w INNER JOIN characters AS c ON w.address = c.speroAddress_dep WHERE c.obj_Id=?");
			statementDB.setInt(1, activeChar.getObjectId());
			ResultSet resultQueryDB = statementDB.executeQuery();
			
			PreparedStatement statementwallet = con.prepareStatement("SELECT speroAddress_dep FROM characters WHERE obj_Id = ?");
			statementwallet.setInt(1, activeChar.getObjectId());
			ResultSet resultQueryWallet = statementwallet.executeQuery();
			
			String nameActiveChar = activeChar.getName();

				if(resultQueryWallet.next()) {
					String speroAddress_dep = resultQueryWallet.getString("speroAddress_dep");
					
					if(speroAddress_dep == null) {
						replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
						replyMSG.append("<br><br>");
						replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
						replyMSG.append("<font color=\"FFFF00\"><center>You don't have wallet data yet, please generate</font></center><br>");
						replyMSG.append("<font color=\"FFFF00\"><center>new address with the command .spero_getnewaddress</font></center><br>");
						replyMSG.append("<br><br>");
						replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
						replyMSG.append("<br><br>");
						replyMSG.append("</body></html>");
					} else {
						if(resultQueryDB.next()) {
							Double speroBalance = resultQueryDB.getDouble("TOTALBALANCE");
							String speroWalletAddress = resultQueryDB.getString("address");
							
							if(speroWalletAddress != null) {
								replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
								replyMSG.append("<br><br>");
								replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>" + formatter.format(speroBalance) + "</center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("</body></html>");
							} else {
								replyMSG.append("<html><title>SPEROCOIN WALLET</title><body>");
								replyMSG.append("<br><br>");
								replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_1\"  width=300 height=32></center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>L2SPERO WALLET</center></font><br><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>WALLET INFORMATION</center></font><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Name: </font>" + nameActiveChar + "</center><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Deposit Address: </font>" + speroAddress_dep + "</center><br>");
								replyMSG.append("<font color=\"FFFF00\"><center>Balance: </font>No Balance</center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("<center><img src=\"L2UI_CH3.onscrmsg_pattern01_2\"  width=300 height=32></center><br>");
								replyMSG.append("<br><br>");
								replyMSG.append("</body></html>");
							}
						}else {
							LOGGER.info("SEM RESULTADOS NA CONSULTA resultQueryDB");
						}
					}
				} else {
					LOGGER.info("SEM RESULTADOS NA CONSULTA resultQueryWallet");
				}

		}catch (Exception e) {
			e.printStackTrace();
			LOGGER.info("GETWALLET - Error!");
		}
	}

Lucas_Camara · Agosto 30, 2021, 1:35pm

Isso mesmo. Vc somente vai pegar dados do resultSet se vc tiver certeza que ele tem registros (que é usando o next para verificar).

SperoCoin · Agosto 30, 2021, 1:36pm

Muito obrigado, me ajudou demais!

Ajuda com Illegal operation on empty result set

Cursos de Mobile

Cursos de Programação

Cursos de Front-end

Cursos de DevOps

Cursos de Design & UX

Cursos de Business

Cursos de Data & BI