上次我们已经搞完了jsp的操作。现在该是后台的配置了。
在dao包里面进行数据链接：DBConn.java
/**
*
*/
/**
* @author Administrator
*
*/
package dao;
import java.sql.*;
public class DBConn {
/**
* 链接数据库
* @return
*/
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DataBaseName=EstateDB","sa","123456");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
BuildingDao.java
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import entity.Building;
public class BuildingDao {
/**
* 操作数据库命令链接
* 数据访问类
*/
private Connection conn;
private Statement state;
private ResultSet rs;
private PreparedStatement pre;
/**
* 查询全部
* @return
* @throws SQLException
*/
public List<Building> fill() throws SQLException {
List<Building> list = new ArrayList<Building>();
String sql = "select * from T_building";
conn = DBConn.getConnection();
state = conn.createStatement();
rs = state.executeQuery(sql);
Building p = null;
while (rs.next()) {
p = new Building();
p.setId(rs.getString("Id"));
p.setCompany(rs.getString("Company"));
p.setPhone(rs.getString("Phone"));
p.setDescription(rs.getString("Description"));
p.setStatus(rs.getString("Status"));
list.add(p);
}
rs.close();
state.close();
conn.close();
return list;
}
/**
* 根据Id查询
* @param Id
* @return
* @throws SQLException
*/
public Building fill(String Id) throws SQLException{
conn = DBConn.getConnection();
String sql="select * from T_building where Id=?";
pre = conn.prepareStatement(sql);
pre.setString(1, Id);
rs=pre.executeQuery();
Building p = null;
if(rs.next()){
p = new Building();
p.setId(rs.getString("Id"));
p.setCompany(rs.getString("Company"));
p.setPhone(rs.getString("Phone"));
p.setDescription(rs.getString("Description"));
p.setStatus(rs.getString("Status"));
}
rs.close();
pre.close();
conn.close();
return p;
}
/**
* 添加
* @param building
* @return
* @throws SQLException
*/
public int add(Building building) throws SQLException {
String sql = "insert T_building values ('" + building.getId() + "','"
+ building.getCompany() + "','" + building.getPhone() + "','"
+ building.getDescription() + "','" + building.getStatus()
+ "')";
System.out.println(sql);
conn = DBConn.getConnection();
state = conn.createStatement();
int result = state.executeUpdate(sql);
state.close();
conn.close();
return result;
}
/**
* 修改
* @param building
* @return
* @throws SQLException
*/
public int update(Building building) throws SQLException {
String sql="UPDATE T_building SET Company=?,Phone =?,"+"Description=?, Status=? WHERE Id=?";
conn=DBConn.getConnection();
pre = conn.prepareStatement(sql);
pre.setString(1, building.getCompany());
pre.setString(2, building.getPhone());
pre.setString(3, building.getDescription());
pre.setString(4, building.getStatus());
pre.setString(5, building.getId());
int count=pre.executeUpdate();
pre.close();
conn.close();
return count;
// TODO Auto-generated method stub
}
/**
* 根据ID删除一项
* @param Id
* @throws SQLException
*/
public void delete(String Id) throws SQLException {
String sql="delete from T_building where Id=?";
conn=DBConn.getConnection();
pre = conn.prepareStatement(sql);
pre.setString(1,Id);
pre.executeUpdate();
pre.close();
conn.close();
// TODO Auto-generated method stub
}
/**
* 多项选择Id删除
* @param Id
* @throws SQLException
*/
public void delete(String[] Id) throws SQLException {
conn = DBConn.getConnection();
String ids="'"+Id[0]+"'";
for(int i=1;i<Id.length;i++) {
ids=ids+",'"+Id[i]+"'";
}
String sql="delete from T_building where Id in ("+ids+")";
pre = conn.prepareStatement(sql);
pre.executeUpdate();
pre.close();
conn.close();
// TODO Auto-generated method stub
}
}
对啦，忘记创建实体类了。在entity包里面建实体类
Building.java
/**
*
*/
/**
* @author Administrator
*
*/
package entity;
public class Building {
/**
* 实体类
* 定义get ，set 属性
*/
private String Id;
private String Company;
private String Phone;
private String Description;
private String Status;
public String getId() {
return Id;
}
public void setId(String id) {
Id = id;
}
public String getCompany() {
return Company;
}
public void setCompany(String company) {
Company = company;
}
public String getPhone() {
return Phone;
}
public void setPhone(String phone) {
Phone = phone;
}
public String getDescription() {
return Description;
}
public void setDescription(String description) {
Description = description;
}
public String getStatus() {
return Status;
}
public void setStatus(String status) {
Status = status;
}
}
service服务
BuildingService.java
/**
*
*/
/**
* @author Administrator
*
*/
package service;
import java.sql.SQLException;
import java.util.List;
import dao.BuildingDao;
import entity.Building;
public class BuildingService{
/**
* 添加
* @param building
* @return
* @throws SQLException
*/
public int add(Building building) throws SQLException {
BuildingDao dao=new BuildingDao();
return dao.add(building);
}
/**
* 查询
* @return
* @throws SQLException
*/
public List<Building> fill() throws SQLException{
BuildingDao dao=new BuildingDao();
return dao.fill();
}
public Building fill(String Id) throws SQLException{
BuildingDao dao=new BuildingDao();
return dao.fill(Id);
}
/**
* 修改
* @param building
* @return
* @throws SQLException
*/
public int update(Building building) throws SQLException{
BuildingDao dao=new BuildingDao();
return dao.update(building);
}
/**
* 删除
* @param Id
* @throws SQLException
*/
public void delete(String Id) throws SQLException{
BuildingDao dao=new BuildingDao();
dao.delete(Id);;
}
public void delete(String[] Id) throws SQLException {
BuildingDao dao=new BuildingDao();
dao.delete(Id);
}
}
在action包里建servlet
BuildingServlet.java
/**
*
*/
/**
* @author Administrator
*
*/
package action;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.JApplet;
import service.BuildingService;
import entity.Building;
public class BuildingServlet extends javax.servlet.http.HttpServlet implements
javax.servlet.Servlet {
static final long serialVersionUID = 1L;
public BuildingServlet() {
super();
}
@Override
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setCharacterEncoding("utf-8");
try {
start(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
try {
start(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void start(HttpServletRequest request, HttpServletResponse response)
throws Exception {
response.setCharacterEncoding("GBK");
response.setContentType("text/html;charset=utf-8");
BuildingService service = new BuildingService();
String action = request.getParameter("action");
String id = request.getParameter("id");
/**
* 添加
*/
if (action.equals("add")) {
response.setContentType("text/html;charset=utf-8");
String Id = request.getParameter("Id");
String Company = request.getParameter("Company");
String Phone = request.getParameter("Phone");
String Description = request.getParameter("Description");
String Status = request.getParameter("Status");
Building b = new Building();
b.setId(Id);
b.setCompany(Company);
b.setPhone(Phone);
b.setDescription(Description);
b.setStatus(Status);
BuildingService buildingService = new BuildingService();
try {
buildingService.add(b);
PrintWriter out = response.getWriter();
out.print("添加成功");
} catch (SQLException e) {
PrintWriter out = response.getWriter();
out.print("添加失败");
e.printStackTrace();
}
}
/**
* 查詢
*/
else if (action.equals("list")) {
try {
List<Building> buildingList = service.fill();
request.setAttribute("buildingList", buildingList);
request.getRequestDispatcher("buildingList.jsp").forward(
request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else if (action.equals("list2")) {
String id1 = request.getParameter("id");
try {
Building building = service.fill(id1);
request.setAttribute("building", building);
request.getRequestDispatcher("buildingList.jsp").forward(
request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 修改
*/
else if (id != null&&action.equals("update")) {
try {
Building building = service.fill(id);
request.setAttribute("building", building);
request.getRequestDispatcher("buildingUpdate.jsp").forward(
request, response);
} catch (Exception e) {
e.printStackTrace();
}
} else if(action.equals("update2")){
String Id = request.getParameter("Id");
String Company = request.getParameter("Company");
String Phone = request.getParameter("Phone");
String Description = request.getParameter("Description");
String Status = request.getParameter("Status");
Building b = new Building();
b.setId(Id);
b.setCompany(Company);
b.setPhone(Phone);
b.setDescription(Description);
b.setStatus(Status);
BuildingService buildingService = new BuildingService();
try {
buildingService.update(b);
PrintWriter out = response.getWriter();
out.print("修改成功");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 删除
*/
if(action.equals("delete")) {
try {
List<Building> buildingDelete = service.fill();
request.setAttribute("buildingDelete", buildingDelete);
request.getRequestDispatcher("buildingDelete.jsp").forward(
request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
else if(action.equals("delete2")) {
String[] ids=request.getParameterValues("Id");
// String id1=request.getParameter("id");
try {
//service.delete(id1);
service.delete(ids);
response.sendRedirect("BuildingServlet?action=delete");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
else if(action.equals("delete3")) {
String id1=request.getParameter("id");
try {
service.delete(id1);
response.sendRedirect("BuildingServlet?action=delete");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
