Java - Java实现对Mysql的图片存取操作

1.MySQL中的BLOB类型

　　Mysql中可以存储大文件数据，一般使用的BLOB对象。如图片，视频等等。

　　BLOB是一个二进制大对象，可以容纳可变数量的数据。因为是二进制对象，所以与编码方式无关。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

　　四种字段类型保存的最大长度如下：

　　TINYBLOB - 255 bytes
　　BLOB - 65535 bytes(64KB)
　　MEDIUMBLOB - 16,777,215 bytes(16MB) (2^24 - 1)
　　LONGBLOB - 4G bytes (2^32 – 1)

2.java对MySQL图片的读取

      /**
	 读写大文本:
	 
	 create table testclob(
	 	id varchar(40) primary key,
	 	resume text
	 );
	 * @throws Exception 
	 
	 */

	@Test
	public void insert() throws Exception {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = \"INSERT INTO testclob(id,resume) values(?,?)\";
			st = conn.prepareStatement(sql);
			st.setString(1, \"1\");
			
			File file = new File(\"src/1.txt\");
			FileReader reader = new FileReader(file);
			st.setCharacterStream(2, reader, file.length());
			
			int num = st.executeUpdate();
			if(num > 0){
				System.out.println(\"INSERT SUCCESSFULLY\");
			}
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void read() throws Exception {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = \"SELECT id,resume FROM testclob WHERE id = \'1\'\";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if(rs.next()){
				//String resume = rs.getString(\"resume\");
				Reader reader = rs.getCharacterStream(\"resume\");
				FileWriter writer = new FileWriter(\"E:\\\\aa.txt\");
				try {
					int len = 0;
					char buffer[] = new char[1024]; //1024个字节
					while((len = reader.read(buffer)) > 0){
						writer.write(buffer,0,len);
					}
				} finally {
					if(reader != null){
						reader.close();
					}
					writer.close();
				}
			}
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}

/**
	 读取图片：
	 create table testblob(
	 	id varchar(40) primary key,
	 	image blob
	 );

     create table testlongblob(
	 	id varchar(40) primary key,
	 	image longblob
	 );
	 * @throws Exception 
	 
	 */

	@Test
	public void insert() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = \"INSERT INTO testblob(id,image) values(?,?)\";
			st = conn.prepareStatement(sql);
			st.setString(1, \"2\");
			File file = new File(\"src/1.jpg\");
			FileInputStream in = new FileInputStream(file);
			st.setBinaryStream(2,in,file.length());
			st.executeUpdate();
			int num = st.executeUpdate();
			if(num > 0){
				System.out.println(\"INSERT SUCCESSFULLY\");
			}
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void read() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = \"SELECT id,image FROM testblob WHERE id = \'2\'\";
			rs = conn.prepareStatement(sql).executeQuery();
		    if(rs.next()){
		    	InputStream in = rs.getBinaryStream(\"image\");
		    	OutputStream out = new FileOutputStream(\"E:\\\\2.jpg\");
		    	try {
					int len = 0;
					byte buffer[] = new byte[1024];
					while((len = in.read(buffer)) > 0){
						out.write(buffer, 0, len);
					}
				} finally {
					if(in != null){
						in.close();
					}
					if(out != null){
						out.close();
					}
				}
		    }
		    
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void insert2() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = \"insert into testlongblob(id,image) values(?,?)\";
			st = conn.prepareStatement(sql);
			st.setString(1, \"1\");
			File file = new File(\"src/2.jpg\");
			FileInputStream in = new FileInputStream(file);
			st.setBinaryStream(2, in, (int) file.length());
			st.executeUpdate();
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Test
	public void read2() throws Exception{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = \"select id,image from testlongblob where id=\'1\'\";
			rs = conn.prepareStatement(sql).executeQuery();
			if(rs.next()){
				InputStream in = rs.getBinaryStream(\"image\");
				OutputStream out = new FileOutputStream(\"E:\\\\2.jpg\");;
				try {
					int len = 0;
					byte buffer[] = new byte[1024];
					while ((len = in.read(buffer)) > 0) {
						out.write(buffer, 0, len);
					}
				} finally {
					if (in != null)
						in.close();
					if (out != null)
						out.close();
				}
			}
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

自己写的Demo，请大神们多多指教！

3.选择合适的字段大小

　　如果选择的字段类型的最大长度较小，放不下保存的数据，可能会报出MySQL数据截断异常。如：

　　　　com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column \'photo\' at row 1