package org.rain.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 数据库BlOB（二进制大对象）和CLOB（字符型大对象）添加、查询
 */
public class SmallRainDBLOB {
	public static void main(String[] args) {
		Connection smallrainConn = null;
//		Statement smallrainStmt = null;
		PreparedStatement smallrainStmt = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			smallrainConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "smallrain123456",
					"Xiaoyu123456");
			SmallRainDBLOB smallrainDB = new SmallRainDBLOB();

//			插入clob数据String
			smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_CLOB,R_ID) VALUES (?,?)");
			String smallRainclobText = "oracle数据库大数据ClOB";
			StringReader smallrainReaderString = new StringReader(smallRainclobText);
			smallrainStmt.setClob(1, smallrainReaderString);
			smallrainStmt.setInt(2, 333);
			smallrainStmt.executeUpdate();

//			插入clob文件数据,将文本文件放到当前java文件目录下，文件名为 22228.txt
			smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_CLOB,R_ID) VALUES (?,?)");
			Reader smallrainFileReader = new FileReader(smallrainDB.getClass().getResource("22228.txt").getFile());

			smallrainStmt.setCharacterStream(1, smallrainFileReader);
			smallrainStmt.setInt(2, 222);
			smallrainStmt.executeUpdate();

//			插入blob数据
			smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_ID, R_BLOB) VALUES (?,?)");
//			将图片文件放到当前java文件目录下，文件名为 345445.png
			FileInputStream fis = new FileInputStream(smallrainDB.getClass().getResource("345445.png").getFile());
			smallrainStmt.setInt(1, 6666);
			smallrainStmt.setBinaryStream(2, fis);
			smallrainStmt.executeUpdate();
			
			
//			查询BLOB数据、存在本地
			String savePath = "C:/SmallRian";
			File fileSaveDir = new File(savePath);
			if (!fileSaveDir.exists()) {
				fileSaveDir.mkdir();
			}
			String smallrainOutputFile = savePath + "\\rain.png";
			FileOutputStream smallrainToFile = new FileOutputStream(smallrainOutputFile);
			smallrainStmt = smallrainConn.prepareStatement("SELECT R_BLOB FROM R_TABLE_LOB WHERE R_ID = ?");

			smallrainStmt.setInt(1, 6666);
			ResultSet rs = smallrainStmt.executeQuery();
			if (rs.next()) {
				Blob blob = rs.getBlob("R_BLOB");
				byte[] data = blob.getBytes(1, (int) blob.length());
				smallrainToFile.write(data);
			}

			
//			查询CLOB数据
			smallrainStmt = smallrainConn.prepareStatement("SELECT R_CLOB FROM R_TABLE_LOB WHERE R_ID = ?");
			smallrainStmt.setInt(1, 222);
			ResultSet rsCLOB = smallrainStmt.executeQuery();
			if (rsCLOB.next()) {
				Reader smallrainQueryreader = rsCLOB.getCharacterStream("R_CLOB");
				if (smallrainQueryreader != null) {
					BufferedReader smallrainBufferReaderQuery = new BufferedReader(smallrainQueryreader);
					
					StringBuilder smallrainBuilderQuery = new StringBuilder();
					String smallrainLine;
					while ((smallrainLine = smallrainBufferReaderQuery.readLine()) != null) {
						smallrainBuilderQuery.append(smallrainLine).append(System.lineSeparator());
					}
					System.out.println("CLOB 数据: " + smallrainBuilderQuery);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (smallrainStmt != null) {
					smallrainStmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (smallrainConn != null) {
					smallrainConn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}