all-project/test/SQL修复最终验证.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
学生信息管理SQL修复最终验证脚本
测试修复后的XML映射文件是否能正确生成SQL
"""

import requests
import json
import time
from datetime import datetime

# 配置
BASE_URL = "http://localhost:8080"
LOGIN_URL = f"{BASE_URL}/auth/login"
STUDENT_LIST_URL = f"{BASE_URL}/xg-xx/studentInfo/list"

# 测试数据
LOGIN_DATA = {
    "clientId": "e5cd7e4891bf95d1d19206ce24a7b32e",
    "username": "admin",
    "password": "admin123",
    "grant_type": "password",
    "tenantId": "000000"
}

def test_server_connection():
    """测试服务器连接"""
    print("🔍 测试服务器连接...")
    try:
        response = requests.get(f"{BASE_URL}/health", timeout=5)
        if response.status_code == 200:
            print("✅ 服务器连接正常")
            return True
        else:
            print(f"❌ 服务器响应异常: {response.status_code}")
            return False
    except Exception as e:
        print(f"❌ 服务器连接失败: {e}")
        return False

def get_auth_token():
    """获取认证Token"""
    print("🔐 获取认证Token...")
    try:
        response = requests.post(LOGIN_URL, json=LOGIN_DATA, timeout=10)
        if response.status_code == 200:
            result = response.json()
            if result.get('code') == 200:
                token = result['data']['access_token']
                print("✅ Token获取成功")
                return token
            else:
                print(f"❌ 登录失败: {result.get('msg', '未知错误')}")
                return None
        else:
            print(f"❌ 登录请求失败: {response.status_code}")
            return None
    except Exception as e:
        print(f"❌ 登录异常: {e}")
        return None

def test_student_list_without_params(token):
    """测试不带参数的学生列表查询"""
    print("📋 测试不带参数的学生列表查询...")
    
    headers = {
        'Authorization': f'Bearer {token}',
        'Content-Type': 'application/json',
        'clientid': 'e5cd7e4891bf95d1d19206ce24a7b32e',
        'referer': 'http://localhost',
        'tenantId': '000000'
    }
    
    params = {
        'pageNum': 1,
        'pageSize': 10
    }
    
    try:
        response = requests.get(STUDENT_LIST_URL, params=params, headers=headers, timeout=10)
        print(f"响应状态码: {response.status_code}")
        
        if response.status_code == 200:
            result = response.json()
            if result.get('code') == 200:
                print("✅ 基础查询成功")
                data = result.get('data', {})
                total = data.get('total', 0)
                rows = data.get('rows', [])
                print(f"📊 查询结果: 总数={total}, 当前页记录数={len(rows)}")
                return True
            else:
                print(f"❌ 查询失败: {result.get('msg', '未知错误')}")
                return False
        else:
            print(f"❌ 请求失败: {response.status_code}")
            print(f"响应内容: {response.text}")
            return False
    except Exception as e:
        print(f"❌ 查询异常: {e}")
        return False

def test_student_list_with_gender_filter(token):
    """测试带性别过滤的学生列表查询（这是之前报错的场景）"""
    print("🔍 测试带性别过滤的学生列表查询...")
    
    headers = {
        'Authorization': f'Bearer {token}',
        'Content-Type': 'application/json',
        'clientid': 'e5cd7e4891bf95d1d19206ce24a7b32e',
        'referer': 'http://localhost',
        'tenantId': '000000'
    }
    
    params = {
        'pageNum': 1,
        'pageSize': 10,
        'gender': '1'  # 这是之前导致SQL重复WHERE的参数
    }
    
    try:
        response = requests.get(STUDENT_LIST_URL, params=params, headers=headers, timeout=10)
        print(f"响应状态码: {response.status_code}")
        
        if response.status_code == 200:
            result = response.json()
            if result.get('code') == 200:
                print("✅ 性别过滤查询成功 - SQL重复WHERE问题已修复！")
                data = result.get('data', {})
                total = data.get('total', 0)
                rows = data.get('rows', [])
                print(f"📊 查询结果: 总数={total}, 当前页记录数={len(rows)}")
                return True
            else:
                print(f"❌ 查询失败: {result.get('msg', '未知错误')}")
                return False
        else:
            print(f"❌ 请求失败: {response.status_code}")
            print(f"响应内容: {response.text}")
            return False
    except Exception as e:
        print(f"❌ 查询异常: {e}")
        return False

def test_student_list_with_multiple_filters(token):
    """测试多条件过滤查询"""
    print("🔍 测试多条件过滤查询...")
    
    headers = {
        'Authorization': f'Bearer {token}',
        'Content-Type': 'application/json',
        'clientid': 'e5cd7e4891bf95d1d19206ce24a7b32e',
        'referer': 'http://localhost',
        'tenantId': '000000'
    }
    
    params = {
        'pageNum': 1,
        'pageSize': 10,
        'gender': '1',
        'studentStatus': '1',
        'name': '张'
    }
    
    try:
        response = requests.get(STUDENT_LIST_URL, params=params, headers=headers, timeout=10)
        print(f"响应状态码: {response.status_code}")
        
        if response.status_code == 200:
            result = response.json()
            if result.get('code') == 200:
                print("✅ 多条件过滤查询成功")
                data = result.get('data', {})
                total = data.get('total', 0)
                rows = data.get('rows', [])
                print(f"📊 查询结果: 总数={total}, 当前页记录数={len(rows)}")
                return True
            else:
                print(f"❌ 查询失败: {result.get('msg', '未知错误')}")
                return False
        else:
            print(f"❌ 请求失败: {response.status_code}")
            print(f"响应内容: {response.text}")
            return False
    except Exception as e:
        print(f"❌ 查询异常: {e}")
        return False

def main():
    """主函数"""
    print("="*80)
    print("🎯 学生信息管理SQL修复最终验证")
    print("="*80)
    print(f"⏰ 测试时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print()
    
    # 1. 测试服务器连接
    if not test_server_connection():
        print("❌ 服务器未启动，请先启动后端服务")
        return
    
    print()
    
    # 2. 获取认证Token
    token = get_auth_token()
    if not token:
        print("❌ 无法获取认证Token，测试中止")
        return
    
    print()
    
    # 3. 测试基础查询
    if not test_student_list_without_params(token):
        print("❌ 基础查询失败")
        return
    
    print()
    
    # 4. 测试性别过滤查询（关键测试）
    if not test_student_list_with_gender_filter(token):
        print("❌ 性别过滤查询失败 - SQL重复WHERE问题可能仍存在")
        return
    
    print()
    
    # 5. 测试多条件过滤查询
    if not test_student_list_with_multiple_filters(token):
        print("❌ 多条件过滤查询失败")
        return
    
    print()
    print("="*80)
    print("🎉 所有测试通过！SQL重复WHERE问题已完全修复！")
    print("="*80)
    print("📋 修复总结:")
    print("  1. ✅ 修复了XML映射文件中的SQL语句结构")
    print("  2. ✅ 使用条件判断避免重复WHERE子句")
    print("  3. ✅ 验证了单条件和多条件查询都正常工作")
    print("  4. ✅ 确认MyBatis-Plus的customSqlSegment正确集成")
    print()
    print("🔧 技术细节:")
    print("  - 修改前: WHERE deleted = 0 AND tenant_id = '000000' ${ew.customSqlSegment}")
    print("  - 修改后: WHERE deleted = 0 AND tenant_id = '000000'")
    print("           <if test=\"ew != null and ew.sqlSegment != null\">")
    print("               AND ${ew.sqlSegment}")
    print("           </if>")
    print()

if __name__ == "__main__":
    main()