Java tutorial
/* * The MIT License * * Copyright 2016 Merlin von Rssing. * * Permission is hereby granted, free of charge, to any person obtaining a copy * of this software and associated documentation files (the "Software"), to deal * in the Software without restriction, including without limitation the rights * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell * copies of the Software, and to permit persons to whom the Software is * furnished to do so, subject to the following conditions: * * The above copyright notice and this permission notice shall be included in * all copies or substantial portions of the Software. * * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN * THE SOFTWARE. */ package com.github.flawedbliss.dicebotr4; import com.jolbox.bonecp.BoneCP; import com.jolbox.bonecp.BoneCPConfig; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import java.util.logging.Handler; import java.util.logging.Logger; import org.joda.time.DateTime; import org.joda.time.format.DateTimeFormat; import org.joda.time.format.DateTimeFormatter; /** * * @author Daiya */ public class MySqlManager { private static final Logger log = Logger.getLogger(MySqlManager.class.getName()); DConfig botConfig; BoneCP cp; public MySqlManager(DConfig botConfig) throws FatalDicebotException { this.botConfig = botConfig; init(); } /** * @desc Makes stuff work * @throws FatalDicebotException */ private void init() throws FatalDicebotException { BoneCPConfig config = new BoneCPConfig(); config.setJdbcUrl("jdbc:mysql://" + botConfig.getMysqlHost() + "/" + botConfig.getMysqlDatabase()); config.setUsername(botConfig.getMysqlUser()); config.setPassword(botConfig.getMysqlPassword()); try { this.cp = new BoneCP(config); } catch (SQLException ex) { if (ex.getMessage() != null) { throw new FatalDicebotException("Failed to connect to the MySql server. Start failed.\n" + ex.getErrorCode() + ex.getMessage()); } else { throw new FatalDicebotException("Failed to connect to the MySql server. Start failed."); } } } /** * * @param uid Unique ID of the user you want the lastlogin of * @return returns a org.joda.time.DateTime object with the last login of * the user * @throws DicebotException */ public DateTime getLastLogin(String uid) throws DicebotException { String sql = "SELECT * FROM lastlogin WHERE uid=?"; try (Connection connect = cp.getConnection(); PreparedStatement statement = connect.prepareStatement(sql)) { DateTime lastlogin = null; statement.setString(0, uid); try (ResultSet rs = statement.executeQuery()) { while (rs.next()) { lastlogin = new DateTime(rs.getDate("lastlogin")); } } return lastlogin; } catch (SQLException ex) { throw new DicebotException("Unable to retrieve lastlogin for uid " + uid + ". Ignoring."); } } /** * * @param uid Unique ID of the user whose lastlogin entry should be * added/updated * @throws DicebotException */ public void updateLastLogin(String uid) throws DicebotException { DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"); try (Connection connect = cp.getConnection()) { String sql; PreparedStatement statement; DateTime now = new DateTime(new Date()); DateTime lastlogin = getLastLogin(uid); if (lastlogin == null) { sql = "INSERT INTO lastlogin (uid, lastlogin) VALUES (?, ?)"; statement = connect.prepareStatement(sql); statement.setString(0, uid); statement.setString(1, dtf.print(now)); statement.executeUpdate(); } else { sql = "UPDATE lastlogin SET lastlogin=? WHERE uid=?"; statement = connect.prepareStatement(sql); statement.setString(0, dtf.print(now)); statement.setString(1, uid); statement.executeUpdate(); } statement.close(); } catch (SQLException ex) { throw new DicebotException("Could not insert/update lastlogin for " + uid + " . Ignoring."); } } /** * * @param uid UniqueId of the user to be checked * @return Returns the date when the user last used a premium code, returns today if never. * @throws DicebotException Throws a DicebotException if there's an SQLException */ public DateTime getPremiumBegin(String uid) throws DicebotException { String sql = "SELECT * FROM premium WHERE uid=?"; DateTime latestDate = new DateTime(2000, 0, 0, 0, 0); try (Connection connect = cp.getConnection(); PreparedStatement statement = connect.prepareStatement(sql)) { statement.setString(0, uid); try (ResultSet rs = statement.executeQuery()) { while (rs.next()) { if (latestDate.isBefore(new DateTime(rs.getDate("date_used")))) { latestDate = new DateTime(rs.getDate("date_used")); } } } } catch (SQLException ex) { throw new DicebotException("Unable to retrieve last premium renewal date for " + uid + " . Ignoring."); } return (latestDate.equals(new DateTime(2000, 0, 0, 0, 0))) ? new DateTime(new Date()) : latestDate; } /** * * @param code The primary key (code) which should be checked. * @return Returns true if the used column was set to 1 or the entry doesn't exist, false if it was set to 0 * @throws DicebotException Thrown if there is a SQLException */ public boolean isCodeUsed(String code) throws DicebotException { String sql = "SELECT * FROM premium WHERE code=?"; boolean codeUsed = true; try (Connection connect = cp.getConnection(); PreparedStatement statement = connect.prepareStatement(sql); ResultSet rs = statement.executeQuery()) { while (rs.next()) { codeUsed = rs.getBoolean("used"); } } catch (SQLException ex) { throw new DicebotException("Unable to verify premium code " + code + ". Ignoring."); } return codeUsed; } /** * * @param code The primary key (code) where the used column used should be updated * @param uid Unique Id of the user who used the code * @throws DicebotException */ public void setCodeUsed(String code, String uid) throws DicebotException { String sql = "UPDATE premium SET used=1, used_by=?, date_used=? WHERE code=?"; DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"); try (Connection connect = cp.getConnection(); PreparedStatement statement = connect.prepareStatement(sql);) { statement.setString(0, uid); statement.setString(1, dtf.print(new DateTime(new Date()))); statement.setString(2, code); statement.executeUpdate(); } catch (SQLException ex) { if (ex.getMessage() != null) { throw new DicebotException("Unable to set code used for code " + code + "\n" + ex.getMessage()); } else { throw new DicebotException("Unable to set code used for " + code + ". No further information."); } } } /** * @desc Closes the connection pool. */ public void close() { cp.close(); } /** * * @param h Handler to be added to the logger */ public void addHandler(Handler h) { log.addHandler(h); } }