companylogodao.java

j2ee源码

/*
 * Created on 2005/11/24
 */
package com.leeman.wkexs.master.companylogo.dao;

import java.sql.*;
import java.util.ArrayList;

import com.leeman.common.data.Dao;
import com.leeman.common.data.ResultSetReader;
import com.leeman.common.util.Formatter;
import com.leeman.wkexs.master.companylogo.entity.*;

public class CompanyLogoDao extends Dao{
	
	public void insertBLOB(CompanyLogoVO vo) throws Exception 
	{
		String sql;
		ResultSet rs;
		initDbConn();
	
		if (vo.getPicture() != null) {
			initDbConn();
			sql = "DELETE FROM WKEXS_LOB.COMPANY_PIC WHERE UPPER(COMPANY_ID) = ?";
	
			dbConn.prepareStatement(sql);
			dbConn.pstmt.setString(1, vo.getCompany_id().trim().toUpperCase());
			dbConn.executeUpdateWithPstmt();
			dbConn.closePreparedStatement();
			
			initDbConn();
			sql = "INSERT INTO WKEXS_LOB.COMPANY_PIC(COMPANY_ID, PICTURE, FILE_NAME, LAST_MODIFY_DTTM, LAST_MODIFY_BY) VALUES(?, '001', ?, ?, ?)";
		
			dbConn.prepareStatement(sql);
			dbConn.pstmt.setString(1, vo.getCompany_id().trim().toUpperCase());
			dbConn.pstmt.setString(2, vo.getFile_name().trim());
			dbConn.pstmt.setDate(3, new java.sql.Date(vo.getLast_modify_dttm().getTime()));
			dbConn.pstmt.setString(4, vo.getLast_modify_by().trim());
			dbConn.executeUpdateWithPstmt();
			dbConn.closePreparedStatement();
		
			initDbConn();
			sql = "SELECT PICTURE FROM WKEXS_LOB.COMPANY_PIC WHERE UPPER(COMPANY_ID) = ? FOR UPDATE ";
		
			dbConn.prepareStatement(sql);
			dbConn.pstmt.setString(1, vo.getCompany_id().trim().toUpperCase());
			rs = dbConn.executeQueryWithPstmt();
		
			oracle.sql.BLOB photoblob = null;
			while (rs.next())
			{
				photoblob = (oracle.sql.BLOB)rs.getObject(1);
				int tempval = photoblob.putBytes(1, vo.getPicture());
			}
			rs.close();
			dbConn.closePreparedStatement();
	
			initDbConn();
			sql = "update WKEXS_LOB.COMPANY_PIC SET PICTURE = ? WHERE UPPER(COMPANY_ID) = ? ";
			
			dbConn.prepareStatement(sql);
			dbConn.pstmt.setBlob(1, photoblob);
			dbConn.pstmt.setString(2, vo.getCompany_id().trim().toUpperCase());
			dbConn.executeUpdateWithPstmt();
			rs.close();
			dbConn.closePreparedStatement();
		}
		else
		{
			initDbConn();
			sql = "UPDATE WKEXS_LOB.COMPANY_PIC SET FILE_NAME = ? WHERE UPPER(COMPANY_ID) = ? ";
	
			dbConn.prepareStatement(sql);
			dbConn.pstmt.setString(1, vo.getFile_name().trim());
			dbConn.pstmt.setString(2, vo.getCompany_id().trim().toUpperCase());
			dbConn.executeUpdateWithPstmt();
			dbConn.closePreparedStatement();
		}
	}
		
	public CompanyLogoVO getBLOB(CompanyLogoVO vo) throws Exception 
	{
		initDbConn();
		String sql = "SELECT PICTURE, FILE_NAME FROM WKEXS_LOB.COMPANY_PIC WHERE UPPER(COMPANY_ID)  = ? ";

		dbConn.prepareStatement(sql); 
		dbConn.pstmt.setString(1, vo.getCompany_id().trim().toUpperCase());
		ResultSet rs = dbConn.executeQueryWithPstmt();

		Blob blobData = null;
		if (rs.next())
		{
			blobData = rs.getBlob("PICTURE");
			int length = (int)blobData.length();
			vo.setPicture(blobData.getBytes(1, length));
			vo.setFile_name(rs.getString("FILE_NAME"));
		}
		return vo;
	}
		
	public boolean isBLOBExist(CompanyLogoVO vo) throws Exception 
	{
		initDbConn();
		String sql = "SELECT FILE_NAME FROM WKEXS_LOB.COMPANY_PIC WHERE UPPER(COMPANY_ID) = ? ";
		
		dbConn.prepareStatement(sql); 
		dbConn.pstmt.setString(1, vo.getCompany_id().trim().toUpperCase());
		ResultSet rs = dbConn.executeQueryWithPstmt();
		boolean exist = false;
		while (rs.next())
		{			
			exist = true;
		}
		return exist;
	}
}