Groovy 读写 postgresql 数据库的JSON字段测试

 POSTGRESQL JDBC 写多个条件 其中的关联是 &

现在JSON是越来越多了，也得研究下这些支持了JSON的数据库到底如何读写JSON了，毕竟关系型还是及其常用的啊

import groovy.json.JsonOutput
import groovy.json.StringEscapeUtils
import groovy.sql.Sql

class read_pg {
    def readpg() {
        def connpg = new conn_pg()
        def conn = connpg.db_conn
        //按照正常数据库字段输出测试
        def sql = """
select a.*,to_timestamp(aa->>'时间' ,'yyyy-mm-ddThh24:mi:ss' )  as time  from  temp_json a where a.aa->>'时间' like '%T%'
"""
        conn.eachRow(sql) {
            println(it)
        }
        println("----------")
        //按照数据库输出JSON字段输出测试
        sql = """
select a.aa from  temp_json a where a.aa->>'时间' like '%T%'
"""
        conn.eachRow(sql) { row ->
            println(row)
            def slurper = new groovy.json.JsonSlurper()
            def json = slurper.parseText(row[0].toString())
            println(json)
            println(json.char)
            //  #将Unicode转为中文
            //  println StringEscapeUtils.unescapeJava(json)
        }
        conn.close()
    }
}

class conn_pg {
    def db = [
            url     : 'jdbc:postgresql://127.0.0.1:5432/postgres?autosave=always&stringtype=unspecified',
            user    : 'postgres',
            password: 'postgres',
            driver  : 'org.postgresql.Driver'
    ]
    def db_conn = Sql.newInstance(db.url, db.user, db.password, db.driver)
}

class write_pg {
    def a = 1
    def sql_create_table = """
create table temp_json
(id uuid PRIMARY KEY DEFAULT  uuid_generate_v5(uuid_generate_v1mc(),'zbunicom'),   ---uuid的唯一索引 
aa jsonb  ---jsonb作为数据类型)
"""

    def writepg(String Json_text) {
        def connpg = new conn_pg()
        def conn = connpg.db_conn
        def sql = """
insert into temp_json
(aa)
select ${Json_text} 
"""
        conn.execute(sql)
        conn.close()
    }
}
//主程序
def runs() {
    /*
    #将中文转为Unicode
#StringEscapeUtils.escapeJava(str)
    * */
    def list = ["number": 1, "char": "Aa我", "时间": new Date()] //时间会是UTC时间不是东八区时间
    println new Date()
    def json = JsonOutput.toJson(list)
    //  #将Unicode转为中文
    println StringEscapeUtils.unescapeJava(json)
    //写数据库测试
    def wpg = new write_pg()
    wpg.writepg(json)
    //读数据库测试
    def rp = new read_pg()
    rp.readpg()
}

static void main(String[] args) {
    runs()
}