mysql 附件上传_JDBC中级篇（MYSQL）——模拟从数据库中上传下载附件

注意：其中的JdbcUtil是我自定义的连接工具类：代码例子链接：

package b_blob_clob;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Date;

import util.JdbcUtil;

/**

* 模拟一个从数据库中：

* 上传下载附件

*

* @author mzy

*

*/

public class Demo03 {

public static void main(String[] args) {

write();

read();

}

private static void read() {

/**

* 读出

*/

Connection conn = null;

PreparedStatement stmt = null;

ResultSet rs = null;

try{

conn = JdbcUtil.getConnection();

String sql = "select * from attachments where id=?";

//预编译

stmt = conn.prepareStatement(sql);

//参数赋值

stmt.setInt(1, 2);

//执行查询

rs = stmt.executeQuery();

//遍历结果

if(rs.next()){

String name = rs.getString("name");

InputStream in = rs.getBinaryStream("file"); //rs.getBlob("file").getBinaryStream();

//取出附件

//写出到硬盘

BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream("e:/picture.png"));

byte[] buf = new byte[1024];

int len = 0;

//边读边写

while( (len=in.read(buf))!=-1 ){

out.write(buf, 0, len);

}

//关闭流

out.close();

in.close();

Date date = rs.getDate("addtime");

String author = rs.getString("author");

System.out.println(name+"\t"+date+"\t"+author);

}

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(stmt, conn);

}

}

private static void write() {

/**

* 写入

*/

Connection conn = null;

PreparedStatement stmt = null;

conn = JdbcUtil.getConnection();

String sql = "insert into attachments(name,file,addtime,author) values(?,?,?,?)";

try {

// 预编译

stmt = conn.prepareStatement(sql);

// 读取图片

File file = new File("./src/人和鸟.png");

FileInputStream in = new FileInputStream(file);

String fileName = file.getName();

// 去掉后缀名

fileName = fileName.substring(0, fileName.indexOf("."));

stmt.setString(1, fileName);

stmt.setBlob(2, in);

// 第三个时间的插入，插入的话要遵照mysql的Date类型，所以要进行转换

stmt.setDate(3, new java.sql.Date(new Date().getTime()));

stmt.setString(4, "mzy");

stmt.executeUpdate();

in.close();

System.out.println("添加成功！");

} catch (SQLException | IOException e) {

e.printStackTrace();

} finally {

JdbcUtil.close(stmt, conn);

}

}

}