Firebird UI for Java

Introduction

A year ago, it took to write a database in the course work. This did not cause much difficulty. I chose a topic, drew an ER-diagram, decided on the fields of the tables and started writing. I did not choose a language for a long time, at that time I started working on Java in Eclipse. Chose a DBMS, my choice fell on Firebird. I added tables via IBExpert and was happy with everything as soon as I wrote a UI for a pair of tables, I realized that you can create the rest with copy-paste. The code turned out terrible (OOP? I did not hear it, so it was possible to characterize it), but at that time everything pleased me. A year has passed and by chance I had to revise my code. It was a terrible thing with an incomprehensible structure.

I decided to set several goals for myself:
- simple addition of tables
- finally apply OOP
- apply design patterns (for training)

Also now it is not clear why it is difficult for people at the institute to write simple databases (or laziness), in any case, I want to show the simplicity of writing the database and introduce the application to my vision (in my opinion very simple).

Beginning of work

For writing the database we need
- Eclipse IDE for java developers
- Firebird
- Jaybird (JDBC driver, essentially a jar library)
- IBExpert (for adding tables)
')
All this can be simply downloaded, so I’ll skip the issue of setting up the environment. In this article I implement the interface for one table, since other tables can be easily added.

Code writing

We will have only one Ranks table with ID and RANK columns.


For writing the interface chose Swing.
Interface duties will be such
- Table selection
- Output \ Update table
- Add \ Delete \ Insert record
As a result, we will have this interface

The architecture of the application is as follows
- main class with main (..) (Application)
- connection to our database (DBHelper)
- general model for any table (AbsTable, Tables)
- base class for all tables (BaseFrame) and classes of tables of successors (RankFrame)
- class creating components (Components)
- helper class for strings, inspired by androids (Strings)

Writing model table

The type of our data is obviously ID - Integer and Rank - String. The names of the columns are obvious.
We enter this data into our class Tables. All newly created tables also need to be described here, according to a given pattern.

public class Tables { public static final Class<?>[] RANKS_TYPE = { Integer.class, String.class }; public static final String[] RANKS_TABLE = { "ID", "Rank" }; } 

We also create the class AbsTable (inherited from AbstractTableModel), which implements the model for the data in the table, we need to redefine several methods of the base class. The implementation is simple; it takes data from the Tables class to create a data matrix. So you can model for tables in general form and avoid the useless copying of code for each table.

 public class AbsTable extends AbstractTableModel { private List<String> mColumnNames; private List<ArrayList<Object>> mTableData; private List<Object> mColumnTypes; public AbsTable(Class<?>[] types, String[] columns) { mColumnTypes = new ArrayList<Object>(types.length); mColumnNames = new ArrayList<String>(columns.length); for (int i = 0; i < columns.length; ++i) { mColumnTypes.add(i, types[i]); mColumnNames.add(columns[i]); } } @Override public int getColumnCount() { return mColumnNames.size(); } @Override public int getRowCount() { return mTableData.size(); } @Override public Object getValueAt(int row, int column) { return mTableData.get(row).get(column); } public String getColumnName(int column) { return mColumnNames.get(column); } @Override public boolean isCellEditable(int row, int column) { return false; } @Override public void setValueAt(Object obj, int row, int column) { } @Override public Class<?> getColumnClass(int col) { return (Class<?>) mColumnTypes.get(col); } public void setTableData(ArrayList<ArrayList<Object>> tableData) { mTableData = tableData; } } 

DB connection

We have one connection, so the class is implemented using Singleton. You can create a connection using the getInstance () method, which connects to the database with the specified login / password / path to the FDB file using the connect () method. For our model, we will take the data using the getData (String sql) method. Also, when we no longer need to close the connection, we need to close it, for this we use the release () method.

 public class DBHelper { private Connection dbConnection; private static DBHelper sDBHelper ; private static final String DRIVER = "org.firebirdsql.jdbc.FBDriver"; private static final String URL = "jdbc:firebirdsql:localhost/3050:C:\\DB\\DB.FDB"; private static final String LOGIN = "SYSDBA"; private static final String PASSWORD = "masterkey"; public static synchronized DBHelper getInstance() { if (sDBHelper == null) { sDBHelper = new DBHelper (); } return sDBHelper ; } private DBHelper () { } public void connect() { try { Class.forName(DRIVER); dbConnection = DriverManager.getConnection(URL, LOGIN, PASSWORD); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public PreparedStatement getPrepareStatement(String sql) throws SQLException { return dbConnection.prepareStatement(sql); } public synchronized ArrayList<ArrayList<Object>> getData(String query) { ArrayList<ArrayList<Object>> dataVector = new ArrayList<ArrayList<Object>>(); Statement st = null; ResultSet rs = null; try { st = dbConnection.createStatement(); rs = st.executeQuery(query); int columns = rs.getMetaData().getColumnCount(); while (rs.next()) { ArrayList<Object> nextRow = new ArrayList<Object>(columns); for (int i = 1; i <= columns; i++) { nextRow.add(rs.getObject(i)); } dataVector.add(nextRow); } } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } } return dataVector; } public void release() { if (sDBHelper != null) { sDBHelper = null; } if (dbConnection != null) { try { dbConnection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } 

Component creation

We will need components such as JTable, JScrollPane, JComboBox, JLabel, JTextField, JButton. The class is made in order to avoid the useless copying of the code for creating components when creating tables.

 public class Components { public static AbsTable createTableModel(Class<?>[] types, String[] col, String sql) { AbsTable table = new AbsTable(types, col); table.setTableData(DBHelper .getInstance().getData(sql)); return table; } public static JTable createTable(AbsTable model) { JTable table = new JTable(model); table.getColumnModel().getColumn(0).setMaxWidth(50); return table; } public static JScrollPane createScroll(JTable table) { return new JScrollPane(table); } public static JComboBox<String> createCombo(String[] items, ItemListener listener) { JComboBox<String> combo = new JComboBox<String>(items); combo.setEditable(false); combo.setSelectedIndex(-1); combo.addItemListener(listener); return combo; } public static JLabel createLabel(String name) { JLabel label = new JLabel(name); label.setHorizontalTextPosition(JLabel.LEFT); label.setIconTextGap(5); label.setForeground(Color.black); return label; } public static JTextField createEdit(String text) { JTextField tf= new JTextField(text); tf.setEditable(true); tf.setForeground(Color.black); return tf; } public static JButton createButton(String name, ActionListener listener) { JButton button = new JButton(name); button.addActionListener(listener); return button; } } 

Displaying tables on a frame and a table interface

We have already determined that the duties of the interface are the output of a table, adding / updating / deleting / changing a table. Therefore, let's create a base abstract class BaseFrame, inherited from JFrame.
Proceeding from the responsibilities, all tables should be updated, ensure the addition / deletion / change of records, therefore the add (), delete (), save (), updateTable methods are made abstract for all tables. Also in the base class there should be a link to the connection (this is our DBHelper).
On the frame we will have
- JTable table
- JButton buttons add \ save \ delete record
- JScrollPane for a large number of records
All this is the same for all created tables, therefore it is located in the base class. From the Components class we create buttons and assign them listeners (listeners) to actions. We also extend the base class using the ListSelectionListener interface to catch click events on our table cells.

 abstract class BaseFrame extends JFrame implements ListSelectionListener { protected JButton mDeleteBtn; protected JButton mAddBtn; protected JButton mSaveBtn; protected JPanel mControlArea; protected JPanel mEditArea; protected JScrollPane mScroll; protected JTable mTable; protected Container mContainer; protected AbsTable mTableModel; protected DBHelper sDBHelper ; private static final int SIZE_X = 300; private static final int SIZE_Y = 450; public BaseFrame(String name) { super(name); sDBHelper = DBHelper .getInstance(); sBDHelper.connect(); mAddBtn = Components.createButton(Strings.ADD, new ActionListener() { @Override public void actionPerformed(ActionEvent e) { add(); } }); mDeleteBtn = Components.createButton(Strings.DELETE, new ActionListener() { @Override public void actionPerformed(ActionEvent e) { delete(); } }); mSaveBtn = Components.createButton(Strings.SAVE, new ActionListener() { @Override public void actionPerformed(ActionEvent e) { save(); } }); setSize(new Dimension(SIZE_X, SIZE_Y)); setVisible(true); setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); } abstract void updateTable(); abstract void add(); abstract void delete(); abstract void save(); } 

Finally, create our table. To edit it, you need 2 JTextField and 2 JLabel. We create our components using the Component class. Add components to the frame. Next, you need to override the abstract methods of the base class to work with the database (adding / changing / deleting records). Writing these methods will require a little knowledge of SQL. Do not forget to update the table interface using the overridden updateTable method. We also override the valueChanged (..) method to handle clicking on a cell.

 public class RanksFrame extends BaseFrame { private JLabel mIdLabel; private JLabel mRankLabel; private JTextField mIdEdit; private JTextField mRankEdit; public RanksFrame() { super(Strings.RANK); mContainer = getContentPane(); mTableModel = Components.createTableModel(Tables.RANKS_TYPE, Tables.RANKS_TABLE, "SELECT * FROM RANKS ORDER BY ID"); mTable = Components.createTable(mTableModel); mScroll = Components.createScroll(mTable); mIdLabel = Components.createLabel(Strings.ID); mIdEdit = Components.createEdit(""); mRankLabel = Components.createLabel(Strings.RANK); mRankEdit = Components.createEdit(""); mTable.getSelectionModel().addListSelectionListener(this); mControlArea = new JPanel(new GridLayout(1, 3)); mEditArea = new JPanel(new GridLayout(2, 2)); mEditArea.add(mIdLabel); mEditArea.add(mIdEdit); mEditArea.add(mRankLabel); mEditArea.add(mRankEdit); mControlArea.add(mSaveBtn); mControlArea.add(mDeleteBtn); mControlArea.add(mAddBtn); mContainer.add(mScroll); mContainer.add(mEditArea); mContainer.add(mControlArea); mContainer.setLayout(new BoxLayout(mContainer, BoxLayout.Y_AXIS)); } @Override public void updateTable() { SwingUtilities.invokeLater(new Runnable() { public void run() { mTableModel.setTableData(sBDHelper .getData("SELECT * FROM RANKS ORDER BY ID")); mTable.updateUI(); mRankEdit.setText(null); mIdEdit.setText(null); } }); } @Override public void add() { PreparedStatement ps = null; try { ps = sBDHelper .getPrepareStatement("INSERT INTO RANKS (ID,RANK) VALUES(?,?)"); ps.setString(1, mIdEdit.getText()); ps.setString(2, mRankEdit.getText()); ps.executeUpdate(); } catch (SQLException r) { r.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } updateTable(); } } @Override public void delete() { PreparedStatement ps = null; try { ps = sBDHelper.getPrepareStatement("DELETE FROM RANKS WHERE ID=?"); ps.setString(1, mIdEdit.getText()); ps.executeUpdate(); } catch (SQLException r) { r.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } updateTable(); } } @Override public void save() { PreparedStatement ps = null; try { ps = sBDHelper .getPrepareStatement("UPDATE RANKS SET RANK=? WHERE ID=?"); ps.setString(1, mRankEdit.getText()); ps.setString(2, mIdEdit.getText()); ps.executeUpdate(); } catch (SQLException r) { r.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } updateTable(); } } @Override public void valueChanged(ListSelectionEvent e) { mIdEdit.setText(mTable.getModel() .getValueAt(mTable.getSelectedRow(), 0).toString()); mRankEdit.setText(mTable.getModel() .getValueAt(mTable.getSelectedRow(), 1).toString()); } } 

The end is close, the table selection interface

We write the code of the main database window. The selection interface looks like a JComboBox with the table names mTables. By pressing the switch to the selected table, here you will need to add all our tables to create them. Oh, finally, to handle the closure of our application, we use the WindowListener interface (wrote only the method that I use, threw the rest for so much code), close the connection when closing.

 public class Application extends JFrame implements WindowListener { private String[] mTables = { "Ranks" }; private static final int RANKS = 0; private JComboBox<String> mComboMenu; public Application() throws SQLException { super(Strings.DB_NAME); mComboMenu = Components.createCombo(mTables, new ItemListener() { @Override public void itemStateChanged(ItemEvent evt) { switch (mComboMenu.getSelectedIndex()) { case RANKS: new RanksFrame(); break; } SwingUtilities.invokeLater(new Runnable() { public void run() { mComboMenu.setSelectedIndex(-1); } }); } }); Container container = getContentPane(); container.add(mComboMenu); container.setLayout(new BoxLayout(container, BoxLayout.Y_AXIS)); setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setSize(400, 80); setResizable(false); setVisible(true); addWindowListener(this); } public static void main(String[] args) throws SQLException { new Application(); } @Override public void windowClosing(WindowEvent arg0) { DBHelper .getInstance().release(); } } 

Conclusion

I hope not tired, the article is educational in nature, so I hope to write / create database tables after reading this article will be easier. I also pursue a ghostly hope that the students will finally sit down and write the database themselves.

PS I hope my refactoring was a success and everything looks simple and clear. Criticism is welcome, especially for design patterns.
upd:
Vector -> ArrayList thanks javax
For many shortcomings thanks gvsmirnov and aleksandy