java操作gaussDB数据库的实现示例

import java.sql.{Connection, DriverManager}
import java.util.Properties

import org.apache.spark.sql.DataFrame

import Scala.collection.mutable.ArrayBuffer;

object GaussDBUtils {

val urls = application.gaussURL //数据库URL
val username = application.gaussName //用户名
val passWord = application.gaussPass //密码
val database = application.gaussDatabase
val gaussCreateDatabase = application.gaussCreateDatabase
//val driver = "org.postgresql.Driver"
val driver = application.driver
var conn: Connection = null;

def getConnection(): Connection = {
try {
Class.forName(driver)
conn = DriverManager.getConnection(urls, username, password)
conn
} catch {
case e: Throwable => e.printStackTrace()
println("连接gaussDB失败===请检查原因")
conn
}
}

//创建表
def createGaussTable(sql: String): Unit = {
try {
val connection = getConnection()
val statement = connection.createStatement()
statement.executeUpdate(sql)
statement.close()
} catch {
case e: Exception =>
e.printStackTrace()
}

}

//判断是否存在该表
def tableExists(database: String, tablName: String): Boolean = {
val connection = getConnection()
val set = connection.getMetaData.getTables(null, database, tablName, null)
if (set.next()) {
return true
} else {
println("gaussDB中" + tablName + "不存在")
return false
}
}

//创建数据库
def createDatabase(): Unit = {
val connection = getConnection()
val statement = connection.createStatement()
statement.executeUpdate(gaussCreateDatabase)
statement.close()
}

//查询 、执行sql语句
def ReadFromGaussDB(sql: String): Unit = {
try {
val connection = getConnection()
val statement = connection.createStatement()
val set = statement.executeQuery(sql)
while (set.next()) {
val str = set.getString(2)
println(str)
}
}catch {
case e: Exception =>
e.printStackTrace()
}
}

//获取gaussDB中所有表名
def getTablesByGaussDB(db: String) :ArrayBuffer[String]={
val tablesList = new ArrayBuffer[String]()
val connection = getConnection()
val set = connection.getMetaData.getTables(null,db,null,null)
while (set.next()) {
val str: String = set.getString("TABLE_NAME")
tablesList += str
// println(str)
}
tablesList
}

//根据resourceId删除gaussDB数据
def deleteDataByResourceId(sql:String): Unit ={
val connection = getConnection()
val statement = connection.createStatement()
statement.executeUpdate(sql)
}

//插入gaussDB库中
def insertgaussDBTable(table: String, df: DataFrame): Unit = {
val connectionProperties = new Properties()
connectionProperties.setProperty("user", username)
connectionProperties.setProperty("password", password)
println("+++++++开始写入GaussDB++++++" + table + "共有" + df.count())
df.write.mode("append").option("batchsize", "50000").option("isolationLevel", "NONE").
option("numPartitions", "1").jdbc(urls, table, connectionProperties)
println("=================GaussDB完成写入========" + table + "====================")
}