文章目录
5-3.从返回的ResultSet对象中获取相应的数据 (查询)
6-1.使用PreparedStatement预处理SQl语句(插入、删除、更新,不包括查询)
前言
作为个人学习记录,我会尽可能介绍的很详细,有误和不清楚地方可评论指正。或加我WX:chen195158
一、JDBC是什么?
JDBC是一套面向对象的应用程序接口,它制定了统一的访问各类关系数据库的标准接口,为各个数据库厂商(我们使用的是mysql数据库)提供了标准接口的实现。通过使用JDBC技术,开发人员可以用纯Java语言和标准的SQL语句编写完整的数据库应用程序,并且真正的实现了软件的跨平台型。
二、使用步骤
1.搭建JDBC开发环境
将jar包放入WebContent\WEB-INF\lib下
2.jsp导入 java.sql包
import="java.sql.*"
3. 注册加载一个driver驱动
Class.forName("com.mysql.jdbc.Driver");
4.创建数据库连接(Connection)
Connection con = DriverManager.getConnection(url,user,password);
其中:url为数据库地址,例如:jdbc:mysql://localhost:3306/stu
user和password为账户名和密码,学校机房的默认为root和root或者123456
关于 Connection类:
类中的方法:
1.获取执行Sql对象Statement
Statement createStatement();//创建一个SQL语句对象
Statement prepareStatement(String sql);//直接传出SQL语句
- Statement类中的方法
ResultSet executeQuery(String sql);//执行DQL(select)语句
5-1. 创建语句对象(查询)
Statement st= con.createStatement(); //获得数据库语句对象
5-2.用结果集ResultSet接收查询操作(查询)
ResultSet rs =st.executeQuery("select * from stu where sno='"+no+"'");//执行查询操作,查询接收到的学号为“no”的学生对象
5-3.从返回的ResultSet对象中获取相应的数据 (查询)
while(rs.next()){
System.out.println("学号:"+rs.getString("sno"));
System.out.println("所属系:"+rs.getString("sdep"));
}
6-1.使用PreparedStatement预处理SQl语句(插入、删除、更新,不包括查询)
PreparedStatement ps = con.prepareStatement("delete from student where sno=?");
//用?来代表要出入的参数值
6-2.设置占位符的值
调用PreparedStatement的setXxx(int index,Object val)设置占位符的值,其中index的值从1开始
ps.setString(1,no);//第一个问号的值为“no”
6-3.执行SQl语句
在查询中执行语句为executeQuery();
在更新操作中为excuteUpdate();
ps.executeUpdate();
7.释放资源
- 需要释放的对象: ResultSet 结果集,Statement 语句,Connection 连接
- 释放原则:先开的后关,后开的先关。ResultSet > Statement > Connection
ps.close();
con.close();
三、实例
1.数据库实时数据展示及操作页面
<%@ 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>
<style type="text/css">
#style-c
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
margin: 45px;
width: 480px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#style-c th
{
font-size: 13px;
font-weight: normal;
padding: 8px;
background: #e8edff;
border-right: 1px solid #9baff1;
border-left: 1px solid #9baff1;
color: #039;
}
#style-c td
{
padding: 8px;
background: #e8edff;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
color: #669;
}
</style>
</head>
<body>
<%
try{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8", "root", "293824");//获得连接
Statement st= con.createStatement(); //获得数据库元语对象
ResultSet rs =st.executeQuery("select * from student");//执行查询操作
%>
<table id='style-c'>
<tr>
<td>学号</td>
<td>系部</td>
<td>性别</td>
<td>年龄</td>
<td>姓名</td>
<td>操作</td>
</tr>
<%
while(rs.next()){
String sno=rs.getString("sno");
String sname= rs.getString("sname");
String sdep= rs.getString("sdep");
int sage=rs.getInt("sage");
String ssex = rs.getString("ssex");
%>
<tr>
<td><%=sno %></td>
<td><%=sdep %></td>
<td><%=ssex %></td>
<td><%=sage %></td>
<td><%=sname %></td>
<td><a href="deleteStu.jsp?no=<%=sno %>">删除</a> <%//把当前表单中的sno数据传出 %>
<a href="update.jsp?no=<%=sno %>">修改</a></td>
</tr>
<%}
rs.close();
st.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<tr><td colspan="6"> <a href="insert.jsp">增加</a></td></tr>
</table>
<form action="queryStu.jsp" method="post">
请输入你要查询的学号:<input type="text" name="no">
<input type="submit" value="查询"></form>
</body>
</html>
2.查询页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!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>Insert title here</title>
<style type="text/css">
#style-c
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
margin: 45px;
width: 480px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#style-c th
{
font-size: 13px;
font-weight: normal;
padding: 8px;
background: #e8edff;
border-right: 1px solid #9baff1;
border-left: 1px solid #9baff1;
color: #039;
}
#style-c td
{
padding: 8px;
background: #e8edff;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
color: #669;
}
</style>
</head>
<body>
<%
String no = request.getParameter("no");
try{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8", "root", "root");//获得连接
Statement st= con.createStatement(); //获得数据库元语对象
ResultSet rs =st.executeQuery("select * from student where sno='"+no+"'");//执行查询操作
%>
<table id="style-c">
<tr>
<td>学号</td>
<td>系部</td>
<td>性别</td>
<td>年龄</td>
<td>姓名</td>
</tr>
<%if(rs.next()){
String sno = rs.getString("sno");
String name = rs.getString("sname");
String dep = rs.getString("sdep");
int age = rs.getInt("sage");
String sex = rs.getString("ssex");
%>
<tr>
<td><%=no %></td>
<td><%=name %></td>
<td><%=dep %></td>
<td><%=age %></td>
<td><%=sex %></td>
</tr>
<%}else{ %>
<tr><td colspan="5">查无此人</td></tr>
<%}rs.close();
st.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}%>
</table>
</body>
</html>
3.执行删除操作的jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!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>Insert title here</title>
</head>
<body>
<%
String no = request.getParameter("no");
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8","root","293824");
PreparedStatement ps = con.prepareStatement("delete from student where sno=?");
ps.setString(1,no);
ps.executeUpdate();
ps.close();
con.close();
request.getRequestDispatcher("showStu.jsp").forward(request,response);//更新后跳转回show页面
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>
4.插入页面
<%@ 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>Insert title here</title>
</head>
<body>
<form action="insertStu.jsp" method="post">
<fieldset>
<legend>添加/修改页面</legend>
<label>学号:<input type="text" name="sno"></label><br>
<label>姓名:<input type="text" name="sname"></label><br>
<label>院系:<select name="sdep">
<option value="计算机系">计算机系</option>
<option value="外语系">外语系</option>
<option value="化工系">化工系</option>
<option value="机械工程系">机械工程系</option>
</select></label><br>
<label>年龄:<input type="text" name="sage"></label><br>
<label>学号:<input type="checkbox" name="ssex" value="男">男
<input type="checkbox" name="ssex" value="女">女
</label><br>
<label><input type="submit" value="提交"></label>
</fieldset>
</form>
</body>
</html>
5.插入操作的jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!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>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String a = request.getParameter("sno");
String b = request.getParameter("sname");
String c = request.getParameter("sdep");
String d = request.getParameter("sage");
String e = request.getParameter("ssex");
try{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8", "root", "293824");//获得连接
PreparedStatement ps = con.prepareStatement("insert into student(sno,sname,sdep,sage,ssex) values(?,?,?,?,?)");
ps.setString(1, a);
ps.setString(2, b);
ps.setString(3, c);
ps.setInt(4, Integer.parseInt(d));
ps.setString(5, e);
ps.executeUpdate();//执行SQl语句
ps.close();
con.close();
request.getRequestDispatcher("showStu.jsp").forward(request, response);
}catch(Exception f){
f.printStackTrace();
}
%>
</body>
</html>
6.更新页面
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import="java.sql.*"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
String no = request.getParameter("no");//接受表单传出的no值用于查询数据库对象
try{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8","root","293824");//获得连接
PreparedStatement ps = con.prepareStatement("select * from student where sno=?");
ps.setString(1,no);
ResultSet rs = ps.executeQuery();//执行SQl语句
rs.next();//判断是否有记录
String sno = rs.getString("sno");
String sname = rs.getString("sname");
String sdep = rs.getString("sdep");
int sage = rs.getInt("sage");
String ssex = rs.getString("ssex");
rs.close();
ps.close();
con.close();
%>
<form action="updateStu.jsp" method="post">
<fieldset>
<legend>添加/修改页面</legend>
<label>学号:<input type="text" name="sno" value="<%=sno%>"></label><br><!-- 从show.jsp页面获取到sno值填充到表单里 -->
<label>姓名:<input type="text" name="sname" value="<%=sname%>"></label><br>
<label>院系:<select name="sdep">
<option value="计算机系" <%if(sdep.equals("计算机系")){ %>selected="selected"<%} %>>计算机系</option><%//默认selected> %><!-- 使用selected设置默认属性 -->
<option value="外语系" <%if(sdep.equals("外语系")){ %>selected="selected"<%} %>>外语系</option>
<option value="化工系" <%if(sdep.equals("化工系")){ %>selected="selected"<%} %>>化工系</option>
<option value="机械工程系" <%if(sdep.equals("机械工程系")){ %>selected="selected"<%} %>>机械工程系</option>
</select></label><br>
<label>年龄:<input type="text" name="sage" value="<%=sage%>"></label><br>
<label>学号:<input type="radio" name="ssex" value="男" <%if(ssex.equals("男")){ %>checked="checked"<%} %>>男
<input type="radio" name="ssex" value="女" <%if(ssex.equals("女")){ %>checked="checked"<%} %>>女
</label><br>
<label><input type="submit" value="提交"></label>
</fieldset>
</form>
<%}catch(Exception e){
e.printStackTrace();
} %>
</body>
</html>
7.更新操作的jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import="java.sql.*"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String a = request.getParameter("sno");
String b = request.getParameter("sname");
String c = request.getParameter("sdep");
String d = request.getParameter("sage");
String e = request.getParameter("ssex");
try{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8","root","293824");//获得连接
PreparedStatement ps = con.prepareStatement("update student set sname=?,sdep=?,sage=?,ssex=? where sno=?");
ps.setString(1, b);
ps.setString(2,c);
ps.setInt(3, Integer.parseInt(d));
ps.setString(4, e);
ps.setString(5, a);
ps.executeUpdate();//执行SQl语句
ps.close();
con.close();
request.getRequestDispatcher("showStu.jsp").forward(request, response);
}catch(Exception f){
f.printStackTrace();
}
%>
</body>
</html>
四、总结