title: Spring boot task to update foreign key in DB
body: <p>I am working on  REST API using Spring boot which is going to manage the auction of items.</p>
  
  <p>Users participating in auction will be in a separate <strong>Users table</strong></p>
  
  <p>Items to be Auctioned will be in <strong>Auction_items table</strong>, along with auction start and end time, winner null if auction is still in progress.</p>
  
  <p>All the bids done by users for respective items are there in <strong>Bids table</strong></p>
  
  <p>I have already tried different ways(cron, fixedRate, fixedDelay) for scheduling the task, got a similar error for all of them, <strong>I am suspecting there might be a DB issue due to which it is happening.</strong></p>
  
  <p>Below are the tables and corresponding model class along  with service and repository interface.</p>
  
  <p><strong>USERS TABLE:</strong></p>
  
  <pre><code>CREATE TABLE USERS
  (
  USER_ID SERIAL,
  NAME VARCHAR(200),
  EMAIL VARCHAR(200),
  PASSWORD VARCHAR(200),
  PRIMARY KEY(USER_ID)
  );
  </code></pre>
  
  <p><strong>AUCTION_ITEMS TABLE:</strong></p>
  
  <pre><code>CREATE TABLE AUCTION_ITEMS
  (
  ITEM_ID SERIAL,
  ITEM_NAME VARCHAR(200),
  ITEM_DESCRIPTION TEXT,
  START_TIME TIMESTAMP,
  END_TIME TIMESTAMP,
  STARTING_AMOUNT INT,
  WINNER INT,
  PRIMARY KEY(ITEM_ID),
  FOREIGN KEY(WINNER) REFERENCES USERS(USER_ID) ON DELETE CASCADE
  );
  </code></pre>
  
  <p><strong>BIDS TABLE:</strong></p>
  
  <pre><code>CREATE TABLE BIDS
  (
  BID_ID SERIAL,
  ITEM_ID INT,
  USER_ID INT,
  AMOUNT INT,
  PRIMARY KEY(BID_ID),
  FOREIGN KEY(ITEM_ID) REFERENCES AUCTION_ITEMS(ITEM_ID) ON DELETE 
  CASCADE,
  FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE
  );
  </code></pre>
  
  <p>Below are the model classes for above tables:</p>
  
  <p><strong>User.java:</strong></p>
  
  <pre><code>@Entity
  @Table(name="USERS")
  public class User {
  
  @Id
  private Integer user_id;
  private String name;
  private String email;
  private String password;
  
  //getter setter methods
  }
  </code></pre>
  
  <p><strong>Item.java:</strong></p>
  
  <pre><code>@Entity
  @Table(name="AUCTION_ITEMS")
  public class Item {
  
  @Id
  private Integer item_id;
  private String item_name;
  private String item_description;
  private Timestamp start_time;
  private Timestamp end_time;
  private int starting_amount;
  @ManyToOne
  @JoinColumn(name="winner")
  private User user;
  
  //getter setter methods
  }
  </code></pre>
  
  <p><strong>Bid.java:</strong></p>
  
  <pre><code>@Entity
  @Table(name="BIDS")
  public class Bid {
  
  @Id
  private Integer bid_id;
  @ManyToOne
  @JoinColumn(name="item_id")
  private Item item;
  @ManyToOne
  @JoinColumn(name="user_id")
  private User user;
  private int amount;
  //getter setter nethods
  }
  </code></pre>
  
  <p>Below is the service class, where I am defining the <strong>scheduledTask() method</strong> to run and calculate the winner of Item auctioned once the end time of auction item is passed:</p>
  
  <p><strong>ItemService.java:</strong></p>
  
  <pre><code>@Service
  public class ItemService {
  
      @Autowired
      private ItemRepository itemRepository;
  
      @Autowired
      private UserRepository userRepository;
  
  
      public List&lt;Item&gt; getAllItems(){
          return (List&lt;Item&gt;) itemRepository.findAll();
      }
  
      public Object getItem(Integer id) {
          Item i = itemRepository.findById(id).orElse(null);
          if(i.getUser()!=null) {
              return i.getUser();
          }
          else {
              //will be returning highest bid amount for that particular item
              return itemRepository.getMaxBid(id);
          }
      }
  
      @Scheduled(cron="20 36 17 * * ?")
      public void scheduledTask() {
          List&lt;Item&gt; listOfItems = (List&lt;Item&gt;)itemRepository.findAll();
          System.out.println("going to update DB");
          for(Item i : listOfItems) {
              Timestamp time = new Timestamp(System.currentTimeMillis());
              if(time.equals(i.getEnd_time()) || time.after(i.getEnd_time())) {
                  if(i.getUser() == null) {
                      Integer item_id = i.getItem_id();
                      Integer winner_id = itemRepository.findWinner(item_id);
                      User u= userRepository.findById(winner_id).orElse(null);
  
                      i.setUser(u);
                      System.out.println("updated");
                  }
              }
          }
      }
  
  }
  </code></pre>
  
  <p><strong>ItemRepository.java:</strong></p>
  
  <pre><code>public interface ItemRepository extends CrudRepository&lt;Item,Integer&gt;{
  
  @Query(value="select max(b.amount) from bids as b where b.item_id=?1", 
  nativeQuery=true )
  public Integer getMaxBid(Integer item_id);
  
  @Query(value="select b.user_id from bids as b where b.item_id=?1 AND 
  b.amount = (select max(amount) from bids);", nativeQuery=true)
  public Integer findWinner(Integer id);
  </code></pre>
  
  <p>Below is the error which I get once, the time comes to update the data in table:</p>
  
  <blockquote>
    <p>2019-04-22 12:55:30.123  INFO 9100 --- [   scheduling-1] >o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using >ASTQueryTranslatorFactory
    going to update DB
    2019-04-22 12:55:30.464 ERROR 9100 --- [   scheduling-1] ?>o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in >scheduled task.</p>
    
    <p>org.springframework.dao.InvalidDataAccessApiUsageException: >org.hibernate.QueryException: JPA-style positional param was not an integral >ordinal; nested exception is java.lang.IllegalArgumentException: >org.hibernate.QueryException: JPA-style positional param was not an integral >ordinal
      at >org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExcepti>onIfPossible(EntityManagerFactoryUtils.java:373) ~[spring-orm->5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPo>ssible(HibernateJpaDialect.java:255) ~[spring-orm->]>5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExcepti>onIfPossible(AbstractEntityManagerFactoryBean.java:527) ~[spring-orm->5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.dao.support.ChainedPersistenceExceptionTranslator.transla>teExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~
    [spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAcce>ssUtils.java:242) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.in>voke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx->5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflecti>veMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcess>or$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPos>tProcessor.java:138) ~[spring-data-jpa-2.1.6.RELEASE.jar:2.1.6.RELEASE]
      at >org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflecti>veMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(Expose>InvocationInterceptor.java:93) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflecti>veMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.data.repository.core.support.SurroundingTransactionDetect>orMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.ja>va:61) ~[spring-data-commons-2.1.6.RELEASE.jar:2.1.6.RELEASE]
      at >org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflecti>veMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProx>y.java:212) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at com.sun.proxy.$Proxy91.findUser(Unknown Source) ~[na:na]
      at auction.demo.service.ItemService.scheduledTask(ItemService.java:55) >~[classes/:na]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~
    [na:1.8.0_121]
      at >sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >~[na:1.8.0_121]
      at >sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.>java:43) ~[na:1.8.0_121]
      at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
      at >org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledM>ethodRunnable.java:84) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(De>legatingErrorHandlingRunnable.java:54) ~[spring-context->5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >org.springframework.scheduling.concurrent.ReschedulingRunnable.run(Rescheduli>ngRunnable.java:93) [spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
      at >java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) 
    [na:1.8.0_121]
      at java.util.concurrent.FutureTask.run(FutureTask.java:266) 
    [na:1.8.0_121]
      at >java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$2>01(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_121]
      at >java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Sche>duledThreadPoolExecutor.java:293) [na:1.8.0_121]
      at >java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:114>2) [na:1.8.0_121]
      at >java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:61>7) [na:1.8.0_121]
      at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]</p>
  </blockquote>
  
  <p><strong>Question:</strong> Create a scheduled task which runs when at the auction end time of an item and decides the winner of that item from Bids table and stores the Id of that user in Winner column of Auction_items table.</p>
  
  <p>I think the reason behind above Error is, I have <strong>created a User member variable in model class, but in table side, it is expecting only the id of User not the complete User instance</strong>.</p>
  
  <p>Even if this is the reason, I am not sure how to solve it, also <strong>if this is not the reason, will the application automatically store the proper user_id in winner column, if proper user instance is set using setUser().</strong></p>
  
  <p>Note that I have used @EnabledScheduling in the class, with main() method.</p>
  
  <p><strong>UPDATE:</strong> Pasted new ItemService.java, Item.java
  In Item.java I have used datatype User for the member variable since making it as Integer was not working</p>