当前位置： 首页 > backend >正文

JavaWeb预习（jdbc）

backend 2025/6/25 4:46:58

基础

1.驱动程序接口Driver

每种数据库都提供了数据库驱动程序，并且都提供了一个实现java.sql.Driver接口的类，称为Driver

对于MySql，其Driver类为com.mysql.jdbc.Driver，加载该类的语句为：

Class.forName("com.mysql.jdbc.Driver")

2.驱动程序管理器DriverManager

DriverManager类用来建立数据库连接

Connection getConnection(String url,String user,String password)

URL语法格式为:

jdbc:子协议:数据源

eg: 

String url = "jdbc:mysql:localhost:3306/user";
Connection con = DriverManager.getConnection(url,"root","123456");

3.数据库连接接口Connection

Java程序对数据库的操作都在该对象上进行

eg：

Statement stmt = con.createStatement();

该语句不需要SQL语句，在执行时加上SQL就行

还有PreparedStatement，直接在括号加上sql执行

4.执行SQL语句接口Statement

用来执行静态SQL语句，并返回执行结果，

executeQuery(String sql)执行select

executeUpdate(String sql)执行insert，update，delete

增加

JDBC连接数据库

<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager" %>
<%@page import="java.sql.Connection" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body><%String driverName = "com.mysql.jdbc.Driver";String userName = "root";String userPwd = "1234";String dbName = "students";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "Insert into stu_info(id,name,sex,age,weight,hight) values(?,?,?,?,?,?)";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1,16);pstmt.setString(2,"张三");pstmt.setString(3,"男");pstmt.setInt(4,20);pstmt.setFloat(5,70);pstmt.setFloat(6,175);int n = pstmt.executeUpdate();if(n == 1){%> 数据插入成功<br> <%}else{%> 数据插入失败<br><%}if(pstmt != null){pstmt.close();}if(conn != null){conn.close();}%>
</body>
</html>

<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body><%String driverName = "com.mysql.jdbc.Driver";String dbName = "students";String userName = "root";String userPwd = "1234";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "Insert into stu_info(id,name,sex,age,weight,hight) values(17,'王五','男',21,80,170)";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.executeUpdate();%>
</body>
</html>

实例：

1.表单： 

<%@ 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="sjk.jsp" method="post"><table><tr><td>学号</td><td><input type="text" name="id"></td></tr><tr><td>姓名</td><td><input type="text" name="name"></td></tr><tr><td>性别</td><td><input type="text" name="sex"></td></tr><tr><td>年龄</td><td><input type="text" name="age"></td></tr><tr><td>体重</td><td><input type="text" name="weight"></td></tr><tr><td>身高</td><td><input type="text" name="hight"></td></tr><tr><td colspan="2" align="center"><input type="submit" value="提交">&nbsp;<input type="reset" value="取消"></td></tr></table></form>	
</body>
</html>

2.连接数据库：


<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body><%String driverName = "com.mysql.jdbc.Driver";String dbName = "students";String userName = "root";String userPwd = "1234";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "Insert into stu_info(id,name,sex,age,weight,hight) values(?,?,?,?,?,?)";PreparedStatement pstmt = conn.prepareStatement(sql);request.setCharacterEncoding("UTF-8");int id = Integer.parseInt(request.getParameter("id"));String name = request.getParameter("name");String sex = request.getParameter("sex");int age = Integer.parseInt(request.getParameter("age"));float weight = Float.parseFloat(request.getParameter("weight"));float hight = Float.parseFloat(request.getParameter("hight"));pstmt.setInt(1,id);pstmt.setString(2,name);pstmt.setString(3,sex);pstmt.setInt(4,age);pstmt.setFloat(5,weight);pstmt.setFloat(6,hight);try{int n = pstmt.executeUpdate();if(n==1){%>数据插入成功<br><%}else {%>数据插入失败<br><%}}catch(Exception e){%>更新过程出现异常错误！<br><%=e.getMessage() %><%}%>
</body>
</html>

3.回显

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生数据表</title>
</head>
<body><%String driverName = "com.mysql.jdbc.Driver";String userName = "root";String userPwd = "1234";String dbName = "students";String url1 = "jdbc:mysql://localhost:3306/" + dbName;String url2 = "?user=" + userName + "&password=" + userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1 + url2 + url3;Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try {Class.forName(driverName);conn = DriverManager.getConnection(url);// 创建可滚动的 PreparedStatementString sql = "select * from stu_info";pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);rs = pstmt.executeQuery();// 获取记录总数rs.last();int totalRows = rs.getRow();%>你要查询的学生数据表中共有<%= totalRows %> 人<center><table border="1"><tr><td>记录条数</td><td>学号</td><td>姓名</td><td>性别</td><td>年龄</td><td>体重</td><td>身高</td></tr><%// 将光标移动到结果集的开始位置rs.beforeFirst(); while(rs.next()){%><tr><td><%= rs.getRow() %></td><td><%= rs.getString("id") %></td><td><%= rs.getString("name") %></td><td><%= rs.getString("sex") %></td><td><%= rs.getString("age") %></td><td><%= rs.getString("weight") %></td><td><%= rs.getString("hight") %></td></tr><%}%></table></center><%} catch (Exception e) {e.printStackTrace();%><p style="color:red">发生错误: <%= e.getMessage() %></p><%} finally {// 确保资源被关闭try {if (rs != null) rs.close();if (pstmt != null) pstmt.close();if (conn != null) conn.close();} catch (Exception e) {e.printStackTrace();}} // 确保 finally 块正确闭合%>
</body>
</html>

4.带条件的回显

<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<html><head><title>查询满足条件的学生页面</title> <style>.style1 {border-collapse: collapse;border-spacing: 0px;}</style></head><body><%String dbName = "students";String driverName = "com.mysql.jdbc.Driver";String userName = "root";String userPwd = "1234";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "select * from stu_info where weight>=? and weight<=?";PreparedStatement pstmt = null;ResultSet rs = null;pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);pstmt.setFloat(1, 60);pstmt.setFloat(2,80);rs = pstmt.executeQuery();rs.last();%>共有<%=rs.getRow() %>人<center><table border="1" class="style1"><tr><td>记录条数</td><td>学号</td><td>姓名</td><td>性别</td><td>年龄</td><td>身高</td><td>体重</td></tr><%rs.beforeFirst();while(rs.next()){%><tr><td><%=rs.getRow() %></td><td><%=rs.getString("id") %></td><td><%=rs.getString("name") %></td><td><%=rs.getString("sex") %></td><td><%=rs.getString("age") %></td><td><%=rs.getString("hight") %></td><td><%=rs.getString("weight") %></td></tr><%}%></table></center><%if(rs!=null){rs.close();}if(conn!=null){conn.close();}if(pstmt!=null){pstmt.close();}%></body>
</html>

5.加客户端的条件查询

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>条件查询页面</title><style>
.css1 {width: 40px;
}
</style>
</head>
<body><form action="huixian2.jsp" method="post">性别:男<input type="radio" value="男" name="sex" checked="checked"> 女<input type="radio" value="女" name="sex"><br>体重范围：最小<input type="text" name="w1" class="css1"> &nbsp;&nbsp;最大<input type="text" name="w2" class="css1"><br><input type="submit" value="提交">&nbsp;&nbsp;<input type="reset" value="取消"></form>
</body>
</html>
<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<html><head><title>查询满足条件的学生页面</title> <style>.style1 {border-collapse: collapse;border-spacing: 0px;}</style></head><body><%String dbName = "students";String driverName = "com.mysql.jdbc.Driver";String userName = "root";String userPwd = "1234";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "select * from stu_info where sex=? and weight>=? and weight<=?";PreparedStatement pstmt = null;ResultSet rs = null;pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);request.setCharacterEncoding("UTF-8");pstmt.setString(1,request.getParameter("sex"));pstmt.setFloat(2,Float.parseFloat(request.getParameter("w1")));pstmt.setFloat(3,Float.parseFloat(request.getParameter("w2")));rs = pstmt.executeQuery();rs.last();%>共有<%=rs.getRow() %>人<center><table border="1" class="style1"><tr><td>记录条数</td><td>学号</td><td>姓名</td><td>性别</td><td>年龄</td><td>身高</td><td>体重</td></tr><%rs.beforeFirst();while(rs.next()){%><tr><td><%=rs.getRow() %></td><td><%=rs.getString("id") %></td><td><%=rs.getString("name") %></td><td><%=rs.getString("sex") %></td><td><%=rs.getString("age") %></td><td><%=rs.getString("hight") %></td><td><%=rs.getString("weight") %></td></tr><%}%></table></center><%if(rs!=null){rs.close();}if(conn!=null){conn.close();}if(pstmt!=null){pstmt.close();}%></body>
</html>

删除

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE html> 
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body><%request.setCharacterEncoding("UTF-8");String driverName = "com.mysql.jdbc.Driver";String dbName = "students";String userName = "root";String userPwd = "1234";String url1 = "jdbc:mysql://localhost:3306/"+dbName;String url2 = "?user="+userName+"&password="+userPwd;String url3 = "&useUnicode=true&characterEncoding=UTF-8";	String url = url1+url2+url3;Class.forName(driverName);Connection conn = DriverManager.getConnection(url);String sql = "Delete from stu_info where name = ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setString(1, request.getParameter("name"));try{ int n= pstmt.executeUpdate();if(n>=1){%>数据删除操作成功！<br><%}else{%>数据删除操作失败！<br><%} }catch(Exception e){%>删除更新过程出现异常错误！<br><%=e.getMessage()%><%            }if(conn!=null){conn.close();}if(pstmt!=null){pstmt.close();}%>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body><form action="delete.jsp" method="post">
请输入要删除数据的姓名：
<input type="text" name="name"><br>
<input type="submit" value="提交">
</form></body>
</html>

http://www.xdnf.cn/news/12841.html

相关文章：

  • 拼多多官方内部版 7.58.0 | 极限精简，只有2.5M
  • 【笔记】Poetry虚拟环境创建示例
  • Prompt Tuning（提示调优）到底训练优化的什么部位
  • DiscuzX3.5发帖json api
  • maven 1.0.0idea的使用说明
  • Vue3学习（watchEffect，标签的ref属性，计数器，defineExpose）
  • SpringCloud学习笔记-4
  • 实验二：数码管动态显示实验
  • 建造者模式深度解析与实战应用
  • WEB3技术重要吗，还是可有可无?
  • STM32入门学习之系统时钟配置
  • K8S认证|CKS题库+答案| 7. Dockerfile 检测
  • 五、jmeter脚本参数化
  • PHP中如何定义常量以及常量和变量的主要区别
  • Spark流水线+Gravitino+Marquez数据血缘采集
  • java综合项目开发一课一得
  • 使用 Melos 高效管理 Flutter/Dart Monorepo 项目
  • 用 Melos 解决 Flutter Monorepo 的依赖冲突：一个真实案例
  • Python 包管理器 uv 介绍
  • 基于PostGIS的各地级市路网长度统计及Echarts图表可视化实践-以湖南省为例
  • 支持selenium的chrome driver更新到137.0.7151.68
  • 时序数据库IoTDB结合SeaTunnel实现高效数据同步
  • 七、Sqoop Job：简化与自动化数据迁移任务及免密执行
  • Ubuntu20.04中 Redis 的安装和配置
  • 通过Cline使用智能体
  • webpack其余配置
  • uni-app学习笔记二十七--设置底部菜单TabBar的样式
  • AUTOSAR实战教程--标准协议栈实现DoIP转DoCAN的方法
  • 12-OPENCV ROCKX项目 人脸拍照
  • 【Blender】Blender 基础：导入导出