这个开发模式,程序调试时间最长,但只有现在多犯错,以后犯的错就会少点。最后在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可以用数据源连接替换进化!