SQL校验方法

问题简介：在工作中，表字段多到一定程度，Select 中的字段和结果集中的字段对比很麻烦，还容易出错。于是写了一个检查Select和Insert的方法。（使用的是Scala语言）

　　1. 代码如下

 

import scala.annotation.tailrec

/**
  * 校验SQL，现支持三种校验
  * select 查询校验：select语句与结果集比对
  * insert into select 查询插入校验：查询语句与插入语句
  * insert into 插入校验：insert字段与values中的值数量
  *
  */
object TestSQL extends App {

  //select语句测试sql
  val selectStr =
    """
      |select
      |name,
      |password,
      |hello2,
      |hello2,
      |hello2
      | from
      |  testTables
      |
      |rs.getString("name")
      |rs.getString("password")
      |rs.getString("hello")
      |rs.getString("hello")
    """.stripMargin

  //insert测试语句
  val insertStr =
    """
      |Insert
      |into
      |testTable
      |(name,
      |password,
      |hello2,,,)
      | values
      |(,,,,,)
    """.stripMargin

  //insert into select测试语句
  val insertAndSelectSql =
    s"""
       |insert
       |into
       |testTable
       |(name,
       |password,
       |password,
       |hello)
       | values
       |select
       |name,
       |password,
       |hello2,
       |hello2,
       |hello3
       | from
       |  testTable2
   """.stripMargin

  //测试方法
  sqlTest(insertAndSelectSql)

  /**
    * 判断字符串中关键字，匹配校验语句
    *
    * @param str 待判断的字符串
    */
  def sqlTest(str: String): Unit = {
    //将字符串转换成小写
    val strLower = str.toLowerCase
    judgeSQL(strLower) match {
      case (true, false) => selectTest(strLower)
      case (false, true) => insertTest(strLower)
      case (true, true) => insertAndSelectTest(strLower)
      case (_, _) => println("暂不支持该SQL")
    }
  }

  /**
    * 查询语句和结果集字段校验
    *
    * @param selectStr 包含select语句和结果集的字符串
    */
  private def selectTest(selectStr: String): Unit = {
    //1.将select与from中的字段，放入链表中
    val selectLists: List[String] = strToList(getSubString(selectStr, "select", "from", 6))
    //2.获取rs.get中所有的值
    var rsLists: List[String] = List()
    rsLists = nextElement(selectStr, rsLists)
    //3.判断select语句和结果集获取的值中的参数是否重复
    judgeIsEquals(selectLists, "select")
    judgeIsEquals(rsLists, "result")
    //4.判断select中列的数量和结果集是否相等
    judgeFieldsNums(selectLists.size, rsLists.size, "select")
    //5.比较select语句和结果集获取的值中的参数是否相等
    judgeFields(selectLists, rsLists, "select")
  }

  /**
    * insert into select 语句校验
    *
    * @param insertAndSelectStr 查询插入语句字符串
    */
  private def insertAndSelectTest(insertAndSelectStr: String): Unit = {
    //1.将select与from中的字段，并计算数量
    //1.1截取select和from中的子字符串
    val selectSubFront = getSubString(insertAndSelectStr, "select", "from", 6)
    //1.2将select和from中的列放入链表集合中
    val selectLists: List[String] = strToList(selectSubFront)
    //1.3.判断select语句中的参数是否重复
    judgeIsEquals(selectLists, "select")
    //2.截取insert和select中间的字符串，计算字段数量
    //2.1截取insert和values中的子字符串
    val insertSubFront = getSubString(insertAndSelectStr, "insert", "values", 0)
    //2.2再截取“()”内列的集合
    val insertSubFrontList = strToList(getSubString(insertSubFront, "(", ")", 1))
    //2.3判断insert语句中字段是否有重复
    judgeIsEquals(insertSubFrontList, "insert")
    //3.判断insert和select中列的数量是否相等
    judgeFieldsNums(selectLists.size, insertSubFrontList.size, "insertAndSelect")
    //4.判断insert语句中的字段与select中是否相等
    judgeFields(selectLists, insertSubFrontList, "insertAndSelect")
  }

  /**
    * 插入SQL校验
    *
    * @param insertStr 插入语句sql
    */
  private def insertTest(insertStr: String): Unit = {
    //1.获取insert和values之间的字符串
    val insertSubFront = getSubString(insertStr, "insert", "values", 0)
    val insertSubFrontNums = countNumbers(insertSubFront)
    //2.获取values之后的字符串
    val insertSubBack = insertStr.substring(insertStr.indexOf("values"))
    val insertSubBackNums = countNumbers(insertSubBack)
    //3.判断两个字符串中的','数量差值
    judgeFieldsNums(insertSubFrontNums, insertSubBackNums, "insert")
  }

  /**
    * 获取结果集中字段组成的链表集合
    *
    * @param string 包含select和结果集的字符串
    * @param list   空list集合
    * @return 返回结果集中字段组成的链表集合
    */
  @tailrec
  private def nextElement(string: String, list: List[String]): List[String] = {
    val rightIndex = string.indexOf("\")")
    val leftIndex = string.indexOf("(\"") + 2
    val lists = list.::(string.substring(leftIndex, rightIndex))
    val subString = string.substring(rightIndex + 2)
    if ( string.lastIndexOf("\")").hashCode() == rightIndex.hashCode() ) {
      lists
    } else {
      nextElement(subString, lists)
    }
  }

  /**
    * 计算“,”的数量
    *
    * @param strs 待计算的字符串
    * @return “，”的数量
    */
  private def countNumbers(strs: String): Integer = {
    //计算包含','字符的数量
    var count: Integer = 0
    strs.foreach {
      str =>
        if ( str == ',' ) {
          count = count + 1
        }
    }
    count
  }

  /**
    * 判断是否是包含SQL关键字
    *
    * @param str 待校验的字符串
    * @return
    */
  private def judgeSQL(str: String): (Boolean, Boolean) = {
    //是否有insert关键字
    val isHasInsert = str.contains("insert")
    //是否有select关键字
    val isHasSelect = str.contains("select")
    (isHasSelect, isHasInsert)
  }

  /**
    * 获取子字符串
    *
    * @param parString     父字符串
    * @param firstString   sql语句第一个关键字
    * @param secoundString sql语句第二个关键字
    * @param shift         下标位移距离
    * @return 字符串
    */
  private def getSubString(parString: String, firstString: String, secoundString: String, shift: Integer): String = {
    parString.substring(parString.indexOf(firstString) + shift, parString.indexOf(secoundString))
  }

  /**
    * 将字符串转换为List集合
    *
    * @param strToListString 将被转换为集合的字符串
    * @return String类型的集合
    */
  private def strToList(strToListString: String): List[String] = {
    strToListString.replace("\r\n", "").trim.split(",").toList
  }

  /**
    * List集合验重
    *
    * @param list       将被校验的list
    * @param typeString 涉及SQL语句类型
    */
  private def judgeIsEquals(list: List[String], typeString: String): Unit = {
    val tmpListResult: Boolean = list.distinct.size != list.size
    typeString.toUpperCase() match {
      case "SELECT" => if ( tmpListResult ) println("查询语句有重复值")
      case "INSERT" => if ( tmpListResult ) println("插入语句有重复值")
      case "RESULT" => if ( tmpListResult ) println("结果集有重复值")
      case _ => println("暂不支持该SQL语句验重")
    }
  }

  /**
    * 比较SQL中字段数量
    *
    * @param firstNum   第一个需要比较字段的数量
    * @param secoundNum 第二个需要比较字段的数量
    * @param typeString SQL类型
    */
  private def judgeFieldsNums(firstNum: Integer, secoundNum: Integer, typeString: String): Unit = {
    var delNums = firstNum - secoundNum
    val delNumsCompare = firstNum.compareTo(secoundNum)
    (typeString.toUpperCase(), delNumsCompare) match {
      case ("SELECT", -1) =>
        delNums = -delNums
        println(s"结果集 多了[$delNums]个数据")
      case ("SELECT", 1) =>
        println(s"SELECT 多了[$delNums]个数据")
      case ("SELECT", 0) =>
        println(s"SELECT语句与结果集中字段数量相等")
      case ("INSERT", -1) =>
        delNums = -delNums
        println(s"VALUES 多了[$delNums]个数据")
      case ("INSERT", 1) =>
        println(s"INSERT 多了[$delNums]个数据")
      case ("INSERT", 0) =>
        println(s"INSERT语句中字段字段数量相等")
      case ("INSERTANDSELECT", -1) =>
        delNums = -delNums
        println(s"INSERT 多了[$delNums]个数据")
      case ("INSERTANDSELECT", 1) =>
        println(s"SELECT 多了[$delNums]个数据")
      case ("INSERTANDSELECT", 0) =>
        println(s"INSERT语句和SELCECT语句中字段数量相等")
      case _ => println("暂不支持该SQL语句比较字段数量")
    }
  }

  /**
    * 判断字段是否相等
    *
    * @param firstList   第一个待比较集合
    * @param secoundList 第二个待比较集合
    * @param typeString  SQL类型
    */
  private def judgeFields(firstList: List[String], secoundList: List[String], typeString: String): Unit = {
    val selectSurplus = firstList.toSet -- secoundList.toSet
    val insertSubFrontSurplus = secoundList.toSet -- firstList.toSet
    typeString.toUpperCase() match {
      case "SELECT" =>
        if ( selectSurplus.nonEmpty ) {
          println("select语句中比结果集多的字段有" + selectSurplus)
        }
        if ( insertSubFrontSurplus.nonEmpty ) {
          println("结果集中比select语句多的字段有" + insertSubFrontSurplus)
        }
        if ( selectSurplus.isEmpty && insertSubFrontSurplus.isEmpty ) {
          println("select语句中与结果集字段相同")
        }
      case "INSERTANDSELECT" =>
        if ( selectSurplus.nonEmpty ) {
          println("select语句中比insert多的字段有" + selectSurplus)
        }
        if ( insertSubFrontSurplus.nonEmpty ) {
          println("insert语句中比select多的字段有" + insertSubFrontSurplus)
        }
        if ( selectSurplus.isEmpty && insertSubFrontSurplus.isEmpty ) {
          println("insert语句中与select语句中的字段相同")
        }
    }
  }
}

　　

 

　　2. 支持的校验类型：现支持三种校验

 (1).select 查询校验：select语句与结果集比对
 (2).insert into select 查询插入校验：查询语句与插入语句
 (3).insert into 插入校验：insert字段与values中的值数量