7

I think it is possible to create a database and tables by the Spring boot web app. If I have a file with mysql queries to make database, tables, for example, sql_table.sql file. How to use generate databases and table on the fly if no exits?

James Z
  • 12,209
  • 10
  • 24
  • 44
masiboo
  • 4,537
  • 9
  • 75
  • 136
  • https://dzone.com/articles/using-mysql-jdbc-driver-with-spring-boot – AchillesVan Mar 13 '18 at 17:24
  • Duplicate question at https://stackoverflow.com/q/26367322/2887739 – abhi3232 Mar 13 '18 at 17:52
  • It only shows how to create tables only. How to create a database ? Is it possible anyway? – masiboo Mar 13 '18 at 17:55
  • Are you willing to create database when application starts and remove when it ends ? If yes, the best way to do it with any in memory database like H2. This scenario is helpful when you are dealing with volatile and less amount of data. – abhi3232 Mar 13 '18 at 18:04
  • No. I want to make only first time if it does not exist. If it exists I will use it. Otherwise, I will create one. – masiboo Mar 13 '18 at 18:10
  • Use hibernate then ... Simillar post https://stackoverflow.com/q/21069687/2887739 – abhi3232 Mar 13 '18 at 18:31
  • New database on spring.datasource.url=jdbc:mysql://localhost/name?createDatabaseIfNotExist=true. But I get lots of warnings as:- WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. – masiboo Mar 14 '18 at 07:13
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166795/discussion-between-masiboo-and-abhi3232). – masiboo Mar 14 '18 at 07:29

2 Answers2

16

I can create a new database without any SSL warnings by this:-

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=create
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/aaa?createDatabaseIfNotExist=true&useSSL=true
spring.datasource.username=root
pring.datasource.password=devv
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
masiboo
  • 4,537
  • 9
  • 75
  • 136
0

Spring Boot loads SQL scripts from the root of the classpath. You have to specify the data source properties in your application.yml or application.properties file. You must specify your database in data source properties like this:

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=create
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=test
spring.datasource.password=

where test is your database.

Ujjawal Gupta
  • 179
  • 9
  • Not sure it's possible to create a database on the fly. With Mysql You must atleast create one manually. – AchillesVan Mar 13 '18 at 18:03
  • error:- ERROR o.a.tomcat.jdbc.pool.ConnectionPool - Unable to create initial connections of pool. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'test' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) – masiboo Mar 13 '18 at 18:27
  • 1
    Hibernate will not create database until you specify it explicitly like jdbc:mysql://db:3306/mydatabase?createDatabaseIfNotExist – abhi3232 Mar 13 '18 at 18:28