Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java this week in the Agile and Other Processes forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

How to go about this issue of accessing by foreign key with Spring Boot?

 
Ranch Hand
Posts: 351
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Background...

I'm trying to create a simple application in Spring Boot using Javascript and MySQL as well.  

It is an application where student reserve a ticket to the prom.  The school has a ticket with a ticket number they give to them, and at the door, they type in the ticket number and it alerts them verify the one who gave them the student who reserved it by student ID.  

Okay, so they have the option to enter a student into the database and manually give them an ID number, but multiple schools can have students with the same student number, so the primary key is the school, and the student ID number.  This can be the same for a ticket number as they may purchase a roll of tickets and just add the number onto them, or pre-print them with their own numbers and add them.  

So, in the event they are checking in at Prom, show their ticket, the number is entered, what is the best way to filter it out so that if multiple tickets in the MySQL have the same ticket number, you only get it to where if it matches that foreign key school ID?  

Would this be best done in a controller method in Spring Boot, or in a Javascript method on the front end?  

I was thinking something where at the beginning of the program, you store the school ID in java as a static int variable, and then only add the ones to the ArrayList in the Repository class if they meet that ID, that way nothing will need to be done in Javascript.  

However, does anyone even know what I'm talking about to begin with to know if this is the right thing to do, and how it could be accomplished?
 
Saloon Keeper
Posts: 21715
148
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What you are talking about is a compound primary key. That is, a primary key that is formed by the concatenation of 2 or more fields in a table. Compound primary keys can be referenced via compound foreign keys. This can be enforced in the usual way when you define the database schema.

Spring Boot doesn't really factor in here. It's Spring Data that deals with that kind of stuff. And actually, it's only the entity mapping parts that even care whether your schema defines a simple key or a compound key. The rest is just straight JPA.
 
Nathan Milota
Ranch Hand
Posts: 351
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:What you are talking about is a compound primary key. That is, a primary key that is formed by the concatenation of 2 or more fields in a table. Compound primary keys can be referenced via compound foreign keys. This can be enforced in the usual way when you define the database schema.

Spring Boot doesn't really factor in here. It's Spring Data that deals with that kind of stuff. And actually, it's only the entity mapping parts that even care whether your schema defines a simple key or a compound key. The rest is just straight JPA.



Say it was a situation with no compound key, and I did a search on the website to get all students in the system.  What do you do to get it so that it only shows the students whose foreign key of the school matches the school ID#?  

That's something I haven't figured out how to retrieve that number from the database based on the current session, and then have it used as a search on the front end, or is the back end the place to set the parameters?
 
Tim Holloway
Saloon Keeper
Posts: 21715
148
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL does not include the concept of a "key". Keys are optimization constructs created and maintained in the DDL. They improve performance, but the SQL required to use them is exactly the same, key or no key. So either way, the underlying SQL would be "SELECT student WHERE student_id =  ? AND school = ?".
 
Nathan Milota
Ranch Hand
Posts: 351
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm bad at explaining this problem.  I guess I just don't know how to say, when a user logs in, to get a way to have the School ID sent to Java and then stored so when I run the query, I can pass in an int variable as that query, and I've not found an example.
 
There will be plenty of time to discuss your objections when and if you return. The cargo is this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!