《大型数据库》实验六Oracle数据库应用系统解析

实验六 Oracle数据库应用系统班级： 计算机13-1 学号： 姓名： 刘炳君 成绩： 一、实验目的（1）掌握通过JDBC连接Oracle数据库的方法；（2）掌握使用JSP对数据库的操纵；（3）掌握Oracle数据库应用系统的实现方法。二、实验内容完成简单的药品信息管理系统：在Oracle数据库中创建用户表user、药品类别表category、药品表medicine、销售明细表sellDetail，采用JSP实现用户管理、类别管理、药品管理等功能。1、创建表、存储过程、触发器2、各功能模块的设计与实现1.登录界面Login.jsp if(self != top)alert();top.location = self.location;function login()var form = document.formsuserForm;if(form.elementsusername.value = )alert();form.elementsusername.focus();return;if(form.elementspassword.value = )alert();form.elementspassword.focus();return;form.submit(); img src=images/ border=0 usemap=#Map ： ： area shape=rect coords=265,140,323,154 href=area shape=rect coords=335,138,406,153 href=Main.jsp MyJsp.jsp Manager.jsp2药品类别信息管理（1）类别添加页面CategoryAction.javapackage com.lyq.struts.action;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import mons.beanutils.BeanUtils;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import org.jfree.chart.JFreeChart;import org.jfree.chart.servlet.ServletUtilities;import com.lyq.dao.CategoryDao;import com.lyq.persistence.Category;import com.lyq.struts.form.CategoryForm;import com.lyq.util.ChartUtil;/* * 药品类别Action类 * author Li Yong Qiang */public class CategoryAction extends BaseAction /添加或修改类别public ActionForward add(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception /获取类别信息CategoryForm cf = (CategoryForm)form;/创建Category对象Category c = new Category();c.setName(cf.getName();c.setDescription(cf.getDescription();c.setCreateTime(new Date();if(cf.getId() != 0)c.setId(cf.getId();CategoryDao dao = new CategoryDao();dao.saveOrUpdate(c);return mapping.findForward(paging);/查询类别public ActionForward findAll(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception List list = null;CategoryDao dao = new CategoryDao();list = dao.findByHQL(from Category);request.setAttribute(list, list);return mapping.findForward(findAllSuccess);/编辑类别public ActionForward edit(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception CategoryForm cf = (CategoryForm)form;/ 判断id是否有效if(cf.getId() 0)CategoryDao dao = new CategoryDao();Category c = dao.loadCategory(cf.getId();BeanUtils.copyProperties(cf, c);return mapping.findForward(edit);/删除类别public ActionForward delete(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception CategoryForm cf = (CategoryForm)form;/ 判断id是否有效if(cf.getId() 0)CategoryDao dao = new CategoryDao();Category c = dao.loadCategory(cf.getId();dao.delete(c);/删除类别return mapping.findForward(paging);/分页public ActionForward paging(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception /获取当前页码String currPage = request.getParameter(currPage);String action = request.getContextPath()+/baseData/category.do?command=paging;String hql = from Category;/分页查询Map map = this.getPage(hql, recPerPage, currPage, action,null);/将结果集放到request中request.setAttribute(list, map.get(list);/将结果集放到分页条中request.setAttribute(pagingBar, map.get(bar);return mapping.findForward(findAllSuccess);/ 统计药品类别数量public ActionForward findCategoryAndCound(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 实例化CategoryDao对象CategoryDao dao = new CategoryDao();/ 查询药品类别数量List list = dao.findCategoryAndCount();ChartUtil chartUtil = new ChartUtil();/ 创建JFreeChart实例JFreeChart chart = chartUtil.categoryChart(list);if(chart != null)/ 获取图片文件名String fileName = ServletUtilities.saveChartAsJPEG(chart,500,300,request.getSession(); / 获取图片地址String graphURL = request.getContextPath() + /DisplayChart?filename= + fileName; / 将图片地址放置到request中request.setAttribute(graphURL, graphURL);/ 页面转发return mapping.findForward(categoryGraph);（2）查看类别信息3. 药品信息管理（1）药品添加页面ManagerAction.javapackage com.lyq.struts.action;import java.io.FileOutputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import mons.beanutils.BeanUtils;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import org.apache.struts.upload.FormFile;import com.lyq.dao.CategoryDao;import com.lyq.dao.MedicineDao;import com.lyq.persistence.Category;import com.lyq.persistence.Medicine;import com.lyq.struts.form.MedicineForm;import com.lyq.util.QueryUtil;import com.lyq.util.StringUtil;/* * 药品Action类 * author Li Yong Qiang */public class MedicineAction extends BaseAction / 添加药品public ActionForward add(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception Medicine med = null;/ 获取MedicineFormMedicineForm df = (MedicineForm) form;MedicineDao dao = new MedicineDao();if (df.getMedCount() = 0) df.setMedCount(1);med = new Medicine();BeanUtils.copyProperties(med, df);CategoryDao cd = new CategoryDao();Category category = cd.loadCategory(df.getCategoryId();med.setCategory(category);/ 上传图片FormFile photo = df.getPhoto();if (photo != null & photo.getFileSize() 0) String realPath = this.getServlet().getServletContext().getRealPath(/upload);/ 将文件上传至服务器中指定的文件下try String fname = photo.getFileName(); / 获取上传文件名称if (fname.indexOf(.) != -1) / 获取文件的扩展名String endWith = fname.substring(fname.lastIndexOf(.),fname.length();/ 用当前的时间生成文件名称SimpleDateFormat sdf = new SimpleDateFormat(yyyyMMddHHmmssSSS);fname = sdf.format(new Date() + endWith;/ 创建写入服务器地址的输出流对象OutputStream out = new FileOutputStream(realPath + / + fname); out.write(photo.getFileData();/ 将文件写入到服务器out.flush();out.close();/ 将文件名称保存到数据库med.setPhotoPath(fname); catch (Exception e) e.printStackTrace();dao.saveOrUpdate(med);return mapping.findForward(addSuccess);/ 添加药品public ActionForward update(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 获取MedicineFormMedicineForm df = (MedicineForm) form;MedicineDao dao = new MedicineDao();/ 药品已存在，更新药品数量Medicine med = dao.loadMedicine(df.getId();int medCount = med.getMedCount();med.setMedCount(medCount + df.getMedCount();return mapping.findForward(addSuccess);/ 根据medNo查询药品public ActionForward findMedicineByMedNo(ActionMapping mapping,ActionForm form, HttpServletRequest request,HttpServletResponse response) throws Exception / 获取MedicineFormMedicineForm df = (MedicineForm) form;Medicine med = null;/ 查询药品if (df != null & df.getMedNo() != null) MedicineDao dao = new MedicineDao();med = dao.findMedicineByMedNo(df.getMedNo();/ 如果药品存在，更新数量，否则添加新药if (med != null) BeanUtils.copyProperties(df, med);request.setAttribute(med, med);return mapping.findForward(medUpdate); else CategoryDao cd = new CategoryDao();List list = cd.findByHQL(from Category);request.setAttribute(cs, list);return mapping.findForward(medSave);/ 分页查询药品信息public ActionForward paging(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 获取页码String currPage = request.getParameter(currPage);/ 构建action地址String action = request.getContextPath() + /baseData/med.do?command=paging;/ HQL查询语句String hql = from Medicine;/ 分页查询，返回Map对象Map map = this.getPage(hql, recPerPage, currPage, action, null);/将结果集放到request中request.setAttribute(list, map.get(list);/将结果集放到分页条中request.setAttribute(pagingBar, map.get(bar);return mapping.findForward(findAllSuccess);/ 查看药品详细信息public ActionForward view(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception String id = request.getParameter(id);Medicine med = null;/ 根据id查询药品信息if (id != null & !.equals(id) MedicineDao dao = new MedicineDao();med = dao.loadMedicineAndCategory(Integer.parseInt(id);request.setAttribute(med, med);return mapping.findForward(view);/ 编辑药品信息public ActionForward edit(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 获取MedicineFormMedicineForm df = (MedicineForm) form;Medicine med = null;/ 根据id查询药品if (df.getId() 0) MedicineDao dao = new MedicineDao();med = dao.loadMedicineAndCategory(df.getId();BeanUtils.copyProperties(df, med);df.setCategoryId(med.getCategory().getId();/ 获取类别信息CategoryDao categoryDao = new CategoryDao();List cs = categoryDao.findByHQL(from Category);request.setAttribute(cs, cs);return mapping.findForward(medSave);/ 多条件查询，分页显示结果public ActionForward query(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 获取MedicineFormMedicineForm df = (MedicineForm) form;String currPage = request.getParameter(currPage);/ 构造查询ActionString action = request.getContextPath() + /baseData/med.do?command=query;/构造HQL语句及分页条件Map mapQuery = QueryUtil.queryMedicine(df, currPage, action);String hql = (String) mapQuery.get(hql);action = (String) mapQuery.get(action);Object where = (Object) mapQuery.get(where);/ 分页查询Map map = this.getPage(hql, recPerPage, currPage, action, where);/将结果集放到request中request.setAttribute(list, map.get(list);/将结果集放到分页条中request.setAttribute(pagingBar, map.get(bar);return mapping.findForward(findAllSuccess);/ 模糊查询public ActionForward blurQuery(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws Exception / 获取关键词String keyWord = request.getParameter(keyWord);/ 获取当前页String currPage = request.getParameter(currPage);String hql = from Medicine d ;Object where = null;String action = request.getContextPath() + /baseData/med.do?command=blurQuery;/ 转码if (currPage != null & !currPage.isEmpty() keyWord = StringUtil.encodeZh(keyWord);if (keyWord != null & !keyWord.isEmpty() action += &keyWord= + StringUtil.encodeURL(keyWord);keyWord = % + keyWord + %;hql += where like ? or d.medNo like ? or d.factoryAdd like ? or d.description like ?;where = new Object keyWord, keyWord, keyWord, keyWord ;/ 分页查询Map map = this.getPage(hql, recPerPage, currPage, action, where);/将结果集放到request中request.setAttribute(list, map.get(list);/将结果集放到分页条中request.setAttribute(pagingBar, map.get(bar);return mapping.findForwar