>前期需要:
1. 一个JavaBean页面对信息进行存储
此处取名为JDBC_Register
package JavaBean;
import java.io.Serializable;
public class JDBC_Register implements Serializable{
private static final long serialVersionUID = 1L;
private String name;
private String username;
private String password1;
private String phone;
private String mailbox;
private String where;
private String usertype;
public JDBC_Register() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword1() {
return password1;
}
public void setPassword1(String password1) {
this.password1 = password1;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getMailbox() {
return mailbox;
}
public void setMailbox(String mailbox) {
this.mailbox = mailbox;
}
public String getWhere() {
return where;
}
public void setWhere(String where) {
this.where = where;
}
public String getUsertype() {
return usertype;
}
public void setUsertype(String usertype) {
this.usertype = usertype;
}
}
- 一个显示页面information.jsp
-
以下显示主要代码< -
<table border=3 class="two">
<tr bgcolor="#CCCCFF" style="color:whitesmoke">
<th> 姓 名 </th>
<th> 学 号 </th>
<th> 密 码 </th>
<th> 手 机 </th>
<th> 邮 箱 </th>
<th> 住 址 </th>
<th> 类 型 </th>
<!-- <th> 操 作
</th> -->
</tr>
<%
List<JDBC_Register> list = (List<JDBC_Register>)request.getAttribute("list");
if (list == null||list.size()<1){
out.print("暂时没有学生注册信息");
}else
{
for(JDBC_Register people:list){
%>
<tr>
<td><%= people.getName() %></td>
<td><%= people.getUsername() %></td>
<td><%= people.getPassword1() %></td>
<td><%= people.getPhone()%></td>
<td><%= people.getMailbox()%></td>
<td><%= people.getWhere()%></td>
<td><%= people.getUsertype()%></td>
</tr>
<%
}
}
%>
</table>
- 显示全部数据库内容
-
以下显示主要代码< -
@WebServlet("/JDBC_select")
public class JDBC_select extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url, Username, Password);
Statement Sta = Conn.createStatement();
String sql = "SELECT * FROM tb_useraccount";
ResultSet RS = Sta.executeQuery(sql);
List<JDBC_Register> list = new ArrayList<JDBC_Register>();
while (RS.next()) {
/* 建立Serverlt JDBC_Register类 */
JDBC_Register people = new JDBC_Register();
/* 从数据库拉取的数据需要URLDecoder解码 */
people.setName(URLDecoder.decode(RS.getString("name"),"UTF-8");
people.setUsername(URLDecoder.decode(RS.getString("username"),"UTF-8")); people.setPassword1(URLDecoder.decode(RS.getString("password"),"UTF-8"));
people.setPhone(URLDecoder.decode(RS.getString("phone"),"UTF-8"));
people.setMailbox(URLDecoder.decode(RS.getString("mailbox"),"UTF-8"));
people.setWhere(URLDecoder.decode(RS.getString("where"),"UTF-8"));
people.setUsertype(URLDecoder.decode(RS.getString("usertype"),"UTF-8"));
list.add(people);
System.out.print(list);
out.print(people);
}
request.setAttribute("list", list);
/* 关闭通道 */
RS.close();
Sta.close();
Conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
- 增加一条数据
-
以下显示主要代码< -
@WebServlet("/JDBC_add")
public class JDBC_add extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
String username=request.getParameter("username");
String password=request.getParameter("password");
String phone=request.getParameter("phone");
String mailbox=request.getParameter("mailbox");
String where=request.getParameter("where");
String usertype = request.getParameter("usertype");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql_insert = "INSERT INTO tb_useraccount VALUES(?,?,?,?,?,?,?)";
PreparedStatement PreSta = Conn.prepareStatement(sql_insert);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
int row = PreSta.executeUpdate();
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
}
response.sendRedirect("information.jsp?name="+URLEncoder.encode(name,"UTF-8")+"&username="+username);
}
}
- 删除一条数据
-
以下显示主要代码< -
@WebServlet("/JDBC_delete")
public class JDBC_delete extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql ="DELETE FROM tb_useraccount WHERE name= ?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
int row = PreSta.executeUpdate();
out.println("成功删除"+row+"条关于-> "+usertype+" "+name+"的数据!");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
}
response.sendRedirect("information.jsp?name="+URLEncoder.encode(name,"UTF-8")+"&username="+username);
}
}
- 查找一条数据并返回显示
-
以下显示主要代码< -
@WebServlet("/JDBC_search")
public class JDBC_search extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
String username=request.getParameter("username");
String password=request.getParameter("password");
String phone=request.getParameter("phone");
String mailbox=request.getParameter("mailbox");
String where=request.getParameter("where");
String usertype = request.getParameter("usertype");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql ="SELECT * FROM tb_useraccount WHERE name= ? OR username= ? OR password= ? OR phone= ? OR mailbox= ? OR `where` = ? OR usertype= ?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
//需要返回结果需要executeQuery
ResultSet res = PreSta.executeQuery();
List<JDBC_Register> list = new ArrayList<JDBC_Register>();
while (res.next()) {
JDBC_Register people = new JDBC_Register();
people.setName(URLDecoder.decode(res.getString("name"),"UTF-8"));
people.setUsername(URLDecoder.decode(res.getString("username"),"UTF-8"));
people.setPassword1(URLDecoder.decode(res.getString("password"),"UTF-8"));
people.setPhone(URLDecoder.decode(res.getString("phone"),"UTF-8"));
people.setMailbox(URLDecoder.decode(res.getString("mailbox"),"UTF-8"));
people.setWhere(URLDecoder.decode(res.getString("where"),"UTF-8"));
people.setUsertype(URLDecoder.decode(res.getString("usertype"),"UTF-8"));
list.add(people);
System.out.print(list);
out.print(people);
}
request.setAttribute("list", list);
res.close();
PreSta.close();
Conn.close();
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
- 修改一条数据
-
以下显示主要代码< -
@WebServlet("/JDBC_change")
public class JDBC_change extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql = "UPDATE tb_useraccount SET name=? WHERE username=?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
int row = PreSta.executeUpdate();
System.out.println("成功修改"+row+"条关于-> "+usertype+" "+name+"的数据!");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
需要使用 ?进行sql参数嵌入时
写法:
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql = "INSERT INTO tb_useraccount VALUES(?,?,?,?,?,?,?)";
//需要加入参数就需要Conn.perpareStatement(sql)
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
//此时不需要带入参数()
int row = PreSta.executeUpdate();
不需要进行sql参数时
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url, Username, Password);
//直接创建Conn.createStatement()
Statement Sta = Conn.createStatement();
String sql = "SELECT * FROM tb_useraccount";
//此时需要带上参数(sql)
ResultSet RS = Sta.executeQuery(sql);