JDBC

最新推荐文章于 2023-10-16 00:03:57 发布

砺晗

最新推荐文章于 2023-10-16 00:03:57 发布

阅读量334

点赞数

分类专栏： Java web 文章标签： JDBC概念 JDBC插入 JDBC删除 JDBC查询 JDBC修改

本文链接：https://blog.csdn.net/xiaowang627/article/details/54890249

版权

Java web 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、概念

JDBC是Java程序操作数据接口的API，是Java程序与数据库相互交互的一门技术，是Java操作数据库的规范，由一组用Java语言编写的类和接口组成，它对数据库的操作提供了基本方法，但对于数据库的细节操作由数据库厂商进行实现，使用JDBC操作数据库，需要数据库厂商提供数据库的驱动程序。

二、JDBC连接数据库的过程

2.1 注册数据库驱动：通常通过将数据库驱动加在到JVM来实现

Class.forName("com.mysql.jdbc.Drive");

2.2 构建数据库连接URL：JDBC协议+IP地址或域名+端口+数据库名称

如MySQL数据库连接URL：jdbc:mysql://localhost:3306/test

2.3 获取Connection对象：JDBC封装的数据库连接对象，只有创建此对象后才可以对数据进行相关操作。

DriverManager.getConnection(url,username,password);

例如：

<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.net.URLDecoder"%>
<%@page import="java.sql.Connection" %>
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title> 数据库连接</title>
</head>
<body>
连接结果如下：
<%
try{
	Class.forName("com.mysql.jdbc.Driver");
	String url="jdbc:mysql://127.0.0.1/student";
	String username="root";
	String password="root";
	Connection conn=DriverManager.getConnection(url,username,password);
	if(conn!=null){
		out.println("数据库连接成功！");
		conn.close();
	}
	else{
		out.println("数据库连接失败！");
	}
}catch(ClassNotFoundException e1){
	e1.printStackTrace();
}catch(SQLException e2){
	e2.printStackTrace();
}
%>
</body>
</html>

注意：MySQL驱动程序导入项目后在web项目中不能被炸到，将其直接复制到Tomcat目录下的lib文件中，正常运行。

三、JDBC API

3.1 Connection接口

3.2 DriverManager类

3.3 Statement接口

3.4 PreparedStatement接口

继承与Statement接口实际开发中，如果涉及向SQL语句传递参数，最好使用PreparedStatement接口实现。它不仅可以提高SQL的执行效率，而且还可以避免SQL语句注入式攻击。

3.5 ResultSet接口

封装数据查询结果集。

四、JDBC操作数据库

4.1 添加数据

参数用？代替，通过PreparedStatement对其赋值并执行SQL语句。

数据表如下：

create table tb_books(
	id integer primary key not null auto_increment,
    name varchar(45) not null,
    price double not null,
    bookCount integer not null,
    author varchar(45) not null
);

JavaBean如下：

package bean;

public class Book {
	private int id;
	private String name;
	private double price;
	private int bookCount;
	private String author;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public int getBookCount() {
		return bookCount;
	}
	public void setBookCount(int bookCount) {
		this.bookCount = bookCount;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
}

index.jsp

<%@ 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>图书管理系统</title>
</head>
<body>
<form action="AddBook.jsp" method="post" οnsubmit="return check(this);">
	<table align="center" width=450>
		<tr>
			<td align="center" colspan="2">
				<h2>添加图书信息</h2>
				<hr>
			</td>
		</tr>
		<tr>
			<td align="right">图书名称：</td>
			<td><input type="text" name="name" /></td>
		</tr>
		<tr>
			<td align="right">价  格：</td>
			<td><input type="text" name="price" /></td>
		</tr>
		<tr>
			<td align="right">数  量：</td>
			<td><input type="text" name="bookCount" /></td>
		</tr>
		<tr>
			<td align="right">作  者：</td>
			<td><input type="text" name="author" /></td>
		</tr>
		<tr>
			<td align="center" colspan="2">
				<input type="submit" value="添加">
			</td>
		</tr>
	</table>
</form>
</body>
</html>

AddBook.jsp

<%@page import="java.sql.SQLException"%>
<%@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 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>添加图书</title>
</head>
<body>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="book" class="bean.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"/>
<%
	try{
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://127.0.0.1:3306/bookstore";
		String username="root";
		String password="root";
		Connection conn=DriverManager.getConnection(url,username,password);
		String sql="insert into tb_books(name,price,bookCount,author) values(?,?,?,?)";
		
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, book.getName());
		ps.setDouble(2, book.getPrice());
		ps.setInt(3, book.getBookCount());
		ps.setString(4, book.getAuthor());
		int row=ps.executeUpdate();
		if(row>0){
			out.print("成功添加了"+row+"条数据！");
		}
		ps.close();
		conn.close();
	}catch(ClassNotFoundException e1){
		out.print("ClassNotFoundException！");
		e1.printStackTrace();
	}catch(SQLException e2){
		out.print("SQLException！");
		e2.printStackTrace();
	}

%>
<br>
<a href="index.jsp">返回</a>
</body>
</html>

说明1：<jsp:setProperty>标签的property属性的值设置为"*"，它的作用是将与表单中同名称的属性值赋值给JavaBean对象中的同名属性，使用这种方式，就不必对JavaBean中的属性一一进行赋值。

说明2：使用PreparedStatement对象对SQL语句的占位符参数赋值，其参数的下表不是从0开始，而是从1开始。

4.2 查询数据

创建FindServlet的Servlet对象，用于查询所有图书信息，重写doGet()方法，建立数据库连接，并将所查询的数据集合放置到HttpServletRequest对象中，将请求转发到JSP页面：

package servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import bean.Book;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Servlet implementation class FindServlet
 */
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public FindServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://127.0.0.1:3306/bookstore";
			String username="root";
			String password="root";
			Connection conn=DriverManager.getConnection(url,username,password);
			Statement stmt=conn.createStatement();
			String sql="select * from tb_books";
			ResultSet rs=stmt.executeQuery(sql);
			List<Book> list=new ArrayList<Book>();
			while(rs.next()){
				Book book=new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getDouble("price"));
				book.setBookCount(rs.getInt("bookCount"));
				book.setAuthor(rs.getString("author"));
				list.add(book);
			}
			request.setAttribute("list", list);
			rs.close();
			stmt.close();
			conn.close();
		}catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}catch (SQLException e2) {
			e2.printStackTrace();
		}
		//请求转发到book_list.jsp
		request.getRequestDispatcher("book_list.jsp").forward(request, response);
	}

}

注意：ResultSet集合中第一行数据之前与最后一行数据之后都存在一个位置，默认情况下光标位于第一行数据之前——while(rs.next())

创建book_list.jsp页面，用于显示所有图书信息：

<%@page import="java.util.List" %>
<%@page import="bean.Book" %>
<%@ 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>查询结果</title>
</head>
<body>
<table align="center" width="450" border="1">
	<tr>
		<td align="center" colspan="5">
			<h2>所有图书信息</h2>
		</td>
	</tr>
	<tr align="center">
		<td><b>ID</b></td>
		<td><b>图书名称</b></td>
		<td><b>价格</b></td>
		<td><b>数量</b></td>
		<td><b>作者</b></td>
	</tr>
	<%
		List<Book> list=(List<Book>)request.getAttribute("list");
		if(list==null || list.size()<1){
			out.print("没有数据！");
		}
		else{
			for(Book book:list){
	%>
		<tr align="center">
			<td><%=book.getId() %></td>
			<td><%=book.getName() %></td>
			<td><%=book.getPrice() %></td>
			<td><%=book.getBookCount() %></td>
			<td><%=book.getAuthor() %></td>
		</tr>
	<%
			}
		}
	%>
</table>
</body>
</html>

4.3 修改数据

在book_list.jsp中增加修改图书数量的表单：

<%@page import="java.util.List" %>
<%@page import="bean.Book" %>
<%@ 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>查询结果</title>
</head>
<body>
<table align="center" width="450" border="1">
	<tr>
		<td align="center" colspan="5">
			<h2>所有图书信息</h2>
		</td>
	</tr>
	<tr align="center">
		<td><b>ID</b></td>
		<td><b>图书名称</b></td>
		<td><b>价格</b></td>
		<td><b>数量</b></td>
		<td><b>作者</b></td>
		<td><b>修改数量</b></td>
	</tr>
	<%
		List<Book> list=(List<Book>)request.getAttribute("list");
		if(list==null || list.size()<1){
			out.print("没有数据！");
		}
		else{
			for(Book book:list){
	%>
		<tr align="center">
			<td><%=book.getId() %></td>
			<td><%=book.getName() %></td>
			<td><%=book.getPrice() %></td>
			<td><%=book.getBookCount() %></td>
			<td><%=book.getAuthor() %></td>
			<td>
				<form action="UpdateServlet" method="post" οnsubmit="return check(this);">
					<input type="hidden" name="id" value="<%=book.getId() %>">
					<input type="text" name="bookCount" size="3">
					<input type="submit" value="修改">  
				</form>
			</td>
		</tr>
	<%
			}
		}
	%>
</table>
</body>
</html>

UpdateSerrlet:

package servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class UpdateServlet
 */
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UpdateServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int id=Integer.valueOf(request.getParameter("id"));
		int bookCount=Integer.valueOf(request.getParameter("bookCount"));
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://127.0.0.1:3306/bookstore";
			String username="root";
			String password="root";
			Connection conn=DriverManager.getConnection(url,username,password);
			String sql="update tb_books set bookCount=? where id=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, bookCount);
			ps.setInt(2, id);
			ps.executeUpdate();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		response.sendRedirect("FindServlet");
	}

}

4.4 删除数据

修改book_list.jsp:

<%@page import="java.util.List" %>
<%@page import="bean.Book" %>
<%@ 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>查询结果</title>
</head>
<body>
<table align="center" width="450" border="1">
	<tr>
		<td align="center" colspan="5">
			<h2>所有图书信息</h2>
		</td>
	</tr>
	<tr align="center">
		<td><b>ID</b></td>
		<td><b>图书名称</b></td>
		<td><b>价格</b></td>
		<td><b>数量</b></td>
		<td><b>作者</b></td>
		<td><b>修改数量</b></td>
		<td><b>删除</b></td>
	</tr>
	<%
		List<Book> list=(List<Book>)request.getAttribute("list");
		if(list==null || list.size()<1){
			out.print("没有数据！");
		}
		else{
			for(Book book:list){
	%>
		<tr align="center">
			<td><%=book.getId() %></td>
			<td><%=book.getName() %></td>
			<td><%=book.getPrice() %></td>
			<td><%=book.getBookCount() %></td>
			<td><%=book.getAuthor() %></td>
			<td>
				<form action="UpdateServlet" method="post" οnsubmit="return check(this);">
					<input type="hidden" name="id" value="<%=book.getId() %>">
					<input type="text" name="bookCount" size="3">
					<input type="submit" value="修改">  
				</form>
			</td>
			<td>
				<a href="DeleteServlet?id=<%=book.getId() %>">删除</a>
			</td>
		</tr>
	<%
			}
		}
	%>
</table>
</body>
</html>

DeleteServlet:

package servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class DeleteServlet
 */
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeleteServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int id=Integer.valueOf(request.getParameter("id"));
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://127.0.0.1:3306/bookstore";
			String username="root";
			String password="root";
			Connection conn=DriverManager.getConnection(url,username,password);
			String sql="delete from tb_books where id=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			conn.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
		response.sendRedirect("FindServlet");
	}

}