脚本方式实现数据库增删改查
哇塞,一个空格的问题花了一整天的时间终于找出了错~~~
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>
编程的路总是孤独的,加油!痛并快乐着~~~~~