JDBC

来源：互联网发布：python黑客编程中文pdf 编辑：程序博客网时间：2024/05/16 17:36

一、概念

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" onsubmit="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.jsprequest.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" onsubmit="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" onsubmit="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");}}

0 0