1019 Part 2 of Java 8 Code for loading h2 database, and editing rows record by record

This is in continuation of previous post NO. 1018 Click to go to 1018 at this blog. As promised, I copy and paste below, the Java 8 code for editing h2 embedded database records, saved by me as file yh2db.java. After saving, this file has been compiled as a Java Class, by using javac command. For those who use installed java 8, the javac compilation command can be invoked from command line reading something like:

javac yh2db.java
(or any other file name, of Readers' Preference, after changing the class name in the file). This javac command is to be invoked from the folder in which the ybh2db.java (or any other name) file is located.
I use linux portable java 8. Hence, I have used a slightly different path for the command line.
....path/javac yh2db.java.

After compilation without errors, the Class can be executed with java command.
java ybh2db (or any other file name of Readers' choice).

Notes about column names used in the tables, and sql statements

For convenience, I have created h2 database tables with simple column names such as c1, c2, c3, c4, c5, c6, c7, c8, c9, c10. The same simple column names c1, c2, c3, c4, c5 have been used in sql statements. Readers can freely change these columns to any other column names of their choice, in the tables they create. This Java 8 code is not useful for creating tables, at this preliminary stage.

Tables for embedded h2 database can be created by using h2 console, discussed in the previous blog post referred to above.

Now, the java 8 code for the Editor

//Editing embedded h2 database tables, with Java 8 code. import java.sql.*; import java.awt.event.*; import java.awt.*; import java.awt.event.*; import java.io.*; import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.StandardOpenOption; import java.nio.file.Paths; import java.nio.charset.StandardCharsets; import java.nio.charset.Charset; import javax.swing.text.*; import java.util.regex.Pattern; import static java.lang.System.*; import javax.swing.*; //to use JButton, JFrame, JPanel, JScrollPane, JTextArea, JOptionPane import java.util.*; //for using h2db import java.io.InputStream; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; //Java naming conventions have not been followed. Sorry. //For convenience, Column names used in the tables are c1, c2, c3, c4, c5. Different column names can be used. But, then code will have to be changed mutatis mutandis. //This file is to be saved as yh2db.java. If you wish to change the class name yh2db to something else, you can do that. But, pl. ensure that your .java file name also bears the same name. public class yh2db extends JFrame { public int uirecno; public static void yh2db(){ JFrame ufra = new JFrame("h2 embedded db loading and editing."); JPanel uupanel = new JPanel(); //upper panel definition. JPanel ulpanel = new JPanel(); //lower panel definition. ufra.getContentPane().add(uupanel, "North"); ufra.getContentPane().add(ulpanel,"South"); ufra.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); JTextArea utarea= new JTextArea("1",1,4); //This text area is for the first primary key column 'pk' in the tables. This number is given by the Database as auto_increment. uupanel.add(utarea ); //adding the text area to the upper panel. utarea.setEditable(true); utarea.setLineWrap(true); utarea.setOpaque(true); utarea.setFont(new Font("Serif", Font.BOLD, 16) ); uupanel.setOpaque(true); /////////second textarea JTextArea utarea2= new JTextArea("column c1. ",5,14); uupanel.add(utarea2 ); utarea2.setEditable(true); utarea2.setLineWrap(true); utarea2.setOpaque(true); utarea2.setFont(new Font("Serif", Font.BOLD, 14) ); utarea2.setForeground(Color.red); /////////third textarea JTextArea utarea3= new JTextArea("column c2. ",5,14); uupanel.add(utarea3 ); utarea3.setEditable(true); utarea3.setLineWrap(true); utarea3.setOpaque(true); utarea3.setFont(new Font("Serif", Font.BOLD, 14) ); utarea3.setForeground(Color.green); /////////fourth textarea JTextArea utarea4= new JTextArea("column c3. ",5,14); uupanel.add(utarea4 ); utarea4.setEditable(true); utarea4.setLineWrap(true); utarea4.setOpaque(true); utarea4.setFont(new Font("Serif", Font.BOLD, 14) ); utarea4.setForeground(Color.magenta); /////////fifth textarea JTextArea utarea5= new JTextArea("column c4. ",5,12); uupanel.add(utarea5 ); utarea5.setEditable(true); utarea5.setLineWrap(true); utarea5.setOpaque(true); utarea5.setFont(new Font("Serif", Font.BOLD, 14) ); utarea5.setForeground(Color.blue); /////////6th textarea JTextArea utarea6= new JTextArea("column c5. ",5,12); uupanel.add(utarea6 ); utarea6.setEditable(true); utarea6.setLineWrap(true); utarea6.setOpaque(true); utarea6.setFont(new Font("Serif", Font.BOLD, 14) ); utarea6.setForeground(Color.blue); //creating and populating lower panel JLabel ulpltable = new JLabel("table name:"); ulpanel.add(ulpltable); JTextArea ulptatable= new JTextArea("tyb3",1,10); ulpanel.add(ulptatable); ulptatable.setEditable(true); ulptatable.setFont(new Font("Serif", Font.BOLD, 12) ); JTextArea ulptasearterm= new JTextArea("fevers",1,10); ulpanel.add(ulptasearterm); ulptasearterm.setEditable(true); ulptasearterm.setFont(new Font("Serif", Font.BOLD, 12) ); JLabel ulcol = new JLabel("Column to search"); ulpanel.add(ulcol); JTextArea ulptacol= new JTextArea("c1",1,5); ulpanel.add(ulptacol); ulptacol.setEditable(true); //button creation JButton ub1 = new JButton("Get "); ulpanel.add(ub1); JButton ub2 = new JButton("Update"); ulpanel.add(ub2); JButton ub3 = new JButton("Next"); ulpanel.add(ub3); JButton ub4 = new JButton("Prev"); ulpanel.add(ub4); JButton ub5 = new JButton("Search"); ulpanel.add(ub5); JButton ub6 = new JButton ("Add"); ulpanel.add(ub6); JButton ub7 = new JButton ("Delete"); ulpanel.add(ub7); ub7.setForeground(Color.red); //Display the window. ufra.pack(); ufra.setVisible(true); ///////////////////buttons and listeners/////////////////////////// //this actionlistener is for getrecord button ub1 ub1.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try{ String usrec = utarea.getText(); int uirecno; uirecno = Integer.parseInt(usrec); int uinext = uirecno + 1; //Class.forName("org.h2.Driver").newInstance(); Class.forName("org.h2.Driver"); Connection conn = DriverManager.getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "select * from tyb3 where pk=' " + uirecno + " ' "; Statement stat = conn.createStatement(); // ResultSet rs = stat.executeQuery("SELECT * FROM tyb3 next 1"); ResultSet rs = stat.executeQuery(sql); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(6) ); } //endwhile rs.close(); // stat.close(); // conn.close(); } //end try catch(Exception e1){ JOptionPane.showMessageDialog(null, "Sorry, not found or some error took place"); } //end catch } //endaction performed }) ; ///////////////ub3///////////next///////////////////////////// //this actionlistener is for getrecord button ub3 ub3.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try{ String usfilen = ulptatable.getText(); String usrec = utarea.getText(); int uirecno; uirecno = Integer.parseInt(usrec); int uinext= uirecno + 1; String usnext = Integer.toString(uinext); Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager. getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "select * from tyb3 where pk=' " + uinext + " ' "; Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery(sql); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(6) ); } //endwhile } //end try catch(Exception e1){ JOptionPane.showMessageDialog(null, "Sorry, not found or some error took place"); } //end catch } //endaction performed }) ; //end addactionListenernewactionListener() /////////////////ub4////////////prev////////////////////////// //this actionlistener is for record prev button ub4 ub4.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try{ String usrec = utarea.getText(); int uirecno; uirecno = Integer.parseInt(usrec); int uiprev= uirecno - 1; String usprev = Integer.toString(uiprev); Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager. getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "select * from tyb3 where pk=' " + uiprev + " ' "; Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery(sql); while (rs.next()) { utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); } //endwhile } //end try catch(Exception e1){ JOptionPane.showMessageDialog(null, "Sorry, not found or some error took place"); } //end catch } //endaction performed }) ; //endof prev button ib4 addActionListener( new action ActionListener(){ ///////////ub5////////////////search//////////////////////////// //this actionlistener is for getrecord search button ub5 ub5.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try{ String ustable = ulptatable.getText(); String usear = ulptasearterm.getText(); System.out.print(ustable); System.out.print(usear); Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here Statement stat = conn.createStatement(); String sql2 = "select * from " + ustable + " where lower(c1) like" + "'%" + usear + "%'" ; ResultSet rs = stat.executeQuery(sql2); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(5) ); } //endwhile } //end try catch(Exception e1){ System.err.println("Database exception: " + e1); JOptionPane.showMessageDialog(null, "Sorry, not found or some error took place"); } //end catch } //endaction performed }) ; //endof prev button ib4 addActionListener( new action ActionListener() //////////////ub2/////////////////update///////////////////////// ub2.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { //int uirecno = Integer.parseInt(utarea.getText() ); String usrec = utarea.getText(); int uirecno; uirecno = Integer.parseInt(usrec); System.out.print(uirecno); try{ String scolc1; String scolc2; String scolc3; String scolc4; String scolc5; scolc1 =utarea2.getText(); scolc2= utarea3.getText(); scolc3=utarea4.getText(); scolc4=utarea5.getText(); scolc5=utarea6.getText(); Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "update tyb3 set " + "c1 =" + "'" + scolc1 + "' ," + "c2=" + "'" + scolc2+ "', " + "c3 =" + "'" + scolc3 + "', " + "c4 =" + "'" + scolc4 + "', " + "c5 =" + "'" + scolc5 + "' " + "where pk=" + uirecno ; Statement stat = conn.createStatement(); stat.executeUpdate(sql); String sql2="select * from tyb3"; ResultSet rs = stat.executeQuery(sql2); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(6) ); } //endwhile JOptionPane.showMessageDialog(null, "Updated. Press next or, get or something else"); }//endtry catch (Exception e2){ System.err.println("Database exception: " + e2); } //endcatch } //actionevent e }) ; //ub2 actionlistener //////////////////////ub6/////////////add///////////////////////////////// //this actionlistener is for button ub6 add ub6.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try{ String scolc1; String scolc2; String scolc3; String scolc4; String scolc5; scolc1 =utarea2.getText(); scolc2= utarea3.getText(); scolc3=utarea4.getText(); scolc4=utarea5.getText(); scolc5=utarea6.getText(); Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "insert into tyb3 (c1, c2, c3, c4, c5) values (" + "'"+ scolc1 + "'" + ",'"+ scolc2+ "'," + "'"+ scolc3 + "'," + "'"+ scolc4 + "'," + "'"+ scolc5 + "'" + ")" ; Statement stat = conn.createStatement(); stat.executeUpdate(sql); String sql2="select * from tyb3"; ResultSet rs = stat.executeQuery(sql2); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(6) ); } //endwhile JOptionPane.showMessageDialog(null, "Added one row"); }//endtry catch (Exception e2){ JOptionPane.showMessageDialog(null, "Sorry something went wrong."); } //endcatch } //actionevent e }) ; //ub6 actionlistener ///////////////////ub7////////delete/////////////////////////// //this actionlistener is for button ub7 delete ub7.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { String usrec = utarea.getText(); int uirecno; uirecno = Integer.parseInt(usrec); System.out.print(uirecno); try{ Class.forName("org.h2.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:h2:~/mytest", "sa", ""); // add application code here String sql = "delete from tyb3 where pk=" + uirecno ; Statement stat = conn.createStatement(); stat.executeUpdate(sql); String sql2="select * from tyb3"; ResultSet rs = stat.executeQuery(sql2); while (rs.next()) { //System.out.println(rs.getString(1)); utarea.setText( rs.getString(1) ); utarea2.setText( rs.getString(2) ); utarea3.setText( rs.getString(3) ); utarea4.setText( rs.getString(4) ); utarea5.setText( rs.getString(5) ); utarea6.setText( rs.getString(6) ); } //endwhile String usrecno = Integer.toString(uirecno+1); ulptacol.setText("0"); JOptionPane.showMessageDialog(null, "Deleted. You can enter fresh recordno. in recordno. box and press get. Alternatively you can use search button. " ); }//endtry catch (Exception e2){ JOptionPane.showMessageDialog(null, "Sorry something went wrong."); } //endcatch } //actionevent e }) ; //ub7 actionlistener for delete }//end meth ydictirecno2 ///////////////////////////main psvm ////////////////////// public static void main(String[] args) throws Exception { yh2db(); } //end psvm } //end public class