Dec14JDBC01.java

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.CharArrayWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Reader;
import java.sql.CallableStatement;
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 java.sql.Statement;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;
import javax.sql.rowset.serial.SerialException;

public class JDBC01 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://127.0.0.1:3306/jdbcDB" + "?user=root&password=password"
				+ "&useSSL=true&useUnicode=yes" + "&characterEncoding=UTF-8";
		String sql2 = "INSERT INTO Company "
				+ " (id, name, capital, amount, createDate, image, text)"
				+ " values(null, ?, ?, ?, ?, ?, ?)"; // placeholder, 佔位符號

		String sql3 = "SELECT id, image, text FROM Company";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		try (

				Connection con = DriverManager.getConnection(url);
				// Connection con = dataSource.getConnection();
				Statement stmt = con.createStatement();
				PreparedStatement pstmt = con.prepareStatement(sql2);
				PreparedStatement pstmt3 = con.prepareStatement(sql3);
				CallableStatement pstmt4 = con.prepareCall(sql3);) {
			// Class.forName("com.mysql.jdbc.Driver");
			String sql = "drop table if exists Company ";
			int n = stmt.executeUpdate(sql);
			System.out.println("n(0)=" + n);
			sql = " Create Table Company (" +
			" id int auto_increment primary key," +
			" name varchar(50), " +
			" capital int(30), " +
			" amount  numeric(15, 2), " +
			" image   longblob, " +
			" text    longtext, " +
			" createDate  date, " +
			" setupDateTime  datetime, " +
			" obj  blob " + ")";

			n = stmt.executeUpdate(sql);
			String insertSQL = "INSERT INTO Company " + " (id, name, capital, amount, createDate)"
					+ " values(null, '快樂谷有限公司', 2147483647, " + " 215.95, '2016-12-10') ";
//			System.out.println(insertSQL);
			n = stmt.executeUpdate(insertSQL);


			// 使用PreparedStatement物件
			pstmt.setString(1, "逍遙遊有限公司");
			pstmt.setInt(2, 2147483646);
			pstmt.setDouble(3, 315.95);
			pstmt.setDate(4, java.sql.Date.valueOf("1950-12-20"));
			File f = new File("D:\\Java004_Shared\\_MySQL\\penelope_cruz_64.jpg");
			int length = (int) f.length();
			FileInputStream fis = new FileInputStream(f);
			pstmt.setBinaryStream(5, fis, length);
			Clob clob = new SerialClob("測試資料".toCharArray());
			pstmt.setClob(6, clob);
			n = pstmt.executeUpdate();

			pstmt.setString(1, "相逢假期有限公司");
			pstmt.setInt(2, -2147483646);
			pstmt.setDouble(3, 123.95);
			pstmt.setDate(4, java.sql.Date.valueOf("1980-11-20"));
			File f1 = new File("C:\\Users\\Public\\Pictures\\Sample Pictures\\Koala.jpg");
			int length1 = (int) f1.length();
			byte[] ba = new byte[length1];
			FileInputStream fis1 = new FileInputStream(f1);
			fis1.read(ba);
			SerialBlob sb = new SerialBlob(ba);
			pstmt.setBlob(5, sb);
			Clob clob2 = getClobFromFile("D:\\java004\\JDBC\\ClobData1.txt");
			pstmt.setClob(6, clob2);

			n = pstmt.executeUpdate();

			pstmt.setString(1, "綠大地有限公司");
			pstmt.setInt(2, 1000646);
			pstmt.setDouble(3, 2255.95);
			pstmt.setDate(4, java.sql.Date.valueOf("1980-11-20"));
			File f2 = new File("C:\\Users\\Public\\Pictures\\Sample Pictures\\Jellyfish.jpg");
			int length2 = (int) f2.length();
			byte[] ba2 = new byte[length2];
			FileInputStream fis2 = new FileInputStream(f2);
			fis2.read(ba2);
			pstmt.setBytes(5, ba2);
			Clob clob3 = getClobFromBigInteger();

			pstmt.setClob(6, clob3);
			n = pstmt.executeUpdate();

			// pstmt3.setInt(1, 2);
			ResultSet rs = pstmt3.executeQuery();
			int count = 0;
			while (rs.next()) {
				int id = rs.getInt(1);
				// --------------- Case1 -----------------
				// try (
				// InputStream is = rs.getBinaryStream(2);
				//
				// FileOutputStream fos = new FileOutputStream
				// ("d:\\images" + count + ".jpg");
				// ) {
				// if (is == null) {
				// continue;
				// }
				// byte[] b = new byte[8192];
				// int len = 0 ;
				// while ((len=is.read(b))!= -1){
				// fos.write(b, 0, len); // AOL
				// }
				// }
				// --------------- Case2 -----------------
				byte[] b2 = rs.getBytes(2);
				if (b2 == null) {
					 continue;
				}
				try (
					FileOutputStream fos = new FileOutputStream
					("d:\\imagesA" + count + ".jpg");
				)
				{
					fos.write(b2);
				}
//				// --------------- Case3 -----------------
//				Blob bb = rs.getBlob(2);
//				if (bb == null) {
//					 continue;
//				}
//				try (
//					InputStream is = bb.getBinaryStream();
//					FileOutputStream fos = new FileOutputStream("d:\\imagesB" + count + ".jpg");) {
//					byte[] b3 = new byte[8192];
//					int len = 0 ;
//					while ((len=is.read(b3))!= -1){
//						fos.write(b3, 0, len);
//					}
//				}
				// 取出Clob資料
				Clob clob9 = rs.getClob(3);
				System.out.println("id=" + id + " " + clob9.length());
				Reader reader = clob9.getCharacterStream();
				String filename = "data" + id + ".txt";
				try (
				FileOutputStream out =
						new FileOutputStream("D:\\Java004\\jdbc\\"+ filename);
				OutputStreamWriter osw =
						new OutputStreamWriter(out, "BIG5");
			    ) {
					char[] ca = new char[8192];
					int len = 0;
					while ((len = reader.read(ca)) != -1) {
						osw.write(ca, 0, len);
					}
				}

				count++;
			}

			System.out.println("n(2)=" + n);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	private static Clob getClobFromBigInteger() {
		try (
			FileInputStream fis = new FileInputStream("D:\\factorial.txt");
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
		) {
			byte[] b3 = new byte[8192];
			int len = 0;
			while ((len = fis.read(b3)) != -1) {
				baos.write(b3, 0, len);
			}
			byte[] ba = baos.toByteArray();
			String s = new String(ba, "UTF-8");
            Clob clob = new SerialClob(s.toCharArray());
            return clob;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}

	private static Clob getClobFromFile(String filename) {
		try (
		   FileReader fr = new FileReader(filename);
           BufferedReader br = new BufferedReader(fr);
		   CharArrayWriter caw = new CharArrayWriter();
		) {
			String line = "";
			while ((line=br.readLine())!= null){
			  caw.write(line.toCharArray());
			  caw.write('\r');
			  caw.write('\n');
			}
			char[] ca = caw.toCharArray();
			SerialClob clob = new SerialClob(ca);
			return clob;
		} catch(IOException ex){
			ex.printStackTrace();
		} catch (SerialException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}