1. db.properties数据库配置参数
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
userpass=tiger
2. DBHelper采用数据库连接池,连接Oracle数据库
package com.ruide.db;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBHelper {
public static ComboPooledDataSource ds;
static{
ds=new ComboPooledDataSource();
InputStream in=DBHelper.class.getClassLoader().getResourceAsStream("db.properties");
Properties pro=new Properties();
try {
pro.load(in);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String driver=pro.getProperty("driver");
String url=pro.getProperty("url");
String username=pro.getProperty("username");
String userpass=pro.getProperty("userpass");
try {
ds.setDriverClass(driver);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ds.setJdbcUrl(url);
ds.setUser(username);
ds.setPassword(userpass);
}
}
3.DBManager类,数据库中的数据采用List集合封装(4种方法封装查询出的数据)
package com.ruide.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.ruide.mapper.IMapper;
public class DBManager{
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
public Connection getconn()throws SQLException{
//返回连接池对象
return DBHelper.ds.getConnection();
}
//1增删改函数
public int executeUpdate(String sql,Object[]params)throws SQLException{
//建连接
try {
conn=this.getconn();
//建预处理通道
pstm=conn.prepareStatement(sql);
if(params!=null){
for (int i = 0; i < params.length; i++) {
pstm.setObject(i+1, params[i]);
}
}
//执行sql
int result=pstm.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
throw e;
}finally{
try {
pstm.close();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//2对象查询函数
public List executeQueryObjectList(String sql,Object[]params,IMapper map)throws SQLException{
try {
//建连接
conn=this.getconn();
//建通道
pstm=conn.prepareStatement(sql);
//sql语句绑定数据
if(params!=null){
for(int i=0;i<params.length;i++){
pstm.setObject(i+1, params[i]);
}
}
//得到结果集
rs=pstm.executeQuery();
//封装结果集
List list=map.mapper(rs);
return list;
} catch (SQLException e) {
e.printStackTrace();
throw e;
}finally{
try {
rs.close();
pstm.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//3总条数查询
public int executeTotal(String sql,Object[]params)throws SQLException{
try {
//建连接
conn=this.getconn();
//建预处理通道
pstm=conn.prepareStatement(sql);
//sql语句绑定数据
if(params!=null){
for (int i = 0; i < params.length; i++) {
pstm.setObject(i+1, params[i]);
}
}
//得到结果集
rs=pstm.executeQuery();
//处理结果集
rs.next();
int total=rs.getInt(1);
return total;
} catch (SQLException e) {
e.printStackTrace();
throw e;
}finally{
try {
rs.close();
pstm.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//4.List集合的 数组方式封装结果集
public List<Object[]> executeQueryArrayList(String sql,Object[] params)throws SQLException{
try {
conn=this.getconn();
pstm=conn.prepareStatement(sql);
if(params!=null){
for (int i = 0; i < params.length; i++) {
pstm.setObject(i+1, params[i]);
}
}
rs=pstm.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
List<Object[]> list=new ArrayList<Object[]>();
while(rs.next()){
Object[] obj=new Object[count];
for (int i = 0; i < obj.length; i++) {
obj[i]=rs.getObject(i+1);
}
list.add(obj);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
throw e;
}finally{
try {
rs.close();
pstm.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//5.List集合的 HashMap方式封装结果集
public List<Map<String,Object>> executeQueryMapList(String sql,Object []params)throws SQLException{
try {
conn=this.getconn();
pstm=conn.prepareStatement(sql);
if(params!=null){
for (int i = 0; i < params.length; i++) {
pstm.setObject(i+1, params[i]);
}
}
rs=pstm.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
while(rs.next()){
Map<String,Object> map=new HashMap<String,Object>();
for (int i = 0; i < count; i++) {
String name=rsmd.getColumnName(i+1);
Object values=rs.getObject(name);
map.put(name,values);
}
list.add(map);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
throw e;
}finally{
try {
rs.close();
pstm.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//6.二维数组方式封装数据
public Object[][] executeQueryArray(String sql,Object []params)throws SQLException{
try {
List<Object[]> list=this.executeQueryArrayList(sql, params);
Object[][] obj=new Object[list.size()][];
for(int i=0;i<obj.length;i++){
obj[i] =list.get(i);
}
return obj;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw e;
}finally{
try {
rs.close();
pstm.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
4.以下vo包和mapper包中的代码是针对以上面向对象的封装的一部分
package com.ruide.vo;
public class Dept {
private int deptno;
private String dname;
private String loc;
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
package com.ruide.vo;
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private double sal;
private double comm;
private int deptno;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
package com.ruide.mapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public interface IMapper {
public List mapper(ResultSet rs)throws SQLException;
}
package com.ruide.mapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ruide.vo.Dept;
public class DeptMapper implements IMapper{
public List mapper(ResultSet rs)throws SQLException{
List<Dept> list=new ArrayList<Dept>();
while(rs.next()){
int deptno=rs.getInt(1);
String dname=rs.getString(2);
String loc=rs.getString(3);
Dept d=new Dept();
d.setDeptno(deptno);
d.setDname(dname);
d.setLoc(loc);
list.add(d);
}
return list;
}
}
package com.ruide.mapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import com.ruide.vo.Emp;
public class EmpMapper implements IMapper {
public List mapper(ResultSet rs)throws SQLException{
List<Emp> list=new ArrayList<Emp>();
while(rs.next()){
int empno=rs.getInt(1);
String ename=rs.getString(2);
String job=rs.getString(3);
int mgr=rs.getInt(4);
//String hiredate=rs.getString(5);
java.sql.Date d=rs.getDate(5);
//java.sql.Date 是 java.util.Date子类
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String hiredate=sdf.format(d);
double sal=rs.getDouble(6);
double comm=rs.getDouble(7);
int deptno=rs.getInt(8);
Emp e=new Emp();
e.setEmpno(empno);
e.setEname(ename);
e.setJob(job);
e.setMgr(mgr);
e.setHiredate(hiredate);
e.setSal(sal);
e.setComm(comm);
e.setDeptno(deptno);
list.add(e);
}
return list;
}
}
5.dao包中主要是Sql语句以及分页查询语句,返回的是封装好的结果集
package com.ruide.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.ruide.db.DBManager;
import com.ruide.mapper.DeptMapper;
import com.ruide.mapper.IMapper;
import com.ruide.vo.Dept;
public class DeptDAO {
//增加
public int save(Dept d)throws SQLException{
String sql=" insert into dept values(?,?,?) ";
Object []params={d.getDeptno(),d.getDname(),d.getLoc()};
DBManager db=new DBManager();
int hang=db.executeUpdate(sql, params);
return hang;
}
//修改
public int merge(Dept d)throws SQLException{
String sql="update dept set dname=?,loc=? where deptno=?";
Object []params={d.getDname(),d.getLoc(),d.getDeptno()};
DBManager db=new DBManager();
return db.executeUpdate(sql, params);
}
//删除
public int delete(int deptno)throws SQLException{
String sql=" delete from dept where deptno=? ";
Object []params={deptno};
DBManager db=new DBManager();
return db.executeUpdate(sql, params);
}
//对象查询语句
public List queryObjectList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.deptno,A.dname,A.loc from ";
sql+=" (select d.*,rownum rn from dept d) A ";
sql+=" where rn>? and rownum<=? ";
Object[] params={(pagenow-1)*pagesize,pagesize};
IMapper mapper=new DeptMapper();
DBManager db=new DBManager();
List list=db.executeQueryObjectList(sql,params, mapper);
return list;
}
//总条数查询
public int querytotal()throws SQLException{
String sql="select count(*) from dept ";
DBManager db=new DBManager();
int total=db.executeTotal(sql, null);
return total;
}
//list 数组查询
public List<Object[]> queryObjectArrayList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.* from ";
sql+=" (select d.*,rownum rn from dept d) A ";
sql+=" where rn>? and rownum<=? ";
Object []params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
return db.executeQueryArrayList(sql, params);
}
//list HashMap查询
public List<Map<String,Object>> queryMapList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+="select A.* from ";
sql+=" (select d.*,rownum rn from dept d) A ";
sql+=" where rn>? and rownum<=? ";
Object[] params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
return db.executeQueryMapList(sql, params);
}
//二维数组查询
public Object[][] queryObjectArray(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+="select A.* from ";
sql+=" (select d.*,rownum rn from dept d) A ";
sql+=" where rn>? and rownum<=? ";
Object[]params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
return db.executeQueryArray(sql, params);
}
//特定对象查询(根据主键)
public Dept queryById(int deptno)throws SQLException{
String sql="select * from dept where deptno=? ";
Object[] params={deptno};
IMapper mapper=new DeptMapper();
DBManager db=new DBManager();
List<Dept> list=db.executeQueryObjectList(sql, params, mapper);
Dept d=list.get(0);
return d;
}
public List queryAll()throws SQLException{
String sql="select *from dept";
IMapper mapper=new DeptMapper();
DBManager db=new DBManager();
List<Dept> list=db.executeQueryObjectList(sql, null, mapper);
return list;
}
}
package com.ruide.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.ruide.db.DBManager;
import com.ruide.mapper.EmpMapper;
import com.ruide.mapper.IMapper;
import com.ruide.vo.Emp;
public class EmpDAO {
//1.增加
public int save(Emp e)throws SQLException{
String sql=" insert into emp values(?,?,?,?,to_date(?,'yyyy-MM-dd'),?,?,?) ";
Object[]params={e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno()};
DBManager db=new DBManager();
return db.executeUpdate(sql, params);
}
//2.修改
public int merge(Emp e)throws SQLException{
String sql="update emp set ename=?,job=?,mgr=?,hiredate=to_date(?,'yyyy-MM-dd'),sal=?,comm=?,deptno=? where empno=?";
Object[]params={e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),e.getEmpno()};
DBManager db=new DBManager();
return db.executeUpdate(sql, params);
}
//3.删除
public int delete(int empno)throws SQLException{
String sql="delete from emp where empno=?";
Object[]params={empno};
DBManager db=new DBManager();
return db.executeUpdate(sql, params);
}
//4.总条数查询
public int queryTotal()throws SQLException{
String sql=" select count(*) from emp ";
DBManager db=new DBManager();
int total=db.executeTotal(sql, null);
return total;
}
//5.根据主键查询
public Emp queryById(int empno)throws SQLException{
String sql=" select *from emp where empno=? ";
Object []params={empno};
IMapper mapper=new EmpMapper();
DBManager db=new DBManager();
List<Emp> list=db.executeQueryObjectList(sql, params, mapper);
Emp e=list.get(0);
return e;
}
//6.List 集合对象查询
public List queryObjectList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.empno,A.ename,A.job,A.mgr,A.hiredate,A.sal,A.comm,A.deptno from ";
sql+=" (select rownum rn,e.* from emp e ) A ";
sql+=" where rn>? and rownum<=? ";
Object[] params={(pagenow-1)*pagesize,pagesize};
IMapper mapper=new EmpMapper();
DBManager db=new DBManager();
List<Emp> list=db.executeQueryObjectList(sql,params, mapper);
return list;
}
//7.List 集合数组的查询
public List<Object[]> queryObjectArrayList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.* from ";
sql+=" (select e.*,rownum rn from emp e ) A ";
sql+=" where rn>? and rownum<=?";
Object []params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
return db.executeQueryArrayList(sql, params);
}
//8.List 集合Map方式查询
public List<Map<String,Object>> queryMapList(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.* from ";
sql+=" (select e.*,rownum rn from emp e ) A ";
sql+=" where rn>? and rownum<=? ";
Object[]params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
return db.executeQueryMapList(sql, params);
}
//二维数组方式查询
public Object[][] queryObjectArray(int pagesize,int pagenow)throws SQLException{
String sql="";
sql+=" select A.* from ";
sql+=" (select e.*,rownum rn from emp e ) A ";
sql+=" where rn>? and rownum<=? ";
Object[]params={(pagenow-1)*pagesize,pagesize};
DBManager db=new DBManager();
Object[][] obj=db.executeQueryArray(sql, params);
return obj;
}
public List queryAll()throws SQLException{
String sql="select *from emp ";
IMapper mapper=new EmpMapper();
DBManager db=new DBManager();
List<Emp> list=db.executeQueryObjectList(sql, null, mapper);
return list;
}
}
6.filter包主要是过滤器:主要过滤编码方式
package com.ruide.filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class EncodingFilter implements Filter{
public void init(FilterConfig filterConfig)throws ServletException{
}
public void destroy(){
}
public void doFilter(ServletRequest request,ServletResponse response,FilterChain chain)
throws ServletException,IOException{
HttpServletRequest request1=(HttpServletRequest)request;
HttpServletResponse response1=(HttpServletResponse)response;
request1.setCharacterEncoding("UTF-8");
response1.setCharacterEncoding("UTF-8");
chain.doFilter(request1, response1);
}
}
7.action包中主要是servlet类,主要用来接收客户端请求和处理客户端请求并给客户响应,与jsp页面交互,
(1)dept表的展示(查),保存(增),删除 ,修改(需要2个servlet)
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.DeptDAO;
import com.ruide.vo.Dept;
public class PreShowDeptAction extends HttpServlet {
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
DeptDAO dao=new DeptDAO();
//List<Dept> list=dao.queryAll();
//请求当前页
String strpagenow=request.getParameter("pagenow");
int pagenow;
if(strpagenow==null){
pagenow=1;
}else{
pagenow=new Integer(strpagenow);
}
//每页大小
int pagesize=2;
List<Dept> list=dao.queryObjectList(pagesize, pagenow);
int total=dao.querytotal();//总条数
int totalpage=(total-1)/pagesize+1;//总页数
//request可以带值传递,把封装好的List集合对象放在request容器中
request.setAttribute("st",list);
request.setAttribute("pagenow", pagenow);
request.setAttribute("pagesize", pagesize);
request.setAttribute("total", total);
request.setAttribute("totalpage", totalpage);
//在哪里展示?请求转发到jsp页面
RequestDispatcher disp=request.getRequestDispatcher("showdept.jsp");
disp.forward(request, response);//同质体
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.DeptDAO;
import com.ruide.vo.Dept;
public class SaveDeptAction extends HttpServlet {
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
//request.setCharacterEncoding("UTF-8");
String deptno=request.getParameter("deptno");
String dname=request.getParameter("dname");
String loc=request.getParameter("loc");
Dept d=new Dept();
d.setDeptno(new Integer(deptno));
d.setDname(dname);
d.setLoc(loc);
try {
DeptDAO dao=new DeptDAO();
int hang=dao.save(d);
response.sendRedirect("preshowdept");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.DeptDAO;
import com.ruide.vo.Dept;
public class DeleteDeptAction extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
String deptno=request.getParameter("deptno");
DeptDAO dao=new DeptDAO();
int hang=dao.delete(new Integer(deptno));
response.sendRedirect("preshowdept");
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.DeptDAO;
import com.ruide.vo.Dept;
public class PreUpdateDeptAction extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
String strdeptno=request.getParameter("deptno");
int deptno=new Integer(strdeptno);
DeptDAO dao=new DeptDAO();
Dept d=dao.queryById(deptno);
//把对象放入request范围
request.setAttribute("d", d);
RequestDispatcher disp=request.getRequestDispatcher("updatedept.jsp");
disp.forward(request, response);
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.DeptDAO;
import com.ruide.vo.Dept;
public class UpdateDeptAction extends HttpServlet{
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
//request.setCharacterEncoding("utf-8");
String deptno=request.getParameter("deptno");
String dname=request.getParameter("dname");
String loc=request.getParameter("loc");
Dept d=new Dept();
d.setDeptno(new Integer(deptno));
d.setDname(dname);
d.setLoc(loc);
DeptDAO dao=new DeptDAO();
int hang=dao.merge(d);
response.sendRedirect("preshowdept");
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
(2)emp表的展示(查),保存(增),删除 ,修改(需要2个servlet)
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.EmpDAO;
import com.ruide.vo.Dept;
import com.ruide.vo.Emp;
public class PreShowEmpAction extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
EmpDAO dao=new EmpDAO();
try {
//List<Emp> list=dao.queryAll();
//请求当前页
String strpagenow=request.getParameter("pagenow");
int pagenow;
if(strpagenow==null){
pagenow=1;
}else{
pagenow=new Integer(strpagenow);
}
//每页大小
//int pagesize=2;
String strpagesize=request.getParameter("pagesize");
int pagesize;
if(strpagesize==null){
pagesize=4;
}else{
pagesize=new Integer(strpagesize);
}
List<Emp> list=dao.queryObjectList(pagesize, pagenow);
int total=dao.queryTotal();//总条数
int totalpage=(total-1)/pagesize+1;//总页数
//request是可以带值传递的
request.setAttribute("list", list);
request.setAttribute("pagenow", pagenow);
request.setAttribute("pagesize", pagesize);
request.setAttribute("total", total);
request.setAttribute("totalpage", totalpage);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//请求转发
RequestDispatcher disp=request.getRequestDispatcher("showemp.jsp");
disp.forward(request, response);
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.EmpDAO;
import com.ruide.vo.Emp;
public class SaveEmpAction extends HttpServlet{
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
//request.setCharacterEncoding("UTF-8");//千万不要忘记设置编码方式,否则出现异常
//(1)获取用户在文本框中输入的信息
String empno=request.getParameter("empno");
String ename=request.getParameter("ename");
String job=request.getParameter("job");
String mgr=request.getParameter("mgr");
String hiredate=request.getParameter("hiredate");
String sal=request.getParameter("sal");
String comm=request.getParameter("comm");
String deptno=request.getParameter("deptno");
//(2)封装对象,即调用setXXX方法,
Emp e=new Emp();
e.setEmpno(new Integer(empno));
e.setEname(ename);
e.setJob(job);
e.setMgr(new Integer(mgr));
e.setHiredate(hiredate);
e.setSal(new Double(sal));
e.setComm(new Double(comm));
e.setDeptno(new Integer(deptno));
//调用DAO中的save方法保存数据
try {
EmpDAO dao=new EmpDAO();
int hang=dao.save(e);
response.sendRedirect("preshowemp");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.EmpDAO;
public class DeleteEmpAction extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
String empno=request.getParameter("empno");
EmpDAO dao=new EmpDAO();
int hang=dao.delete(new Integer(empno));
response.sendRedirect("preshowemp");
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.EmpDAO;
import com.ruide.vo.Emp;
public class PreUpdateEmpAction extends HttpServlet {
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
try {
String strempno=request.getParameter("empno");
int empno=new Integer(strempno);
EmpDAO dao=new EmpDAO();
Emp e=dao.queryById(empno);
request.setAttribute("e", e);
RequestDispatcher disp=request.getRequestDispatcher("updateemp.jsp");
disp.forward(request, response);
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.ruide.action;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ruide.dao.EmpDAO;
import com.ruide.vo.Emp;
public class UpdateEmpAction extends HttpServlet {
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
//request.setCharacterEncoding("UTF-8");
String empno=request.getParameter("empno");
String ename=request.getParameter("ename");
String job=request.getParameter("job");
String mgr=request.getParameter("mgr");
String hiredate=request.getParameter("hiredate");
String sal=request.getParameter("sal");
String comm=request.getParameter("comm");
String deptno=request.getParameter("deptno");
Emp e=new Emp();
e.setEmpno(new Integer(empno));
e.setEname(ename);
e.setJob(job);
e.setMgr(new Integer(mgr));
e.setHiredate(hiredate);
e.setSal(new Double(sal));
e.setComm(new Double(comm));
e.setDeptno(new Integer(deptno));
EmpDAO dao=new EmpDAO();
try {
int hang=dao.merge(e);
response.sendRedirect("preshowemp");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
8.web.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<filter>
<filter-name>encoding</filter-name>
<filter-class>com.ruide.filter.EncodingFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>encoding</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- 2016-8-4 修改,分页查询-->
<servlet>
<servlet-name>preupdateemp</servlet-name>
<servlet-class>com.ruide.action.PreUpdateEmpAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>preupdateemp</servlet-name>
<url-pattern>/preupdateemp</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>updateemp</servlet-name>
<servlet-class>com.ruide.action.UpdateEmpAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>updateemp</servlet-name>
<url-pattern>/updateemp</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>preupdatedept</servlet-name>
<servlet-class>com.ruide.action.PreUpdateDeptAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>preupdatedept</servlet-name>
<url-pattern>/preupdatedept</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>updatedept</servlet-name>
<servlet-class>com.ruide.action.UpdateDeptAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>updatedept</servlet-name>
<url-pattern>/updatedept</url-pattern>
</servlet-mapping>
<!--2016-8-3 保存,增加,删除 -->
<servlet>
<servlet-name>preshowemp</servlet-name>
<servlet-class>com.ruide.action.PreShowEmpAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>preshowemp</servlet-name>
<url-pattern>/preshowemp</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>preshowdept</servlet-name>
<servlet-class>com.ruide.action.PreShowDeptAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>preshowdept</servlet-name>
<url-pattern>/preshowdept</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>saveemp</servlet-name>
<servlet-class>com.ruide.action.SaveEmpAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>saveemp</servlet-name>
<url-pattern>/saveemp</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>savedept</servlet-name>
<servlet-class>com.ruide.action.SaveDeptAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>savedept</servlet-name>
<url-pattern>/savedept</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>deletedept</servlet-name>
<servlet-class>com.ruide.action.DeleteDeptAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>deletedept</servlet-name>
<url-pattern>/deletedept</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>deleteemp</servlet-name>
<servlet-class>com.ruide.action.DeleteEmpAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>deleteemp</servlet-name>
<url-pattern>/deleteemp</url-pattern>
</servlet-mapping>
</web-app>
9.jsp页面(jsp的本质是个特殊的servlet类)主要用来展示数据,发送请求
(1)dept表的数据展示页面,数据保存页面,数据修改页面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="com.ruide.vo.Dept" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'showdept.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
function doclick(){
var value=document.getElementById("pagenow").value;
window.location.href="preshowdept?pagenow="+value;
}
function dochange(obj){
var value=obj.value;
window.location.href="preshowdept?pagenow="+value;
}
</script>
</head>
<body>
<%
Object objpagenow=request.getAttribute("pagenow");
int pagenow=new Integer(objpagenow.toString());
Object objpagesize=request.getAttribute("pagesize");
int pagesize=new Integer(objpagesize.toString());
Object objtotal=request.getAttribute("total");
int total=new Integer(objtotal.toString());
Object objtotalpage=request.getAttribute("totalpage");
int totalpage=new Integer(objtotalpage.toString());
%>
<table border="1" align="center" width="800">
<tr>
<th colspan="4" align="center">部门信息表</th>
</tr>
<tr>
<td>部门编号</td>
<td>部门名称</td>
<td>部门地址</td>
<td>操作</td>
</tr>
<%
List<Dept> list=(List<Dept>)request.getAttribute("st");
for(int i=0;i<list.size();i++){
Dept d=list.get(i);
%>
<tr>
<td><%=d.getDeptno() %></td>
<td><%=d.getDname() %></td>
<td><%=d.getLoc() %></td>
<td><a href="deletedept?deptno=<%=d.getDeptno()%>">删除</a>
<a href="preupdatedept?deptno=<%=d.getDeptno() %>">修改</a>
</td>
</tr>
<%
}
%>
<tr><td colspan="4"><a href="savedept.jsp">增加部门信息</a></td></tr>
<tr>
<td colspan="4">
<%if(pagenow==1){ %>
首页 上一页
<% }else{%>
<a href="preshowdept?pagenow=1"> 首页</a>
<a href="preshowdept?pagenow=<%=pagenow-1%>">上一页 </a>
<%} %>
<%if(pagenow==totalpage){ %>
下一页 尾页
<%}else{ %>
<a href="preshowdept?pagenow=<%=pagenow+1 %>">下一页</a>
<a href="preshowdept?pagenow=<%=totalpage %>">尾页</a>
<%} %>
共<%=totalpage %>页
当前<%=pagenow %>页
每页<%=pagesize %>条
共<%=total %>条
请输入<input id="pagenow" value="<%=pagenow %>" size="2"/>
<input type="button" onclick="doclick()" value="跳转">
<select onchange="dochange(this)">
<%
for(int i=1;i<totalpage;i++){
%>
<option <%=pagenow==i?"selected='true'":"" %>value="<%=i%>">第<%=i%>页</option>
<%} %>
</select>
</td>
</tr>
</table>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'savedept.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="savedept" method="post">
<table border="1" width="400" align="center">
<tr><td align="center">新增部门信息</td></tr>
<tr><td>部门编号 <input type="text" name="deptno"></td></tr>
<tr><td>部门名称 <input type="text" name="dname"></td></tr>
<tr><td>部门地址 <input type="text" name="loc"></td></tr>
<tr><td>
<input type="submit" value="保存">
<input type="reset" value="重置">
</td></tr>
</table>
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="com.ruide.vo.Dept" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'updatedept.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<% Dept d=(Dept)request.getAttribute("d"); %>
<form action="updatedept" method="post">
<table border="1" align="center">
<tr><td align="center">修改部门信息表数据</td></tr>
<tr><td>部门编号:<input type="text" name="deptno" value="<%=d.getDeptno() %>"></td></tr>
<tr><td>部门名称:<input type="text" name="dname" value="<%=d.getDname()%>"></td></tr>
<tr><td>部门地址:<input type="text" name="loc" value="<%=d.getLoc() %>"></td></tr>
<tr><td>
<input type="submit" value="修改">
<input type="reset" value="重置">
</td></tr>
</table>
</form>
</body>
</html>
(2)emp表的数据展示页面,数据保存页面,数据修改页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="com.ruide.vo.Emp" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'showemp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
function doclick1(){//传递每页大小pagesize
var value=document.getElementById("pagesizeid").value;
window.location.href="preshowemp?pagesize="+value;
}
function doclick(){//跳转方法
var valuenow=document.getElementById("pagenowid").value;
var valuesize=document.getElementById("pagesizeid").value;
window.location.href="preshowemp?pagenow="+valuenow+"&pagesize="+valuesize;
}
function dochange(obj){
var value=obj.value;
var valuesize=document.getElementById("pagesizeid").value;
window.location.href="preshowemp?pagenow="+value+"&pagesize="+valuesize;
}
</script>
</head>
<body>
<%
//获取当前页码
Object objpagenow=request.getAttribute("pagenow");
int pagenow=new Integer(objpagenow.toString());
//每页大小(即每页有多少条数据)
Object objpagesize=request.getAttribute("pagesize");
int pagesize=new Integer(objpagesize.toString());
//总条数
Object objtotal=request.getAttribute("total");
int total=new Integer(objtotal.toString());
//总页数
Object objtotalpage=request.getAttribute("totalpage");
int totalpage=new Integer(objtotalpage.toString());
%>
<table border="1" align="center">
<tr>
<th colspan="9">员工信息表</th>
</tr>
<tr>
<td>员工编号</td>
<td>员工姓名</td>
<td>员工工作</td>
<td>上级编号</td>
<td>入职日期</td>
<td>员工工资</td>
<td>员工奖金</td>
<td>部门编号</td>
<td>操作</td>
</tr>
<%
List<Emp> list=(List<Emp>)request.getAttribute("list");
for(int i=0;i<list.size();i++){
Emp e=list.get(i);
%>
<tr>
<td><%=e.getEmpno() %></td>
<td><%=e.getEname() %></td>
<td><%=e.getJob() %></td>
<td><%=e.getMgr() %></td>
<td><%=e.getHiredate() %></td>
<td><%=e.getSal() %></td>
<td><%=e.getComm() %></td>
<td><%=e.getDeptno() %></td>
<td><a href="deleteemp?empno=<%=e.getEmpno()%>">删除</a>
<a href="preupdateemp?empno=<%=e.getEmpno()%>">修改</a>
</td>
</tr>
<%
}
%>
<tr><td colspan="9"><a href="saveemp.jsp">增加员工信息</a></td></tr>
<tr>
<td colspan="9">
<%if(pagenow==1){ %>
首页 上一页
<% }else{%>
<a href="preshowemp?pagenow=1&pagesize=<%=pagesize %>"> 首页</a>
<a href="preshowemp?pagenow=<%=pagenow-1%>&pagesize=<%=pagesize %>">上一页 </a>
<%} %>
<%if(pagenow==totalpage){ %>
下一页 尾页
<%}else{ %>
<a href="preshowemp?pagenow=<%=pagenow+1 %>&pagesize=<%=pagesize %>">下一页</a>
<a href="preshowemp?pagenow=<%=totalpage %>&pagesize=<%=pagesize %>">尾页</a>
<%} %>
共<%=totalpage %>页
当前<%=pagenow %>页
每页<%=pagesize %>条
共<%=total %>条
每页展示条数<input id="pagesizeid" value="<%=pagesize%>" size="1" />
<input type="button" onclick="doclick1()" value="展示">
请输入页码<input id="pagenowid" value="<%=pagenow%>" size="1" />
<input type="button" onclick="doclick()" value="转到该页" />
<select onchange="dochange(this)">
<%
for(int i=1;i<=totalpage;i++){
%>
<option <%=pagenow==i?"selected='true'":"" %>value="<%=i %>">第<%=i%>页</option>
<%} %>
</select>
</td>
</tr>
</table>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'saveemp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="saveemp" method="post">
<table border="1" align="center" width="400">
<tr>
<th>新增员工信息</th>
</tr>
<tr>
<td>员工编号 <input type="text" name="empno" /></td>
</tr>
<tr>
<td>员工姓名 <input type="text" name="ename" /></td>
</tr>
<tr>
<td>员工工作 <input type="text" name="job" /></td>
</tr>
<tr>
<td>上级编号 <input type="text" name="mgr" /></td>
</tr>
<tr>
<td>入职日期 <input type="text" name="hiredate" /></td>
</tr>
<tr>
<td>员工工资 <input type="text" name="sal" /></td>
</tr>
<tr>
<td>员工奖金 <input type="text" name="comm" /></td>
</tr>
<tr>
<td>部门编号 <input type="text" name="deptno" /></td>
</tr>
<tr>
<td align="center">
<input type="submit" value="保存信息">
<input type="reset" value="重置信息">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="com.ruide.vo.Emp" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'updateemp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<% Emp e=(Emp)request.getAttribute("e"); %>
<form action="updateemp" method="post">
<table border="1" align="center" width="400">
<tr><th align="center">修改员工信息表</th></tr>
<tr><td>员工编号 <input type="text" name="empno" value="<%=e.getEmpno() %>"></td></tr>
<tr><td>员工姓名 <input type="text" name="ename" value="<%=e.getEname() %>"></td></tr>
<tr><td>员工工作 <input type="text" name="job" value="<%=e.getJob() %>"></td></tr>
<tr><td>上级编号 <input type="text" name="mgr" value="<%=e.getMgr() %>"></td></tr>
<tr><td>入职日期 <input type="text" name="hiredate" value="<%=e.getHiredate() %>"></td></tr>
<tr><td>员工工资 <input type="text" name="sal" value="<%=e.getSal() %>"></td></tr>
<tr><td>员工奖金 <input type="text" name="comm" value="<%=e.getComm() %>"></td></tr>
<tr><td>部门编号 <input type="text" name="deptno" value="<%=e.getDeptno() %>"></td></tr>
<tr>
<td>
<input type="submit" value="确定修改"/>
<input type="reset" value="重置" />
</td>
</tr>
</table>
</form>
</body>
</html>