Connect MySQL to Spring application

Question

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="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <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>http://repo.spring.io/libs-snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>

    <pluginRepositories>
        <pluginRepository>
            <id>spring-libs-snapshot</id>
            <url>http://repo.spring.io/libs-snapshot</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.

Make sure your settings are right, that your database exists and is up and running and that you can connect to it with that user. Also make sure that you have selected the correct dialect for Hibernate else you might end up with the wrong SQL . — M. Deinum, Sep 19 '14 at 09:21
hey, my database exists and its up and running... But I dont know how to select dialect for hibernate. can you suggest something useful related? — jimish, Sep 19 '14 at 09:59
I suggest a read of the documentation instead of only hacking around. However setting the `spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect` should do the trick. You also might want to add the full stack trace to your post. — M. Deinum, Sep 19 '14 at 10:06

score 34 · Answer 1 · edited Oct 14 '21 at 22:59

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.

score 7 · Answer 2 · answered Sep 15 '20 at 10:46

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

Edward J Beckett · Answer 3 · 2014-09-19T20:21:19.717

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

score 4 · Answer 4 · answered Jul 04 '23 at 18:08

4

As of spring boot 3, org.hibernate.dialect.MySQL8Dialect and com.mysql.jdbc.Driver are deprecated. Use the below properties instead

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

sample application.properties file

spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true
spring.datasource.url=jdbc:mysql://localhost:3306/dev
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

answered Jul 04 '23 at 18:08

srinivas chaitanya

427
5
10

where can I find the values to set `spring.jpa.properties.hibernate.dialect` ? This property also doesn't show up in https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html#application-properties.data.spring.jpa.database-platform – sat1017 Aug 11 '23 at 03:24
Please have a look at this article https://vladmihalcea.com/hibernate-dialect/ – srinivas chaitanya Aug 11 '23 at 06:23

score 1 · Answer 5 · edited Jun 20 '20 at 09:12

1

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 14 '16 at 06:08

Vinoj Vetha

726
6
8

score 1 · Answer 6 · answered Nov 11 '16 at 00:51

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

score 1 · Answer 7 · answered Apr 21 '21 at 04:07

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

score 0 · Answer 8 · answered Mar 03 '20 at 17:37

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= )

score 0 · Answer 9 · answered Jul 20 '22 at 09:15

I will like to add that we should be mindful of the database platform we are using while connecting to our SQL database.

spring.jpa.database-platform=org.hibernate.dialect.MYSQL8Dialect.

wasn't working for me, until I commented it out and added

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

score 0 · Answer 10 · answered Nov 29 '22 at 11:15

spring.datasource.url = jdbc:mysql://127.0.0.1:3306/employee-schema
spring.datasource.username = root
spring.datasource.password = Vinojan
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

score -1 · Answer 11 · edited Sep 19 '14 at 10:08

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 id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
          <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 id="dataSource" class="{our.DataSource}" 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 id="entityManagerFactory"
            class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="persistenceUnitName" value="spring-jpa" />
            <property name="dataSource" ref="dataSource" />
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                    <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

He has a problem in parsing a result, the application is running fine so the dependencies are ok. Also the dependencies come from a spring boot starter project which already include the proper dependencies. — M. Deinum, Sep 19 '14 at 10:08

Connect MySQL to Spring application

11 Answers11

Database Server IP, Port Number and DB Name

Database Server UserName

Database Server Password

Update database on every Time Server starts

Linked