8000 valiate jdbc generated keys when insert into on duplicate key update … · JavaCourse00/JavaCourseCodes@ccd1eb5 · GitHub
[go: up one dir, main page]
Skip to content

Commit ccd1eb5

Browse files
kimmkingkimmking
committed
valiate jdbc generated keys when insert into on duplicate key update statement
1 parent b0f2bc7 commit ccd1eb5

File tree

4 files changed

+167
-103
lines changed

4 files changed

+167
-103
lines changed

‎08cache/cache/src/main/java/io/kimmking/cache/CacheApplication.java‎

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,15 @@
11
package io.kimmking.cache;
22

33
import org.mybatis.spring.annotation.MapperScan;
4+
import org.springframework.beans.factory.annotation.Autowired;
5+
import org.springframework.boot.ApplicationRunner;
46
import org.springframework.boot.SpringApplication;
57
import org.springframework.boot.autoconfigure.SpringBootApplication;
68
import org.springframework.cache.annotation.EnableCaching;
9+
import org.springframework.context.annotation.Bean;
10+
11+
import javax.sql.DataSource;
12+
import java.sql.*;
713

814
@SpringBootApplication(scanBasePackages = "io.kimmking.cache")
915
@MapperScan("io.kimmking.cache.mapper")
@@ -14,4 +20,58 @@ public static void main(String[] args) {
1420
SpringApplication.run(CacheApplication.class, args);
1521
}
1622

23+
@Bean
24+
@Autowired
25+
public ApplicationRunner runner(DataSource dataSource) {
26+
return (x) -> {
27+
// testInsert(dataSource);
28+
// 测试 insert into on duplicate key update时如果没有执行更新条件，则回填id为空
29+
};
30+
}
31+
32+
private void testInsert(DataSource dataSource) throws SQLException {
33+
Connection connection = dataSource.getConnection();
34+
35+
System.out.println(" =====> test Insert ...");
36+
PreparedStatement statement = connection.prepareStatement("insert into user(name,age) values(?,20)", Statement.RETURN_GENERATED_KEYS);
37+
//boolean b = statement.execute("insert into user(name,age) values('K8',20)");
38+
//System.out.println("insert:" +b);
39+
// You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate(), Statement.executeLargeUpdate() or Connection.prepareStatement().
40+
statement.setString(1, "K8");
41+
boolean b = statement.execute();
42+
// System.out.println("insert:" +b);
43 8000 +
ResultSet rs = statement.getGeneratedKeys();
44+
System.out.println("insert getGeneratedKeys => " + (rs.next() ? rs.getInt(1): "NULL"));
45+
rs.close();
46+
statement.close();
47+
48+
49+
System.out.println(" =====> test Duplicate ...");
50+
statement = connection.prepareStatement("insert into user(name,age) values(?,20) on duplicate key update age=age+1", Statement.RETURN_GENERATED_KEYS);
51+
statement.setString(1, "K8");
52+
b = statement.execute();
53+
// System.out.println("insert:" +b);
54+
rs = statement.getGeneratedKeys();
55+
System.out.println("insert getGeneratedKeys => " + (rs.next() ? rs.getInt(1): "NULL"));
56+
rs.close();
57+
//statement.execute("delete from user where name='K8'");
58+
statement.close();
59+
60+
61+
System.out.println(" =====> test Duplicate No Update ...");
62+
statement = connection.prepareStatement("insert into user(name,age) values(?,20) on duplicate key update age=age", Statement.RETURN_GENERATED_KEYS);
63+
statement.setString(1, "K8");
64+
b = statement.execute();
65+
// System.out.println("insert:" +b);
66+
rs = statement.getGeneratedKeys();
67+
System.out.println("insert getGeneratedKeys => " + (rs.next() ? rs.getInt(1): "NULL"));
68+
rs.close();
69+
//statement.execute("delete from user where name='K8'");
70+
statement.close();
71+
72+
73+
connection.close();
74+
}
75+
76+
1777
}

‎08cache/cache/src/main/java/io/kimmking/cache/CacheConfig.java‎

Lines changed: 83 additions & 83 deletions
Original file line numberDiff line numberDiff line change
@@ -1,83 +1,83 @@
1-
package io.kimmking.cache;
2-
3-
import org.springframework.cache.CacheManager;
4-
import org.springframework.cache.annotation.CachingConfigurerSupport;
5-
import org.springframework.cache.interceptor.*;
6-
import org.springframework.context.annotation.Bean;
7-
import org.springframework.context.annotation.Configuration;
8-
import org.springframework.data.redis.cache.RedisCacheConfiguration;
9-
import org.springframework.data.redis.cache.RedisCacheManager;
10-
import org.springframework.data.redis.connection.RedisConnectionFactory;
11-
import org.springframework.data.redis.core.RedisTemplate;
12-
import org.springframework.data.redis.serializer.GenericJackson2JsonRedisSerializer;
13-
import org.springframework.data.redis.serializer.RedisSerializationContext;
14-
import org.springframework.data.redis.serializer.StringRedisSerializer;
15-
16-
import javax.annotation.Resource;
17-
18-
import static org.springframework.data.redis.cache.RedisCacheConfiguration.defaultCacheConfig;
19-
20-
@Configuration
21-
public class CacheConfig extends CachingConfigurerSupport {
22-
23-
@Resource
24-
private RedisConnectionFactory factory;
25-
26-
/**
27-
* 自定义生成redis-key
28-
*
29-
* @return
30-
*/
31-
@Override
32-
@Bean
33-
public KeyGenerator keyGenerator() {
34-
return (o, method, objects) -> {
35-
StringBuilder sb = new StringBuilder();
36-
sb.append(o.getClass().getName()).append(".");
37-
sb.append(method.getName()).append(".");
38-
for (Object obj : objects) {
39-
sb.append(obj.toString()).append(".");
40-
}
41-
//System.out.println("keyGenerator=" + sb.toString());
42-
return sb.toString();
43-
};
44-
}
45-
46-
@Bean
47-
public RedisTemplate<Object, Object> redisTemplate() {
48-
RedisTemplate<Object, Object> redisTemplate = new RedisTemplate<>();
49-
redisTemplate.setConnectionFactory(factory);
50-
51-
GenericJackson2JsonRedisSerializer genericJackson2JsonRedisSerializer = new GenericJackson2JsonRedisSerializer();
52-
53-
redisTemplate.setKeySerializer(genericJackson2JsonRedisSerializer);
54-
redisTemplate.setValueSerializer(genericJackson2JsonRedisSerializer);
55-
56-
redisTemplate.setHashKeySerializer(new StringRedisSerializer());
57-
redisTemplate.setHashValueSerializer(genericJackson2JsonRedisSerializer);
58-
return redisTemplate;
59-
}
60-
61-
@Bean
62-
@Override
63-
public CacheResolver cacheResolver() {
64-
return new SimpleCacheResolver(cacheManager());
65-
}
66-
67-
@Bean
68-
@Override
69-
public CacheErrorHandler errorHandler() {
70-
// 用于捕获从Cache中进行CRUD时的异常的回调处理器。
71-
return new SimpleCacheErrorHandler();
72-
}
73-
74-
@Bean
75-
@Override
76-
public CacheManager cacheManager() {
77-
RedisCacheConfiguration cacheConfiguration =
78-
defaultCacheConfig()
79-
.disableCachingNullValues()
80-
.serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(new GenericJackson2JsonRedisSerializer()));
81-
return RedisCacheManager.builder(factory).cacheDefaults(cacheConfiguration).build();
82-
}
83-
}
1+
//package io.kimmking.cache;
2+
//
3+
//import org.springframework.cache.CacheManager;
4+
//import org.springframework.cache.annotation.CachingConfigurerSupport;
5+
//import org.springframework.cache.interceptor.*;
6+
//import org.springframework.context.annotation.Bean;
7+
//import org.springframework.context.annotation.Configuration;
8+
//import org.springframework.data.redis.cache.RedisCacheConfiguration;
9+
//import org.springframework.data.redis.cache.RedisCacheManager;
10+
//import org.springframework.data.redis.connection.RedisConnectionFactory;
11+
//import org.springframework.data.redis.core.RedisTemplate;
12+
//import org.springframework.data.redis.serializer.GenericJackson2JsonRedisSerializer;
13+
//import org.springframework.data.redis.serializer.RedisSerializationContext;
14+
//import org.springframework.data.redis.serializer.StringRedisSerializer;
15+
//
16+
//import javax.annotation.Resource;
17+
//
18+
//import static org.springframework.data.redis.cache.RedisCacheConfiguration.defaultCacheConfig;
19+
//
20+
//@Configuration
21+
//public class CacheConfig extends CachingConfigurerSupport {
22+
//
23+
// @Resource
24+
// private RedisConnectionFactory factory;
25+
//
26+
// /**
27+
// * 自定义生成redis-key
28+
// *
29+
// * @return
30+
// */
31+
// @Override
32+
// @Bean
33+
// public KeyGenerator keyGenerator() {
34+
// return (o, method, objects) -> {
35+
// StringBuilder sb = new StringBuilder();
36+
// sb.append(o.getClass().getName()).append(".");
37+
// sb.append(method.getName()).append(".");
38+
// for (Object obj : objects) {
39+
// sb.append(obj.toString()).append(".");
40+
// }
41+
// //System.out.println("keyGenerator=" + sb.toString());
42+
// return sb.toString();
43+
// };
44+
// }
45+
//
46+
// @Bean
47+
// public RedisTemplate<Object, Object> redisTemplate() {
48+
// RedisTemplate<Object, Object> redisTemplate = new RedisTemplate<>();
49+
// redisTemplate.setConnectionFactory(factory);
50+
//
51+
// GenericJackson2JsonRedisSerializer genericJackson2JsonRedisSerializer = new GenericJackson2JsonRedisSerializer();
52+
//
53+
// redisTemplate.setKeySerializer(genericJackson2JsonRedisSerializer);
54+
// redisTemplate.setValueSerializer(genericJackson2JsonRedisSerializer);
55+
//
56+
// redisTemplate.setHashKeySerializer(new StringRedisSerializer());
57+
// redisTemplate.setHashValueSerializer(genericJackson2JsonRedisSerializer);
58+
// return redisTemplate;
59+
// }
60+
//
61+
// @Bean
62+
// @Override
63+
// public CacheResolver cacheResolver() {
64+
// return new SimpleCacheResolver(cacheManager());
65+
// }
66+
//
67+
// @Bean
68+
// @Override
69+
// public CacheErrorHandler errorHandler() {
70+
// // 用于捕获从Cache中进行CRUD时的异常的回调处理器。
71+
// return new SimpleCacheErrorHandler();
72+
// }
73+
//
74+
// @Bean
75+
// @Override
76+
// public CacheManager cacheManager() {
77+
// RedisCacheConfiguration cacheConfiguration =
78+
// defaultCacheConfig()
79+
// .disableCachingNullValues()
80+
// .serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(new GenericJackson2JsonRedisSerializer()));
81+
// return RedisCacheManager.builder(factory).cacheDefaults(cacheConfiguration).build();
82+
// }
83+
//}

‎08cache/cache/src/main/resources/application.yml‎

Lines changed: 21 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -8,25 +8,27 @@ spring:
88
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
99
driver-class-name: com.mysql.jdbc.Driver
1010
cache:
11-
type: redis
12-
redis:
13-
timeout: 3000ms
14-
database: 0
15-
cluster:
16-
nodes:
17-
- 127.0.0.1:7000
18-
- 127.0.0.1:7001
19-
- 127.0.0.1:7002
20-
- 127.0.0.1:7003
21-
- 127.0.0.1:7004
22-
- 127.0.0.1:7005
23-
max-redirects: 3 # 获取失败 最大重定向次数
24-
lettuce:
25-
pool:
26-
max-active: 1000 #连接池最大连接数（使用负值表示没有限制）
27-
max-idle: 10 # 连接池中的最大空闲连接
28-
min-idle: 5 # 连接池中的最小空闲连接
29-
max-wait: -1 # 连接池最大阻塞等待时间（使用负值表示没有限制）
11+
type: simple
12+
13+
# type: redis
14+
# redis:
15+
# timeout: 3000ms
16+
# database: 0
17+
# 4E22 cluster:
18+
# nodes:
19+
# - 127.0.0.1:7000
20+
# - 127.0.0.1:7001
21+
# - 127.0.0.1:7002
22+
# - 127.0.0.1:7003
23+
# - 127.0.0.1:7004
24+
# - 127.0.0.1:7005
25+
# max-redirects: 3 # 获取失败 最大重定向次数
26+
# lettuce:
27+
# pool:
28+
# max-active: 1000 #连接池最大连接数（使用负值表示没有限制）
29+
# max-idle: 10 # 连接池中的最大空闲连接
30+
# min-idle: 5 # 连接池中的最小空闲连接
31+
# max-wait: -1 # 连接池最大阻塞等待时间（使用负值表示没有限制）
3032

3133
# type: ehcache
3234
# ehcache:

‎08cache/cache/test.sql‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,9 @@ CREATE TABLE `user` (
2626
`id` int(11) NOT NULL AUTO_INCREMENT,
2727
`name` varchar(32) NOT NULL,
2828
`age` int(11) NOT NULL,
29-
PRIMARY KEY (`id`)
29+
PRIMARY KEY (`id`),
30+
constraint user_name_uindex
31+
unique (name)
3032
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
3133
/*!40101 SET character_set_client = @saved_cs_client */;
3234

0 commit comments

Comments
 (0)
0