Help needed with obtaining values from DB.

[tyson.crouch]

G'day,

I've got rather a simple problem to deal with though i'm personally having some trouble.
I've recently fixed up some of my database queries so that i use a view to get the data that i need.

My view gets the following info:

• Attraction Name
• Package ID
• Package Price

Each attraction name has an Package ID, and a Package price. So my table looks like this
Zoo 1 55.00
Circus 1 55.00
Safari 2 88.99
Temple 2 88.99
Park 2 88.99
... And so on.

I need to be able to store the attraction names in either a vector or an array/arrayList
while having only one instance of the Package ID and Package Price being stored.

My Code is as Follows:

public attractionPackage[] getPakcage(){
	int numPack = 0;   // Number of packages
	int i = 0;         // Counter for packageList

	try{
		Connection connect = new database().getConnection();
		Statement statement = connect.createStatement();

		// Query Selects Attraction Name, Package ID and Package Price
		String query = "SELECT * FROM attpack_view";
		ResultSet res = statement.executeQuery(query);

		// Query gets number of DISTINCT Package ID entries
		String numPackages = "SELECT DISTINCT COUNT( pacID ) AS num FROM packages";
		ResultSet result = statement.executeQuery(numPackages);

		// Set number of packages
		while(result.next()){
                	numPack = result.getInt("num");
            	}

		// Initialize packageList as attractionPackage[]
		packageList = new attractionPackage[numPack];
            
		/**************************
		* THIS IS WHERE I NEED HELP
		***************************/		
		int previous_id = -1;
		while (res.next()) {
            		int current_id = res.getInt("pacID");
            		if (current_id != previous_id) {
                  		packageList[i].setPackageID(res.getInt("pacID"));
	                	packageList[i].setPackagePrice(res.getDouble("pacID"));
				previous_id = current_id;
            		}else {
                  		// process the next (same) pac_id with a different att_id
                	}
            	} // End WHILE
	// Catch exceptions thrown by SQL
	}catch(SQLException e){
		// OUTPUT Errors
		e.printStackTrace();
	}

	return packageList;
}

I'd appreciate any help you could give. I understand that my code isnt the cleanest or most efficient, but for the most part it works.

Thx
TC

[peter_budo]

You are wasting time with querying to get number of packages to determinate size of array, use ArrayList instead.

Your database structure seems to be mess. ID should be unique and your primary key. Why do you have repeating IDs?

[tyson.crouch]

My DB structure is quite sound.

This is from a VIEW which i had created to get the details of attraction packages.
I have 3 tables,

1. Attractions has;
- Attraction ID -- Primary Key
- Attraction Name
- Adult Price
- Child Price
- Reference ID -- Which references the Attraction ID as the 'Location'

2. Packages has;
- Package ID -- Primary Key
- Package Price

3. AttractionPackage has;
- Attraction ID -- Foreign Key to Attraction
- Package ID -- Foreign Key to Packages

The VIEW Which I have created gets the name from the attraction ID in AttractionPackage(table3). It also holds the package ID and Package price for that particular Package.

SELECT `attractions`.`attName` ,`packages`.`pacID`,`packages`.`packagePrice` 
FROM (
`attractions` INNER JOIN `attractionpackages` 
ON `attractions`.`attID` = `attractionpackages`.`attID`)
INNER JOIN `packages` WHERE `packages`.`pacID` = `attractionpackages`.`pacID`;

That explains why there are duplicate values in the table.

The reason as to why i get the number of values is that I'm using an object to store the values. Package price, package ID and a String array of attractionNames

[peter_budo]

I have feeling you are overcomplicating problem and have issue of making it more simplified.
You should either create object type "Attraction" with following parameters attraction name, Package ID and a Package price that can be easily added to to any Collection type and with implementation of sortable can be easily sorted by request.Or make more precise queries that will sort your return by attraction ID, attraction name and price.

It is difficult to give more valuable input as I'm struggling to understand what you up-to...

[tyson.crouch]

I think that you may be right in that I'm over-complicating matters while trying to make things simpler.

I'm trying to iterate through the ResultSet To get the data for ONE package, which includes multiple Attraction Names, one Package ID and one Package Price.

So, on the first run of the while loop, i store the package ID, package price and the first of the attractions. In the iteration i am trying to figure out how i can add this attraction name to the ArrayList of the previous iteration (which holds the attraction names).. i hope that i haven't confused you too much.

I think that i might scrap this, and use two queries instead. One to get the package ID, and package Price, and in the nested loop, get the attraction details....