JDBC-处理大数据对象

代码框架如下，test表示处理CLOB数据，test1表示处理BLOB对象

package com.chb.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.chb.model.Book;
import com.chb.util.DbUtil;

public class test {

private static DbUtil dbUtil=new DbUtil();
	
	/**
	 * 添加图书
	 * @param book
	 * @return
	 * @throws Exception
	 */
	private static int addBook(Book book)throws Exception{
		Connection con=dbUtil.getCon(); // 获取连接
		String sql="insert into t_book values(null,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());  // 给第一个坑设置值
		pstmt.setFloat(2, book.getPrice());  // 给第二个坑设置值
		pstmt.setString(3, book.getAuthor()); // 给第三个坑设置值
		pstmt.setInt(4, book.getBookTypeId());  // 给第四个坑设置值
		File context=book.getContext(); // 获取文件
		InputStream inputStream=new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream,context.length());  // 给第五个坑设置值
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void getBook(int id)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="select * from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			String bookName=rs.getString("bookName");
			float price=rs.getFloat("price");
			String author=rs.getString("author");
			int bookTypeId=rs.getInt("bookTypeId");
			//读取插入的文本
			Clob c=rs.getClob("context");
			String context=c.getSubString(1, (int)c.length());
			
			System.out.println("图书名称："+bookName);
			System.out.println("图书价格:"+price);
			System.out.println("图书作者："+author);
			System.out.println("图书类型ID："+bookTypeId);
			System.out.println("图书内容："+context);
		}
		dbUtil.close(pstmt, con);
	}
	
	public static void main(String[] args)throws Exception {
		/*File context=new File("c:/helloWorld.txt");
		Book book=new Book("helloWorld", 100, "小锋", 1,context);
		int result=addBook(book);
		if(result==1){
			System.out.println("添加成功！");
		}else{
			System.out.println("添加失败！");
		}*/
		getBook(16);
	}
}

package com.chb.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.chb.model.Book;
import com.chb.util.DbUtil;

public class test1 {

private static DbUtil dbUtil=new DbUtil();
	
	/**
	 * 添加图书
	 * @param book
	 * @return
	 * @throws Exception
	 */
	private static int addBook(Book book)throws Exception{
		Connection con=dbUtil.getCon(); // 获取连接
		String sql="insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());  // 给第一个坑设置值
		pstmt.setFloat(2, book.getPrice());  // 给第二个坑设置值
		pstmt.setString(3, book.getAuthor()); // 给第三个坑设置值
		pstmt.setInt(4, book.getBookTypeId());  // 给第四个坑设置值
		File context=book.getContext(); // 获取文件
		InputStream inputStream=new FileInputStream(context);
		pstmt.setAsciiStream(5, inputStream,context.length());  // 给第五个坑设置值
		
		File pic=book.getPic(); // 获取图片文件
		InputStream inputStream2=new FileInputStream(pic);
		pstmt.setBinaryStream(6, inputStream2, pic.length()); // 给第六个坑设置值
		int result=pstmt.executeUpdate();
		dbUtil.close(pstmt, con);
		return result;
	}
	
	public static void getBook(int id)throws Exception{
		Connection con=dbUtil.getCon();
		String sql="select * from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			String bookName=rs.getString("bookName");
			float price=rs.getFloat("price");
			String author=rs.getString("author");
			int bookTypeId=rs.getInt("bookTypeId");
			Clob c=rs.getClob("context");
			String context=c.getSubString(1, (int)c.length());
			Blob b=rs.getBlob("pic");
			FileOutputStream out=new FileOutputStream(new File("d:/pic2.jpg"));//把图片写到d盘
			out.write(b.getBytes(1, (int)b.length()));
			out.close();
			System.out.println("图书名称："+bookName);
			System.out.println("图书价格:"+price);
			System.out.println("图书作者："+author);
			System.out.println("图书类型ID："+bookTypeId);
			System.out.println("图书内容："+context);
		}
		dbUtil.close(pstmt, con);
	}
	
	public static void main(String[] args)throws Exception {
		/*File context=new File("c:/helloWorld.txt");
		File pic=new File("c:/pic1.jpg");
		Book book=new Book("helloWorld", 100, "小锋", 1,context,pic);
		int result=addBook(book);
		if(result==1){
			System.out.println("添加成功！");
		}else{
			System.out.println("添加失败！");
		}*/
	    getBook(18);
	}
}

package com.chb.model;

import java.io.File;

/**
 * 图书模型
 */
public class Book {

	private int id;
	private String bookName;
	private float price;
	private String author;
	private int bookTypeId;
	private File context;
	private File pic;
	
	
	
	public Book(int id, String bookName, float price, String author,
			int bookTypeId) {
		super();
		this.id = id;
		this.bookName = bookName;
		this.price = price;
		this.author = author;
		this.bookTypeId = bookTypeId;
	}
	public Book(String bookName, float price, String author, int bookTypeId) {
		super();
		this.bookName = bookName;
		this.price = price;
		this.author = author;
		this.bookTypeId = bookTypeId;
	}
	
	
	public Book(String bookName, float price, String author, int bookTypeId,
			File context) {
		super();
		this.bookName = bookName;
		this.price = price;
		this.author = author;
		this.bookTypeId = bookTypeId;
		this.context = context;
	}
	
	
	public Book(String bookName, float price, String author, int bookTypeId,
			File context, File pic) {
		super();
		this.bookName = bookName;
		this.price = price;
		this.author = author;
		this.bookTypeId = bookTypeId;
		this.context = context;
		this.pic = pic;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public int getBookTypeId() {
		return bookTypeId;
	}
	public void setBookTypeId(int bookTypeId) {
		this.bookTypeId = bookTypeId;
	}
	
	
	public File getContext() {
		return context;
	}
	public void setContext(File context) {
		this.context = context;
	}
	
	
	public File getPic() {
		return pic;
	}
	public void setPic(File pic) {
		this.pic = pic;
	}
	@Override
	public String toString() {
		return "["+this.id+","+this.bookName+","+this.price+","+this.author+","+this.bookTypeId+"]";
	}
}

package com.chb.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class DbUtil {

	// 数据库地址
	private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
	// 用户名
	private static String dbUserName="root";
	// 密码
	private static String dbPassword="123456";
	// 驱动名称
	private static String jdbcName="com.mysql.jdbc.Driver";
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws Exception
	 */
	public Connection getCon()throws Exception{
		Class.forName(jdbcName);
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
		return con;
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(Statement stmt,Connection con)throws Exception{
		if(stmt!=null){
			stmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
	
	/**
	 * 关闭连接
	 * @param con
	 * @throws Exception
	 */
	public void close(PreparedStatement pstmt,Connection con)throws Exception{
		if(pstmt!=null){
			pstmt.close();
			if(con!=null){
				con.close();
			}
		}
	}
}