I got a task, to create a basic app with some database handling. I'm really new at this, the whole spring boot and stuff. I did all the GET, PUT, POST, DELETE stuff without writing any line of SQL, starts with table creation. But they asked me to provide the SQL script, how I managed to create the database structure, connections and stuff. How can I solve this problem?
1
-
1It sounds like you've already got the database working ... without SQL. But now you've been asked to generate SQL (for what's already working). Correct? SOLUTION: Just configure Spring/JPA to dump the SQL commands (e.g. on a scratch database), and copy/paste the output from the Spring boot log into a SQL script: baeldung.com/sql-logging-spring-bootpaulsm4– paulsm42021-08-18 21:17:17 +00:00Commented Aug 18, 2021 at 21:17
Add a comment
|
3 Answers
Add following to spring.jpa.properties
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-source=metadata
Please refer this article https://www.baeldung.com/spring-data-jpa-generate-db-schema by @baeldung for more detail.
Comments
The most simple way to do this is to create in the resources folder a file named schema.sql with all your queries concerning the DDL (tables creation...) and a data.sql if needed where you can add INSERT queries to populate your database.
In the application.properties then you have to add spring.jpa.hibernate.ddl-auto=none to disable the schema creation of JPA and let Spring Boot executes those scripts at startup automatically.
If you want something more powerful, you should try Liquibase or Flyway.
Comments
You can used Jpa Like JpaRepository interface. You don't need write any SQL query we use it just used config your data base into Application.Property like database type
Your user name and password
public interface UserRepository extends CrudRepository<User, Integer> {}
when execute this code you can used create user , get find all user , find user by id , delete user and update user
and used @Entity annotaion into Entity class the Entity class Represent Table into your database
@Entity
@Table(name="user")
public class User{
@Colume(name="id")
private Long id;
@Colume(name="name")
private String name;
//getter and setter here
}
when run this class your data base contract User table with id and name
this link create spring boot application with database https://spring.io/guides/gs/accessing-data-mysql/
2 Comments
Artisz
That's how I did it. But they asked me to provide the SQL script in a separate .sql file
Abdalrhman Alkraien
the hibernate in your system record any operation between db and your app but i dont know where you can find this record or if you can write this logger into text file or not but you can search it @Artisz