package com.ocean.san;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.CLOB;
public class JDBC {
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user = "scott";
private static String password = "tiger";
private static Connection conn;
private static Connection getConn() {
if (conn == null) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
return conn;
}
/**
* JDBC操作clob类型
*
* @param args
*/
public static void main(String[] args) {
try {
PreparedStatement ps3 = JDBC.getConn().prepareStatement(
"select nvl(max(key)+1,0) key from ex_tab ");
ps3.executeQuery();
ResultSet rs1 = ps3.getResultSet();
int key = 0;
while (rs1.next()) {
key = rs1.getInt("key");
}
if (ps3 != null) {
ps3.close();
}
// 插入空clob
PreparedStatement ps = JDBC.getConn().prepareStatement(
"insert into ex_tab (key,msg) values(?,?)");
ps.setInt(1, key);
ps.setClob(2, CLOB.getEmptyCLOB());
ps.executeUpdate();
if (ps != null) {
ps.close();
}
// 锁定插入的空clob
PreparedStatement ps1 = JDBC.getConn().prepareStatement(
"select msg from ex_tab where key=" + key + " for update");
ps1.executeQuery();
ResultSet rs = ps1.getResultSet();
Clob colb = null;
while (rs.next()) {
colb = rs.getClob("msg");
}
if (ps1 != null) {
ps1.close();
}
// 更新clob
PreparedStatement ps2 = JDBC.getConn().prepareStatement(
"update ex_tab set msg=? where key=1 ");
colb.setString(1, "ooooooooooooooooooooooooooo");
ps2.setClob(1, colb);
ps2.executeUpdate();
if (ps2 != null) {
ps2.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConn();
}
}
static void closeConn() {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}