11 Replies - 1071 Views - Last Post: 02 May 2011 - 08:28 AM Rate Topic: -----

#1 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 03:49 AM

Hey all, I am trying to make my little TicTacToe game playable online, I have decided to do this over the internet but I am having trouble setting up the players, everything seems to work except this and I am guessing this is because you cant use a SQL query with a if expression?

Anyway, I decided this would be somewhat logical, the current user_name field is set to player1 and player2 for the two players. If when I click playOnline from my Swing application, I want to check these fields and if someone has already connected to the DB, then they would have most likely taken player1's fields and I change user_name which was 'player1' to 'home'. In the same instance, if someone else has connected and player1 is already changed, then I must change user_name for 'player2' to 'away'. Now I then would set their player values respectively (home = 1, away = 2) and then this way, they can only make an X or a O. After the game, I then reset the values in the DB to player1 and player2 so that another game can be played (gonna look @ multiple connections later)

I then compiled my code but I get an error, apparently a NullPointerException when I try and compare user_name for player1 and player2 to see if they are taken, however I have included the libraries and imports needed. Here is my relevant code:


    public void connectDB() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String connectionUrl = "jdbc:mysql://localhost/grid_db?" + "user=user&password=userpass";
            con = DriverManager.getConnection(connectionUrl);

        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.toString());
        } catch (ClassNotFoundException cE) {
            System.out.println("Class Not Found Exception: " + cE.toString());
        }
    }

       public void setupPlayers() {
        try {       
            if (rs.getString("user_name").equals("player1")) {
                String playerchange = "UPDATE user SET user_name = 'home' WHERE user_name = 'player1'";
                stmt.executeUpdate(playerchange);
                player = 1;
            }
            if (rs.getString("user_name").equals("player2")) {
                String playerchange = "UPDATE user SET user_name = 'away' WHERE user_name = 'player2'";
                stmt.executeUpdate(playerchange);
                player = 2;
            }
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.toString());
        }

    }


    public void cleanPlayers() {
        try {
            String playerchange = "UPDATE user SET user_name = 'player1'";
            stmt.executeUpdate(playerchange);
            String player2change = "UPDATE user SET user_name = 'player2'";
            stmt.executeUpdate(player2change);
        } catch (SQLException e) {
            System.out.println("SQL Exception" + e.toString());
        }
    }

    public void updatePlayersDB(int player, int xpos, int ypos) {
        try {
            if (player == 1) {
                String playerchange = "UPDATE user SET xpos = " + xpos + ",ypos = " + ypos + " WHERE user_name = 'home'";
                stmt.executeUpdate(playerchange);
                int boardxpos = rs.getInt("xpos WHERE user_name = 'player1'");
                int boardypos = rs.getInt("ypos WHERE user_name = 'player2'");
                board[ypos][xpos] = 1;
            }
            if (player == 2) {
                String playerchange = "UPDATE user SET xpos = " + xpos + ",ypos = " + ypos + " WHERE user_name = 'away'";
                stmt.executeUpdate(playerchange);
                int boardxpos = rs.getInt("xpos WHERE user_name = 'player1'");
                int boardypos = rs.getInt("ypos WHERE user_name = 'player2'");
                board[ypos][xpos] = 2;

            }
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.toString());
        }


    }


And now the Swing form code:



import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
public class MainFrame extends JFrame {
    BoardPanel noughtsAndCrosses;
  
    public MainFrame() {
        super("Noughts and Crosses");
        setSize(615, 675); //set Frame Size
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //set what occurs when the "X" button is pressed.
        setLocationRelativeTo(null);
        // Set up the Noughts and Crosses Panel
        noughtsAndCrosses = new BoardPanel();
        add(noughtsAndCrosses, BorderLayout.CENTER); //BorderLayout is a LayoutManager, it tells the computer how to arrange the visual elements on the screen.

        //Menu+Submenu UI:
        JPanel bottom = new JPanel();
        bottom.setBackground(Color.white);
        JMenuBar menuBar = new JMenuBar();
        JMenu menu = new JMenu("Game");

        ImageIcon resetIcon = new ImageIcon("newgame.gif");
        ImageIcon aboutIcon = new ImageIcon("info.gif");
        ImageIcon exitIcon = new ImageIcon("exit.gif");
        ImageIcon botIcon = new ImageIcon("computer.gif");
        ImageIcon onlineIcon = new ImageIcon("online.gif");
        ImageIcon humanIcon = new ImageIcon ("human.gif");
        JMenuItem resetItem = new JMenuItem("Reset Game", resetIcon);
        JMenuItem aboutItem = new JMenuItem("About Game", aboutIcon);
        JMenuItem exitItem = new JMenuItem("Exit Game", exitIcon);
        JMenuItem humanItem = new JMenuItem ("Play Human @ pc", humanIcon);
        JMenuItem botItem = new JMenuItem("Play Bot", botIcon);
        JMenuItem onlineItem = new JMenuItem("Play Online", onlineIcon);
        resetItem.setToolTipText("Reset Board");
        aboutItem.setToolTipText("About Game");
        exitItem.setToolTipText("Exit Game");
        botItem.setToolTipText("Play a Bot");
        onlineItem.setToolTipText("Play someone online");
        humanItem.setToolTipText("Play against a human at the same pc");
       /* **Toolbar UI: JButton resetButton = new JButton(image1);
        JButton aboutButton = new JButton(image2);
        JToolBar menu = new JToolBar();
        menu.add(resetButton);
        menu.add(aboutButton);
        bottom.add(menu);
        add(bottom, BorderLayout.NORTH);
        */
        // Make the button reset the panel
        resetItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                noughtsAndCrosses.reset();
                noughtsAndCrosses.requestFocus();
            }
        });
            aboutItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                new aboutDialog();     
            }
        });
          exitItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                JOptionPane.showMessageDialog(noughtsAndCrosses, "Thanks for Playing!", "Quit", JOptionPane.INFORMATION_MESSAGE);
                System.exit(0);
            }
        });  
           botItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {

            noughtsAndCrosses.bot = true;
            noughtsAndCrosses.online = false;
            noughtsAndCrosses.reset();
            noughtsAndCrosses.requestFocus();
            }
        });
         onlineItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {

                noughtsAndCrosses.online = true;
                noughtsAndCrosses.connectDB();
                noughtsAndCrosses.setupPlayers();
                noughtsAndCrosses.reset();
                noughtsAndCrosses.requestFocus();
            }
        });
        humanItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                noughtsAndCrosses.bot = false;
                noughtsAndCrosses.online = false;
                noughtsAndCrosses.reset();
                noughtsAndCrosses.requestFocus();
            }
        });
        menu.add(resetItem);
        menu.add(aboutItem);
        menu.add(humanItem);
        menu.add(botItem);
        menu.add(onlineItem);
        menu.addSeparator();
        menu.add(exitItem);
        menuBar.add(menu);
        setJMenuBar(menuBar);
        noughtsAndCrosses.requestFocus(); //make the panel the focus of the OS currently.
        // make the elements included above visible.
        setVisible(true);
    }
    public class aboutDialog extends JFrame {
        aboutDialog() {
                super("About noughtsAndCrosses");
                requestFocus();
                setSize(355,145);
                setLocationRelativeTo(noughtsAndCrosses);
                setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
                 FlowLayout flow = new FlowLayout();
        JTextArea label = new JTextArea("Welcome to this replication of the\n "
                                 + "classic noughtsAndCrosses game, also known\n as TicTacToe."
                                 + "players \n take turns applying various logical moves "
                                 + " to best \ntheir opponent by lining up"
                                 + " 3 X's or O's respectively.\n | version 1.0 coded by v0rtex |"
                                       );
        label.setEnabled(false);
        setLayout(flow);
        add(label);
        setVisible(true);
    }

    }
    public static void main(String[] args) {
        new MainFrame();
    }
}


I'm kinda stuck with this one, I will keep working on it.
Any help is appreciated,
thanks
v0rtex

This post has been edited by v0rtex: 02 May 2011 - 03:58 AM


Is This A Good Question/Topic? 0
  • +

Replies To: using an IF statement to compare data in my mySQL DB

#2 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2639
  • View blog
  • Posts: 11,148
  • Joined: 20-September 08

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 03:58 AM

This all seems excessively complex. Why not just flag the user names available as 'taken' in the db?
Was This Post Helpful? 1
  • +
  • -

#3 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 03:59 AM

I am unsure as to how to do that, I'm very new to mySQL, if you have any code/tutorials on how to do that g00se, I would be very appreciative :)
Was This Post Helpful? 0
  • +
  • -

#4 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2639
  • View blog
  • Posts: 11,148
  • Joined: 20-September 08

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 04:06 AM

Add the field 'taken' of type bit to the table. When a user connects, you will execute 'select username from foo where taken = 0'. That will give you a free slot (limit 1) or slots. Take the first. When that user has connected, you will update 'taken' to 1
Was This Post Helpful? 0
  • +
  • -

#5 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 04:18 AM

I tried
INSERT INTO user
(taken BIT NOT NULL AUTO_INCREMENT)
VALUES 
(0);


To no avail, must I recreate my Table?

Sorry, I only started reading up on mySQL databases yesterday :/
Was This Post Helpful? 0
  • +
  • -

#6 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2639
  • View blog
  • Posts: 11,148
  • Joined: 20-September 08

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 04:56 AM

You'd need an ALTER TABLE statement, but considering the circumstances, it's probably better to redefine in the CREATE TABLE statement
Was This Post Helpful? 0
  • +
  • -

#7 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 05:17 AM

Thanks a lot, the bit operator did not work in SQL. Is the bit flag different in SQL?
I tried:
taken BIT NOT NULL AUTO_INCREMENT


?
Any help is appreciated.

EDIT: Whilst creating my table using CREATE TABLE
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5761
  • View blog
  • Posts: 12,574
  • Joined: 16-October 07

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 05:42 AM

How do you expect this to work?
String playerchange = "UPDATE user SET user_name = 'player1'";
stmt.executeUpdate(playerchange);
String player2change = "UPDATE user SET user_name = 'player2'";
stmt.executeUpdate(player2change);



Make a class that is completely in charge of database stuff. Make it separate. That may clarify your logic.

e.g.
class DataManager {
	private final String connectionUrl
	
	public DataManager(String user, String pass) throws ClassNotFoundException {
		connectionUrl = "jdbc:mysql://localhost/grid_db?" + "user=" + user + "&password=" + pass;
		Class.forName("com.mysql.jdbc.Driver");
	}

	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection(connectionUrl);
	}

	public void changeUserName(String fromName, String toName) throws SQLException {
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = getConnection();
			prep = conn.prepareStatement("UPDATE user SET user_name = ? WHERE user_name = ?");
			prep.setString(1, toName);
			prep.setString(2, fromName);
			prep.execute();
		} finally {
			if (prep!=null) try { prep.close(); } catch (SQLException e) { }
			if (conn!=null) try { conn.close(); } catch (SQLException e) { }
		}
	}
//...


Was This Post Helpful? 2
  • +
  • -

#9 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 05:58 AM

Thanks a lot, Ill give that a try. I'm just currently struggling to determine if someone is say already player1, then the next user must be player2. I'm struggling with how to check that in the database. Thanks though all.
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5761
  • View blog
  • Posts: 12,574
  • Joined: 16-October 07

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 06:44 AM

You're not using the database as a huge data store, just as a persistence mechanism. So, for the moment, forget about your database.

Write code with methods that update changes to the users state calling methods. You probably want a Player class. For now, it just runs in memory; changes lost when you end the program. When you're ready, you make those methods read and write to a database as well.
Was This Post Helpful? 0
  • +
  • -

#11 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2639
  • View blog
  • Posts: 11,148
  • Joined: 20-September 08

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 06:52 AM

Quote

taken BIT NOT NULL AUTO_INCREMENT



Try instead

Quote

taken smallint


bit only works with MyISAM. You don't want autoincrement
Was This Post Helpful? 0
  • +
  • -

#12 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Re: using an IF statement to compare data in my mySQL DB

Posted 02 May 2011 - 08:28 AM

Okay, I'm going to go back to the drawing board, thanks baavgai and g00se
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1