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.

CodePudding user response：

It seems that you don't care about revocation of the application or concurrency problem. You can use another SQL to get the current count number before you insert a new application.

select max(aug_cnt) as count from eventcount_aug;

And then if count < 100 then set new count as count 1, or else if count >= 100 then refuse the new application.

CodePudding user response：

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.