java 当查询中的值为$时，通过flyway插入数据时引发错误

yrwegjxp  于 2023-11-15  发布在  Java
关注(0)|答案(2)|浏览(219)

当查询中的值为$时，通过flyway插入数据时引发错误
我得到一个错误，而插入数据到数据库时，每当它发现**"$"**在值字段。我使用flyway。但当我手动插入的值是插入罚款。
这是我在flyway中的插入查询：
v2_InsertData.sql

INSERT INTO property
         (application, profile, label, property_key, property_value)
     VALUES
         (‘app’, 'dev', 'v1', 'database.master.url',
'jdbc:mysql://${MYSQL_HOST_NAME:localhost}:${MYSQL_PORT:3306}/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true'
         );

字符串
要求：
我通过属性文件使用这些数据
Db.properties

database.master.url=jdbc:mysql://${MYSQL_HOST_NAME:localhost}:${MYSQL_PORT:3306}/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true

型
Db.config

@Configuration
@PropertySource(value = { "classpath:properties/db.properties" })
@Data
public class DBConfig {
    @Value("${database.master.url}")
    private String url;
}

型
现在我已经移动到Spring Cloud配置，加载正常。
所以我的db.config就变成了

@Configuration
@Data
public class DBConfig {
    @Value("${database.master.url}")
    private String url;
}

型
错误代码：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Unable to parse statement in db/migration/V4__Insert_Property.sql at line 1 col 1. See https://rd.gt/3ipi7Pm for more information: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:598) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:520) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:325) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:323) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:312) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1158) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:932) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:608) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-3.1.4.jar:3.1.4]
    at com.myApp.confingserver.ConfingServerApplication.main(ConfingServerApplication.java:12) ~[classes/:na]
Caused by: org.flywaydb.core.api.FlywayException: Unable to parse statement in db/migration/V4__Insert_Property.sql at line 1 col 1. See https://rd.gt/3ipi7Pm for more information: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:306) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.<init>(Parser.java:715) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.parse(Parser.java:130) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.parse(ParserSqlScript.java:72) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.validate(ParserSqlScript.java:120) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.executeInTransaction(ParserSqlScript.java:187) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.canExecuteInTransaction(SqlMigrationExecutor.java:73) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.isExecuteGroupInTransaction(DbMigrate.java:302) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:267) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:139) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.database.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:58) ~[flyway-mysql-9.16.3.jar:na]
    at org.flywaydb.database.mysql.MySQLConnection.lock(MySQLConnection.java:152) ~[flyway-mysql-9.16.3.jar:na]
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:144) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:139) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:97) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:188) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:196) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:140) ~[flyway-core-9.20.1.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-3.1.4.jar:3.1.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.12.jar:6.0.12]
    ... 18 common frames omitted
Caused by: org.flywaydb.core.api.FlywayException: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.flywaydb.core.internal.parser.PlaceholderReplacingReader.read(PlaceholderReplacingReader.java:165) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.PositionTrackingReader.read(PositionTrackingReader.java:33) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.RecordingReader.read(RecordingReader.java:33) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.PeekingReader.refillPeekBuffer(PeekingReader.java:73) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.PeekingReader.peek(PeekingReader.java:187) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.PeekingReader.peek(PeekingReader.java:169) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.readToken(Parser.java:477) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:175) ~[flyway-core-9.20.1.jar:na]
    ... 39 common frames omitted

型
现在的问题是我需要手动插入这个查询，这在批量文件的情况下是很棘手的。我想用flyway做。如何通过flyway插入这个值。

Java

2条答案

按热度按时间
j13ufse2

j13ufse21#

默认情况下，flyway会在执行查询时查找以前缀${开头的占位符。
如果flyway在查询中找到一个占位符，那么它会查找该占位符的值（如果存在）。
在您的情况下，错误是有效的，因为此占位符MYSQL_HOST_NAME:localhost没有值。

解决方案：

有两种方法可以接近：

*第一种方法：

将v2_InsertData.sql更新为：

INSERT INTO property (application, profile, label, property_key, 
property_value) 
VALUES (‘app’, 'dev', 'v1', 'database.master.url', 
CONCAT('jdbc:mysql://$','{MYSQL_HOST_NAME:localhost}', ':$', '{MYSQL_PORT:3306}', 
'/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true'));

字符串

**注意：**我在CONCAT()函数的帮助下对查询进行了一点修改，以绕过占位符验证。
*第二种方法：

通过application.properties告诉flyway跳过替换查询中找到的占位符：

spring.flyway.placeholder-replacement=false

型
屏幕截图证明它适用于两种方法：


的数据

注意：我建议你使用first approach，因为它是一个很好的安全选项。此外，除非需要，否则你不需要修改flyway的默认行为。

赞(0）分享  回复(0）举报  2023-11-15
j2cgzkjk

j2cgzkjk2#

flyway会“插入”$ things吗？
如果PHP是在封面下使用，那么这将是一个问题：

'...${MYSQL_PORT:3306}...'

字符串
但这不会：

"...${MYSQL_PORT:3306}..."

型
这是因为插值发生在双引号内，而不是单引号内。
看看双引号是否能解决这个问题。

赞(0）分享  回复(0）举报  2023-11-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com