第一步,打开数据库连接
上图,代码在图下
package SqlDB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import log4j.Log4jMain;
public class SqlDB {
private static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=ContactsBook";
private static final String user= "sa";
private static final String password="123456";
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
try {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL,user,password);
return conn;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
return null;
}
/*
* 关闭数据库连接,注意关闭的顺序
*/
public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
if(rs!=null){
try{
rs.close();
rs=null;
}catch(SQLException e){
e.printStackTrace();
}
}
if(ps!=null){
try{
ps.close();
ps=null;
}catch(SQLException e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
conn=null;
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
第二步 创建实体类
package CategoryBean;
public class TB_Contacts {
int contactsID;
String Name;
String telPhone;
String address;
String mobilePhone;
String eMail;
String reMark;
String type;
String belong;
public int getContactsID() {
return contactsID;
}
public void setContactsID(int contactsID) {
this.contactsID = contactsID;
}
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
public String getTelPhone() {
return telPhone;
}
public void setTelPhone(String telPhone) {
this.telPhone = telPhone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getMobilePhone() {
return mobilePhone;
}
public void setMobilePhone(String mobilePhone) {
this.mobilePhone = mobilePhone;
}
public String geteMail() {
return eMail;
}
public void seteMail(String eMail) {
this.eMail = eMail;
}
public String getReMark() {
return reMark;
}
public void setReMark(String reMark) {
this.reMark = reMark;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getBelong() {
return belong;
}
public void setBelong(String belong) {
this.belong = belong;
}
public TB_Contacts(){};
public TB_Contacts( String Name,String telPhone,String address,String mobilePhone,String eMail,String reMark,String type,String belong){
this.Name=Name;
this.telPhone=telPhone;
this.address=address;
this.mobilePhone=mobilePhone;
this.eMail=eMail;
this.reMark=reMark;
this.type=type;
this.belong=belong;
}
public TB_Contacts(int contactsID,String Name,String telPhone,String address,String mobilePhone,String eMail,String reMark,String type,String belong){
this.contactsID=contactsID;
this.Name=Name;
this.telPhone=telPhone;
this.address=address;
this.mobilePhone=mobilePhone;
this.eMail=eMail;
this.reMark=reMark;
this.type=type;
this.belong=belong;
}
}
第三步:创建dao
</pre><pre name="code" class="java">package CategoryDao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import CategoryBean.TB_Contacts;
import CategoryBean.Tb_Users;
import SqlDB.SqlDB;
public class ContactsDao {
private Connection conn=SqlDB.getConnection();
TB_Contacts tb_Contacts=null;
//增加联系人
public void addUser(TB_Contacts tb_Contacts){
try {
String sql="insert into Contacts(Name,telPhone,address,mobilePhone,eMail,reMark,type,belong)values(?,?,?,?,?,?,?,?)";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, tb_Contacts.getName());
ptmt.setString(2, tb_Contacts.getTelPhone());
ptmt.setString(3, tb_Contacts.getAddress());
ptmt.setString(4, tb_Contacts.getMobilePhone());
ptmt.setString(5, tb_Contacts.geteMail());
ptmt.setString(6, tb_Contacts.getReMark());
ptmt.setString(7, tb_Contacts.getType());
ptmt.setString(8, tb_Contacts.getBelong());
ptmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//删除联系人
public void deleteUser(int contactsID ){
try {
String sql="delete from Contacts where contactsID=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, contactsID);
ptmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//修改联系人信息
public void updateUser(TB_Contacts tb_Contacts){
try {
String sql="update Contacts set Name=?,telPhone=?,address=?,mobilePhone=?,eMail=?,reMark=?,type=?,belong=? where contactsID=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, tb_Contacts.getName());
ptmt.setString(2, tb_Contacts.getTelPhone());
ptmt.setString(3, tb_Contacts.getAddress());
ptmt.setString(4, tb_Contacts.getMobilePhone());
ptmt.setString(5, tb_Contacts.geteMail());
ptmt.setString(6, tb_Contacts.getReMark());
ptmt.setString(7, tb_Contacts.getType());
ptmt.setString(8, tb_Contacts.getBelong());
ptmt.setInt(9, tb_Contacts.getContactsID());
ptmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取一个联系人
public TB_Contacts getOneUser(Integer contactsID){
try {
String sql="select * from Contacts where contactsID=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, contactsID);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
tb_Contacts=new TB_Contacts();
tb_Contacts.setName(rs.getString("Name"));
tb_Contacts.setTelPhone(rs.getString("telPhone"));
tb_Contacts.setAddress(rs.getString("address"));
tb_Contacts.setMobilePhone(rs.getString("mobilePhone"));
tb_Contacts.seteMail(rs.getString("eMail"));
tb_Contacts.setReMark(rs.getString("reMark"));
tb_Contacts.setType(rs.getString("type"));
tb_Contacts.setBelong(rs.getString("belong"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return tb_Contacts;
}
//获取所有联系人
public List<TB_Contacts>getAllUser(){
List<TB_Contacts> dataContacts=new ArrayList<>();
try {
String sql="select * from Contacts";
PreparedStatement ptmt = conn.prepareStatement(sql);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
tb_Contacts=new TB_Contacts();
tb_Contacts.setContactsID(rs.getInt("contactsID"));
tb_Contacts.setName(rs.getString("Name"));
tb_Contacts.setTelPhone(rs.getString("telPhone"));
tb_Contacts.setAddress(rs.getString("address"));
tb_Contacts.setMobilePhone(rs.getString("mobilePhone"));
tb_Contacts.seteMail(rs.getString("eMail"));
tb_Contacts.setType(rs.getString("type"));
tb_Contacts.setReMark(rs.getString("reMark"));
tb_Contacts.setBelong(rs.getString("belong"));
dataContacts.add(tb_Contacts);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dataContacts;
}
/**
* 根据登录用户查询联系人
* @throws SQLException */
public List<TB_Contacts>selectByUser(String belong){
List<TB_Contacts> dataContacts=new ArrayList<>();
try {
String sql="select * from Contacts where belong=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, belong);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
tb_Contacts=new TB_Contacts();
tb_Contacts.setContactsID(rs.getInt("contactsID"));
tb_Contacts.setName(rs.getString("Name"));
tb_Contacts.setTelPhone(rs.getString("telPhone"));
tb_Contacts.setAddress(rs.getString("address"));
tb_Contacts.setMobilePhone(rs.getString("mobilePhone"));
tb_Contacts.seteMail(rs.getString("eMail"));
tb_Contacts.setType(rs.getString("type"));
tb_Contacts.setReMark(rs.getString("reMark"));
tb_Contacts.setBelong(rs.getString("belong"));
dataContacts.add(tb_Contacts);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dataContacts;
}
/**
* id降序排序
* @throws SQLException */
public List<TB_Contacts> idSort() throws SQLException{
String sql="select * from Contacts order by contactsID desc";
PreparedStatement ptmt = conn.prepareStatement(sql);
ResultSet rs=ptmt.executeQuery();
List<TB_Contacts> dataContacts=new ArrayList<>();
while(rs.next()){
tb_Contacts=new TB_Contacts();
tb_Contacts.setContactsID(rs.getInt("contactsID"));
tb_Contacts.setName(rs.getString("Name"));
tb_Contacts.setTelPhone(rs.getString("telPhone"));
tb_Contacts.setAddress(rs.getString("address"));
tb_Contacts.setMobilePhone(rs.getString("mobilePhone"));
tb_Contacts.seteMail(rs.getString("eMail"));
tb_Contacts.setType(rs.getString("type"));
tb_Contacts.setReMark(rs.getString("reMark"));
tb_Contacts.setBelong(rs.getString("belong"));
dataContacts.add(tb_Contacts);
}
return dataContacts;
}
/**
* 模糊查询
* @throws SQLException */
public List<TB_Contacts> findByContext(String context) throws SQLException{
String sql="select * from Contacts where Name like '%"+context +"%' or type like '%"+context +"%'";
PreparedStatement ptmt = conn.prepareStatement(sql);
ResultSet rs=ptmt.executeQuery();
List<TB_Contacts> dataContacts=new ArrayList<>();
while(rs.next()){
tb_Contacts=new TB_Contacts();
tb_Contacts.setContactsID(rs.getInt("contactsID"));
tb_Contacts.setName(rs.getString("Name"));
tb_Contacts.setTelPhone(rs.getString("telPhone"));
tb_Contacts.setAddress(rs.getString("address"));
tb_Contacts.setMobilePhone(rs.getString("mobilePhone"));
tb_Contacts.seteMail(rs.getString("eMail"));
tb_Contacts.setType(rs.getString("type"));
tb_Contacts.setReMark(rs.getString("reMark"));
tb_Contacts.setBelong(rs.getString("belong"));
dataContacts.add(tb_Contacts);
}
return dataContacts;
}
}
第四步:控制层的实现,JSPservlet
package ContactServlet;
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 javax.servlet.http.HttpSession;
import CategoryBean.TB_Contacts;
import CategoryDao.ContactsDao;
/**
* Servlet implementation class mainCtr
*/
@WebServlet(name="main",urlPatterns={"/main"})
public class mainCtr extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public mainCtr() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//String loBelong=request.getParameter("louserName");
String operationType=request.getParameter("operation");
HttpSession session=request.getSession(true);
//新增联系人
if(operationType.equals("add")){
String loBelong2=session.getAttribute("LoginUser").toString();
String conName=request.getParameter("conName");
String telPhone=request.getParameter("telPhone");
String address=request.getParameter("address");
String mobilePhone=request.getParameter("mobilePhone");
String eMail=request.getParameter("eMail");
String reMark=request.getParameter("reMark");
String type=request.getParameter("type");
TB_Contacts tb_Contacts=new TB_Contacts(conName,telPhone,address,mobilePhone
,eMail,reMark,type,loBelong2);
ContactsDao contactsDao=new ContactsDao();
contactsDao.addUser(tb_Contacts);
response.sendRedirect("PAGE/Main.jsp");
}
//编辑联系人
if(operationType.equals("edit")){
request.setCharacterEncoding("utf-8");
String aa=session.getAttribute("editid").toString();
int id=Integer.parseInt(aa);
String eloBelong2=session.getAttribute("LoginUser").toString();
String econName=request.getParameter("conName");
String etelPhone=request.getParameter("telPhone");
String eaddress=request.getParameter("address");
String emobilePhone=request.getParameter("mobilePhone");
String eeMail=request.getParameter("eMail");
String ereMark=request.getParameter("reMark");
String etype=request.getParameter("type");
TB_Contacts tb_Contacts=new TB_Contacts(id,econName,etelPhone,eaddress,emobilePhone,eeMail,ereMark,etype,eloBelong2);
ContactsDao contactsDao=new ContactsDao();
contactsDao.updateUser(tb_Contacts);
}
//删除联系人
if(operationType.equals("delete")){
request.setCharacterEncoding("utf-8");
ContactsDao contactsDao=new ContactsDao();
int contactsID=Integer.parseInt(request.getParameter("deleteID"));
contactsDao.deleteUser(contactsID);
}
//获取所有联系人
if(operationType.equals("all")){
ContactsDao contactsDao=new ContactsDao();
List<TB_Contacts> contactData=new ArrayList<>();
contactData=contactsDao.getAllUser();
}
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest
request, HttpServletResponse response) throws ServletException, IOException
{// TODO Auto-generated method stubdoGet(request, response);}}
前台页面
<%@ 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>Insert title here</title>
</head>
<body>
<a href="../mainCtr?operation=add"id="add">添加联系人</a>
<a href="../mainCtr?operation=delete"id="delete">删除联系人</a>
<a href="../mainCtr?operation=edit"id="edit">修改联系人</a>
</body>
</html>