jsp连接mysql的增删改操作

create database addressdb;

use addressdb;

create table addressList(

　　id int primary key,

　　name varchar(20),

　　phone varchar(20),

　　address varchar(100),

　　email varchar(40)

);

 

 

 

 

 

 

//AddressBean.java

package beans;

public class AddressBean {
 private int id;
 private String name;
 private String phone;
 private String address;
 private String email;

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getPhone() {
  return phone;
 }

 public void setPhone(String phone) {
  this.phone = phone;
 }

 public String getAddress() {
  return address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }
}

 

 

//AddressDemo.java

package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class AddressDemo extends ConnBean {
 private Connection connection = null;

 public void closePstmt(PreparedStatement pstmt) {
  try {
   if (connection != null)
    connection.close();
   connection = null;
  } catch (SQLException e3) {
   e3.printStackTrace();
  }
 }

 public AddressBean[] getAllRecords() {
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  Collection list = new ArrayList();
  try {
   connection = getConnection();
   pstmt = connection.prepareStatement("select * from addresslist");
   rs = pstmt.executeQuery();
   while (rs.next()) {
    AddressBean address = new AddressBean();
    address.setId(rs.getInt(1));
    address.setName(rs.getString(2));
    address.setPhone(rs.getString(3));
    address.setAddress(rs.getString(4));
    address.setEmail(rs.getString(5));
    list.add(address);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closePstmt(pstmt);
   closeConnection(connection);
  }
  AddressBean[] records = (AddressBean[]) list
    .toArray(new AddressBean[0]);
  return records;
 }

 public boolean insertRecord(AddressBean record) {
  
  PreparedStatement pstmt = null;
  String insertStr = "insert into addressList values(?,?,?,?,?)";
  try {
   connection = getConnection();
   pstmt = connection.prepareStatement(insertStr);
   pstmt.setInt(1, record.getId());
   pstmt.setString(2, ChineseStr(record.getName()));
   pstmt.setString(3, ChineseStr(record.getPhone()));
   pstmt.setString(4, ChineseStr(record.getAddress()));
   pstmt.setString(5, ChineseStr(record.getEmail()));
   pstmt.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closePstmt(pstmt);
   closeConnection(connection);
  }
  return true;
 }
 public String ChineseStr(String str) {                   //过滤函数
  if (str == null) {
   str = "";
  } else {
   try {
    str = (new String(str.getBytes("iso-8859-1"), "utf-8")).trim();
   } catch (Exception e) {
    e.printStackTrace(System.err);
   }
  }
  return str;
 }

 public boolean deleteRecord(AddressBean record) {
  PreparedStatement pstmt = null;
  String deleteStr = "delete from addressList where id=?";
  if (record == null)
   return false;
  try {
   connection=getConnection();
   pstmt = connection.prepareStatement(deleteStr);
   pstmt.setInt(1, record.getId());
   pstmt.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closePstmt(pstmt);
   closeConnection(connection);
  }
  return true;
 }

 public boolean updateRecord(AddressBean ro, AddressBean rn) {
  if (ro == null || rn == null)
   return false;
  PreparedStatement pstmt = null;
  String updateStr = "update addressList set name=?,phone=?,address=?,email=?";
  updateStr = updateStr + "where id=" + ro.getId();
  try {
   connection = getConnection();
   pstmt = connection.prepareStatement(updateStr);
   pstmt.setString(1, rn.getName());
   pstmt.setString(2, rn.getPhone());
   pstmt.setString(3, rn.getAddress());
   pstmt.setString(4, rn.getEmail());
   pstmt.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closePstmt(pstmt);
   closeConnection(connection);
  }
  return true;
 }

}

 

 

//ConnBean.java

package beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnBean {
 private String driver = "com.mysql.jdbc.Driver";
 private String jdbcurl = "jdbc:mysql://localhost:3306/";
 private String database = "addressDB";
 private String userName = "root";
 private String password = "110";
 private Connection connection = null;

 public ConnBean() {
  getConnection();
 }

 public Connection getConnection() {
  try {
   Class.forName(driver);
   connection = DriverManager.getConnection(jdbcurl + database,
     userName, password);
  } catch (ClassNotFoundException e1) {
   e1.printStackTrace();
  } catch (SQLException e2) {
   e2.printStackTrace();
  }
  return connection;
 }

 public void closeConnection(Connection connection) {
  try {
   if (connection != null)
    connection.close();
   connection = null;
  } catch (SQLException e3) {
   e3.printStackTrace();
  }
 }

 public String getDriver() {
  return driver;
 }

 public void setDriver(String driver) {
  this.driver = driver;
 }

 public String getJdbcurl() {
  return jdbcurl;
 }

 public void setJdbcurl(String jdbcurl) {
  this.jdbcurl = jdbcurl;
 }

 public String getDatabase() {
  return database;
 }

 public void setDatabase(String database) {
  this.database = database;
 }

 public String getUserName() {
  return userName;
 }

 public void setUserName(String userName) {
  this.userName = userName;
 }

 public String getPassword() {
  return password;
 }

 public void setPassword(String password) {
  this.password = password;
 }

}

 

 

// AddressLook.jsp查询

<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@ page import="beans.*"%>
<%@ page import="java.sql.*"%>
<!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>
<!-- <script language="javascript">
 function add() {
  document.frm1.action="Index.jsp"
   document.frm1.submit()

 }
</script> -->
<body>
 <jsp:useBean id="conn" scope="session" class="beans.AddressDemo" />
 <center>
  <table border="1">
   <caption>通讯录</caption>
   <tr>
    <td>编号</td>
    <td>姓名</td>
    <td>电话</td>
    <td>地址</td>
    <td>E-mail</td>
   </tr>
   <%
    request.setCharacterEncoding("UTF-8");
    AddressBean[] records = conn.getAllRecords();
    if (records != null) {
     for (int i = 0; i < records.length; i++) {
      out.println("<tr>");
      out.println("<td>" + records[i].getId() + "</td>");
      out.println("<td>" + records[i].getName() + "</td>");
      out.println("<td>" + records[i].getPhone() + "</td>");
      out.println("<td>" + records[i].getAddress() + "</td>");
      out.println("<td>" + records[i].getEmail() + "</td>"
        + "</tr>");
     }
    }
   %>

  </table>
  <hr>
  <hr>
  <hr>
  <input type="button" value="添加"
   onclick="javascript:location.href='AddressInsert.jsp'" /> <input
   type="button" value="删除"
   onclick="javascript:location.href='AddressDelete.jsp'" />
 </center>
</body>
</html>

 

 

//AddressInsert.jsp  添加

<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@ page import="beans.AddressBean,beans.ConnBean"%>
<!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>

 <table>
  <form action="" method="post">
   <center>添加记录</center>
  <tr>
   <td>编号</td>
   <td><input type="text" name="id" colspan="2" /></td>
  </tr>
  <tr>
   <td>姓名</td>
   <td><input type="text" name="name" colspan="2" /></td>
  </tr>
  <tr>
   <td>电话</td>
   <td><input type="text" name="phone" colspan="2" /></td>
  </tr>
  <tr>
   <td>地址</td>
   <td><input type="text" name="address" colspan="2"></td>
  </tr>
  <tr>
   <td>E-mail</td>
   <td><textarea name="email" cols="20" rows="5" colspan="2"></textarea></td>
  </tr>
  <tr>
   <td><input type="submit" value="添加"></td>
   <td><input type="reset" value="重置"></td>
   <td><input type="button" value="查看"
    onclick="javascript:location.href='AddressLook.jsp'" /></td>
  </tr>
  </form>
 </table>
 <jsp:useBean id="add" scope="session" class="beans.AddressBean"></jsp:useBean>
 <jsp:setProperty property="*" name="add" />
 <jsp:useBean id="conn" scope="session" class="beans.AddressDemo"></jsp:useBean>
 <%
  if (add.getId() != 0 && conn.insertRecord(add))
   out.println("添加成功！");
 %>
</body>
</html>

 

 

//AddressDelete.jsp 删除

<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@ page import="beans.*"%>
<!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>
 <p>
  <jsp:useBean id="conn" scope="session" class="beans.AddressDemo" />
 <form action="" method="post">
  <table border="1">
   <caption>通讯录</caption>
   <tr>
    <th>编号</th>
    <th>姓名</th>
    <th>电话</th>
    <th>地址</th>
    <th>E-mail</th>
    <th>选择</th>
   </tr>
   <%
    AddressBean[] records = conn.getAllRecords();
    if (records != null) {
     for (int i = 0; i < records.length; i++) {
      out.println("<tr>" + "<td>" + records[i].getId() + "</td>");
      out.println("<td>" + records[i].getName() + "</td>");
      out.println("<td>" + records[i].getPhone() + "</td>");
      out.println("<td>" + records[i].getAddress() + "</td>");
      out.println("<td>" + records[i].getEmail() + "</td>");
   %>
   <td><input type="checkbox" name="<%="check" + i%>"
    value="<%=i%>"></td>
   <%
    out.println("</tr>");
     }
    }
   %>
  </table>
  <input type="submit" value="提交" />
 </form>
 <%
  String[] check = new String[records.length];
  for (int i = 0; i < check.length; i++) {
   check[i] = request.getParameter("check" + i);
   if (check[i] == null)
    check[i] = "";
   if (check[i].equals("" + i)) {
    if (conn.deleteRecord(records[i])) {
     out.println("<hr>删除成功！");
     response.setHeader("refresh", "1");
    }
   }
  }
 %>
 </p>
</body>
</html>

注意：两种乱码。一种为网数据库写入的信息为乱码，可以采用“过滤函数”。另一种为接收数据显示乱码，可以用代码转换“request.setCharacterEncoding("UTF-8");”。