Connecting a spring-boot application to MySQL

We know that spring-boot by default runs using H2 database. In this article, we will see how to tweak the default configuration to work with MySQL database.

Spring-boot sample using MySQL

We will follow the official guide for spring-boot and spring-data-jpa. We will be building the application using gradle.

**Create the gradle build file** build.gradle

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.4.3.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'

jar {
    baseName = 'gs-accessing-data-jpa'
    version =  '0.1.0'
}

repositories {
    mavenCentral()
    maven { url "https://repository.jboss.org/nexus/content/repositories/releases" }
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    runtime('mysql:mysql-connector-java')
    testCompile("junit:junit")
}

**Create the customer entity** src/main/java/hello/Customer.java

@Entity
public class Customer {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String firstName;
    private String lastName;

    protected Customer() {}

    public Customer(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    @Override
    public String toString() {
        return String.format(
                "Customer[id=%d, firstName='%s', lastName='%s']",
                id, firstName, lastName);
    }
}

**Create Repositories** src/main/java/hello/CustomerRepository.java

import java.util.List;
import org.springframework.data.repository.CrudRepository;

public interface CustomerRepository extends CrudRepository<Customer, Long> {
    List<Customer> findByLastName(String lastName);
}

**Create application.properties file**

################### DataSource Configuration ##########################
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/your_database_name
jdbc.username=username
jdbc.password=password

init-db=false

################### Hibernate Configuration ##########################

hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=update

**Create the PersistenceConfig.java file**

In step 5, we will be defining how the datasource will be loaded and how our application connects to MySQL. The above snippet is the bare minimum configuration we need to connect to MySQL. Here we provide two beans:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages="hello")
public class PersistenceConfig
{
    @Autowired
    private Environment env;

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory()
    {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(Boolean.TRUE);
        vendorAdapter.setShowSql(Boolean.TRUE);

        factory.setDataSource(dataSource());
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("hello");

        Properties jpaProperties = new Properties();
        jpaProperties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
        factory.setJpaProperties(jpaProperties);

        factory.afterPropertiesSet();
        factory.setLoadTimeWeaver(new InstrumentationLoadTimeWeaver());
        return factory;
    }

    @Bean
    public DataSource dataSource()
    {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.username"));
        dataSource.setPassword(env.getProperty("jdbc.password"));
        return dataSource;
    }

}

****LocalContainerEntityManagerFactoryBean**** This gives us an handle over the EntityManagerFactory configurations and allows us to do customizations. It also allows us to inject the PersistenceContext in our components as below:

@PersistenceContext
private EntityManager em;

****DataSource**** Here we return an instance of the `DriverManagerDataSource`. It is a simple implementation of the standard JDBC DataSource interface, configuring a plain old JDBC Driver via bean properties, and returning a new Connection for every getConnection call. Note that I recommend to use this strictly for testing purposes as there are better alternatives like `BasicDataSource` available. Refer [here](https://mytechrepo.wordpress.com/2015/02/17/drivermanagerdatasource-vs-basicdatasource/) for more details

**Create an Application class** src/main/java/hello/Application.java

 @SpringBootApplication
 public class Application {

     private static final Logger log = LoggerFactory.getLogger(Application.class);

     @Autowired
     private CustomerRepository repository;

     public static void main(String[] args) {
         SpringApplication.run(TestCoreApplication.class, args);
     }

     @Bean
     public CommandLineRunner demo() {
         return (args) -> {
             // save a couple of customers
             repository.save(new Customer("Jack", "Bauer"));
             repository.save(new Customer("Chloe", "O'Brian"));
             repository.save(new Customer("Kim", "Bauer"));
             repository.save(new Customer("David", "Palmer"));
             repository.save(new Customer("Michelle", "Dessler"));

             // fetch all customers
             log.info("Customers found with findAll():");
             log.info("-------------------------------");
             for (Customer customer : repository.findAll()) {
                 log.info(customer.toString());
             }
             log.info("");

             // fetch an individual customer by ID
             Customer customer = repository.findOne(1L);
             log.info("Customer found with findOne(1L):");
             log.info("--------------------------------");
             log.info(customer.toString());
             log.info("");

             // fetch customers by last name
             log.info("Customer found with findByLastName('Bauer'):");
             log.info("--------------------------------------------");
             for (Customer bauer : repository.findByLastName("Bauer")) {
                 log.info(bauer.toString());
         }
         log.info("");
     };
 }

}

**Running the application**

If you are using an IDE like STS, you can simply right click your project -> Run As -> Gradle (STS) Build… In the tasks list, type bootRun and Run.

If you are using gradle on command line, you can simply run the application as follows:

./gradlew bootRun

You should see something like this:

== Customers found with findAll():
Customer[id=1, firstName='Jack', lastName='Bauer']
Customer[id=2, firstName='Chloe', lastName='O'Brian']
Customer[id=3, firstName='Kim', lastName='Bauer']
Customer[id=4, firstName='David', lastName='Palmer']
Customer[id=5, firstName='Michelle', lastName='Dessler']

== Customer found with findOne(1L):
Customer[id=1, firstName='Jack', lastName='Bauer']

== Customer found with findByLastName('Bauer'):
Customer[id=1, firstName='Jack', lastName='Bauer']
Customer[id=3, firstName='Kim', lastName='Bauer']

Remarks

As a pre-requisite, make sure that MySQL is already running on port 3306 and has your database created.