Solved Prepared Statement being null

Discussion in 'Plugin Development' started by Dpasi314, Jul 1, 2013.

Thread Status:
Not open for further replies.
  1. Offline

    Dpasi314

    Hello, I decided to rewrite my shop plugin, and use MySQL for the database storage. I've been having major issues with the MySQL portion though.

    What I'm trying to attempt to do is: prepare a statement, and have it be executed. However, when I call the function to do this, it throws a NullPointerException.

    This is the line throwing the exception:
    Code:
    PreparedStatement st = con.prepareStatement(addItem);
    This is the class (Up to the method):
    MySQL Class (open)

    Code:
    package com.nedcraft.dpasi314.DynamicMarket.Handlers;
     
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
     
    import java.util.logging.Level;
    import com.nedcraft.dpasi314.DynamicMarket.DynamicMarket;
     
    public class MySQLHandler {
        DynamicMarket plugin;
        private  Connection con;
     
        public MySQLHandler(DynamicMarket plugin, Connection con){
            this.con = con;
            this.plugin = plugin;
        }
     
        public String servername = "localhost";
        public String port = "3306";
        public String database = "DynamicMarket";
        public String username = "root";
        public String password = "";
        public String url = "jdbc:mysql://" + servername +":"+ port +  "/" + database;
     
     
        /*public String servername = plugin.getConfig().getString("DynamicMarket.Database.ServerName");
        public String port = plugin.getConfig().getString("DynamicMarket.Database.Port");
        public String database = plugin.getConfig().getString("DynamicMarket.Database.Database");
        public String username = plugin.getConfig().getString("DynamicMarket.Database.Username");
        public String password = plugin.getConfig().getString("DynamicMarket.Database.Password");
        public String url = "jdbc:mysql://" + servername +":"+ port +  "/" + database; */
     
        public void createItemTable(String tablename1, String tablename2){
            try{
             
                Statement st = con.createStatement();
             
                String useDatabase = "USE " + database + ";";
                st.executeUpdate(useDatabase);
             
                String createTableItems = "CREATE TABLE IF NOT EXISTS `" + tablename1 + "`"
                        + " (" +
                        "`Item` varchar(255) PRIMARY KEY," +
                        "`ID` INTEGER," +
                        "`Price` DOUBLE," +
                        "`Static` BOOLEAN," +
                        "`Buyable` BOOLEAN," +
                        "`Sellable` BOOLEAN," +
                        "`Alias` varchar(255)" +
                        ");";
             
                st.executeUpdate(createTableItems);
             
                String createTableTransactions = "CREATE TABLE IF NOT EXISTS `" + tablename2 + "`"
                        + " ("+
                        "`Player` varchar(255) PRIMARY KEY," +
                        "`Item` varchar(255),"+
                        "`Amount` INTEGER, " +
                        "`Type` varchar(255), "+
                        "`Time` varchar(255)"+
                        ");";
             
                st.executeUpdate(createTableTransactions);
            } catch (SQLException e){
                System.out.println("ERROR: Could not create tables!");
            }
        }
     
        public void addTransaction(String player, String item, int amount, String type, String timeString, String tablename){
            try{
             
                String addTransaction = "INSERT INTO " + tablename + " VALUES (?,?,?,?,?);";
                PreparedStatement st = con.prepareStatement(addTransaction);
             
                st.setString(1, player);
                st.setString(2, item);
                st.setString(3, String.valueOf(amount));
                st.setString(4, type);
                st.setString(5, timeString);
             
                st.executeUpdate(addTransaction);
            } catch (SQLException e){
                MessageHandler.sendConsoleErrorMessage(Level.SEVERE, "Could not add transaction!");
            }
        }
        public void addItem(String item, int ID, double cost, boolean isStatic, boolean isBuyable,
                boolean isSellable,String alias, String tablename){
            try{
             
                String addItem = "INSERT INTO " + tablename + " VALUES (?,?,?,?,?,?,?);";
                PreparedStatement st = con.prepareStatement(addItem);
             
                st.setString(1, item);
                st.setString(2, String.valueOf(ID));
                st.setString(3, String.valueOf(cost));
                st.setString(4, String.valueOf(isStatic));
                st.setString(5, String.valueOf(isBuyable));
                st.setString(6, String.valueOf(isSellable));
                st.setString(7, alias);
             
                st.executeUpdate(addItem);
             
         
            } catch (SQLException e){
                MessageHandler.sendConsoleErrorMessage(Level.SEVERE, "Could not add item to database!");
            }
        }
        


    This is the command I'm attempting to use:
    Command Class (open)

    Code:
    import java.sql.Connection;
     
    import org.bukkit.ChatColor;
    import org.bukkit.Material;
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandExecutor;
    import org.bukkit.command.CommandSender;
    import org.bukkit.entity.Player;
     
    import com.nedcraft.dpasi314.DynamicMarket.DynamicMarket;
    import com.nedcraft.dpasi314.DynamicMarket.Handlers.MessageHandler;
    import com.nedcraft.dpasi314.DynamicMarket.Handlers.MySQLHandler;
     
    public class AddCommand implements CommandExecutor{
     
        DynamicMarket plugin;
     
        MySQLHandler MySQL;
        private Connection connection;
     
        public AddCommand(DynamicMarket plugin, Connection connection){
            this.plugin = plugin;
            this.connection = connection;
        }
        @Override
        public boolean onCommand(CommandSender sender, Command command, String label,
                String[] args) {
         
            MySQL = new MySQLHandler(plugin, connection);
            Player player = (Player) sender;
         
            if(!(player.hasPermission("dynamicmarket.admin"))){
                MessageHandler.NoPermission(player, "dynamicmarket.admin");
                return true;
            }
         
            if(args.length != 7){
                MessageHandler.InvalidCommandSyntax(player, "/shop add <item:ID> <starting price> <static> <buyable> <sellable> [alias]");
                return true;
            }
         
            String item = Material.matchMaterial(args[1]).toString();
            int ID = Material.matchMaterial(args[1]).getId();
            double startingPrice = Double.parseDouble(args[2]);
            boolean isStatic = Boolean.parseBoolean(args[3]);
            boolean isBuyable = Boolean.parseBoolean(args[4]);
            boolean isSellable = Boolean.parseBoolean(args[5]);
            String alias = args[6];
         
            String tablename = "DynamicMarket-Items";
         
            MySQL.addItem(item, ID, startingPrice, isStatic, isBuyable, isSellable, alias, tablename);
            MessageHandler.sendEncasedMessage();
            player.sendMessage(ChatColor.YELLOW + "Item: " + ChatColor.AQUA + item + ChatColor.YELLOW + " has been added with the following statistics:");
            player.sendMessage(ChatColor.YELLOW + "Item Name: " + ChatColor.AQUA + item);
            player.sendMessage(ChatColor.YELLOW + "Starting Price: " + ChatColor.AQUA + startingPrice);
            player.sendMessage(ChatColor.YELLOW + "Static Priced: " + ChatColor.AQUA + isStatic);
            player.sendMessage(ChatColor.YELLOW + "Is Buyable: " + ChatColor.AQUA + isBuyable);
            player.sendMessage(ChatColor.YELLOW + "Is Sellable: " + ChatColor.AQUA + isSellable);
            player.sendMessage(ChatColor.YELLOW + "Alias: " + ChatColor.AQUA + alias);
            MessageHandler.sendEncasedMessage();
            return true;
        }
    }
    

    And this is the main class:
    Main Class (open)

    Code:
    package com.nedcraft.dpasi314.DynamicMarket;
     
    import java.io.File;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
     
    import org.bukkit.plugin.java.JavaPlugin;
     
    import com.nedcraft.dpasi314.DynamicMarket.Commands.MarketCommand;
    import com.nedcraft.dpasi314.DynamicMarket.Handlers.MySQLHandler;
     
    public class DynamicMarket extends JavaPlugin {
     
        private String prefix = "[Dynamic Market] ";
        public String servername = "localhost";
        public String port = "3306";
        public String database = "DynamicMarket";
        public String username = "root";
        public String password = "";
        public String url = "jdbc:mysql://" + servername +":"+ port +  "/" + database;
        public Connection connection = null;
        MySQLHandler MySQL;
     
        public void onEnable(){
         
            try{
                connection = DriverManager.getConnection(url, username, password);
              if(!connection.isClosed())
                System.out.println("Successfully connected to " +servername+ " MySQL server using TCP/IP.");
                  System.out.println("Using the database: " + database);
                  MySQL = new MySQLHandler(this, connection);
                  getCommand("shop").setExecutor(new MarketCommand(this,connection));
                  MySQL.createItemTable("DynamicMarket-Items", "DynamicMarket-Transactions");
                System.out.println("Done!");
     
            } catch(Exception e) {
              e.printStackTrace();
            } finally {
              try {
                if(connection != null)
                  connection.close();
              } catch(SQLException e) {}
        }
     
            File file = new File(getDataFolder(),"config.yml");
     
            if(!getDataFolder().exists()) getDataFolder().mkdir(); 
                    if(!file.exists()) { 
                        try {
                            file.createNewFile();
                            getConfig().options().copyDefaults(true);
                            getConfig().save(file);
                            getConfig().options().copyDefaults(false);
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            System.out.println(prefix +"ERROR: Dynamic Market has encountered an error: Could not create Information file.");
                        }
                    }
            System.out.println(prefix + "has been enabled!");
        }
     
        public void onDisable(){
            System.out.println(prefix + "has been disabled");
        }
     
    }
    


    I'm not sure I'm passing the connection in properly, or if I'm doing anything properly. I'd appreciate some help. Thanks!
     
  2. Offline

    hamgooof

    Have you tried making a log to make sure the connection isn't null with something like

    Code:
    System.out.println("Connection is null: " + (con==null));
    And for the connections with MySQL I use the following string
    Code:
    jdbc:mysql://IP/databasename?user=loginusername&password=loginpassword
    Edit: Are you passing it the connection, or creating it in the class? Because I see you have connection info in the classes, but I can't see you getting the connection using DriverManager?
     
  3. Offline

    Dpasi314

    Well, I'm passing in the connection to the SQLHandler through the main class. Which is making the Connection not null. Regular SQL Statements are parsed just fine.

    EDIT: I did a simple null test. The connection is NOT null.
     
  4. Offline

    slayr288

    Dpasi314
    You put you're query in prepareStatement, not a method (Which doesn't return a string). I'm surprised you're IDE didn't give you errors.
     
  5. Offline

    Dpasi314

    slayr288
    Nope no errors, it also turns out the connection goes Null when it's passed into the constructer. Also, Where am I putting my query in a prepareStatement? I'd like to know so that maybe I can fix it =P
     
  6. Offline

    hamgooof

  7. Offline

    Dpasi314

    Well I don't ever get passed the PreparedStatement. But in my other plugins that use MySQL it works fine. And i know why, because the connection goes null somewhere....
    In the main class it isn't null. When it's passed into the constructer it goes null. =L
     
  8. Offline

    LucasEmanuel

  9. Offline

    Dpasi314

    It's not the preparedstatment that's the issue ( I don't think) the connection is turning up null, and it shouldn't be>_<
    But I'll add spaces, see if it helps

    Yeah, LucasEmanuel that didn't help :L But thanks for the suggestions.
    hamgooof So yeah, the connection is going null. And I'm not sure why.
    It's not null here:
    Code:
    try{
                connection = DriverManager.getConnection(url, username, password);
              if(!connection.isClosed())
                System.out.println("Successfully connected to " +servername+ " MySQL server using TCP/IP.");
                  System.out.println("Using the database: " + database);
                  MySQL = new MySQLHandler(this, connection);
                  getCommand("shop").setExecutor(new MarketCommand(this,connection));
                  MySQL.createItemTable("DynamicMarket-Items", "DynamicMarket-Transactions");
                System.out.println("Done!");
               
                if(connection == null){
                    System.out.println("Connection is null!");
                } else {
                    System.out.println("Connection isn't null");
                }
     
            } catch(Exception e) {
              e.printStackTrace();
            } finally {
              try {
                if(connection != null)
                  connection.close();
              } catch(SQLException e) {}
        }
    
    But it's null here:

    Code:
    public class MySQLHandler {
        DynamicMarket plugin;
        private Connection con = null;
       
        public MySQLHandler(DynamicMarket plugin, Connection con){
            this.con = con;
            this.plugin = plugin;
        }
       
        public String servername = "localhost";
        public String port = "3306";
        public String database = "DynamicMarket";
        public String username = "root";
        public String password = "";
        public String url = "jdbc:mysql://" + servername +":"+ port +  "/" + database;
       
       
        /*public String servername = plugin.getConfig().getString("DynamicMarket.Database.ServerName");
        public String port = plugin.getConfig().getString("DynamicMarket.Database.Port");
        public String database = plugin.getConfig().getString("DynamicMarket.Database.Database");
        public String username = plugin.getConfig().getString("DynamicMarket.Database.Username");
        public String password = plugin.getConfig().getString("DynamicMarket.Database.Password");
        public String url = "jdbc:mysql://" + servername +":"+ port +  "/" + database; */
       
        public void createItemTable(String tablename1, String tablename2){
            try{
               
                Statement st = con.createStatement();
               
                String useDatabase = "USE " + database + ";";
                st.executeUpdate(useDatabase);
               
                String createTableItems = "CREATE TABLE IF NOT EXISTS `" + tablename1 + "`"
                        + " (" +
                        "`Item` varchar(255) PRIMARY KEY," +
                        "`ID` INTEGER," +
                        "`Price` DOUBLE," +
                        "`Static` BOOLEAN," +
                        "`Buyable` BOOLEAN," +
                        "`Sellable` BOOLEAN," +
                        "`Alias` varchar(255)" +
                        ");";
               
                st.executeUpdate(createTableItems);
               
                String createTableTransactions = "CREATE TABLE IF NOT EXISTS `" + tablename2 + "`"
                        + " ("+
                        "`Player` varchar(255) PRIMARY KEY," +
                        "`Item` varchar(255),"+
                        "`Amount` INTEGER, " +
                        "`Type` varchar(255), "+
                        "`Time` varchar(255)"+
                        ");";
               
                st.executeUpdate(createTableTransactions);
                if(con == null){
                    System.out.println("Connection is null!");
                }
            } catch (SQLException e){
                System.out.println("ERROR: Could not create tables!");
            }
        }
       
        public void addTransaction(String player, String item, int amount, String type, String timeString, String tablename){
            try{
               
                String addTransaction = "INSERT INTO " + tablename + " VALUES ( ?, ?, ?, ?, ?);";
                PreparedStatement st = con.prepareStatement(addTransaction);
               
                st.setString(1, player);
                st.setString(2, item);
                st.setString(3, String.valueOf(amount));
                st.setString(4, type);
                st.setString(5, timeString);
               
                st.executeUpdate(addTransaction);
               
       
            } catch (SQLException e){
                MessageHandler.sendConsoleErrorMessage(Level.SEVERE, "Could not add transaction!");
            }
        }
        public void addItem(String item, int ID, double cost, boolean isStatic, boolean isBuyable,
                boolean isSellable,String alias, String tablename){
            try{
                if(con == null){
                    System.out.println("Connection is null!");
                }
                String addItem = "INSERT INTO " + tablename + " VALUES (?,?,?,?,?,?,?);";
                PreparedStatement st = con.prepareStatement(addItem);
               
                st.setString(1, item);
                st.setString(2, String.valueOf(ID));
                st.setString(3, String.valueOf(cost));
                st.setString(4, String.valueOf(isStatic));
                st.setString(5, String.valueOf(isBuyable));
                st.setString(6, String.valueOf(isSellable));
                st.setString(7, alias);
               
                st.executeUpdate(addItem);
               
           
            } catch (SQLException e){
                MessageHandler.sendConsoleErrorMessage(Level.SEVERE, "Could not add item to database!");
            }
        }
        
    Any idea as to why?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 2, 2016
  10. Offline

    LucasEmanuel

    Dpasi314
    You don't seem to be initiating the driver :)
     
  11. Offline

    Dpasi314

    Code:
    connection = DriverManager.getConnection(url, username, password);
    I think I am?
     
  12. Offline

    LucasEmanuel

  13. Offline

    Dpasi314

  14. Offline

    hamgooof

    Which class calls addItem()?
     
  15. Offline

    Dpasi314

    the AddCommand class does.
    It's included in my first post.
     
  16. Offline

    hamgooof

    Sorry, lastly what inits the AddCommand class?
     
  17. Offline

    Dpasi314

    Sorry, read that weird, the MarketCommand initializes it.

    But I shouldn't' have to pass in the connection through either of those classes.

    Hey, hamgooof LucasEmanuel slayr288
    Just wanted to let you know that I fixed the issue, thank you for all of your help.
    Turns out something I should have caught. I was explicitly closing the connection.
    Code:
    if(connection != null)
                  connection.close();
    haha =P. Thanks again
    -Solved-

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 2, 2016
Thread Status:
Not open for further replies.

Share This Page