DAO设计模式开发

这个开发模式，程序调试时间最长，但只有现在多犯错，以后犯的错就会少点。最后在MLDN 的BBS论坛上提问，中心的李祺老师帮助解答了，这里帮他们打打广告，MLDN，不错哦！
一、dao开发

/*===========删除数据库=============*/drop database if exists zz;/*===========创建数据库=============*/create database zz;/*===========使用数据库=============*/use zz;/*===========删除数据表=============*/drop table if exists emp;/*===========创建数据表=============*/create table emp(empno int(8) primary key,ename varchar(20),job varchar(9),hiredate date,sal float(11,2));

Emp.java

package zz.vo;import java.util.Date;public class Emp{private int empno;private String ename;private String job;private Date hiredate;private float sal;public void setEmpno(int empno){// 雇员编号this.empno = empno;}public int getEmpno(){return this.empno;}public void setName(String ename){// 雇员姓名this.ename = ename;}public String getName(){return this.ename;}public void setJob(String job){// 雇员职位this.job =job;}public String getJob(){return this.job;}public void setHiredate(Date hiredate){// 雇员入职日期this.hiredate = hiredate;}public Date getHiredate(){return this.hiredate;}public void setSal(float sal){// 雇员工资this.sal = sal;}public float getSal(){return this.sal;}}

DatabaseConnetion.java

package zz.dbc;import java.sql.DriverManager;import java.sql.Connection;public class DatabaseConnection{public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";public static final String DBURL = "jdbc:mysql://localhost:3306/zz";public static final String DBUSER = "root";public static final String DBPASSWORD = "mysql";private Connection conn = null; public DatabaseConnection() throws Exception{// 在构造方法中连接数据库try{Class.forName(DBDRIVER);this.conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);}catch (Exception e){throw e;}}public Connection getConnection(){return this.conn;}public void close() throws Exception{if (this.conn!=null){try{this.conn.close();}catch (Exception e){throw e;}}}}

IEmpDAO.java

package zz.dao;import java.util.List;import zz.vo.Emp;public interface IEmpDAO{/**  *数据的增加操作，一般以doXxx的方式命名  *@param emp 要增加的数据对象  *@return 是否增加成功的标记  *@throws Exception 有异常交给被调用处处理  */  public boolean doCreate(Emp emp) throws Exception;/**查询全部的数据，一般以finaXxx的方式命名 *@param keyWord 查询的关键字 *@return ,返回全部的查询结果，每一个Emp对象表示表的一行记录  *@throws Exception */  public List<Emp> findAll(String keyWord)throws Exception;/**根据雇员编号查询雇员信息 *@param empno雇员编号 *@return 雇员的VO对象  *@throws Exception */ public Emp findByNo(int empno)throws Exception;}

EmpDAOImpl.java

package zz.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.List;import java.util.ArrayList;import java.sql.ResultSet;import zz.vo.Emp;import zz.dao.IEmpDAO;public class EmpDAOImpl implements IEmpDAO{private Connection conn = null;private PreparedStatement pstmt = null;public EmpDAOImpl(Connection conn){this.conn = conn;}public boolean doCreate(Emp emp)throws Exception{boolean flag = false;String sql = "INSERT INTO emp(empno, ename, job, hiredate, sal) VALUES (?,?,?,?,?)";this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setInt(1, emp.getEmpno());this.pstmt.setString(2, emp.getName());this.pstmt.setString(3, emp.getJob());this.pstmt.setDate(4, new java.sql.Date(emp.getHiredate().getTime()));this.pstmt.setFloat(5, emp.getSal());if (this.pstmt.executeUpdate() > 0){flag = true;}this.pstmt.close();return flag;}public List<Emp> findAll(String keyWord)throws Exception{List<Emp> all = new ArrayList<Emp>();// 定义集合，接收全部数据String sql = "SELECT empno, ename, job, hiredate, sal FROM Emp WHERE ename LIKE ? OR job LIKE ?";this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setString(1, "%" + keyWord + "%");// 设置关键字this.pstmt.setString(2, "%" + keyWord + "%");// 设置关键字ResultSet rs = this.pstmt.executeQuery();// 执行查询操作Emp emp = null;// 定义Emp对象while (rs.next()){// 依次取出每一条数据emp = new Emp();emp.setEmpno(rs.getInt(1));// 设置empno内容emp.setName(rs.getString(2));emp.setJob(rs.getString(3));emp.setHiredate(rs.getDate(4));emp.setSal(rs.getFloat(5));all.add(emp);// 向集合中增加对象}this.pstmt.close();return all;}public Emp findByNo(int empno)throws Exception{Emp emp = null;String sql = "SELECT empno, ename, job, hiredate, sal FROM emp WHERE empno=?";this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setInt(1, empno);// 错误！设置雇员编号ResultSet rs = this.pstmt.executeQuery();// 执行查询操作if (rs.next()){emp = new Emp();emp.setEmpno(rs.getInt(1));emp.setName(rs.getString(2));emp.setJob(rs.getString(3));emp.setHiredate(rs.getDate(4));emp.setSal(rs.getFloat(5));}this.pstmt.close();return emp;}}

EmpDAOProxy.java

package zz.dao.proxy;import java.util.List;import zz.vo.Emp;import zz.dbc.DatabaseConnection;import zz.dao.IEmpDAO;import zz.dao.impl.EmpDAOImpl;public class EmpDAOProxy implements IEmpDAO {private DatabaseConnection dbc = null;// 定义数据库的连接private IEmpDAO dao = null;// 定义DAO对象public EmpDAOProxy()throws Exception{this.dbc = new DatabaseConnection();this.dao = new EmpDAOImpl(this.dbc.getConnection());}public boolean doCreate(Emp emp) throws Exception{boolean flag = false;try{if (this.dao.findByNo(emp.getEmpno())==null){// 错误！如果插入的雇员编号不存在flag = this.dao.doCreate(emp);// 调用真实主题操作}}catch (Exception e){e.printStackTrace();}finally{this.dbc.close();}return flag;}public List<Emp> findAll(String keyWord)throws Exception{List<Emp> all = null;try{all = this.dao.findAll(keyWord);}catch (Exception e){e.printStackTrace();}finally{this.dbc.close();}return all;}public Emp findByNo(int empno)throws Exception{Emp emp = null;try{emp = this.dao.findByNo(empno);}catch (Exception e){e.printStackTrace();}finally{this.dbc.close();}return emp;}}

TestDAOInsert.java

package zz.dao.test;import zz.vo.Emp;import zz.factory.DAOFactory;public class TestDAOInsert{public static void main(String args[])throws Exception{Emp emp = null;for (int x = 1; x <= 5; x++){emp = new Emp();emp.setEmpno(1007 + x);emp.setName("张三四-" + x);emp.setJob("清洁员-" + x);emp.setHiredate(new java.util.Date());emp.setSal(500 * x);DAOFactory.getIEmpDAOInstance().doCreate(emp);}}}

二、JSP调用 DAO

D:\ProgramFiles\webdemo\7_java_bean\emp_insert.jsp

<%@ page contentType="text/html" pageEncoding="GBK"%><html><head><title>增加雇员</title></head><body><form action="emp_insert_do.jsp" method="post">雇员编号：<input type="text" name ="empno"><br>雇员姓名：<input type="text" name ="ename"><br>雇员职位：<input type="text" name ="job"><br>雇员日期：<input type="text" name ="hiredate"><br>雇员工资：<input type="text" name ="sal"><br><input type="submit" value="注册"><input type="reset" value="重置"></form></body></html>

D:\ProgramFiles\webdemo\7_java_bean\emp_insert_do.jsp

<%@ page contentType="text/html" pageEncoding="GBK"%><%@ page import="zz.factory.*,zz.vo.*"%><%@ page import="java.text.*"%><html><head><title>完成增加雇员操作</title></head><% request.setCharacterEncoding("GBK");%><body><%Emp emp = new Emp();emp.setEmpno(Integer.parseInt(request.getParameter("empno")));emp.setName(request.getParameter("ename"));emp.setJob(request.getParameter("job"));emp.setHiredate(new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("hiredate")));emp.setSal(Float.parseFloat(request.getParameter("sal")));try {if (DAOFactory.getIEmpDAOInstance().doCreate(emp)){%><h3>雇员信息添加成功</h3><%}else {%><h3>雇员信息添加失败</h3><%}}catch(Exception e){e.printStackTrace();}%></body></html>

D:\ProgramFiles\webdemo\7_java_bean\emp_search.jsp

<%@ page contentType="text/html" pageEncoding="GBK"%><%@ page import="zz.factory.*,zz.vo.*"%><%@ page import="java.util.*"%><html><head><title>完成增加雇员操作</title></head><% request.setCharacterEncoding("GBK");%><body><%try {String keyWord = request.getParameter("kw");// 接收查询的关键字if (keyWord==null){// 判断是否有传递的关键字参数keyWord="";// 如果为空(没有关键字)表示查询全部！}List<Emp> all = DAOFactory.getIEmpDAOInstance().findAll(keyWord);Iterator<Emp> iter = all.iterator();%><center><form action = "emp_search.jsp" method="post">请输入查询的关键字<input type="text" name = "kw"><input type="submit" value="百度"></form><table border="1" width="80%"><tr><td>雇员编号</td><td>雇员姓名</td><td>雇员工作</td><td>雇佣日期</td><td>雇员工资</td></tr><%while (iter.hasNext()){Emp emp = iter.next();%><tr><td><%=emp.getEmpno()%></td><td><%=emp.getName()%></td><td><%=emp.getJob()%></td><td><%=emp.getHiredate()%></td><td><%=emp.getSal()%></td></tr><%}%></table></center><%}catch(Exception e){e.printStackTrace();}%></body></html>

问题：
编译错误：

Exception in thread "main"com.mysql.jdbc.exceptions.jdbc4.CommunicationsExcepti

on: Communicationslink failure
老师解答：

你的EmpDAOProxy中的添加方法写了死循环，应该是this.dao.doCreate()，你写成了this.doCreate()

问题解决：
1/端口号写错，3360 - - > 3306
2/

              ResultSetrs = this.pstmt.executeQuery();            //执行查询操作

              if (rs.next()){         // 修改为if

编译后再出错：

java.lang.NullPointerException

        atzz.dao.impl.EmpDAOImpl.findByNo(EmpDAOImpl.java:57)

        atzz.dao.proxy.EmpDAOProxy.doCreate(EmpDAOProxy.java:19)

        atzz.dao.test.TestDAOInsert.main(TestDAOInsert.java:15)

解决：

             56行： PreparedStatementpstmt pstmt= this.conn.prepareStatement(sql);// 应该是this.pstmt,之前已经定义过

              57行：this.pstmt.setInt(1,empno);   // 编译提示这行错误！设置雇员编号

              ResultSetrs = this.pstmt.executeQuery();            //执行查询操作

注意：
1、在写等于时要像念经一样念：“等于等于”两遍！！！
2、一个页面绝不容许导入SQL包，最好一个不要！
3、数据库连接类DatabaseConnection可以用数据源连接替换进化！