package postidx;

import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.table.AbstractTableModel;

public class postidxTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 1L;
	
	//public static Statement statmt;
    //public static ResultSet resSet;
	
	private int columnCount = 6;
	private ArrayList<String []> DataArrayList;
	
	public postidxTableModel() {
		DataArrayList = new ArrayList<String []>();
		for (int i = 0; i < DataArrayList.size(); i++) {
			DataArrayList.add(new String[getColumnCount()]);
		}
	}

	@Override
	public int getColumnCount() {
		return columnCount;
	}

	@Override
	public String getColumnName(int columnIndex) {
		switch(columnIndex) {
			case 0: return "Индекс";
			case 1: return "Наименование ОПС";
			case 2: return "Регион";
			case 3: return "Автономная область";
			case 4: return "Район";
			case 5: return "Населенный пункт";
		}
		return "";
	}

	@Override
	public int getRowCount() {
		return DataArrayList.size();
	}

	@Override
	public Object getValueAt(int rowIndex, int columnIndex) {
		String []rows = DataArrayList.get(rowIndex);
		return rows[columnIndex];
	}

	public void addData(String []row) {
		String []rowTable = new String[getColumnCount()];
		rowTable = row;
		DataArrayList.add(rowTable);
	}
	
	public void addData(ConnectionDb connect, String querystr) throws SQLException {
		ResultSet result = connect.resultSetQuery(querystr);
	    try {
			while(result.next())
			{
				String []row = {
						result.getString("idx"),
						result.getString("opsname"),
						result.getString("region"),
						result.getString("autonom"),
						result.getString("area"),
						result.getString("city")
				};
				addData(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

package postidx;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.GridBagConstraints;
import java.awt.Insets;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import java.awt.Toolkit;
import java.awt.Dimension;
import java.util.Locale;
import javax.swing.JMenuBar;
import javax.swing.JMenu;
import javax.swing.JMenuItem;
import javax.swing.JPanel;
import javax.swing.KeyStroke;
import javax.swing.border.EmptyBorder;

import java.awt.event.KeyEvent;
import java.sql.SQLException;
import java.awt.event.InputEvent;
import javax.swing.ImageIcon;
import javax.swing.JTable;
import java.awt.Font;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;


public class frmMain extends JFrame {

	private static final long serialVersionUID = 1L;

    public static String mainQueryStr = "SELECT idx, opsname, region, autonom, area, city FROM postidx";
	public static JTable postidxTable = null;
    
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					frmMain frame = new frmMain();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	public frmMain() {
		setLocale(new Locale("ru"));
		setSize(new Dimension(900, 700));
		setResizable(false);
		setName("MainForm");
		setMinimumSize(new Dimension(900, 700));
		setTitle("\u041F\u043Eчт\u043Eв\u044Bе и\u043Dде\u043Aс\u044B суб\u044Aе\u043Aт\u043Eв РФ");
		setIconImage(Toolkit.getDefaultToolkit().getImage(frmMain.class.getResource("/postidx/res/postidx.png")));
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 700, 379);
		setVisible(true);

		pack();
		setLocationRelativeTo(null);


		ConnectionDb connect = new ConnectionDb();
		connect.initProperties();
		connect.init();
		
		JMenuBar menuBar = new JMenuBar();
		menuBar.setName("menubar1");
		setJMenuBar(menuBar);
		
		JMenu mmWorkWithDB = new JMenu("Раб\u043Eта с баз\u043Eй");
		mmWorkWithDB.setName("mmWorkWithDB");
		menuBar.add(mmWorkWithDB);
		
		JMenuItem mniFind = new JMenuItem("\u041F\u043Eис\u043A за\u043Fисей");
		mniFind.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				frmFind frmFind = new frmFind();
				frmFind.setVisible(true);
			}
		});
		mniFind.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/find.png")));
		mniFind.setAccelerator(KeyStroke.getKeyStroke(KeyEvent.VK_F, InputEvent.CTRL_MASK));
		mmWorkWithDB.add(mniFind);
		
		JMenuItem mniSort = new JMenuItem("С\u043Eртир\u043Eв\u043Aа за\u043Fисей");
		mniSort.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/sort.png")));
		mniSort.setAccelerator(KeyStroke.getKeyStroke(KeyEvent.VK_F8, InputEvent.CTRL_MASK));
		mmWorkWithDB.add(mniSort);
		
		JMenu mmNavigation = new JMenu("\u041Dавигаци\u044F");
		mmNavigation.setName("mmNavigation");
		menuBar.add(mmNavigation);
		
		JMenuItem mniNavigation = new JMenuItem("\u041Eбрат\u043D\u043E в режи\u043C \u043Fр\u043Eс\u043C\u043Eтра");
		mniNavigation.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/undo.png")));
		mmNavigation.add(mniNavigation);
		
		JMenu mmHelp = new JMenu("\u041F\u043E\u043C\u043Eщ\u044C");
		mmHelp.setName("mmHelp");
		menuBar.add(mmHelp);
		
		JMenuItem mniAbout = new JMenuItem("\u041E \u043Fр\u043Eгра\u043C\u043Cе");
		mniAbout.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/about.png")));
		mniAbout.setAccelerator(KeyStroke.getKeyStroke(KeyEvent.VK_F9, 0));
		mmHelp.add(mniAbout);
		
		JMenuItem mniReadme = new JMenuItem("\u041Aрат\u043Aа\u044F и\u043Dф\u043Eр\u043Cаци\u044F");
		mniReadme.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/readme.png")));
		mmHelp.add(mniReadme);
		
		JMenuItem mniHistory = new JMenuItem("Ист\u043Eри\u044F из\u043Cе\u043Dе\u043Dий");
		mniHistory.setIcon(new ImageIcon(frmMain.class.getResource("/postidx/res/history.png")));
		mmHelp.add(mniHistory);
		
		JMenu mmExit = new JMenu("В\u044Bх\u043Eд");
		mmExit.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent arg0) {
				connect.closeConnection();
		        System.exit(0);
			}
		});
		mmExit.setName("mmExit");
		menuBar.add(mmExit);

		JPanel contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(1, 1, 1, 1));
		contentPane.setLayout(new BorderLayout(0, 0));
		setContentPane(contentPane);

		postidxTableModel ptm = new postidxTableModel();
		JTable postidxTable = new JTable(ptm);
		postidxTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		postidxTable.setSize(new Dimension(895, 650));

//		setColumnsWidth();
		JScrollPane postidxTableScrollPane = new JScrollPane(postidxTable);
		postidxTableScrollPane.setFont(new Font("Dialog", Font.BOLD, 12));
		postidxTableScrollPane.setSize(new Dimension(895, 650));

		try {
			ptm.addData(connect, mainQueryStr);
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		contentPane.add(postidxTableScrollPane, new GridBagConstraints(0,0,1,1,1,1,
				GridBagConstraints.NORTH, GridBagConstraints.BOTH, new Insets(1,1,1,1), 0,0));
	}

/*	public void setColumnsWidth() {
		postidxTable.getColumnModel().getColumn(0).setMinWidth(70);
		postidxTable.getColumnModel().getColumn(0).setMaxWidth(70);
		postidxTable.getColumnModel().getColumn(1).setMinWidth(350);
		postidxTable.getColumnModel().getColumn(1).setMaxWidth(350);
		postidxTable.getColumnModel().getColumn(2).setMinWidth(200);
		postidxTable.getColumnModel().getColumn(2).setMaxWidth(200);
		postidxTable.getColumnModel().getColumn(3).setMinWidth(190);
		postidxTable.getColumnModel().getColumn(3).setMaxWidth(190);
		postidxTable.getColumnModel().getColumn(4).setMinWidth(180);
		postidxTable.getColumnModel().getColumn(4).setMaxWidth(180);
		postidxTable.getColumnModel().getColumn(5).setMinWidth(170);
		postidxTable.getColumnModel().getColumn(5).setMaxWidth(170);
	} */

}

package postidx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;


public class ConnectionDb {

	private String curdir = System.getProperty("user.dir");
	private String dataDb = "db/postidx.sqlite";
	private String url;
	
	private Properties properties = new Properties();
	private Connection sqliteConnection;

	// Конструктор для класса
	public ConnectionDb() {
		
	}

	public ConnectionDb(String url, Properties properties) {
		this.url = url;
		this.properties = properties;
	}
	
	public void setNameDataBases(String DataDb) {
		this.dataDb = DataDb;
	}

	public void initProperties() {
		this.url = "jdbc:sqlite:" + curdir + "/" + this.dataDb;
		
		properties.setProperty("useUnicode", "true");
		properties.setProperty("characterEncoding", "UTF-8");
		
		System.out.println(url);
	}
	
	public int init() {
		if (sqliteConnection != null) {
			try {
				sqliteConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			Class.forName("org.sqlite.JDBC");
			sqliteConnection = DriverManager.getConnection(url, properties);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return -1;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		return 0;
	}

	public void finalize() {
		try {
			sqliteConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void openConnection() {
		init();
	}
	
	public void closeConnection() {
		try {
			sqliteConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ResultSet resultSetQuery(String query) {
		try {
			java.sql.Statement stmt = sqliteConnection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
}

package postidx.model;

import com.jfoenix.controls.datamodels.treetable.RecursiveTreeObject;

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;


public class postidx extends RecursiveTreeObject<postidx> {

	// Опишем свойства столбцов
    public StringProperty idx;
    public StringProperty opsname;
    public StringProperty region;
    public StringProperty autonom;
    public StringProperty area;
    public StringProperty city;

    // Конструктор по умолчанию
    public postidx() {
    	this(null, null, null, null, null, null);
    }
    
    // Конструктор с некоторыми начальными данными
    public postidx(String idx, String opsname, String region, String autonom, String area, String city) {

    	// начальные данные
        this.idx =  new SimpleStringProperty(idx);
        this.opsname = new SimpleStringProperty(opsname);
        this.region = new SimpleStringProperty(region);
        this.autonom = new SimpleStringProperty(autonom);
        this.area = new SimpleStringProperty (area);
        this.city = new SimpleStringProperty (city);
    }

    // Геттеры и сеттеры
    
    public String getIdx() {
        return idx.get();
    }

    public void setIdx(String idx) {
        this.idx.set(idx);
    }

    public StringProperty idxProperty() {
        return idx;
    }

    public String getOPSName() {
        return opsname.get();
    }

    public void setOPSName(String opsname) {
        this.opsname.set(opsname);
    }

    public StringProperty OPSNameProperty() {
        return opsname;
    }

    public String getRegion() {
        return region.get();
    }

    public void setRegion(String region) {
        this.region.set(region);
    }

    public StringProperty regionProperty() {
        return region;
    }

    public String getAutonom() {
        return autonom.get();
    }

    public void setAutonom(String autonom) {
        this.autonom.set(autonom);
    }

    public StringProperty autonomProperty() {
        return autonom;
    }

    public String getArea() {
        return area.get();
    }

    public void setArea(String area) {
        this.area.set(area);
    }

    public StringProperty areaProperty() {
        return area;
    }
    
    public String getCity() {
        return city.get();
    }

    public void setCity(String city) {
        this.city.set(city);
    }

    public StringProperty cityProperty() {
        return city;
    }

}

package postidx.view;

import java.io.IOException;

import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;
import postidx.MainApp;
import postidx.view.postidxOverviewController;

// Контроллер для корневого макета. Корневой макет предоставляет базовый
// макет приложения, содержащий строку меню и место, где будут размещены
// остальные элементы JavaFX
public class rootLayoutController {
	
    // Ссылка на главное приложение
    @SuppressWarnings("unused")
	private MainApp mainApp;
    
    // Ссылка на контроллер
    private postidxOverviewController postidxController;

    public rootLayoutController () {
    	
    }
    
    // Открывает панель быстрого поиска
    @FXML private void mniQuickSearch(ActionEvent ae) throws IOException {
        FXMLLoader loader = new FXMLLoader();
        loader.setLocation(MainApp.class.getResource("view/postidxOverview.fxml"));
	    postidxController = loader.getController();
        postidxController.ShowCloseQuickSearchPane();
    }
    
    // Открывает форму поиска
    @FXML private void mniFind(ActionEvent ae) {
        try {
            new frmFind();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Открывает диалоговое окно About
    @FXML private void mniAbout() {
        Alert alert = new Alert(AlertType.INFORMATION);
        alert.setTitle("Почтовые индексы субъектов Российской Федерации");
        alert.setHeaderText("О программе");
        alert.setContentText("Почтовые индексы субъектов РФ\n"+
        		"версия 4.0\n\n"+
        		"Copyright (c) 2002-2018 by BSA Soft\n\n"+
        		"По данным сайта Почты России\n"+
        		"по состоянию на 18.12.2017 г.\n\n"+
        		"Website: http://bsasoft.ru\n"+
        		"E-mail: bsasoftrus@gmail.com");

        alert.showAndWait();
    }

    // Закрывает приложение
    @FXML private void mniExit() {
        System.exit(0);
    }

    // Вызывается главным приложением, чтобы оставить ссылку на самого себя
    public void setMainApp(MainApp mainApp) {
        this.mainApp = mainApp;
    }

}

package postidx.view;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.function.Predicate;

import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
//import javafx.collections.ListChangeListener;
import javafx.collections.ObservableList;
//import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.TreeItem;
import javafx.scene.control.TreeTableColumn;
import javafx.util.Callback;

import com.jfoenix.controls.JFXTreeTableColumn;
import com.jfoenix.controls.JFXTreeTableView;
import com.jfoenix.controls.RecursiveTreeItem;
import com.jfoenix.controls.datamodels.treetable.RecursiveTreeObject;
import com.jfoenix.controls.JFXButton;

import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.Pane;

import com.jfoenix.controls.JFXTextField;

import postidx.MainApp;
import postidx.model.postidx;

public class postidxOverviewController {

    // Данные в виде наблюдаемого списка индексов
    private ObservableList<postidx> data = FXCollections.observableArrayList();
    
    @FXML public JFXTreeTableView<postidx> postidxTable;
    @FXML public JFXTreeTableColumn<postidx, String> idx;
    @FXML public JFXTreeTableColumn<postidx, String> opsname;
    @FXML public JFXTreeTableColumn<postidx, String> region;
    @FXML public JFXTreeTableColumn<postidx, String> autonom;
    @FXML public JFXTreeTableColumn<postidx, String> area;
    @FXML public JFXTreeTableColumn<postidx, String> city;

    @FXML public AnchorPane anchorPane;
    
    @FXML public Pane quickSearchPane;
    @FXML public JFXTextField txtQuickSearch;
    @FXML public JFXButton btnClosePane;
    
	private String curdir = System.getProperty("user.dir");
	private String dataDb = "db/postidx.sqlite";
	private String url;
	private Properties properties = new Properties();
	
    public Connection con = null;
    public ResultSet rs = null;
    public Statement stmt = null;
    public String mainQueryStr = "SELECT idx, opsname, region, autonom, area, city FROM postidx";

    
    // Ссылка на главное приложение
	@SuppressWarnings("unused")
	private MainApp mainApp;
	// Ссылка на контроллер
	@SuppressWarnings("unused")
	private rootLayoutController rootLayoutController;
    
    // Конструктор
	public postidxOverviewController() {
	    //quickSearchPane = null;
	    //txtQuickSearch = null;
	    //btnClosePane = null;
    }

    // Инициализация класса-контроллера. Этот метод вызывается автоматически
	// после того, как fxml-файл будет загружен
	@SuppressWarnings("unchecked")
    @FXML private void initialize() {
    	
        // Инициализация таблицы
        try {
        	JFXTreeTableColumn<postidx, String> idx = new JFXTreeTableColumn<>("Индекс");
        	idx.setPrefWidth(75);
        	idx.setStyle("-fx-font-name: Liberation Serif;");
        	idx.setStyle("-fx-font-size: 11px;");
        	idx.setStyle("-fx-alignment: CENTER-LEFT;");
        	idx.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().idx;
        		}
        	});
        	
        	JFXTreeTableColumn<postidx, String> opsname = new JFXTreeTableColumn<>("Наименование ОПС");
        	opsname.setPrefWidth(350);
        	opsname.setStyle("-fx-font-name: Liberation Serif;");
        	opsname.setStyle("-fx-font-size: 11px;");
        	opsname.setStyle("-fx-alignment: CENTER-LEFT;");
        	opsname.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().opsname;
        		}
        	});
        	
        	JFXTreeTableColumn<postidx, String> region = new JFXTreeTableColumn<>("Регион");
        	region.setPrefWidth(200);
        	region.setStyle("-fx-font-name: Liberation Serif;");
        	region.setStyle("-fx-font-size: 11px;");
        	region.setStyle("-fx-alignment: CENTER-LEFT;");
        	region.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().region;
        		}
        	});
        	
        	JFXTreeTableColumn<postidx, String> autonom = new JFXTreeTableColumn<>("Автономная область");
        	autonom.setPrefWidth(190);
        	autonom.setStyle("-fx-font-name: Liberation Serif;");
        	autonom.setStyle("-fx-font-size: 11px;");
        	autonom.setStyle("-fx-alignment: CENTER-LEFT;");
        	autonom.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().autonom;
        		}
        	});
        	
        	JFXTreeTableColumn<postidx, String> area = new JFXTreeTableColumn<>("Район");
        	area.setPrefWidth(180);
        	area.setStyle("-fx-font-name: Liberation Serif;");
        	area.setStyle("-fx-font-size: 11px;");
        	area.setStyle("-fx-alignment: CENTER-LEFT;");
        	area.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().area;
        		}
        	});
        	
        	JFXTreeTableColumn<postidx, String> city = new JFXTreeTableColumn<>("Населенный пункт");
        	city.setPrefWidth(170);
        	city.setStyle("-fx-font-name: Liberation Serif;");
        	city.setStyle("-fx-font-size: 11px;");
        	city.setStyle("-fx-alignment: CENTER-LEFT;");
        	city.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<postidx, String>, ObservableValue<String>>() {
        		@Override
        		public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<postidx, String> param) {
        			return param.getValue().getValue().city;
        		}
        	});
        	
            initTable();
        	LoadDatasInTable(mainQueryStr);
        	//ShowCloseQuickSearchPane();
        	
		    final TreeItem<postidx> root = new RecursiveTreeItem<postidx>(data, RecursiveTreeObject::getChildren);
		    postidxTable.getColumns().setAll(idx, opsname, region, autonom, area, city);
		    postidxTable.setRoot(root);
		    postidxTable.setShowRoot(false);

		} catch (SQLException e) {
			e.printStackTrace();
		}
        //Быстрый поиск ро базе
    	txtQuickSearch.textProperty().addListener(new ChangeListener<String>() {
    		//@Override
    		public void changed(ObservableValue<? extends String> observable, String oldValue, String newValue) {
    			postidxTable.setPredicate(new Predicate<TreeItem<postidx>>() {
    				@Override
    				public boolean test(TreeItem<postidx> postidx) {
    					Boolean flag = postidx.getValue().idx.getValue().contains(newValue) || postidx.getValue().region.getValue().contains(newValue) ||
    							postidx.getValue().autonom.getValue().contains(newValue) || postidx.getValue().area.getValue().contains(newValue) ||
    							postidx.getValue().city.getValue().contains(newValue);
    					return flag;
    				}
    			});
    		}
    	});

    	// Слушатель базы. Если включить - отваливается соединение с базой
//        mainApp.getPostidxData().addListener(new ListChangeListener<postidx>() {
//        	
//        	@Override
//        	public void onChanged(Change<? extends postidx> c) {
//        		mainApp.getPostidxData();
//        	}
//        });
    }

    // Задаем св-ва соединения
    public void initTable() {
		try {
			this.url = "jdbc:sqlite:" + this.curdir + "/" + this.dataDb;
			properties.setProperty("useUnicode", "true");
			properties.setProperty("characterEncoding", "UTF-8");
			System.out.println(url);
		} catch (Exception e) {
			e.printStackTrace();
		}
    }
    
    // Подключимся к базе и получим начальные данные
    public void LoadDatasInTable(String querystr) throws SQLException {
		try {
				Class.forName("org.sqlite.JDBC");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			con = DriverManager.getConnection(url, properties);

            data.clear();
            stmt = con.createStatement();
            rs = stmt.executeQuery(querystr);
 
            try {
				while (rs.next()) {
				    String idx = rs.getString("idx");
				    String opsname = rs.getString("opsname");
				    String region = rs.getString("region");
				    String autonom = rs.getString("autonom");
				    String area = rs.getString("area");
				    String city = rs.getString("city");
				    data.add(new postidx(idx, opsname, region, autonom, area, city));
				}
            } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
        	// Если включить - соединение с базой отваливается и данные из таблицы пропадают
        	//closeConnection(con, stmt, rs);
		}
    }

    // Показать/скрыть панель быстрого поиска
    @FXML public void ShowCloseQuickSearchPane() {
    	
    	if (quickSearchPane.getParent().isVisible()) {
    		btnClosePane.setDisable(true);
    		txtQuickSearch.setText("");
    		txtQuickSearch.setDisable(true);
    		quickSearchPane.setOpacity(0.0F);
    	} else {
    		btnClosePane.setDisable(false);
    		txtQuickSearch.setText("");
    		txtQuickSearch.setDisable(false);
    		quickSearchPane.setOpacity(0.7F);
    	}
    }
    
    // Закрыть соединение с базой
    public void closeConnection(Connection con, Statement stmt, ResultSet rs) throws SQLException {
    	this.con = con;
    	this.stmt = stmt;
    	this.rs = rs;
    	
        con.close();
        stmt.close();
        rs.close();
    }
    
    // Вызывается главным приложением, которое даёт на себя ссылку
    public void setMainApp(MainApp mainApp) {
        this.mainApp = mainApp;
        
        // Добавление в таблицу данных из наблюдаемого списка
        //postidxTable.setTreeColumn((TreeTableColumn<postidx, ?>) mainApp.getPostidxData());
    }
    
}

package postidx.view;

import java.sql.SQLException;
import javax.swing.JOptionPane;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Label;
import javafx.scene.control.RadioButton;
import javafx.scene.control.ToggleGroup;
import javafx.stage.Stage;

import com.jfoenix.controls.JFXButton;
import com.jfoenix.controls.JFXRadioButton;
import com.jfoenix.controls.JFXTextField;

import postidx.view.postidxOverviewController;

public class frmFindController {

    // Идентификаторы для формы поиска
    @FXML private javafx.scene.layout.Pane Pane;

    @FXML private Label lblFind;
    @FXML private JFXTextField txtFind;

    @FXML private ToggleGroup rgFind;
    @FXML private JFXRadioButton rbIdx;
    @FXML private JFXRadioButton rbNaimOPS;
    @FXML private JFXRadioButton rbRegion;
    @FXML private JFXRadioButton rbAutonom;
    @FXML private JFXRadioButton rbArea;
    @FXML private JFXRadioButton rbCity;

    @FXML private JFXButton btnOkFind;
    @FXML private JFXButton btnCancelFind;
    
    
    // Задаем строку поиска
    private String sqlStrFind;
    private String findSqlStr = "select idx, opsname, region, autonom, area, city from postidx where ";
    private String indexSqlStr = "[idx] like ";
    private String opsnameSqlStr = "[opsname] like ";
    private String regionSqlStr = "[region] like ";
    private String autonomSqlStr = "'[autonom] like ";
    private String areaSqlStr =  "[area] like ";
    private String citySqlStr = "[city] like ";
    private int resId;

 // Ссылка на контроллер модели
 	private postidxOverviewController controller;
 	
	public frmFindController() throws Exception {
	}

	@FXML
	private void initialize() {
		
	}
	
	@FXML
	private void rbSelected(ActionEvent ev) {
		switch ( ((RadioButton)ev.getSource()).getText() ) {
        	case "Индексу":
        		resId = 0;
        		break;
        	case "Наименованию ОПС":
        		resId = 1;
        		break;
        	case "Региону (область, край и т.п.)":
        		resId = 2;
        		break;
        	case "Автономной области":
        		resId = 3;
        		break;
        	case "Району, в котором находится ОПС":
        		resId = 4;
        		break;
        	case "Населенному пункту":
        		resId = 5;
        		break;
        	default:
        		break;
		}
		return;
	}
	
	@FXML
	private void btnOkFindClick() {
		if (!rbIdx.isSelected() & !rbNaimOPS.isSelected() & !rbRegion.isSelected() & !rbAutonom.isSelected()
				& !rbArea.isSelected() & !rbCity.isSelected()) {
			JOptionPane.showMessageDialog(null, "Не задана область поиска!");
			}
		if (txtFind.getText().trim().length() == 0) {
			JOptionPane.showMessageDialog(null, "Не задана строка поиска!");
		} else {
			switch ( resId ) {
           	case 0:
           		sqlStrFind = findSqlStr + indexSqlStr + txtFind.getText() + ";";
               	break;
           	case 1:
           		sqlStrFind = findSqlStr + opsnameSqlStr + "'%" + txtFind.getText().toUpperCase() + "%';";
               	break;
           	case 2:
           		sqlStrFind = findSqlStr + regionSqlStr + "'%" + txtFind.getText().toUpperCase() + "%';";
               	break;
           	case 3:
           		sqlStrFind = findSqlStr + autonomSqlStr + "'%" + txtFind.getText().toUpperCase() + "%';";
               	break;
           	case 4:
           		sqlStrFind = findSqlStr + areaSqlStr + "%" + txtFind.getText().toUpperCase() + "%;";
               	break;
           	case 5:
           		sqlStrFind = findSqlStr + citySqlStr + "'%" + txtFind.getText().toUpperCase() + "%';";
               	break;
           	default:
           	break;
			}
		}
		controller = new postidxOverviewController();
		try {
			//controller.closeConnection(controller.con, controller.stmt, controller.rs);
			controller.initTable();
			controller.LoadDatasInTable(sqlStrFind);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			btnCancelFindClick();
		}
	}

	@FXML
	public void btnCancelFindClick() {
        try {
        	Stage stage = (Stage) btnCancelFind.getScene().getWindow();
        	stage.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
	} 
}

Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
	at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)
	at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
	at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
	at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
	at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
	at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
	at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
	at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
	at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
	at javafx.event.Event.fireEvent(Event.java:198)
	at javafx.scene.control.MenuItem.fire(MenuItem.java:462)
	at com.sun.javafx.scene.control.skin.ContextMenuContent$MenuItemContainer.doSelect(ContextMenuContent.java:1405)
	at com.sun.javafx.scene.control.skin.ContextMenuContent$MenuItemContainer.lambda$createChildren$6(ContextMenuContent.java:1358)
	at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
	at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
	at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
	at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
	at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
	at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
	at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
	at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
	at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
	at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
	at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
	at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
	at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
	at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
	at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
	at javafx.event.Event.fireEvent(Event.java:198)
	at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
	at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
	at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
	at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
	at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
	at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:432)
	at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
	at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
	at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
	at com.sun.glass.ui.View.notifyMouse(View.java:937)
	at com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
	at com.sun.glass.ui.gtk.GtkApplication.lambda$null$5(GtkApplication.java:139)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
	at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
	at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)
	... 43 more
Caused by: java.lang.NullPointerException
	at postidx.view.rootLayoutController.mniQuickSearch(rootLayoutController.java:35)
	... 53 more