mysql 建表 blob_用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();

}

}

}

}

}