String content = "null";
try {
Class.forName(this.sDBDriver);
Connection conn = DriverManager.getConnection(this.url,
this.user,
this.pwd);
conn.setAutoCommit(false);
ResultSet rs = null;
CLOB clob = null;
String sql = "";
sql = "select Report from User_CourseWare where user_id =?and courseware_id =?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, user_id);
pstmt.setInt(2, courseware_id);
rs = pstmt.executeQuery();
if (rs.next()) {
clob = (CLOB) rs.getClob(1);
if (clob != null && clob.length() != 0) {
content = clob.getSubString((long) 1, (int) clob.length());
content = this.Clob2String(clob);
}
}
rs.close();
conn.commit();
pstmt.close();
conn.close();
} catch (ClassNotFoundException
e) {
e.printStackTrace();
//
return "null";
content = "error";
} catch (SQLException e) {
e.printStackTrace();
//
return "null";
content = "error";
}
return content;
}
/*
clob
to string
大字符串格式转换STRING
@param
clob
@return
大字符串
*/
public String
Clob2String(CLOB clob) {
//Clob转换成String 的方法
String content = null;
StringBuffer stringBuf = new StringBuffer();
try {
int length = 0;
Reader inStream = clob.getCharacterStream();
//取得大字侧段对象数据输出流
char[] buffer = new char[10];
while ((length = inStream.read(buffer)) != -1)
// 读取数据库 //每10个10个读取
{
for (int i = 0; i < length; i++) {
stringBuf.append(buffer[i]);
}
}
inStream.close();
content = stringBuf.toString();
} catch (Exception
ex) {
System.out.println("ClobUtil.Clob2String:" +
ex.getMessage());
}
return content;
}
/*
更新Clob(大字符串格式)内容
*/
public String updateClob(int userid,
int courseware_Id,
int Progress,
String CourseClob) {
this.updateUser_Course(userid, courseware_Id, Progress);
//调用更新进度
try {
Class.forName(this.sDBDriver);
Connection conn = DriverManager.getConnection(this.url,
this.user,
this.pwd);
String sql = "update User_CourseWare set Report = empty_clob(), Progress =?where User_Id = ?and Courseware_Id =?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, Progress);
pstmt.setInt(2, userid);
pstmt.setInt(3, courseware_Id);
int i1 = pstmt.executeUpdate();
conn.commit();
//
System.out.println("update sql=" + sql);
pstmt = null;
if (i1 > 0) {
}
ResultSet rs = null;
CLOB clob = null;
String sql1 = "select Report from User_CourseWare where User_id =?and Courseware_id =?for update ";
pstmt = conn.prepareStatement(sql1);
//
System.out.println("select sql=" + sql1);
pstmt.setInt(1, userid);
pstmt.setInt(2, courseware_Id);
rs = pstmt.executeQuery();
if (rs.next()) {
clob = (CLOB) rs.getClob(1);
}
Writer writer = clob.getCharacterOutputStream();
writer.write(CourseClob);
writer.flush();
writer.close();
rs.close();
conn.commit();
pstmt.close();
conn.close();
} catch (Exception
e) {
e.printStackTrace();
return "error";
}
return "success";
}
}
}
https://blog.csdn.net/qq_25106373/article/details/80946549
获取返回的表字段类型,通过类型获取clob值,特殊处理
public void test1(){
//1. 获取连接
Connection conn = null;
PreparedStatement ps = null;
//4. 执行 SQL(获取 ResultSet : 结果集)
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2. 获取 PreparedStatement,用于发送 SQL
String sql = "select id, name, email, birth from customers where id = ?";
ps = conn.prepareStatement(sql);
//3. 填充占位符
ps.setInt(1, 16);
rs = ps.executeQuery();
//5. 获取结果集数据
if(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
System.out.println(id + "," + name + "," + email + "," + birth);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭连接
JDBCUtils.close(rs, ps, conn);
}
}
/*
* ORM (Object Relateional Mapping) : 对象关系映射
*
* 数据表中一张表 ---- Java 一个类
* 数据表中一个字段 ---- Java 一个属性
* 数据表中一条数据 ---- Java 一个对象
*
*/
//针对 Order 表写一个查询一个 Order 对象并返回的方法,以及一堆 Order 对象并返回的方法
@Test
public void test2(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id, name, email, birth from customers where id < ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 16);
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, ps, conn);
}
}
@Test
public void test4(){
String sql = "select id, name, email, birth from customers where id = ?";
Customer cust = get2(sql, Customer.class, 10);
System.out.println(cust);
System.out.println("-----------------------------------------------");
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = get2(sql2, Order.class, 2);
System.out.println(order);
}
public <T> T get2(String sql, Class<T> clazz, Object ... args){//get2(sql, Order.class)
T t = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
//获取当前结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()){
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnName = rsmd.getColumnLabel(i+1);
//根据列名获取对应列的数据
Object columnValue = rs.getObject(columnName);
Field field = clazz.getDeclaredField(columnName);//注意:必须保证结果集中列名(别名),与属性名称保持一致!!!!!
field.setAccessible(true);//忽略访问权限
field.set(t, columnValue);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, ps, conn);
}
return t;
}
/*
* **通用查询:
*
* 1. 需要返回一个什么类型的对象,不能确定?
* 泛型:确定返回值类型
* 反射:确定返回的对象
*
* 2. 对于结果集的处理,不能确定?
* ResultSetMetaData : 结果集的元数据
* getColumnCount() : 返回结果集的列数
* getColumnName() : 返回结果集的列名
* getColumnLabel() : 返回结果集列的别名(有别名获取别名,没别名获取列名)
*/
@Test
public void test5(){
String sql = "select id, name, email, birth from customers where id < ?";
List<Customer> list = getList(sql, Customer.class, 16);
for (Customer customer : list) {
System.out.println(customer);
}
System.out.println("----------------------------------------------------");
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id < ?";
List<Order> list2 = getList(sql2, Order.class, 5);
for (Order order : list2) {
System.out.println(order);
}
}
public <T> List<T> getList(String sql, Class<T> clazz, Object... args) {
List<T> list = new ArrayList<>();
// 1. 获取连接
Connection conn = null;
// 2. 获取 PreparedStatement, 用于发送 SQL
PreparedStatement ps = null;
// 4. 执行 SQL, 获取 ResultSet 结果集
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
// 3. 根据可变参数填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 5. 获取当前结果集的元数据 ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 6. 获取结果集的列数
int columnCount = rsmd.getColumnCount();
// 7. 获取结果集中数据
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 7.1 根据元数据获取列名
String columnName = rsmd.getColumnLabel(i + 1);
// 7.2 根据列名获取对应列的值
Object columnValue = rs.getObject(columnName);
// 7.3 将获取的列值封装进对象
PropertyUtils.setProperty(t, columnName, columnValue);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 8. 关闭连接
JDBCUtils.close(rs, ps, conn);
}
return list;
}
@Test
public void test1(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into emp values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i+1);
ps.setString(2, "emp_" + i);
//执行 SQL
ps.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
}
//批量处理
@Test
public void test2(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into emp values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i+1);
ps.setString(2, "emp_" + i);
//积攒 SQL
ps.addBatch();
if((i+1) % 1000 == 0){
//执行 SQL
ps.executeBatch();
//清空 SQL
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}