package org.webchat.repository;

import java.sql.*;

public class DatabaseConnection {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/";
    private static final String USER = "daniyar";
    private static final String PASSWORD = "";

    public static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        try(Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = connection.createStatement();
        ) {
            String sql = "CREATE DATABASE STUDENTS IF NOT EXISTS";
            stmt.executeUpdate(sql);
            System.out.println("Database created successfully...");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static Connection getConnectionPostrges() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class.forName("org.postgresql.Driver");
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static boolean createAllTable(){ // MariaBD
        String createTableChats = "CREATE TABLE chats(id_chat VARCHAR(255) PRIMARY KEY, name VARCHAR(255) NOT NULL)";
        String createTableMessages = "CREATE TABLE messages(id_message BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_chat VARCHAR(255) NOT NULL REFERENCES chats(id_chat), id_from VARCHAR(255) NOT NULL, content TEXT, date TIMESTAMP)";
        String createTableUserChats = "CREATE TABLE user_chats(id_user VARCHAR(255) NOT NULL REFERENCES users(id_user), id_chat VARCHAR(255) NOT NULL REFERENCES chats(id_chat))";
        String createTableUsers = "CREATE TABLE users(id_user VARCHAR(255) PRIMARY KEY, username VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL)";

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement chatsStatement = connection.prepareStatement(createTableChats);
             PreparedStatement messagesStatement = connection.prepareStatement(createTableMessages);
             PreparedStatement userChatsStatement = connection.prepareStatement(createTableUserChats);
             PreparedStatement userStatement = connection.prepareStatement(createTableUsers)) {

            ResultSet userResultSet1 = chatsStatement.executeQuery();
            ResultSet userResultSet2 = messagesStatement.executeQuery();
            ResultSet userResultSet3 = userChatsStatement.executeQuery();
            ResultSet userResultSet4 = userStatement.executeQuery();
            return (userResultSet1.next() && userResultSet2.next() && userResultSet3.next() && userResultSet4.next());
        } catch (SQLException e) {
            return false;
        }
    }
}/home/daniyar/Downloads/apache-tomcat-10.1.29/bin/catalina.sh run
[2024-11-08 01:29:55,333] Artifact WebChat:war exploded: Waiting for server connection to start artifact deployment...
08-Nov-2024 13:29:55.847 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Server version name:   Apache Tomcat/10.1.29
08-Nov-2024 13:29:55.849 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Server built:          Sep 5 2024 14:23:34 UTC
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Server version number: 10.1.29.0
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log OS Name:               Linux
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log OS Version:            6.6.41
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Architecture:          amd64
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Java Home:             /nix/store/yr5065y6vcf380lh7zw982qm35pmlqy8-jetbrains-jdk-jcef-17.0.8-b1000.8/lib/openjdk
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log JVM Version:           17.0.8+7-nixos
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log JVM Vendor:            JetBrains s.r.o.
08-Nov-2024 13:29:55.850 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log CATALINA_BASE:         /home/daniyar/.cache/JetBrains/IntelliJIdea2024.1/tomcat/732c1be9-5199-4f59-8cb7-8d2d61a0d576
08-Nov-2024 13:29:55.851 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log CATALINA_HOME:         /home/daniyar/Downloads/apache-tomcat-10.1.29
08-Nov-2024 13:29:55.852 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djava.util.logging.config.file=/home/daniyar/.cache/JetBrains/IntelliJIdea2024.1/tomcat/732c1be9-5199-4f59-8cb7-8d2d61a0d576/conf/logging.properties
08-Nov-2024 13:29:55.852 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
08-Nov-2024 13:29:55.852 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcom.sun.management.jmxremote=
08-Nov-2024 13:29:55.852 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcom.sun.management.jmxremote.port=1099
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcom.sun.management.jmxremote.ssl=false
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcom.sun.management.jmxremote.password.file=/home/daniyar/.cache/JetBrains/IntelliJIdea2024.1/tomcat/732c1be9-5199-4f59-8cb7-8d2d61a0d576/jmxremote.password
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcom.sun.management.jmxremote.access.file=/home/daniyar/.cache/JetBrains/IntelliJIdea2024.1/tomcat/732c1be9-5199-4f59-8cb7-8d2d61a0d576/jmxremote.access
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djava.rmi.server.hostname=127.0.0.1
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djdk.tls.ephemeralDHKeySize=2048
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED
08-Nov-2024 13:29:55.853 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: --add-opens=java.base/java.util=ALL-UNNAMED
08-Nov-2024 13:29:55.854 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: --add-opens=java.base/java.util.concurrent=ALL-UNNAMED
08-Nov-2024 13:29:55.854 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED
08-Nov-2024 13:29:55.854 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcatalina.base=/home/daniyar/.cache/JetBrains/IntelliJIdea2024.1/tomcat/732c1be9-5199-4f59-8cb7-8d2d61a0d576
08-Nov-2024 13:29:55.854 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Dcatalina.home=/home/daniyar/Downloads/apache-tomcat-10.1.29
08-Nov-2024 13:29:55.854 INFO [main] org.apache.catalina.startup.VersionLoggerListener.log Command line argument: -Djava.io.tmpdir=/home/daniyar/Downloads/apache-tomcat-10.1.29/temp
08-Nov-2024 13:29:55.855 INFO [main] org.apache.catalina.core.AprLifecycleListener.lifecycleEvent The Apache Tomcat Native library which allows using OpenSSL was not found on the java.library.path: [/nix/store/v2ny69wp81ch6k4bxmp4lnhh77r0n4h1-zlib-1.3.1/lib:/run/opengl-driver/lib:/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib]
08-Nov-2024 13:29:55.990 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler ["http-nio-8080"]
08-Nov-2024 13:29:56.003 INFO [main] org.apache.catalina.startup.Catalina.load Server initialization in [296] milliseconds
08-Nov-2024 13:29:56.037 INFO [main] org.apache.catalina.core.StandardService.startInternal Starting service [Catalina]
08-Nov-2024 13:29:56.037 INFO [main] org.apache.catalina.core.StandardEngine.startInternal Starting Servlet engine: [Apache Tomcat/10.1.29]
08-Nov-2024 13:29:56.044 INFO [main] org.apache.coyote.AbstractProtocol.start Starting ProtocolHandler ["http-nio-8080"]
08-Nov-2024 13:29:56.054 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in [50] milliseconds
Connected to server
[2024-11-08 01:29:56,427] Artifact WebChat:war exploded: Artifact is being deployed, please wait...
08-Nov-2024 13:29:57.463 INFO [RMI TCP Connection(2)-127.0.0.1] org.apache.jasper.servlet.TldScanner.scanJars At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
[2024-11-08 01:29:57,544] Artifact WebChat:war exploded: Artifact is deployed successfully
[2024-11-08 01:29:57,545] Artifact WebChat:war exploded: Deploy took 1,118 milliseconds
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXIT' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)
	at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1246)
	at org.webchat.repository.DatabaseConnection.getConnection(DatabaseConnection.java:25)
	at org.webchat.repository.UserDAOImpl.getUser(UserDAOImpl.java:22)
	at org.webchat.repository.UsersRepoImpl.getUser(UsersRepoImpl.java:11)
	at org.webchat.servlets.ListChatsServlet.doGet(ListChatsServlet.java:22)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:663)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:904)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:833)
java.sql.SQLException: No database selected
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
	at org.webchat.repository.UserDAOImpl.getUser(UserDAOImpl.java:27)
	at org.webchat.repository.UsersRepoImpl.getUser(UsersRepoImpl.java:11)
	at org.webchat.servlets.ListChatsServlet.doGet(ListChatsServlet.java:22)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:663)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:904)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:833)
08-Nov-2024 13:30:06.046 INFO [Catalina-utility-2] org.apache.catalina.startup.HostConfig.deployDirectory Deploying web application directory [/home/daniyar/Downloads/apache-tomcat-10.1.29/webapps/manager]
08-Nov-2024 13:30:06.078 INFO [Catalina-utility-2] org.apache.catalina.startup.HostConfig.deployDirectory Deployment of web application directory [/home/daniyar/Downloads/apache-tomcat-10.1.29/webapps/manager] has finished in [29] ms
/home/daniyar/Downloads/apache-tomcat-10.1.29/bin/catalina.sh stop
08-Nov-2024 13:31:39.001 INFO [main] org.apache.catalina.core.StandardServer.await A valid shutdown command was received via the shutdown port. Stopping the Server instance.
08-Nov-2024 13:31:39.002 INFO [main] org.apache.coyote.AbstractProtocol.pause Pausing ProtocolHandler ["http-nio-8080"]
08-Nov-2024 13:31:39.003 INFO [main] org.apache.catalina.core.StandardService.stopInternal Stopping service [Catalina]
08-Nov-2024 13:31:39.009 WARNING [main] org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesJdbc The web application [WebChat_war_exploded] registered the JDBC driver [com.mysql.cj.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
08-Nov-2024 13:31:39.009 WARNING [main] org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesThreads The web application [WebChat_war_exploded] appears to have started a thread named [mysql-cj-abandoned-connection-cleanup] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 java.base@17.0.8/java.lang.Object.wait(Native Method)
 java.base@17.0.8/java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:155)
 com.mysql.cj.jdbc.AbandonedConnectionCleanupThread.run(AbandonedConnectionCleanupThread.java:91)
 java.base@17.0.8/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
 java.base@17.0.8/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
 java.base@17.0.8/java.lang.Thread.run(Thread.java:833)
08-Nov-2024 13:31:39.013 INFO [main] org.apache.coyote.AbstractProtocol.stop Stopping ProtocolHandler ["http-nio-8080"]
08-Nov-2024 13:31:39.026 INFO [main] org.apache.coyote.AbstractProtocol.destroy Destroying ProtocolHandler ["http-nio-8080"]
Disconnected from server