Sales Transaction Management in Java Mysql

in this tutorial will teach you Sales Transaction Management using java 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;

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

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

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

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

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

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
      
        Save();
        print();
        
    }

Paste the code inside the print button

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        try {
         
            txtprint.print();
        } catch (PrinterException ex) {
            Logger.getLogger(Transaction.class.getName()).log(Level.SEVERE, null, ex);
        }
    }