Hey everyone, I'm looking for suggestions, as a pa...

# spring

[Sushruth Nagaraj]

Hey everyone, I'm looking for suggestions, as a part of a database management systems course in my engineering college, I've to build a mini project to demonstrate the various concepts like create, update and query on the database etc. I've decided to build a dog adoption system. Basically, there are two types of users, 1). admin - posts the details of the dogs, maintains the record of users and dogs, processes the requests of dogs by the user, etc. 2). user- creates a profile, views a list of dogs, requests for dogs and waits for the confirmation, etc(I've attached the ER diagram below, any suggestions are welcomed). I've thought of using MySQL as the database management system, Spring Boot (Spring MVC, Spring Security, Thymeleaf, JDBC API and optionally Vaadin) I don't think I can use an ORM or API's like Spring Data JPA and I'll have to work with pure SQL queries and statements. I'm finding it hard to look for examples and resources for the same, any help would be appreciated. Thanks.

[Sushruth Nagaraj]

As of now, I've created the database and tables in MySQL workbench. I know how to connect to the Spring Boot application to MySQL using JDBC API (adding the dependencies in pom.xml and specifying the database configurations in application.properties). I'm finding it hard to implement role based authentication and authorization of user role and admin role and the login and registration using Spring Boot(Spring MVC, Spring Security), MySQL, JDBC API and view layer technologies(Thymeleaf or HTML, CSS or Bootstrap or Vaadin)

[James Whitehead]

I don't think I can use an ORM or API's like Spring Data JPA and I'll have to work with pure SQL queries and statements.

Why not? It would make your life much easier. Define your tables as entities (classes), spring boot can generate the DDL and apply it automatically for you at boot.

[Sushruth Nagaraj]

The thing is that it's a mini project as a part of my college curriculum and I'll have to work close to the database. I already have the database part with the tables. I need to connect it via JDBC API and do the login, sign up l, create, retrieve, update, delete and query operations

[Ties]

You can actually do normal queries in Spring boot, look at the @Query annotation 🙂

[Ties]

https://www.baeldung.com/spring-data-jpa-query here are some examples (do pay attention to the @Modifying annotation part.. that is going to safe you some debugging time :P)

[James Whitehead]

You can still create Entities even with an existing DB. You'll just need to set the spring boot auto ddl mode to validate

[Sushruth Nagaraj]

Thanks for the info, I didn't know this, I'll take a look into it.

[Ties]

Good luck 🙂 and let me know if you have any questions (spring security can be quite a thing...) also.. as another tip. Start off with the in-memory database H2, all you need is to add a dependency for that. This will speed up development quite a bit since you will get a clean database everytime you restart your application. Once you have stuff working you can add a MySQL (or Postgres) driver

[Sushruth Nagaraj]

Thanks for the suggestions, I'll definitely consider these points and reach out if I get blocked.

[corneil]

You can also consider using Spring Data JDBC which is simpler than JPA but still better than plain JDBC.

[corneil]

Your alternative is to use JDBCTemplate to perform all the operations creating rowmappers etc as needed.

[Sushruth Nagaraj]

Yes, I'll look into it.