|
#2
林月儿2017-06-25 09:52
 程序代码： /** * 得到patient查询结果 * @param con * @param pageBean * @param patient * @return * @throws Exception */ public ResultSet patientList(Connection con,PageBean pageBean,Patient patient,String zhuang,String jie)throws Exception{ StringBuffer sb=new StringBuffer("select * from t_patient"); if(StringUtil.isNotEmpty(patient.getPatientName())){ sb.append(" and patientName like '%"+patient.getPatientName()+"%'"); } //分页 if(pageBean!=null){ sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows()); } PreparedStatement pstmt=con.prepareStatement(sb.toString()); return pstmt.executeQuery(); } 是这个查询吗？ |
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>用户信息管理</title>
<script type="text/javascript">
var url;
//打开新增患者对话框
function openPatientAddDialog(){
//在勾选情况下点击新增要先清除数据
resetValue();
$("#dlg").dialog("open").dialog("setTitle","添加患者信息");
url="patient!save";//为url赋值
}
//重置对话框内数据
function resetValue(){
$("#patientName").val("");
$("#zhuangtai").combobox("setValue","");
$("#birthday").datebox("setValue","");
$("#chuli").datebox("setValue","");
$("#patientDesc").val("");
$("#didian").combobox("setValue","");
$("#jielun").combobox("setValue","");
$("#tel").val("");
$("#money").val("");
$("#baoxiao").val("");
}
//关闭对话框
function closePatientDialog(){
$("#dlg").dialog("close");
resetValue();
}
//提交新增患者数据
function savePatient(){
$("#fm").form("submit",{
url:url,
onSubmit:function(){
if($('#zhuangtai').combobox("getValue")==""){
$.messager.alert("系统提示","请选择手续状态");
return false;
}
if($('#didian').combobox("getValue")==""){
$.messager.alert("系统提示","请选择调查地点");
return false;
}
return $(this).form("validate");
},
success:function(result){
if(result.errorMsg){
$.messager.alert("系统提示",result.errorMsg);
return;
}else{
$.messager.alert("系统提示","保存成功");
resetValue();
$("#dlg").dialog("close");
$("#dg").datagrid("reload");
}
}
});
}
//删除选中的患者数据
function deletePatient(){
//获得选中数据对象
var selectedRows=$("#dg").datagrid('getSelections');
if(selectedRows.length==0){
$.messager.alert("系统提示","请选择要删除的数据！");
return;
}
var strIds=[];//要删除的序号组合
for(var i=0;i<selectedRows.length;i++){
strIds.push(selectedRows[i].patientId);
}
var ids=strIds.join(",");
$.messager.confirm("系统提示","您确认要删掉这<font color=red>"+selectedRows.length+"</font>条数据吗？",function(r){
if(r){
//ajax提交 delIds
$.post("patient!delete",{delIds:ids},function(result){
if(result.success){
$.messager.alert("系统提示","您已成功删除<font color=red>"+result.delNums+"</font>条数据！");
$("#dg").datagrid("reload");
}else{
$.messager.alert('系统提示',result.errorMsg);
}
},"json");
}
});
}
//修改患者资料
function openPatientModifyDialog(){
var selectedRows=$("#dg").datagrid('getSelections');
if(selectedRows.length!=1){
$.messager.alert("系统提示","请选择一条要编辑的数据！");
return;
}
var row=selectedRows[0];
$("#dlg").dialog("open").dialog("setTitle","编辑患者资料");
$("#patientName").val(row.patientName);
$("#zhuangtai").combobox("setValue",row.zhuangtai);
$("#birthday").datebox("setValue",row.birthday);
$("#chuli").datebox("setValue",row.chuli);
$("#patientDesc").val(row.patientDesc);
$("#didian").combobox("setValue",row.didian);
$("#jielun").combobox("setValue",row.jielun);
$("#tel").val(row.tel);
$("#money").val(row.money);
$("#baoxiao").val(row.baoxiao);
url="patient!save?patientId="+row.patientId;
}
//查询符合条件的用户
function searchPatient(){
$('#dg').datagrid('load',{
s_patientName:$('#s_patientName').val(""),
zhuang:$('#zhuang').combobox("getValue"),
jie:$('#jie').combobox("getValue"),
});
}
//倒出excel数据
function exportPatient(){
$('#export').form("submit",{
url:"patient!exportPatient"
})
//window.location.href="employee!ExportEmployee";
}
</script>
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">
<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>
</head>
<body style="margin:5px">
<table id="dg" title="患者信息" class="easyui-datagrid" fitColumns="true"
pagination="true" rownumbers="true" url="patient" fit="true" toolbar="#tb">
<thead>
<tr>
<th field="cb" checkbox="true"></th>
<th field="patientId" width="40" align="center">编号</th>
<th field="patientName" width="80" align="center">患者名称</th>
<th field="zhuangtai" width="80" align="center">手续状态</th>
<th field="didian" width="80" align="center">调查地点</th>
<th field="jielun" width="80" align="center">调查结论 </th>
<th field="birthday" width="80" align="center">调查日期</th>
<th field="chuli" width="80" align="center">处理日期</th>
<th field="tel" width="80" align="center">联系电话</th>
<th field="patientDesc" width="250" align="center">家庭住址</th>
<th field="money" width="80" align="center">花费金额</th>
<th field="baoxiao" width="80" align="center">报销金额</th>
</tr>
</thead>
</table>
<div id="tb">
<div>
<a href="javascript:openPatientAddDialog()" class="easyui-linkbutton" iconCls="icon-add" plain="true">添加</a>
<a href="javascript:openPatientModifyDialog()" class="easyui-linkbutton" iconCls="icon-edit" plain="true">修改</a>
<a href="javascript:deletePatient()" class="easyui-linkbutton" iconCls="icon-remove" plain="true">删除</a>
<a href="javascript:exportPatient()" class="easyui-linkbutton" iconCls="icon-export" plain="true">导出Execl</a>
</div>
<div>
<form id="export" method="post">
姓名： <input type="text" name="s_patientName" id="s_patientName" size="10"/>
手续状态： <select id="zhuang" name="zhuang" editable="false" panelHeight="auto" style="width: 155px">
<option value="">请选择...</option>
<option value="未调">未调</option>
<option value="已调">已调</option>
</select>
调查结论： <select id="jie" name="jie" editable="false" panelHeight="auto" style="width: 155px">
<option value="">请选择...</option>
<option value="正常">正常</option>
<option value="侧调">侧调</option>
<option value="拒赔">拒赔</option>
</select>
<a href="javascript:searchPatient()" class="easyui-linkbutton" iconCls="icon-search" plain="true">搜索</a>
</form>
</div>
</div>
<div id="dlg" class="easyui-dialog" style="width: 570px;height: 340px;padding: 10px 20px"
closed="true" buttons="#dlg-buttons">
<form id="fm" method="post" enctype="multipart/form-data">
<table cellspacing="5px;">
<tr>
<td>患者姓名：</td>
<td><input type="text" name="patient.patientName" id="patientName" class="easyui-validatebox" required/></td>
<td>手续状态：</td>
<td><select class="easyui-combobox" id="zhuangtai" name="patient.zhuangtai" editable="false" panelHeight="auto" style="width: 155px">
<option value="">请选择...</option>
<option value="未调">未调</option>
<option value="已调">已调</option>
</select></td>
</tr>
<tr>
<td>调查地点：</td>
<td><select class="easyui-combobox" id="didian" name="patient.didian" editable="false" panelHeight="auto" style="width: 155px">
<option value="">请选择...</option>
<option value="医院">医院</option>
<option value="实地">实地</option>
<option value="公司">公司</option>
<option value="无">无</option>
</select></td>
<td>调查结论：</td>
<td><select class="easyui-combobox" id="jielun" name="patient.jielun" editable="false" panelHeight="auto" style="width: 155px">
<option value="">请选择...</option>
<option value="正常">正常</option>
<option value="侧调">侧调</option>
<option value="拒赔">拒赔</option>
<option value="无">无</option>
</select></td>
</tr>
<tr>
<td>调查日期：</td>
<td><input class="easyui-datebox" name="patient.birthday" id="birthday" required editable="false" /></td>
<td>处理日期：</td>
<td><input class="easyui-datebox" name="patient.chuli" id="chuli" required editable="false" /></td>
</tr>
<tr>
<td>花费金额：</td>
<td><input type="text" name="patient.money" id="money" class="easyui-validatebox" required/></td>
<td>报销金额：</td>
<td><input type="text" name="patient.baoxiao" id="baoxiao" required/></td>
</tr>
<tr>
<td>联系电话：</td>
<td><input type="text" name="patient.tel" id="tel" class="easyui-validatebox" required/></td>
</tr>
<tr>
<td valign="top">家庭住址：</td>
<td colspan="4"><textarea rows="7" cols="46" name="patient.patientDesc" id="patientDesc"></textarea></td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons">
<a href="javascript:savePatient()" class="easyui-linkbutton" iconCls="icon-ok">保存</a>
<a href="javascript:closePatientDialog()" class="easyui-linkbutton" iconCls="icon-cancel">关闭</a>
</div>
</body>
</html>
Dao.java:
package
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import
import
import
import
public class PatientDao {
/**
* 得到patient查询结果
* @param con
* @param pageBean
* @param patient
* @return
* @throws Exception
*/
public ResultSet patientList(Connection con,PageBean pageBean,Patient patient,String zhuang,String jie)throws Exception{
StringBuffer sb=new StringBuffer("select * from t_patient");
if(StringUtil.isNotEmpty(patient.getPatientName())){
sb.append(" and patientName like '%"+patient.getPatientName()+"%'");
}
//分页
if(pageBean!=null){
sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
/**
* 获取数据总条数
* @param con
* @param grade
* @return
* @throws Exception
*/
public int patientCount(Connection con,Patient patient,String zhuang,String jie)throws Exception{
StringBuffer sb=new StringBuffer("select count(*) as total from t_patient");
if(StringUtil.isNotEmpty(patient.getPatientName())){
sb.append(" and patientName like '%"+patient.getPatientName()+"%'");
}
PreparedStatement pstmt=con.prepareStatement(sb.toString().replaceFirst("and", "where"));
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
return rs.getInt("total");
}else{
return 0;
}
}
/**
* 数据库插入数据
* @param con
* @param patient
* @return
* @throws Exception
*/
public int patientAdd(Connection con,Patient patient)throws Exception{
String sql="insert into t_patient values(null,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, patient.getPatientName());
pstmt.setString(2, patient.getZhuangtai());
pstmt.setString(3, DateUtil.formatDate(patient.getChuli(), "yyyy-MM-dd"));
pstmt.setString(4, DateUtil.formatDate(patient.getBirthday(), "yyyy-MM-dd"));
pstmt.setString(5, patient.getDidian());
pstmt.setString(6, patient.getJielun());
pstmt.setString(7, patient.getTel());
pstmt.setString(8, patient.getPatientDesc());
pstmt.setString(9, patient.getMoney());
pstmt.setString(10, patient.getBaoxiao());
return pstmt.executeUpdate();
}
/**
* 删除数据
* @param con
* @param delIds
* @return
* @throws Exception
*/
public int patientDelete(Connection con,String delIds)throws Exception{
String sql="delete from t_patient where patientId in("+delIds+")";
PreparedStatement pstmt=con.prepareStatement(sql);
return pstmt.executeUpdate();
}
/**
* 患者资料更改
* @param con
* @param patient
* @return
* @throws Exception
*/
public int patientModify(Connection con,Patient patient)throws Exception{
String sql="update t_patient set patientName=?,zhuangtai=?,birthday=?,chuli=?,didian=?,jielun=?,tel=?,patientDesc=?,money=?,baoxiao=? where patientId=?";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setInt(11, patient.getPatientId());
pstmt.setString(1, patient.getPatientName());
pstmt.setString(2, patient.getZhuangtai());
pstmt.setString(3, DateUtil.formatDate(patient.getChuli(), "yyyy-MM-dd"));
pstmt.setString(4, DateUtil.formatDate(patient.getBirthday(), "yyyy-MM-dd"));
pstmt.setString(5, patient.getDidian());
pstmt.setString(6, patient.getJielun());
pstmt.setString(7, patient.getTel());
pstmt.setString(8, patient.getPatientDesc());
pstmt.setString(9, patient.getMoney());
pstmt.setString(10, patient.getBaoxiao());
return pstmt.executeUpdate();
}
}
Avtion.java:
package
import
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.interceptor.ServletRequestAware;
import
import
import
import
import
import
import
import
import
import
import com.opensymphony.xwork2.ActionSupport;
public class PatientAction extends ActionSupport implements ServletRequestAware{
private Patient patient;//patient对象
private String page;//分页数据
private String rows;//分页数据
private String delIds;//批量删除数据的序列号
private String patientId;//修改患者资料传递的患者编号
//查询条件的变量
private String s_patientName;
private String zhuang;
private String jie;
public String getPage() {
return page;
}
public void setPage(String page) {
this.page = page;
}
public String getRows() {
return rows;
}
public void setRows(String rows) {
this.rows = rows;
}
public Patient getPatient() {
return patient;
}
public void setPatient(Patient patient) {
this.patient = patient;
}
public String getDelIds() {
return delIds;
}
public void setDelIds(String delIds) {
this.delIds = delIds;
}
public String getPatientId() {
return patientId;
}
public void setPatientId(String patientId) {
this.patientId = patientId;
}
public String getS_patientName() {
return s_patientName;
}
public void setS_patientName(String s_patientName) {
this.s_patientName = s_patientName;
}
public String getZhuang() {
return zhuang;
}
public void setZhuang(String zhuang) {
this.zhuang = zhuang;
}
public String getJie() {
return jie;
}
public void setJie(String jie) {
this.jie = jie;
}
DbUtil dbUtil=new DbUtil();
UserDao userDao=new UserDao();
PatientDao patientDao=new PatientDao();
HttpServletRequest request;
//当前用户
User currentUser;
@Override
public String execute() throws Exception {
// 获取Session
HttpSession session=request.getSession();
//链接connection
Connection con=null;
PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));//分页pageBean
if(patient==null){
patient=new Patient();
}
if(s_patientName!=null){
patient.setPatientName(s_patientName);
patient.setZhuangtai(zhuang);
patient.setJielun(jie);
}
try{
con=dbUtil.getCon();
JSONObject result=new JSONObject();
JSONArray jsonArray=JsonUtil.formatRsToJsonArray(patientDao.patientList(con, pageBean,patient,zhuang,jie));
int total=patientDao.patientCount(con,patient,zhuang,jie);
result.put("rows", jsonArray);
result.put("total", total);
ResponseUtil.write(ServletActionContext.getResponse(), result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
//保存患者数据(新增和修改)
public String save()throws Exception{
//判断patientId是否为空
if(StringUtil.isNotEmpty(patientId)){
patient.setPatientId(Integer.parseInt(patientId));
}
Connection con=null;
try{
con=dbUtil.getCon();
int saveNums=0;
JSONObject result=new JSONObject();
if(StringUtil.isNotEmpty(patientId)){
//不为空，修改患者信息
saveNums=patientDao.patientModify(con, patient);
}else{
//patientId为空，新增患者
saveNums=patientDao.patientAdd(con, patient);
}
if(saveNums>0){
result.put("success", "true");
}else{
result.put("success", "true");//业务逻辑,需要返回success，但返回的是错误message
result.put("errorMsg", "保存失败");
}
ResponseUtil.write(ServletActionContext.getResponse(), result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
//删除数据
public String delete()throws Exception{
Connection con=null;
try{
con=dbUtil.getCon();
JSONObject result=new JSONObject();
int delNums=patientDao.patientDelete(con, delIds);
if(delNums>0){
result.put("success", "true");
result.put("delNums", delNums);
}else{
result.put("errorMsg", "删除失败");
}
ResponseUtil.write(ServletActionContext.getResponse(), result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
//下拉框赋值 如果是管理员可以查询所有用户的患者，如果是用户只能显示当前用户患者
//输出excel表格（xls）
public void exportPatient() throws Exception{
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置excel每列宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建字体样式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBottomBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);// 设置字体
style.setWrapText(true);// 自动换行
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
HSSFCell cell=null;
// 创建一个Excel的单元格
String[] cellTitle = {"编号", "患者姓名", "手续状态","调查地点", "调查结论", "调查日期","处理日期","家庭住址"};
for (int i = 0; i < cellTitle.length; i++) {
cell = row.createCell(i);
// 给Excel的单元格设置样式和赋值
cell.setCellStyle(style);
cell.setCellValue(cellTitle[i]);
}
// 获取Session
HttpSession session=request.getSession();
//链接connection
Connection con=null;
if(patient==null){
patient=new Patient();
}
//将查询条件赋值给patient对象
if(s_patientName!=null){
patient.setPatientName(s_patientName);
patient.setZhuangtai(zhuang);
patient.setJielun(jie);
}
ResultSet rs=null;
try{
con=dbUtil.getCon();
rs=patientDao.patientList(con, null,patient,zhuang,jie);
int rowIndex=1;
while(rs.next()){
row = sheet.createRow(rowIndex++);
cell = row.createCell(0);
cell.setCellValue(rs.getInt("patientId"));
cell = row.createCell(1);
cell.setCellValue(rs.getString("patientName"));
cell = row.createCell(2);
cell.setCellValue(rs.getString("zhuangtai"));
cell = row.createCell(3);
cell.setCellValue(rs.getString("didian"));
cell = row.createCell(4);
cell.setCellValue(rs.getString("jielun"));
cell = row.createCell(5);
cell.setCellValue(rs.getString("birthday"));
cell = row.createCell(6);
cell.setCellValue(rs.getString("chuli"));
cell = row.createCell(7);
cell.setCellValue(rs.getString("patientDesc"));
}
String exportFileName = "patient.xls";
ServletActionContext.getResponse().setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName).getBytes(), "ISO8859-1"));//设定输出文件头
ServletActionContext.getResponse().setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型
OutputStream out = ServletActionContext.getResponse().getOutputStream();
wb.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void setServletRequest(HttpServletRequest request) {
// TODO Auto-generated method stub
this.request=request;
查询的功能就是实现不了 也不知道哪的问题 各位大神帮帮忙看看 谢谢
[此贴子已经被作者于2017-6-24 23:35编辑过]
我能给您发过去整个项目，您帮忙给看看行么 
