How to insert "count" and get "count" to JDBC DB Column?

Question

I want to make a small "event apply page" for training.

(the event is First-come, first-served basis.)

I'm using JSP, JAVA(DAO(Data Access Object)) and oracle JDBC.

When a customer clicks "apply" button,

[1. id], [2. apply date], [3. apply count (+1)] will be saved in db table that I made.

I don't know how to make login session yet. So I made input field that can input id directly when applying.

I can insert and get from db table about id and apply date.

But I'm trouble because "apply" counting.

I will put limit that no more apply when "apply count" is 100. (First-come, first-served basis)

How to insert apply count to DB and how to get count number from DB?

here is my code and situation.

1. DB TABLE

Column that I made are 3 [1. id] / [2. aug_cnt] / [3. applydate] enter image description here

2. Applybean.java

package model;

import java.sql.Timestamp;

public class ApplyBean {
    
    private String id ;
    private int aug_cnt;
    private Timestamp applydate;
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public int getAug_cnt() {
        return aug_cnt;
    }
    public void setAug_cnt(int aug_cnt) {
        this.aug_cnt = aug_cnt;
    }
    public Timestamp getApplydate() {
        return applydate;
    }
    public void setApplydate(Timestamp applydate) {
        this.applydate = applydate;
    }

}

3. ApplyDAO.java

public class ApplyDAO {     

    Connection con; 
    PreparedStatement pstmt; 
    ResultSet rs;   
    
    public void getCon() {          
        
        try {               
            Context initctx = new InitialContext();             
            Context envctx = (Context)initctx.lookup("java:comp/env");
            DataSource ds = (DataSource)envctx.lookup("jdbc/pool");
            con = ds.getConnection();
        }catch(Exception e) {
            e.printStackTrace();
        }    
    }
    
    public void insertApply(ApplyBean abean) {

        try{    
            getCon();                       

            String sql = "insert into eventcount_aug values(?,sysdate)";
            PreparedStatement pstmt = con.prepareStatement(sql);

            pstmt.setString(1, abean.getId());
            pstmt.setTimestamp(2, abean.getApplydate()); 
            pstmt.executeUpdate();    

            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }  
    }
}

I think I need to edit only "Applybean.java" file...

If I realize how to insert and get "apply count",

I can find how to make my event page perfectly.

I would be really grateful for your help.

Thank you.

Answer 1

If I am understanding your question correctly, you need following things:

1. Create sequence object in oracle:

create sequence my_test_id_seq increment by 1;

2. Alter your Oracle table & use this sequence as default value:

  alter table eventcount_aug modify (aug_cnt number default my_test_id_seq.nextval);

By this, you got capability that your aug_cnt column gets automatically incremented whenever there are inserts.

3. You can then use select max(aug_cnt) as count from eventcount_aug; before your insert call in insertApply which will decide if you need to go ahead with insert or not. For this you can create one utility method something like:

private int getCount(){

try{    
         getCon();                       
         int length=0;
         String sql = "select max(?) as count from eventcount_aug";
         PreparedStatement pstmt = con.prepareStatement(sql);

         pstmt.setString(1,"aug_cnt");
         ResultSet rs =  preparedStatement.executeQuery();    
       if (rs.next()) {
         length = rs.getInt(1);
       }
         con.close();
     }catch(Exception e){
    }
  return length;

}

And then use this in your insertApply before try block:

public void insertApply(ApplyBean abean) {
  if(getCount()<100){ // if count is less than 100, then only insert.
   try{
   ..
  } 

---

Update : For oracle <12c versions , you can update your insert in your original insertApply method.:

String sql = "insert into eventcount_aug values(?,sysdate,my_test_id_seq.nextval)";

No need to re-create table or alter table & use sequence.