脚本方式实现数据库增删改查

哇塞，一个空格的问题花了一整天的时间终于找出了错~~~

1.首先实现查询表中的所有数据：

<%@page import="java.sql.*"%>
<%@ 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>查看数据库表中信息</title>
</head>
<body>
<%
String dbDriver = "com.mysql.jdbc.Driver";
String dbUrl = "jdbc:mysql://localhost:3306/book";
String dbUser = "root";
String dbPwd = "123456";
Class.forName(dbDriver);
Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
String sql = "select * from admin order by id";
Statement stmt = conn.createStatement();//创建Statement对象stmt
ResultSet rs = stmt.executeQuery(sql);//调用executeQuery方法进行数据库查询，结果放在rs中
%>
<table border="1">
<%while(rs.next()){ %>//判断结果集是否为空，如果不为空，进行循环画表
<tr>
<td><%= rs.getInt("id") %></td>
<td><%= rs.getString("username") %></td>
<td><%= rs.getString("password") %></td>
<td><a href="do_delete.jsp?id=<%= rs.getInt("id")%>">删除</a></td>
<td><a href="do_edit.jsp?id=<%= rs.getInt("id")%>">编辑</a></td>
</tr>
<%} %>
</table>
<a href="addAdmin.jsp">增加用户</a>
</body>
</html>

2.实现删除功能：

 1 <%@page import="java.sql.*"%>
 2 <%@ page language="java" contentType="text/html; charset=UTF-8"
 3     pageEncoding="UTF-8"%>
 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 5 <html>
 6 <head>
 7 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 8 <title>删除</title>
 9 </head>
10 <body>
11 <%
12 String idStr = request.getParameter( "id" );//通过request内置对象获得页面传递主键
13 int id=Integer.parseInt(idStr == null || "".equals(idStr)?"0":idStr);
14 String dbDriver = "com.mysql.jdbc.Driver";
15 String dbUrl = "jdbc:mysql://localhost:3306/book";
16 String dbUser = "root";
17 String dbPwd = "123456";
18 Class.forName(dbDriver);
19 Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
20 String sql = "delete from admin where id=" + id;//定义执行的删除sql语句
21 Statement stmt = conn.createStatement();
22 int res = stmt.executeUpdate(sql);
23 if (res < 0)
24     out.print("删除失败");
25 else
26     response.sendRedirect("do_query.jsp");
27 %>
28 </body>
29 </html>

3.实现编辑功能：

 1 <%@page import="java.sql.*"%>
 2 <%@ page language="java" contentType="text/html; charset=UTF-8"
 3     pageEncoding="UTF-8"%>
 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 5 <html>
 6 <head>
 7 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 8 <title>编辑</title>
 9 </head>
10 <body>
11 <%
12 String idStr = request.getParameter( "id" );
13 int id=Integer.parseInt(idStr == null || "".equals(idStr)?"0":idStr);
14 String dbDriver = "com.mysql.jdbc.Driver";
15 String dbUrl = "jdbc:mysql://localhost:3306/book";
16 String dbUser = "root";
17 String dbPwd = "123456";
18 Class.forName(dbDriver);
19 Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
20 String sql = "select * from admin where id=" + id;
21 Statement stmt = conn.createStatement();
22 ResultSet rs = stmt.executeQuery(sql);
23 if (rs.next()){
24 %>
25 <form action="do_update.jsp?id=<%= rs.getInt("id") %>" method="post">
26 <input type="text" name="username" value=<%= rs.getString("username") %>><br>
27 <input type="text" name="password" value=<%= rs.getString("password") %>><br>
28 <input type="submit" value="更新">
29 </form>
30 <%
31 } 
32 %>
33 </body>
34 </html>

4.实现更新功能:

<%@page import="java.sql.*"%>
<%@ 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>更新</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String idStr = request.getParameter( "id" );
int id=Integer.parseInt(idStr == null || "".equals(idStr)?"0":idStr);
String username = new String(request.getParameter("username"));
String password = new String(request.getParameter("password"));
String dbDriver = "com.mysql.jdbc.Driver";
String dbUrl = "jdbc:mysql://localhost:3306/book";
String dbUser = "root";
String dbPwd = "123456";
Class.forName(dbDriver);
Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
String sql = "update admin set username='"+username+"',password='"+password+"'where id =" +id;//定义执行更新的sql语句
Statement stmt = conn.createStatement();//创建Statement对象stmt
int res = stmt.executeUpdate(sql);//调用executeUpdate方法执行更新操作，结果值存储到res
if(res>0)
    response.sendRedirect("do_query.jsp");
else
    out.print("更新失败");
%>
</body>
</html>

5.向数据库添加记录：

<%@ 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>添加新用户</title>
</head>
<body>
<form action="do_insert.jsp" method="post">
<input type="text" name="username"><br>
<input type="password" name="password"><br>
<input type="submit" value="添加">
</form>
</body>
</html>

6.实现添加功能：

<%@page import="java.sql.*"%>
<%@ 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>添加用户</title>
</head>
<body>
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
String dbDriver = "com.mysql.jdbc.Driver";
String dbUrl = "jdbc:mysql://localhost:3306/book";
String dbUser = "root";
String dbPwd = "123456";
Class.forName(dbDriver);
Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
String sql = "insert into admin(username,password)values('"+ username +"','"+ password +"')";
Statement stmt = conn.createStatement();
int res = stmt.executeUpdate(sql);
if(res>0)
    response.sendRedirect("do_query.jsp");
else
    out.print("添加失败");
%>
</body>
</html>

编程的路总是孤独的，加油！痛并快乐着~~~~~