[实验目的]
1. 了解BLOB与CLOB
2. 掌握BLOB的在数据库的存取
[实验内容]
1. 设计一个用于测试的数据表，其中包括image类型的字段
2. 编写一个程序实现把图片的往数据库中存储。
3. 编写一个程序实现把图片的从数据库中取出来，并显示在页面中。
4. 尝试修改Blob对象
----------------解决方案--------------------------------------------------------
package com.city.oa.business.impl;
import java.sql.*;
import java.util.*;
import java.io.*;
import com.city.oa.factory.*;
import com.city.oa.value.*;
public class EmployeeImpl
{
public void add(String empNo,String password,String name) throws Exception
{
String sql="insert into Employee (EMPNO,PASSWORD,NAME) values (?,?,?)";
Connection cn=null;
try
{
cn=ConnectionFactory.getConnection();
PreparedStatement ps=cn.prepareStatement(sql);
ps.setString(1, empNo);
ps.setString(2, password);
ps.setString(3, name);
ps.executeUpdate();
ps.close();
}
catch(Exception e)
{
throw new Exception("增加员工错误:"+e.getMessage());
}
finally
{
if(cn!=null)
{
cn.close();
}
}
}
public List getAllList() throws Exception
{
List empList=new ArrayList();
String sql="select * from EMPLOYEE";
Connection cn=null;
try
{
cn=ConnectionFactory.getConnection();
PreparedStatement ps=cn.prepareStatement(sql);
ResultSet rs=ps.executeQuery();
while(rs.next())
{
EmployeeValue ev=new EmployeeValue();
ev.setEmpNo(rs.getString("EMPNO"));
ev.setPassword(rs.getString("PASSWORD"));
ev.setName(rs.getString("NAME"));
ev.setFileType(rs.getString("FILETYPE"));
empList.add(ev);
}
rs.close();
ps.close();
}
catch(Exception e)
{
throw new Exception("取得员工列表错误:"+e.getMessage());
}
finally
{
if(cn!=null)
{
cn.close();
}
}
return empList;
}
public EmployeeValue getEmployee(String empNo) throws Exception
{
EmployeeValue ev=null;
String sql="select * from Employee where EMPNO=?";
Connection cn=null;
try
{
cn=ConnectionFactory.getConnection();
PreparedStatement ps=cn.prepareStatement(sql);
ps.setString(1, empNo);
ResultSet rs=ps.executeQuery();
if(rs.next())
{
ev=new EmployeeValue();
ev.setEmpNo(rs.getString("EMPNO"));
ev.setPassword(rs.getString("PASSWORD"));
ev.setName(rs.getString("NAME"));
ev.setFileType(rs.getString("FILETYPE"));
}
rs.close();
ps.close();
}
catch(Exception e)
{
throw new Exception("取得指定员工信息方法错误:"+e.getMessage());
}
finally
{
if(cn!=null)
{
cn.close();
}
}
return ev;
}
public void savePhoto(String empNo,InputStream in,int fileSize,String fileType) throws Exception
{
String sql="update EMPLOYEE set Photo=?,FileType=? where empNo=?";
Connection cn=null;
try
{
cn=ConnectionFactory.getConnection();
PreparedStatement ps=cn.prepareStatement(sql);
ps.setBinaryStream(1, in, fileSize);
ps.setString(2, fileType);
ps.setString(3, empNo);
ps.executeUpdate();
ps.close();
}
catch(Exception e)
{
throw new Exception("保存照片方法错误:"+e.getMessage());
}
finally
{
if(cn!=null)
{
cn.close();
}
}
}
}
----------------解决方案--------------------------------------------------------
package com.city.oa.servlet;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.city.oa.factory.ConnectionFactory;
public class ShowPhoto extends HttpServlet
{
/**
* Constructor of the object.
*/
public ShowPhoto() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sql="select * from Employee where EMPNO=?";
String empNo=request.getParameter("empno");
Connection cn=null;
try
{
cn=ConnectionFactory.getConnection();
PreparedStatement ps=cn.prepareStatement(sql);
ps.setString(1, empNo);
ResultSet rs=ps.executeQuery();
if(rs.next())
{
InputStream in=rs.getBinaryStream("photo");
String fileType=rs.getString("FileType");
response.setContentType(fileType);
OutputStream out=response.getOutputStream();
int len=0;
byte[] data=new byte[100];
while( (len=in.read(data))!=-1 )
{
out.write(data,0,len);
}
in.close();
out.flush();
out.close();
}
rs.close();
ps.close();
}
catch(Exception e)
{
System.out.println("Error:"+e.getMessage());
}
finally
{
if(cn!=null)
{
try{ cn.close();}catch(Exception e){}
}
}
}
----------------解决方案--------------------------------------------------------
package com.city.oa.value;
import java.io.Serializable;
public class EmployeeValue implements Serializable
{
private String empNo=null;
private String password=null;
private String name=null;
private String fileType=null;
public String getEmpNo() {
return empNo;
}
public void setEmpNo(String empNo) {
this.empNo = empNo;
}
public String getFileType() {
return fileType;
}
public void setFileType(String fileType) {
this.fileType = fileType;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
----------------解决方案--------------------------------------------------------