Creating a stored procedure

[BSewell]

Hi,
I would like to create a procedure so that I can run a select query. What I would like to return is all the fields so that obviously means I got to make a new type which all the properties of the fields.

Can anyone tell me how to do this?

 

[digitalnomad]

I'm going to rephrase your question and then answer it as I've interpreted it. Correct me if I'm wrong in the interpretation. You a) know how to make a stored proc on the DB server, and b) want to "objectize" the results from that proc in Java objects, but you c) aren't sure how to implement the materialization process, or the class whose instances will hold the results.

Since you are using a stored procedure approach, you are not like using a persistence architecture, like iBatis, Hibernate (Object-to-Relational mapping packages). So you would be implementing this the "old-fashioned" way, with factories, etc.

Here's how I've done this in the past. Let's say you have the simple case, where your class maps 1-1 with one table. Say your table is "Person" with columns "FName" and "LName"... something simple. You have two tasks:
1) create the business class
2) create the factory
I will omit discussion surrounding custom exceptions and exception translation within the factory, and stick to just answering the basic question.

--- 1) create the business class ---

public class Person
{
  private string firstName = null;
  private string lastName = null;

  public Person()
  {
  }

  public String getFirstName()
  {
    return this.firstName;
  }
  public void setFirstName(string aValue)
  {
    this.firstName = aValue;
  }

  public String getLastName()
  {
    return this.lastName;
  }
  public void setLastName(string aValue)
  {
    this.lastName = aValue;
  }
}

--- 2) create the factory ---
I have found that the easiest way to do this is to structure the factory so that you can easily add methods that retrieve single objects or Collections. All methods are static, because you don't need an instance of the factory. The public (client) methods are whatever you need them to be, and the non-public methods facilitate the process.

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import [your Person class];

public abstract class PersonFactory
{
  public static ArrayList getPersons(String aLastName)
  throws SQLException
  {
    ArrayList result = null;
    Connection con = null;
    CallableStatement stmt = null;
    ResultSet rs = null;
    
    try
    {
      con = [get a connection];
      stmt = con.prepareCall("{call proc_name[(?)]}");
      stmt.setString(1, aLastName);
      rs = stmt.executeQuery();
      result = materialize(rs);
    }
    finally
    {
      try
      {
        rs.close();
      }
      catch (SQLException closeException)
      {
      }
      try
      {
        stmt.close();
      }
      catch (SQLException closeException)
      {
      }
      try
      {
        con.close();
      }
      catch (SQLException closeException)
      {
      }
    }
    
    return result;
  }
  
  /**
   * Materializes all rows in ResultSet.
   */
  private static Collection materialize(ResultSet rs)
  throws SQLException
  {
    Person person = null;
    // ensure result is not null;
    ArrayList result = new ArrayList(); 
    while (rs.next())
    {
      person = materializeSingle(rs);
      result.add(person);
    }
    return result;    
  }
  
  /**
   * Materializes each row in ResultSet, creating
   * an object from the row at the current cursor position.
   */
  private static Person materializeSingle(ResultSet rs)
  throws SQLException
  {
    Person person = new Person();
    
    person.setFirstName(rs.getString("FName));
    person.setLastName(rs.getString("LName));

    return person;
  }
}

In this way, you can add further methods easily, because all you have to do is code the connection/statement part. you can even go further and create a non-public method that will run any statement you send it, etc. You can also create a "FactoryUtil" class with static methods that will close connections, statements, and resultsets, squelching the SQLException that you can't do anything about. You can also include rollbacks in that util class. If you organize this correctly, it's a pretty clean implementation. And if you're going to do this manually, you want it to be clean & easy to maintain.

Hope this helps... feel free to ask if you have questions or if I have not correctly understood the question.

 

[BSewell]

Hi, thanks for your reply. Sorry but it seems you mis interpreted the question. It's a nice bit of code through, which I learnt in Object-Orientated Programming in Java.

I'd try and be more specific with what my problem is, feel free to ask any questions if you want to.

I've just done my 2nd year at university and am taking part in an national award for work placements, so I got some industrial exprerience for when I actually finish my degree. Not that it matters, but I got another year long placement for a well known consulting company once my placement here is finished. Anyways, back to the problem:

I've migrated from a MS Access backend to a Postgresql 8.1 backend and have all the data loaded for me to test it. Unfortunately, I've been asked to keep the existing MS Access frontend so that more work can be done to it when I'm gone. I've setup ODBC successfully and created link tables to the server. The problem is that currently the reports are taking way too long to load, and then I got told MS Access downloads the tables needed in the query and does all the processing there. I would like to make pass through queries so that all the processing is done on the server. The proccessed data would then be sent back to the front end. Correct me if I'm wrong, butI seem to understand running the query as a stored procedure would do the trick, as it also caches the data as well.

I do have exprerience in creating procedures in Oracle 9i from university, but in postgresql it seems compelete different than what I have learnt. Could you help let me know what to do, and maybe an example to set me off?

Cheers,
Ben

 

[BSewell]

actually just wanted to give an update in my search to find a solution for the pass through queries problem.

http://support.microsoft.com/kb/q131534/

is basicly what I'm looking for but it's for SQL Server. Does anyone know if there is a function simular to sp_server_info" in postgres?

Ben