You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/03/13 15:11:10 UTC
[GitHub] [incubator-shardingsphere] lennonn opened a new issue #4754: How do
I configure case insensitive for oracle
lennonn opened a new issue #4754: How do I configure case insensitive for oracle
URL: https://github.com/apache/incubator-shardingsphere/issues/4754
@Bean(name = "dataSource")
public DataSource dataSource() throws SQLException {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(jdbcDriver);
dataSource.setUrl(jdbcUrl);
dataSource.setUsername(jdbcUser);
dataSource.setPassword(jdbcPassword);
Properties props = new Properties();
props.setProperty("sql.show","true");
props.setProperty("query.with.cipher.column","true");
props.setProperty("check.table.metadata.enabled","true");
return EncryptDataSourceFactory.createDataSource(dataSource, getEncryptRuleConfiguration(), props);
}
private static EncryptRuleConfiguration getEncryptRuleConfiguration() {
Properties props = new Properties();
props.setProperty("aes.key.value", "123456");
EncryptorRuleConfiguration encryptorConfig = new EncryptorRuleConfiguration("AES", props);
EncryptColumnRuleConfiguration columnConfig = new EncryptColumnRuleConfiguration("", "CIPHER_NAME", "", "aes");
EncryptTableRuleConfiguration tableConfig = new EncryptTableRuleConfiguration(Collections.singletonMap("namee", columnConfig));
EncryptTableRuleConfiguration tableConfig1 = new EncryptTableRuleConfiguration(Collections.singletonMap("NAMEE", columnConfig));
EncryptRuleConfiguration encryptRuleConfig = new EncryptRuleConfiguration();
encryptRuleConfig.getEncryptors().put("aes", encryptorConfig);
encryptRuleConfig.getTables().put("TEST", tableConfig);
encryptRuleConfig.getTables().put("TEST", tableConfig1);
return encryptRuleConfig;
}
public void run(String... strings) throws Exception {
List<Object[]> splitUpNames = Arrays.asList("11 11", "12 12", "13 13", "14 14").stream()
.map(name -> name.split(" "))
.collect(Collectors.toList());
splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %d %s", Integer.parseInt((String) name[0]), name[1])));
// Uses JdbcTemplate's batchUpdate operation to bulk load data
jdbcTemplate.batchUpdate("INSERT INTO TEST(id, namee) VALUES (?,?)", splitUpNames);
//jdbcTemplate.update("INSERT INTO TEST(id, NAMEE) VALUES (2,'teeeee')");
List<Map<String, Object>> users = jdbcTemplate.queryForList("SELECT * FROM TEST");
System.out.println(users);
}
- my Table structure
### jdbcTemplate.batchUpdate("INSERT INTO TEST(id, NAMEE) VALUES (?,?)", splitUpNames);
this sql it works ,but The following not work
### jdbcTemplate.batchUpdate("INSERT INTO TEST(id, **namee**) VALUES (?,?)", splitUpNames);
java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:787) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
at shardingtest.Application.main(Application.java:27) [classes/:na]
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TEST(id, namee) VALUES (?,?)]; ORA-01722: 无效数字
; nested exception is java.sql.SQLSyntaxErrorException: ORA-01722: 无效数字
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:654) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:955) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:40) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1005) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1000) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at shardingtest.Application.run(Application.java:46) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: 无效数字
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:10096) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10200) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216) ~[ojdbc6.jar:11.2.0.2.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.executeBatch(EncryptPreparedStatement.java:198) ~[sharding-jdbc-core-4.0.1.jar:4.0.1]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:971) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:955) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
... 12 common frames omitted
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:654) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:955) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:40) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1005) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1000) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at shardingtest.Application.run(Application.java:46) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.4.RELEASE.jar:2.2.4.RELEASE]
... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: 无效数字
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:10096) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10200) ~[ojdbc6.jar:11.2.0.2.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216) ~[ojdbc6.jar:11.2.0.2.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.executeBatch(EncryptPreparedStatement.java:198) ~[sharding-jdbc-core-4.0.1.jar:4.0.1]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:971) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:955) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.3.26.RELEASE.jar:4.3.26.RELEASE]
... 12 common frames omitted`
If i want to use both uppercase and lowercase,What should I do?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #4754: How
do I configure case insensitive for oracle
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4754: How do I configure case insensitive for oracle
URL: https://github.com/apache/incubator-shardingsphere/issues/4754#issuecomment-608372238
Closed until somebody interesting about this issue
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #4754: How
do I configure case insensitive for oracle
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4754: How do I configure case insensitive for oracle
URL: https://github.com/apache/incubator-shardingsphere/issues/4754#issuecomment-598821738
The feature of case insensitive is not implement yet and we have no plan to do this.
I just open this feature request for some time, if anybody interest about it, please discuss here.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4754: How do I
configure case insensitive for oracle
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4754: How do I configure case insensitive for oracle
URL: https://github.com/apache/incubator-shardingsphere/issues/4754
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services