Spring Boot, Spring security linked with database

Question

Sorry in advance for my bad english..

I am a beginner with Spring. A colleague advised me to use Spring boot to begin. For the moment I like that.

To begin I want to create an authentication / login module linked with a mySQL database.

I am working on IntelliJ and phpMyAdmin.

For this job there are 3 parts:
- Authentication system - OK
- Database link and basic operations - OK
- Link between authentication and database - NOT OK.

For the moment, for the authentication I have this file :

WebSecurityConfig.java

package hello;

//imports


@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {



@Override
protected void configure(HttpSecurity http) throws Exception {
    http
            .authorizeRequests()
            .antMatchers("/", "/home").permitAll()
            .anyRequest().authenticated()
            .and()
            .formLogin()
            .loginPage("/login")
            .permitAll()
            .and()
            .logout()
            .permitAll();
}

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
    auth
            .inMemoryAuthentication()
            .withUser("user").password("password").roles("USER");
}
}

To connect my aplication with the database I have this file :

application.properties

spring.datasource.url = jdbc:mysql://localhost/simulateur
spring.datasource.username =  root
spring.datasource.password =

# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = update

# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

# Use spring.jpa.properties.* for Hibernate native properties (the prefix is
# stripped before adding them to the entity manager)

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

I like this solution because it is remember me solution with Play Framework and a solution at my entreprise. I wish keep this file : application.properties. One config file seems me great.

To link all that, I found a solution on this website. I have to add this in my WebSecurityConfig.java :

@Autowired
DataSource dataSource;

@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {

  auth.jdbcAuthentication().dataSource(dataSource)
.usersByUsernameQuery(
 "select username,password, enabled from users where username=?")
.authoritiesByUsernameQuery(
 "select username, role from user_roles where username=?");
 }

And add this in file MvcConfig.java with routes and other features with correct parameters:

@Bean(name = "dataSource")
 public DriverManagerDataSource dataSource() {
 DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
 driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
 driverManagerDataSource.setUrl("jdbc:mysql://localhost/simulateur");
 driverManagerDataSource.setUsername("root");
 driverManagerDataSource.setPassword("");
 return driverManagerDataSource;
 }

My problem is that I have the impression to redefine database connection. I would like to use my file : application.properties. Have you an idea to use this file and don't use the part of code in MvcConfig.java ?

Thank you in advance for your help ! :)

You are creating a datasource bean, this is really helpful if you are working with multiple datasources for ex., Mysql and Oracle together. If I understood your question correctly you are working with only one datasource so you can achieve this without **MyConfig.java** class. As you specified the datasource params in application.properties, springboot creates a bean for you to play around. All you need to do is to autowire the JdbcTemplate object.. — harshavmb, Jan 02 '17 at 16:08
You have perfectly understood my question. I want to define my datasource param in my application.properties but now, how can I use it? I didn't found example of implementation without **MyConfig.java** on net.. — D.Frances, Jan 02 '17 at 16:14
Try this http://stackoverflow.com/questions/27697190/spring-boot-autoconfiguration-with-jdbc-template-autowiring-datasource-issue — harshavmb, Jan 02 '17 at 16:17

score 0 · Accepted Answer · answered Jan 02 '17 at 16:24

0

In spring boot you have two ways of defining a datasource, either you define it on the application.properties, or you do it programmatically. Unless you require some very specific configuration, or you have more than one datasource, you should define your datasource configuration on your application.properties so springboot will autoconfigure everything for you.

In the example you provided you are defining the datasource twice, you should remove the MvcConfig.java.

I recommend that you read this article about how to work with sql databases and spring boot Working with SQL databases

answered Jan 02 '17 at 16:24

IgnacioL

136
4

Thanks you a lot for your help. But if I remove code in **MvcConfig.java**, how my application will understand added code in **WebSecurityConfig.java**? – D.Frances Jan 02 '17 at 16:28
Springboot should instantiate a Datasource with the configuration you provided in the application.properties. You WebSecurityConfig should work as it is. – IgnacioL Jan 02 '17 at 16:34
I just checked your application.properties configuration. Make sure you are providing all the information spring needs to configure your datasource. At least, you will need to define this properties:spring.datasource.url, spring.datasource.username, spring.datasource.password, **spring.datasource.driver-class-name** – IgnacioL Jan 02 '17 at 16:41

score 0 · Answer 2 · answered Dec 23 '17 at 23:59

server.port = 8084  
#create  and drop tables and sequences, loads import.sql
spring.jpa.hibernate.ddl-auto=update

# MySQL settings
#spring.datasource.url=jdbc:Mysql://localhost:3306/simplehr
spring.datasource.url=jdbc:mysql://localhost:3306/myProject
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# HikariCP settings
 spring.datasource.hikari.*

spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=5

# logging
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - 
%msg%n
logging.level.org.hibernate.SQL=debug
#logging.level.org.hibernate.type.descriptor.sql=trace
logging.level.=error

spring.mvc.view.prefix:/WEB-INF/jsp/
spring.mvc.view.suffix:.jsp

Spring Boot, Spring security linked with database

2 Answers2