APIJSON/src/main/java/apijson/orm/AbstractSQLExecutor.java at main · APIJSON/APIJSON · GitHub

History

executable file

2177 lines (1904 loc) · 80.9 KB

Raw

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

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

This source code is licensed under the Apache License Version 2.0.*/

package apijson.orm;

import apijson.*;

import apijson.orm.Join.On;

import apijson.orm.exception.NotExistException;

import java.io.Reader;

import java.lang.reflect.Method;

import java.math.BigDecimal;

import java.math.BigInteger;

import java.nio.charset.StandardCharsets;

import java.sql.*;

import java.time.DayOfWeek;

import java.time.LocalDateTime;

import java.time.Month;

import java.time.Year;

import java.time.temporal.TemporalAccessor;

import java.util.Date;

import java.util.*;

import java.util.Map.Entry;

import java.util.regex.Pattern;

/**executor for query(read) or update(write) MySQL database

* @author Lemon

public abstract class AbstractSQLExecutor<T, M extends Map<String, Object>, L extends List<Object>>

implements SQLExecutor<T, M, L> {

private static final String TAG = "AbstractSQLExecutor";

//是否返回值为null的字段

public static boolean ENABLE_OUTPUT_NULL_COLUMN = false;

public static String KEY_RAW_LIST = "@RAW@LIST"; // 避免和字段命名冲突，不用 $RAW@LIST$ 是因为 $ 会在 fastjson 内部转义，浪费性能

public static String KEY_VICE_ITEM = "@VICE@ITEM"; // 避免和字段命名冲突，不用 $VICE@LIST$ 是因为 $ 会在 fastjson 内部转义，浪费性能

private Parser<T, M, L> parser;

@Override

public Parser<T, M, L> getParser() {

return parser;

}

@Override

public AbstractSQLExecutor<T, M, L> setParser(Parser<T, M, L> parser) {

this.parser = parser;

return this;

}

private int generatedSQLCount = 0;

private int cachedSQLCount = 0;

private int executedSQLCount = 0;

@Override

public int getGeneratedSQLCount() {

return generatedSQLCount;

}

@Override

public int getCachedSQLCount() {

return cachedSQLCount;

}

@Override

public int getExecutedSQLCount() {

return executedSQLCount;

}

private long executedSQLDuration = 0;

private long sqlResultDuration = 0;

@Override

public long getExecutedSQLDuration() {

return executedSQLDuration;

}

@Override

public long getSqlResultDuration() {

return sqlResultDuration;

}

/**

* 缓存 Map

protected Map<String, List<M>> cacheMap = new HashMap<>();

/**保存缓存

* @param sql key

* @param list value

* @param config 一般主表 SQLConfig<T, M, L> 不为 null，JOIN 副表的为 null

@Override

public void putCache(String sql, List<M> list, SQLConfig<T, M, L> config) {

if (sql == null || list == null) { // 空 list 有效，说明查询过 sql 了 || list.isEmpty()) {

Log.i(TAG, "saveList sql == null || list == null >> return;");

return;

}

cacheMap.put(sql, list);

}

/**获取缓存

* @param sql key

* @param config 一般主表 SQLConfig<T, M, L> 不为 null，JOIN 副表的为 null

@Override

public List<M> getCache(String sql, SQLConfig<T, M, L> config) {

return cacheMap.get(sql);

}

/**获取缓存

* @param sql key

* @param position

* @param config 一般主表 SQLConfig<T, M, L> 不为 null，JOIN 副表的为 null

* @return

@Override

public M getCacheItem(String sql, int position, SQLConfig<T, M, L> config) {

List<M> list = getCache(sql, config);

return getCacheItem(list, position, config);

}

public M getCacheItem(List<M> list, int position, SQLConfig<T, M, L> config) {

// 只要 list 不为 null，则如果 list.get(position) == null，则返回 {} ，避免再次 SQL 查询

if (list == null) {

return null;

}

M result = position >= list.size() ? null : list.get(position);

return result != null ? result : JSON.createJSONObject();

}

/**移除缓存

* @param sql key

* @param config

@Override

public void removeCache(String sql, SQLConfig<T, M, L> config) {

if (sql == null) {

Log.i(TAG, "removeList sql == null >> return;");

return;

}

cacheMap.remove(sql);

}

@Override

public ResultSet executeQuery(@NotNull Statement statement, String sql) throws Exception {

ResultSet rs = statement.executeQuery(sql);

return rs;

}

@Override

public int executeUpdate(@NotNull Statement statement, String sql) throws Exception {

int c = statement.executeUpdate(sql);

return c;

}

@Override

public ResultSet execute(@NotNull Statement statement, String sql) throws Exception {

statement.execute(sql);

ResultSet rs = statement.getResultSet();

return rs;

}

public static int MIN_OPTIMIZE_CAPACITY = 2^16 - 1;

/**执行SQL

* @param config

* @return

* @throws Exception

@Override

public M execute(@NotNull SQLConfig<T, M, L> config, boolean unknownType) throws Exception {

long executedSQLStartTime = System.currentTimeMillis();

final String sql = config.gainSQL(false);

if (StringUtil.isEmpty(sql, true)) {

Log.e(TAG, "execute StringUtil.isEmpty(sql, true) >> return null;");

return null;

}

Parser<T, M, L> parser2 = config.gainParser();

parser = parser2 != null ? parser2 : getParser();;

boolean isExplain = config.isExplain();

boolean isHead = RequestMethod.isHeadMethod(config.getMethod(), true);

final int position = config.getPosition();

M result;

if (isExplain == false) {

generatedSQLCount ++;

}

long startTime = System.currentTimeMillis();

Log.d(TAG, "\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"

+ "\n已生成 " + generatedSQLCount + " 条 SQL"

+ "\nexecute startTime = " + startTime

+ "\ndatabase = " + StringUtil.get(config.getDatabase())

+ "; schema = " + StringUtil.get(config.getSchema())

+ "; sql = \n" + sql

+ "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");

ResultSet rs = null;

List<M> resultList = null;

Map<String, M> childMap = null;

Map<String, String> keyMap = null;

try {

if (unknownType) {

if (isExplain == false) { //只有 SELECT 才能 EXPLAIN

executedSQLCount ++;

executedSQLStartTime = System.currentTimeMillis();

}

Statement statement = getStatement(config);

rs = execute(statement, sql);

int updateCount = statement.getUpdateCount();

if (isExplain == false) {

executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;

}

result = JSON.createJSONObject();

result.put(JSONResponse.KEY_COUNT, updateCount);

result.put("update", updateCount >= 0);

//导致后面 rs.getMetaData() 报错 Operation not allowed after ResultSet closed result.put("moreResults", statement.getMoreResults());

}

else {

RequestMethod method = config.getMethod();

switch (method) {

case POST:

case PUT:

case DELETE:

if (isExplain == false) { //只有 SELECT 才能 EXPLAIN

executedSQLCount ++;

executedSQLStartTime = System.currentTimeMillis();

}

int updateCount = executeUpdate(config);

if (isExplain == false) {

executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;

}

if (updateCount <= 0) {

throw new IllegalAccessException("没权限访问或对象不存在！"); // NotExistException 会被 catch 转为成功状态

}

// updateCount>0时收集结果。例如更新操作成功时，返回count(affected rows)、id字段

result = parser.newSuccessResult(); // TODO 对 APIAuto 及其它现有的前端/客户端影响比较大，暂时还是返回 code 和 msg，5.0 再移除 JSON.createJSONObject();

//id,id{}至少一个会有，一定会返回，不用抛异常来阻止关联写操作时前面错误导致后面无条件执行！

result.put(JSONResponse.KEY_COUNT, updateCount);//返回修改的记录数

String idKey = config.getIdKey();

if (config.getId() != null) {

result.put(idKey, config.getId());

}

if (config.getIdIn() != null) {

result.put(idKey + "[]", config.getIdIn());

}

if (method == RequestMethod.PUT || method == RequestMethod.DELETE) {

config.setMethod(RequestMethod.GET);

removeCache(config.gainSQL(false), config);

config.setMethod(method);

}

return result;

case GET:

case GETS:

case HEAD:

case HEADS:

List<M> cache = getCache(sql, config);

result = getCacheItem(cache, position, config);

Log.i(TAG, ">>> execute result = getCache('" + sql + "', " + position + ") = " + result);

if (result != null) {

if (isExplain == false) {

cachedSQLCount ++;

}

if (cache != null && cache.size() > 1) {

result.put(KEY_RAW_LIST, cache);

}

Log.d(TAG, "\n\n execute result != null >> return result;" + "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");

return result;

}

if (isExplain == false) { //只有 SELECT 才能 EXPLAIN

executedSQLCount ++;

executedSQLStartTime = System.currentTimeMillis();

}

rs = executeQuery(config); //FIXME SQL Server 是一次返回两个结果集，包括查询结果和执行计划，需要 moreResults

if (isExplain == false) {

executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;

}

break;

default: //OPTIONS, TRACE等

Log.e(TAG, "execute sql = " + sql + " ; method = " + config.getMethod() + " >> return null;");

return null;

}

if (isExplain == false && isHead) {

if (rs.next() == false) {

return parser.newErrorResult(new SQLException("数据库错误, rs.next() 失败！"));

}

result = parser.newSuccessResult();

// 兼容nosql,比如 elasticSearch-sql

if(config.isElasticsearch()) {

result.put(JSONResponse.KEY_COUNT, rs.getObject(1));

}else {

result.put(JSONResponse.KEY_COUNT, rs.getLong(1));

}

resultList = new ArrayList<>(1);

resultList.add(result);

}

else {

// final boolean cache = config.getCount() != 1;

// Log.d(TAG, "select cache = " + cache + "; resultList" + (resultList == null ? "=" : "!=") + "null");

try { // 设置初始容量为查到的数据量，解决频繁扩容导致的延迟，貌似只有 rs.last 取 rs.getRow() ? 然后又得 rs.beforeFirst 重置位置以便下方取值

rs.last(); //移到最后一行

resultList = new ArrayList<>(rs.getRow());

rs.beforeFirst();

}

catch (Throwable e) {

Log.e(TAG, "try { rs.last(); resultList = new ArrayList<>(rs.getRow()); rs.beforeFirst(); >> } catch (Throwable e) = " + e.getMessage());

int capacity;

if (config.getId() != null) { // id:Object 一定是 AND 条件，最终返回数据最多就这么多

capacity = 1;

}

else {

Object idIn = config.getIdIn();

if (idIn instanceof Collection<?>) { // id{}:[] 一定是 AND 条件，最终返回数据最多就这么多

capacity = ((Collection<?>) idIn).size();

}

else { // 预估容量

capacity = config.getCount() <= 0 ? AbstractParser.MAX_QUERY_COUNT : config.getCount();

if (capacity > MIN_OPTIMIZE_CAPACITY) {

// 有 WHERE 条件，条件越多过滤数据越多，暂时不考虑 @combine:"a | (b & !c)" 里面 | OR 和 ! NOT 条件，太复杂也不是很必要

Map<String, List<String>> combine = config.getCombineMap();

List<String> andList = combine == null ? null : combine.get("&");

int andCondCount = andList == null ? (config.getWhere() == null ? 0 : config.getWhere().size()) : andList.size();

List<String> orList = combine == null ? null : combine.get("|");

int orCondCount = orList == null ? 0 : orList.size();

List<String> notList = combine == null ? null : combine.get("!");

int notCondCount = notList == null ? 0 : notList.size();

// 有 GROUP BY 分组，字段越少过滤数据越多

String[] group = StringUtil.split(config.getGroup());

int groupCount = group == null ? 0 : group.length;

if (groupCount > 0 && Arrays.asList(group).contains(config.getIdKey())) {

groupCount = 0;

}

// 有 HAVING 聚合函数，字段越多过滤数据越多，暂时不考虑 @combine:"a | (b & !c)" 里面 | OR 和 ! NOT 条件，太复杂也不是很必要

Map<String, Object> having = config.getHaving();

int havingCount = having == null ? 0 : having.size();

double cap = capacity / Math.pow(1.5, Math.log10(capacity)/8 // LIMIT 10^9 = 1 亿以内无任何条件时最多扩容 1 次

+ andCondCount

+ ((orCondCount <= 0 ? 0 : 2.0d/orCondCount) // 1: 2.3, 2: 1.5, 3: 1.3, 4: 1.23, 5: 1.18

+ (notCondCount/5.0d) // 1: 1.08, 2: 1.18, 3: 1.28, 4: 1.38, 1.50

+ (groupCount <= 0 ? 0 : 10.0d/Math.min(5, groupCount))) // 1: 57.7, 7.6, 3: 3.9, 4: 2.8, 5: 2.3

+ havingCount

);

cap = groupCount > 0 ? cap : Math.max(MIN_OPTIMIZE_CAPACITY, Math.max(cap, capacity/Math.pow(1.5, 5))); // 1/(1.5^5) = 0.13

capacity = (int) (cap + 1); // 避免正好比需要容量少一点点导致多一次扩容，大量数据 System.arrayCopy

}

resultList = new ArrayList<>(capacity);

}

int index = -1;

long startTime2 = System.currentTimeMillis();

ResultSetMetaData rsmd = rs.getMetaData();

final int length = rsmd.getColumnCount();

sqlResultDuration += System.currentTimeMillis() - startTime2;

//<SELECT * FROM Comment WHERE momentId = '470', { id: 1, content: "csdgs" }>

childMap = new HashMap<>(); //要存到cacheMap

// Map<Integer, Join> columnIndexAndJoinMap = new HashMap<>(length);

String lastTableName = null; // 默认就是主表 config.getTable();

String lastAliasName = null; // 默认就是主表 config.getAlias();

int lastViceTableStart = 0;

int lastViceColumnStart = 0;

Join lastJoin = null;

// TODO String[] columnIndexAndTableMap = new String[length];

// WHERE id = ? AND ... 或 WHERE ... AND id = ? 强制排序 remove 再 put，还是重新 getSQL吧

List<Join<T, M, L>> joinList = config.getJoinList();

boolean hasJoin = config.hasJoin() && joinList != null && ! joinList.isEmpty();

// 直接用数组存取更快 Map<Integer, Join> columnIndexAndJoinMap = isExplain || ! hasJoin ? null : new HashMap<>(length);

Join[] columnIndexAndJoinMap = isExplain || ! hasJoin ? null : new Join[length];

Map<String, Integer> repeatMap = columnIndexAndJoinMap == null || ! config.isQuestDB() ? null : new HashMap<>();

keyMap = repeatMap == null ? null : new HashMap<>();

// int viceColumnStart = length + 1; //第一个副表字段的index

// FIXME 统计游标查找的时长？可能 ResultSet.next() 及 getTableName, getColumnName, getObject 比较耗时，因为不是一次加载到内存，而是边读边发

long lastCursorTime = System.currentTimeMillis();

while (rs.next()) {

sqlResultDuration += System.currentTimeMillis() - lastCursorTime;

lastCursorTime = System.currentTimeMillis();

index ++;

Log.d(TAG, "\n\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n execute while (rs.next()){ index = " + index + "\n\n");

M item = JSON.createJSONObject();

M viceItem = null;

M curItem = item;

boolean isMain = true;

boolean reseted = false;

for (int i = 1; i <= length; i++) {

// if (hasJoin && viceColumnStart > length && config.getSQLTable().equalsIgnoreCase(rsmd.getTableName(i)) == false) {

// viceColumnStart = i;

// }

// bugfix-修复非常规数据库字段，获取表名失败导致输出异常

Join curJoin = columnIndexAndJoinMap == null ? null : columnIndexAndJoinMap[i - 1]; // columnIndexAndJoinMap.get(i);

// 为什么 isExplain == false 不用判断？因为所有字段都在一张 Query Plan 表

if (index <= 0 && columnIndexAndJoinMap != null) { // && viceColumnStart > length) {

SQLConfig<T, M, L> curConfig = curJoin == null || ! curJoin.isSQLJoin() ? null : curJoin.getCacheConfig();

List<String> curColumn = curConfig == null ? null : curConfig.getColumn();

String sqlTable = curConfig == null ? null : curConfig.gainSQLTable();

String sqlAlias = curConfig == null ? null : curConfig.getAlias();

List<String> column = config.getColumn();

int mainColumnSize = column == null ? 0 : column.size();

boolean toFindJoin = mainColumnSize <= 0 || i > mainColumnSize; // 主表就不用找 JOIN 配置

if (StringUtil.isEmpty(sqlTable, true)) {

//sqlTable = null;

if (toFindJoin) { // 在主表字段数量内的都归属主表

long startTime3 = System.currentTimeMillis();

sqlTable = rsmd.getTableName(i); // SQL 函数甚至部分字段都不返回表名，当然如果没传 @column 生成的 Table.* 则返回的所有字段都会带表名

//if (StringUtil.isEmpty(sqlTable, true)) {

// boolean isEmpty = curItem == null || curItem.isEmpty();

String key = getKey(config, rs, rsmd, index, curItem, i, childMap, keyMap);

char last = repeatMap == null ? 0 : key.charAt(key.length() - 1);

String repeatKey = last < '0' || last > '9' ? null : key.substring(0, key.length() - 1);

Integer repeatCount = repeatKey == null ? null : repeatMap.get(repeatKey);

int nc = repeatCount == null ? 1 : repeatCount + 1;

if (last == nc + '0') {

keyMap.put(key, repeatKey);

repeatMap.put(repeatKey, nc);

key = repeatKey; // QuestDB 会自动把副表与主表同名的字段重命名，例如 id 改为 id1, date 改为 date1

}

if (i > 1 && ( (curItem != null && curItem.containsKey(key))

|| (StringUtil.isNotEmpty(key) && StringUtil.equals(key, curConfig == null ? null : curConfig.getIdKey())))

) { // Presto 等引擎 JDBC 返回 rsmd.getTableName(i) 为空，主表如果一个字段都没有会导致 APISJON 主副表所有字段都不返回

sqlTable = null;

if (reseted) {

SQLConfig<T, M, L> lastCfg = lastJoin == null ? null : lastJoin.getCacheConfig();

List<String> lastColumn = lastCfg == null ? null : lastCfg.getColumn();

lastViceTableStart ++;

lastViceColumnStart += lastColumn == null ? 1 : lastColumn.size();

}

else if (isMain) {

for (int j = 0; j < joinList.size(); j++) {

Join<T, M, L> join = joinList.get(j);

SQLConfig<T, M, L> cfg = join == null || ! join.isSQLJoin() ? null : join.getJoinConfig();

List<String> c = cfg == null ? null : cfg.getColumn();

if (cfg != null) {

sqlTable = cfg.gainSQLTable();

sqlAlias = cfg.getAlias();

lastViceTableStart = j; // 避免后面的空 @column 表内字段被放到之前的空 @column 表

lastViceColumnStart = i + 1;

curJoin = join;

curConfig = cfg;

curColumn = c;

toFindJoin = false;

isMain = false;

break;

}

reseted = true;

}

//}

sqlResultDuration += System.currentTimeMillis() - startTime3;

if (toFindJoin && StringUtil.isEmpty(sqlTable, true)) { // hasJoin 已包含这个判断 && joinList != null) {

//sqlTable = null; // QuestDB 等 rsmd.getTableName(i) 返回 "" 导致以下 StringUtil.equalsIgnoreCase 对比失败

int nextViceColumnStart = lastViceColumnStart; // 主表没有 @column 时会偏小 lastViceColumnStart

int joinCount = joinList.size();

for (int j = lastViceTableStart; j < joinCount; j++) { // 查找副表 @column，定位字段所在表

Join<T, M, L> join = joinList.get(j);

SQLConfig<T, M, L> cfg = join == null || ! join.isSQLJoin() ? null : join.getJoinConfig();

List<String> c = cfg == null ? null : cfg.getColumn();

nextViceColumnStart += (c != null && ! c.isEmpty() ?

c.size() : (

StringUtil.equalsIgnoreCase(sqlTable, lastTableName)

&& StringUtil.equals(sqlAlias, lastAliasName) ? 1 : 0

)

);

if (i < nextViceColumnStart) { // 导致只 JOIN 一张副表时主表数据放到副表 || j >= joinCount - 1) {

sqlTable = cfg.gainSQLTable();

sqlAlias = cfg.getAlias();

lastViceTableStart = j; // 避免后面的空 @column 表内字段被放到之前的空 @column 表

curJoin = join;

curConfig = cfg;

curColumn = c;

toFindJoin = false;

isMain = false;

break;

}

// 没有 @column，仍然定位不了，用前一个 table 名。FIXME 如果刚好某个表内第一个字段是就是 SQL 函数？

if (StringUtil.isEmpty(sqlTable, true)) {

sqlTable = lastTableName;

sqlAlias = lastAliasName;

toFindJoin = false;

}

} else if (config.isClickHouse() && (sqlTable.startsWith("`") || sqlTable.startsWith("\""))){

sqlTable = sqlTable.substring(1, sqlTable.length() - 1);

}

if (StringUtil.equalsIgnoreCase(sqlTable, lastTableName) == false || StringUtil.equals(sqlAlias, lastAliasName) == false) {

lastTableName = sqlTable;

lastAliasName = sqlAlias;

lastViceColumnStart = i;

if (toFindJoin) { // 找到对应的副表 JOIN 配置

for (int j = lastViceTableStart; j < joinList.size(); j++) { // 查找副表 @column，定位字段所在表

Join join = joinList.get(j);

SQLConfig<T, M, L> cfg = join == null || ! join.isSQLJoin() ? null : join.getJoinConfig();

if (cfg != null && StringUtil.equalsIgnoreCase(sqlTable, cfg.gainSQLTable())

) { // FIXME 导致副表字段错放到主表 && StringUtil.equals(sqlAlias, cfg.getAlias())) {

lastViceTableStart = j; // 避免后面的空 @column 表内字段被放到之前的空 @column 表

curJoin = join;

curConfig = cfg;

curColumn = curConfig == null ? null : curConfig.getColumn();

isMain = false;

break;

}

if (isMain) {

lastViceColumnStart ++;

}

else {

if (curJoin == null) {

curJoin = lastJoin;

}

else {

lastJoin = curJoin;

}

if (curColumn == null) {

curConfig = curJoin == null || ! curJoin.isSQLJoin() ? null : curJoin.getJoinConfig();

curColumn = curConfig == null ? null : curConfig.getColumn();

}

// 解决后面的表内 SQL 函数被放到之前的空 @column 表

if (curColumn == null || curColumn.isEmpty()) {

lastViceColumnStart ++;

}

columnIndexAndJoinMap[i - 1] = curJoin;

}

// 如果是主表则直接用主表对应的 item，否则缓存副表数据到 childMap

Join prevJoin = columnIndexAndJoinMap == null || i < 2 ? null : columnIndexAndJoinMap[i - 2];

if (curJoin != prevJoin) { // 前后字段不在同一个表对象，即便后面出现 null，也不该是主表数据，而是逻辑 bug 导致

SQLConfig<T, M, L> viceConfig = curJoin != null && curJoin.isSQLJoin() ? curJoin.getCacheConfig() : null;

boolean hasPK = false;

if (viceConfig != null) { //FIXME 只有和主表关联才能用 item，否则应该从 childMap 查其它副表数据

List<On> onList = curJoin.getOnList();

int size = onList == null ? 0 : onList.size();

if (size > 0) {

String idKey = viceConfig.getIdKey();

String tblKey = config.gainTableKey();

for (int j = size - 1; j >= 0; j--) {

On on = onList.get(j);

String ok = on == null ? null : on.getOriginKey();

if (ok == null) {

throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[" + j + (on == null ? "] = null！" : ".getOriginKey() = null！"));

}

String k = ok.substring(0, ok.length() - 1);

String ttk = on.getTargetTableKey();

M target = StringUtil.equals(ttk, tblKey) ? item : (viceItem == null ? null : JSON.get(viceItem, ttk));

Object v = target == null ? null : target.get(on.getTargetKey());

hasPK = hasPK || (k.equals(idKey) && v != null);

viceConfig.putWhere(k, v, true);

}

if (viceConfig == null) { // StringUtil.isEmpty(viceSql, true)) {

Log.i(TAG, "execute viceConfig == null >> item = null; >> ");

curItem = null;

}

else if (curJoin.isOuterJoin() || curJoin.isAntiJoin()) {

Log.i(TAG, "execute curJoin.isOuterJoin() || curJoin.isAntiJoin() >> item = null; >> ");

curItem = null; // 肯定没有数据，缓存也无意义

// 副表是按常规条件查询，缓存会导致其它同表同条件对象查询结果集为空 childMap.put(viceSql, JSON.createJSONObject()); // 缓存固定空数据，避免后续多余查询

}

else {

String viceName = viceConfig.gainTableKey();

if (viceItem == null) {

viceItem = JSON.createJSONObject();

}

curItem = JSON.get(viceItem, viceName);

String viceSql = hasPK ? viceConfig.gainSQL(false) : null; // TODO 在 SQLConfig<T, M, L> 缓存 SQL，减少大量的重复生成

M curCache = hasPK ? childMap.get(viceSql) : null;

if (curItem == null || curItem.isEmpty()) {

// 导致前面判断重复 key 出错 curItem = curCache != null ? curCache : JSON.createJSONObject();

curItem = JSON.createJSONObject();

viceItem.put(viceName, curItem);

if (hasPK && curCache == null) {

childMap.put(viceSql, curItem);

}

else if (hasPK) {

if (curCache == null || curCache.isEmpty()) {

childMap.put(viceSql, curItem);

}

else {

curCache.putAll(curItem);

// 导致前面判断重复 key 出错 curItem = curCache;

// viceItem.put(viceName, curItem);

}

curItem = (M) onPutColumn(config, rs, rsmd, index, curItem, i, curJoin, childMap, keyMap); // isExplain == false && hasJoin && i >= viceColumnStart ? childMap : null);

}

if (viceItem != null) {

item.put(KEY_VICE_ITEM, viceItem);

}

resultList = onPutTable(config, rs, rsmd, resultList, index, item);

Log.d(TAG, "execute while (rs.next()) { resultList.put( " + index + ", result); " + "\n >>>>>>>>>>>>>>>>>>>>>>>>>>> \n\n");

}

finally {

if (rs != null) {

try {

rs.close();

}

catch (Exception e) {

Log.e(TAG, "close ResultSet failed", e);

}

if (resultList == null) {

return null;

}

if (unknownType || isExplain) {

if (isExplain) {

if (result == null) {

result = JSON.createJSONObject();

}

config.setExplain(false);

result.put("sql", config.gainSQL(false));

config.setExplain(isExplain);

}

result.put("list", resultList);

if (unknownType == false) {

putCache(sql, Arrays.asList(result), config);

}

return result;

}

if (isHead == false) {

// @ APP JOIN 查询副表并缓存到 childMap <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Map<String, List<M>> appJoinChildMap = new HashMap<>();

childMap.forEach((viceSql, item) -> appJoinChildMap.put(viceSql, Arrays.asList(item)));

executeAppJoin(config, resultList, appJoinChildMap, keyMap);

// @ APP JOIN 查询副表并缓存到 childMap >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

//子查询 SELECT Moment.*, Comment.id 中的 Comment 内字段

Set<Entry<String, List<M>>> set = appJoinChildMap.entrySet();

//<sql, Table>

for (Entry<String, List<M>> entry : set) {

putCache(entry.getKey(), entry.getValue(), null);

}

Log.i(TAG, ">>> execute putCache('" + sql + "', resultList); resultList.size() = " + resultList.size());

// 数组主表对象额外一次返回全部，方便 Parser<T, M, L> 缓存来提高性能

result = position >= resultList.size() ? JSON.createJSONObject() : resultList.get(position);

if (position == 0 && resultList.size() > 1 && result != null && result.isEmpty() == false) {

// 不是 main 不会直接执行，count=1 返回的不会超过 1 && config.isMain() && config.getCount() != 1

Log.i(TAG, ">>> execute position == 0 && resultList.size() > 1 && result != null && result.isEmpty() == false"

+ " >> result = JSON.createJSONObject(result); result.put(KEY_RAW_LIST, resultList);");

result = (M) JSON.createJSONObject(result);

result.put(KEY_RAW_LIST, resultList);

}

putCache(sql, resultList, config);

long endTime = System.currentTimeMillis();

Log.d(TAG, "\n\n execute endTime = " + endTime + "; duration = " + (endTime - startTime)

+ "\n return resultList.get(" + position + ");" + "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");

return result;

}

/**@ APP JOIN 查询副表并缓存到 childMap

* @param config

* @param resultList

* @param childMap

* @throws Exception

protected void executeAppJoin(SQLConfig<T, M, L> config, List<M> resultList, Map<String, List<M>> childMap, Map<String, String> keyMap) throws Exception {

List<Join<T, M, L>> joinList = config.getJoinList();

if (joinList != null) {

for (Join<T, M, L> join : joinList) {

if (join.isAppJoin() == false) {

Log.i(TAG, "executeAppJoin for (Join j : joinList) >> j.isAppJoin() == false >> continue;");

continue;

}

SQLConfig<T, M, L> cc = join.getCacheConfig(); //这里用config改了getSQL后再还原很麻烦，所以提前给一个config2更好

if (cc == null) {

if (Log.DEBUG) {

throw new NullPointerException("服务器内部错误, executeAppJoin cc == null ! 导致不能缓存 @ APP JOIN 的副表数据！");

}

continue;

}

SQLConfig<T, M, L> jc = join.getJoinConfig();

List<On> onList = join.getOnList();

On on = onList == null || onList.isEmpty() ? null : onList.get(0); // APP JOIN 应该有且只有一个 ON 条件

String originKey = on == null ? null : on.getOriginKey();

if (originKey == null) {

throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[0" + (on == null ? "] = null！" : ".getOriginKey() = null！"));

}

String key = on.getKey();

if (key == null) {

throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[0" + (on == null ? "] = null！" : ".getKey() = null！"));

}

// 取出 "id@": "@/User/userId" 中所有 userId 的值

List<Object> targetValueList = new ArrayList<>();

for (int i = 0; i < resultList.size(); i++) {

M mainTable = resultList.get(i);

Object targetValue = mainTable == null ? null : mainTable.get(on.getTargetKey());

if (targetValue != null && targetValueList.contains(targetValue) == false) {

targetValueList.add(targetValue);

}

if (targetValueList.isEmpty() && config.isExplain() == false) {

throw new NotExistException("targetValueList.isEmpty() && config.isExplain() == false");

}

// 替换为 "id{}": [userId1, userId2, userId3...]

jc.putWhere(originKey, null, false); // remove originKey

jc.putWhere(key + "{}", targetValueList, true); // add originKey{} }

jc.setMain(true).setPreparedValueList(new ArrayList<>());

// 放一块逻辑更清晰，也避免解析 * 等不支持或性能开销

// String q = jc.getQuote();

// if (allChildCount > 0 && jc.getCount() <= 0) {

// List<String> column = jc.getColumn();

// if (column == null || column.isEmpty()) {

// column = Arrays.asList("*;row_number()OVER(PARTITION BY " + q + key + q + " ORDER BY " + q + key + q + " ASC):_row_num_");

// }

// else {

// column.add("row_number()OVER(PARTITION BY " + q + key + q + " ORDER BY " + q + key + q + " ASC):_row_num_");

// }

// jc.setColumn(column);

// }

int childCount = cc.getCount();

int allChildCount = childCount*config.getCount(); // 所有分组子项数量总和

boolean isOne2Many = childCount != 1 || join.isOne2Many();

// 一对多会漏副表数据 TODO 似乎一对一走以下优化 row_number() <= childCount 逻辑也没问题

// if (isOne2Many == false && allChildCount > 0 && jc.getCount() < allChildCount) {

// jc.setCount(allChildCount);

// }

boolean prepared = jc.isPrepared();

String sql = jc.gainSQL(false);

if (StringUtil.isEmpty(sql, true)) {

throw new NullPointerException(TAG + ".executeAppJoin StringUtil.isEmpty(sql, true) >> return null;");

}

String sql2 = null;

if (childCount > 0 && isOne2Many && (jc.isMySQL() == false || jc.gainDBVersionNums()[0] >= 8)) {

// 加 row_number 字段并不会导致 count 等聚合函数统计出错，结果偏大，SQL JOIN 才会，之前没发现是因为缓存失效 bug

// boolean noAggrFun = true;

// List<String> column = jc.getColumn();

// if (column != null) {

// for (String c : column) {

// int start = c == null ? -1 : c.indexOf("(");

// int end = start <= 0 ? -1 : c.lastIndexOf(")");

// if (start > 0 && end > start) {

// String fun = c.substring(0, start);

// if (AbstractSQLConfig.SQL_AGGREGATE_FUNCTION_MAP.containsKey(fun)) {

// noAggrFun = false;

// break;

// }

// if (noAggrFun) { // 加 row_number 字段会导致 count 等聚合函数统计出错，结果偏大？

String q = jc.getQuote();

sql2 = prepared && jc.isTDengine() == false ? jc.gainSQL(true) : sql;

String prefix = "SELECT * FROM(";

String rnStr = ", row_number() OVER (PARTITION BY " + q + key + q + ((AbstractSQLConfig) jc).gainOrderString(true) + ") _row_num_ FROM ";

String suffix = ") _t WHERE ( (_row_num_ <= " + childCount + ") )" + (allChildCount > 0 ? " LIMIT " + allChildCount : "");

sql2 = prefix

// 放一块逻辑更清晰，也避免解析 * 等不支持或性能开销 + sql

+ sql2.replaceFirst(" FROM ", rnStr) // * 居然只能放在 row_number() 前面，放后面就报错 "SELECT ", rnStr)

+ suffix;

sql = prepared ? (prefix + sql.replaceFirst(" FROM ", rnStr) + suffix) : sql2;

// }

}

boolean isExplain = jc.isExplain();

if (isExplain == false) {

generatedSQLCount ++;

}

long startTime = System.currentTimeMillis();

Log.d(TAG, "\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"

+ "\n executeAppJoin startTime = " + startTime

+ "\n sql = \n " + sql

+ "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");

//执行副表的批量查询并缓存到 childMap

ResultSet rs = null;

try {

long executedSQLStartTime = 0;

if (isExplain == false) { //只有 SELECT 才能 EXPLAIN

executedSQLCount ++;

executedSQLStartTime = System.currentTimeMillis();

}

rs = executeQuery(jc, sql2);

if (isExplain == false) {

executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;

}

int count = 0;

int index = -1;

long startTime2 = System.currentTimeMillis();

ResultSetMetaData rsmd = rs.getMetaData();

final int length = rsmd.getColumnCount();

sqlResultDuration += System.currentTimeMillis() - startTime2;

Map<String, Boolean> skipMap = new HashMap<>();

long lastCursorTime = System.currentTimeMillis();

while ((allChildCount <= 0 || count < allChildCount) && rs.next()) { //FIXME 同时有 @ APP JOIN 和 < 等 SQL JOIN 时，next = false 总是无法进入循环，导致缓存失效，可能是连接池或线程问题

sqlResultDuration += System.currentTimeMillis() - lastCursorTime;

lastCursorTime = System.currentTimeMillis();

index ++;

Log.d(TAG, "\n\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n executeAppJoin while (rs.next()){ index = " + index + "\n\n");

M result = JSON.createJSONObject();

for (int i = 1; i <= length; i++) {

result = onPutColumn(jc, rs, rsmd, index, result, i, null, null, keyMap);

}

//每个 result 都要用新的 SQL 来存 childResultMap = onPutTable(config, rs, rsmd, childResultMap, index, result);

Log.d(TAG, "\n executeAppJoin while (rs.next()) { resultList.put(" + index + ", result); "

+ "\n >>>>>>>>>>>>>>>>>>>>>>>>>>> \n\n");

//TODO 兼容复杂关联

cc.putWhere(key, result.get(key), true); // APP JOIN 应该有且只有一个 ON 条件

String cacheSql = cc.gainSQL(false);

List<M> results = childMap.get(cacheSql);

if (results == null || skipMap.get(cacheSql) == null) { // 避免添加重复数据

results = new ArrayList<>(childCount);

childMap.put(cacheSql, results);

skipMap.put(cacheSql, Boolean.TRUE);

}

if (childCount <= 0 || results.size() < childCount) { // 避免超过子数组每页数量

// if (count == 1 && results.isEmpty() == false) { // 避免添加重复数据

// results.clear();

// }

results.add(result); //缓存到 childMap

count ++;

Log.d(TAG, ">>> executeAppJoin childMap.put('" + cacheSql + "', result); childMap.size() = " + childMap.size());

}

finally {

if (rs != null) {

try {

rs.close();

}

catch (Exception e) {

Log.e(TAG, "close ResultSet failed in executeAppJoin", e);

}

long endTime = System.currentTimeMillis();

Log.d(TAG, "\n\n executeAppJoin endTime = " + endTime + "; duration = " + (endTime - startTime)

+ "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");

}

/**table.put(rsmd.getColumnLabel(i), rs.getObject(i));

* @param config

* @param rs

* @param rsmd

* @param row 从0开始

* @param table

* @param columnIndex 从1开始

* @param childMap

* @return result

* @throws Exception

protected M onPutColumn(@NotNull SQLConfig<T, M, L> config, @NotNull ResultSet rs, @NotNull ResultSetMetaData rsmd

, final int row, @NotNull M table, final int columnIndex, Join<T, M, L> join, Map<String, M> childMap

, Map<String, String> keyMap) throws Exception {

if (table == null) { // 对应副表 viceSql 不能生成正常 SQL，或者是 ! - Outer, ( - ANTI JOIN 的副表这种不需要缓存及返回的数据

Log.i(TAG, "onPutColumn table == null >> return table;");

return table;

}

if (isHideColumn(config, rs, rsmd, row, table, columnIndex, childMap, keyMap)) {

Log.i(TAG, "onPutColumn isHideColumn(config, rs, rsmd, row, table, columnIndex, childMap) >> return table;");

return table;

}

String label = getKey(config, rs, rsmd, row, table, columnIndex, childMap, keyMap);

Object value = getValue(config, rs, rsmd, row, table, columnIndex, label, childMap, keyMap);

// 主表必须 put 至少一个 null 进去，否则全部字段为 null 都不 put 会导致中断后续正常返回值

if (value != null || ENABLE_OUTPUT_NULL_COLUMN || (join == null && table.isEmpty())) {

View remainder of file in raw view

APIJSON/src/main/java/apijson/orm/AbstractSQLExecutor.java at main · APIJSON/APIJSON · GitHub

Expand file tree

Breadcrumbs

AbstractSQLExecutor.java

Latest commit

History

Breadcrumbs

AbstractSQLExecutor.java

File metadata and controls