货架仓库管理package real.action.dao;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.swing.ComboBoxModel;
import real.action.data.SupplierData;
import real.action.sql.ConnectionFactory;
public class SupplierDao
{
private Connection conn;
private Statement statement;
private PreparedStatement preparedStatement;//SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
private ResultSet resultSet;//结果集
public boolean addSupplier(SupplierData s)//添加供应商
{
String sql = "insert into suppliers values(?,?,?,?,?,?,?,?)";
try
{
this.conn = ConnectionFactory.getConnection();
this.preparedStatement = this.conn.prepareStatement(sql);
this.preparedStatement.setInt(1, s.getSupplierId());
this.preparedStatement.setString(2, s.getSupplierName());
this.preparedStatement.setString(3, s.getSupplierAddress());
this.preparedStatement.setString(4, s.getPostCode());
this.preparedStatement.setString(5, s.getSupplierTelephone());
this.preparedStatement.setString(6, s.getSupplierFax());
this.preparedStatement.setString(7, s.getSupplierRelationer());
this.preparedStatement.setString(8, s.getSupplierEmail());
int flag = this.preparedStatement.executeUpdate();
if (flag > 0) {
return true;
}
}
catch (Exception e)
{
e.printStackTrace();
}
return false;
}
public boolean isExistSupplierData()
{
boolean flag = true;
int row = 0;
String sql = "select count(sup_id) as count from suppliers";
try {
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(sql);
if (this.resultSet.next()) {
row = this.resultSet.getInt("count");
}
if (row == 0) {
return false;
}
}
catch (Exception e)
{
e.printStackTrace();
}
return flag;
}
public boolean isExistSupplierById(int number)//ID已存在
{
boolean flag = false;
String sql = "select sup_id from suppliers";
try
{
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(sql);
while (this.resultSet.next()) {
SupplierData supplierData = new SupplierData();
supplierData.setSupplierId(this.resultSet.getInt(1));//设置ID
if (supplierData.getSupplierId() == number) {//比较ID
flag = true;
break;
}
}
}
catch (Exception e) {
e.printStackTrace();
}
return flag;
}
public List<Object> getFindAllSupplierId()//获取所有供应商id
{
List listsupplierId = new ArrayList();//滚动列表
String sql = "select sup_id from suppliers ";
try
{
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(sql);
while (this.resultSet.next())
listsupplierId.add(Integer.valueOf(this.resultSet.getInt(1)));
}
catch (Exception e) {
e.printStackTrace();
}
return listsupplierId;
}
public SupplierData getSupplierbySupplierName(String name) {
SupplierData supplierData = null;
String sql = "select * from suppliers where sup_name = ?";
try
{
this.conn = ConnectionFactory.getConnection();
this.preparedStatement = this.conn.prepareStatement(sql);
this.preparedStatement.setString(1, name);
this.resultSet = this.preparedStatement.executeQuery();
if (this.resultSet.next()) {
supplierData = new SupplierData();
supplierData.setSupplierId(this.resultSet.getInt(1));
supplierData.setSupplierName(this.resultSet.getString(2));
supplierData.setSupplierAddress(this.resultSet.getString(3));
supplierData.setPostCode(this.resultSet.getString(4));
supplierData.setSupplierTelephone(this.resultSet.getString(5));
supplierData.setSupplierFax(this.resultSet.getString(6));
supplierData.setSupplierRelationer(this.resultSet.getString(7));
supplierData.setSupplierEmail(this.resultSet.getString(8));
}
}
catch (Exception e) {
e.printStackTrace();
}
return supplierData;
}
public Map<Integer, String> getAllSupplier() {
Map supplierData = new HashMap();//基于哈希表的 Map 接口的实现。此实现提供所有可选的映射操作
String sql = "select sup_id,sup_name,sup_address,postcode,sup_telephone,sup_fax,sup_relationer,sup_email from suppliers";
try
{
conn = ConnectionFactory.getConnection();
preparedStatement = conn.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next())// 在此映射中关联指定值与指定键。使供应商ID与名称关联
supplierData.put(Integer.valueOf(resultSet.getInt("sup_id")), resultSet.getString("sup_name"));
}
catch (Exception e) {
e.printStackTrace();
}
return supplierData;//返回映射关系
}
public SupplierData getSupplierbySupplierId(String number)
{
SupplierData supplierData = null;
String sql = "select sup_id,sup_name,sup_address,postcode,sup_telephone,sup_fax,sup_relationer,sup_email from suppliers where sup_id = ?";
try
{
this.conn = ConnectionFactory.getConnection();
this.preparedStatement = this.conn.prepareStatement(sql);
this.preparedStatement.setString(1, number);
this.resultSet = this.preparedStatement.executeQuery();
if (this.resultSet.next()) {
supplierData = new SupplierData();
supplierData.setSupplierId(this.resultSet.getInt(1));
supplierData.setSupplierName(this.resultSet.getString(2));
supplierData.setSupplierAddress(this.resultSet.getString(3));
supplierData.setPostCode(this.resultSet.getString(4));
supplierData.setSupplierTelephone(this.resultSet.getString(5));
supplierData.setSupplierFax(this.resultSet.getString(6));
supplierData.setSupplierRelationer(this.resultSet.getString(7));
supplierData.setSupplierEmail(this.resultSet.getString(8));
}
}
catch (Exception e)
{
e.printStackTrace();
}
return supplierData;
}
public List<Object> getFindSupplier()
{
List lstSupplierData = new ArrayList();
String sql = "select sup_id,sup_name,sup_address,postcode,sup_telephone,sup_fax,sup_relationer,sup_email from suppliers";
try
{
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(sql);
while (this.resultSet.next()) {
lstSupplierData.add(Integer.valueOf(this.resultSet.getInt(1)));
lstSupplierData.add(this.resultSet.getString(2));
lstSupplierData.add(this.resultSet.getString(3));
lstSupplierData.add(this.resultSet.getString(4));
lstSupplierData.add(this.resultSet.getString(5));
lstSupplierData.add(this.resultSet.getString(6));
lstSupplierData.add(this.resultSet.getString(7));
lstSupplierData.add(this.resultSet.getString(8));
}
}
catch (Exception e)
{
e.printStackTrace();
}
return lstSupplierData;
}
public boolean updateSupplierById(SupplierData s)//按ID更新供应商
{
boolean flag = false;
String sql = "update suppliers set sup_name = ?,sup_address = ? ,postcode = ? ,sup_telephone = ? ,sup_fax = ? , sup_relationer = ? , sup_email = ? where sup_id = ?";
try
{
this.conn = ConnectionFactory.getConnection();
this.preparedStatement = this.conn.prepareStatement(sql);
this.preparedStatement.setInt(8, s.getSupplierId());
this.preparedStatement.setString(1, s.getSupplierName());
this.preparedStatement.setString(2, s.getSupplierAddress());
this.preparedStatement.setString(3, s.getPostCode());
this.preparedStatement.setString(4, s.getSupplierTelephone());
this.preparedStatement.setString(5, s.getSupplierFax());
this.preparedStatement.setString(6, s.getSupplierRelationer());
this.preparedStatement.setString(7, s.getSupplierEmail());
int insertReow = this.preparedStatement.executeUpdate();
if (insertReow > 0)
flag = true;
}
catch (Exception e)
{
e.printStackTrace();
}
return flag;
}
public boolean deleteSupplierById(int number)
{
boolean flag = false;
String sql = "delete from suppliers where sup_id=?";
try {
this.conn = ConnectionFactory.getConnection();
this.preparedStatement = this.conn.prepareStatement(sql);
this.preparedStatement.setInt(1, number);
int insertReow = this.preparedStatement.executeUpdate();
if (insertReow > 0) {
flag = true;
}
}
catch (Exception e)
{
e.printStackTrace();
}
return flag;
}
public List<Object> getSupplier()//获取所有供应商
{
List lstSuppplierData = new ArrayList();
String sql = "select sup_id,sup_name,sup_address,postcode,sup_telephone,sup_fax,sup_relationer,sup_email from suppliers";
try
{
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(sql);
while (this.resultSet.next()) {
lstSuppplierData.add(Integer.valueOf(this.resultSet.getInt(1)));
lstSuppplierData.add(this.resultSet.getString(2));
lstSuppplierData.add(this.resultSet.getString(3));
lstSuppplierData.add(this.resultSet.getString(4));
lstSuppplierData.add(this.resultSet.getString(5));
lstSuppplierData.add(this.resultSet.getString(6));
lstSuppplierData.add(this.resultSet.getString(7));
lstSuppplierData.add(this.resultSet.getString(8));
}
}
catch (Exception e)
{
e.printStackTrace();
}
return lstSuppplierData;
}
public List<Object> getFoundSuppliers(String string)//获取指定ID供应商
{
List lstSupplierData = new ArrayList();
try {
this.conn = ConnectionFactory.getConnection();
this.statement = this.conn.createStatement();
this.resultSet = this.statement.executeQuery(string);
while (this.resultSet.next()) {
lstSupplierData.add(Integer.valueOf(this.resultSet.getInt(1)));
lstSupplierData.add(this.resultSet.getString(2));
lstSupplierData.add(this.resultSet.getString(3));
lstSupplierData.add(this.resultSet.getString(4));
lstSupplierData.add(this.resultSet.getString(5));
lstSupplierData.add(this.resultSet.getString(6));
lstSupplierData.add(this.resultSet.getString(7));
lstSupplierData.add(this.resultSet.getString(8));
}
}
catch (Exception e)
{
e.printStackTrace();
}
return lstSupplierData;
}
public ComboBoxModel getProductId() {
// TODO Auto-generated method stub
return null;
}
}