java：JDBC ResultSet结合Spring的TransactionTemplate事务模板的查询方式

java：JDBC ResultSet结合Spring的TransactionTemplate事务模板的查询方式

1 前言

一般业务开发中，数据库查询使用mybatis框架居多。mybatis底层将结果赋予到实体类，使用的是反射方式（如org.apache.ibatis.reflection.Reflector类等逻辑），常和Spring的编程式事务TransactionTemplate一同使用。

当然，Spring的TransactionTemplate也可以和JDBC的ResultSet联合使用，这里采用根据Spring asm所创的工具类，来将数据库查询结果赋予到实体类（和mybatis底层使用反射有所区别，反射调用实体类的getter和setter方法，效率上较之字节码直接调用getter或setter方法会差些）。

Spring asm使用及部分源码分析，可参考如下文章：

asm实现ResultSet结果映射到实体类

2 使用

使用如下（mysql默认事务隔离为可重复读，可能出现幻读，但相比于事务隔离的序列化，吞吐量较好）：

import AbstractTest.AbstractBaseTest;
import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.ConnectionHolder;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.Assert;
import org.springframework.util.StopWatch;
import trans.TransitionBean;

import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;


public class TestTransition extends AbstractBaseTest {

    private DruidDataSource dataSource;

    @Before
    public void before() {
        dataSource = new DruidDataSource();
        dataSource.setInitialSize(2);
        dataSource.setMinIdle(0);
        dataSource.setMaxActive(4);

        dataSource.setMaxWait(5000);
        dataSource.setMinEvictableIdleTimeMillis(1000L * 60 * 5);
        dataSource.setMaxEvictableIdleTimeMillis(1000L * 60 * 120);

        dataSource.setUrl("jdbc:mysql://localhost:3306/xiaoxu?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC");
        dataSource.setUsername("root");
        dataSource.setPassword("******");
    }

    @Test
    public void testQueryWithTransaction() {

        boolean isTx = true;
        if (isTx) {
            PlatformTransactionManager txManager = new DataSourceTransactionManager(this.dataSource);
            TransactionTemplate txTemplate = new TransactionTemplate(txManager);
            // mysql 默认事务隔离机制，可重复读
            txTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);

            txTemplate.execute(new TransactionCallbackWithoutResult() {
                @Override
                protected void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
                    if (TransactionSynchronizationManager.isSynchronizationActive()) {
                        ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(TestTransition.this.dataSource);
                        Objects.requireNonNull(holder, "Holder could not be null.");
                        // Connection复用连接池中的，不使用try-with-resources auto close
                        Connection conn = holder.getConnection();
                        Assert.notNull(conn, () -> "Connection could not be null.");
                        try (PreparedStatement preparedStatement = conn.prepareStatement("select * from my_people");) {
                            ResultSet resultSet = preparedStatement.executeQuery();

                            System.getProperties().put("cglib.debugLocation", "src/test/java/trans_asm/printer");

                            StopWatch stopWatch = new StopWatch("query time");
                            stopWatch.start();
                            TransitionBean transitionBean = TransitionBean.create(UserModel.class, resultSet);
                            List<UserModel> userModelList = new ArrayList<>();
                            transitionBean.transition(userModelList, resultSet);
                            stopWatch.stop();
                            System.out.println("结束时间：" + stopWatch.getTotalTimeMillis() + "ms.");
                            userModelList.forEach(System.out::println);
                        } catch (SQLException sqlExp) {
                            throw new IllegalStateException(MessageFormat.format(
                            "unknown sql error occurred, state is :{0}, msg is {1}.", 
                            sqlExp.getSQLState(), sqlExp.getMessage()));
                        } catch (Exception | Error ex) {
                            transactionStatus.setRollbackOnly();
                            throw ex;
                        }
                    }

                }
            });
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

上述ResultSet亦可使用try-with-resources。

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MainApplication.class)
public class AbstractBaseTest {
}
1
2
3
4

实体类UserModel（TransitionBean默认将实体类的小驼峰字段名和数据库表下划线字段名比较，一致且字段类型符合预期才会赋值，所以实体类字段名称必须按照小驼峰写法，表字段是下划线写法。区别于mybatis，mybatis是从caseInsensitivePropertyMap中获取，大小写不敏感，该map的key是大写，且配置useCamelCaseMapping为true，会去掉下划线再将值转换为大写后再从caseInsensitivePropertyMap中获取）：

mybatis源码片段：

public String findProperty(String name, boolean useCamelCaseMapping) {
    if (useCamelCaseMapping) {
        name = name.replace("_", "");
    }

    return this.findProperty(name);
}
1
2
3
4
5
6
7

实体类：

@Data
@ToString
public class UserModel {
    String myName;
    long id;
    int myAge;
    BigDecimal moneyMe;
    Date birthday;
}
1
2
3
4
5
6
7
8
9

对应的数据库表DDL及数据：

TransitionBean：

package trans;

import com.mysql.cj.jdbc.result.ResultSetMetaData;
import com.mysql.cj.result.Field;
import org.springframework.asm.ClassVisitor;
import org.springframework.asm.Label;
import org.springframework.asm.Opcodes;
import org.springframework.asm.Type;
import org.springframework.cglib.core.*;
import org.springframework.util.Assert;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Modifier;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.IntFunction;

/**
 * @author xiaoxu
 * @date 2023-09-11
 * spring_boot:trans.TransitionBean
 */
@SuppressWarnings("all")
public abstract class TransitionBean {

    private static final String underLineMark = "_";
    private static final boolean treatYearAsData = Boolean.parseBoolean(System.getProperty("treatYearToDate", "true"));
    private static final Map<String, Class<?>> typeMapping;

    private static final TransitionBeanKey KEY_FACTORY = (TransitionBeanKey) KeyFactory.create(TransitionBean.TransitionBeanKey.class);
    private static final Type TRANSITION_BEAN = TypeUtils.parseType("trans.TransitionBean");
    private static final Type RESULT_SET = TypeUtils.parseType("java.sql.ResultSet");
    private static final Type LIST = TypeUtils.parseType("java.util.List");

    private static final Signature TRANSITION;
    private static final Signature FETCH_METADATA;
    private static final Signature NEXT;
    private static final Signature GET_OBJECT;
    private static final Signature ADD_ELEMENT;

    private static final Map<Class<?>, Class<?>> primitiveTypeToWrapperMap = new IdentityHashMap<>(9);

    public TransitionBean() {
    }

    static {
        typeMapping = new HashMap<>();
        TRANSITION = new Signature("transition", Type.VOID_TYPE, new Type[]{Constants.TYPE_OBJECT, Constants.TYPE_OBJECT});
        FETCH_METADATA = TypeUtils.parseSignature("java.sql.ResultSetMetaData getMetaData()");
        NEXT = new Signature("next", Type.BOOLEAN_TYPE, new Type[0]);
        GET_OBJECT = TypeUtils.parseSignature("Object getObject(int)");
        ADD_ELEMENT = TypeUtils.parseSignature("boolean add(Object)");
        staticPrimHook();
    }

    private static void staticPrimHook() {
        // Map entry iteration is less expensive to initialize than forEach with lambdas
        primitiveTypeToWrapperMap.put(boolean.class, Boolean.class);
        primitiveTypeToWrapperMap.put(byte.class, Byte.class);
        primitiveTypeToWrapperMap.put(char.class, Character.class);
        primitiveTypeToWrapperMap.put(double.class, Double.class);
        primitiveTypeToWrapperMap.put(float.class, Float.class);
        primitiveTypeToWrapperMap.put(int.class, Integer.class);
        primitiveTypeToWrapperMap.put(long.class, Long.class);
        primitiveTypeToWrapperMap.put(short.class, Short.class);
        primitiveTypeToWrapperMap.put(void.class, Void.class);
    }

    public abstract void transition(Object var1, Object var2);

    public static TransitionBean create(Class targetEntityType, ResultSet result) {
        TransitionBean.Generator gen = new TransitionBean.Generator();
        gen.setTargetType(targetEntityType);
        gen.setFieldMetaSet(TransitionBean.convertFieldMetaSet(result));
        return gen.create();
    }

    public static class PlusClassEmitter extends ClassEmitter {
        private Map fieldMetaInfo;
        private static int metaHookCounter;

        public PlusClassEmitter() {
            super();
        }

        public PlusClassEmitter(ClassVisitor cv) {
            super(cv);
        }

        private boolean isFieldMetaDeclared(String name) {
            return this.fieldMetaInfo.get(name) != null;
        }

        private static synchronized int getNextMetaHook() {
            return ++metaHookCounter;
        }

        private PlusClassEmitter.FieldMetaInfo getFieldMetaInfo(String name) {
            PlusClassEmitter.FieldMetaInfo fieldMeta = (PlusClassEmitter.FieldMetaInfo) this.fieldMetaInfo.get(name);
            if (fieldMeta == null) {
                throw new IllegalArgumentException("Field Meta " + name + " is not declared in " + this.getClassType().getClassName());
            } else {
                return fieldMeta;
            }
        }

        public void declare_meta_field(int access, String name, Type type, Object value) {
            PlusClassEmitter.FieldMetaInfo existing = (PlusClassEmitter.FieldMetaInfo) this.fieldMetaInfo.get(name);
            PlusClassEmitter.FieldMetaInfo metaInfo = new PlusClassEmitter.FieldMetaInfo(access, name, type, value);
            if (existing != null) {
                if (!metaInfo.equals(existing)) {
                    throw new IllegalArgumentException("Field Meta\"" + name + "\" has been declared differently");
                }
            } else {
                this.fieldMetaInfo.put(name, metaInfo);
                this.cv.visitField(access, name, type.getDescriptor(), (String) null, value);
            }
        }

        public void end_class() {
            super.end_class();

        }

        private void end_m(CodeEmitter e) {
            if (e != null) {
                e.return_value();
                e.end_method();
            }
        }

        public static class FieldMetaInfo {
            int access;
            String name;
            Type type;
            Object value;

            public FieldMetaInfo(int access, String name, Type type, Object value) {
                this.access = access;
                this.name = name;
                this.type = type;
                this.value = value;
            }

            public boolean equals(Object o) {
                if (o == null) {
                    return false;
                } else if (!(o instanceof PlusClassEmitter.FieldMetaInfo)) {
                    return false;
                } else {
                    PlusClassEmitter.FieldMetaInfo other = (PlusClassEmitter.FieldMetaInfo) o;
                    if (this.access == other.access && this.name.equals(other.name) && this.type.equals(other.type)) {
                        if (this.value == null ^ other.value == null) {
                            return false;
                        } else {
                            return this.value == null || this.value.equals(other.value);
                        }
                    } else {
                        return false;
                    }
                }
            }

            public int hashCode() {
                return this.access ^ this.name.hashCode() ^ this.type.hashCode() ^ (this.value == null ? 0 : this.value.hashCode());
            }

        }

    }

    public static class Generator extends AbstractClassGenerator {

        private static final Source SOURCE = new Source(TransitionBean.class.getCanonicalName());

        private Class<?> targetType;
        private FieldMetaSet fieldMetaSet;
        private ClassLoader contextLoader;

        protected Generator() {
            super(SOURCE);
            this.setNamingPolicy(TransitionBeanNamingPolicy.INSTANCE);
            this.setNamePrefix(TransitionBean.class.getName());
            this.contextLoader = TransitionBean.class.getClassLoader();
        }

        public void setTargetType(Class<?> targetType) {
            Objects.requireNonNull(targetType, () -> "target type do not allow null.");
            if (!Modifier.isPublic(targetType.getModifiers())) {
                this.setNamePrefix(targetType.getName());
            }

            this.targetType = targetType;
        }

        public void setFieldMetaSet(FieldMetaSet fieldMetaSet) {
            Assert.notNull(fieldMetaSet, () -> "fieldMetaSet access null");
            Assert.notEmpty(fieldMetaSet.getFieldMetas(), () -> "fields meta should not be empty.");
            this.fieldMetaSet = fieldMetaSet;
        }

        public TransitionBean create() {
            Object key = TransitionBean.KEY_FACTORY.newInstance(this.targetType.getName(), this.fieldMetaSet);
            return (TransitionBean) super.create(key);
        }

        @Override
        protected ClassLoader getDefaultClassLoader() {
            return this.targetType.getClassLoader();
        }

        @Override
        protected Object firstInstance(Class type) throws Exception {
            return ReflectUtils.newInstance(type);
        }

        @Override
        protected Object nextInstance(Object instance) throws Exception {
            return instance;
        }

        private boolean nullSafeEquals(String var1, String var2) {
            // both null also is wrong
            return var1 != null && var1.equals(var2);
        }

        public boolean isCompatible(Class<?> clazz, PropertyDescriptor setter) {
            if (clazz.isPrimitive())
                throw new IllegalStateException(clazz.getCanonicalName() + " clazz is primitive type here, it is wrong.");
            Class<?> propertyType = setter.getPropertyType();
            if (propertyType.isPrimitive()) {
                Class<?> wrapperType = primitiveTypeToWrapperMap.get(propertyType);
                return clazz.equals(wrapperType);
            }
            return propertyType.isAssignableFrom(clazz);
        }

        @Override
        public void generateClass(ClassVisitor v) throws Exception {
            ClassEmitter ce = new TransitionBean.PlusClassEmitter(v);
            ce.begin_class(52, 1, this.getClassName(), TransitionBean.TRANSITION_BEAN, (Type[]) null, "TransitionBean.java");
            EmitUtils.null_constructor(ce);
            CodeEmitter e = ce.begin_method(1, TransitionBean.TRANSITION, (Type[]) null);

            Local rsList = e.make_local();
            Local rs = e.make_local();
            e.load_arg(1);
            e.checkcast(RESULT_SET);
            e.store_local(rs);

            e.load_arg(0);
            e.checkcast(LIST);
            e.store_local(rsList);

            TransitionBean.nonNull(e, rsList, "var1 could not be null.");
            TransitionBean.nonNull(e, rs, "var2 could not be null.");

            PropertyDescriptor[] setters = ReflectUtils.getBeanSetters(this.targetType);

            Map names = new HashMap();

            for (int i = 0; i < setters.length; ++i) {
                names.put(setters[i].getName(), setters[i]);
            }

            // for loop is less expensive than forEach lambda
            FieldMeta[] fieldMetas = this.fieldMetaSet.getFieldMetas();

            TransitionBean.for_loop(this, e, new TransitionCallBack() {
                @Override
                public void tansitionTo(CodeEmitter e) {
                    Local instance = Generator.this.newInstance(e);

                    for (int i = 0; i < fieldMetas.length; i++) {
                        FieldMeta fieldMeta = fieldMetas[i];
                        PropertyDescriptor prop = (PropertyDescriptor) names.get(
                                TransitionBean.underlineTransferSmallHump(fieldMeta.getColumnName())
                        );
                        if (prop != null) {
                            Class typeClass = typeMapping.computeIfAbsent(fieldMeta.getClassName(), (name) -> {
                                try {
                                    return Class.forName(name, true, Generator.this.contextLoader);
                                } catch (ClassNotFoundException notFoundException) {
                                    throw new IllegalStateException("Class could not found:" + name);
                                }
                            });
                            if (Generator.this.isCompatible(typeClass, prop)) {
                                int index = Generator.this.fieldMetaSet.indexOf(fieldMeta);
                                if (index == -1) {
                                    throw new IllegalStateException("Out of index of field:" + fieldMeta);
                                }
                                MethodInfo write = ReflectUtils.getMethodInfo(prop.getWriteMethod());
                                Type setterType = write.getSignature().getArgumentTypes()[0];
                                e.load_local(instance);
                                e.load_local(rs);
                                e.push(++index);
                                e.invoke_interface(RESULT_SET, GET_OBJECT);
                                e.unbox_or_zero(setterType);
                                e.invoke(write);
                            }
                        }
                    }

                    e.load_local(rsList);
                    e.load_local(instance);
                    e.invoke_interface(LIST, ADD_ELEMENT);
                    e.pop();
                }
            }, rs);

        }

        private Local newInstance(CodeEmitter e) {
            Type type = Type.getType(Generator.this.targetType);
            e.new_instance(type);
            e.dup();
            e.invoke_constructor(type);

            Local local = e.make_local();
            e.store_local(local);
            return local;
        }

        private interface TransitionCallBack {
            void tansitionTo(CodeEmitter e);
        }

        private interface ResultProcessCallBack {
            /**
             * @param e              {@link org.springframework.cglib.core.CodeEmitter}
             * @param transitionCall
             */
            public void loop_around(CodeEmitter e, Generator.TransitionCallBack transitionCall);

            /**
             * @param e codeEmitter {@link org.springframework.cglib.core.CodeEmitter}
             */
            void loop_end(CodeEmitter e);

            static void process(ResultProcessCallBack processCallBack, CodeEmitter e, Generator.TransitionCallBack c) {
                if (processCallBack == null)
                    throw new NullPointerException("ResultProcessCallBack null");
                processCallBack.loop_around(e, c);
                processCallBack.loop_end(e);
            }
        }
    }

    private static void nonNull(CodeEmitter e, Local local, String errorMsg) {
        e.load_local(local);
        e.dup();
        Label end = e.make_label();
        e.ifnonnull(end);
        e.throw_exception(Type.getType(NullPointerException.class), errorMsg);
        e.goTo(end);
        e.mark(end);
    }

    static class TransitionBeanNamingPolicy extends DefaultNamingPolicy {
        public static final TransitionBeanNamingPolicy INSTANCE = new TransitionBeanNamingPolicy();

        @Override
        protected String getTag() {
            return "ByXiaoXu";
        }
    }

    interface TransitionBeanKey {
        Object newInstance(String var1, FieldMetaSet var2);
    }

    public static FieldMetaSet convertFieldMetaSet(ResultSet resultSetImpl) {
        Objects.requireNonNull(resultSetImpl, () -> "resultSetImpl is null.");

        try {
            // mysql-connector-java:8.0.26 support
            if (resultSetImpl.getMetaData() instanceof ResultSetMetaData) {
                ResultSetMetaData resultSetImplData = (ResultSetMetaData) resultSetImpl.getMetaData();
                Field[] fields = resultSetImplData.getFields();
                FieldMeta[] fieldMetas = Arrays.stream(fields).map(f -> {
                    FieldMeta fieldMeta = new FieldMeta();
                    String originalName = f.getOriginalName();
                    fieldMeta.setColumnName(originalName == null ? f.getName() : originalName);

                    String className;
                    switch (f.getMysqlType()) {
                        case YEAR:
                            if (!treatYearAsData) {
                                className = Short.class.getName();
                                break;
                            }
                            className = f.getMysqlType().getClassName();
                            break;
                        default:
                            className = f.getMysqlType().getClassName();
                            break;
                    }
                    fieldMeta.setClassName(className);
                    return fieldMeta;
                }).toArray(new IntFunction<FieldMeta[]>() {
                    @Override
                    public FieldMeta[] apply(int value) {
                        return new FieldMeta[value];
                    }
                });

                FieldMetaSet fieldMetaSet = new FieldMetaSet();
                fieldMetaSet.setFieldMetas(fieldMetas);
                return fieldMetaSet;
            }

            throw new IllegalStateException("could not access fieldMetaSet.");
        } catch (SQLException sqlError) {
            throw new IllegalStateException(sqlError);
        }
    }

    private static void for_loop(TransitionBean.Generator generator, CodeEmitter e, Generator.TransitionCallBack c, Local result$) {
        final Generator.ResultProcessCallBack processor = new Generator.ResultProcessCallBack() {
            @Override
            public void loop_around(CodeEmitter e, Generator.TransitionCallBack transitionCall) {
                // forEach
                Label hasNext = e.make_label();
                e.mark(hasNext);

                e.load_local(result$);
                e.invoke_interface(RESULT_SET, NEXT);
                Label end = e.make_label();
                e.if_jump(Opcodes.IFEQ, end);

                transitionCall.tansitionTo(e);

                e.goTo(hasNext);
                e.mark(end);
            }

            @Override
            public void loop_end(CodeEmitter e) {
                e.return_value();
                e.end_method();
            }
        };

        Generator.ResultProcessCallBack.process(processor, e, c);
    }

    /**
     * @param e      codeEmitter
     * @param loader operand stack action call back
     * @see System.out
     * @see java.io.PrintStream#println(String)
     */
    private static void debugPrinter(CodeEmitter e, Load loader) {
        e.getstatic(TypeUtils.parseType("System"), "out", TypeUtils.parseType("java.io.PrintStream"));
        loader.pushOperandStack(e);
        e.invoke_virtual(TypeUtils.parseType("java.io.PrintStream"), TypeUtils.parseSignature("void println(Object)"));
    }

    private interface Load {
        void pushOperandStack(CodeEmitter e);
    }

    /**
     * @param name 下划线
     * @return 小驼峰
     */
    public static String underlineTransferSmallHump(String name) {
        return symbolTransferSmallCamel(name, underLineMark.toCharArray()[0]);
    }

    public static boolean nonEmptyContains(String str1, String str2) {
        return str1.contains(str2);
    }

    @SuppressWarnings("all")
    public static String symbolTransferSmallCamel(String name, Character symbol) {
        if (null == symbol) {
            throw new RuntimeException("symbol access empty");
        }

        if (name == null)
            throw new NullPointerException("name null");

        if (nonEmptyContains(name, symbol.toString())) {
            CharSequence cs = name;
            int i = 0, csLen = cs.length();
            StringBuilder sbd = new StringBuilder(csLen);
            boolean isUpper = false;

            for (; i < csLen; ++i) {
                char c;
                if (i == 0 && Character.isUpperCase(c = cs.charAt(i))) {
                    sbd.append(Character.toLowerCase(c));
                    continue;
                }

                c = cs.charAt(i);
                if (c == symbol) {

                    isUpper = true;

                } else if (isUpper) {

                    if (sbd.length() == 0) {
                        sbd.append(Character.toLowerCase(c));
                    } else {
                        sbd.append(Character.toUpperCase(c));
                    }
                    isUpper = false;
                } else {
                    sbd.append(c);
                }
            }

            return sbd.toString();
        } else {
            int strLen;
            return (strLen = name.length()) > 1
                    ? name.substring(0, 1).toLowerCase() + name.substring(1, strLen)
                    : name.toLowerCase();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524

执行单测结果如下：

因上述生成的字节码Class类，存于Spring的LoadingCache的缓存map中，故而后续多次执行该实体类和对应的表查询时，不会反复生成字节码Class，直接从缓存map中获取执行，会显著提升查询结果返回的效率，性能优于反射。

最后，我们知道mysql事务中查询，常与for update一同使用，简单来说，如果select … for update查询使用索引，则锁行，否则锁表，一般分布式锁可以采用mysql此种方式实现，因为for update在事务中使用时（注意是for update或for update nowait和事务一同使用，for update是阻塞式执行方式，即按顺序执行，后续执行等待前面释放锁；而for update nowait是不等待执行，即当前数据被锁，别的事务查询需要使用该数据，那么mysql直接返回报错信息，不会阻塞等待事务提交锁的释放），直到事务提交，才会释放锁。mysql锁行（锁表影响性能，这种方式查询需要有索引）可以避免其他请求操作了已经被处理过（或者处理中）的数据（比如接口可能存在高并发请求，该请求会根据数据状态，落其它数据或者更新数据等，那么此时锁行，可以保证其它数据无法操作该数据，查询并更新常见此处理方式）。