用clob , blob在mysql中建表 , 并使用jdbc操作

窝窝插件

　　clob  对应四种类型 ，TINYTEXT ,    TEXT ,      MEDIUMTEXT , LONGTEXT
　　2的8次方     2的16次方      2的24次方          2的32次方
　　blob 对应四种类型  .  TINYBLOB ,     BLOB ,    MEDIUMBLOB , LONGBLOB
　　
　　
　　clob
　　create table testclob(id integer primary key auto_increment , content text);
　　
　　
　　blob
　　create tabke testblob(id integer primary key auto_increment , image blog);
　　
　　

package com.zly.test;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main2(String[] args) {
Connection connection = null;
PreparedStatement psmt = null;
BufferedInputStream bis = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc" , "root" , "root");
String sql = "insert into testjdbc(pic) values(?)";
psmt = connection.prepareStatement(sql);
File file = new File("src/tab.png");
bis = new BufferedInputStream(new FileInputStream(file));
psmt.setBinaryStream(1, bis, new Long(file.length()).intValue());
int executeUpdate = psmt.executeUpdate();
System.out.println(executeUpdate);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if(bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(psmt != null) {
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main1(String[] args) {
Connection connection = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc" , "root" , "root");
String sql = "select content from testjdbc where id = ?";
psmt = connection.prepareStatement(sql);
psmt.setInt(1, 1);
rs = psmt.executeQuery();
if(rs.next()) {
String string = rs.getString("content");
System.out.println(new String(string.getBytes() , "UTF-8"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} finally {
if(psmt != null) {
try {
psmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
InputStream binaryStream = null;
OutputStream out = null;
Connection connection = null;
PreparedStatement prepareStatement = null;
ResultSet executeQuery = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
String sql = "select pic from testjdbc where id = ?";
prepareStatement = connection.prepareStatement(sql);
prepareStatement.setInt(1, 1);
executeQuery = prepareStatement.executeQuery();
if(executeQuery.next()) {
binaryStream = executeQuery.getBinaryStream("pic");
out = new FileOutputStream("src/tab_bak.png");
byte[] bytes = new byte[1024];
int read = 0;
while((read = binaryStream.read(bytes)) != -1) {
out.write(bytes , 0 , read);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(binaryStream != null) {
try {
binaryStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(executeQuery != null) {
try {
executeQuery.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}