Help with MYSQL SELECT

I'm trying to get a super simple MYSQL SELECT query to work but have been trying for hours and can't find anything to help me.

You can re-create my exact database with this:

PHP:

CREATE TABLE IF NOT EXISTS `priority` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_name` varchar(255) NOT NULL,
  `priority` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
 
--
-- Dumping data for table `priority`
--
 
INSERT INTO `priority` (`id`, `player_name`, `priority`) VALUES
(1, 'h8crew', 9000);

So I've got my id, player_name and priority. I'm just trying to query the priority of any given player and this is the code I'm using:

PHP:

public void checkPriority(Player p)
  {
      Connection con = null;
      try {
          PreparedStatement pst = null;
          String database = "minetest";
          con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/minetest", "minetest", "minepass");
          ResultSet rp1 = null;
          Statement stat = (Statement) con.createStatement();
          pst = (PreparedStatement) ((StatementImpl) con).executeQuery("SELECT * FROM `priority` WHERE  `player_name` = `" + p.getName() + "`;");
          int prio = ((ResultSet) pst).getInt(0);
          Bukkit.broadcastMessage("PRIORITY = " + prio);
          con.close();
      } catch (SQLException ex) {
    }
  }

And I'm importing:

PHP:

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.StatementImpl;

 

Why are you casting the Connection to StatementImpl, execute a query (which I assume would return a ResultSet) and cast that to a prepared statement. And why cast that to a ResultSet and get the first int?

 

I have no idea how prepared statements work, but for the query, if you're just trying to get one value, you could replace * with 'value'

So basically: SELECT 'priorty'

Also, a tip:
When inserting into a table, and you have an ID field that is auto incrementng, you can insert into it and it will do It for you if you ignore the ID colum, ex:
INSERT INTO `priority` ( `player_name`, `priority`) VALUES ("skipperguy12", 9999)
This will insert skipperguy12 with 9999 on the next ID.

 

Sorry I've never really used MYSQL before, I'm just grabbing bits and parts of code I've found just trying to Frankenstein something that works. I have basically no idea what I'm doing and just need the code to query the priority column of a given player name.

 

Even in Java terms this is just horrible ;D

 

I havent much clue on the actual java programming side of it, but for an sql query to return the priority of a column when searching for a given player name something such as "select 'priority' from 'table' where 'player_name' = "playername" " should be more than sufficient. You already know what player name you are looking for so you do not really need to return both the players name and their priority in a query result with regards to programming unless you are trying to do a where name like to attempt a "search"(select * from 'table' where'player_name' like "partial name to search for" Just remember with where like statements you could end up with multiple results).

 

I've created few plugins with MySQL support and I remember I had heaps of touble at the beginning my self
You can look at my MySQL class here: https://github.com/lololmaker/Ahola.../aholacraft/lololmaker/aholaban/AholaSQL.java

You basically need:
1. Connection string(URL):

PHP:

jdbc:mysql://HOST:PORT/DATABASE

2. You need to connect:

PHP:

Connection con = DriverManager.getConnection(URL, USER, PASSWORD);

3. I like to use prepared statements:

PHP:

PreparedStatement ps = connectionYouEstablishedBefore.prepareStatement(String);

4. Make sure if you use prepared statements you use correct method, use ps.executeQuery(); if you are getting information, i.e. using SELECT. When using this you usually need to initialize ResultSet with the return value of .executeQuery();
When you are using INSERT and basically edits the data in database, use ps.executeUpdate();

If you have any questions, feel free to post/PM me.

 

Thanks so much for the help! I ended up getting this far but still am having trouble. Here is what I have now:

PHP:

//This is where I call the function:
 
if (cmd.getLabel().equalsIgnoreCase("test"))
    {
        Player p = (Player)cmdSender;
        try {
            Bukkit.broadcastMessage("About To Check Priority!");
            Bukkit.broadcastMessage("" + checkPriority(p));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


/////////////////////////////////////
// This is the MYSQL function
/////////////////////////////////////

PHP:

public int checkPriority(Player p) throws SQLException {
      java.sql.PreparedStatement ps = null;
      ResultSet rs = null;
      int pri = 0;
      try
        {
          java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/minetest", "minetest", "minepass");
          ps = conn.prepareStatement("SELECT priority FROM priority WHERE player_name = '" + p.getName() + "'");
          rs = ps.executeQuery();
          ps.close(); //Closes the query
          conn.close(); //Closes the connection
          pri = rs.getInt(1);
          return pri;
        } catch (SQLException e) {
          e.printStackTrace();
        }
    return pri;
  }

 

It seems that you forgot to execute

Code:

conn.open();

EDIT: Also I don't think you need to use throws SQLException since you are already using try catch statement

 

conn.open(); doesn't seem to be a valid command nor is it used in your link: https://github.com/lololmaker/Ahola.../aholacraft/lololmaker/aholaban/AholaSQL.java

 

Sorry I'm kind of sleepy.

The problem you are experiencing is that when you retrieve ResultSet, pointer is always before first index, you you have to move it to first index by calling rs.next().
Also if you ever need to check if ResultSet is empty, do if(rs.isBeforeFirst() && rs.isAfterLast())
I believe that's how methods are called, you will see pop it up in your IDE anyway

PHP:

          java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/minetest", "minetest", "minepass");
          ps = conn.prepareStatement("SELECT priority FROM priority WHERE player_name = '" + p.getName() + "'");
          rs = ps.executeQuery();
          ps.close(); //Closes the query
          conn.close(); //Closes the connection
          while(rs.next()){
              pri = rs.getInt("priority"); //Personally I think the code is more readable this way
              return pri;
          }
          //Add code below this if there were no entries

Since this is about getting value from one player only, you can use if statement instead of while, it's your choice.

Cheers

 

Thanks for the help guys, I was stupid and was closing the resultSet and the connection before doing what I needed to do. For anyone interested my code ended up being this:

PHP:

public int checkPriority(Player p) throws SQLException {
      PreparedStatement ps = null;
      ResultSet rs = null;
      int pri = 0;
      try
        {
          Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/minetest", "minetest", "minepass");
          ps = conn.prepareStatement("SELECT * FROM priority WHERE player_name = '" + p.getName() + "'");
          rs = ps.executeQuery();
          while(rs.next()){
              pri = rs.getInt("priority");
              return pri;
          }
          ps.close(); //Closes the query
          conn.close(); //Closes the connection
        } catch (SQLException e) {
          e.printStackTrace();
        }
    return pri;
  }

 

Glad you solved your problem

Btw, also ps.close() and conn.close() won't have any effect unless resultset is empty, but it anywhere before return statement. If you close connection and PreparedStatement, that doesn't affect ResultSet in any way after it's assigned.


Now I was scheduled for removal (Read: ban; discouragement) by Bukkit team, guess they don't accept complaints. So I guess I won't be in much help, at least not on this account. ha.