How to build AraryList effectively

[Dieter Stryker]

I need to build an ArrayList (I believe this is the best data structure) of Teams from a mysql database.

the Teams class contains a String teamName, and the constructor is:
Team(String name) { teamName = name; }


the table contains two areas where teams can be identified: column "home_team" and column "away_team" (no team is guaranteed to be both home and away)

I will run two queries.
The first query's ResultSet will be from "SELECT DISTINCT home_team FROM table", which will create a one column ResultSet of Strings
// what would be the code to let me iterate through the ResultSet and add Team objects to the ArrayList
The second query will acquire the remaining teams, using "SELECT DISTINCT away_team FROM table", also creating the ResultSet of Strings
// what is the code to compare the ResultSet's String values with the teamName values in the ArrayList and then add any Teams that haven't been added to the ArrayList

I believe the first algorithm is something like: (THIS IS PSEUDOCODE)

	ArrayList<Team> teamList

	Connection connectToDB()
	
	int arrayIndex = 0;

	ArrayList getTeams(Connection)
		run "SELECT DISTINCT teams FROM scores"
			resultSet.last()
			int numberOfRows = resultSet.getRow()
			resultSet.beforeFirst()
			
                        //loop through resultSet
			while(resultSet.next(){
				for(int i=0; i < resultSet.length; i++)
				teamList.add(new Team(resultSet.getString("home_team")) }  //create Team object, add to ArrayList

If I am on the right track, then I now need a comparison operation to add only the teams from the second query (the away_teams) that are not in the home_teams column. I'm not sure how to iterate through the existing Teams to determine which away_team results should be added to the ArrayList
(MORE PSEUDOCODE)

                        //loop through resultSet
			while(resultSet.next() {
				for(int i=0; i < resultSet.length; i++)
                                if (!teamList.contains(new Team(resultSet.getString("home_team") //if Team object doesn't exist in teamsList
                              { teamList.add(new Team(resultSet.getString("home_team"))  }}//create Team object, add to teamsList

Is this looking correct?

[Jhakda Velu]

Hi
I don't think your logic for checking if a team exists would work as you are creating new objects. So even if a team name returned by the second resultset is same as that already returned by the first resultset, doing a

  new Team(resultSet.getString("home_team")  

would result in creating a new object.

  
                Team a=new Team("A");
		Team b=new Team("A");
		List<Team> alTeamList= new ArrayList<Team>();
		alTeamList.add(a);
		alTeamList.add(b);
		if(alTeamList.contains(new Team("A"))){
			System.out.println("contains");
		}
		else{
			System.out.println("new");
		}
  

The above code will print new all the time

Think of other ways to do this and people here would help you come out with good solutions(managing through SQL itself, using a HashMap with "home_team" as key and Team object as value(assuming you need a Team object(containing other attributes that you may fetch from the Database) and not just team names),etc etc).

Jhakda

[Keith R Wegner]

Jhakda Velu wrote:
Think of other ways to do this and people here would help you come out with good solutions(managing through SQL itself, using a HashMap with "home_team" as key and Team object as value(assuming you need a Team object(containing other attributes that you may fetch from the Database) and not just team names),etc etc).

Jhakda

Could you show me pseudocode for this; I am not sure if I understand it.

[Jhakda Velu]

Hi
I would try to fetch only the required data by tweaking the SQL. This way, i need to hit the DB only once and i need not check for duplicates.
In case you wish to use the HashMap approach, the key will be a string(the team name fetched from DB) and value can be the Team object created while iterating through the second resultset,duplicate values will be rejected, but here, there is an overhead of extra iterations.

   
Team a=new Team("A");//say this came from first resultset
Team b=new Team("A");//say this came from second resultset

HashMap<String, Team> hm= new HashMap<String, Team>();

hm.put("A", a);
hm.put("A", b);//this entry will be rejected

 

Jhakda

[Keith R Wegner]

Jhakda Velu wrote:Hi
I would try to fetch only the required data by tweaking the SQL. This way, i need to hit the DB only once and i need not check for duplicates.
In case you wish to use the HashMap approach, the key will be a string(the team name fetched from DB) and value can be the Team object created while iterating through the second resultset,duplicate values will be rejected, but here, there is an overhead of extra iterations.

How would this only be hitting the SQL table once?

So I would then need to run select home_team, away_team, home_score_away_score from table from the table.

Could you show me the pseudocode that would use a HashMap to store the distinct teams fetched from the home_team and away_team columns?

Thanks..

[Jhakda Velu]

Hi
Can you give the exact table structure,that way i can hint about the possible query to use.
The HashMap example in my previous post should show you the direction. Try making a hashmap and add key value pairs to it and test.

[Keith R Wegner]

Jhakda Velu wrote:Hi
Can you give the exact table structure,that way i can hint about the possible query to use.
The HashMap example in my previous post should show you the direction. Try making a hashmap and add key value pairs to it and test.

Column Data __Type
game_id_______Integer
home_team____String
away_team____String
home_score____Integer
away_score____Integer
game_note_____String

The goal of pulling the data will be to create the following XML document

<lacrosse>
<title>College Lacrosse Results</title>
_____<team>
________team_name
________<wins>number of wins</wins>
________<losses>number of losses</losses>
________<opponent>
_____________opponent_name
_____________<location>home or away</location>
_____________<for>goals scored</for>
_____________<against>goals given up</against>
________</opponent>
________<opponent>
________:
________</opponent>
________:
_____</team>
_____<team>
_____:
_____</team>
</lacrosse>

I'm also not 100% sure what the Team class' data structure should look like as far as implementing the opponent data.

I'm guessing I should make an inner class Opponent

[Jhakda Velu]

Hi
Is this a college assignment? I think it is as this thread was started by someone else and now being carried forward by you.

I won't give you the complete solution,but would give you a few pointers.
Use order by clause in your sql(say on home team) to get teams in a particular order.
While iterating through your resultset create a new instance of your Team VO if the previous team_name and the current is not the same(this is the reason for order by) .You will also need 2 variables to count number of wins and losses. This should be reset to 0 every time a new team is encountered.

You can have a Arraylist for opponents,keeping in mind that data will repeat every 4 elements(opponent_name,location, for,against).

Each Team VO should then be added to a collection which should be used to form the XML.



Best wishes
Jhakda

[Keith R Wegner]

Yes, he must be in a different section of this course

Jhakda Velu wrote:Hi
I won't give you the complete solution,but would give you a few pointers.
Use order by clause in your sql(say on home team) to get teams in a particular order.
While iterating through your resultset create a new instance of your Team VO if the previous team_name and the current is not the same(this is the reason for order by) .You will also need 2 variables to count number of wins and losses. This should be reset to 0 every time a new team is encountered.

You can have a Arraylist for opponents,keeping in mind that data will repeat every 4 elements(opponent_name,location, for,against).

Each Team VO should then be added to a collection which should be used to form the XML.

So, my SQL query should be SELECT home_team, away_team, home_score, away_score FROM table ORDER BY home_team

Now, how am I to retrieve the teams that were only in the away_team column

Example:

HOME_TEAM    AWAY_TEAM
A                              C
A                              [b]E[/b]
B                              A
D                              B
C                             D