通过spring boot和java连接到2个用户的单个数据库

mjqavswn  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(213)

我有一个数据库名“dev”,我想连接2个用户,

user1: "root" , password1: "password", GRANTS: save, update, delete, read
user2: "testuser", password: "password", GRANTS: Read only

我在mysql数据库中指定了授权,而不是在spring boot或代码中。
如果am阻止更新,则仍允许其更新。
请给我一个数据库与多个不同的授权用户解决方案。
这是我的密码
我的application.properties是:

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/dev?useSSL=false
spring.datasource.username =root
spring.datasource.password =password

spring.datasource2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource2.url = jdbc:mysql://localhost:3306/dev?useSSL=false
spring.datasource2.username =testuser
spring.datasource2.password =password

application.java(主类):

@SpringBootApplication(scanBasePackages = {"com.ssss"})
@EnableTransactionManagement
@EnableAsync
@ComponentScan(basePackages = {"com.ssss"})
public class Application {
@Bean(name = "datasource")
    @ConfigurationProperties(prefix = "spring.datasource")
    @Primary
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "datasource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource2(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name="tm1")
    @Autowired
    @Primary
    DataSourceTransactionManager tm1(@Qualifier("datasource") DataSource datasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(dataSource());
        return txm;
    }

    @Bean(name="tm2")
    @Autowired
    DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(dataSource2());
        return txm;
    }
}

存储库类:

@Repository
public interface testRepository extends JpaRepository<testModel, Integer> {

    @Qualifier("datasource2")
    Optional<testModel> findBySbAndIsActiveTrueAndIsDeletedFalse( String sb );

    @Qualifier("datasource2")
    @Modifying
    @Query( value = "UPDATE testModel c SET c.buttonColor =:buttonColor" )
    Integer updateTestModelByFirmLogoPath( @Param( "buttonColor" ) String buttonColor );

服务实现类:

@Service("OpenApiService")
public class OpenApiServiceImpl implements OpenApiService {

    ......
    @Autowired
    private TestRepository testRepository;

    @Qualifier("datasource2")
    @Autowired
    private DataSource dataSource;

 //Fetching Authority
        @Override
        public TestBean getTestBean(String sb) throws Exception {

        try {
            Optional<TestModel> testModelOptional = cpaFirmRepository.findBySuAndIsActiveTrueAndIsDeletedFalse(sb);
            if(!TestModelOptional.isPresent()){
                throw Exception .......
            }

            TestModel testModel = testModelOptional.get();
            TestBean testBean= new TestBean();

            testBean.setPc(testModel.getPrimaryColor());
            testBean.setSc(testModel.getSecondaryColor());
            testBean.setBc(testModel.getButtonColor());
            testBean.setFl(testModel.getFirmLogoPath());

            return testBean;
        }catch (Exception e){
            L....error(ERROR, e);
            throw e;
        }
    }

//Updating Grant i blocked in the Mysql database
    @Transactional("tm2")
    @Override
    public String updateDB(String sb) throws Exception {

        try {

            Integer ss = testRepository.updateCpaFirmModelByFirmLogoPath("#005062");

            return "Updated success";
        }catch (Exception e){
            LOGGER.error(ERROR, e);
            throw e;
        }
    }
}

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题