From where my springboot app loads database? - Mysql

John_Doe
March 29, 2023
157 views
0 votes
2 Answers

I’m trying to link a project to a database

My application.properties file

spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false

Config file

@Configuration
@EnableTransactionManagement
public class DBConfig {

      @Bean
      public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/users");
        config.setUsername("root");
        config.setPassword("1111");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        

        return new HikariDataSource(config);
      }

      @Bean
      public LocalSessionFactoryBean sessionFactory() {
          LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
          sessionFactory.setDataSource(dataSource());
          sessionFactory.setPackagesToScan("com.example.database");
          sessionFactory.setHibernateProperties(hibernateProperties());; 
          return sessionFactory;
      }

      private Properties hibernateProperties() {
        Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        hibernateProperties.setProperty("hibernate.show_sql", "true");
        hibernateProperties.setProperty("hibernate.format_sql", "true");
        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", "update");

        return hibernateProperties;
      }

      @Bean
      public HibernateTransactionManager transactionManager() {
        HibernateTransactionManager transactionManager = new HibernateTransactionManager();
        transactionManager.setSessionFactory(sessionFactory().getObject());

        return transactionManager;
      }

}

Maven configuration

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>ToDoList</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>ToDoList</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
    </dependency>
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.14</version>
       </dependency>
         <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.5.7.Final</version>
    </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        <scope>runtime</scope>
</dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>
        <dependency>
            <groupId>org.example</groupId>
            <artifactId>HibernateApp</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

When I try to launch it, I get this

HHH000205: Loaded properties from resource hibernate.properties: {hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect, hibernate.driver_class=org.postgresql.Driver, hibernate.connection.password=****, hibernate.connection.username=postgres, hibernate.connection.url=jdbc:postgresql://localhost:5432/hibernate_demo_db, hibernate.bytecode.use_reflection_optimizer=false, hibernate.show_sql=true, hibernate.current_session_context_class=thread}
HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
HHH000342: Could not obtain connection to query metadata


java.sql.SQLException: Access denied for user 'postgres'@'localhost' (using password: YES)

From where this database "jdbc:postgresql://localhost:5432/hibernate_demo_db" appears? I don`t have any other configurations

Thanks in advance for any help

From where this database "jdbc:postgresql://localhost:5432/hibernate_demo_db" appears? I don`t have any other configurations

Thanks in advance for any help

Answers

I did configuration as below for my application and works seamlessly.

*MySqlConfiguration.java*

import javax.sql.DataSource;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


@Configuration
public class MySqlConfiguration {

private static final Logger logger = Logger.getLogger(MySqlConfiguration.class);

    @Bean(name = "mySQL")
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource dataSource() {
        final String METHOD_NAME = ":: DataSource ::";

        logger.info(METHOD_NAME + " - Initialising the MySQL Connection ::");
        
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.mysql.jpa")
    public JpaProperties jpaProperties() {
        JpaProperties properties = new JpaProperties();
        return properties;
    }

}

application.properties

#MySQL Datasource Configuration - Start --  Uses the Hikari CP as the Default Connection Pool

spring.mysql.datasource.jdbcUrl=jdbc:mysql://localhost:3306/serviceName
spring.mysql.datasource.hikari.jdbc-url=jdbc:mysql://localhost:3306/serviceName
spring.mysql.datasource.username=user
spring.mysql.datasource.password=password
spring.mysql.jpa.hibernate.ddl-auto=none
spring.mysql.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.mysql.datasource.poolName=MySQLPool
spring.mysql.datasource.maximumPoolSize=10
spring.mysql.datasource.minimumIdle=3
spring.mysql.datasource.maxLifetime=2000000
spring.mysql.datasource.connectionTimeout=30000
spring.mysql.datasource.idleTimeout=30000
spring.mysql.datasource.pool-prepared-statements=true
spring.mysql.datasource.max-open-prepared-statements=50

#hibernate/jpa config
spring.mysql.jpa.defer-datasource-initialization=true
spring.mysql.jpa.hibernate.use-new-id-generator-mappings=false
spring.mysql.jpa.hibernate.ddl-auto=none
spring.mysql.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

## For DB Batch Update/Inserts
# for logging purpose, to make sure it is working
spring.mysql.jpa.properties.hibernate.generate_statistics=true

spring.mysql.jpa.properties.hibernate.jdbc.batch_size=1000
spring.mysql.jpa.properties.hibernate.jdbc.fetch_size=2000
spring.mysql.jpa.properties.hibernate.order_inserts=true
spring.mysql.jpa.properties.hibernate.order_updates=true
spring.mysql.jpa.properties.hibernate.batch_versioned_data=true

# For tracing the Executed SQL Queries
spring.mysql.jpa.show-sql=true
spring.mysql.jpa.properties.hibernate.format_sql=true
spring.mysql.jpa.open-in-view=false
#MySQL Datasource Configuration - End

Moreover, You don’t need to add the below dependency:

   <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    <scope>runtime</scope>

Cheers!!

How can I see, you try to use Postgres(this configuration works for other db too), please try to add following dependencies and configuration:

<dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
</dependency>
<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

application.properties configuration (bpm is prefix):

spring.bpm.datasource.jdbc-url=jdbc:postgresql://localhost:5432/db_name
spring.bpm.datasource.username=user_name
spring.bpm.datasource.password=password
spring.bpm.datasource.driverClassName=org.postgresql.Driver
spring.bpm.database.platform=Postgre

Please don’t forget about configuration for JPA & Entity Manager

@Configuration
@EnableTransactionManagement
@EnableAutoConfiguration
@EnableJpaRepositories(
        entityManagerFactoryRef = "bpmEntityManager",
        transactionManagerRef = "bpmTransactionManager",
        basePackages = { path to scanning path, for example ->
                "kz.stack.repository"
        }
)
public class DatabaseConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.bpm.datasource")
    public DataSource bpmDataSource() {
        return DataSourceBuilder
                .create()
                .build();
    }
    @Primary
    @Bean(name = "bpmEntityManager")
    public LocalContainerEntityManagerFactoryBean bpmEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(bpmDataSource())
                .properties(hibernateProperties())
                .packages(path to scanning path model, for example ->
                "kz.stack.model"
                )
                .persistenceUnit("bpmPU")
                .build();
    }

    @Primary
    @Bean(name = "bpmTransactionManager")
    public PlatformTransactionManager bpmTransactionManager(@Qualifier("bpmEntityManager") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> hibernateProperties() {

        Resource resource = new ClassPathResource("hibernate.properties");
        try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
            return properties.entrySet().stream()
                    .collect(Collectors.toMap(
                            e -> e.getKey().toString(),
                            e -> e.getValue())
                    );
        } catch (IOException e) {
            return new HashMap<String, Object>();
        }
    }


}

Please signup or login to give your own answer.

Click here to cancel reply.

From where my springboot app loads database? – Mysql

Answers