Java tutorial
/** * Copyright 2012 Emily Soldal * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package me.emmy.db; import static com.google.common.base.Preconditions.checkState; import java.io.File; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.StringWriter; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Calendar; import java.util.List; import java.util.Scanner; import java.util.logging.Logger; import javax.persistence.Table; import me.emmy.queermap.BasePlugin; import org.apache.commons.dbcp.BasicDataSource; import org.bukkit.configuration.file.FileConfiguration; import org.bukkit.entity.Player; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.google.common.base.Charsets; import com.google.common.base.Optional; import com.google.common.base.Preconditions; import com.google.common.base.Predicates; import com.google.common.base.Throwables; import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import com.google.common.io.Files; /** * @author Emily Soldal * @created 18 Apr 2012 */ // TODO Messy messy messy! public class MySQLLayer implements DatabaseLayer { enum LocationMapper implements RowMapper<PlayerLocationRecord> { instance; public PlayerLocationRecord mapRow(ResultSet rs, int rowNum) throws SQLException { PlayerLocationRecord plr = new PlayerLocationRecord(); plr.setCity(rs.getString("city")); plr.setCountry(rs.getString("country")); plr.setName(rs.getString("name")); return plr; } } enum StringMapper implements RowMapper<String> { instance; @Override public String mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } } private static final Table playerTable; private static final String selectQuery; static { playerTable = PlayerLocationRecord.class.getAnnotation(Table.class); selectQuery = String.format("SELECT * FROM %s WHERE name = ?", playerTable.name()); } final JdbcTemplate jdbc; final BasicDataSource source; final BasePlugin plugin; final String schema, host, port; public MySQLLayer(BasePlugin plugin, FileConfiguration config) { super(); this.plugin = plugin; source = new BasicDataSource(); source.setDriverClassName("com.mysql.jdbc.Driver"); schema = config.getString("database"); host = config.getString("host"); port = config.getString("port"); String url = String.format("jdbc:mysql://%1$s:%2$s/%3$s", host, port, schema); source.setUrl(url); String username = config.getString("username"); source.setUsername(username); source.setPassword(config.getString("password")); plugin.log("Connecting to %s with user %s", url, username); jdbc = new JdbcTemplate(source); } public void close() throws IOException { try { source.close(); } catch (SQLException e) { throw new IOException(e); } } public Optional<PlayerLocationRecord> find(final Player player) { return Optional.fromNullable(Iterables.getFirst( jdbc.query(selectQuery, new Object[] { player.getName() }, LocationMapper.instance), null)); } @Override public void tryFixTables() { DataLayerVersion dbv = detectVersion(); if (tableExists() && dbv != DataLayerVersion.latest()) { List<DataLayerVersion> missingVersions = Lists.newArrayList(); for (DataLayerVersion ver : DataLayerVersion.values()) { if (ver.compareTo(dbv) > 0) { missingVersions.add(ver); } } plugin.log("Upgrading tables"); for (DataLayerVersion ver : missingVersions) { for (String file : ver.getUpgradeFiles()) { InputStream stream = MySQLLayer.class.getResourceAsStream("file"); String sql = String.format(new Scanner(stream).useDelimiter("\\A").next(), schema, playerTable.name()); plugin.log("Query: %s", sql); jdbc.execute(sql); } } plugin.log("Upgrading complete!"); enforceVersion(); } else { plugin.log("Attempting to install tables"); InputStream stream = MySQLLayer.class.getResourceAsStream("/createTable-qm_location.sql"); String sql = String.format(new Scanner(stream).useDelimiter("\\A").next(), schema, playerTable.name()); plugin.log("Query: %s", sql); jdbc.execute(sql); enforceVersion(); } } private void enforceVersion() { File versionFile = new File(plugin.getDataFolder(), "mysql.dl.version"); if (versionFile.exists()) { checkState(versionFile.isFile(), "%s is not a file!", "mysql.dl.version"); versionFile.delete(); } try { Files.touch(versionFile); Files.write(DataLayerVersion.latest().toString(), versionFile, Charsets.UTF_8); } catch (IOException e) { Throwables.propagate(e); } } @Override public boolean verifyTables() { plugin.log("Attempting to verify tables"); boolean exists = tableExists(); if (exists) { plugin.log("Verifying versions..."); DataLayerVersion dbv = detectVersion(); if (dbv != DataLayerVersion.latest()) { plugin.log("Database is version %s, expected %s... will upgrade!", dbv, DataLayerVersion.latest()); return false; } } return exists; } private boolean tableExists() { // InputStream stream = MySQLLayer.class // .getResourceAsStream("/verifyTable.sql"); // String sql = String.format(new Scanner(stream).useDelimiter("\\A") // .next(), schema, playerTable.name()); boolean exists = Iterables.any(jdbc.query("show tables", StringMapper.instance), Predicates.equalTo(playerTable.name())); return exists; } private DataLayerVersion detectVersion() { try { Scanner scanner = new Scanner(new File(plugin.getDataFolder(), "mysql.dl.version")); return DataLayerVersion.valueOf(scanner.next()); } catch (FileNotFoundException e) { return DataLayerVersion.v0; } } @Override public void save(PlayerLocationRecord loc) { plugin.info("Inserting record for %s", loc.getName()); jdbc.update(String.format( "insert into %s(name,city,country,region,countrycode,postalcode,latitude,longitude,lastmodified,dmacode,areacode,metrocode) values(?,?,?,?,?,?,?,?,?,?,?,?)", playerTable.name()), loc.getName(), loc.getCountry(), loc.getCity(), loc.getRegion(), loc.getCountryCode(), loc.getPostalCode(), loc.getLatitude(), loc.getLongitude(), Calendar.getInstance().getTimeInMillis(), loc.getDmaCode(), loc.getAreaCode(), loc.getMetroCode()); } }