postgres数据库实现存在则更新，不存在则插入的方法

实现记录存在时，count字段自动累计1。

INSERT INTO t_sjwh_spark_jj
VALUES
    ('pgKey', 0, jjpl) ON conflict ( pgmxid ) DO
UPDATE 
    SET "count" = t_sjwh_spark_jj."count" + 1

实现记录存在时，count字段更新。

INSERT INTO t_sjwh_spark_jj
VALUES
    ('pgKey', 0, jjpl) ON conflict ( pgmxid ) DO
UPDATE 
    SET "count" = '5'

注意：conflict后面的主键字段一定要保持其唯一性(unique)。

完整代码如下，动态匹配id进行数据库的更新插入操作：

object GatherPostgres extends Serializable {

    lazy private val sdf = new SimpleDateFormat("yyyy-MM-dd")
    lazy private val todayDate = sdf.format(new Date)//系统当前时间

    def insertIntoPg(mxid:String,jjpl:Int): Unit ={
      //拼装id
      val pgKey = mxid + "\001" + todayDate

      //如果存在则count加1，如果不存在则插入数据库
      val insertPgSql =
        s"""
          |INSERT INTO t_sjwh_spark_jj
          |VALUES
          | ('${ pgKey}', 0, ${ jjpl} ) ON conflict ( pgmxid ) DO
          |UPDATE
          | SET "count" = t_sjwh_spark_jj."count" + 1
        """.stripMargin

      //获得连接池资源
      val conn = DruidTool.getConnection.orNull
      if (conn != null) {
        //把数据存入PG库
        val insertpst: PreparedStatement = conn.prepareStatement(insertPgSql)
        try {
          //设置批量提交
          conn.setAutoCommit(false)
          insertpst.addBatch()
          insertpst.executeBatch()
          conn.commit()
        } catch {
          case ex: Exception => println(ex.getMessage)
        } finally {
          insertpst.close()
          DruidTool.closeConnection(conn)
        }
      }
    }
}

/**
 * 数据库连接池工具类
 */
object DruidTool extends Serializable {
 
    //连接池配置
  
    private val LOG = Logger.getLogger(DruidTool.getClass.getName)
    
    private val dataSource: Option[DataSource] = {
      
      try{
        val druidProps = new Properties() 
        //获取Druid连接池配置文件
        val druidConfig = getClass.getResourceAsStream("/druid.properties")   
        druidProps.load(druidConfig)
        Some(DruidDataSourceFactory.createDataSource(druidProps))
      }catch{
        case exception:Exception =>
          LOG.error("Create Connection Error: \n",exception)
          None
      } 
    }   
    
      //获取连接池连接
     def getConnection : Option[Connection] = {
          dataSource match{
            case Some(pool) => Some(pool.getConnection)
            case None =>None
          }    
      }
      
     //关闭数据库连接
     def closeConnection (connection:Connection):Unit={
        if (!connection.isClosed){
          connection.close()
        }
      }
}

druid.properties配置信息

driverClassName =org.postgresql.Driver
url =jdbc:postgresql://192.1111.111.111:5432/database?currentSchema=table
username =postgres
password =postgres
initialSzie =5
maxActive =10
maxWait =60000
maxldle =6
minldle =3