I want to use MySQL database rather than using runtime database like hsqldb. I have cloned this repository and it is using hsqldb as its database.

As I want to learn how to use relational database with rest based spring application. So I have made following changes to pom.xml: changed pom.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project xmlns="" xmlns:xsi=""
xsi:schemaLocation=" "> <modelVersion>4.0.0</modelVersion> <groupId>org.springsource.restbucks</groupId> <artifactId>restbucks</artifactId> <packaging>war</packaging> <version>1.0.0.BUILD-SNAPSHOT</version> <name>Spring RESTBucks</name> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.1.5.RELEASE</version> </parent> <properties> <spring-data-releasetrain.version>Evans-RC1</spring-data-releasetrain.version> <tomcat.version>8.0.9</tomcat.version> </properties> <dependencies> <!-- Spring Data REST --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-rest</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <exclusions> <exclusion> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <!-- JDK 8 DateTime support for Hibernate --> <dependency> <groupId>org.jadira.usertype</groupId> <artifactId>usertype.extended</artifactId> <version>3.2.0.GA</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-jsr310</artifactId> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> </dependency> <!-- Database > <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <scope>runtime</scope> </dependency --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- Misc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx-events</artifactId> <version>1.0.0.BUILD-SNAPSHOT</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.14.4</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.jayway.jsonpath</groupId> <artifactId>json-path</artifactId> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> <repositories> <repository> <id>spring-libs-snapshot</id> <url> <snapshots> <enabled>true</enabled> </snapshots> </repository> </repositories> <pluginRepositories> <pluginRepository> <id>spring-libs-snapshot</id> <url> <snapshots> <enabled>true</enabled> </snapshots> </pluginRepository> </pluginRepositories>
</project>

and in application.properties (spring-restbucks/src/main/resources/application.properties), I have made following changes:

# JPA
spring.jpa.show-sql=true
server.port=8080
spring.datasource.url=jdbc:mysql://localhost/restBucks
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

but I am facing about 15 errors like:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'orderInitializer' defined in file [/home/jimish/projects/spring_projects/spring/spring-restbucks/target/classes/org/springsource/restbucks/order/OrderInitializer.class]: Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [org.springsource.restbucks.order.OrderInitializer]: Constructor threw exception; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

So if anyone can suggest path for how to connect mysql to spring application that would be great. Thanks.

3

8 Answers

Include only the following configuration in your application.properties in classpath and it will work like a charm:

 spring.datasource.url=jdbc:mysql://localhost/jpa_example spring.datasource.username=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

This "spring.jpa.database-platform" specify which database you are going to use. It may be postgress, MySQL etc. According to that you need to specify it.

Expounding on @M. Deinum's comment... You need to specify the JPA configuration information as the Spring RestBucks App is using Spring Data JPA.

Adding standard JPA properties and specifying the database-platform (Common Application Properties) should get your JPA connection working.

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
# Enable spring data repos
spring.data.jpa.repositories.enabled=true
# Replace with your connection string
spring.datasource.url=jdbc:mysql://localhost:3306/restbucks
# Replace with your credentials
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=com.mysql.jdbc.Driver

Add bellow properties in your application.properties file:

# Use one of create/update/create-update
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/db?createDatabaseIfNotExist=true
spring.datasource.username=root //your db user name
spring.datasource.password=root //your db user password
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
#If your MySql version 5 then use MySQL5Dialect
# Enable spring data repos
spring.data.jpa.repositories.enabled=true
spring.jpa.show-sql=true
spring.jpa.database=mysql

Database Server IP, Port Number and DB Name

spring.datasource.url=jdbc:mysql://localhost/sample

Database Server UserName

spring.datasource.username=root

Database Server Password

spring.datasource.password=root123

Update database on every Time Server starts

spring.jpa.hibernate.ddl-auto=update

Add MySQL Connector/J to classpath:

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.5</version>
</dependency>

This is an example of file application.properties inside Spring-based application:

datasource.mine.poolSize=30
spring.datasource.url=jdbc:mysql://127.0.0.1/vy?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/boot
spring.datasource.username=root
spring.datasource.password=

I was tried with above configuration but was not connected after 1 hour I was get connected by putting an SPACE after password= (password= )

Please add mysql-connector-java dependency

 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>

and Add the below mentioned properties in application.properties

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
spring.datasource.url=jdbc:mysql://localhost:3306/yourDatabaseName?useSSL=false
spring.datasource.username=root
spring.datasource.password=YourPassword
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

You can verify your pom file and make sure minimum of below jars are available

 <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> </dependency> <dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> </dependency> <!-- Spring AOP --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> </dependency> <!-- MySQL JDBC connector --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> **Make sure you are reading your db.properties file from application-context.xml** <bean> <property name="locations"> <list> <value>classpath:db.properties</value> </list> </property> </bean> **Make sure you use proper datasource and entity manager in application-context.xml** <bean destroy-method="close"> <property name="driverClass" value="${driver}"/> <property name="jdbcUrl" value="${url}"/> <property name="user" value="${username}"/> <property name="password" value="${password}"/> </bean> <bean> <property name="persistenceUnitName" value="spring-jpa" /> <property name="dataSource" ref="dataSource" /> <property name="jpaVendorAdapter"> <bean> <property name="generateDdl" value="true" /> <property name="showSql" value="false" /> <property name="databasePlatform" value="${dialect}" /> </bean> </property> </bean> **Make sure you are adding minimum of below properties in db.properties** dialect=org.hibernate.dialect.MySQL5InnoDBDialect url=jdbc:mysql://localhost:3306/dbName driver=com.mysql.jdbc.Driver username=root password=test

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy