Hallo zusammen,
mein Quelltext, um Daten in einer online-db zu bearbeiten sieht wie folgt aus:
Habt ihr Ideen, wie ich sämtliche Querys optimieren kann, damit ich kürzere Ladezeiten erreiche?
Beispiel: Ein Turnier (mit 13 Teams à 4 Spielern) benötigt mit allen dazugehörenden Operationen rund 8 Sekunden zum Laden. Ich weiß, dass ich viele Schleifen in meinem Quelltext habe, die eine ungeheuer lange Laufzeit haben. Leider weiß ich nicht, wie ich all diese verschachtelten Schleifen optimieren kann. Ich bin mir sicher, dass man da einiges verkürzen kann.
Ich wäre echt dankbar, wenn da mal jemand drüberschauen würde
MfG Jakob
mein Quelltext, um Daten in einer online-db zu bearbeiten sieht wie folgt aus:
Habt ihr Ideen, wie ich sämtliche Querys optimieren kann, damit ich kürzere Ladezeiten erreiche?
Beispiel: Ein Turnier (mit 13 Teams à 4 Spielern) benötigt mit allen dazugehörenden Operationen rund 8 Sekunden zum Laden. Ich weiß, dass ich viele Schleifen in meinem Quelltext habe, die eine ungeheuer lange Laufzeit haben. Leider weiß ich nicht, wie ich all diese verschachtelten Schleifen optimieren kann. Ich bin mir sicher, dass man da einiges verkürzen kann.
Java:
/*
* 10.08.2016 - 23:11:44 | DisplayDatabase.java
*/
package main.model;
/**
*
* @author jakobbennemann
*/
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.collections.ObservableList;
import main.Main;
public class DisplayDatabase {
//Tableview and data
private static ObservableList<ObservableList> data;
public static boolean updateLastUpdated(String time) {
try {
Connection con = DBConnection.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = null;
String sql = "UPDATE tournaments SET last_edited = '" + time + "' WHERE id=" + Main.TOURNAMENT_ID + ";";
System.out.println(sql);
if (stmt.executeUpdate(sql) > 0) {
System.out.println("updated");
con.close();
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public static boolean loadTournaments() {
try {
Tournament tmpTournament = null;
Team tmpTeam = null;
List<Player> tmpPlayers = new ArrayList<>();
Connection con = DBConnection.getConnection();
Statement stmtTournament = con.createStatement();
ResultSet rsTournament = null;
String sqlTournament = "SELECT * FROM tournaments;";
rsTournament = stmtTournament.executeQuery(sqlTournament);
while (rsTournament.next()) {
int tournamentID = rsTournament.getInt("id");
String tournamentName = rsTournament.getString("name");
String tournamentOrganizer = rsTournament.getString("organizer");
String tournamentDate = rsTournament.getString("date");
int tournamentTyp = rsTournament.getInt("type");
tmpTournament = new Tournament(tournamentID, tournamentName, tournamentOrganizer, tournamentDate, tournamentTyp);
Statement stmtTeam = con.createStatement();
ResultSet rsTeam = null;
String sqlTeam = "SELECT * FROM teams WHERE tournament_id = " + tournamentID + " ORDER BY name;";
rsTeam = stmtTeam.executeQuery(sqlTeam);
while (rsTeam.next()) {
int teamID = rsTeam.getInt("id");
String teamName = rsTeam.getString("name");
tmpTeam = new Team(teamName, teamID);
int main = rsTeam.getInt("main_team_id");
if (main == 0 || main < 0) {
main = 0;
}
tmpTeam.setMainTeamNumber(main);
Statement stmtPlayer = con.createStatement();
ResultSet rsPlayer = null;
String sqlPlayer = "SELECT * FROM players WHERE team_id = " + teamID + " ORDER BY name;";
rsPlayer = stmtPlayer.executeQuery(sqlPlayer);
while (rsPlayer.next()) {
Player newPlayer = new Player(rsPlayer.getString("name"), rsPlayer.getInt("team_id"));
newPlayer.setPlayerID(rsPlayer.getInt("id"));
tmpTeam.addPlayer(newPlayer);
}
tmpTournament.addTeam(tmpTeam);
}
Main.tournaments.add(tmpTournament);
}
con.close();
return true;
} catch (SQLException ex) {
Logger.getLogger(DisplayDatabase.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
}
public static boolean loadTournamentData() throws SQLException, Exception {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
ResultSet rs = null;
int numberOfRequiredDesks = 0;
String desks = "SELECT count(id) AS number FROM round_division WHERE tournament_id = " + Main.TOURNAMENT_ID + ";";
rs = stmt.executeQuery(desks);
if (rs.next()) {
numberOfRequiredDesks = (rs.getInt("number") / 4 / 2);
}
desks = "SELECT count(id) AS number FROM points WHERE tournament_id =" + Main.TOURNAMENT_ID + " AND type='pointsSingle';";
rs = stmt.executeQuery(desks);
int numberOfPlayersSingle = 0;
if (rs.next()) {
numberOfPlayersSingle = rs.getInt("number");
}
ArrayList<Desk> tablesRoundOne = new ArrayList<>();
ArrayList<Desk> tablesRoundTwo = new ArrayList<>();
ArrayList<Desk> tablesRoundSingle = new ArrayList<>();
for (int i = 0; i < numberOfRequiredDesks; i++) {
Desk newDesk = new Desk((i + 1));
Statement stmt2 = c.createStatement();
String sqlPlayer = "SELECT player_id FROM round_division WHERE tournament_id = " + Main.TOURNAMENT_ID + " AND round = 1 AND desk_id = " + (i + 1) + ";";
ResultSet rsPlayer = stmt2.executeQuery(sqlPlayer);
while (rsPlayer.next()) {
Player newPlayer = new Player();
newPlayer.setPlayerID(rsPlayer.getInt("player_id"));
newPlayer.setDeskRoundOne((i + 1));
String sqlTeam = "SELECT team_id FROM players WHERE id = " + newPlayer.getPlayerID() + ";";
Statement stmt3 = c.createStatement();
ResultSet rsTeam = null;
rsTeam = stmt3.executeQuery(sqlTeam);
int teamID = -1;
if (rsTeam.next()) {
teamID = rsTeam.getInt("team_id");
}
newPlayer.setTeamNumber(teamID);
newPlayer.setTeamName(Main.getTeamNameById(teamID));
String sqlName = "SELECT name FROM players WHERE id= " + newPlayer.getPlayerID() + ";";
Statement stmt4 = c.createStatement();
ResultSet rsName = stmt4.executeQuery(sqlName);
if (rsName.next()) {
newPlayer.setName(rsName.getString("name"));
}
newDesk.addPlayer(newPlayer);
}
tablesRoundOne.add(newDesk);
}
// System.out.println("AUSGABE");
// for (Desk d : tablesRoundOne) {
// for (Player p : d.getPlayers()) {
// System.out.println("Name: " + p.getName() + " T:" + p.getTeamNumber() + " P1:" + p.getPointsOne() + " O1:" + p.getOutsOne());
// }
// }
// System.out.println("AUSGABE ENDE\n");
for (int i = 0; i < numberOfRequiredDesks; i++) {
Desk newDesk = new Desk((i + 1));
Statement stmt2 = c.createStatement();
String sqlPlayer = "SELECT * FROM round_division WHERE tournament_id = " + Main.TOURNAMENT_ID + " AND round = 2 AND desk_id = " + (i + 1) + ";";
ResultSet rsPlayer = stmt2.executeQuery(sqlPlayer);
while (rsPlayer.next()) {
Player newPlayer = new Player();
newPlayer.setPlayerID(rsPlayer.getInt("player_id"));
newPlayer.setDeskRoundTwo(rsPlayer.getInt("desk_id"));
String sqlTeam = "SELECT team_id FROM players WHERE id = " + newPlayer.getPlayerID() + ";";
Statement stmt3 = c.createStatement();
ResultSet rsTeam = stmt3.executeQuery(sqlTeam);
int teamID = -1;
if (rsTeam.next()) {
teamID = rsTeam.getInt("team_id");
}
newPlayer.setTeamNumber(teamID);
newPlayer.setTeamName(Main.getTeamNameById(teamID));
String sqlName = "SELECT name FROM players WHERE id= " + newPlayer.getPlayerID() + ";";
Statement stmt4 = c.createStatement();
ResultSet rsName = stmt4.executeQuery(sqlName);
if (rsName.next()) {
newPlayer.setName(rsName.getString("name"));
}
newDesk.addPlayer(newPlayer);
}
tablesRoundTwo.add(newDesk);
}
Desk newDesk = new Desk(1);
for (int i = 0; i < numberOfPlayersSingle; i++) {
Statement stmt5 = c.createStatement();
String sqlSingle = "SELECT * FROM points WHERE tournament_id = " + Main.TOURNAMENT_ID + " AND type='pointsSingle';";
ResultSet rsSingle = stmt5.executeQuery(sqlSingle);
Player newPlayer = new Player();
while (rsSingle.next()) {
newPlayer.setPlayerID(rsSingle.getInt("player_id"));
newPlayer.setPointsSingle(rsSingle.getInt("value"));
}
sqlSingle = "SELECT * FROM points WHERE tournament_id = " + Main.TOURNAMENT_ID + " AND type='outsSingle';";
stmt5.close();
stmt5 = c.createStatement();
rsSingle.close();
rsSingle = stmt5.executeQuery(sqlSingle);
while (rsSingle.next()) {
newPlayer.setOutsSingle(rsSingle.getInt("value"));
}
Statement stmt6 = c.createStatement();
String sql = "SELECT name,team_id FROM players WHERE id = " + newPlayer.getPlayerID() + ";";
ResultSet rsSingleTeamNumber = stmt6.executeQuery(sql);
while (rsSingleTeamNumber.next()) {
newPlayer.setTeamNumber(rsSingleTeamNumber.getInt("team_id"));
newPlayer.setName(rsSingleTeamNumber.getString("name"));
newPlayer.setTeamName(Main.getTeamNameById(newPlayer.getTeamNumber()));
}
newDesk.addPlayer(newPlayer);
}
tablesRoundSingle.add(newDesk);
for (Desk desk : tablesRoundOne) {
for (Player p : desk.getPlayers()) {
String sql = "SELECT value FROM points WHERE type= 'points1' AND player_id = " + p.getPlayerID() + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rsPlayer = stmt.executeQuery(sql);
while (rsPlayer.next()) {
p.setPointsOne(rsPlayer.getInt("value"));
}
sql = "SELECT value FROM points WHERE type= 'outs1' AND player_id = " + p.getPlayerID() + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
rsPlayer = stmt.executeQuery(sql);
while (rsPlayer.next()) {
p.setOutsOne(rsPlayer.getInt("value"));
}
}
}
for (Desk desk : tablesRoundTwo) {
for (Player p : desk.getPlayers()) {
String sql = "SELECT value FROM points WHERE type= 'points2' AND player_id = " + p.getPlayerID() + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rsPlayer = null;
rsPlayer = stmt.executeQuery(sql);
while (rsPlayer.next()) {
p.setPointsTwo(rsPlayer.getInt("value"));
}
sql = "SELECT value FROM points WHERE type= 'outs2' AND player_id = " + p.getPlayerID() + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
rsPlayer = stmt.executeQuery(sql);
while (rsPlayer.next()) {
p.setOutsTwo(rsPlayer.getInt("value"));
}
}
}
c.close();
/*for (Player p : tablesRoundSingle.get(0).getPlayers()) {
System.out.println("SPIELER: " + p.getPointsSingle());
System.out.println("OUTS: " + p.getOutsSingle());
System.out.println("TEAMNUMMER: " + p.getTeamNumber());
}*/
Main.tablesRound1 = (ArrayList<Desk>) tablesRoundOne.clone();
Main.tablesRound2 = (ArrayList<Desk>) tablesRoundTwo.clone();
Main.tablesRoundSingle = (ArrayList<Desk>) tablesRoundSingle.clone();
System.out.println("1. " + Main.tablesRound1.size());
System.out.println("2. " + Main.tablesRound2.size());
System.out.println("Single. " + Main.tablesRoundSingle.size());
System.out.println("ID: " + Main.TOURNAMENT_ID);
System.out.println(Main.getCurrentTournament().getTeams().size());
Main.updatePlayerDivision();
Main.ROUNDS_GENERATED = DisplayDatabase.getRoundsGenerated();
return true;
}
public static double getAvgPointsPerPlayerRoundOne() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
double numberPlayers = (double) DisplayDatabase.getNumberOfPlayers();
String sql = "SELECT sum(value) as avg FROM points WHERE type='points1' AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
int count = 0;
if (rs.next()) {
count = rs.getInt("avg");
}
rs.close();
stmt.close();
c.close();
return ((double) count / numberPlayers);
}
public static double getAvgPointsPerPlayerRoundTwo() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
double numberPlayers = (double) DisplayDatabase.getNumberOfPlayers();
String sql = "SELECT sum(value) as avg FROM points WHERE type='points2' AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
int count = 0;
if (rs.next()) {
count = rs.getInt("avg");
}
c.close();
return ((double) count / numberPlayers);
}
public static double getAvgPointsPerTeamRoundOne() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
int numberTeams = 0;
int points = 0;
String sql = "SELECT id FROM teams WHERE tournament_id=" + Main.TOURNAMENT_ID + " AND tournament_id =" + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
numberTeams++;
Statement stmt2 = c.createStatement();
ResultSet rs2 = null;
String sql2 = "SELECT id FROM players WHERE team_id =" + rs.getInt("id") + ";";
rs2 = stmt2.executeQuery(sql2);
while (rs2.next()) {
Statement stmt3 = c.createStatement();
String sql3 = "SELECT value FROM points WHERE type='points1' AND player_id=" + rs2.getInt("id") + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs3 = stmt3.executeQuery(sql3);
while (rs3.next()) {
points += rs3.getInt("value");
}
}
}
c.close();
//kann man so machen, wenn mal die Teams, welche getielt wurde zusammenzählen möchte, sprich als 1 Teams, nicht als 2
// if(Main.getCurrentTournament().getTournamentType() == 3){
// numberTeams -= DisplayDatabase.getNumberDividedTeams();
// }
return ((double) points / numberTeams);
}
public static double getAvgPointsPerTeamRoundTwo() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
int numberTeams = 0;
int points = 0;
String sql = "SELECT id FROM teams WHERE tournament_id=" + Main.TOURNAMENT_ID + " AND tournament_id =" + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
numberTeams++;
Statement stmt2 = c.createStatement();
ResultSet rs2 = null;
String sql2 = "SELECT id FROM players WHERE team_id =" + rs.getInt("id") + ";";
rs2 = stmt2.executeQuery(sql2);
while (rs2.next()) {
Statement stmt3 = c.createStatement();
String sql3 = "SELECT value FROM points WHERE type='points2' AND player_id=" + rs2.getInt("id") + " AND tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs3 = stmt3.executeQuery(sql3);
while (rs3.next()) {
points += rs3.getInt("value");
}
}
}
c.close();
//kann man so machen, wenn mal die Teams, welche getielt wurde zusammenzählen möchte, sprich als 1 Teams, nicht als 2
// if(Main.getCurrentTournament().getTournamentType() == 3){
// numberTeams -= DisplayDatabase.getNumberDividedTeams();
// }
return ((double) points / numberTeams);
}
public static int getNumberOfPlayers() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
int number = 0;
String sql = "SELECT id FROM teams WHERE tournament_id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Statement stmt2 = c.createStatement();
ResultSet rs2 = null;
String sql2 = "SELECT count(id) as c FROM players WHERE team_id = " + rs.getInt("id") + ";";
rs2 = stmt2.executeQuery(sql2);
if (rs2.next()) {
number += rs2.getInt("c");
}
stmt2.close();
rs2.close();
}
c.close();
return number;
}
public static int getPointsSingle(int playerID) throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmtSingleQuery = c.createStatement();
String sql = "SELECT value FROM points WHERE type = 'pointsSingle' AND tournament_id = " + Main.TOURNAMENT_ID + " AND player_id = " + playerID + ";";
ResultSet rsSingleQuery = stmtSingleQuery.executeQuery(sql);
if (rsSingleQuery.next()) {
return rsSingleQuery.getInt("value");
} else {
return 0;
}
}
public static int getOutsSingle(int playerID) throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmtSingleQuery = c.createStatement();
String sql = "SELECT value FROM points WHERE type = 'outsSingle' AND tournament_id = " + Main.TOURNAMENT_ID + " AND player_id = " + playerID + ";";
ResultSet rsSingleQuery = stmtSingleQuery.executeQuery(sql);
if (rsSingleQuery.next()) {
return rsSingleQuery.getInt("value");
} else {
return 0;
}
}
public static int getNextTournamentID() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT max(id) AS id FROM tournaments;";
//ResultSet
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
int tmp = rs.getInt("id") + 1;
stmt.close();
c.close();
return tmp;
} else {
stmt.close();
c.close();
return 1;
}
}
public static int getNextTeamID() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT max(id) AS id FROM teams;";
//ResultSet
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
int tmp = rs.getInt("id") + 1;
stmt.close();
c.close();
return tmp;
} else {
stmt.close();
c.close();
return 1;
}
}
public static int getNextPlayerID() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT max(id) AS id FROM players;";
//ResultSet
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
int tmp = rs.getInt("id") + 1;
stmt.close();
c.close();
return tmp;
} else {
stmt.close();
c.close();
return 1;
}
}
public static int getNextPointsID() throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT max(id) AS id FROM points;";
//ResultSet
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
int tmp = rs.getInt("id") + 1;
stmt.close();
c.close();
return tmp;
} else {
stmt.close();
c.close();
return 1;
}
}
public static boolean insertTournament(Tournament newTournament) {
try {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "INSERT INTO tournaments (id,name,organizer,date,type,rounds_generated) VALUES"
+ "("
+ newTournament.getTournamentID() + ",'"
+ newTournament.getTournamentName() + "','"
+ newTournament.getTournamentOrganizer() + "','"
+ newTournament.getTournamentDate() + "',"
+ newTournament.getTournamentType() + ", 0);";
if (stmt.executeUpdate(sql) > 0) {
stmt.close();
c.close();
return true;
} else {
stmt.close();
c.close();
return false;
}
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
}
public static void updateRoundsGenerated(int state) throws SQLException {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "UPDATE tournaments SET rounds_generated = " + state + ";";
stmt.executeUpdate(sql);
stmt.close();
c.close();
}
public static boolean getRoundsGenerated() {
try {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT rounds_generated FROM tournaments WHERE id = " + Main.TOURNAMENT_ID + ";";
ResultSet rs = stmt.executeQuery(sql);
int m = -1;
if (rs.next()) {
m = rs.getInt("rounds_generated");
}
stmt.close();
c.close();
return m == 1;
} catch (SQLException ex) {
Logger.getLogger(DisplayDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return false;
}
public static boolean insertTeam(Team team) {
try {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "INSERT INTO teams (id,name,tournament_id,main_team_id) VALUES"
+ "("
+ team.getTeamNumber() + ",'"
+ team.getTeamName() + "',"
+ Main.getCurrentTournament().getTournamentID() + ",0);";
if (stmt.executeUpdate(sql) > 0) {
if (team.getMainTeamNumber() != 0) {
sql = "UPDATE teams SET main_team_id = " + team.getMainTeamNumber() + " WHERE tournament_id = " + Main.getCurrentTournament().getTournamentID() + " AND id = " + team.getTeamNumber() + ";";
if (stmt.executeUpdate(sql) > 0) {
stmt.close();
c.close();
return true;
}
}
stmt.close();
c.close();
return true;
} else {
stmt.close();
c.close();
return false;
}
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
}
public static boolean insertPlayers(List<Player> players) {
try {
int tmp = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
for (Player p : players) {
String sql = "INSERT INTO players (name,team_id) VALUES"
+ "('"
+ p.getName() + "',"
+ p.getTeamNumber() + ");";
if (stmt.executeUpdate(sql) > 0) {
tmp++;
}
}
stmt.close();
c.close();
if (tmp == players.size()) {
return true;
} else {
return false;
}
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
}
public static int getAnzahlTeams() {
try {
int tmpCount = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT id FROM teams WHERE tournament_id = " + Main.getCurrentTournament().getTournamentID() + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
tmpCount++;
}
c.close();
return tmpCount;
} catch (SQLException ex) {
Logger.getLogger(DisplayDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return -1;
}
public static int getNumberDividedTeams() {
try {
int tmpCount = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "SELECT id FROM teams WHERE tournament_id = " + Main.getCurrentTournament().getTournamentID() + " AND main_team_id != 0;";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
tmpCount++;
}
c.close();
return tmpCount;
} catch (SQLException ex) {
Logger.getLogger(DisplayDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return -1;
}
public static boolean deletePoints() {
try {
int tmpCount = 0;
int countPlayer = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "DELETE FROM points WHERE tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
c.close();
return true;
} catch (Exception e) {
return false;
}
}
public static boolean insertRoundDivisions() {
try {
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "DELETE FROM round_division WHERE tournament_id =" + Main.TOURNAMENT_ID + ";";
stmt.executeUpdate(sql);
int tmpCount = 0;
for (Team team : Main.getCurrentTournament().getTeams()) {
for (Player p : team.getPlayers()) {
sql = "INSERT INTO round_division (player_id,desk_id,round,tournament_id) VALUES("
+ p.getPlayerID() + ","
+ p.getDeskRoundOne() + ","
+ "1,"
+ Main.getCurrentTournament().getTournamentID() + ");";
tmpCount += stmt.executeUpdate(sql);
}
for (Player p : team.getPlayers()) {
sql = "INSERT INTO round_division (player_id,desk_id,round,tournament_id) VALUES("
+ p.getPlayerID() + ","
+ p.getDeskRoundTwo() + ","
+ "2,"
+ Main.getCurrentTournament().getTournamentID() + ");";
tmpCount += stmt.executeUpdate(sql);
}
}
c.close();
return true;
} catch (SQLException ex) {
Logger.getLogger(DisplayDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return false;
}
public static boolean insertPointsRoundOne() throws SQLException {
int tmpCount = 0;
int countPlayer = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "DELETE FROM points WHERE type='points1' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
sql = "DELETE FROM points WHERE type='outs1' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
for (Desk desk : Main.tablesRound1) {
for (Player p : desk.getPlayers()) {
countPlayer++;
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( "
+ p.getPlayerID() + ", 'points1', "
+ p.getPointsOne() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( " + p.getPlayerID() + ", 'outs1', "
+ p.getOutsOne() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
}
}
stmt.close();
c.close();
if (tmpCount == countPlayer * 2) {
return true;
}
return false;
}
public static boolean insertPointsRoundTwo() throws SQLException {
int tmpCount = 0;
int countPlayer = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "DELETE FROM points WHERE type='points2' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
sql = "DELETE FROM points WHERE type='outs2' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
for (Desk desk : Main.tablesRound2) {
for (Player p : desk.getPlayers()) {
countPlayer++;
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( "
+ p.getPlayerID() + ", 'points2', "
+ p.getPointsTwo() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( " + p.getPlayerID() + ", 'outs2', "
+ p.getOutsTwo() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
}
}
if (tmpCount == countPlayer * 2) {
c.close();
return true;
}
c.close();
return false;
}
public static boolean insertPointsRoundSingle() throws SQLException {
int tmpCount = 0;
int countPlayer = 0;
Connection c = DBConnection.getConnection();
Statement stmt = c.createStatement();
String sql = "DELETE FROM points WHERE type='pointsSingle' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
sql = "DELETE FROM points WHERE type='outsSingle' AND tournament_id=" + Main.TOURNAMENT_ID + "";
stmt.executeUpdate(sql);
for (Desk desk : Main.tablesRoundSingle) {
for (Player p : desk.getPlayers()) {
countPlayer++;
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( "
+ p.getPlayerID() + ", 'pointsSingle', "
+ p.getPointsSingle() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
sql = "INSERT INTO points (player_id, type, value, tournament_id) "
+ " VALUES ( " + p.getPlayerID() + ", 'outsSingle', "
+ p.getOutsSingle() + ","
+ Main.TOURNAMENT_ID + " );";
tmpCount += stmt.executeUpdate(sql);
}
}
if (tmpCount == countPlayer * 2) {
c.close();
return true;
}
c.close();
return false;
}
}
MfG Jakob