联系人作业

Oracle数据库建表

contacts表

-- Create table
create table T_CONTACTS
(
  id      NUMBER not null,
  name    VARCHAR2(20) not null,
  tel     VARCHAR2(15) not null,
  groupid NUMBER not null
)
tablespace TEST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_CONTACTS
  add constraint PK_CONTACTS primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table T_CONTACTS
  add constraint F_CONTACTS foreign key (GROUPID)
  references T_GROUPS (ID);

 

group表

-- Create table
create table T_GROUPS
(
  id   NUMBER not null,
  name VARCHAR2(4) not null
)
tablespace TEST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_GROUPS
  add constraint PK_GROUP primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

contacts序列

-- Create sequence 
create sequence SQ_CONTACTS_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 41
increment by 1
cache 20;

 

 

com.hanqi.common包

JDBC连接方法

package com.hanqi.common;

import java.sql.*;

public class DBHelper {
    
    public static Connection getConnection() throws Exception
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        
        String id = "JDBC_TEST";
        
        String pw = "test";
        
        Connection conn = DriverManager.getConnection(url, id, pw);    
        
        return conn;
    }

}

 

com.hanqi.dao包

Contacts表实体类

package com.hanqi.dao;

public class Contact {
    
    private int Id;
    
    private String Name;
    
    private String Tel;
    
    private int GroupId;
    
    private String Group;
    
    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }
    
    
    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public String getTel() {
        return Tel;
    }

    public void setTel(String tel) {
        Tel = tel;
    }

    public int getGroupId() {
        return GroupId;
    }

    public void setGroupId(int groupId) {
        GroupId = groupId;
    }

    public String getGroup() {
        return Group;
    }

    public void setGroup(String group) {
        Group = group;
    }

    
    
}

 

数据库操作类

package com.hanqi.dao;

import java.sql.*;
import java.util.ArrayList;

import com.hanqi.common.DBHelper;

public class ContactsDal {

    
    //增加联系人
    public void insert(Contact contact) throws Exception
    {
        Connection conn = DBHelper.getConnection();
    
        PreparedStatement ps = null;
        try
        {
            if(conn != null)
            {
                String sql = "insert into t_contacts (id,name,tel,groupid) values (SQ_contactS_ID.nextval,?,?,?)";
            
                ps = conn.prepareStatement(sql);
            
                ps.setString(1, contact.getName());
                ps.setString(2, contact.getTel());
                ps.setInt(3, contact.getGroupId());
            
                ps.executeUpdate();
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                ps.close();
            }
            catch(Exception e)
            {}
                conn.close();
        }
        
    }
    
    
    //查询联系人
    
    public ArrayList<Contact> selectAll() throws Exception
    {
        Connection conn = DBHelper.getConnection();
    
        PreparedStatement ps = null;
        
        ResultSet rs = null;
        
        ArrayList<Contact> al = new ArrayList<Contact>();
        
        try
        {
            if(conn != null)
            {
                String sql = "SELECT c.id as i,c.name as a,c.tel as b,g.name as c from t_contacts c,t_groups g where c.groupid = g.id";
                
                ps = conn.prepareStatement(sql);
                
                rs = ps.executeQuery();
                
                if(rs != null)
                {
                    while(rs.next())
                    {
                        Contact c = new Contact();
                    
                        c.setId(rs.getInt("i"));
                        c.setName(rs.getString("a"));
                        c.setTel(rs.getString("b"));
                        c.setGroup(rs.getString("c"));
                        
                        al.add(c);
                    }
                }
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                rs.close();
            }
            catch(Exception e)
            {}
            try
            {
                ps.close();
            }
            catch(Exception e)
            {}
                conn.close();
        }
        return al;
    }    
    
    
    //修改联系人
    public void update(Contact contact) throws Exception
    {
        Connection conn = DBHelper.getConnection();
        
        PreparedStatement ps = null;
        
        try
        {
            if(conn != null)
            {    
                String sql = "update t_contacts set name = ?, tel = ?, groupid = ? where id = ?";
            
                ps = conn.prepareStatement(sql);
                
                ps.setString(1, contact.getName());
                ps.setString(2, contact.getTel());
                ps.setInt(3, contact.getGroupId());
                ps.setInt(4, contact.getId());
                
                ps.executeUpdate();
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                ps.close();
            }
            catch(Exception e)
            {}
                conn.close();
        }
    }
    
    
    //删除联系人
    public void deleteById(int id) throws Exception
    {

        Connection conn = DBHelper.getConnection();
        
        PreparedStatement ps = null;
        
        if(conn != null)
        {
            try
            {    
                String sql ="delete from t_contacts where id =?";
            
                ps = conn.prepareStatement(sql);
            
                ps.setInt(1, id);
            
                ps.executeUpdate();
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                try
                {
                    ps.close();
                }
                catch(Exception e)
                {}
                    conn.close();
            }
        }
    }    
    
}

 

首页JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="com.hanqi.dao.*" %>
        <%@ page import="java.util.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>电话本</title>

<script type="application/javascript" src="jquery/jquery-1.11.3.js"></script>

<script type="application/javascript">

$(document).ready(function(e) {
    $("#p").hide();  //默认隐藏
    $("#hide").click(function(){$("#p").hide();})
    $("#show").click(function(){$("#p").show();})
}); 


function sc()
{
    return confirm("是否删除数据？删除数据会将下级地区全部删除！")
}

</script>

</head>

<body>
<table border="1" width="30%">
<tr align="center">
<td width="30%"></td>
<td width="20%">姓名</td>
<td width="30%">电话</td>
<td width="20%">分组</td>

<%


    ContactsDal cd = new ContactsDal();

    ArrayList<Contact> al = cd.selectAll();

    if(al != null)
    {
        for(Contact c : al)
        {
            out.print("<tr> <td><a href='xiugai.jsp?id="+c.getId()+"&name="+c.getName()+"&tel="+c.getTel()+" '>修改</a>&nbsp;&nbsp;<a href='shanchu?id="+c.getId()+"' onclick='return sc();'>删除</a></td> <td>"+c.getName()+"</td> <td>"+c.getTel()+"</td> <td>"+c.getGroup()+"</td> </tr>");
        }
    }
    else
    {
        out.print("查询数据失败");
    }

%>


</tr>
</table>

<input id="show" type="button" value="添加新联系人"  />

<form id="p" action="tianjia" method="get">

姓名<input name="name" type="text" width="15" />
<br>
电话<input name="tel" type="text" width="15" />
<br>
分组<select name="groupid">
<option value="1">同学</option>
<option value="2">同事</option>
<option value="3">家人</option>
<option value="4">朋友</option>
<option value="5">其他</option>
</select>
<br>
<input type="submit" value="提交" />

<input id="hide" type="button" value="取消" />

</form>

</body>
</html>

 

修改页JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改联系人</title>
</head>
<body>

<%
String id = request.getParameter("id");
String name = new String(request.getParameter("name").getBytes("ISO-8859-1"),"UTF-8");
String tel = request.getParameter("tel");
%>

<form action="xiugai" method="get">

<input type="hidden" name="id" value="<%= id %>" /> 

姓名<input type="text" name="name" width="10" value="<%= name %>" />

电话<input type="text" name="tel" width="15" value="<%= tel %>" />

<select name="groupid">
分组
<option value="1">同学</option>
<option value="2">同事</option>
<option value="3">家人</option>
<option value="4">朋友</option>
<option value="5">其他</option>
</select>
<br>
<input type="submit" value="提交修改" />

</form>


</body>
</html>

 

 

添加联系人servlet

package com.hanqi;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.hanqi.dao.Contact;
import com.hanqi.dao.ContactsDal;

@WebServlet("/tianjia")
public class tianjia extends HttpServlet {
    private static final long serialVersionUID = 1L;
   
    public tianjia() {
        super();
        
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        request.setCharacterEncoding("UTF-8");    
        response.setContentType("text/html; charset=UTF-8");
        
        String name = new String(request.getParameter("name").getBytes("ISO-8859-1"),"UTF-8");
        String tel = request.getParameter("tel");
        String groupid = request.getParameter("groupid");
        
        if(name != null && name.trim().length() > 0)
        {
            if(tel != null && tel.trim().length() > 0)
            {
                if(groupid != null && groupid.trim().length() > 0)
                {
                    int gid = Integer.parseInt(groupid);
                    
                    Contact c = new Contact();
                    
                    c.setName(name);
                    c.setTel(tel);
                    c.setGroupId(gid);
                    
                    ContactsDal cd = new ContactsDal(); 
                    
                    try 
                    {
                        cd.insert(c);
                        
                        response.sendRedirect("Index.jsp");
                    } 
                    catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }
                else
                {
                    response.getWriter().append("请选择联系人分组");
                }
            }
            else
            {
                response.getWriter().append("请输入"+ name +"电话" );
            }
        }
        else
        {
            response.getWriter().append("请输入联系人姓名");
        }
        
        
        //response.getWriter().append("Served at: ").append(request.getContextPath());
    }


    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}

 

删除联系人servlet

package com.hanqi;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.hanqi.dao.ContactsDal;


@WebServlet("/shanchu")
public class shanchu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
   
    public shanchu() {
        super();
        
    }

    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        String id = request.getParameter("id");
        
        if(id != null)
        {
            int i = Integer.parseInt(id);
            
            ContactsDal cd = new ContactsDal();
            
            try 
            {
                cd.deleteById(i);
                
                response.sendRedirect("Index.jsp");
            } 
            catch (Exception e) 
            {
                e.printStackTrace();
            }
        }
        
        
        
        
        //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}

 

修改联系人servlet

package com.hanqi;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hanqi.common.*;
import com.hanqi.dao.*;
@WebServlet("/xiugai")
public class xiugai extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
   
    public xiugai() {
        super();
       
    }

    
    
    @SuppressWarnings("unused")
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        request.setCharacterEncoding("UTF-8");    
        response.setContentType("text/html; charset=UTF-8");
        
        String name = new String(request.getParameter("name").getBytes("ISO-8859-1"),"UTF-8");
        String tel = request.getParameter("tel");
        String groupid = request.getParameter("groupid");
        String id = request.getParameter("id");
        

        if(name != null && name.trim().length() > 0)
        {
            if(tel != null && tel.trim().length() > 0)
            {
                if(groupid != null && groupid.trim().length() > 0)
                {
                    if(id != null)
                    {
                        int gid = Integer.parseInt(groupid);
                        int i = Integer.parseInt(id);
                    
                        Contact c = new Contact();
                    
                        c.setName(name);
                        c.setTel(tel);
                        c.setGroupId(gid);
                        c.setId(i);
                    
                        ContactsDal cd = new ContactsDal(); 
                    
                        try 
                        {
                            cd.update(c);
                        
                            response.sendRedirect("Index.jsp");
                        } 
                        catch (Exception e)
                        {
                            e.printStackTrace();
                        }
                    }
                    else
                    {
                        response.getWriter().append("联系人ID为空");
                    }
                }
                else
                {
                    response.getWriter().append("请选择联系人分组");
                }
            }
            else
            {
                response.getWriter().append("请输入"+ name +"电话" );
            }
        }
        else
        {
            response.getWriter().append("请输入联系人姓名");
        }
        
        
        
        //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}