[INACTIVE] SQLite and MySQL Tutorial/Library

Discussion in 'Resources' started by alta189, May 12, 2011.

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

    cholo71796

    @alta189
    I'm a noob. Caught where you were going. The insert is now:
    Code:java
    1. manageSQLite.updateQuery("UPDATE power SET amount=" + money + " WHERE player='" + player + "';");

    and works fine. Thanks!
     
  2. Offline

    alta189

    :p
    btw this is my 1000 post :D
     
    cholo71796 likes this.
  3. Offline

    cholo71796

    @alta189
    This my 473rd. What a milestone!
     
  4. Offline

    alta189

    I want to be one of the highest posting members eventually. :p
     
  5. Offline

    cholo71796

    @alta189
    Post count is epeen length?
     
  6. Offline

    alta189

    Still working on a fix, but MySQL works 100%
    The fix is actually v3. It will improve the SQL library a lot and make it easier to use!

    ;) I mainly just want to show that I am really involved in the community

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jul 16, 2016
  7. Offline

    Kalman Olah

    I'm already getting hyped because of the exclamation mark!
     
  8. Offline

    cholo71796

    And your post has an exclamation point, so that makes two exclamation points! Wow, this is exciting!
     
  9. Offline

    Kalman Olah

    Holy haberdashery, Batman! If my calculations are correct, your two exclamation marks, along with our previous two exclamation marks, add up to four exclamation marks in total!
     
  10. Offline

    cholo71796

    Wait a second-- your post has exclamation points! This one does too! By the end of this post, it looks like we have ten whole exclamation points! (I know how to add numbers!)
     
  11. Offline

    nubpro

    @alta189
    Can you give me your email?
     
  12. Offline

    alta189

    Why
     
  13. Offline

    e_zach

    he wants to spam you with messages of love i believe. :p
     
  14. Offline

    nubpro

    I need to ask some random questions.
    @e_zach
    I'm going to SPAM!
    jk
     
  15. Offline

    alta189

    [email protected]

    Note: Im not great with queries, I get them from w3schools
     
  16. Offline

    nubpro

    I'm a Php coder, I know how to work with MSQL in website but I'm not good at Java.
    Here's my question:
    I decide to setup a MSQL db for a plugin (Example: MutePlayerChat) but this plugin is using PlayerChatEvent as default, is there anyway I could setup in website and connect with this plugin?
     
  17. Offline

    alta189

    I would use a PHP backend to comunicate with a MySQL database so that you don't give out the connection info for your database
     
  18. Offline

    wwsean08

    Thanks so much for this, Hopefully it will make my life so much easier (though I may end up going with php calls for security reasons on my final product)
     
  19. Offline

    drampelt

    EDIT: Problem solved, turns out I missed some single quotes.

    Hello, I am fairly new to java and bukkit development. I am making a chat bot, and if its the first time someone logs on it will give them a welcome message and if its not it will say welcome back. Whenever I log in to test it I get this error in console:
    Error at SQL Query: [SQLITE_ERROR] SQL error or missing database (no such column: drampelt)
    If someone could please look at my query(ies) and tell me what I am doing wrong, that would be greatly appreciated. The tables are set up fine.

    Code:
    [/S]
    
    [S]public void onPlayerJoin(PlayerJoinEvent event) {[/S]
    [S]		super.onPlayerJoin(event);[/S]
    [S]		Player p = event.getPlayer();[/S]
    [S]		String n = p.getName();[/S]
    [S]		String q = "SELECT username FROM users WHERE username="+n+";";[/S]
    [S]		ResultSet result = null;[/S]
    
    [S]		if (plugin.configMYSQL) {[/S]
    [S]			try {[/S]
    [S]				result = this.plugin.manageMySQL.sqlQuery(q);[/S]
    [S]			} catch (MalformedURLException e) {[/S]
    [S]				// TODO Auto-generated catch block[/S]
    [S]				e.printStackTrace();[/S]
    [S]			} catch (InstantiationException e) {[/S]
    [S]				// TODO Auto-generated catch block[/S]
    [S]				e.printStackTrace();[/S]
    [S]			} catch (IllegalAccessException e) {[/S]
    [S]				// TODO Auto-generated catch block[/S]
    [S]				e.printStackTrace();[/S]
    [S]			}[/S]
    [S]		} else {[/S]
    [S]			result = this.plugin.manageSQLite.sqlQuery(q);[/S]
    [S]		}[/S]
    
    [S]		try {[/S]
    [S]			if(result != null && result.next()){[/S]
    [S]				talk("Welcome back, " + p.getName() + ". It's nice to see you again.");[/S]
    [S]				log.info("[Daisy] " + p.getName() + " is back.");[/S]
    [S]			} else {[/S]
    [S]				talk("Hello " + p.getName() + ", I am Daisy. It seems this is your first time here. Welcome to " + plugin.configWelcome + " :D");[/S]
    [S]				log.info("[Daisy] " + p.getName() + " is here for the first time.");[/S]
    [S]				String query = "INSERT INTO users (username) VALUES ('" + p.getName() + "');";[/S]
    
    [S]				if (plugin.configMYSQL) {[/S]
    [S]					try {[/S]
    [S]						this.plugin.manageMySQL.insertQuery(query);[/S]
    [S]					} catch (MalformedURLException e) {[/S]
    [S]						// TODO Auto-generated catch block[/S]
    [S]						e.printStackTrace();[/S]
    [S]					} catch (InstantiationException e) {[/S]
    [S]						// TODO Auto-generated catch block[/S]
    [S]						e.printStackTrace();[/S]
    [S]					} catch (IllegalAccessException e) {[/S]
    [S]						// TODO Auto-generated catch block[/S]
    [S]						e.printStackTrace();[/S]
    [S]					}[/S]
    [S]				} else {[/S]
    [S]					this.plugin.manageSQLite.insertQuery(query);[/S]
    [S]				}[/S]
    [S]			}[/S]
    [S]		} catch (SQLException e) {[/S]
    [S]			// TODO Auto-generated catch block[/S]
    [S]			e.printStackTrace();[/S]
    [S]		}[/S]
    
    [S]	}[/S]
    [S]
    [/CODE][/S]

    New problem, sqlite works great, but if mysql is set to true in my config file the plugin doesn't start and gives me this NullPointerException:
    Code:
    java.lang.NullPointerException
    	at com.alta189.sqlLibrary.MySQL.mysqlCore.checkConnection(mysqlCore.java:88)
    	at me.drampelt.daisy.Daisy.onEnable(Daisy.java:95)
    	at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:126)
    	at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:878)
    	at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:272)
    	at org.bukkit.craftbukkit.CraftServer.loadPlugin(CraftServer.java:162)
    	at org.bukkit.craftbukkit.CraftServer.enablePlugins(CraftServer.java:146)
    	at net.minecraft.server.MinecraftServer.e(MinecraftServer.java:284)
    	at net.minecraft.server.MinecraftServer.a(MinecraftServer.java:271)
    	at net.minecraft.server.MinecraftServer.init(MinecraftServer.java:148)
    	at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:335)
    	at net.minecraft.server.ThreadServerApplication.run(SourceFile:422)
    
    How can I fix this?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jul 16, 2016
  20. Hey alta189, I am new to plugin writting and using your tutorial and it helped me already
    a lot. But now I have a problem, I should be one in the logic of my code, that I can't solve.
    I am creting a poll plugin, curently I was able to create polls and list them to players
    but the listet poll is not what I typed in. In fact for example when I create my poll
    with " /poll create test " and then use " /poll list " to see all active polls I get the following one
    "[1] [Ljava.lang.String: @61b00766" as output. No error, nothing. Below you can find
    my create and list method. I guess it's some wierd noob mistake =)

    Code:
        public void getActivePolls(CommandSender sender) throws SQLException {
            String query = "SELECT poll FROM polls WHERE active = 'true' ";
            ResultSet result = dbManage.sqlQuery(query);
            Integer n = 0;
            if (result.next()) {
                do {
                    String activepoll = result.getString("poll");
                    n++;
                    sender.sendMessage( ChatColor.AQUA + "["+ n +"] " + ChatColor.YELLOW + activepoll);
            } while (result.next());
            } else{
                sender.sendMessage(ChatColor.YELLOW + "There are no active polls.");
            }
        }
    
        public boolean createPoll(String[] args, CommandSender sender){
            String query = "INSERT INTO polls (poll) VALUES ('"+ args + "')";
            dbManage.insertQuery(query);
            return true;
        }
     
  21. Offline

    cwingrav

    alta189, nice simplifying plugin. I like that it is a unified method for configuring MySQL and SQLite for the Bukkit community.

    One feature request I would have, now that you are supporting both DBs, it that you create a new java interface that has all the methods used in MySQL and SQLite, and have both mysqlCore.java and sqlCore.java implement them. This allows for a new class, say "SQLMaster" to basically have all the code of your OwnerCore.java file for reading settings.properties and then redirecting method calls like "insertQuery" to either the this.MySQL or this.manageSQLite, depending on those settings.

    I could write this for you if you want to see what I mean. I just hacked it for my code, but I can see a way to make your code even more valuable.
    Cheers!
     
  22. Offline

    Mr Smith

    Hi, I have problem. After some time, the plugin breaks the connection with mysql. I get this error in console:
    Code:
    Error at SQL UPDATE Query: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    The last packet successfully received from the server was 5,647,257 milliseconds ago.  The last packet sent successfully to the server was 8 milliseconds ago.
    Error at SQL UPDATE Query: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
     
  23. Offline

    alexh

    im making a plugin using this, its a secret tho but its going well might be beta released in 2 days, im adding your sql library now. only worry is this is my first time coding in java, i have no errors yet but ive not run it in bukkit yet :p
     
  24. Offline

    PatPeter

    I have some quirks with this library, namely redundancy.

    1. Why do you have two packages that contain two files with the exact same title, i.e. "DatabaseHandler.java"? It is much more efficient to have one package with one DatabaseHandler.java as a superclass or interface.​

    2. Why do you have useless wrapper methods from "mysqlCore.java"/"sqlCore.java" that simply point to a method in DatabaseHandler? i.e.:​
    Code:
        public Boolean checkTable(String table) throws MalformedURLException, InstantiationException, IllegalAccessException {
            return this.manageDB.checkTable(table);
        }
     
        public Boolean wipeTable(String table) throws MalformedURLException, InstantiationException, IllegalAccessException {
            return this.manageDB.wipeTable(table);
        }
     
        public Connection getConnection() throws MalformedURLException, InstantiationException, IllegalAccessException {
            return this.manageDB.getConnection();
        }
    3. Why do you have copypasta methods wherein the only difference is one word inside of a string? i.e.​
    Code:
        public void insertQuery(String query) throws MalformedURLException, InstantiationException, IllegalAccessException {
            try {
                Connection connection = getConnection();
                Statement statement = connection.createStatement();
     
                statement.executeUpdate(query);
     
            } catch (SQLException ex) {
     
                    if (!ex.toString().contains("not return ResultSet")) core.writeError("Error at SQL INSERT Query: " + ex, false);
     
            }
        }
     
        public void updateQuery(String query) throws MalformedURLException, InstantiationException, IllegalAccessException {
            try {
                Connection connection = getConnection();
                Statement statement = connection.createStatement();
     
                statement.executeUpdate(query);
     
            } catch (SQLException ex) {
     
                    if (!ex.toString().contains("not return ResultSet")) core.writeError("Error at SQL UPDATE Query: " + ex, false);
     
            }
        }
    If there is one priceless piece of advice I can give coders, it's this: if you are using CTRL+C and CTRL+V in excess, you're doing it wrong.

    I've uploaded a version of your library that I converted to superclass/subclass functionality. This way someone can add Oracle or another database driver if they want, all in one package. If you are going to use this code please accredit me. Otherwise I have some methods and Javadoc I wish to add myself. Also, I have not had the chance to test this yet; I plan to as my plugin progresses.

    EDIT: Forgot to include log and prefix in constructors.
     
  25. Offline

    alta189

    I planned to do this later, I coded this 2 weeks after I "learned" java. Now I have much more knowledge, however I don't support this anymore since I am too busy with Spout
     
  26. Offline

    PatPeter

    Should I take it over for you?

    And don't worry man, Java sucks. First time I heard of interfaces I was like, what?
     
  27. Offline

    alta189

    Sure I would love if you could, I started working on it never had time.
     
  28. Offline

    PatPeter

Thread Status:
Not open for further replies.

Share This Page