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 MedicineDAO
{
private Connection conn; //连接对象
private Statement stmt; //通过简单SQL语句访问数据库
private PreparedStatement pstmt; //预编译的SQL语句访问数据库
private ResultSet rs; //结果集
private MedicineDTO medicineDTO;
public MedicineDAO()
{
conn = ConnectionDB.getConnection();
}
public Vector<MedicineDTO> fillMedicine()
{
Vector<MedicineDTO> users = new Vector<MedicineDTO>();
try
{
conn = ConnectionDB.getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery("select * from MedicineDictionary");
while(rs.next())
{
medicineDTO = new MedicineDTO();
medicineDTO.setMdCode(rs.getString(rs.findColumn("MDCode")));
medicineDTO.setMdName(rs.getString(rs.findColumn("MDName")));
medicineDTO.setSpellCode(rs.getString(rs.findColumn("SpellCode")));
medicineDTO.setTypeCode(rs.getString(rs.findColumn("TypeCode")));
medicineDTO.setSpecContent(rs.getString(rs.findColumn("SpecContent")));
medicineDTO.setMdUnit(rs.getString(rs.findColumn("MDUnit")));
medicineDTO.setMdDose(rs.getString(rs.findColumn("MDDose")));
medicineDTO.setIngredient(rs.getString(rs.findColumn("Ingredient")));
medicineDTO.setMdFunction(rs.getString(rs.findColumn("MDFunction")));
medicineDTO.setUsage(rs.getString(rs.findColumn("Usage")));
medicineDTO.setMemo(rs.getString(rs.findColumn("Memo")));
users.add(medicineDTO);
}
rs.close();
stmt.close();
conn.close();
}
catch (SQLException e)
{
System.out.print(e.getMessage());
}
return users;
}
public Vector<MedicineDTO> searchMedicine()
{
Vector<MedicineDTO> users = new Vector<MedicineDTO>();
try
{
conn = ConnectionDB.getConnection();
pstmt=conn.prepareStatement("select * from MedicineDictionary where MDCode like ? and MDName like ? and SpellCode like ? and TypeCode like ?");
pstmt.setString(1, medicineDTO.getMdCode() + "%");
pstmt.setString(2, "%" + medicineDTO.getMdName() + "%");
pstmt.setString(3, medicineDTO.getSpellCode() + "%");
pstmt.setString(4, medicineDTO.getTypeCode() + "%");
rs=pstmt.executeQuery();
while(rs.next())
{
medicineDTO = new MedicineDTO();
medicineDTO.setMdCode(rs.getString(rs.findColumn("MDCode")));
medicineDTO.setMdName(rs.getString(rs.findColumn("MDName")));
medicineDTO.setSpellCode(rs.getString(rs.findColumn("SpellCode")));
medicineDTO.setTypeCode(rs.getString(rs.findColumn("TypeCode")));
medicineDTO.setSpecContent(rs.getString(rs.findColumn("SpecContent")));
medicineDTO.setMdUnit(rs.getString(rs.findColumn("MDUnit")));
medicineDTO.setMdDose(rs.getString(rs.findColumn("MDDose")));
medicineDTO.setIngredient(rs.getString(rs.findColumn("Ingredient")));
medicineDTO.setMdFunction(rs.getString(rs.findColumn("MDFunction")));
medicineDTO.setUsage(rs.getString(rs.findColumn("Usage")));
medicineDTO.setMemo(rs.getString(rs.findColumn("Memo")));
users.add(medicineDTO);
}
rs.close();
pstmt.close();
conn.close();
}
catch (SQLException e)
{
System.out.print(e.getMessage());
}
return users;
}
public boolean addMedicine()
{
boolean flag = false;
try
{
conn = ConnectionDB.getConnection();
pstmt=conn.prepareStatement("insert into MedicineDictionary values(?,?,?,?,?,?,?,?,?,?,?)");
pstmt.setString(1, medicineDTO.getMdCode());
pstmt.setString(2, medicineDTO.getMdName());
pstmt.setString(3, medicineDTO.getSpellCode());
pstmt.setString(4, medicineDTO.getTypeCode());
pstmt.setString(5, medicineDTO.getSpecContent());
pstmt.setString(6, medicineDTO.getMdUnit());
pstmt.setString(7, medicineDTO.getMdDose());
pstmt.setString(8, medicineDTO.getIngredient());
pstmt.setString(9, medicineDTO.getMdFunction());
pstmt.setString(10, medicineDTO.getUsage());
pstmt.setString(11, medicineDTO.getMemo());
flag = (pstmt.executeUpdate() > 0);
pstmt.close();
conn.close();
}
catch (SQLException e)
{
flag = false;
System.out.print(e.getMessage());
}
return flag;
}
public boolean updateMedicine()
{
boolean flag = false;
try
{
conn = ConnectionDB.getConnection();
pstmt=conn.prepareStatement("update MedicineDictionary set MDName=?,SpellCode=?,TypeCode=?,SpecContent=?,MDUnit=?,MDDose=?,Ingredient=?,MDFunction=?,Usage=?,Memo=? where MDCode=?");
pstmt.setString(1, medicineDTO.getMdName());
pstmt.setString(2, medicineDTO.getSpellCode());
pstmt.setString(3, medicineDTO.getTypeCode());
pstmt.setString(4, medicineDTO.getSpecContent());
pstmt.setString(5, medicineDTO.getMdUnit());
pstmt.setString(6, medicineDTO.getMdDose());
pstmt.setString(7, medicineDTO.getIngredient());
pstmt.setString(8, medicineDTO.getMdFunction());
pstmt.setString(9, medicineDTO.getUsage());
pstmt.setString(10, medicineDTO.getMemo());
pstmt.setString(11, medicineDTO.getMdCode());
flag = (pstmt.executeUpdate() > 0);
pstmt.close();
conn.close();
}
catch (SQLException e)
{
flag = false;
System.out.print(e.getMessage());
}
return flag;
}
public boolean deleteMedicine()
{
boolean flag = false;
try
{
conn = ConnectionDB.getConnection();
pstmt=conn.prepareStatement("delete from MedicineDictionary where MDCode=?");
pstmt.setString(1, medicineDTO.getMdCode());
flag = (pstmt.executeUpdate() > 0);
pstmt.close();
conn.close();
}
catch (SQLException e)
{
flag = false;
System.out.print(e.getMessage());
}
return flag;
}
public Vector<String> getTypeCode()
{
Vector<String> typeCode = new Vector<String>();
try
{
conn = ConnectionDB.getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery("select TypeCode from MedicineType");
while(rs.next())
{
typeCode.add(rs.getString(rs.findColumn("TypeCode")));
}
rs.close();
stmt.close();
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
return typeCode;
}
public void setMedicineDTO(MedicineDTO medicineDTO) {
this.medicineDTO = medicineDTO;
}
}