MYSQL Inventory Items

Discussion in 'Spigot Plugin Development' started by Daeshan, Jun 1, 2016.

  1. So I am trying to create a quests system so that each custom NPC that I have ,using CitizensAPI, will have a list of their own quests. I have a mysql database that has a Q_NPC_ID which looks for all the quest for a certain npc with that id and gives them an inventory full of quests that Have their id.

    Right now it is just giving me everyones quests no matter the NPC. Dont mind crappy and ugly code, im still very new. But if someone could help out with that :D
    Code (Text):
        public void npcclick(PlayerInteractEntityEvent event){
            if(event.getRightClicked() instanceof Villager){
                    Statement statement2 = Main.c.createStatement();
                    ResultSet rss = statement2
                            .executeQuery("SELECT * FROM NPCS WHERE Cate='Q'");
                    String questinv = "ยง4"+ event.getRightClicked().getName() + "'s Quests";
                    Inventory inv = Bukkit.createInventory(null,27, questinv);
                    Statement statement = Main.c.createStatement();
                    ResultSet rs = statement
                            .executeQuery("SELECT * FROM Quests WHERE NPC_ID='" + rss.getInt("ID") + "'");
                    while ( {
                        ItemStack quests = new ItemStack(Material.PAPER, 1);
                        ItemMeta questsmeta = quests.getItemMeta();
                        questsmeta.setDisplayName(ChatColor.AQUA + "" + ChatColor.BOLD + rs.getString("Name").toUpperCase());
                        ArrayList<String> questlore = new ArrayList<String>();
                        questlore.add(ChatColor.GRAY + rs.getString("Q_Desc"));
                        questlore.add(ChatColor.WHITE + "Required Lvl: "+ ChatColor.RED + rs.getString("Req_LVL"));
                        questlore.add(ChatColor.WHITE + "Reward: "+ ChatColor.GOLD + rs.getInt("Reward_Amount")+ " " +rs.getString("Reward_Item") );
                        questlore.add(ChatColor.WHITE + "Return to: "+ ChatColor.RED + rss.getString("Name"));

                    Player player = event.getPlayer();
  2. You don't really seem to do entity based filtering in your query, you just always fetch all NPCs with Cate = Q.

    • Don't query your database on the main thread.
    • Use PreparedStatements when parameters are involved (rather than simply concatenating Strings)
    • First collect all Strings, then query the DB once for all IDs (chain some ORs in the WHERE)
    • Close your resources after you're done with them (preferably with try-with-resources)
    • Don't depend on static where not necessary (static cling)
      • Main.c shouldn't be a thing
  3. Could you explain and provide small exmaples for some of this
    • This is basically wrapping all DB interaction in a BukkitScheduler#runTaskAsynchronously call
    • Code (Java):
      PreparedStatement stmt = conn.prepareStatement("SELECT foo FROM bar WHERE foo = ?");
      stmt.setString(1, "footastic"); // Starts at index 1
      ResultSet rs = stmt.executeQuery();
    • Assuming you have a ResultSet for IDs
      Code (Java):
      List ids = new List;
      queryAppend = new List
      while idSet has rows
          add id to ids
          add 'id = ?' to queryAppend
      join queryAppend with ' OR '
      stmt =conn.prepareStatement('SELECT foo FROM bar WHERE ' + queryAppend); // joinedIDs is 'id = ? OR id = ?' ... repeated <# of id> times
      index = 1
      for each id in ids
          stmt.setString(index, id);
          increment index
      result = stmt.executeQuery()
    • Learn how to use constructors. This is basic Java, really :p.
  4. I get a null pointer here in my code. I have not yet put it in the async I will after I get it working.

    Code (Text):
    [17:28:58 ERROR]: Could not pass event PlayerJoinEvent to AtlasCraft v1.0
            at$1.execute( ~[latest.jar:git-Spigot-8a048fe-a022dd2]
            at org.bukkit.plugin.RegisteredListener.callEvent( ~[latest.jar:git-Spigot-8a048fe-a022dd2]
            at org.bukkit.plugin.SimplePluginManager.fireEvent( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at org.bukkit.plugin.SimplePluginManager.callEvent( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.PlayerList.a( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.LoginListener.b( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.LoginListener.c( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.NetworkManager.a( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.ServerConnection.c( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.MinecraftServer.D( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.DedicatedServer.D( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at net.minecraft.server.v1_9_R2.MinecraftServer.C( [latest.jar:git-Spigot-8a048fe-a022dd2]
            at [latest.jar:git-Spigot-8a048fe-a022dd2]
            at [?:1.7.0_79]
    Caused by: java.lang.NullPointerException
            at me.dabuseck.atlas.MYSQL.MYSQL_InsertPlayerData.playerExists( ~[?:?]
            at ~[?:?]
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_79]
            at sun.reflect.NativeMethodAccessorImpl.invoke( ~[?:1.7.0_79]
            at sun.reflect.DelegatingMethodAccessorImpl.invoke( ~[?:1.7.0_79]
            at java.lang.reflect.Method.invoke( ~[?:1.7.0_79]
            at$1.execute( ~[latest.jar:git-Spigot-8a048fe-a022dd2]
            ... 14 more
    So I know where the error is occuring. It is happening at the m.c...This is a new problem now. I will worry about the other. I am trying to make it not depend on statics.

    [EDIT 2]
    #5 Daeshan, Jun 1, 2016
    Last edited: Jun 2, 2016