Performance/VoteAnalyzer/src/DBConnection.java - master - Nicholas/java_basics

Performance/VoteAnalyzer/src/DBConnection.java
71 строка3 KB
nikolai_sokolovskii
20.2 VoteAnalyser
23 дек 2023, 21:56
23 дек 2023, 21:56ae43e96
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class DBConnection {

    private static Connection connection;
    private static String driver = "org.mariadb.jdbc.Driver";
    private static String url = "jdbc:mariadb://192.168.1.136:3306/";
    private static String dbName = "test";
    private static String dbUser = "test";
    private static String dbPass = "tset321TSET#@!";
    private static HashMap<Voter, Integer> voterCounts = new HashMap<>();
    private static StringBuilder insQuery = new StringBuilder();

    public static Connection getConnection() {
        if (connection == null) {
            try {
                Class.forName(driver);
                connection = DriverManager.getConnection(url + dbName + "?user=" + dbUser + "&password=" + dbPass);
                connection.createStatement().execute("DROP TABLE IF EXISTS voter_count");
                connection.createStatement().execute("CREATE TABLE voter_count(" +
                        "id INT NOT NULL AUTO_INCREMENT, " +
                        "name TINYTEXT NOT NULL, " +
                        "birthDate DATE NOT NULL, " +
                        "`count` INT NOT NULL, " +
                        "PRIMARY KEY(id), " +
                        "UNIQUE KEY name_date(name(50), birthDate))");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    public static void execMultiInsert() throws SQLException {
        String sql = "INSERT INTO voter_count(name, birthDate, `count`) VALUES " + insQuery.toString() +
                " ON DUPLICATE KEY UPDATE `count` = `count` + 1";
        DBConnection.getConnection().createStatement().executeQuery(sql);

    }
    public static void countVoter() throws SQLException {
        for (Map.Entry <Voter, Integer> entry : voterCounts.entrySet()) {
            Voter voter = entry.getKey();
            insQuery.append((insQuery.length() == 0 ? " " : ", ") + // накапливаем данные в StringBuilder
                    "('" + voter.getName() + "', STR_TO_DATE('" + voter.getBirthDay() + "', '%Y.%m.%d'), " + entry.getValue() + ")");

        }
        execMultiInsert();
        insQuery = new StringBuilder();
    }

    public static void addVoter(Voter voter) throws SQLException {
        int count = voterCounts.getOrDefault(voter, 0);
        voterCounts.put(voter, count + 1);
        if (voterCounts.size() > 5000) {
            countVoter();
            voterCounts = new HashMap<>();
        }
    }

    public static void printVoterCounts() throws SQLException {
        String sql = "SELECT name, birthDate, `count` FROM voter_count WHERE `count` > 1";
        ResultSet rs = DBConnection.getConnection().createStatement().executeQuery(sql);
        while (rs.next()) {
            System.out.println("\t" + rs.getString("name") + " (" +
                    rs.getString("birthDate") + ") - " + rs.getInt("count"));
        }
        rs.close();
    }
}