Connecting MySql Database in Spring JDBC

This example shows how to connect MySql database server in Spring JDBC application. If you do not have MySql database server installed, follow this tutorial to download, install and create a database schema to connect with.

Example

Creating Spring Jdbc application

@Repository
public class PersonDao implements Dao<Person> {
  @Autowired
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;
  private SimpleJdbcInsert jdbcInsert;

  @PostConstruct
  private void postConstruct() {
      jdbcTemplate = new JdbcTemplate(dataSource);
      jdbcInsert = new SimpleJdbcInsert(dataSource)
              .withTableName("PERSON").usingGeneratedKeyColumns("ID");
  }

  @Override
  public void save(Person person) {
      SqlParameterSource parameters = new BeanPropertySqlParameterSource(person);
      jdbcInsert.execute(parameters);
  }
    .............
  @Override
  public List<Person> loadAll() {
      return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
          return toPerson(resultSet);
      });
  }
    .............
}

public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
    .............
}

@Component
public class PersonClient {

  @Autowired
  Dao<Person> personDao;

  public void process() {
      Person person = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
      System.out.println("saving: "+person);
      personDao.save(person);

      person = Person.create("Robin", "Cash", "64 Zella Park");
      System.out.println("saving: "+person);
      personDao.save(person);

      List<Person> list = personDao.loadAll();
      System.out.println("Loaded all: " + list);

  }
}

src/main/resources/createPersonTable.sql

CREATE TABLE PERSON(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);

Java Config and main class

DriverManagerDataSource can be used to connect any database server via a right JDBC driver for that particular database. We should also know the connection url and user/password to connect the server. The database should be running in the server mode for following to work.

@Configuration
@ComponentScan
public class AppConfig {
  @Bean
  public DataSource dataSource() {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
      ds.setUrl("jdbc:mysql://localhost:3306/my_schema");
      ds.setUsername("root");
      ds.setPassword("1234");
      return ds;
  }

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      context.getBean(PersonClient.class).process();
  }
}

Output

Sep 15, 2017 10:45:59 AM org.springframework.context.annotation.AnnotationConfigApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@254989ff: startup date [Fri Sep 15 10:45:59 CDT 2017]; root of context hierarchy
Sep 15, 2017 10:46:00 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
saving: Person{id=0, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Fri Sep 15 10:46:00 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ 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.
Fri Sep 15 10:46:00 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ 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.
saving: Person{id=0, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Fri Sep 15 10:46:00 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ 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.
Fri Sep 15 10:46:00 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ 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.
Loaded all: [Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}, Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}]

Example Project

Dependencies and Technologies Used:

• spring-context 4.2.3.RELEASE: Spring Context.
• spring-jdbc 4.2.3.RELEASE: Spring JDBC.
• mysql-connector-java 5.1.44: MySQL JDBC Type 4 driver.
• JDK 1.8
• Maven 3.3.9