Using a java method to return multiple values?

Question

I was just wondering whether or not there is a way to make a java method return multiple values.

I am creating an application that uses the jdbc library to work with a database. Now, i can successfully enter values into the database but now i just need a way to return them and this is where im a bit stuck. I creating a form where the user enters a specific value (an ID number) which is then passed to by Database class which carries out my database work.

Database newQuery = new Database();     
newQuery.getCust(c_ID);         //uses the GetCust method in my class,
                                //passing it the ID of the customer.

now the getCust() method in my Database class creates the following query:

ResultSet Customer = stat.executeQuery("SELECT * FROM Persons WHERE Cust_ID=C_ID");

Now i just need a way to return the results that are stored in Customer back, Any Ideas?

Answer 1

Why not just return Customer, or create a small class with all the values you want returned in it and return that class?

Answer 2

You can't exactly return multiple values from a method in Java, but you can always return a container object that holds several values. In your case, the easiest thing to do would be to return the ResultSet, Customer.

If you're concerned about exposing your data layer to your UI, you can copy the data from the ResultSet into a structure that is less specific to the database, either a List of Maps, or perhaps a List of Customer objects, where Custom is a new class that represents your business entity.

Answer 3

Each customer could be accompanied with a little interface that describes a method that takes multiple arguments. You then pass in the object that implements this interface to have the result delivered to it.

The object that implement it can of course be the same as the method calling getCustomer belongs to, so it just passes a reference to 'this' and assign the arguments to fields that you can expect to have been set when it all returns.

Answer 4

Consider using an object/relational mapping library. It will handle the details of packaging the multiple data values you need to return from the JDBC ResultSet into a single Java bean object.

Which one to pick is another discussion. A lot of smart people use Hibernate. The Java platform includes JPA. Using one off the shelf will save you from inventing your own, which is what devising your own combination of objects and collections would end up being.

Answer 5

Ok so i hve tried out a few different things. When i run the following code:

Class.forName("org.sqlite.JDBC");       
Connection conn = DriverManager.getConnection("jdbc:sqlite:Customers.db");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from Customers where Cust_ID=5;");
String theName = rs.getString("cust_Name");
System.out.println("Customer ID: " + toSearch);
System.out.println("Customer Name: " + theName);
rs.close();
conn.close();

Is does print out the name of the customer with the Cust_ID of 5, so the query is successful, However when i try the following, using the value that is passed to the method, Nothing is printed:

String toSearch = custId;
Class.forName("org.sqlite.JDBC");       
Connection conn = DriverManager.getConnection("jdbc:sqlite:Customers.db");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from Customers where Cust_ID=toSearch;");
//or even ResultSet rs = stat.executeQuery("select * from Customers where Cust_ID='toSearch';");
String theName = rs.getString("cust_Name");
System.out.println("Customer ID: " + toSearch);
System.out.println("Customer Name: " + theName);
rs.close();
conn.close();

So it seems i cant use a variable in the query which is kind of a big problem.

Answer 6

Thanks for all the great tips everyone, they are really going to help me as i move on through java, It's very useful to know all these little tidbits of information.

I got the query working by doing this:

String theQuery = ("select * from Customers where Cust_ID=" + custId + ";");
ResultSet rs = stat.executeQuery(theQuery);

Cheers everyone!

Answer 7

So your actual problem is that you didn't know how to set values/parameters in a SQL query? The only right way to do this is using PreparedStatement.

String sql = "select * from Customers where Cust_ID = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setLong(custId);
resultSet = preparedStatement.executeQuery();

It not only eases setting Java objects (String, Long, Integer, Date, InputStream and so on) in a SQL query, but most importantingly it will save you from SQL Injection risks. Further it's also faster than a Statement because it's precompiled.

As to your code logic, you should always close the DB resources in the reverse order in the finally block to avoid resource leaks in case of exceptions. Here's a basic example how to obtain a Customer the right JDBC way:

public Customer find(Long customerId) throws SQLException {
    String sql = "SELECT id, name, age FROM customer WHERE id = ?";
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    Customer customer = null;

    try {
        connection = getConnectionSomehow();
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setLong(custId);
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            customer = new Customer();
            customer.setId(resultSet.getLong("id"));
            customer.setName(resultSet.getString("name"));
            customer.setAge(resultSet.getInteger("age"));
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
        if (preparedStatement != null) try { preparedStatement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    return customer;
}

You may find this tutorial useful to get more insights and examples.

Answer 8

In addition to using Hibernate - take a look at Spring. It supports connection pooling etc and allows you to abstract the JDBC away from your code completely.

It will either return you a List of Maps or a List of your custom type (depending on how you call it).