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;
}
}