JDBC.java

package githubsqlfinder;

import java.sql.*;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;

public class JDBC {

    private Connection connection = null;

    private void connect() {
        try 
        {
            this.connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);
            statement.executeUpdate("create table if not exists sql_info (id integer primary key, sql string, repo_name string, file_path string, db_dialect string)");
        } 
        catch(SQLException e) 
        {
            e.printStackTrace(System.err);
        }
    }

    public void deleteDb() {
        if (this.connection == null) {
            this.connect();
        }
        try {
            Statement statement = this.connection.createStatement();
            statement.executeUpdate("drop table if exists sql_info");
            statement.executeUpdate("create table if not exists sql_info (id integer primary key, sql string, repo_name string, file_path string, db_dialect string)");
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.out.printf("%d ||| %s", e.getErrorCode(), e.getMessage());
        }
    }

    public void addSqlInfo(String sql, String repoName, String filePath, String dbDialect) {

        if (sql.equals("<EOF>")  || sql.equals(";") || sql.equals("<EOF>;")) {
            return;
        }

        if (this.connection == null) {
            this.connect();
        }

        try {
            PreparedStatement pstmt = connection.prepareStatement("insert into sql_info(sql, repo_name, file_path, db_dialect) values(?, ?, ?, ?)");

            pstmt.setString(1, sql);
            pstmt.setString(2, repoName);
            pstmt.setString(3, filePath);
            pstmt.setString(4, dbDialect);

            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.out.printf("%d ||| %s", e.getErrorCode(), e.getMessage());
        }
    }

    public void showPage(int offset, boolean all) {

        if (this.connection == null) {
            this.connect();
        }

        try {
            Statement statement = this.connection.createStatement();

            String sql;
            if (all) {
                sql = "select * from sql_info";
            } else {
                sql = "select * from sql_info limit 100 offset " + offset;
            }
 
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("| Id | Repozitory | File | DB dialect | SQL query |");
            System.out.println( "| -- | -- | -- | -- | -- |");
            while(rs.next())
            {
                System.out.printf(" | %d | %s | %s | %s | %s |\n", rs.getInt("id"), rs.getString("repo_name"),  rs.getString("file_path"),  rs.getString("db_dialect"),  rs.getString("sql"));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.out.printf("%d ||| %s", e.getErrorCode(), e.getMessage());
        }
    }

    public static void writeCsvFile(String fileName, List<String[]> data) {
        try (FileWriter writer = new FileWriter(fileName)) {
            for (String[] row : data) {
                writeRow(writer, row);
            }
            System.out.println("CSV file created: " + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void writeRow(FileWriter writer, String[] row) throws IOException {
        for (int i = 0; i < row.length; i++) {
            writer.append(row[i]);
            if (i < row.length - 1) {
                writer.append(",");
            }
        }
        writer.append("\n");
    }
}