Count records of row SQL in Java MVC

Question

I have a small question here. I create a JFrame. Everything insert and display ok. But I want to display the total number of teacher with counting rows. It means when I clicked display, the records in database will display in table, and number total of records also appears in TextField next to. For example, my table get 2 records in Teacher Number. So a textfield (Total Number of Teachers must be 2). Someone told me using Select Count(*) from table, but I really could not get the solution.

This is my Controller

package controller;
import model.*;
import view.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;


public class HRCController implements ActionListener, KeyListener{
    HRCView viewHRC = new HRCView();
    HRCDAO modelHRC = new HRCDAO();

    public HRCController(HRCView viewHRC, HRCDAO modelHRC){
        this.modelHRC = modelHRC;
        this.viewHRC = viewHRC;
        this.viewHRC.btnadd.addActionListener(this);
        this.viewHRC.btndisplays.addActionListener(this);
                ////Teacher  
                this.viewHRC.btnadd1.addActionListener(this);
                this.viewHRC.btndisplay1.addActionListener(this);
                this.viewHRC.btnexitS.addActionListener(this);
                this.viewHRC.btnexitS1.addActionListener(this);
                this.viewHRC.txtphone1.addKeyListener(this);
                this.viewHRC.txtexp1.addKeyListener(this);
                this.viewHRC.txtjobexp.addKeyListener(this);
    }
    //public void InitializeSchool(){}  

    public void LLenarTabla(JTable tableD){
        DefaultTableModel modelT = new DefaultTableModel();
        tableD.setModel(modelT);
     modelT.addColumn("School Name");
         modelT.addColumn("School Adddress");
         modelT.addColumn("Job Position");
         modelT.addColumn("Years of Experience");

      Object[] column = new Object[4];      
      int numAdd = modelHRC.listHRC().size();
      for (int i =0; i<numAdd; i++)
    {
          column[0] = modelHRC.listHRC().get(i).getSchoolname();
          column[1] = modelHRC.listHRC().get(i).getSchooladd();
          column[2] = modelHRC.listHRC().get(i).getJobposition();
          column[3] = modelHRC.listHRC().get(i).getJobexp();
          modelT.addRow(column);
}
}
   ///// ADD data for table records
        public void LLenarTablaTeacher(JTable tableT){ //Sua LLenarTabla = LLenarTablaTeacher, tableD = tableT
        DefaultTableModel modelTc = new DefaultTableModel(); // sua modelT = modelTc
        tableT.setModel(modelTc);
     modelTc.addColumn("Teacher Number");
         modelTc.addColumn("Teacher Name");
         modelTc.addColumn("Telephone");
         modelTc.addColumn("Qualification");
         modelTc.addColumn("Years of Experience");  

      Object[] column = new Object[5];      
      ArrayList<HRC> arrLitHRC = modelHRC.listTeacher();
      int numTeacherAdd = arrLitHRC.size(); 
      for (int i =0; i<numTeacherAdd; i++)
    {
          column[0] = arrLitHRC.get(i).getTno();
          column[1] = arrLitHRC.get(i).getTname();
          column[2] = arrLitHRC.get(i).getTphone();
          column[3] = arrLitHRC.get(i).getTqualification();
          column[4] = arrLitHRC.get(i).getTexp();
          modelTc.addRow(column);
}         
}



   @Override     
public void actionPerformed(ActionEvent e){
      if(e.getSource() ==viewHRC.btnadd){
          String scname = viewHRC.txtname.getText();
          String scadd = viewHRC.txtadd.getText();
          String scjobpos = viewHRC.txtjobpos.getText();
          String scjobexp = viewHRC.txtjobexp.getText();       
          String rptaRegister = modelHRC.insertSchool(scname, scadd, scjobpos, scjobexp);

    if(rptaRegister!=null){
        JOptionPane.showMessageDialog(null, rptaRegister);
    }else{
        JOptionPane.showMessageDialog(null, "Add Error");
             }                                            }

    if (e.getSource()==viewHRC.btndisplays){                
        LLenarTabla(viewHRC.tbdatas);
                JOptionPane.showMessageDialog(null, "Display records");
                                                }
///////////////////////////Add Teacher
if(e.getSource() ==viewHRC.btnadd1){ 
          //String tbno = viewHRC.txtno1.getText();
          String tbname = viewHRC.txtname1.getText();
          String tbphone = viewHRC.txtphone1.getText();
          String tbqualification = viewHRC.txtqua1.getText();
          String tbexp = viewHRC.txtexp1.getText();          

    String rptaAdd = modelHRC.insertTeacher(tbname,tbphone,tbqualification,tbexp);
    //Dau?
    if(rptaAdd!=null){
        JOptionPane.showMessageDialog(null, rptaAdd);
    }else{
        JOptionPane.showMessageDialog(null, "Add Error");
             }
            }

    if (e.getSource()==viewHRC.btndisplay1){
        LLenarTablaTeacher(viewHRC.tbdataTeacher);                
                JOptionPane.showMessageDialog(null, "Display records");
            }   

        if(e.getSource() == viewHRC.btnexitS) {       
          System.exit(0);
       }else if(e.getSource()==viewHRC.btnexitS1){
         System.exit(0);
     }
}


 @Override
    public void keyPressed(KeyEvent e){

    }
    @Override
    public void keyTyped(KeyEvent e) {
        if(e.getSource() == viewHRC.txtphone1){
            char c = e.getKeyChar();
            if(c<'0' || c>'9'){
                e.consume();
            }
        }else if(e.getSource() == viewHRC.txtexp1){
            char c = e.getKeyChar();
            if(c<'0' || c>'9'){
                e.consume();
            }

        }else if(e.getSource() == viewHRC.txtjobexp){
            char c = e.getKeyChar();
            if(c<'0' || c>'9'){
                e.consume();
            }
        }
    }

    @Override
    public void keyReleased(KeyEvent e) {
    }
 }

My HRCDAO

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;


public class HRCDAO{
    HRCConnection hrcconnection;
    public HRCDAO(){
    hrcconnection = new HRCConnection();
}

public String insertSchool(String scname, String scadd, String scjobpos, String scjobexp){
    String rptaAdd = null;
try {
    Connection accessDB = hrcconnection.getHRCConnection(); 
    CallableStatement cs = accessDB.prepareCall ("{call school_insert(?,?,?,?)}");
                  cs.setString(1, scname);
                  cs.setString(2, scadd);
                  cs.setString(3, scjobpos);
                  cs.setString(4, scjobexp);

    int numFAeffect = cs.executeUpdate();

            if(numFAeffect>0){
                rptaAdd ="Add successful.";
            }
    } catch (Exception e){
}
    return rptaAdd;
}

        public ArrayList<HRC> listHRC(){
    ArrayList listaSchool = new ArrayList();
    HRC hrc;
    try{
    Connection accessDB = hrcconnection.getHRCConnection();
    PreparedStatement ps = accessDB.prepareStatement("select * from school");
    ResultSet rs = ps.executeQuery();
    while(rs.next()){
         hrc = new HRC();
             hrc.setSchoolname(rs.getString(1));
             hrc.setSchooladd(rs.getString(2));
             hrc.setJobposition(rs.getString(3));
             hrc.setJobexp(rs.getString(4));
             listaSchool.add(hrc);                
            }
        }catch (Exception e){

        }
        return listaSchool;        
    }
        //Get tblNo max of teacher
        public String getMaxTblNo(){
            String result = "";
            Connection accessDB = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try{
                accessDB  = hrcconnection.getHRCConnection();
                ps = accessDB.prepareStatement("SELECT tbno FROM db_hrc.teacher ORDER BY tbno DESC limit 0,1");
                rs = ps.executeQuery();
                while(rs.next()){
                     result = rs.getString(1);
                } 
            } catch (Exception e){
                JOptionPane.showMessageDialog(null, "Error getMaxTblNo method:\n" + e.toString());
            } finally {
                try {
                    if(accessDB != null){
                        accessDB.close();
                    }
                    if(ps != null){
                        ps.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(HRCDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            return result;
        }

        //Auto gen tblNo (Add 1)
        public String autoGenTblno(){
            String[] tblNoSplit = getMaxTblNo().split("T");
            if(tblNoSplit.length < 2)
                return "T00001";
            //OK
            tblNoSplit[1] = (Integer.parseInt(tblNoSplit[1]) + 1) + "";

            while(tblNoSplit[1].length() < 5)
                tblNoSplit[1] = "0" + tblNoSplit[1];

            return "T" + tblNoSplit[1];
        }
        /////INSERT TEACHER
        public String insertTeacher(String tbname, String tbphone, String tbqualification, String tbexp){
    String rptaAdd = null;
        String tbno = autoGenTblno();
try {   
    Connection accessDB  = hrcconnection.getHRCConnection(); 
    CallableStatement cs  = accessDB.prepareCall ("{call teacher_insert(?,?,?,?,?)}");
                  cs.setString(1, tbno);
                  cs.setString(2, tbname);
                  cs.setString(3, tbphone);
                  cs.setString(4, tbqualification);
                  cs.setString(5, tbexp);

    int numFAeffect = cs.executeUpdate();

            if(numFAeffect>0){
                rptaAdd ="Add successful.";
            }
    } catch (Exception e){
            JOptionPane.showMessageDialog(null, e.toString());
}
    return rptaAdd;
}

        public ArrayList<HRC> listTeacher(){
    ArrayList listaTeacher = new ArrayList();
    HRC hrc;
    try{
    Connection accessDB = hrcconnection.getHRCConnection();
    PreparedStatement ps = accessDB.prepareStatement("select * from teacher");
    ResultSet rs = ps.executeQuery();
    while(rs.next()){
         hrc = new HRC();
             hrc.setTno(rs.getString(1));
             hrc.setTname(rs.getString(2));
             hrc.setTphone(rs.getString(3));
             hrc.setTqualification(rs.getString(4));
             hrc.setTexp(rs.getString(5));
             listaTeacher.add(hrc);                
            }
        }catch (Exception e){
        }
        return listaTeacher;        
    }       
    }

[Take a look](http://stackoverflow.com/questions/7886462/how-to-get-row-count-using-resultset-in-java) — JBaba, Aug 06 '15 at 20:00

score 1 · Accepted Answer · answered Aug 07 '15 at 14:43

1

It seems to me you already have the number at int numAdd = modelHRC.listHRC().size(); and/or at int numTeacherAdd = arrLitHRC.size();. So all you need to do is set it to your text field with txtTotalNumberOfTeachers.setText(numAdd) or whatever id it has.

answered Aug 07 '15 at 14:43

Tolio

1,023
13
30

Thank you so much @Túlio F. I did it. – nguyen tran hoang nguyen Aug 07 '15 at 17:12

Count records of row SQL in Java MVC

1 Answers1