我的第一个JDBC小项目

引言：以下都是个人学习中的一些总结，本人能力有限，其中存在很多错误与不足，还请阅读者斧正。！并非教程！！并非教程！！并非教程！

JDBC是Java DataBase Connectivity的简称，在我理解看来，它就是Java配合DB用来实现客户增删改查的需求的工具。

JDBC的五个步骤： 1.加载JDBC驱动Class.forName(“com.mysql.jdbc.Driver”); 2.与数据库建立连接DriverManager.getConnection(url,uname,pwd); 3.获取操作对象，发送sql语句得到返回结果 4.处理返回结果 5.释放资源.close() ———————————————— 版权声明：本文为博主「CHANGEXCX」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/CHANGEXCX/article/details/80399626

一、项目需求

1.完成数据从前端（页面）成功添加数据到后台数据库中（增） 2.实现数据从前端（页面）成功删除后台数据库中的数据（删） 3.完成数据从前端（页面）成功修改数据并在后台数据库中显示（改） 4.实现从前端（页面）成功查看后台数据库中所有数据（查）

二、构建项目大概框架(MVC)

1.后台数据库(Model) 2.前端JSP块(View) 3.Java代码实现块(Controller)

三、具体实施步骤

1.数据库 1.1建立数据库，创建数据表

2.Java 2.1创建好相应的包与文件夹

2.2编写User包中的User.class

package User; public class User{ private int id; private String uname; private String up; private String sex; public void setId(int id) { this.id= id; } public int getId() { return id; } public void setUname(String uname) { this.uname=uname; } public String getUname() { return uname; } public void setUp(String up) { this.up=up; } public String getUp() { return up; } public void setSex(String sex) { this.sex=sex; } public String getSex() { return sex; } public String toString() { return "User[id="+id+",uname="+uname+",up="+up+",sex="+sex+"]"; } }

2.3架包：mysql-connector-java 没有这个包Java和数据库就不能连接

2.4编写DB包中的有关数据库的一系列操作的DB.class

package DB; import User.User; import java.sql.*; import java.util.ArrayList; public class DB{ private Connection conn = null;//连接对象 private PreparedStatement pstmt = null;//执行sql语句对象 private ResultSet rs= null;//装载查询结果集 //=================数据库连接======================= public Connection getConnection() { Connection con = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 con=(Connection)DriverManager.getConnection ("jdbc:mysql://localhost:3306/testttt?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root","root");//与数据库建立连接DriverManager.getConnection(url,uname,pwd); }catch(Exception e){ e.printStackTrace(); System.out.println("数据库连接失败"); } return con; } //=================关闭======================= public void releaseDB(ResultSet rs,PreparedStatement pstmt,Connection conn) { if(rs!=null) { try { rs.close(); }catch(SQLException e) { e.printStackTrace(); } } if(pstmt!=null) { try { pstmt.close(); }catch(SQLException e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); }catch(SQLException e){ e.printStackTrace(); } } } //====================添加用户数据=================== public boolean add(User user) { conn=getConnection();//连接数据库 try { String sql = "insert into test (uname,up,sex) values(?,?,?)";//sql插入语句 pstmt=conn.prepareStatement(sql);//创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库 pstmt.setString(1,user.getUname());//将页面端获取到的用户填入的数据覆盖数据库中原始数据(ps:“1”指第一个问号) pstmt.setString(2, user.getUp()); pstmt.setString(3,user.getSex()); pstmt.executeUpdate();//此方法用于执行sql语句，返回值为受影响的行数（更新计数），int类型 pstmt.close();//关闭 return true; }catch(SQLException e) { e.printStackTrace(); return false; } } //====================根据id删除用户数据=================== public boolean delete(int id) { conn = getConnection(); try { String sql = "delete from test where id="+id;//sql删除语句 pstmt=conn.prepareStatement(sql); pstmt.executeUpdate(); pstmt.close(); return true; }catch(SQLException e) { e.printStackTrace(); return false; } } //====================根据ID修改用户数据=================== public boolean update(User user,int id) { conn = getConnection(); try { String sql = "update test set uname=?,up=?,sex=? where id=?";//sql修改语句 pstmt = conn.prepareStatement(sql); pstmt.setString(1,user.getUname()); pstmt.setString(2,user.getUp()); pstmt.setString(3,user.getSex()); pstmt.setInt(4,id);//此ID是需要修改的用户ID pstmt.executeUpdate(); pstmt.close(); }catch(SQLException e) { e.printStackTrace(); return false; }return true; } //====================查看所有用户的数据=================== public ArrayList<User> Query(){ conn = getConnection(); ArrayList<User> list = new ArrayList<User>();//用于存储**对象**。与数组不同，数组一旦创建，长度固定，但是ArrayList的长度是动态的，不受限制，可以存储任意多的对象 try { String sql = "select * from test";//sql查询语句 pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery();//此方法用于下达select指令，以查询数据库，它会把数据库响应的查询结果存放在ResultSet类对象中供我们使用 while(rs.next()) {//rs是结果集。查询出的记录是一个列表，初始时指针指向的是第一条记录之前的。每rs.next()一次指针都会向后移动一位，指向下一条记录。 User user = new User(); user.setId(rs.getInt("id")); user.setUname(rs.getString("uname")); user.setUp(rs.getString("up")); user.setSex(rs.getString("sex")); list.add(user);//将每次查询的user对象放到list中储存 } }catch(SQLException e) { e.printStackTrace(); System.out.println("查询失败"); }return list; } //====================根据id查看其中一个用户的数据=================== //原理同上 public ArrayList<User> QueryOne(int id){ conn=getConnection(); ArrayList<User> list =new ArrayList<User>(); try { String sql = "select * from test where id="+id; pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()) { User user = new User(); user.setId(rs.getInt("id")); user.setUname(rs.getString("uname")); user.setUp(rs.getString("up")); user.setSex(rs.getString("sex")); list.add(user); } }catch(SQLException e) { e.printStackTrace(); System.out.println("查询失败"); }return list; } //================根据uname，up登陆============================ public boolean Login(String uname ,String up){ conn=getConnection(); boolean flag = false; try{ String sql ="select * from test where uname=? and up=?"; pstmt=conn.prepareStatement(sql); pstmt.setString(1,uname); pstmt.setString(2,up); rs=pstmt.executeQuery(); if(rs.next()){ flag = ture; } } catch(SQLException e){ e.printStackTrace(); } return flag; } //====================测试==================================== public static void main(String[] args) { DB db = new DB(); User user = new User(); user.setUname("a"); user.setUp("a"); user.setSex("a"); db.add(user); System.out.println(db.update(user, 8)); ; }; }

3.JSP 3.1建立几个必要的JSP前端页面

显示用户信息的主页面：Manage.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import = "DB.DB" import = "User.User" import = "java.util.ArrayList"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>管理用户界面</title> </head> <body> <table align="center" border="1" width="800" > <tr> <td align="center" colspan="8">用户管理界面</td> </tr> <tr> <td>id</td> <td>用户名</td> <td>密码</td> <td>性别</td> <td>删除</td> <td>修改</td> <td>查看</td> </tr> <%ArrayList<User> list =(ArrayList<User>)request.getAttribute("list");for(User user:list){ %> <tr><!--request.getAttribute需要返回对象时要强制转换,foreach遍历 --> <td><%=user.getId()%></td> <td><%=user.getUname() %></td> <td><%=user.getUp() %></td> <td><%=user.getSex() %></td> <td><a href="<%=basePath %>/controller?id=<%=user.getId()%>&action=delete">删除</a></td> <td><a href="<%=basePath %>/controller?id=<%=user.getId()%>&action=update">修改</a></td> <td><a href="<%=basePath %>/controller?id=<%=user.getId()%>&action=detail">查看</a></td> <td><a href="<%=basePath %>/register.jsp">添加</a></td> </tr> <%} %> </table> </body> </html>

注册(添加信息）页面，register.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>注册</title> </head> <body> <form action="add" method="post"> 用户名:<input type="text" name = uname><br> 密码<input type = "password" name = up><br> <input type="radio" name=sex value="male">男<input type="radio" name= sex value="female">女<br> <input type ="submit" value = "注册"> <br> </form> </body> </html>

查看个人信息，index.jsp (请无视菜鸟的乱码）

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import ="DB.DB" import ="User.User" import = "java.util.ArrayList" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>查看个人信息</title> </head> <body> <table align="center" border="1"> <tr> <td>id</td> <td>用户名</td> <td>密码</td> <td>性别</td> </tr> <%ArrayList<User> list =(ArrayList<User>)request.getAttribute("usermessagall") ;for(User user:list){%> <tr> <td><%=user.getId() %></td> <td><%=user.getUname() %></td> <td><%=user.getUp() %></td> <td><%=user.getSex() %></td> </tr> <%} %> </table> </body> </html>

修改数据，update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import ="java.util.ArrayList" import ="User.User" import ="DB.DB" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>修改信息</title> </head> <body> <form action="update" method="post"> <%int id=Integer.parseInt(request.getParameter("id")); session.setAttribute("id",id);%> <%ArrayList<User> list= (ArrayList<User>)request.getAttribute("list");for(User user:list){%> <br> Username:<input type="text" name = "uname" value ="<%=user.getUname()%>" ><br> Password<input type = "text" name ="up" value="<%=user.getUp()%>"><br> <%if(user.getSex().equals("male")){ %> <input type="radio" name="sex" value="male" checked="male">Male <input type="radio" name= "sex" value="female">Female<br> <%}else{ %> <input type="radio" name="sex" value="male" >Male <input type="radio" name= "sex" value="female" checked="female">Female<br> <% }}%> <%-- <%=request.getParameter("id") %> <%request.getParameter("id");request.setAttribute("id",id) ;%> --%> <input type ="submit" value = "Update"> <br> </form> </body> </html>

登陆,login.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>登陆</title> </head> <body> <form action="login" method="post"> 用户名:<input type="text" name = uname><br> 密码<input type = "password" name = up><br> <input type="submit" value="登陆"> </form> </body> </html>

4.Servlet**（以下文件均为servlet） servlet在我的项目中是非常重要的一环，它主要负责处理客户请求与业务处理（中间角色） 创建h.java，作用：把查询结果转发到Manage.jsp

package Servlet; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import DB.DB; import User.User; import java.sql.*; import java.util.ArrayList; /** * Servlet implementation class h */ public class h extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public h() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse * response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub DB db = new DB(); ArrayList<User> list = db.Query();//查询“所有数据”，并把“所有数据”放到list中 request.setAttribute("list", list);//servlet传递参数到jsp用setAttribute（）方法 request.getRequestDispatcher("/Manage.jsp").forward(request, response);//转发：地址不变，可以用request对象传递参数，“”中带/ } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse * response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub // doGet(request, response); } }

创建add.java，作用：添加用户

package Servlet; import java.io.PrintWriter; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import DB.DB; import User.User; /** * Servlet implementation class add */ public class add extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public add() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); User user = new User(); PrintWriter out = response.getWriter(); String uname=request.getParameter("uname");//以form表单和url从jsp传到servlet的参数，可以用getParameter（）方法获取 String up = request.getParameter("up"); String sex = request.getParameter("sex"); user.setUname(uname); user.setUp(up); user.setSex(sex); DB db = new DB(); if(db.add(user)) { out.print("<script language=javascript>alert('Add Success');window.location.href='/JspDemo/h';</script>"); }else { out.print("<script language=javascript>alert('Add failure');window.location.href='/JspDemo/register.jsp';</script>"); } // out.print(uname); // out.print(up); // out.print(sex); } }

创建controller.java，作用：对用户发送的请求做出响应

package Servlet; import java.io.PrintWriter; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import DB.DB; import User.User; import java.sql.*; import java.util.ArrayList; /** * Servlet implementation class controller */ public class controller extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public controller() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse * response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub PrintWriter out = response.getWriter(); response.setCharacterEncoding("UTF-8"); int id = Integer.parseInt(request.getParameter("id"));//获取id，注意：request.getParameter()所取到的都是默认字符串类型，需要类型转换。 String action = request.getParameter("action"); DB db = new DB(); User user = new User(); if (action.equals("detail")) { ArrayList<User> list = db.QueryOne(id); request.setAttribute("usermessagall", list); request.getRequestDispatcher("/index.jsp").forward(request, response); } if (action.equals("delete")) { if (db.delete(id)) { out.println("<script language=javascript>alert('Delete Success');window.location.href='/JspDemo/h';</script>"); } else { out.println("<script language=javascript>alert('Delete Failure');window.location.href='/JspDemo/h';</script>"); } } if (action.equals("update")) { ArrayList<User> list = db.QueryOne(id); request.setAttribute("list", list); request.getRequestDispatcher("/update.jsp").forward(request, response); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse * response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }

建立update.java，作用用于响应客户端的修改信息请求（本来应该放在controller里，后期再完善，明天考教资没时间了）

package Servlet; import java.io.PrintWriter; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import DB.DB; import User.User; /** * Servlet implementation class update */ public class update extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public update() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); PrintWriter out = response.getWriter(); //在servlet中用session方法只能request.getSession().getAttribute()，此时获得到的是对象，需先转为String型，再转为Int型 int id = Integer.parseInt((request.getSession().getAttribute("id").toString())); String uname = request.getParameter("uname");//获取客户端输入的数据 String up = request.getParameter("up"); String sex = request.getParameter("sex"); // out.print(uname); // out.print(up); // out.print(sex); User user = new User(); user.setUname(uname); user.setUp(up); user.setSex(sex); // DB db = new DB(); // out.print(id); if(db.update(user, id)) { out.print("<script language=javascript>alert('Update Success');window.location.href='/JspDemo/h';</script>"); }else { out.print("<script language=javascript>alert('Update Failure');window.location.href='/JspDemo/update.jsp';</script>"); } } }

创建login.java，作用：实现登录功能。

package Servlet; import java.io.PrintWriter; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.sql.*; import DB.DB; import User.User; /** * Servlet implementation class login */ public class login extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public login() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.getWriter().append("Served at: ").append(request.getContextPath()); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub User user = new User(); PrintWriter out = response.getWriter(); String uname = request.getParameter("uname"); String up = request.getParameter("up"); // user.setUname(uname); // user.setUp(up); DB db = new DB(); if(db.Login(uname, up)) { out.println("<script language=javascript>alert('Login Success');window.location.href='/JspDemo/loginSuccess.jsp';</script>"); }else { out.println("<script language=javascript>alert('Login failure');window.location.href='/JspDemo/login.jsp';</script>"); } } }

四、功能展示

原始界面： 1.添加 2.删除： 删除id28 3.修改 修改id27 4.查看 查看id27 5.登陆 登陆成功 登陆失败

五、学习笔记

1.业务逻辑

2.关于JSP与Servlet之间的传参问题 2.1JSP👉Servlet 2.1.1：form表单传递。要传递的参数用 dd<input name="xxx">,把要传递的参数名写在name里，servlet中用request.getParameter()方法接收参数 2.1.2：URL中传递。比如 <a href="<%=basePath %>/controller?id=<%=user.getId()%>&action=delete"> 其中id为URL中传递的参数，user.getId()为其值，servlet中同样用request.getParameter()方法接收参数 2.1.3：session对象（不可以用request.setAttribute)。在jsp中设置要传递的参数

String a="b"; session.setAttribute(“a”,a);

在servlet中只需 String a = (request.getSession().getAttribute("a")).toString(); 即可从jsp传递参数a到servlet

form和URL只可用请求转发 session请求转发和重定向都能传递参数

2.2JSP👈Servlet 2.2.1.session对象传值 在servlet中传递，利用request.getSession().setAttribute(" ", );方法

String a = "aaaa"; request.getSession().setAttribute("a",a); request.getRequestDispatcher("/t.jsp").forward(request,response);//请求转发

在jsp中接收

<%=session.getAttribute("a")%>//即可接收到传递过来的值

2.2.2 request.setAttribute();传递，原理同上 昨日疑问：session对象能不能从servlet传参到jsp

另，附上session与request的区别 request request范围较小一些，只是一个请求。

request对象的生命周期是针对一个客户端(说确切点就是一个浏览器应用程序)的一次请求，当请求完毕之后，request里边的内容也将被释放点 。

简单说就是你在页面上的一个操作，request.getParameter()就是从上一个页面中的url、form中获取参数。

但如果一个request涉及多个类，后面还要取参数，可以用request.setAttribute()和request.getAttribute()。

但是当结果输出之后，request就结束了。

session session可以跨越很多页面。 而session的生命周期也是针对一个客户端，但是却是在别人设置的会话周期内(一般是20-30分钟)，session里边的内容将一直存在，即便关闭了这个客户端浏览器 session也不一定会马上释放掉的。

可以理解是客户端同一个IE窗口发出的多个请求。 这之间都可以传递参数，比如很多网站的用户登录都用到了。