Results 1 to 11 of 11
  1. #1
    caryr is offline Member
    Join Date
    Nov 2008
    Location
    Colorado
    Posts
    18
    Rep Power
    0

    Default Help with understanding how to get records out of a table.

    Okay I can get all the information into my table through Java. My question comes in how to get it out of the table through Java. I know each person will have more then one entry in 3 of my tables for the three different games they can play. I was wondering how to get all the records out of the table. Here is how am trying to do it and I know its not right because I do not get all the records for the person. I only get about half. If its and even number I get half if its odd I get less than half.
    I do not need to get the first column in the table because it is only used in the database to reference the players by a number and not a String.
    Java Code:
          public void getGame1Info(String studentNumber, ArrayList AllInfoForStudent)
          {
            connectToDB();
            try
            {
                // Gets the information out of the tblstudentInfo about the student and places it in the arraylist named
                // AllInfoForStudent except the studentNumber the studentNumber used in the database only.
              ResultSet rs = statement.executeQuery("SELECT * FROM tblgame1 WHERE studentNumber= '"+studentNumber+"'");
              int count = 1;
              while(rs.next())
              {
            	  System.out.println("rs.getString(1): " + rs.getString(1));
            	  if(rs.getString(1).equalsIgnoreCase(studentNumber))
            	  {
            		  System.out.println("ifrs.getString(1).equals: " + count);
                 	  	if(rs.next())
                     	 {
                     	      AllInfoForStudent.add(rs.getString(2));
                      	      AllInfoForStudent.add(rs.getString(3));
                       	      AllInfoForStudent.add(rs.getString(4));
                       	      AllInfoForStudent.add(rs.getString(5));
     			      System.out.println("ADDED INFO");
                      	 }	// end if
            	  } 		// end if
       
            	  System.out.println("Count: " + count);
            	  count++;
              } // end while
              rs.close();                                      // Close the ResultSet
              statement.close();                               // Close the statement
              connection.close();                              // close the connection.
            }// End try
            catch (SQLException e)
            {
              e.printStackTrace();
              System.out.println("Error in getStudentInfo: " + e);
            } // end catch        
          } // end public void getGame1Info()

  2. #2
    OrangeDog's Avatar
    OrangeDog is offline Senior Member
    Join Date
    Jan 2009
    Location
    Cambridge, UK
    Posts
    838
    Rep Power
    6

    Default

    What is the design of the table? If what is an even number? Shouldn't studentNumber be a number and not a string?
    Don't forget to mark threads as [SOLVED] and give reps to helpful posts.
    How To Ask Questions The Smart Way

  3. #3
    caryr is offline Member
    Join Date
    Nov 2008
    Location
    Colorado
    Posts
    18
    Rep Power
    0

    Default

    Quote Originally Posted by OrangeDog View Post
    What is the design of the table? If what is an even number? Shouldn't studentNumber be a number and not a string?
    The studentNumber in the database is and integer. However when I work when I was placing it (studentnumber) into the database through Java it liked for it to be a string. Which works and it does store it as an integer.

    Java Code:
      statement.executeUpdate("CREATE TABLE tblGame1(studentNumber int," 
                + "date DATE," + "audio varchar(3)," + "level varchar(10)," + "numberCorrect int(4))");
    Each level only has 10 questions. So I'm not storing how many question are in each level. The questions just get harder for each level.

  4. #4
    OrangeDog's Avatar
    OrangeDog is offline Senior Member
    Join Date
    Jan 2009
    Location
    Cambridge, UK
    Posts
    838
    Rep Power
    6

    Default

    Things are going to be a lot easier if you use the correct types. Even in your SQL query you are using the string 'studentNumber' instead of the number studentNumber.

    Can you give an example of a query and the expected vs. actual results.
    Don't forget to mark threads as [SOLVED] and give reps to helpful posts.
    How To Ask Questions The Smart Way

  5. #5
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    When I work with databases I find it easier to get my queries down before I try to program it... You also want to make sure that you database is normalized... Google it and you will find lots of other tutorials and examples...

    If you post your database designs, database name and your queries, I can help you normalize it... Because as of right now if two or more people have the same student number you will pull all the data for those people... To correct that, what you want to do is in your main table set the student number as your primary key and have it be of a serial type*... In your other two tables you want the student number to be a foreign key... This way they can only exist if the student number exists in your main table...


    *I use a Postgres database and the serial type is auto incrementing...
    Who Cares... As Long As It Works...

  6. #6
    caryr is offline Member
    Join Date
    Nov 2008
    Location
    Colorado
    Posts
    18
    Rep Power
    0

    Default

    Sorry it has taken a while to get back to you I was in class last night.

    Quote Originally Posted by OrangeDog View Post
    Things are going to be a lot easier if you use the correct types. Even in your SQL query you are using the string 'studentNumber' instead of the number studentNumber.

    Can you give an example of a query and the expected vs. actual results.
    I have two students in the database for the game. the first student has 5 entries in the table when you look at the database in a command window.
    When I get the results back from Mysql (numbergames) through Java it only shows 2 of the result sets. Player 2 has only one entry and it show in a
    command window as being there however Java does not show it at all.



    Quote Originally Posted by markw8500 View Post
    When I work with databases I find it easier to get my queries down before I try to program it... You also want to make sure that you database is Google it and you will find lots of other tutorials and examples...

    If you post your database designs, database name and your queries, I can help you normalize it... Because as of right now if two or more people have the same student number you will pull all the data for those people... To correct that, what you want to do is in your main table set the student number as your primary key and have it be of a serial type*... In your other two tables you want the student number to be a foreign key... This way they can only exist if the student number exists in your main table...


    *I use a Postgres database and the serial type is auto incrementing...
    Its telling me I have not posted enough time to be able to add an attachment. I was going to attach my program in a zip folder.
    This is how I have the database set up.
    create database NumberGames;

    "CREATE TABLE tblStudentInfo(studentNumber int NOT NULL AUTO_INCREMENT, schoolStudentID varchar(20), studentName varchar(50), grade varchar(2), gender varchar(2), race varchar(20),
    homeLanguage varchar(20), DOB DATE, teacherName varchar(50), primary key(studentNumber);

    CREATE TABLE tblGameInfo(gameID int(4) NOT NULL AUTO_INCREMENT, gameName varchar(20), numberOfLevels varchar(2), numberOfQuestions int(2), primary key(gameID));

    CREATE TABLE tblGameLevel(studentNumber int, game1Level varchar(10), game2Level int, game3Level varchar(10));

    CREATE TABLE tblGame1(studentNumber int, date DATE, audio varchar(3), level varchar(10), numberCorrect int(4));

    CREATE TABLE tblGame2(studentNumber int, date DATE, audio varchar(3), level varchar(10), numberCorrect int(4));

    CREATE TABLE tblGame3(studentNumber int, date DATE, audio varchar(3), level varchar(10), numberCorrect int(4));

    Insert the game names, number of levels and the number of question for each game into tblGameInfo.
    INSERT INTO tblGameInfo(gameName, numberOfLevels, numberOfQuestions) VALUES('5 Ten Frames', '5', '10');

    INSERT INTO tblGameInfo(gameName, numberOfLevels, numberOfQuestions) VALUES('Stroop Test', '5', '10');

    INSERT INTO tblGameInfo(gameName, numberOfLevels, numberOfQuestions) VALUES('Take Away', '5', '10');

  7. #7
    OrangeDog's Avatar
    OrangeDog is offline Senior Member
    Join Date
    Jan 2009
    Location
    Cambridge, UK
    Posts
    838
    Rep Power
    6

    Default

    I meant an example of the actual SQL query, the content of the table you execute it on, the expected result set and the actual result set.

    The problem is probably because you don't have proper primary/foreign keys on your tables.
    Don't forget to mark threads as [SOLVED] and give reps to helpful posts.
    How To Ask Questions The Smart Way

  8. #8
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    Third time is a charm... I tried to post a reply but it would not let me until now...

    OrangeDog is correct when he says:

    The problem is probably because you don't have proper primary/foreign keys on your tables.

    And if it is not the problem now... It will be eventually... Primary and Foreign keys maintain what is called Referential Integrity... For example in your tblGameLevel table, I can insert a row of data using a student number that does not exist... Which we will fix...


    The next thing we will look at is your database design... The biggest question always is "How do we pick our tables"... I was always tought to pick tables by pulling out the nouns in your problem... In our problem the nouns would be students and games... So this is where we will start...

    Games:

    In your games table each game will have one and only one:

    -gameID
    -gameName
    -totalNumberOfLevels
    -totalNumberOfQuestions

    The primary key will be gameId... So your table design will work nicely...


    Java Code:
    CREATE TABLE tblGameInfo(
    	gameID int(4) AUTO_INCREMENT, 
    	gameName varchar(20), 
    	numberOfLevels varchar(2), 
    	numberOfQuestions int(2), 
    	primary key(gameID)
    
    );
    Now as we continue to analize your games, notice that each game will also have many game levels... So... With this we can create a new table gameLevels...

    Each gameLevel will be a part of one and only one game...

    Java Code:
    
    CREATE TABLE gameLevels(
    	levelName varchar(10), 
    	gameId int(4), 
    	Primary Key(levelName, gameId), 
    	Foreign Key(gameId) references tblGameInfo(gameid) ON UPDATE CASCADE
    );

    Notice that in this table we will implement a composit primary key, a foreign key and ON UPDATE CASCADE...


    The composit primary key will allow you to have multiple levelNames that are the same as long as the games are different... Which can occur...

    The foreign key will only allow an entry if the gameId exists in the tblGameInfo table... If the gameId does not exist, MySql will not allow you to insert that record...

    The ON UPDATE CASCADE is pretty neat... This will automatically update the gameId in this table if the gameId is updated in your tblGameInfo table...



    Now on the the Student...

    In this table you have both a studentNumber and a schoolStudentID... If all schoolStudentID will be unique, you can probably get rid of studentNumber... But if they are not or if you are not sure, then it probably is best that we keep both...


    So... Each student will have one and only one:

    -studentNumber
    -schoolStudentID
    -studentName
    -gender
    -homeLanguage

    -teacherName ???

    -if each student will only have one teacher, then this will work... If each student will have multiple teachers, then we will have to create a new table... For now I will assume that each student will have only one teacher...


    Java Code:
    CREATE TABLE tblStudentInfo(
    
    	studentNumber int  AUTO_INCREMENT,
    	schoolStudentID varchar(20), 
    	studentName varchar(50), grade varchar(2), 
    	gender varchar(2), race varchar(20), 
    	homeLanguage varchar(20), DOB DATE, 
    	teacherName varchar(50), 
    	primary key(studentNumber)
    
    );
    --Again your table will work nicely...





    Now it gets tricky...


    What we have to do is somehow link students to games... We can create a new table called gameSatus... Or whatever you like...

    So... I think it is best to show you the table first then explain it...


    Java Code:
    CREATE TABLE gameStatus(
    	studentNumber int, 
    	gameID int(4), 
    	levelName varchar(10), 
    	date Date, audio varchar(3), 
    	numCorrect  int(4), 
    	primary key(studentNumber, gameId, levelName), 
    	Foreign Key(studentNumber) References tblStudentInfo(studentNumber) ON UPDATE CASCADE, 
    	Foreign Key(levelName, gameId) references gameLevels(levelName, gameId) ON UPDATE CASCADE
    );

    Now in this table the composit primary key of studentNumber, gameId and levelName will allow you to insert multiple, identical:

    -studentNumbers
    -gameIds
    -levelNames

    But it will not allow to have the same studentNumber, gameId and levelName in two rows...

    As you already know that the foreign keys will not allow you to insert a row if their counter part primary key does not exist...

    And the ON UPDATE CASCADE will automatically update the data if it gets updated in the counter part table...


    So go ahead and build this database and practice quering it... If you get stuck let me know...

    .
    Last edited by markw8500; 04-29-2009 at 05:33 PM.
    Who Cares... As Long As It Works...

  9. #9
    2potatocakes is offline Member
    Join Date
    Sep 2008
    Posts
    43
    Rep Power
    0

    Default

    I don't know, maybe I'm reading your question differently to the other guys but I'm pretty sure you're only getting half or occassionally half because you're calling rs.next() twice

    think of each resultSet as calling each row
    and each time you use rs.getString(1), or (2), or (3) you're calling each column of that row

    but if you use
    while(rs.next())
    {
    System.out.println("rs.getString(1): " + rs.getString(1));
    if(rs.getString(1).equalsIgnoreCase(studentNumber) )
    {
    System.out.println("ifrs.getString(1).equals: " + count);
    if(rs.next())
    {

    You're not going to be getting all of your data back out if you do it that way. That conditional if statement in the middle is gunna split your data

  10. #10
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    True... Calling rs.next() is the underling problem in this post... But if caryr does not fix the database, it is only a matter of time before all the data is mismatched...
    Who Cares... As Long As It Works...

  11. #11
    caryr is offline Member
    Join Date
    Nov 2008
    Location
    Colorado
    Posts
    18
    Rep Power
    0

    Default

    Yes my problem was I was calling rs.next() twice.
    I want to thank all of you for the great help you have given me.

    markw8500 thanks for the help with understanding MySql. I was given this project with no training at all in MySQL by the instructor of my advanced Java class.

    OrangeDog you have also been a great help.

    Thank you all.
    Cary

Similar Threads

  1. Help understanding packages and classpaths
    By porchrat in forum New To Java
    Replies: 2
    Last Post: 04-24-2009, 10:22 PM
  2. I have trouble understanding this program!
    By PureAwesomeness in forum New To Java
    Replies: 1
    Last Post: 03-16-2009, 06:34 AM
  3. [SOLVED] Understanding if else statements
    By hungdukie in forum New To Java
    Replies: 6
    Last Post: 11-18-2008, 08:56 AM
  4. Replies: 0
    Last Post: 02-09-2008, 09:31 PM
  5. Understanding Vectors
    By cbrown08 in forum New To Java
    Replies: 7
    Last Post: 11-05-2007, 07:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •