ProviderDAO.java

 package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import database.ConnectionDB;

public class ProviderDAO
{
 private ProviderDTO providerDTO;
 private Connection conn;              //连接对象
 private Statement stmt;     //通过简单SQL语句访问数据库
 private PreparedStatement pstmt;   //预编译的SQL语句访问数据库
 private ResultSet rs;             //结果集

 public ProviderDAO()
 {
  conn = ConnectionDB.getConnection();
 }

 public Vector<ProviderDTO> fillProvider()
 {
  Vector<ProviderDTO> users = new Vector<ProviderDTO>();
  try
  {
   conn = ConnectionDB.getConnection();
   stmt=conn.createStatement();
   rs=stmt.executeQuery("select * from Provider");
   while(rs.next())
   {
    providerDTO = new ProviderDTO();
    providerDTO.setProviderID(rs.getString(rs.findColumn("ProviderID")));
    providerDTO.setProviderName(rs.getString(rs.findColumn("ProviderName")));
    providerDTO.setLinkman(rs.getString(rs.findColumn("Linkman")));
    providerDTO.setLinkPhone(rs.getString(rs.findColumn("LinkPhone")));
    providerDTO.setFax(rs.getString(rs.findColumn("Fax")));
    providerDTO.setEmail(rs.getString(rs.findColumn("Email")));
    providerDTO.setUrl(rs.getString(rs.findColumn("URL")));
    providerDTO.setAddress(rs.getString(rs.findColumn("Address")));
    providerDTO.setPostCode(rs.getString(rs.findColumn("PostCode")));
    providerDTO.setBank(rs.getString(rs.findColumn("Bank")));
    providerDTO.setBankAccount(rs.getString(rs.findColumn("BankAccount")));
    users.add(providerDTO);
   }
   rs.close();
   stmt.close();
   conn.close();
  }
  catch (SQLException e)
  {
   System.out.print(e.getMessage());
  }
  return users;
 }

 public Vector<ProviderDTO> searchProvider()
 {
  Vector<ProviderDTO> users = new Vector<ProviderDTO>();
  try
  {
   conn = ConnectionDB.getConnection();
   pstmt=conn.prepareStatement("select * from Provider where ProviderID like ? and ProviderName like ? and Linkman like ?");
   pstmt.setString(1, providerDTO.getProviderID() + "%");
   pstmt.setString(2, "%" + providerDTO.getProviderName() + "%");
   pstmt.setString(3, providerDTO.getLinkman() + "%");
   rs=pstmt.executeQuery();
   while(rs.next())
   {
    providerDTO = new ProviderDTO();
    providerDTO.setProviderID(rs.getString(rs.findColumn("ProviderID")));
    providerDTO.setProviderName(rs.getString(rs.findColumn("ProviderName")));
    providerDTO.setLinkman(rs.getString(rs.findColumn("Linkman")));
    providerDTO.setLinkPhone(rs.getString(rs.findColumn("LinkPhone")));
    providerDTO.setFax(rs.getString(rs.findColumn("Fax")));
    providerDTO.setEmail(rs.getString(rs.findColumn("Email")));
    providerDTO.setUrl(rs.getString(rs.findColumn("URL")));
    providerDTO.setAddress(rs.getString(rs.findColumn("Address")));
    providerDTO.setPostCode(rs.getString(rs.findColumn("PostCode")));
    providerDTO.setBank(rs.getString(rs.findColumn("Bank")));
    providerDTO.setBankAccount(rs.getString(rs.findColumn("BankAccount")));
    users.add(providerDTO);
   }
   rs.close();
   pstmt.close();
   conn.close();
  }
  catch (SQLException e)
  {
   System.out.print(e.getMessage());
  }
  return users;
 }

 public boolean addProvider()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    pstmt=conn.prepareStatement("insert into Provider values(?,?,?,?,?,?,?,?,?,?,?)");
    pstmt.setString(1, providerDTO.getProviderID());
    pstmt.setString(2, providerDTO.getProviderName());
    pstmt.setString(3, providerDTO.getLinkman());
    pstmt.setString(4, providerDTO.getLinkPhone());
    pstmt.setString(5, providerDTO.getFax());
    pstmt.setString(6, providerDTO.getEmail());
    pstmt.setString(7, providerDTO.getUrl());
    pstmt.setString(8, providerDTO.getAddress());
    pstmt.setString(9, providerDTO.getPostCode());
    pstmt.setString(10, providerDTO.getBank());
    pstmt.setString(11, providerDTO.getBankAccount());
   flag = (pstmt.executeUpdate() > 0);
   pstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public boolean updateProvider()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    pstmt=conn.prepareStatement("update Provider set ProviderName=?,Linkman=?,LinkPhone=?,Fax=?,Email=?,URL=?,Address=?,PostCode=?,Bank=?,BankAccount=? where ProviderID=?");
    pstmt.setString(1, providerDTO.getProviderName());
    pstmt.setString(2, providerDTO.getLinkman());
    pstmt.setString(3, providerDTO.getLinkPhone());
    pstmt.setString(4, providerDTO.getFax());
    pstmt.setString(5, providerDTO.getEmail());
    pstmt.setString(6, providerDTO.getUrl());
    pstmt.setString(7, providerDTO.getAddress());
    pstmt.setString(8, providerDTO.getPostCode());
    pstmt.setString(9, providerDTO.getBank());
    pstmt.setString(10, providerDTO.getBankAccount());
    pstmt.setString(11, providerDTO.getProviderID());
   flag = (pstmt.executeUpdate() > 0);
   pstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public boolean deleteProvider()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    pstmt=conn.prepareStatement("delete from Provider where ProviderID=?");
    pstmt.setString(1, providerDTO.getProviderID());
   flag = (pstmt.executeUpdate() > 0);
   pstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public void setProviderDTO(ProviderDTO providerDTO) {
  this.providerDTO = providerDTO;
 }
}