I would like to know what I am doing wrong, by returning a ResultSet that executes a stored procedure that performs a select on a sql table. I'm working on Java
I have a java project that serves as the data layer where I connect to the database and receives the parameters of the presentation layer to summon and send this data to the sql DB, after the process, the product must return the ResultSet to another java project, which would be the data layer, where is the table.
package Conectar;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;
import java.sql.CallableStatement;
import java.sql.Types;
public class Coneccion
{
static String login="";
static String Password="";
Connection conexion=null;
String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
CallableStatement cst;
ResultSet r;
Statement sentencia;
Coneccion con;
public Coneccion()
{
try
{
Class.forName(driver);
conexion=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; databaseName= NEGOCIO", "sa", "123");
}
catch(SQLException | ClassNotFoundException e)
{
JOptionPane.showMessageDialog(null, e);
}
}
public Connection getConnection()
{
return conexion;
}
public void desconectar()
{
conexion=null;
if(conexion!=null)
{
JOptionPane.showMessageDialog(null, "No se pudo cerra la conexion");
}
}
public ResultSet Listar (String Cad)
{
try
{
Class.forName(driver).newInstance();
Connection cn= DriverManager.getConnection("jdbc:sqlserver://localhost:1433; databaseName= NEGOCIO", "sa", "123");
PreparedStatement da= cn.prepareStatement(Cad);
ResultSet tbl= da.executeQuery();
return tbl;
}
catch(ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e)
{
javax.swing.JOptionPane.showMessageDialog(null, e);
return null;
}
}
public String Ejecutar (String Cad)
{
try
{
Class.forName(driver).newInstance();
Connection cn= DriverManager.getConnection("jdbc:sqlserver://localhost:1433; databaseName= NEGOCIO", "sa", "123");
PreparedStatement da= cn.prepareStatement(Cad);
ResultSet r = da.executeQuery();
return "Registro Grabados con exito";
}
catch(ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e)
{
javax.swing.JOptionPane.showMessageDialog(null, e);
return "Error: " + e.getMessage();
}
}
public ResultSet TraerProducto(int id, String nombre, int m) {
try
{
Coneccion obj= new Coneccion();
PreparedStatement pstm= con.getConnection().prepareStatement("{call TraerProducto ?, ?}");
pstm.setInt(1, id);
pstm.setString(2, nombre);
ResultSet consulta = pstm.executeQuery();
ResultSet r=obj.Listar(String.valueOf(consulta));
}
catch(Exception e)
{
JOptionPane.showMessageDialog(null, JOptionPane.WARNING_MESSAGE);
}
return r;
}
}
This is the procedure that summons to bring Product from the data layer, and load the result into the jtable. The procedure is executed every time data is entered in a Jtext:
public void keyTyped(KeyEvent e)
{
if (textBuscarP.getText().length()>3)
{
String eleccion=String.valueOf(comboBoxFiltroP.getSelectedIndex());
if(eleccion=="CODIGO")
{
m=1;
}
else
{
m=0;
}
Coneccion objeto= new Coneccion();
ResultSet r;
if (m==1)
{
r = objeto.TraerProducto(Integer.parseInt(this.textBuscarP.getText()), "", m);
}
else
{
r= objeto.TraerProducto(-10, this.textBuscarP.getText(), m);
}
try
{
String titulos[]= {"ID","Nombre","Precio","Cantidad","Precio de Descuento", "Cantidad de Descuento","Codigo de Barra"};
DefaultTableModel m1 = new DefaultTableModel(null, titulos);
JTable p= new JTable (m1);
String fila[]= new String[7];
int c=1;
while (r.next())
{
fila[1]=r.getString(1);
fila[2]=r.getString(2);
fila[3]=r.getString(3);
fila[4]=r.getString(4);
fila[5]=r.getString(5);
fila[6]=r.getString(6);
m1.addRow(fila);
c++;
}
TablaProducto.setModel(m1);
TableRowSorter<TableModel> ordenar= new TableRowSorter<TableModel> (m1);
TablaProducto.setRowSorter(ordenar);
this.TablaProducto.setModel(m1);
}
catch(Exception e1)
{
JOptionPane.showMessageDialog(null, JOptionPane.WARNING_MESSAGE);
}
}
}