The set of one or more sql statements that are executed as a single unit called as transaction.the example is very useful for learning transaction jdbc.the main purpose of writing transaction if any query fails the entire transaction is rolled back.the good example is used to transfer the money from one bank account to another.if the transaction is competed the money is deducted from the first account and added to the second account.while doing the transaction error occurs both account remain unchanged. if the both query is working correctly only transaction commit other wise rollback.
Establish the Database Connection.
In this example created three different PreparedStatements
PreparedStatement pst;
PreparedStatement pst1;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | public Transaction() { initComponents(); Connect(); txtbillno.setText(String.valueOf(getBillNumber())); SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); Date day = new Date(); txtdate.setText(format.format(day)); txticode.requestFocus(); } Connection con; PreparedStatement pst; PreparedStatement pst1; public void Connect() { try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/vjiproducts","root",""); } catch (ClassNotFoundException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } } |
Bill No generated automatically
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | public int getBillNumber() { int billno = 1; try { Statement smt = con.createStatement(); ResultSet rs = smt.executeQuery("select max(billno) from sales"); rs.next(); billno = rs.getInt(1) + 1; rs.close(); } catch (SQLException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } return billno; } |
Stock Check
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public int getStock(String pcode) { int stock_level = 0; try { Statement smt1 = con.createStatement(); ResultSet rs = smt1.executeQuery("select stock from products where id = '"+ pcode + "'"); rs.next(); stock_level = rs.getInt(1); rs.close(); } catch (SQLException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } return stock_level; } |
Create a Save Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | public void Save() { try { con.setAutoCommit(false); String billno = txtbillno.getText(); String billdate = txtdate.getText(); String pcode = txticode.getText(); String qty = txtqty.getText(); String Customer = txtcustomer.getText(); int i = 0; pst = con.prepareStatement("insert into sales(billno,salesdate,productname,qty,customer)values(?,?,?,?,?)"); pst.setString(1, billno); pst.setString(2, billdate); pst.setString(3, pcode); pst.setString(4, qty); pst.setString(5, Customer); i = pst.executeUpdate(); pst1 = con.prepareStatement("update products set stock=? where id =?"); int curstock = getStock(pcode); curstock = curstock - Integer.parseInt(txtqty.getText()); pst1.setInt(1, curstock); pst1.setString(2, pcode); i = pst1.executeUpdate(); curstock = getStock(pcode); if(curstock<0) { con.rollback(); JOptionPane.showMessageDialog(this, "Sorry Transaction Failed"); } else { con.commit(); JOptionPane.showMessageDialog(this, "Sales Successfully!!!!!!!!!!!"); } } catch (SQLException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } } |
Create a Function Print
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | public void print() { txtprint.setText(txtprint.getText() + "\n"); String billno = txtbillno.getText(); String billdate = txtdate.getText(); String pcode = txticode.getText(); String qty = txtqty.getText(); String Customer = txtcustomer.getText(); txtprint.setText( txtprint.getText() + "Bill No : - " + billno + "\n"); txtprint.setText( txtprint.getText() + "Bill Date : - " + billdate + "\n"); txtprint.setText( txtprint.getText() + "Item Code : - " + pcode + "\n"); txtprint.setText( txtprint.getText() + "Qty : - " + qty + "\n"); txtprint.setText( txtprint.getText() + "Customer : - " + Customer + "\n"); txtprint.setText(txtprint.getText() + "\n"); txtprint.setText(txtprint.getText() + "******************************\n"); txtprint.setText(txtprint.getText() + "Thank you Come Again \n"); } |
Paste the Code Inside the Save Button
1 2 3 4 5 6 | private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) { Save(); print(); } |
Paste the code inside the print button
1 2 3 4 5 6 7 8 | private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) { try { txtprint.print(); } catch (PrinterException ex) { Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex); } } |
I have attached the video tutorial below it will help you to do this step by step.
