CODEWITHSUNDEEP
javamysql
Radhika Kulkarni
Radhika Kulkarni

Reputation: 498

delete database automatically after a particular date when date column is not available in database

I want to delete database after 31st march of any year. However i don't have date column in my database. Can u please tell the java code which will do this? I have searched that i can probably use events in myql but i am not getting how to use it to suit my application. Also, is there any better option? I have created following database:

CREATE TABLE `amount` (                                                                                                                                                                                                                                                                                                                                                                     

           `userName` VARCHAR(100),
           `DayCareAmount` INT(11) DEFAULT NULL,  
           `HealthCareAmount` INT(11) DEFAULT NULL,  
            `HealthClubAmount` INT(11) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                       

           PRIMARY KEY (`userName`)                                                                                                                                                                                                                                                                                                                                                                         
         ) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Upvotes: 1

Views: 172

Answers (2)

Sudeep Gujar
Sudeep Gujar

Reputation: 327

alter your table to add date or year field

ALTER TABLE table_name
ADD column_name datatype

update your amount fields with respect to the date I got the current financial year as follows 

public int getyear( LocalDate date) {
        System.out.println(date.getYear());
        if(date.getMonthValue() < 4){
            return date.getYear() - 1;
        }
        return date.getYear();

    }

Upvotes: 5

Ketan Chitale
Ketan Chitale

Reputation: 331

You have to add year column to your database table. CREATE TABLE amount ( 

       `userName` VARCHAR(100),
       `DayCareAmount` INT(11) DEFAULT NULL,  
       `HealthCareAmount` INT(11) DEFAULT NULL,  
       `HealthClubAmount` INT(11) DEFAULT NULL, 
        finYr INT(4),                                                                                                                                                                                                                                                                                                                                                      

       PRIMARY KEY (`userName`)                                                                                                                                                                                                                                                                                                                                                                         
     ) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Write a logic in your servlet to check whether the date fetched from your jsp is in the particular financial year and store that year in your database table. While fetching year, you can use where clause on finYr to get data for that particular financial year.

Upvotes: 4

Related Questions