scala读取csv文件批量插入数据库

待我称王封你为后i 2022-05-17 10:48 200阅读 0赞

import java.io.FileWriter
    import scala.io.Source
    
    
    
    /** * 通过CSV文件，及其预处理的字段类型字符串，输出insert语句 */
    class csv2InsertSQLServer {
      /** * @param CLASS_PATH 输入/输出路径 
    * @param inFilename 输入文件名 
    * @param tableName 表名 
    * @param headType 表字段的类型，以竖线（|）分割 */
      def mkSQL(CLASS_PATH: String, inFilename: String, tableName: String, headType: String): Unit = {
        //输入/输出文件
        val IN_FILE_NAME = CLASS_PATH + "\\inData\\" + inFilename
        val OUT_FILE_NAME = CLASS_PATH + "\\outData\\" + inFilename //SQL字符串
        val inSqlString: StringBuilder = new StringBuilder //读取文件（CSV）
        val sourceFile = Source.fromFile(IN_FILE_NAME, "utf-8") //解析字段类型
        val headTypeList: Array[String] = headType.split("\\|")
        //拼装SQL
        inSqlString.append("insert into " + tableName + " values ")
        for (line <- sourceFile.getLines) {
          inSqlString.append("(")
          val lineList: Array[String] = line.split(",")
          val len = lineList.length for (i <- 0 to len - 1) {
            //null值处理
            if (lineList(i) == "\\N" || lineList(i) == "Null" || lineList(i) == "\\NuN") inSqlString.append("null,")
            //字符类型的字段拼装时候加引号
            else if (headTypeList(i).contains("varchar") || headTypeList(i).contains("string")) inSqlString.append("\"" + lineList(i) + "\",") else inSqlString.append(lineList(i) + ",")
          }
          inSqlString.deleteCharAt(inSqlString.length - 1)
          inSqlString.append("),\n")
        }
        val outFileWriter = new FileWriter(OUT_FILE_NAME)
        outFileWriter.write(inSqlString.deleteCharAt(inSqlString.length - 1).toString() + "\n")
        println(inSqlString.deleteCharAt(inSqlString.length - 1))
        outFileWriter.close()
        sourceFile.close()
      }
    }