Some table rows are not returned by hibernate native query

See also questions close to this topic

• Read each name in Array list to create seperate object for

  I have a file that has student names, age, and an id number. I have a student class that holds the everything above for each student object. I stored all the names, id numbers. and age separately in an array list. Now im trying to assign the info to create a student object.

  public class Student {
      private String lName;
      private int idNumber;
      private int age;
  
      public Student() {
          lName = "";
          idNumber = 0;
          age = 0;
      }
      public Student(String l, int i, int a) {
          lName = l;
          idNumber = i;
          age = a;
      }
  
      public void setName(String last) {
          lName = last;
      }
  
      public String getName() {
          return lName;
      }
  
      public void setIdNum(int num) {
          idNumber = num;
      }
  
      public int getIdNum() {
          return idNumber;
      }
  
      public void setAge(int a) {
          age = a;
      }
  
      public int getAge() {
          return age;
      }
  }
  

  My Text File looks something like this: This info is stored in parallel array lists. I don't quite get how to implement this into an object to pass it into my second contractor method.

    Josh          2134         19
    Smith         5256         21
    Rogers        9248         19
    Andrew        7742         20
  

  Here's what I've tried;

  public static void main(String[] args) {
      String file = "studentData.txt";
      Scanner reader = new Scanner(file);
  
      ArrayList<String> lastNames = lNames(file);
      ArrayList<Integer> idNumbers = idNum(file);
      ArrayList<Integer> ageList = ages(file);
  
      Scanner input = new Scanner(System.in);
  
      Student s1 = new Student();
  
      // confused about how to implement this constructor with the textile info
      for (int i = 0; i<idNumbers.size(); i++) {
          Student user = new Student(lastNames.get(i), idNumbers.get(i), ageList.get(i));
      }
  
      //user enters idNumber to display age
      System.out.println("Enter ID Number"); //exception handling to be added
      int idNum = input.nextInt();
  
      for (int i = 0; i<idNumbers.size(); i++) {
          if (idNum == idNumbers.get(i)) {
              s1.setAge(ageList.get(i));
              System.out.println(s1.getAge());
  
          }
      }
  }
  
• Using EdittextPreference for Goto search

  sorry for my poor English. I want to use EditTextPreference in my bottom nav like the pic below, ![screenshot][1]

  I have recycleview xml in my project with in many sub cardview layouts(which is scrollable) and I want to create item in the bottom nav which is called "Goto". When the "Goto" item is clicked i want it to pop-up like the screenshot. And when user enters a number(according to the cardviews i.e if the number of cardview is 40 user must enter 1-40) I want to search the cardview by its ID. Thank you and I hope u got it, If u have any questions let me know [1]: https://i.stack.imgur.com/grK8P.jpg

  My xml format look like this. As you see in the blow since the cardviews are huge in number it is not cool to scroll all the way down that is why i need Goto item in the bottom nav to search it by its ID when the user click number in the EditTextPreference as u see in the screenshot. i.e The screenshot is not from my app

  <LinearLayout>
   <LinearLayout>
    <androidx.cardview.widget.CardView>
      <RealtiveLayout>
         <Textview/>
      <RealtiveLayout>
   </androidx.cardview.widget.CardView>
   </LinearLayout>
  
   <LinearLayout>
    <androidx.cardview.widget.CardView>
      <RealtiveLayout>
         <Textview/>
      <RealtiveLayout>
   </androidx.cardview.widget.CardView>
   </LinearLayout>
  
  <LinearLayout>
    <androidx.cardview.widget.CardView>
      <RealtiveLayout>
         <Textview/>
      <RealtiveLayout>
   </androidx.cardview.widget.CardView>
   </LinearLayout>
  
    <LinearLayout>
    <androidx.cardview.widget.CardView>
      <RealtiveLayout>
         <Textview/>
      <RealtiveLayout>
   </androidx.cardview.widget.CardView>
   </LinearLayout>
   
    ..
    ..
  
    ..
    ..
    many more..
  
• How to get remaining time of the day in java?

  I would like to calculate the time remaining for next day 00:00:00 from the current date time.

  For e.g. time difference between 2022-05-07T05:49:41.883807900Z and 2022-05-08T00:00:00Z

  Expected answer: 18:10:19 or 65419 (in seconds).

  How can I achieve this with efficiently using java 8?
• Why does this Sql query shows the possible changes but does not implement it?

  So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

  SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

  Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
• how to get weeklytotal and yesterday record in mysql in one table

  Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

  Table name-dailydata-

  Sample data

  employee_id	date	total
  20	2022-04-25	10
  20	2022-04-26	20
  20	2022-04-27	20
  20	2022-04-28	10
  20	2022-04-29	20
  20	2022-04-30	30
  20	2022-04-31	40
  20	2022-05-01	50
  40	2022-04-26	20

  expected output

  employee_id	weekly_total	yesterday_record
  20	200	40
  40	20	0

  mysql query to get weekly data

  select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  
• Join to another table only matching specific records

  I have a table of ports:

  drop table if exists ports;
  create table ports(id int, name char(20));
  
  insert into ports (id, name ) values
    (1, 'Port hedland'), 
    (2, 'Kwinana');
  

  And a table of tariffs connected to those ports:

  drop table if exists tariffs;
  create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 2, 11.00, 1648408400), 
      (2, 2, 12.00, 1648508400), 
      (3, 2, 13.00, 1648594800), 
      (4, 2, 14.00, 1651273200),
      (5, 2, 15.00, 2250000000 );
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 1, 21.00, 1648408400), 
      (2, 1, 22.00, 1648508400), 
      (3, 1, 23.00, 1648594800), 
      (4, 1, 24.00, 1651273200),
      (5, 1, 25.00, 2250000000 );
  

  Each tariff has an expiry.

  I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

  SELECT * FROM tariffs 
  WHERE 1648594700 < expiry AND portId = 2
  ORDER BY expiry
  LIMIT 1
  

  Result:

  id  portId  price   expiry
  3   2   13.00   1648594800
  

  However, in my application I want to be able to pull in the right tariff starting from the ports record.

  For one record, I can do this:

  SELECT * FROM ports 
  LEFT JOIN tariffs on tariffs.portId = ports.id 
  WHERE 1648594700 < tariffs.expiry AND ports.id = 2
  LIMIT 1
  

  Result:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  

  This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

  What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

  This obviously won't work:

  SELECT * FROM ports 
  left join tariffs on tariffs.portId = ports.id
  where 1648594700 < tariffs.expiry
  

  Since the result of the query, testing with timestamp 1648594700, would be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  2   Kwinana 4   2   14.00   1651273200
  2   Kwinana 5   2   15.00   2250000000
  1   Port he 3   1   23.00   1648594800
  1   Port he 4   1   24.00   1651273200
  1   Port he 5   1   25.00   2250000000
  

  Instead, the result for all ports (before further filtering) should be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  1   Port he 3   1   23.00   1648594800
  

  Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?
• Hibernate handles id assigning instead of database

  I have an auto-increment PK in a table and I want hibernate to handle id assigning instead of database. As my understanding the @GeneratedValue(strategy = GenerationType.IDENTITY) lets the database generate a new value with each insertion operation. So do we have any different solution to handle it?
• Spring boot mapsId

  Hi I using spring boot jpa and I need to use mapsid. There are not so much information about that and I wonder if anyone can help me understand how to use maybe link a simple code example where its 2 table that joins 1 table (oneToOne)
• Multiple list of Data comming in API when using ManyToOne Relationship

  CarMake.java

  @Getter
  @Setter
  @EqualsAndHashCode
  @NoArgsConstructor
  @Entity
  public class CarMake {
      @SequenceGenerator(
              name = "car_make_sequence",
              sequenceName = "car_make_sequence",
              allocationSize = 1
      )
      @Id
      @GeneratedValue(
              strategy = GenerationType.SEQUENCE,
              generator = "car_make_sequence"
      )
      private Long id;
      private String name;
      @CreationTimestamp
      private LocalDateTime createdAt;
      @UpdateTimestamp
      private LocalDateTime updatedAt;
      @OneToMany(mappedBy = "make", cascade = CascadeType.ALL)
      private List<CarModel> model;
  }
  

  CarModel.java

  @Getter
  @Setter
  @EqualsAndHashCode
  @NoArgsConstructor
  @Entity
  public class CarModel {
      @SequenceGenerator(
              name = "car_model_sequence",
              sequenceName = "car_model_sequence",
              allocationSize = 1
      )
      @Id
      @GeneratedValue(
              strategy = GenerationType.SEQUENCE,
              generator = "car_model_sequence"
      )
      private Long id;
      private String name;
      private Integer beginDate;
      private Integer endDate;
      @CreationTimestamp
      private LocalDateTime createdAt;
      @UpdateTimestamp
      private LocalDateTime updatedAt;
      @ManyToOne(fetch = FetchType.EAGER)
      CarMake make;
      @OneToMany(mappedBy = "model", cascade = CascadeType.ALL)
      private List<CarVariant> variants;
  
  }
  

  CarVariant.java

  @Getter
  @Setter
  @EqualsAndHashCode
  @NoArgsConstructor
  @Entity
  public class CarVariant {
      @SequenceGenerator(
              name = "car_variant_sequence",
              sequenceName = "car_variant_sequence",
              allocationSize = 1
      )
      @Id
      @GeneratedValue(
              strategy = GenerationType.SEQUENCE,
              generator = "car_variant_sequence"
      )
      private Long id;
      @Column(nullable = false)
      private String name;
      @CreationTimestamp
      private LocalDateTime createdAt;
      @UpdateTimestamp
      private LocalDateTime updatedAt;
      @ManyToOne(fetch = FetchType.LAZY)
      CarModel model;
  }
  

  If i am using repository i am getting loop of data which is not present even in database

  @GetMapping("api/car/model/{id}")
      @ResponseBody
      public Iterable<CarMake> showCarModelsByMakeId(@PathVariable Long id){
          return carMakeRepository.findAll();
      }
  

  Bellow is the response of API

  [{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983","updatedAt":"2022-05-06T17:07:48.40903","model":[{"id":1,"name":"hello","beginDate":1,"endDate":3,"createdAt":"2022-05-06T17:07:59.440783","updatedAt":"2022-05-06T17:07:59.440826","make":{"id":1,"name":"hello","createdAt":"2022-05-06T17:07:48.408983",
  

  I beleive i should get only one instance of data as only one row is present in table. what is the reason i am getting so much data, did i created onetomany relationship correctly?, how i can achieve it

  Database data
• Why the data resets after save in database?

  I'm trying to save new phone number in database

  First by another method get phone number and temporary save it and send a code to user then by following methods check if user entered correct code

  The controller:

      @GetMapping("/check-code/{code}")
      public Boolean checkPhoneNumberCode(@PathVariable("code") int code){
          return phoneService.validateCode(code);
      }
  

  The service:

      @Override
      public Boolean validateCode(int code) {
          Integer phoneNumberCode = cacheService.getPhoneNumberCode(phoneNumber.toString());
          if (!phoneNumberCode.equals(code)) {
              resendCode();
              return false;
          }
          updateUser();
          clear();
          return true;
  
      }
  

  after the code was ok updateUser() method will call:

      @Override
      public void updateUser() {
          User user = userRepository.findByIdAndIsDeletedFalse(userId).get();
          user.setPhoneNumber(phoneNumber);
          userRepository.save(user);
      }
  

  The UserRepository:

  public interface UserRepository extends MongoRepository<User, String> {
  

  And at the end of updateUser() method data must be updated and it updated but when updateUser() method finishes and validateCode() method finishes I've checked, before checkPhoneNumberCode() method finishes, data still updated (before "}" finishes and program exit from debug mode) but when program exit from debug mode (checkPhoneNumberCode() method completes) data will reset and back to what was before I'm using Intellij as IDE and Java for back-end and MongoDB for database I don't think it relates to front-end but I'm using angular for front-end

  Github repository

  Controller: src -> main -> ToDoWeb -> controller -> PhoneController; Service: src -> main -> ToDoWeb -> service -> impl -> PhoneServiceImpl; Repository: src -> main -> ToDoWeb -> repository -> UserRepository;