我的第一个JDBC小项目
程序员文章站
2022-04-10 23:38:04
...
我的第一个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()
————————————————
版权声明：本文为CSDN博主「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;
}
//====================测试====================================
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>
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>");
}
}
}
四、功能展示
原始界面：
1.添加
2.删除：
删除id28
3.修改
修改id27
4.查看
查看id27
五、学习笔记
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对象。在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
昨日疑问：session对象能不能从servlet传参到jsp
上一篇： 桂皮能长期吃吗，桂皮黑暗料理吃法你会吗