Database is not accessing by the project done in Spring Boot

Question

I am new to spring boot and I want to connect my database with jdbc to spring boot project. It is succeeding only for one database and when I change the name of database to another it won't work. In my project I have done only connecting database to spring boot. So I think it should work when database name change. I am grateful if someone can help me with my problem.

ERROR

Access denied for user ''@'localhost' to database 'daddycoffeeshop_db'.
and
Error creating bean with name 'jdbcConverter' defined in class path resource

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/daddycoffeeshop_db
spring.datasource.username=root
spring.datasource.password=

Here daddycoffeeshop_db is not working but test database is working. Why is it?

Other codes... DaddyCoffeeShoApplication.java

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DaddyCoffeeeShopApplication {

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

}

TestData.java

package com.example.demo;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class TestData implements CommandLineRunner {
    
    @Autowired
    private JdbcTemplate jt;
    
    @Autowired
    private DataSource ds;

    @Override
    public void run(String... args) throws Exception {
        String sql = "INSERT INTO RODUCT_DETAILS VALUES(?,?,?,?)" ;
        int count = jt.update(sql, 10, "A", 234.45,"this is babysoap");
        System.out.print("Inserted Rows" + count);
    }
    

}

Does that database have the same login credentials as the test database? Or does the user you're using to access the SQL database with have privileges to access the database? — Jaeheon Shim, Dec 04 '20 at 06:15
Have you checked "daddycoffeeshop_db" database is there in your local? — priyranjan, Dec 04 '20 at 06:20
@JaeheonShim I am using same login credentials to all databases I created. — Kamalka Fernando, Dec 04 '20 at 06:40
Your properties are wrong. `spring.datasource.data-username` is wrong should be `spring.datasource.username` the same for the password. Also remove the `"` unless that is really part of your user name. Also your password is a single whitespace which I doubt is the actual password. — M. Deinum, Dec 04 '20 at 08:17
@M.Deinum sorry about that. I was thinking to change but I forgot. Even though it is corrected, It is coming an error **Failed to execute CommandLineunner** — Kamalka Fernando, Dec 04 '20 at 08:26
The error clearly states you are connecting **without** a user, hence your properties are wrong to start with. `Access denied for user ''@'localhost' to database 'daddycoffeeshop_db'.` contains no user name. If it would it would be `root@localhost`.and I doubt your password is empty. — M. Deinum, Dec 04 '20 at 08:27

score 1 · Accepted Answer · answered Dec 04 '20 at 06:45

1

I think your problem is authority.

Please delegate DB role for 'daddycopeeshop_db' to new account instead of root account.

You also need to open up your access to the outside world.

answered Dec 04 '20 at 06:45

youdozi

114
5

Hello @youdozi. How can I do that? Can you provide any reference I can check on? – Kamalka Fernando Dec 04 '20 at 07:01
1. MySql console access. 2. create user '{user}'@'%' identified by '{passwrod}'; -> Please enter {user} and {password}. 3. grant all privileges on daddycoffeeshop_db.* to '{user}@'%'; 4. check it. – youdozi Dec 04 '20 at 07:32

score 1 · Answer 2 · answered Dec 04 '20 at 07:30

1

Please try the below:

GRANT ALL PRIVILEGES ON . TO 'user'@'localhost' WITH GRANT OPTION;

This can be accomplished with MySQL workbench as well.

answered Dec 04 '20 at 07:30

Keerthi Kumar N

34
4

score 1 · Answer 3 · answered Dec 04 '20 at 08:46

Follow the solution steps mentioned below one of them may solve your issue :

Solution 1 :

The first option is to use the command below to attempt login with username and correct password. This command will prompt you to enter your password. Enter the right password. Mysql will validate the username and password. If both are matching with the existing system, then mysql will allow to login. The error “Access denied for user ‘root’@’localhost'” is thrown due to the invalid password.

$>mysql -u root -p Enter password:

Solution 2 :

If you have forgotten the root user, the following steps will help you to update your root password. The root user will be modified in PASSWORD() function with the provided password. The password is set as “password” in the example below. This step will allow to reset the root user password.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> update mysql.user set password=PASSWORD("password") where User='root';

mysql> flush privileges;

Solution 3 :

By default, the root user will not have a plugin attached. Update the root user with the plugin ‘ mysql native password ‘ This plugin ‘ mysql native password ‘ allows native authentication. For any command, the error “Access denied for user ‘root’@’localhost'” is shown if the mysql native password plugin is not set to the root user.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> update mysql.user set plugin = 'mysql_native_password' where User='root';

mysql> flush privileges;

$> mysql -u root mysql

mysql> update mysql.user set password=PASSWORD("password") where User='root';

mysql> flush privileges;

Solution 4 :

The password of the root user and the plugin must be changed. The alter command will change the password of the root user and update the plugin as “mysql native password.” The plugin stores a password in a native format and allows native authentication.

$> mysqld --skip-grant-tables

$> mysql -u root mysql

mysql> ALTER mysql.user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> exit

start mysql again

Solution 5 :

The root user has the admin privileges. Provide all permissions for the root user to connect from the local system. The grant command will add the privileges to the root command. If you want to revert the privilege, use the ‘revoke’ command. The ‘revoke’ command will remove the privileges from the user.

 mysql> CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
        or
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;

mysql> FLUSH PRIVILEGES;

Database is not accessing by the project done in Spring Boot

3 Answers3