侧边栏壁纸
  • 累计撰写 131 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

【openGauss】如何将数据库中的自定义复合类型绑定到java的class中

DarkAthena
2023-05-21 / 0 评论 / 0 点赞 / 408 阅读 / 11905 字

前言

某客户,将应用的数据库从oracle迁移到openGauss系,源库中使用了大量的自定义type,所以java代码里也有很多对type对象的处理。在oracle中可以直接传对象给数据库,但是openGauss/postgresql的源码用例中,都是通过PGobject.setValue,把对象中的所有值拼成一个字符串,用逗号分隔,用括号括起来。如果对象中的某个值可能存在有逗号,那么就会出现异常。

我以前没正经写过java,只能看懂个大概意思,当然,依葫芦画瓢也没啥问题。现在有chatGPT,只要思路清晰,应该也能写出来个解决方案。

问题表现

在源码中 ,有如何使用自定义复合类型的示例
\openGauss-connector-jdbc\pgjdbc\src\test\java\org\postgresql\test\jdbc3\CompositeTest.java

@Before
  public void setUp() throws Exception {
    _conn = TestUtil.openDB();
    TestUtil.createSchema(_conn, "\"Composites\"");
    TestUtil.createCompositeType(_conn, "simplecompositetest", "i int, d decimal, u uuid");
    TestUtil.createCompositeType(_conn, "nestedcompositetest", "t text, s simplecompositetest");
    TestUtil.createCompositeType(_conn, "\"Composites\".\"ComplexCompositeTest\"",
        "l bigint[], n nestedcompositetest[], s simplecompositetest");
    TestUtil.createTable(_conn, "compositetabletest",
        "s simplecompositetest, cc \"Composites\".\"ComplexCompositeTest\"[]");
    TestUtil.createTable(_conn, "\"Composites\".\"Table\"",
        "s simplecompositetest, cc \"Composites\".\"ComplexCompositeTest\"[]");
  }

 @Test
  public void testSimpleArgumentSelect() throws SQLException {
    PreparedStatement pstmt = _conn.prepareStatement("SELECT ?");
    PGobject pgo = new PGobject();
    pgo.setType("simplecompositetest");
    pgo.setValue("(1,2.2,)");
    pstmt.setObject(1, pgo);
    ResultSet rs = pstmt.executeQuery();
    assertTrue(rs.next());
    PGobject pgo2 = (PGobject) rs.getObject(1);
    assertEquals(pgo, pgo2);
  }

这里关键的代码在下面这4行

PGobject pgo = new PGobject();  // new一个PGobject()对象
    pgo.setType("simplecompositetest");  //将这个对象设置成自定义类型
    pgo.setValue("(1,2.2,)");  //设置这个对象的值
    pstmt.setObject(1, pgo); //把这个对象绑定到sql的变量里

这里其实就有点难受了,simplecompositetest这个类型在数据库中已经定义,而且真实的业务代码中,java里往往会建一个对应的class,数据应该在这个class的对象里,而此处却需要将业务对象转换成字符串,再塞到PGobject对象里后,才能绑定变量到sql。

ORACLE怎么做的

You: 
在oracle里创建自定义类型,然后在java里怎么使用这个自定义类型进行绑定变量的sql执行?

Bot:
在Oracle里创建自定义类型,你可以使用CREATE TYPE语句,例如:

CREATE TYPE person_t AS OBJECT (
  name VARCHAR2(50),
  age NUMBER
);
这样就创建了一个名为person_t的自定义类型,它有两个属性:name和age。

在Java里使用这个自定义类型进行绑定变量的SQL执行,你可以使用OraclePreparedStatement和它的方法setObjectAtName(),例如:

//假设你已经建立了一个数据库连接conn
String sql = "INSERT INTO person VALUES (:p)";
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql);
//创建一个person_t类型的对象
Object[] person = new Object[2];
person[0] = "张三";
person[1] = 25;
//将对象绑定到SQL语句中的变量p
stmt.setObjectAtName("p", person);
//执行SQL语句
stmt.executeUpdate();
这样就可以在Java里使用自定义类型进行绑定变量的SQL执行了。^1^ ^2^ ^3^

希望这些信息对你有用。👍

oracle 里new的这个object,可以直接绑定到sql的变量里

openGauss里怎么处理?

由于是连续对话,代码太长了,所以这里只截取关键的

You: 
我想定义一个class,能和数据库中的自定义类型ty_test对应上,
因为setValue只能接受逗号分隔的字符串输入,如果某个值本身可能
存在逗号或其他特殊符号,就会执行失败 ,ty_test的代码为 
create type ty_test as (col1 text, col2 text, col3 int);

Bot:
我理解您的需求。如果您想定义一个class,能和数据库中的自定义类型ty_test对应上,
您可以使用org.opengauss.util.PGobject类的子类,并重写它的setValue()和getValue()方法,以实现您自己的逻辑。例如:

// Assume we have a custom composite type called ty_test with three fields col1, col2 and col3
public class TY_TEST extends PGobject {
  private String col1;
  private String col2;
  private int col3;

  public TY_TEST() {
    setType("ty_test"); // Set the type name in the database
  }

  public TY_TEST(String col1, String col2, int col3) {
    this();
    this.col1 = col1;
    this.col2 = col2;
    this.col3 = col3;
  }

  @Override
  public void setValue(String value) throws SQLException {
    // Parse the string value and assign to the fields
    // You can use your own logic to handle special characters
    // For example, you can use quotation marks to enclose the values
    if (value != null && value.startsWith("(") && value.endsWith(")")) {
      value = value.substring(1, value.length() - 1); // Remove the parentheses
      String[] parts = value.split(","); // Split by comma
      if (parts.length == 3) {
        this.col1 = parts[0].replaceAll("\"", ""); // Remove the quotation marks
        this.col2 = parts[1].replaceAll("\"", "");
        this.col3 = Integer.parseInt(parts[2]);
      } else {
        throw new SQLException("Invalid value for ty_test: " + value);
      }
    } else {
      throw new SQLException("Invalid value for ty_test: " + value);
    }
  }

  @Override
  public String getValue() {
    // Return a string representation of the object
    // You can use your own logic to handle special characters
    // For example, you can use quotation marks to enclose the values
    return "(" + "\"" + col1 + "\"" + "," + "\"" + col2 + "\"" + "," + col3 + ")";
  }
}

// Then you can use the TY_TEST class as the parameter
TY_TEST values = new TY_TEST("abc", "def", 1);
pstmt.setObject(1, values);
希望这些信息对您有所帮助。

这段代码的几个关键点

  1. extends PGobject
  2. setType(“ty_test”);
  3. this();
  4. getValue()

我测了下,其实setValue可以去掉,因为本身就是用的java的类,不需要用字符串去传,所以整理好可以执行的代码如下

import java.sql.*;
import org.opengauss.util.PGobject;

//create type ty_test as (col1 text, col2 text, col3 int);

class TY_TEST extends PGobject{
  private String col1;
  private  String col2;
  private  int col3;
  
  public TY_TEST() {
    setType("ty_test"); 
  }

  public TY_TEST(String i_col1,String i_col2, int i_col3){
    this();
    this.col1=i_col1;
    this.col2=i_col2;
    this.col3=i_col3;
  }

  @Override
  public String getValue() {
    return "(" + "\"" + col1 + "\"" + "," + "\"" + col2 + "\"" + "," + col3 + ")";
  }
}

public class test_type4 {

  static final String jdbcString = "org.opengauss.Driver";
  static final String urlString = "jdbc:opengauss://192.168.1.115:26000/postgres?batchMode=off";
  static final String userName = "xxxxxxxx";
  static final String password = "xxxxxxxx";

  /**
 * @param args
 * @throws Exception
 */
public static void main(String[] args) throws SQLException {
  Connection _conn = DriverManager.getConnection(urlString, userName, password);
  PreparedStatement pstmt = _conn.prepareStatement("SELECT ?");
  //PGobject pgo = new PGobject();
  //pgo.setType("ty_test");
  //pgo.setValue("(abc,def,1)");
  TY_TEST values = new TY_TEST("abc","de,f",1);
  pstmt.setObject(1, values);
  ResultSet rs = pstmt.executeQuery();
  rs.next();
  System.out.println(rs.getString(1));
  rs.close();
  pstmt.close();
  _conn.close();
}
}

执行执行输出为

(abc,“de,f”,1)

这样就不需要再去转换了。但是,实际上这个原理,还是转成了字符串,只是在字符类型的值上加了双引号。所以就算不扩展PGobject,也可以写段代码转换成字符串,比如

  @Override public String toString(){
    return "(\""+col1+"\",\""+col2+"\",\""+col3+"\")";
  }
  PGobject pgo = new PGobject();
  pgo.setType("ty_test");
  TY_TEST values = new TY_TEST("abc","d,ef",1);
  pgo.setValue(values.toString());
  pstmt.setObject(1, pgo);

不过这样就多了几行代码,而且setType可能都会重复出现,还是扩展PGobject更干净。

其他问题

目前已知这个方案,其实就是加双引号再拼字符串,那么如果值里本身就有双引号,该如何处理呢?
当然,这里最简单的就是给双引号加转义了

import java.sql.*;
import org.opengauss.util.PGobject;

//create type ty_test as (col1 text, col2 text, col3 int);

class TY_TEST extends PGobject{
  private String col1;
  private  String col2;
  private  int col3;
  
  public TY_TEST() {
    setType("ty_test"); 
  }

  public TY_TEST(String i_col1,String i_col2, int i_col3){
    this();
    this.col1=i_col1;
    this.col2=i_col2;
    this.col3=i_col3;
  }

  @Override
  public String getValue() {
    return "(" + "\"" + col1.replaceAll("\"", "\"\"") + "\"" + "," + "\"" + col2.replaceAll("\"", "\"\"") + "\"" + "," + col3 + ")";
  }
}

public class test_type4 {

  static final String jdbcString = "org.opengauss.Driver";
  static final String urlString = "jdbc:opengauss://192.168.1.115:26000/postgres?batchMode=off";
  static final String userName = "xxxxxxxx";
  static final String password = "xxxxxxxx";

  /**
 * @param args
 * @throws Exception
 */
public static void main(String[] args) throws SQLException {
  Connection _conn = DriverManager.getConnection(urlString, userName, password);
  PreparedStatement pstmt = _conn.prepareStatement("SELECT ?");
  TY_TEST values = new TY_TEST("abc","de,\"f",1);
  pstmt.setObject(1, values);
  ResultSet rs = pstmt.executeQuery();
  rs.next();
  System.out.println(rs.getString(1));
  rs.close();
  pstmt.close();
  PreparedStatement pstmt2 = _conn.prepareStatement("insert into t_ty_test values (?) "); //create table t_ty_test(a ty_test);
  pstmt2.setObject(1, values);
  int rs2 = pstmt2.executeUpdate();
  pstmt2.close();
  _conn.close();
}
}

由于java打印出来的这个值长这样

(abc,“de,”“f”,1)

所以我建了个表,把值插进去看到底是什么样子

MogDB=> select a, (a).col2 from t_ty_test ;
        a         | col2
------------------+-------
 (abc,"de,""f",1) | de,"f
(1 row)

看上去没有问题,显示两个双引号是自动转义出来的。
于是,问题解决,分隔符和字符串标记符都能自动处理了。

虽然但是,还是不得劲

在oracle里代码那么少,openGauss里要实现同样的效果代码量翻了几倍,虽然这些代码规律性很强,可以写个框架自动生成,但是应该有办法通过修改jdbc驱动,实现和oracle相同的用法。
这个课题就抛给感兴趣的人去试试了。

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论