Halo数据库JDBC兼容部分Oracle功能简单演示

JDBC是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC 有两部分组成：JDBC API 和 JDBC Driver Interface，提供数据库建立连接、执行sql语句、处理结果。

一、Halo兼容oracleBlob类型使用

创建表
create table testblobbytea(blobasbytea bytes);

数据库URL增加blobAsBytea=true开启转换

public static byte[] testblob(Connection c, Statement stmt) throws SQLException ,IOException {
InputStream in = null;
ByteArrayOutputStream baos = null;
try {
c.setAutoCommit(false);
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("select * from testblobbytea");
while (rs.next()) {
Blob blob = rs.getBlob("blobasbytea");
in = blob.getBinaryStream();
baos = new ByteArrayOutputStream();
byte[] buf = new byte[512];
int len = -1;
while ((len = in.read(buf)) != -1) {
baos .write(buf,0, len ) ;
}
}
return baos.toByteArray();

} finally {
if (in != null){
in.close();
}
if (baos != null){
in.close();
}
}
}

二、Halo兼容oracleClob类型使用

创建表
create table clob1(clobtest text);

数据库URL增加clobAsText=true开启转换

public static void clob(Connection c, Statement stmt) throws SQLException ,IOException {

try {
c.setAutoCommit(false);
String body ="";
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("select * from clob1");
while (rs.next()) {

Clob clob = rs.getClob(1);
Reader is= clob.getCharacterStream();
BufferedReader br =new BufferedReader(is);
String message = br.readLine();
StringBuffer sb = new StringBuffer();
while(message!=null){
sb.append(message);
message = br.readLine();
}
body = sb.toString();
}
System.out.println(body);
stmt.close();
rs.close();
c.close();
} catch (Exception e) {
e.printStackTrace();
}
}

三、支持Oracle的ArrayDescriptor、StructDescriptor使用

public static void testArrayDescriptor(Connection c) throws SQLException ,IOException {
PreparedStatement pres = null;
try {
pres = c.prepareStatement("Select Ls.Zsxm_Dm, Ls.Swjg_Dm From table(Cast(? As Hx_Zgxt.PARAM_ZS_RKXHXX_ARRAYList)) Ls");
List strucList = new ArrayList();
Object[] a = new Object[2];
a[0] = "31";
a[1] = "43";
strucList.add(a);
STRUCT[] struct1 = new STRUCT[strucList.size()];
for (Object[] objs : ((ArrayList<Object[]>) strucList)) {
StructDescriptor structdesc = StructDescriptor.createDescriptor("hx_zgxt.param_zs_rkxhxx", c);
struct1[0] = new STRUCT(structdesc, c, objs);
}
ArrayDescriptor des = ArrayDescriptor.createDescriptor("hx_zgxt.param_zs_rkxhxx_arraylist", c);
ARRAY inputArray = new ARRAY(des, c, struct1);
pres.setArray(1, inputArray);
ResultSet resultSet = pres.executeQuery();
} finally {
stmt.close();
c.close();
}
}

四、Timestamp时间转换为二进制形式

数据库URL增加binaryTransfer=true开启二进制

public static void testtrunc(Connection c){
PreparedStatement pst = null;
try {
java.util.Date utilDate= new java.util.Date();
long datems = utilDate.getTime();
java.sql.Date sqlDate = new java.sql.Date(datems);
Timestamp ts = new Timestamp(sqlDate.getTime());
String sql = "select * from data1 where date1 <=trunc(?)+1";
pst = c.prepareStatement(sql);
pst.setTimestamp(1,ts);
ResultSet res = pst.executeQuery();
while (res.next()){
System.out.println(res.getObject(1));
}
c.commit();
pst.close();
c.close();
} catch (Exception e) {
e.printStackTrace();
}
}