For example, I have field in database table named date, and I want to insert into table date range from 2021-11-25 to 2021-11-30. I do not know how to write query to insert dates into database each date in new row. Like this:

1. 2021-11-25
2. 2021-11-26
3. 2021-11-27
4. 2021-11-28
5. 2021-11-29
6. 2021-11-30

Thanks for help...

CodePudding user response：

how to write query to insert dates into database each date in new row.

OK, given that the question is tagged with JPA I'm going to give you a JPA answer.

You create an entity like ...

@Entity
class MyEntity {
  @Id
  @GeneratedValue
  Long id;
  DateTime myDate
}

And then you can create a data range list like this ..

 public static List<DateTime> getDateRange(DateTime start, DateTime end) {

        List<DateTime> ret = new ArrayList<DateTime>();
        DateTime tmp = start;
        while(tmp.isBefore(end) || tmp.equals(end)) {
            ret.add(tmp);
            tmp = tmp.plusDays(1);
        }
        return ret;
    }

And then you create your entities from this list and insert them using a repository class.

Does this solve your problem ?

CodePudding user response：

First, you have to generate your data, by defining a simple method:

public List<LocalDate> dateRange(LocalDate from, LocalDate to) {
   return from.datesUntil(to).collect(Collectors.toList());
}

And then, you can store the data in your favorite way.

Using Spring Data JPA for example:

// Your Entity
@Entity
public class DateEntity {

  public DateEntity(LocalDate localDate) {
     this.localDate = localDate;
  }

  @Id
  @GeneratedValue
  private Long id;
  private LocalDate localDate;
}

// Your Repository
@Repository
public interface DateEntityRepository extends JpaRepository<DateEntity, Long> {
}

// And the glue
List<DateEntity> dateEntities = dateRange(from, to).stream()
       .map(localDate -> new DateEntity(localDate))
       .collect(Collectors.toList());

dateEntityRepository.saveAll(dateEntities);

CodePudding user response：

you can use a query like this.

insert into table_name (....  , date_field) values 
select .... , selected_date from 
(select adddate('1970-01-01',t4.i*10000   t3.i*1000   t2.i*100   t1.i*10   t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'