deme的结构图,采用的是三层架构,降低耦合度。
public class DBHelper {
//属性,必须为私有变量
private Connection conn;
private Statement state;
private PreparedStatement prep;
//构造方法
public DBHelper(){
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//方法
//增删改
public int update(String sql) throws SQLException{
state=conn.createStatement();
int temp=prep.executeUpdate(sql); //增删改最后要返回一个整形变量
return temp;
}
//方法重载,当所变化的变量个数不确定的时候,就要用到表达式
public int update(String sql,Object ... arr) throws SQLException{
prep=conn.prepareStatement(sql);
//给问号赋值
for(int i=0;i<arr.length;i++){
prep.setObject(i+1, arr[i]);
}
return prep.executeUpdate();
}
//查询
public ResultSet select(String sql) throws SQLException{
state=conn.createStatement();
ResultSet rs=state.executeQuery(sql);
return rs;
}
public ResultSet select(String sql,Object ... arr) throws SQLException{
prep=conn.prepareStatement(sql);
for(int i=0;i<arr.length;i++){
prep.setObject(i+1, arr[i]); //提取数据,索引从零开始,所以会i+1;
}
return prep.executeQuery();
}
//关闭
public void closeAll() throws SQLException{
if(state!=null && !state.isClosed()) state.close();
if(prep!=null && !prep.isClosed()) prep.close();
if(conn!=null && !conn.isClosed()) conn.close();
}
}
//完成一个插入操作
public class Demo4 {
public static void main(String[] args) {
String username="xi";
String userpass="fei";
DBHelp hp=new DBHelp();
String sql="insert into users values(?,?)";
try {
int temp=hp.update(sql,username,userpass);
System.out.println(temp);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注意:ResultSet 中的next() 将指针移动到下一条记录,不允许有空指针
public static void main(String[] args) {
System.out.println(getConnection());
}
测试一下,是否连接成功
连接成功
接下来创建实体类Employee
- import java.io.Serializable;
- public class Employee implements Serializable{
- /**
- *
- */
- private static final long serialVersionUID = 1L;
- // ps:写属性推荐一个冷门的快捷键 shift+alt+a 谁用谁知道
- private int employeeId;
- private String employeeName;
- private String employeeSex;
- private String employeePhone;
- private String employeeEmail;
- // shift+alt+s 选择Generate Getters and setters to create 生成get set方法
- public int getEmployeeId() {
- return employeeId;
- }
- public void setEmployeeId(int employeeId) {
- this.employeeId = employeeId;
- }
- public String getEmployeeName() {
- return employeeName;
- }
- public void setEmployeeName(String employeeName) {
- this.employeeName = employeeName;
- }
- public String getEmployeeSex() {
- return employeeSex;
- }
- public void setEmployeeSex(String employeeSex) {
- this.employeeSex = employeeSex;
- }
- public String getEmployeePhone() {
- return employeePhone;
- }
- public void setEmployeePhone(String employeePhone) {
- this.employeePhone = employeePhone;
- }
- public String getEmployeeEmail() {
- return employeeEmail;
- }
- public void setEmployeeEmail(String employeeEmail) {
- this.employeeEmail = employeeEmail;
- }
- }
查询列表
DAO里写方法
- public List<Employee> getAllEmployee(){
- List<Employee> list = new ArrayList<Employee>();
- Connection conn = DBHelper.getConnection();
- String sql = "select * from Employee";
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- ResultSet rst = pst.executeQuery();
- while (rst.next()) {
- Employee employee = new Employee();
- employee.setEmployeeEmail(rst.getString("EmployeeEmail"));
- employee.setEmployeeId(rst.getInt("EmployeeId"));
- employee.setEmployeeName(rst.getString("EmployeeName"));
- employee.setEmployeePhone(rst.getString("EmployeePhone"));
- employee.setEmployeeSex(rst.getString("EmployeeSex"));
- list.add(employee);
- }
- rst.close();
- pst.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return list;
- }
自动创建一个servlet类,或者继承HttpServlet类。
- public class EmployeeShowServlet extends HttpServlet{
- private static final long serialVersionUID = 1L;
- @Override
- protected void doGet(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- this.doPost(req, resp);
- }
- @Override
- protected void doPost(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- EmployeeDao dao = new EmployeeDao();
- List<Employee> list = dao.getAllEmployee();
- req.setAttribute("list", list);
- req.getRequestDispatcher("index.jsp").forward(req, resp);
- }
- }
最后在xml文件里面配置写好的servlet
- <servlet>
- <servlet-name>employeeShowServlet</servlet-name>
- <servlet-class>com.xgyb.servlet.EmployeeShowServlet</servlet-class>
- </servlet>
- <servlet-mapping>
- <servlet-name>employeeShowServlet</servlet-name>
- <url-pattern>/employeeShowServlet</url-pattern>
- </servlet-mapping>
jsp页面
- <table>
- <tr>
- <td>编号</td>
- <td>姓名</td>
- <td>性别</td>
- <td>电话</td>
- <td>邮箱</td>
- <td>操作</td>
- </tr>
- <c:forEach items="${list}" var="item">
- <tr>
- <td>${item.employeeId }</td>
- <td>${item.employeeName }</td>
- <td>${item.employeeSex }</td>
- <td>${item.employeePhone }</td>
- <td>${item.employeeEmail }</td>
- </tr>
- </c:forEach>
- </table>
实现添加
dao层
- public boolean addEmployee(Employee employee){
- String sql = "INSERT INTO `employee`(`EmployeeName`,`EmployeeSex`,`EmployeePhone`,`EmployeeEmail`) VALUES (?,?,?,?)";
- Connection conn = DBHelper.getConnection();
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- pst.setString(1, employee.getEmployeeName());
- pst.setString(2, employee.getEmployeeSex());
- pst.setString(3, employee.getEmployeePhone());
- pst.setString(4, employee.getEmployeeEmail());
- int count = pst.executeUpdate();
- pst.close();
- return count>0?true:false;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
创建EmployeeAddServlet
- public class EmployeeAddServlet extends HttpServlet{
- private static final long serialVersionUID = 1L;
- @Override
- protected void doGet(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- this.doPost(req, resp);
- }
- @Override
- protected void doPost(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String employeeName = req.getParameter("employeeName");
- String employeeSex = req.getParameter("employeeSex");
- String employeePhone = req.getParameter("employeePhone");
- String employeeEmail = req.getParameter("employeeEmail");
- Employee employee = new Employee();
- employee.setEmployeeEmail(employeeEmail);
- employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));
- employee.setEmployeePhone(employeePhone);
- employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));
- EmployeeDao dao = new EmployeeDao();
- dao.addEmployee(employee);
- req.getRequestDispatcher("employeeShowServlet").forward(req, resp);
- }
- }
xml配置
- <servlet>
- <servlet-name>employeeAddServlet</servlet-name>
- <servlet-class>com.xgyb.servlet.EmployeeAddServlet</servlet-class>
- </servlet>
- <servlet-mapping>
- <servlet-name>employeeAddServlet</servlet-name>
- <url-pattern>/employeeAddServlet</url-pattern>
- </servlet-mapping>
jsp
- <form action="employeeAddServlet" method="post">
- <table border="1">
- <tr>
- <td colspan="2"><h1>添加员工</h1></td>
- </tr>
- <tr>
- <td>姓名:</td>
- <td><input type="text" name="employeeName"/></td>
- </tr>
- <tr>
- <td>性别:</td>
- <td><input type="text" name="employeeSex"/></td>
- </tr>
- <tr>
- <td>电话:</td>
- <td><input type="text" name="employeePhone"/></td>
- </tr>
- <tr>
- <td>邮箱:</td>
- <td><input type="text" name="employeeEmail"/></td>
- </tr>
- <tr>
- <td colspan="2"><input type="submit" value="提交"/></td>
- </tr>
- </table>
- </form>
添加成功
Dao 修改有两个,根据id选中需要修改的内容
- public boolean updateEmployee(Employee employee){
- String sql = "UPDATE `employee` SET `EmployeeName`=?,`EmployeeSex`=?,`EmployeePhone`=?,`EmployeeEmail`=? WHERE `EmployeeId` = ?";
- Connection conn = DBHelper.getConnection();
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- pst.setString(1, employee.getEmployeeName());
- pst.setString(2, employee.getEmployeeSex());
- pst.setString(3, employee.getEmployeePhone());
- pst.setString(4, employee.getEmployeeEmail());
- pst.setInt(5, employee.getEmployeeId());
- int count = pst.executeUpdate();
- pst.close();
- return count>0?true:false;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- public Employee selectEmployeeById(int id){
- Connection conn = DBHelper.getConnection();
- String sql = "select * from Employee where EmployeeId = "+id;
- Employee employee = null;
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- ResultSet rst = pst.executeQuery();
- while (rst.next()) {
- employee = new Employee();
- employee.setEmployeeEmail(rst.getString("EmployeeEmail"));
- employee.setEmployeeId(rst.getInt("EmployeeId"));
- employee.setEmployeeName(rst.getString("EmployeeName"));
- employee.setEmployeePhone(rst.getString("EmployeePhone"));
- employee.setEmployeeSex(rst.getString("EmployeeSex"));
- }
- rst.close();
- pst.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return employee;
- }
Servlet
- public class EmployeeupdateServlet extends HttpServlet{
- private static final long serialVersionUID = 1L;
- @Override
- protected void doGet(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String idStr = req.getParameter("id");
- if(idStr != null && !idStr.equals("")){
- int id = Integer.valueOf(idStr);
- EmployeeDao dao = new EmployeeDao();
- Employee employee = dao.selectEmployeeById(id);
- req.setAttribute("employee", employee);
- }
- req.getRequestDispatcher("updateEmployee.jsp").forward(req, resp);
- }
- @Override
- protected void doPost(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String employeeName = req.getParameter("employeeName");
- String employeeSex = req.getParameter("employeeSex");
- String employeePhone = req.getParameter("employeePhone");
- String employeeEmail = req.getParameter("employeeEmail");
- String idStr = req.getParameter("employeeId");
- Employee employee = new Employee();
- employee.setEmployeeId(Integer.valueOf(idStr));
- employee.setEmployeeEmail(employeeEmail);
- employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));
- employee.setEmployeePhone(employeePhone);
- employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));
- EmployeeDao dao = new EmployeeDao();
- dao.updateEmployee(employee);
- req.getRequestDispatcher("employeeShowServlet").forward(req, resp);
- }
- }
跟前篇一样需要在xml 配置一下,这个很简单自己试着写。
修改用的页面就是增加的页面。
至尊宝 改成了孙悟空。
Dao 修改有两个,根据id选中需要修改的内容
- public boolean updateEmployee(Employee employee){
- String sql = "UPDATE `employee` SET `EmployeeName`=?,`EmployeeSex`=?,`EmployeePhone`=?,`EmployeeEmail`=? WHERE `EmployeeId` = ?";
- Connection conn = DBHelper.getConnection();
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- pst.setString(1, employee.getEmployeeName());
- pst.setString(2, employee.getEmployeeSex());
- pst.setString(3, employee.getEmployeePhone());
- pst.setString(4, employee.getEmployeeEmail());
- pst.setInt(5, employee.getEmployeeId());
- int count = pst.executeUpdate();
- pst.close();
- return count>0?true:false;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- public Employee selectEmployeeById(int id){
- Connection conn = DBHelper.getConnection();
- String sql = "select * from Employee where EmployeeId = "+id;
- Employee employee = null;
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- ResultSet rst = pst.executeQuery();
- while (rst.next()) {
- employee = new Employee();
- employee.setEmployeeEmail(rst.getString("EmployeeEmail"));
- employee.setEmployeeId(rst.getInt("EmployeeId"));
- employee.setEmployeeName(rst.getString("EmployeeName"));
- employee.setEmployeePhone(rst.getString("EmployeePhone"));
- employee.setEmployeeSex(rst.getString("EmployeeSex"));
- }
- rst.close();
- pst.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return employee;
- }
Servlet
- public class EmployeeupdateServlet extends HttpServlet{
- private static final long serialVersionUID = 1L;
- @Override
- protected void doGet(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String idStr = req.getParameter("id");
- if(idStr != null && !idStr.equals("")){
- int id = Integer.valueOf(idStr);
- EmployeeDao dao = new EmployeeDao();
- Employee employee = dao.selectEmployeeById(id);
- req.setAttribute("employee", employee);
- }
- req.getRequestDispatcher("updateEmployee.jsp").forward(req, resp);
- }
- @Override
- protected void doPost(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String employeeName = req.getParameter("employeeName");
- String employeeSex = req.getParameter("employeeSex");
- String employeePhone = req.getParameter("employeePhone");
- String employeeEmail = req.getParameter("employeeEmail");
- String idStr = req.getParameter("employeeId");
- Employee employee = new Employee();
- employee.setEmployeeId(Integer.valueOf(idStr));
- employee.setEmployeeEmail(employeeEmail);
- employee.setEmployeeName(new String(employeeName.getBytes("ISO-8859-1"),"UTF-8"));
- employee.setEmployeePhone(employeePhone);
- employee.setEmployeeSex(new String(employeeSex.getBytes("ISO-8859-1"),"UTF-8"));
- EmployeeDao dao = new EmployeeDao();
- dao.updateEmployee(employee);
- req.getRequestDispatcher("employeeShowServlet").forward(req, resp);
- }
- }
跟前篇一样需要在xml 配置一下,这个很简单自己试着写。
修改用的页面就是增加的页面。
至尊宝 改成了孙悟空。
删除就非常简单了,根据id来删除人物。
- public boolean deleteEmployee(int id){
- String sql = "delete from Employee where EmployeeId = ?";
- Connection conn = DBHelper.getConnection();
- try {
- PreparedStatement pst = conn.prepareStatement(sql);
- pst.setInt(1, id);
- int count = pst.executeUpdate();
- pst.close();
- return count>0?true:false;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- public class EmployeeDeleteServlet extends HttpServlet{
- private static final long serialVersionUID = 1L;
- @Override
- protected void doGet(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- this.doPost(req, resp);
- }
- @Override
- protected void doPost(HttpServletRequest req, HttpServletResponse resp)
- throws ServletException, IOException {
- String idStr = req.getParameter("id");
- if(idStr != null && !idStr.equals("")){
- int id = Integer.valueOf(idStr);
- EmployeeDao dao = new EmployeeDao();
- dao.deleteEmployee(id);
- }
- req.getRequestDispatcher("employeeShowServlet").forward(req, resp);
- }
- }
牛魔王被删掉了,是不是很简单。
http://download.csdn.net/download/alexander_yun/10129982
项目的代码,建议手动敲完