JavaWeb-JDBC增删改查

玉蜉蝣

已于 2024-05-22 00:02:27 修改

阅读量311

点赞数 9

文章标签： java 数据库开发语言 jsp jdbc

于 2024-05-21 23:58:10 首次发布

本文链接：https://blog.csdn.net/weixin_74092648/article/details/139104788

版权

一、实验内容

二、项目结构

三、数据库

数据库名称：users

表名：a

创建表的SQL语句：

create table a
(
    uname varchar(20),
    pword varchar(20),
    gender varchar(10)
)

四、源代码

4.1 User.java

package test;

public class User {
	
	String uname;
	String pword;
	String gender;
	
	public void setUname(String uname) {
		this.uname = uname;
	}
	public String getUname() {
		return uname;
	}
	public void setPword(String pword) {
		this.pword = pword;
	}
	public String getPword() {
		return pword;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getGender() {
		return gender;
	}
}

4.2 Test.java

package test;

import java.sql.*;
import com.microsoft.sqlserver.jdbc.SQLServerDriver; //SQL Server数据库引擎
import java.util.ArrayList;

public class Test {
	static Connection con; //连接数据库对象。必须加static，否则会报错
	static Statement smt;  //创建SQL命令的对象
	static ResultSet rs; //返回SQL语句查询结果集
	
	static String url = "jdbc:sqlserver://localhost:1433;databaseName=users"; //SQL Server数据源URL
	static String user = "yufuyou";
	static String password = "bugaosuni";
	
	static void open() {
		try {
			DriverManager.registerDriver(new SQLServerDriver()); //加载驱动
		}catch(SQLException e) {
			System.out.println("驱动不正确！");
		}
		System.out.println("数据库驱动成功！");
		
		try {
			con = DriverManager.getConnection(url, user, password);
			smt = con.createStatement();
		}catch(SQLException e) {
			e.printStackTrace();
			System.out.println("连接数据库失败！");
		}
		System.out.println("数据库连接成功！");
	} //打开数据库
	
	static void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (smt != null) {
				smt.close();
			}
			if (con != null) {
				con.close();
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
	} //关闭数据库
	
	public static User getOneUser(String uname, String pword) {
		User u = new User();
		String sql = "select * from a where uname = '"+uname+"' and pword = '"+pword+"'";
		
		open();
		try {
			rs = smt.executeQuery(sql);
			if (rs.next()) {
				u.setUname(rs.getString(1)); //写列序号或者列名
				u.setPword(rs.getString(2));
				u.setGender(rs.getString(3));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		close();
		
		return u;
	} //得到某一个用户的信息
	
	public static ArrayList<User> getAllUsers(){
		ArrayList<User> list = new ArrayList<User>();
		String sql = "select * from a";
		
		open();
		try {
			rs = smt.executeQuery(sql);
			while (rs.next()) {
				User u = new User();
				u.setUname(rs.getString(1));
				u.setPword(rs.getString(2));
				u.setGender(rs.getString(3));
				list.add(u);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		close();
		
		return list;
	} //得到所有用户的信息
	
	public static int InsertNewUser(User user) {
		int flag = 0;
		String sql = "insert into a values('"+user.getUname()+"', '"+user.getPword()+"', '"+user.getGender()+"')";
		
		open();
		try {
			flag = smt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close();
		
		return flag;
	} //增添新用户。executeUpdate 方法返回的是影响的行数，如果大于0，则表明成功插入
	
	public static int DeleteUser(String uname) {
		int flag = 0;
		String sql = "delete from a where uname = '"+uname+"'";
		
		open();
		try {
			flag = smt.executeUpdate(sql);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		close();
		
		return flag;
	} //删除用户
}

4.3 suc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="test.Test, test.User, java.util.ArrayList" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>JDBC增删改查</title>
</head>
<body>

	<%
		ArrayList<User> userlist = Test.getAllUsers();
	%>
	<h2 align="center">UserList</h2>
	<table cellpadding="2" align="center">
		<thead> <!-- 表头标签 -->
			<tr>
				<th>姓名</th>
				<th>密码</th>
				<th>性别</th>
			</tr>	
		</thead>
		<tbody> <!-- 表格主体标签 -->
		<%
			for (User user : userlist) {	
		%>
			<tr>
				<td><%= user.getUname() %></td>
				<td><%= user.getPword() %></td>
				<td><%= user.getGender() %></td>
				<td><a href="delete.jsp?uname=<%= user.getUname() %>">删除</a></td>
			</tr>
		 <%
			}
		 %>
		 	<tr>
				<td><button onclick="window.location.href='insert.jsp'">插入</button></td>
				<td><button onclick="window.location.href='query.jsp'">查询</button></td>
			</tr>
	</table>
	
</body>
</html>

4.4 insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="test.Test, test.User" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>insert</title>
</head>
<body>

	<form action="insert.jsp" method="post">
		<table cellpadding="2" align="center">
			<tr>
				<td align="center"><h2>表单</h2></td>
			</tr>
			<tr>
				<td>姓名：</td>
				<td><input type="text" name="uname"></td>
			</tr>
			<tr>
				<td>密码：</td>
				<td><input type="password" name="pword"></td>
			</tr>
			<tr>
				<td>性别：</td>
				<td><input type="radio" name="gender" value="female">女</td>
				<td><input type="radio" name="gender" value="male">男</td>
			</tr>
			<tr>
				<td><input type="submit" value="提交"></td>
			</tr>
		</table>
	</form>
	<%
		if (request.getMethod().equalsIgnoreCase("post")){ //等到页面提交之后再进行插入操作
			User user = new User();
			user.setUname(request.getParameter("uname"));
			user.setPword(request.getParameter("pword"));
			user.setGender(request.getParameter("gender"));
			int flag = Test.InsertNewUser(user);
			if (flag > 0){
				out.print("插入成功！");
	%>			
			<script type="text/javascript">
                setTimeout(function() {
                    window.location.href = "suc.jsp";
                }, 3000); //设置3s后跳转
            </script>
	<%
			}
			else{
				out.print("插入失败！");
			}
		} //插入中文会乱码。
	%>

</body>
</html>

4.5 delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="test.Test, test.User" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>delete</title>
</head>
<body>

	<%
		String uname=request.getParameter("uname"); 
		int flag = Test.DeleteUser(uname);
		if (flag > 0){
			out.print("删除成功！");
	%>
			<script type="text/javascript">
                setTimeout(function() {
                    window.location.href = "suc.jsp";
                }, 3000); //设置3s后跳转
            </script>
	<%
		}
		else{
			out.print("删除失败！");
		}
	%>

</body>
</html>

4.6 query.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="test.Test, test.User" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>query</title>
</head>
<body>

	<form action="query.jsp" align="center" method="post">
		<table>
			<tr>
				<td align="center"><h2>查询用户性别</h2></td>
			</tr>
			<tr>
				<td>姓名：</td>
				<td><input type="text" name="uname"></td>
			</tr>
			<tr>
				<td>密码：</td>
				<td><input type="password" name="pword"></td>
			</tr>
			<tr>
				<td><input type="submit" value="查询"></td>
			</tr>
		</table>
	</form>
	<%
		if (request.getMethod().equalsIgnoreCase("post")){
			User user = Test.getOneUser(request.getParameter("uname"), request.getParameter("pword"));
	%>
			<table>
				<tr>
					<td>性别：</td>
					<td><%=user.getGender() %></td>
				</tr>
			</table>
	<%
		}
	%>

</body>
</html>