SQL database connection times out

Discussion in 'Spigot Plugin Development' started by Nukreeper64, Apr 19, 2017.

  1. I followed this tutorial to integrate my plugins with my server's mySQL database. I'm setting up the connection inside my onEnable() and storing it in memory for access by the parts of the plugin that need it. However sometimes the plugin will lose connection to the database, rendering the plugin useless until I reload. What is happening here, and what is the solution? I looked it up and saw people recommending using external libraries to manage SQL connections through pools. Is using a library the only way to effectively manage SQL? I would be willing to do so if necessary, but I want to keep my imports to a minimum.
     
  2. I've also seen a few people talking about Redis. Is it possible to use that if I do not have my own server, and am just renting from a host?
     
  3. Are there any problems with this? Is it OK to simply create a new connection when the old connection times out?

    Code (Java):
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    public class SQL
    {
        private String host;
        private int port;
        private String database;
        private String username;
        private String password;
        private Connection connection;
        private PreparedStatement statement;
        private Main plugin;
       
        public SQL(Main plugin, String host, int port, String database, String username, String password)
        {
            this.plugin = plugin;
            this.host = host;
            this.port = port;
            this.database = database;
            this.username = username;
            this.password = password;
        }
       
        public synchronized boolean openConnection()
        {
            try
            {
                if (connection != null && !connection.isClosed()) {
                    return false;
                }
               
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + this.host+ ":" + this.port + "/" + this.database, this.username, this.password);
               
                return true;
            }
            catch(SQLException e)
            {
                plugin.log.severe("SQL exception while attempting to open a connection.");
                plugin.log.severe(e.getMessage());
                return false;
            }
            catch(ClassNotFoundException e)
            {
                plugin.log.severe("SQL driver not found on server.");
                return false;
            }
        }
       
        public boolean executeCommand(String command, Object...inputs) throws SQLException
        {
            if(connection.isClosed())
            {
                openConnection();
            }
            statement = connection.prepareStatement(command);
               int i = 0;
               for(Object input : inputs)
               {
                   if(input instanceof String)
                       statement.setString(++i, (String)input);
                   if(input instanceof Date)
                       statement.setDate(++i, (Date)input);
               }
               return statement.execute();
        }
       
        public ResultSet getResult(String command, Object...inputs) throws SQLException
        {
            if(connection.isClosed())
            {
                openConnection();
            }
            statement = connection.prepareStatement(command);
            int i = 0;
            for(Object input : inputs)
            {
                if(input instanceof String)
                       statement.setString(++i, (String)input);
                   if(input instanceof Date)
                       statement.setDate(++i, (Date)input);
            }
            return statement.executeQuery();
        }
    }
     
  4. Close the connection after use a request
     
  5. Use connection pooling via hikariCP is so much simpler. I'll post some example code when I get home.