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
– M. Deinum, Commented Sep 19, 2014 at 9: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
– jimish, Commented Sep 19, 2014 at 9: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
– M. Deinum, Commented Sep 19, 2014 at 10:06

Community · Accepted Answer · 2021-10-14 22:59:20Z

42

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.

edited Oct 14, 2021 at 22:59

CommunityBot

11 silver badge

answered Jul 12, 2015 at 4:41

gauravbhatt13

5314 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

srinivas chaitanya · Accepted Answer · 2023-07-04 18:08:35Z

11

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 4, 2023 at 18:08

srinivas chaitanya

5976 silver badges13 bronze badges

2 Comments

sat1017 Over a year ago

where can I find the values to set spring.jpa.properties.hibernate.dialect ? This property also doesn't show up in docs.spring.io/spring-boot/docs/current/reference/html/…

srinivas chaitanya Over a year ago

Please have a look at this article vladmihalcea.com/hibernate-dialect

Md Tofazzal Hossain · Accepted Answer · 2020-09-15 10:46:31Z

10

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

answered Sep 15, 2020 at 10:46

Md Tofazzal Hossain

2162 silver badges6 bronze badges

Comments

Edward J Beckett · Accepted Answer · 2014-09-19 20:21:19Z

6

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

edited Sep 19, 2014 at 20:21

answered Sep 19, 2014 at 20:04

Edward J Beckett

5,1402 gold badges43 silver badges41 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 14, 2016 at 6:08

Vinoj Vetha

7346 silver badges9 bronze badges

Comments

Vy Do · Accepted Answer · 2016-11-11 00:51:47Z

1

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

answered Nov 11, 2016 at 0:51

Vy Do

1

Comments

Lova Chittumuri · Accepted Answer · 2021-04-21 04:07:41Z

1

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

answered Apr 21, 2021 at 4:07

Lova Chittumuri

3,4011 gold badge38 silver badges39 bronze badges

Comments

Manojkumaar G · Accepted Answer · 2023-10-08 18:15:33Z

1

The following can be used for application.yml file

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/wikimedia
    username: root
    password: root
  jpa:
    database: mysql
    show-sql: true
    database-platform: org.hibernate.dialect.MySQLDialect
    hibernate:
      ddl-auto: update
  data:
    jpa:
      repositories:
        enabled: true

answered Oct 8, 2023 at 18:15

Manojkumaar G

1121 silver badge7 bronze badges

Comments

Mahfuz Ahmed · Accepted Answer · 2020-03-03 17:37:55Z

0

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

answered Mar 3, 2020 at 17:37

Mahfuz Ahmed

7199 silver badges23 bronze badges

Comments

Salami Korede · Accepted Answer · 2022-07-20 09:15:46Z

0

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

answered Jul 20, 2022 at 9:15

Salami Korede

4295 silver badges10 bronze badges

Comments

Vinojan Isoft · Accepted Answer · 2022-11-29 11:15:46Z

0

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

answered Nov 29, 2022 at 11:15

Vinojan Isoft

611 silver badge1 bronze badge

Comments

M. Deinum · Accepted Answer · 2014-09-19 10:08:11Z

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.

Collectives™ on Stack Overflow

Connect MySQL to Spring application

12 Answers 12

Comments

2 Comments

Comments

Comments

Database Server IP, Port Number and DB Name

Database Server UserName

Database Server Password

Update database on every Time Server starts

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

Comments

2 Comments

Comments

Comments

Database Server IP, Port Number and DB Name

Database Server UserName

Database Server Password

Update database on every Time Server starts

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related