Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.time.LocalDate;
- import java.time.format.DateTimeFormatter;
- import javafx.application.Application;
- import javafx.beans.property.SimpleStringProperty;
- import javafx.beans.value.ObservableValue;
- import javafx.collections.FXCollections;
- import javafx.collections.ObservableList;
- import javafx.event.ActionEvent;
- import javafx.event.EventHandler;
- import javafx.geometry.Insets;
- import javafx.scene.Scene;
- import javafx.scene.control.*;
- import javafx.scene.layout.ColumnConstraints;
- import javafx.scene.layout.GridPane;
- import javafx.scene.layout.VBox;
- import javafx.stage.Stage;
- import javafx.util.Callback;
- public class Main extends Application {
- static int fileCounter;
- // Default window buttons and text fields
- Button searchBtn;
- Button addBtn;
- TextField titleField = new TextField();
- TextField categoriesField = new TextField();
- TextField summaryField = new TextField();
- TextField descriptionField = new TextField();
- Button analysisBtn;
- //Primary stage
- public void start(Stage primaryStage) throws SQLException {
- GridPane grid = new GridPane();
- ColumnConstraints column;
- column = new ColumnConstraints();
- column.setPercentWidth(15);
- grid.getColumnConstraints().add(column);
- column = new ColumnConstraints();
- column.setPercentWidth(70);
- grid.getColumnConstraints().add(column);
- grid.setHgap(10);
- grid.setVgap(10);
- grid.setPadding(new Insets(15, 15, 15, 15));
- addBtn = new Button("Add");
- addBtn.setMaxWidth(Double.MAX_VALUE);
- grid.add(addBtn, 0, 0);
- titleField.setPromptText("Enter the movie title...");
- titleField.setPrefHeight(50);
- titleField.getText();
- grid.add(titleField, 1, 0);
- categoriesField.setPromptText("Enter the movie category...");
- categoriesField.setPrefHeight(50);
- categoriesField.getText();
- grid.add(categoriesField, 1, 1);
- summaryField.setPromptText("Enter the movie summary...");
- summaryField.setPrefHeight(50);
- summaryField.getText();
- grid.add(summaryField,1, 2);
- descriptionField.setPromptText("Enter the move description...");
- descriptionField.setPrefHeight(50);
- descriptionField.getText();
- grid.add(descriptionField, 1, 3);
- searchBtn = new Button("Search");
- searchBtn.setMaxWidth(Double.MAX_VALUE);
- grid.add(searchBtn, 0, 1);
- analysisBtn = new Button("Analysis");
- analysisBtn.setMaxWidth(Double.MAX_VALUE);
- grid.add(analysisBtn, 0, 2);
- primaryStage.setTitle("NMBPMovies");
- VBox layout = new VBox();
- DatabaseHandler handler = new DatabaseHandler();
- //Add a new movie to database
- addBtn.setOnAction(new EventHandler<ActionEvent>() {
- @Override
- public void handle(ActionEvent event) {
- String title = titleField.getText();
- String category = categoriesField.getText();
- String summary = summaryField.getText();
- String description = descriptionField.getText();
- Statement stmt = null;
- try {
- stmt = handler.connection.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- String query = "INSERT INTO movie (title, categories, summary, description) "
- + "VALUES ('" + title + "', '" + category +"', '" + summary + "', '" + description + "');";
- System.out.println(query);
- try {
- stmt.executeUpdate(query);
- } catch (SQLException e) {
- e.printStackTrace();
- }
- try {
- stmt.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- titleField.setText("");
- categoriesField.setText("");
- summaryField.setText("");
- descriptionField.setText("");
- }
- });
- //Open new window when Analysis button is clicked
- analysisBtn.setOnAction(new EventHandler<ActionEvent>() {
- @Override
- public void handle(ActionEvent event) {
- Stage analysisStage = new Stage();
- analysisStage.setTitle("Analysis info");
- DatePicker dateFrom = new DatePicker();
- DatePicker dateTo = new DatePicker();
- ToggleGroup group = new ToggleGroup();
- RadioButton hoursBtn = new RadioButton("Hours");
- RadioButton daysBtn = new RadioButton("Days");
- hoursBtn.setToggleGroup(group);
- hoursBtn.setSelected(true);
- daysBtn.setToggleGroup(group);
- TableView analysisResult = new TableView();
- Button searchButton = new Button("Search");
- //Date and radio grid
- GridPane grid1 = new GridPane();
- grid1.setHgap(10);
- grid1.setVgap(10);
- grid1.setPadding(new Insets(15, 15, 15, 15));
- ColumnConstraints column;
- column = new ColumnConstraints();
- column.setPercentWidth(30);
- grid1.getColumnConstraints().add(column);
- grid1.add(dateFrom, 0, 0);
- grid1.add(dateTo, 1, 0);
- grid1.add(hoursBtn, 0, 1);
- grid1.add(daysBtn, 0, 2);
- grid1.add(searchButton, 0, 3);
- //Results grid
- GridPane grid2 = new GridPane();
- grid2.setHgap(10);
- grid2.setVgap(10);
- grid2.setPadding(new Insets(15, 15, 15, 15));
- analysisResult.setPrefHeight(300);
- analysisResult.setPrefWidth(800);
- grid2.add(analysisResult, 0, 0);
- VBox layout = new VBox();
- Scene scene = new Scene(layout, 900, 400);
- ((VBox) scene.getRoot()).getChildren().addAll(grid1);
- ((VBox) scene.getRoot()).getChildren().addAll(grid2);
- analysisStage.setScene(scene);
- analysisStage.show();
- // Show analysis table based on the selected options
- searchButton.setOnAction(new EventHandler<ActionEvent>() {
- @Override
- public void handle(ActionEvent event) {
- ObservableList<ObservableList> data = FXCollections.observableArrayList();
- if(hoursBtn.isSelected()) {
- if(!data.isEmpty()) {
- data.clear();
- }
- Statement stmt = null;
- try {
- stmt = handler.connection.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- String query = "select * from crosstab('select querystring, date_part, count (*) from hourAnalysis\n" +
- "group by querystring, date_part\n" +
- "order by date_part', 'SELECT hour from hours order by hour') as \n" +
- "(query char(200),\n" +
- "sat00 INT,\n" +
- "sat1 INT,\n" +
- "sat2 INT,\n" +
- "sat3 INT,\n" +
- "sat4 INT,\n" +
- "sat5 INT,\n" +
- "sat6 INT,\n" +
- "sat7 INT,\n" +
- "sat8 INT,\n" +
- "sat9 INT,\n" +
- "sat10 INT,\n" +
- "sat11 INT,\n" +
- "sat12 INT,\n" +
- "sat13 INT,\n" +
- "sat14 INT,\n" +
- "sat15 INT,\n" +
- "sat16 INT,\n" +
- "sat17 INT,\n" +
- "sat18 INT,\n" +
- "sat19 INT,\n" +
- "sat20 INT,\n" +
- "sat21 INT,\n" +
- "sat22 INT,\n" +
- "sat23 INT);";
- ResultSet rset = null;
- try {
- rset = stmt.executeQuery(query);
- } catch (SQLException e){
- e.printStackTrace();
- }
- ResultSetMetaData rsmd = null;
- try {
- rsmd = rset.getMetaData();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- int columnCount = 0;
- try {
- columnCount = rsmd.getColumnCount();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- for(int i = 0; i < columnCount; i++) {
- final int j = i;
- TableColumn col = null;
- try {
- col = new TableColumn(rsmd.getColumnName(i+1));
- } catch (SQLException e) {
- e.printStackTrace();
- }
- col.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ObservableList,String>,ObservableValue<String>>(){
- public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
- if(param.getValue().get(j) != null) {
- return new SimpleStringProperty(param.getValue().get(j).toString());
- }
- return new SimpleStringProperty("");
- }
- });
- analysisResult.getColumns().addAll(col);
- }
- try {
- while(rset.next()){
- //Iterate Row
- ObservableList<String> row = FXCollections.observableArrayList();
- for(int i=1 ; i<=rset.getMetaData().getColumnCount(); i++){
- //Iterate Column
- row.add(rset.getString(i));
- }
- System.out.println("Row [1] added "+row );
- data.add(row);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- analysisResult.setItems(data);
- } if(daysBtn.isSelected()) {
- data = FXCollections.observableArrayList();
- Statement stmt = null;
- try {
- stmt = handler.connection.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- String asPart = "";
- LocalDate startDate = dateFrom.getValue();
- LocalDate endDate = dateTo.getValue();
- while (startDate.isBefore(endDate.plusDays(1))) {
- asPart +="\n\"" + startDate.toString() + "\" char(50),";
- startDate = startDate.plusDays(1);
- System.out.println(startDate);
- }
- asPart = asPart.substring(0, asPart.length() - 1);
- String query = "select * from crosstab('select querystring, date, count(*) from dateAnalysis\n" +
- "where date between ''" + dateFrom.getValue() + "'' AND ''" + dateTo.getValue() + "''\n" +
- "group by querystring, date\n" +
- "order by date', 'select (date(generate_series)) as date from generate_series(''"+ dateFrom.getValue() +"''::timestamp, ''" + dateTo.getValue() + "''::timestamp, ''1 day'')')\n" +
- "as (query char(200)," + asPart +
- ")";
- System.out.println(query);
- ResultSet rset = null;
- try {
- rset = stmt.executeQuery(query);
- } catch (SQLException e){
- e.printStackTrace();
- }
- ResultSetMetaData rsmd = null;
- try {
- rsmd = rset.getMetaData();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- int columnCount = 0;
- try {
- columnCount = rsmd.getColumnCount();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- for(int i = 0; i < columnCount; i++) {
- final int j = i;
- TableColumn col = null;
- try {
- col = new TableColumn(rsmd.getColumnName(i+1));
- } catch (SQLException e) {
- e.printStackTrace();
- }
- col.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ObservableList,String>,ObservableValue<String>>(){
- public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
- if(param.getValue().get(j) != null) {
- return new SimpleStringProperty(param.getValue().get(j).toString());
- }
- return new SimpleStringProperty("");
- }
- });
- analysisResult.getColumns().addAll(col);
- }
- try {
- while(rset.next()){
- //Iterate Row
- ObservableList<String> row = FXCollections.observableArrayList();
- for(int i=1 ; i<=rset.getMetaData().getColumnCount(); i++){
- //Iterate Column
- row.add(rset.getString(i));
- }
- System.out.println("Row [1] added "+row );
- data.add(row);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- analysisResult.setItems(data);
- }
- }
- });
- }
- });
- //Open new window when Search button is clicked
- searchBtn.setOnAction(new EventHandler<ActionEvent>() {
- @Override
- public void handle(ActionEvent event) {
- //Search scene buttons and text fields
- Stage searchStage = new Stage();
- searchStage.setTitle("Search the database");
- fileCounter = 0;
- TextField searchString = new TextField();
- Button searchBtn2 = new Button("Search");
- ToggleGroup group = new ToggleGroup();
- RadioButton andBtn = new RadioButton("AND");
- RadioButton orBtn = new RadioButton("OR");
- andBtn.setToggleGroup(group);
- andBtn.setSelected(true);
- orBtn.setToggleGroup(group);
- Label label = new Label("Query string:");
- TextArea queryStringArea = new TextArea();
- Label retrieve = new Label("Number of documents retrieved: " + fileCounter);
- TextArea resultStrings = new TextArea();
- GridPane grid = new GridPane();
- grid.setHgap(10);
- grid.setVgap(10);
- grid.setPadding(new Insets(15, 15, 15, 15));
- ColumnConstraints column;
- column = new ColumnConstraints();
- column.setPercentWidth(250);
- grid.getColumnConstraints().add(column);
- column = new ColumnConstraints();
- column.setPercentWidth(50);
- grid.getColumnConstraints().add(column);
- searchString.setPrefHeight(20);
- searchString.getText();
- grid.add(searchString, 0, 0);
- searchBtn2.setPrefHeight(20);
- grid.add(searchBtn2, 1, 0);
- grid.add(andBtn, 0, 1);
- grid.add(orBtn, 0, 2);
- grid.add(label, 0, 3);
- queryStringArea.setPrefHeight(150);
- grid.add(queryStringArea, 0, 4);
- grid.add(retrieve, 0, 5);
- resultStrings.setPrefHeight(150);
- grid.add(resultStrings, 0, 6);
- VBox layout = new VBox();
- Scene scene = new Scene(layout, 900, 400);
- ((VBox) scene.getRoot()).getChildren().addAll(grid);
- searchStage.setScene(scene);
- searchStage.show();
- //Search for movies based on search entry
- searchBtn2.setOnAction(new EventHandler<ActionEvent>() {
- @Override
- public void handle(ActionEvent event) {
- String searchBar = searchString.getText();
- StringHandler stringHandler = new StringHandler(searchBar);
- stringHandler.splitTheSearch(stringHandler.inputString);
- StringHandler.makeTSQueryString();
- String tsqueryString = "'";
- String queryName = "";
- if(orBtn.isSelected()) {
- StringHandler.wherePartOR();
- for(String s : StringHandler.queryElements) {
- tsqueryString += s + " | ";
- }
- for (String s : StringHandler.queryName) {
- queryName += s + " | ";
- }
- queryName = queryName.substring(0, queryName.length() - 3);
- tsqueryString = tsqueryString.substring(0, tsqueryString.length() - 3);
- tsqueryString += "'";
- } else {
- StringHandler.wherePartAND();
- for(String s : StringHandler.queryElements) {
- tsqueryString += s + " & ";
- }
- for (String s : StringHandler.queryName) {
- queryName += s + " & ";
- }
- queryName = queryName.substring(0, queryName.length() - 3);
- tsqueryString = tsqueryString.substring(0, tsqueryString.length() - 3);
- tsqueryString += "'";
- }
- System.out.println(queryName);
- String query = "";
- String analysisInsertQuery = "";
- query = "SELECT movieID, ts_headline(title, to_tsquery('english', " + tsqueryString +"))," +
- "\n"+"ts_headline(description, to_tsquery('english', " + tsqueryString +"))," +
- "\n"+"description, " +
- "\nts_rank(allAboutMovieTSV, to_tsquery('english', " + tsqueryString +")) rank" +
- "\n"+"FROM movie WHERE " + StringHandler.wherePartString +
- "\nORDER BY rank DESC";
- analysisInsertQuery = "INSERT INTO analysis (querystring) " +
- "VALUES (" + "'" + queryName + "'" + ");";
- queryStringArea.setText(query);
- System.out.println(analysisInsertQuery);
- Statement stmt = null;
- try {
- stmt = handler.connection.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- ResultSet rset = null;
- try {
- rset = stmt.executeQuery(query);
- String searchedMovies = "";
- fileCounter = 0;
- while(rset.next()) {
- fileCounter++;
- String name = rset.getString("ts_headline");
- float rank = rset.getFloat("rank");
- searchedMovies += name + " [" + rank + "] \n";
- }
- stmt.executeUpdate(analysisInsertQuery);
- resultStrings.setText(searchedMovies);
- retrieve.setText("Number of documents retrieved: " + fileCounter);
- searchedMovies = "";
- fileCounter = 0;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- });
- }
- });
- Scene scene = new Scene(layout, 900, 300);
- ((VBox) scene.getRoot()).getChildren().addAll(grid);
- primaryStage.setScene(scene);
- primaryStage.show();
- }
- public static void main(String[] args) {
- launch(args);
- }
- }
- import java.util.ArrayList;
- import java.util.Arrays;
- import java.util.List;
- import java.util.regex.Matcher;
- import java.util.regex.Pattern;
- public class StringHandler {
- static String inputString;
- static String wherePartString;
- static List<String> splitSearch = new ArrayList<String>();
- static List<String> queryElements = new ArrayList<String>();
- static List<String> queryName = new ArrayList<>();
- static String queryPart = "";
- StringHandler(String string) {
- this.inputString = string;
- }
- public void splitTheSearch(String inputString) {
- splitSearch.clear();
- Matcher m = Pattern.compile("([^\"]\\S*|\".+?\")\\s*").matcher(inputString);
- while (m.find())
- splitSearch.add(m.group(1));
- }
- public static void makeTSQueryString() {
- queryElements.clear();
- queryName.clear();
- for (String s : splitSearch) {
- String TSQueryString = "";
- if(s.startsWith("\"") && s.endsWith("\"")) {
- TSQueryString = "(";
- queryPart = "^";
- s = s.substring(1, s.length()-1);
- List<String> wordByWord = Arrays.asList(s.split(" "));
- for (String c : wordByWord) {
- if(wordByWord.indexOf(c) != (wordByWord.size() - 1)) {
- TSQueryString += c + " & ";
- queryPart += c + " ";
- } else {
- TSQueryString += c + ")";
- queryPart += c + "^";
- }
- }
- } else {
- TSQueryString = s;
- queryPart = s;
- }
- queryElements.add(TSQueryString);
- queryName.add(queryPart);
- System.out.println(queryName);
- }
- }
- public static void wherePartOR() {
- wherePartString = "";
- for (String s : queryElements) {
- if(s.startsWith("(") && s.endsWith(")")) {
- s = s.substring(1, s.length() - 1);
- s = "'" + s + "'";
- } else {
- s = "'" + s + "'";
- }
- wherePartString += "allAboutMovieTSV @@ to_tsquery('english'," + s + ") OR\n";
- }
- wherePartString = wherePartString.substring(0, wherePartString.length() - 3);
- }
- public static void wherePartAND() {
- wherePartString = "";
- for (String s : queryElements) {
- if(s.startsWith("(") && s.endsWith(")")) {
- s = s.substring(1, s.length() - 1);
- s = "'" + s + "'";
- } else {
- s = "'" + s + "'";
- }
- wherePartString += "allAboutMovieTSV @@ to_tsquery('english'," + s + ") AND\n";
- }
- wherePartString = wherePartString.substring(0, wherePartString.length() - 4);
- }
- }
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.sql.ResultSet;
- public class DatabaseHandler {
- public Connection connection = null;
- public DatabaseHandler() {
- try {
- connection = DriverManager
- .getConnection("jdbc:postgresql://192.168.56.12:5432/Movies",
- "postgres", "reverse");
- } catch (SQLException e) {
- e.printStackTrace();
- System.out.println("SQLException: " + e.getMessage());
- System.out.println("SQLState: " + e.getSQLState());
- System.out.println("VendorError: " + e.getErrorCode());
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement