package utils

import java.lang.reflect.Field
import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet, ResultSetMetaData, Statement}

import com.alibaba.fastjson.JSONObject
import com.google.common.base.CaseFormat
import config.MyConfigSession

import scala.collection.mutable.ListBuffer

object MySqlUtil {


  def main(args: Array[String]): Unit = {
//     val mysql_tablename = "r_project_doctor_certificate_log"
//      val hive_table="pica.pica_project_x_gpr_doctor_certificate"
//      val mc_table="pica.dws_gpranking_x_gpr_doctor_certificate"
//    val list: java.util.List[JSONObject] = queryList(s"show create table ${mysql_tablename}"
//      ,MyConfigSession.mysql_url_user_profile)
//    import collection.JavaConverters._
//    val profileListScala = list.asScala
//    println("profileListScala"+profileListScala)
////    var index = 0  //插入新增标识
////    val tbbuffer = new StringBuilder
////    val tbbuffer2 = new StringBuilder
////    tbbuffer.append("where ")
////    for(profile <- profileListScala) {
////      val s: JSONObject = profile
////
////      if (!profile.getString("COLUMN_NAME").equals("id") && !profile.getString("COLUMN_NAME").contains("log_date") && !profile.getString("COLUMN_NAME").contains("times")
////        && !profile.getString("COLUMN_NAME").contains("delete_flag") && !profile.getString("COLUMN_NAME").contains("created_id")
////        && !profile.getString("COLUMN_NAME").contains("created_time") && !profile.getString("COLUMN_NAME").contains("modified_id")
////        && !profile.getString("COLUMN_NAME").contains("modified_time")) {
////          if (index != 0) {
////            tbbuffer.append("\n")
////            tbbuffer.append("or  ")
////          }
////          if (profile.getString("COLUMN_TYPE").contains("varchar")) {
////            tbbuffer.append(s"NVL(A.${profile.getString("COLUMN_NAME")}, '') != NVL(B.${profile.getString("COLUMN_NAME")}, '')")
////          } else if (profile.getString("COLUMN_TYPE").contains("int")) {
////            tbbuffer.append(s"NVL(A.${profile.getString("COLUMN_NAME")}, -1) != NVL(B.${profile.getString("COLUMN_NAME")}, -1)")
////          } else {
////            tbbuffer.append(s"NVL(A.${profile.getString("COLUMN_NAME")}, '') != NVL(B.${profile.getString("COLUMN_NAME")}, '')")
////          }
////          tbbuffer2.append(s"case when NVL(A.${profile.getString("COLUMN_NAME")}, '') != NVL(B.${profile.getString("COLUMN_NAME")}, '') THEN concat(NVL(A.${profile.getString("COLUMN_NAME")}, 'null'),'##',NVL(B.${profile.getString("COLUMN_NAME")}, 'null'))  END AS ${profile.getString("COLUMN_NAME")} , \n")
////
////          index += 1
////        }
////
////    }
//////    println(tbbuffer)
//////    println(tbbuffer2)
////
////    val tbbuffer3 = new StringBuilder
////    tbbuffer3.append("select ").append(tbbuffer2.toString())
////      .append(s"from ${hive_table} as A \n join ${mc_table} as b \n")
////      .append(tbbuffer)
////    println(tbbuffer3)





    }

//  private val properties: Properties = MyPropertiesUtil.load("myconfig.properties")


  val MYSQL_URL = MyConfigSession.mysql_url_user_profile
  val MYSQL_USERNAME = MyConfigSession.mysql_username_user_profile
  val MYSQL_PASSWORD = MyConfigSession.mysql_password_user_profile

//  val MYSQL_URL = "jdbc:mysql://rm-nf9p0m7e89sji2n8ijo.mysql.rds.aliyuncs.com:3306/pica_report?useTimezone=true&serverTimezone=GMT%2B8"
//    val MYSQL_USERNAME = "pica_report_w"
//    val MYSQL_PASSWORD = "lsHB!%@cPGJOfw0E"


  def queryList(sql: String,mySqlUrl:String): java.util.List[JSONObject] = {
    Class.forName("com.mysql.jdbc.Driver")
    //创建结果列表
    val resultList: java.util.List[JSONObject] = new java.util.ArrayList[JSONObject]()

    println("mySqlUrl:"+mySqlUrl)
    println("MYSQL_USERNAME:"+MYSQL_USERNAME)
    println("MYSQL_PASSWORD:"+MYSQL_PASSWORD)


    //创建连接
    val conn: Connection = DriverManager.getConnection(mySqlUrl, MYSQL_USERNAME, MYSQL_PASSWORD)
    //创建会话
    val stat: Statement = conn.createStatement
//    println(sql)
    //提交sql 返回结果
    val rs: ResultSet = stat.executeQuery(sql)
    //为了获得列名 要取得元数据
    val md: ResultSetMetaData = rs.getMetaData
    while (rs.next) {
      val rowData = new JSONObject();
      for (i <- 1 to md.getColumnCount) {
        // 根据下标得到对应元数据中的字段名，以及结果中值
        rowData.put(md.getColumnName(i), rs.getObject(i))
      }
      resultList.add(rowData)
    }

    stat.close()
    conn.close()
    resultList
  }


  def queryList[T<:AnyRef](sql: String, clazz: Class[T], underScoreToCamel: Boolean):List[T]={

    Class.forName("com.mysql.jdbc.Driver");
    val resultList: ListBuffer[T] = new ListBuffer[T]();
    val connection: Connection = DriverManager.getConnection(MYSQL_URL, MYSQL_USERNAME, MYSQL_PASSWORD)
    val stat: Statement = connection.createStatement();
    val rs: ResultSet = stat.executeQuery(sql);
    val md: ResultSetMetaData = rs.getMetaData();
    while (rs.next()) {
      val obj: T = clazz.newInstance()
      for (i <- 1 to md.getColumnCount) {
        var propertyName: String = md.getColumnLabel(i)

        if (underScoreToCamel) {
          propertyName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, md.getColumnLabel(i))
        }
        MyBeanUtils.setV(obj,propertyName,rs.getObject(i))

      }
      resultList.append(obj);
    }
    stat.close()
    connection.close()
    resultList.toList

  }

  def queryOne[T<:AnyRef](sql: String, clazz: Class[T] ,
                          underScoreToCamel: Boolean,mySql_URL:String): Option[T] ={
    println(sql)
    Class.forName("com.mysql.jdbc.Driver");
    val resultList: ListBuffer[T] = new ListBuffer[T]();
    val connection: Connection =
      DriverManager.getConnection(mySql_URL, MYSQL_USERNAME, MYSQL_PASSWORD)
    val stat: Statement = connection.createStatement();
    val rs: ResultSet = stat.executeQuery(sql);
    val md: ResultSetMetaData = rs.getMetaData();
    while (rs.next()) {
      val obj: T = clazz.newInstance()


      for (i <- 1 to md.getColumnCount) {
        var propertyName: String = md.getColumnName(i)
        if (underScoreToCamel) {
          propertyName =
            CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, md.getColumnName(i))
        }
        MyBeanUtils.setV(obj,propertyName,rs.getObject(i))
      }
      resultList.append(obj);
    }
    stat.close()
    connection.close()
    if(resultList!=null){
      Some(resultList(0))
    }else{
      None
    }

  }


  def queryOne[T<:AnyRef](sql: String,mySql_URL:String): Option[JSONObject] ={
    Class.forName("com.mysql.jdbc.Driver")
    //创建结果列表
    val resultList: java.util.List[JSONObject] = new java.util.ArrayList[JSONObject]()
    //创建连接
    val conn: Connection = DriverManager.getConnection(mySql_URL, MYSQL_USERNAME, MYSQL_PASSWORD)
    //创建会话
    val stat: Statement = conn.createStatement
    println(sql)
    //提交sql 返回结果
    val rs: ResultSet = stat.executeQuery(sql)
    //为了获得列名 要取得元数据
    val md: ResultSetMetaData = rs.getMetaData
    while (rs.next) {
      val rowData = new JSONObject();
      for (i <- 1 to md.getColumnCount) {
        // 根据下标得到对应元数据中的字段名，以及结果中值
        rowData.put(md.getColumnName(i), rs.getObject(i))
      }
      resultList.add(rowData)
    }

    stat.close()
    conn.close()

    if(resultList!=null){
      Some(resultList.get(0))
    }else{
      None
    }



  }

  def insertOne[T](sql: String, obj:T ): Unit ={
    Class.forName("com.mysql.jdbc.Driver");
    val resultList: ListBuffer[T] = new ListBuffer[T]();
    val connection: Connection =
      DriverManager.getConnection(MYSQL_URL, MYSQL_USERNAME, MYSQL_PASSWORD)
    val pstat: PreparedStatement = connection.prepareStatement(sql)
    val fields: Array[Field] = obj.getClass.getDeclaredFields

    for(i<- 1 to fields.size){
      val field:Field = fields(i-1);
      field.setAccessible(true)
      val value: AnyRef = field.get(obj)
      pstat.setObject(i,value)
    }
    val resInsert = pstat.execute()
    println("插入数据，返回值=>" + resInsert)

  }



}
