从JDBC学习数据库的基础功能:查找修改添加删除
首先,学习数据库技术,我们先要安装JDBC驱动,连接数据库,然后学习并掌握JDBC中常用接口。
在数据库操作技术中,最简单的几个功能就是数据库的查找修改添加和删除这四个基本功能,而在JDBC中提供了两种可以实现这四个功能的方法,一种是通过Statement对象执行静态的SQL语句实现,一种是通过PrepaerdStatement对象执行动态的SQL语句实现。
1)、查找操作:
以查找一个数据库中某一表为例,应用上述的两个对象从数据库t_class中查询c_class字段:
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8” import=“java.sql.*”%>
<%
String banji=new String(request.getParameter(“banji”).getBytes(“UTF-8”),“utf-8”);
try {
Class.forName(“com.mysql.cj.jdbc.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con=DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/jwdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8”,“root”,“123456”);
PreparedStatement stmt =con.prepareStatement(“select*from t_class where c_class=?”);
stmt.setString(1, banji);
ResultSet rs=stmt.executeQuery();
while(rs.next()){
out.print(“行政班:”+rs.getString(2)+" 年级:"+rs.getString(3)+" 学院:"+rs.getString(4)+" 专业:"+rs.getString(5));
}
rs.close();
stmt.close();
con.close();
%>
运行结果:
2)、修改功能:
以修改一个数据库中某一表为例,应用上述的两个对象从数据库t_class中修改c_year字段:
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8” import=“java.sql.*”%>
<%
String banji2=new String(request.getParameter(“banji2”).getBytes(“UTF-8”),“utf-8”);
String xuenian=new String(request.getParameter(“xuenian1”).getBytes(“UTF-8”),“utf-8”);
String xueyuan=new String(request.getParameter(“xueyuan1”).getBytes(“UTF-8”),“utf-8”);
String zhuanye=new String(request.getParameter(“zhuanye1”).getBytes(“UTF-8”),“utf-8”);
try {
Class.forName(“com.mysql.cj.jdbc.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con=DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/jwdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8”,“root”,“123456”);
PreparedStatement stmt=con.prepareStatement(“update t_class set c_year=?,c_school=?,c_major=? where c_class =?”);
stmt.setString(4, banji2);
stmt.setString(1, xuenian);
stmt.setString(2, xueyuan);
stmt.setString(3, zhuanye);
int rtn=stmt.executeUpdate();
stmt.close();
con.close();
out.print(“修改成功!”);
%>
3)、添加功能:
以添加一个数据库中某一表为例,应用上述的两个对象从数据库t_class中添加c_class字段:
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8” import=“java.sql.*”%>
<%
String banji1=new String(request.getParameter(“banji1”).getBytes(“UTF-8”),“utf-8”);
String xuenian=new String(request.getParameter(“xuenian”).getBytes(“UTF-8”),“utf-8”);
String xueyuan=new String(request.getParameter(“xueyuan”).getBytes(“UTF-8”),“utf-8”);
String zhuanye=new String(request.getParameter(“zhuanye”).getBytes(“UTF-8”),“utf-8”);
try {
Class.forName(“com.mysql.cj.jdbc.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con=DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/jwdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8”,“root”,“123456”);
PreparedStatement stmt=con.prepareStatement(“insert into t_class (c_class,c_year,c_school,c_major)values(?,?,?,?)”);
stmt.setString(1, banji1);
stmt.setString(2, xuenian);
stmt.setString(3, xueyuan);
stmt.setString(4, zhuanye);
int rtn=stmt.executeUpdate();
stmt.close();
con.close();
out.print(“添加成功!”);
%>
4)、删除功能:
以删除一个数据库中某一表为例,应用上述的两个对象从数据库t_class中删除c_class字段:
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8” import=“java.sql.*”%>
<%
String banji3=new String(request.getParameter(“banji3”).getBytes(“UTF-8”),“utf-8”);
try {
Class.forName(“com.mysql.cj.jdbc.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con=DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/jwdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8”,“root”,“123456”);
PreparedStatement stmt=con.prepareStatement(“delete from t_class where c_class=?”);
stmt.setString(1, banji3);
int rtn=stmt.executeUpdate();
stmt.close();
con.close();
out.print(“删除成功!”);
%>