spacegeek224 spacegeek224 - 10 days ago 5
Java Question

NullPointerException and Parameter index out of range exception using MySQL & PreparedStatement

I am getting this error when using MySQL, and cannot figure out how to fix it. Ever since I switched from

Connection.execute[whatever]()
, to
PreparedStatement
, I've been getting this error, but can't seem to fix it.

NullPointerException:

guild info space
[09:51:56 WARN]: java.lang.NullPointerException
[09:51:56 WARN]: at net.aspace.guilds.Guild.getGuild(Guild.java:56)
[09:51:56 WARN]: at net.aspace.guilds.command.GuildsInfoCommand.execute(GuildsInfoCommand.java:28)
[09:51:56 WARN]: at net.aspace.command.CommandNode.onCommand(CommandNode.java:64)
[09:51:56 WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
[09:51:56 WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:140)
[09:51:56 WARN]: at org.bukkit.craftbukkit.v1_10_R1.CraftServer.dispatchCommand(CraftServer.java:646)
[09:51:56 WARN]: at org.bukkit.craftbukkit.v1_10_R1.CraftServer.dispatchServerCommand(CraftServer.java:632)
[09:51:56 WARN]: at net.minecraft.server.v1_10_R1.DedicatedServer.aL(DedicatedServer.java:438)
[09:51:56 WARN]: at net.minecraft.server.v1_10_R1.DedicatedServer.D(DedicatedServer.java:401)
[09:51:56 WARN]: at net.minecraft.server.v1_10_R1.MinecraftServer.C(MinecraftServer.java:668)
[09:51:56 WARN]: at net.minecraft.server.v1_10_R1.MinecraftServer.run(MinecraftServer.java:567)
[09:51:56 WARN]: at java.lang.Thread.run(Thread.java:745)
[09:51:56 INFO]: Guild space does not exist!


Parameter index out of range exception:

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
[11:11:37 WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
[11:11:37 WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
[11:11:37 WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
[11:11:37 WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
[11:11:37 WARN]: at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3321)
[11:11:37 WARN]: at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3306)
[11:11:37 WARN]: at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4021)
[11:11:37 WARN]: at net.aspace.guilds.Guild.getGuild(Guild.java:57)
[11:11:37 WARN]: at net.aspace.guilds.command.GuildsInfoCommand.execute(GuildsInfoCommand.java:28)


Here is the
Guild
class:

package net.aspace.guilds;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import net.aspace.chat.Message;
import net.aspace.guilds.GuildsPlugin;
import net.aspace.guilds.util.MySQL;
import net.aspace.util.SimpleCallback;

import org.bukkit.entity.Player;
import org.bukkit.plugin.Plugin;
import org.bukkit.scheduler.BukkitRunnable;
import org.bukkit.scheduler.BukkitTask;

public class Guild {

public String name;
public List<String> members;
public int maxPlayers = 3;

public Guild(String name) {
}

public Guild(String name, List<String> members, int maxPlayers) {
this.name = name;
this.members = members;
this.maxPlayers = maxPlayers;
}

public void addPlayer(final Player p) {
final String gName = this.name;
new BukkitRunnable() {

public void run() {
try {
PreparedStatement ps = MySQL.getConnection()
.prepareStatement("UPDATE PlayerData SET GUILD='?' WHERE PLAYERNAME='?'");
ps.setString(1, gName);
ps.setString(2, p.getName());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}.runTaskAsynchronously((Plugin) GuildsPlugin.getInstance());
}

public static void getGuild(final String name, final SimpleCallback<Guild> callback) {
try {
PreparedStatement ps = MySQL.getConnection().prepareStatement("SELECT * FROM Guilds WHERE GUILD_NAME='?'");
ps.setString(1, name);
ArrayList<String> players = new ArrayList<String>();

ResultSet set = ps.executeQuery();
if (set.next()) {
String gName = set.getString("GUILD_NAME");
int maxMembers = set.getInt("MAX_MEMBERS");
try {
PreparedStatement ps2 = MySQL.getConnection()
.prepareStatement("SELECT * FROM PlayerData WHERE GUILD = '?'");
ps2.setString(1, name);
ResultSet result = ps2.executeQuery();
GuildsPlugin.getInstance().getLogger().info("select");
while (result.next()) {
players.add(result.getString("PLAYERNAME"));
}
result.close();
callback.execute(new Guild(gName, players, maxMembers));
} catch (SQLException e) {
e.printStackTrace();
}
set.close();

}
} catch (SQLException e) {
e.printStackTrace();
callback.execute(null);
} catch (NullPointerException e) {
e.printStackTrace();
callback.execute(null);
}
}

public static void createGuild(final Player p, final String name) {
new BukkitRunnable() {

@Override
public void run() {
try {
PreparedStatement ps = MySQL.getConnection().prepareStatement("SELECT * FROM PlayerData WHERE PLAYER_NAME = '?'");
ps.setString(1,p.getName());



//Get player data
ResultSet result = ps.executeQuery();
if (result.first()) {
//Check if player is in a guild
if (result.getString("GUILD") == null) {
// TODO Check if guild exists
try {
//Add guild
PreparedStatement ps2 = MySQL.getConnection().prepareStatement("INSERT INTO Guilds (GUILD_NAME,MAX_MEMBERS) VALUES ('?',10)");
ps2.setString(1,name);
ps2.executeUpdate();
//Add player to guild
PreparedStatement ps3 = MySQL.getConnection().prepareStatement("UPDATE PlayerData SET GUILD='?'"
+ "WHERE PLAYER_NAME='?'");
ps3.setString(1,name);
ps3.setString(2,p.getName());
p.sendMessage(Message.fromString(String.format("&eYou have created and joined the guild &b%s&e!",name)));
} catch (SQLException e) {
e.printStackTrace();
}
} else {
//Player is in a guild
p.sendMessage(Message.fromString("&cYou are already in a guild!"));
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}.runTaskAsynchronously(GuildsPlugin.getInstance());
}

}


Here is the
GuildInfoCommand
class:

package net.aspace.guilds.command;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;
import org.bukkit.plugin.Plugin;

import net.aspace.chat.Message;
import net.aspace.command.CommandNode;
import net.aspace.guilds.Guild;
import net.aspace.guilds.GuildsPlugin;
import net.aspace.guilds.util.GuildsPlayer;
import net.aspace.util.SimpleCallback;

public class GuildsInfoCommand<E extends Plugin> extends CommandNode<E> {

public GuildsInfoCommand(E plugin) {
super(plugin);
// TODO Auto-generated constructor stub
}

@Override
public boolean execute(CommandSender sender, String... args) {
if (args.length >= 1) {
Guild.getGuild(args[0], new SimpleCallback<Guild>() {

@Override
public void execute(Guild guild) {
if (guild != null) {

sender.sendMessage(Message.fromString("&b&lGUILD INFO"));
sender.sendMessage(Message.fromString("&eGuild name: " + guild.name));
sender.sendMessage(Message.fromString("&eGuild maxmembers: " + guild.maxPlayers));
sender.sendMessage(
Message.fromString("&eGuild members &7(" + Integer.toString(guild.members.size()) + "/"
+ Integer.toString(guild.maxPlayers) + ")&e: "));
guild.members.forEach(s -> {
sender.sendMessage(Message.fromString("&7- " + s));
});
sender.sendMessage(Message.fromString("&b&lGUILD INFO"));

} else {
sender.sendMessage(Message.fromString(String.format("&cGuild &e%s&c does not exist!", args[0])));
}

}
});

} else {
if (sender instanceof Player) {
Player player = (Player) sender;
GuildsPlayer player2 = GuildsPlayer.getPlayer(player);
player2.inGuild(new SimpleCallback<Boolean>() {

@Override
public void execute(Boolean response) {
if (response) {
Guild.getGuild(args[0], new SimpleCallback<Guild>() {

@Override
public void execute(Guild guild) {
if (guild != null) {
sender.sendMessage(Message.fromString("&b&lGUILD INFO"));
sender.sendMessage(Message.fromString("&eGuild name: &b" + guild.name));
sender.sendMessage(Message.fromString("&eMember Limit: &b" + guild.maxPlayers));
sender.sendMessage(Message.fromString(
"&eGuild members &7(" + Integer.toString(guild.members.size()) + "/"
+ Integer.toString(guild.maxPlayers) + ")&e: "));
guild.members.forEach(s -> {
sender.sendMessage(Message.fromString("&7- " + s));
});
sender.sendMessage(Message.fromString("&b&lGUILD INFO"));

} else {
sender.sendMessage(Message.fromString("&cYou are not in a guild!"));
}
}

});
} else {

}
}
});
} else {
sender.sendMessage(Message.fromString("&cYou must specify a guild name!"));
}
}

return true;
}

@Override
public String getName() {
// TODO Auto-generated method stub
return "info";
}

}


Code for
MySQL
class:

package net.aspace.guilds.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.bukkit.Bukkit;
import org.bukkit.command.ConsoleCommandSender;

public class MySQL {

public static String host = "****";
public static String port = "****";
public static String database = "****";
public static String username = "****";
public static String password = "****";
public static Connection con;

static ConsoleCommandSender console = Bukkit.getConsoleSender();

// connect
public static void connect() {
if (!isConnected()) {
try {
con = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
console.sendMessage("\247c[\2476Minepedia-System\247c] \247bMySQL-Verbindung wurde aufgebaut!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}

// disconnect
public static void disconnect() {
if (isConnected()) {
try {
con.close();
console.sendMessage("\247c[\2476Minepedia-System\247c]\247bMySQL-Verbindung wurde geschlossen!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}

// isConnected
public static boolean isConnected() {
return (con == null ? false : true);
}

// getConnection
public static Connection getConnection() {
return con;
}
}

Answer

You need to invoke MySQL.connect() method before you try to invoke MySQL.getConnection().

When you invoke MySQL.getConnection() method without MySQL.connect() method you don't create connection, and as result got NullPointerException upon attempt invoke MySQL.getConnection().prepareStatement...

You need to delete ' characters, because SQL parser interpret them like string value and omit placeholder ?.

For example:

PreparedStatement ps = 
    MySQL.getConnection().prepareStatement("SELECT * FROM Guilds WHERE GUILD_NAME=?");
Comments