TelBiz.java
package cn.edu.ccnu.imd.study.demo.vo;
public class TelVo {
private String no;
private String name;
private String tel;
private String qq;
private String address;
private String others;
private int id;
public void setNo(String no)
{
this.no = no;
}
public void setName(String name)
{
this.name = name;
}
public void setTel(String tel)
{
this.tel = tel;
}
public void setQq(String qq)
{
this.qq = qq;
}
public void setAddress(String address)
{
this.address = address;
}
public void setOthers(String others)
{
this.others = others;
}
public void setId(int id)
{
this.id = id;
}
public String getNo()
{
return no;
}
public String getName()
{
return name;
}
public String getTel()
{
return tel;
}
public String getQq()
{
return qq;
}
public String getAddress()
{
return address;
}
public String getOthers()
{
return others;
}
public int getId()
{
return id;
}
}
TelDao.java
package cn.edu.ccnu.imd.study.demo.dao;
import java.sql.*;
import java.util.*;
import cn.edu.ccnu.imd.study.demo.vo.TelVo;
public class TelDao {
public TelVo findbykey(Connection _conn, TelVo vo)
{
//定义 存放SQL语句的 StringBuffer
StringBuffer sbSQL = null;
//定义 PreparedStatement
PreparedStatement ps = null;
//定义数据库链接
Connection conn = null;
ResultSet rs=null;
try
{
conn = _conn;
//拼写sql
sbSQL = new StringBuffer();
sbSQL.append(" select * from Telx where ");
sbSQL.append(" id = ?");
//获得 数据库 prepareStatement
ps = conn.prepareStatement(sbSQL.toString());
int nIndex = 1;
ps.setLong(nIndex++, vo.getId());
//执行SQL
rs = ps.executeQuery();
while(rs.next()){
TelVo vo1= new TelVo();
vo1.setNo(rs.getString("no"));
vo1.setName(rs.getString("name"));
vo1.setTel(rs.getString("tel"));
vo1.setQq(rs.getString("qq"));
vo1.setAddress(rs.getString("address"));
vo1.setOthers(rs.getString("others"));
vo1.setId(rs.getInt("id"));
vo=vo1;
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
//关闭 ps
if (ps != null)
{
ps.close();
ps = null;
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
return vo;
}
public List findbyAll(Connection _conn, String strSql)
{
Vector v = new Vector();
//定义 存放SQL语句的 StringBuffer
StringBuffer sbSQL = null;
//定义 PreparedStatement
PreparedStatement ps = null;
//定义数据库链接
Connection conn = null;
ResultSet rs=null;
try
{
conn = _conn;
sbSQL = new StringBuffer();
sbSQL.append("select * from Telx where ");
sbSQL.append(strSql);
ps = conn.prepareStatement(sbSQL.toString());
//执行SQL
rs = ps.executeQuery();
while(rs.next()){
TelVo vo1= new TelVo();
vo1.setNo(rs.getString("no"));
vo1.setName(rs.getString("name"));
vo1.setTel(rs.getString("tel"));
vo1.setQq(rs.getString("qq"));
vo1.setAddress(rs.getString("address"));
vo1.setOthers(rs.getString("others"));
vo1.setId(rs.getInt("id"));
v.addElement(vo1);
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
//关闭 ps
if (ps != null)
{
ps.close();
ps = null;
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
return v;
}
public void delete(Connection _conn, TelVo vo)
{
//定义 存放SQL语句的 StringBuffer
StringBuffer sbSQL = null;
//定义 PreparedStatement
PreparedStatement ps = null;
//定义数据库链接
Connection conn = null;
try
{
//将obj强制转换为con
conn = _conn;
//拼写sql
sbSQL = new StringBuffer();
sbSQL.append(" delete from Telx where ");
sbSQL.append("id=?");
//获得 数据库 prepareStatement
ps = conn.prepareStatement(sbSQL.toString());
int nIndex = 1;
ps.setLong(nIndex++, vo.getId());
//执行SQL
ps.executeUpdate();
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
//关闭 ps
if (ps != null)
{
ps.close();
ps = null;
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
public void insert(Connection _conn, TelVo vo) {
// 定义 存放SQL语句的 StringBuffer
StringBuffer sbSQL = null;
// 定义 PreparedStatement
PreparedStatement ps = null;
// 定义数据库链接
Connection conn = null;
try {
// 将obj强制转换为con
conn = _conn;
// 拼写sql
sbSQL = new StringBuffer();
sbSQL.append("insert into telx ( no ,name ,tel ,qq ,address ,others ) ");
sbSQL.append(" values (?,?,?,?,?,?) ");
// 定义新增代码的 ID
// 获得 数据库 prepareStatement
ps = conn.prepareStatement(sbSQL.toString());
//System.out.println(sbSQL.toString());
/*
if(vo.get()==0){
String _sql = "select max() from Tel";
Statement _st=_conn.createStatement();
ResultSet rs = _st.executeQuery(_sql);
while(rs.next()){
vo.set(rs.getInt(1)+1);
}
}*/
// 定义参数递增变量
int nIndex = 1;
ps.setString(nIndex++, vo.getNo());
ps.setString(nIndex++, vo.getName());
ps.setString(nIndex++, vo.getTel());
ps.setString(nIndex++, vo.getQq());
ps.setString(nIndex++, vo.getAddress());
ps.setString(nIndex++, vo.getOthers());
// 执行SQL
ps.executeUpdate();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
public void update(Connection _conn, TelVo vo)
{
//定义 存放SQL语句的 StringBuffer
StringBuffer sbSQL = null;
//定义 PreparedStatement
PreparedStatement ps = null;
//定义数据库链接
Connection conn = null;
try
{
//将obj强制转换为con
conn = _conn;
//拼写sql
sbSQL = new StringBuffer();
sbSQL.append(
"update Telx set no=? ,name=?,tel=?,qq=?,address=?,others=? where id=?");
//sbSQL.append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ");
//获得 数据库 prepareStatement
ps = conn.prepareStatement(sbSQL.toString());
//定义参数递增变量
int nIndex = 1;
ps.setString(nIndex++, vo.getNo());
ps.setString(nIndex++, vo.getName());
ps.setString(nIndex++, vo.getTel());
ps.setString(nIndex++, vo.getQq());
ps.setString(nIndex++, vo.getAddress());
ps.setString(nIndex++, vo.getOthers());
ps.setInt(nIndex++, vo.getId());
//给SQL语句赋值
//#sql=#
//执行SQL
ps.executeUpdate();
}
catch (Exception ex)
{
//在dao中发生异常默认为系统级异常,将其抛出
ex.printStackTrace();
}
finally
{
try
{
//关闭 ps
if (ps != null)
{
ps.close();
ps = null;
}
}
catch (Exception ex)
{
//在dao中发生异常默认为系统级异常,将其抛出
ex.printStackTrace();
}
}
}
}
TelBiz.java
package cn.edu.ccnu.imd.study.demo.biz;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import cn.edu.ccnu.imd.study.demo.common.DBConnection;
import cn.edu.ccnu.imd.study.demo.dao.TelDao;
import cn.edu.ccnu.imd.study.demo.vo.TelVo;
public class TelBiz {
//增加通讯录
public void addTel(TelVo vo)
{
DBConnection dbc=new DBConnection();
if(dbc.getConnect())
{
Connection con = dbc.getConn();
TelDao dao = new TelDao();
dao.insert(con,vo);
try
{
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
else
{
System.out.println("数据库连接失败!");
}
}
public void updateTel(TelVo vo){
DBConnection dbc=new DBConnection();
if(dbc.getConnect()){
Connection con =dbc.getConn();
TelDao dao = new TelDao();
dao.update(con,vo);
try{
con.close();
}catch(Exception ex){
ex.printStackTrace();
}
}else{
System.out.println("数据库连接失败!");
}
}
//获取通讯录列表
public List getAllTel(String sql){
List _l = new ArrayList();
DBConnection dbc = new DBConnection();
if(dbc.getConnect()){
Connection conn = dbc.getConn();
try{
TelDao dao = new TelDao();
_l=dao.findbyAll(conn,sql);
}catch(Exception e){
e.printStackTrace();
}finally{
try{
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}else{
System.out.println("数据库连接失败!");
}
return _l;
}
//获取一条通讯录
public TelVo getATel(TelVo vo){
DBConnection dbc = new DBConnection();
if(dbc.getConnect()){
Connection conn = dbc.getConn();
try{
TelDao dao = new TelDao();
vo=dao.findbykey(conn,vo);
}catch(Exception e){
e.printStackTrace();
}finally{
try{
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}else{
System.out.println("数据库连接失败!");
}
return vo;
}
//删除通讯录
public TelVo delTel(TelVo vo){
DBConnection dbc = new DBConnection();
if(dbc.getConnect()){
Connection conn = dbc.getConn();
try{
TelDao dao = new TelDao();
dao.delete(conn,vo);
}catch(Exception e){
e.printStackTrace();
}finally{
try{
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}else{
System.out.println("数据库连接失败!");
}
return vo;
}
}
TelServlet.java
package cn.edu.ccnu.imd.study.demo.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.edu.ccnu.imd.study.demo.biz.TelBiz;
import cn.edu.ccnu.imd.study.demo.vo.TelVo;
public class TelServlet extends HttpServlet
{
private int action;
private String url;
private String path;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
this.doPost(request,response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
this.path=request.getContextPath();
this.url=this.path+"/servlets/Tel.html?action=0";
try{
String _action=request.getParameter("action");
this.action=Integer.parseInt(_action);
}catch(Exception e){
this.action=0;
}
TelVo vo=new TelVo();
switch(this.action){
//通讯录列表
case 0:
this.getTelList(request, response);
request.getRequestDispatcher(this.url).forward(request,response);
break;
//添加通讯录第一步
case 1:
this.url="/WEB-INF/jsp/TelAdd.jsp";
request.getRequestDispatcher(this.url).forward(request,response);
break;
//添加通讯录
case 2:
vo=this.getForm(request, response);
this.addATel(vo);
response.sendRedirect(this.url);
break;
//修改通讯录第一步
case 4:
this.updateTel1(request, response);
request.getRequestDispatcher(this.url).forward(request,response);
break;
//修改通讯录第二步
case 5:
vo=this.getForm(request, response);
this.updateTel2(request, response,vo);
response.sendRedirect(this.url);
break;
//修改通讯录状态
//删除通讯录
case 7:
this.delTel(request, response);
response.sendRedirect(this.url);
break;
default:
this.url=this.path+"/servlets/Tel.html?action=0";
}
}
//获取表单列表
void getTelList(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sql = "1=1" ;
TelBiz biz=new TelBiz();
List _l=biz.getAllTel(sql);
request.setAttribute("TelList",_l);
this.url="/WEB-INF/jsp/TelList.jsp";
}
//获取表单的植
TelVo getForm(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//定义变量取值
String no = "";
String name = "";
String tel = "";
String qq = "";
String address = "";
String others = "";
int id = 0;
//取值
String strno=request.getParameter("no");
String strname=request.getParameter("name");
String strtel=request.getParameter("tel");
String strqq=request.getParameter("qq");
String straddress=request.getParameter("address");
String strothers=request.getParameter("others");
String strid=request.getParameter("id");
try{
if(null != strno && !"".equals(strno) ){
no= strno;
}
if(null != strname && !"".equals(strname) ){
name= strname;
}
if(null != strtel && !"".equals(strtel) ){
tel= strtel;
}
if(null != strqq && !"".equals(strqq) ){
qq= strqq;
}
if(null != straddress && !"".equals(straddress) ){
address= straddress;
}
if(null != strothers && !"".equals(strothers) ){
others= strothers;
}
if(null != strid && !"".equals(strid) ){
id= Integer.parseInt(strid);
}
}catch(Exception e){
e.printStackTrace();
}
//创建VO实例
TelVo vo1=new TelVo();
vo1.setNo(no);
vo1.setName(name);
vo1.setTel(tel);
vo1.setQq(qq);
vo1.setAddress(address);
vo1.setOthers(others);
vo1.setId(id);
return vo1;
}
//添加通讯录
void addATel(TelVo vo) {
//创建BIZ
TelBiz ob=new TelBiz();
ob.addTel(vo);
}
//修改通讯录
void updateTel1(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//定义变量取值
int id ;
try{
id=Integer.parseInt(request.getParameter("id"));
}catch(Exception e){
id=0;
System.out.println("获取ID失败!");
}
// 创建VO
TelVo vo=new TelVo();
vo.setId(id);
//创建BIZ
TelBiz biz=new TelBiz();
vo=biz.getATel(vo);
//储存入request
request.setAttribute("TelVo",vo) ;
this.url="/WEB-INF/jsp/TelUpdate.jsp";
}
//修改通讯录第2步
void updateTel2(HttpServletRequest request, HttpServletResponse response,TelVo vo)
throws ServletException, IOException {
//定义变量取值
int id ;
try{
id=Integer.parseInt(request.getParameter("id"));
}catch(Exception e){
id=0;
System.out.println("获取ID失败!");
}
vo.setId(id);
//创建BIZ
TelBiz ob=new TelBiz();
ob.updateTel(vo);
}
//删除通讯录
void delTel(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//定义变量取值
int id ;
try{
id=Integer.parseInt(request.getParameter("id"));
}catch(Exception e){
id=0;
System.out.println("获取ID失败!");
}
TelVo vo=new TelVo();
vo.setId(id);
TelBiz ob=new TelBiz();
ob.delTel(vo);
}
}
问题:
1.页面显示之后,无法进行数据的添加和修改?
解决:在TelDao.java的insert方法中加入了一句ps.setString(nIndex++, vo.getId()); 而id是自动生成的,在输入数据的时候并没有输入id,导致获取失败,数据压根存不到数据库中,删除此语句后,成功添加。
但是依然不能修改数据。当时想到既然修改不了,说明id没有获取到,通过查找,发现在jsp文件中忘了获取id的语句,添加后,成功修改。
2.修改数据或添加数据问中文时,显示乱码?
解决:在数据库连接的配置文件中添加
useUnicode=true
characterEncoding=utf8