Tomcat与JavaWeb 4.1 MySQL数据库与JDBC基础-CSDN博客

本文链接：https://blog.csdn.net/MustangJy/article/details/79703829

本系列博客以MySQL作为数据库服务器。MySQL是一个多用户、多线程的强壮的关系数据库服务器。对Unix和Windows平台，MySQL的官方网站提供了免费的安装软件。 MySQL官方网站关于mysql的安装教程网上比比皆是，就不另说了。

1. 访问数据库

按照网上的配置设置好数据库之后，访问数据库。

创建一个数据库BookDB，进入该数据库并在其中创建BOOKS表：

在BOOKS表中加入一些记录：

2. JDBC简介

JDBC是Java Database Connectivity的缩写，它是连接Java程序和数据库服务器的纽带。如图所示，JDBC的实现封装了与各种数据库服务器通信的细节。

Java程序通过JDBC API来访问数据库，有以下优点：

简化访问数据库的程序代码，无须涉及与数据库服务器通信的细节。
不依赖于任何数据库平台，同一个Java程序可以访问多种数据库服务器。

JDBC API主要位于java.sql包中，此外在javax.sql中也包含了一些提供高级特性的API。

如下图所示，JDBC的实现包括三部分。

JDBC驱动管理器：java.sql.DriverManager类，由sun公司实现，负责注册特定JDBC驱动器，以及根据特定驱动器建立与数据库的连接。
JDBC驱动器API：由sun公司制定，其中最主要的接口是java.sql.Driver接口。
JDBC驱动器：由数据库供应商或其他第三方工具提供商创建，也称为JDBC驱动程序。JDBC驱动器实现了JDBC驱动器API，负责与特定数据库连接，以及处理通信细节，它可以注册到JDBC驱动管理器中。SUN公司很明智地让数据库供应商或其他第三方工具提供商来创建JDBC驱动器，因为他们才最了解与特定数据库通信的细节，因而有能力对特定数据库的驱动器进行优化。

通过上图还可以看出，JDBC驱动器才是真正的连接Java应用与特定数据库的纽带，如果Java应用程序希望访问某种数据库，必须先获得相应的JDBC驱动器的类库，然后再把它注册到JDBC驱动管理器中。

JDBC驱动器可分为4类，将它们按照访问数据库的速度由快到慢进行排列：

第一类驱动器：完全由Java语言编写的类库。它直接按照特定数据库的协议，把请求送给数据库服务器。
第二类驱动器：完全由Java语言编写的类库，它用一种与具体数据库服务器无关的协议将请求发送给服务器的特定组件，再由该组件按照特定数据库协议对请求进行翻译，并且把翻译后的内容发送给数据库服务器。
第三类驱动器：由部分Java代码和部分本地代码组成，用于与数据库的客户端API通信。在使用这种驱动器时，不仅需要安装相关的Java类库，还要安装一些与平台相关的本地代码。
第四类驱动器：JDBC-ODBC驱动器。是微软公司为应用程序提供的访问任何一种数据库的标准API。这种驱动器是唯一由SUN公司实现的驱动器，属于JDK的一部分，在默认情况下，该驱动器已经在JDBC驱动管理器中注册了。但是由于它连接数据库的速度比较慢，现在已经不提倡使用它了。

大部分数据库供应商都为它们的数据库产品提供前面两类最快的驱动器。许多第三方工具提供商也开发了复合JDBC标准的驱动器产品，它们往往支持更多的数据库平台，具有很好的运行性能和可靠性。因此Java程序应该优先考虑使用最快的两种驱动器，如果某种数据库还不存在前面两类驱动器，可以把较慢的驱动器作为暂时的替代品。

Java应用程序必须通过JDBC驱动器来访问数据库，那它如何能与各种不同的JDBC驱动器通信呢？这要归功于java.sql.DriverManager类，它运用桥梁设计模式，成为连接Java应用程序和各种JDBC驱动器的桥梁。

如下图所示，应用程序只和JDBC API打交道，JDBC API依赖DriverManager类来管理JDBC驱动器。加入应用程序需要向一个MySQL数据库提交一条SQL语句，DriverManager类就会委派特定的MySQL的驱动器来执行这个任务；同理，假如应用程序需要向一个Oracle数据库提交一条SQL语句，DriverManager类就会委派特定的Oracle的驱动器来执行该任务。

当Java应用程序希望访问某种数据库时，要先向DriverManager类注册该数据库的驱动器类，这样DriverManager类就能委派该驱动器来执行由应用程序下达的操控数据库的各种任务了。

2.1 java.sql包中的接口和类

JDBC API主要位于java.sql包中，关键的接口和类包括以下几种：

Driver接口和DriverManager类：前者表示驱动器，后者表示驱动管理器。
Connection接口：表示数据库连接。
Statement接口：负责执行SQL语句。
PerparedStatement接口：负责执行预准备的SQL语句。
CallableStatement接口：负责执行SQL存储过程。
ResultSet接口：表示SQL查询语句返回的结果集。

1.Driver接口和DriverManager类

所有JDBC驱动器都必须实现Driver接口，JDBC驱动器由数据库厂商或第三方提供。在编写访问数据库的Java程序时，必须把特定数据库的JDBC驱动器的类库加入到classpath中。

DriverManager类用来建立和数据库的连接以及管理JDBC驱动器。DriverManager类的方法都是静态的，主要包括以下几种：

registerDriver(Driver driver):在DriverManager中注册JDBC驱动器。
getConnection(String url,String user,String pwd):建立和数据库的连接，并返回表示数据库连接的Connection对象。
setLoginTimeOut(int seconds):设定等待建立数据库连接的超时时间。
setLogWriter(PrintWriter out):设定输出JDBC日志的PrintWriter对象。

2.Connection接口

Connection接口代表Java程序和数据库的连接，主要包括以下方法：

getMetaData()：返回表示数据库的元数据的DatabaseMetaData对象。元数据包含了描述数据库的相关信息，如数据库中表的结构就是一种元数据。
createStatement()：创建并返回Statement对象。
prepareStatement(String sql)：创建并返回PreparedStatement对象。

3.Statement接口

该接口提供了三个执行SQL语句的方法：

execute(String sql)：执行各种SQL语句。该方法返回一个boolean类型的值，如果为true，表示所执行的SQL语句具有查询结果，可通过Statement的getResultSet()方法获得这一查询结果。
executeUpdate(String sql)：执行SQL的insert、update和delete语句。该方法返回一个int类型的值，表示数据库中受到该语句影响的记录的数目。
executeQuery(String sql)：执行SQL的select语句。该方法返回一个表示查询结果的ResultSet对象，如下所示：

String sql="select * from BOOKS where NAME='Tom' and PRICE=40";
ResultSet rs = executeQuery(sql);

4.PreparedStatement接口：

该接口继承了Statement接口，用来执行预准备的SQL语句。在访问数据库时，可能遇到某条SQL语句被多次执行，仅仅其中的参数不同的情况，例如：

select * from BOOKS where NAME='Tomcat' and PRICE=40
select * from BOOKS where NAME='Mike' and PRICE=50
select * from BOOKS where NAME='Jack' and PRICE=30

以上SQL语句的格式为：

select * from BOOKS where NAME=? and PRICE=?

在这种情况下，使用PreparedStatement来执行SQL语句，这样做，一方面可以简化程序代码，另一方面可以提高访问数据库的性能：PreparedStatement执行预准备的SQL语句，数据库只需对这种SQL语句编译一次，然后就可以多次执行，而当每次使用Statement对象来执行SQL语句时，数据库都需要对该SQL语句进行编译。

PreparedStatement的使用步骤如下：

1)通过Connection对象的prepareStatement()方法生成PreparedStatement对象。以下SQL语句中的NAME的值和PRICE的
值都用”？“代替，他们表示两个可被替换的参数:

String selectStatement = "select * from BOOKS where NAME = ? and PRICE = ?";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);

2)调用PreparedStatement的setXXX方法，给参数赋值。预准备SQL语句的中的第一个参数为String类型，因此调用色同
String方法，第二个参数是float类型所以调用setFloat方法。这些setXXX方法的第一个参数表示预准备SQL语句中的"?"的位
置，第二个参数表示替换这些问号的具体值：

prepstmt.setString(1,name);
prepstmt.setFloat(2,price);

3)执行SQL语句：

ResultSet rs = prepstmt.executeQuery();

5.ResultSet接口：

该接口表示select查询语句得到的结果集。调用ResultSet对象的next()方法，可以使游标定位到结果集中的下一条记录。调用ResultSet对象的getXXX()方法，可以获得一条记录中的某个字段的值。ResultSet接口提供了以下常用的getXXX()方法。

getString(int columnIndex)：返回指定字段的String类型的值，参数columnIndex代表字段的索引。
getString(String clomnName)：返回指定字段的String类型的值，参数columnName代表字段的名字。

类似的还有getInt(int columnIndex)、getInt(String clomnName)、getFloat(int columnIndex)、

getFloat(String columnName)方法。

ResultSet提供了这些方法，程序应该根据字段的数据类型来决定调用哪种get方法。此外，程序既可以通过字段的索引来指定字段，也可以通过字段的名字类指定。字段的索引从1开始编号。例如对于以下select查询语句，查询结果存放在rs变量中：

String sql="select * from BOOKS where NAME='Tom' and PRICE=40";
ResultSet rs = executeQuery(sql);

如果要访问ID字段，可以使用以下语句：

rs.getString(1);

或者：

rs.getString("ID");

如果要访问PRICE字段，可以使用下面的语句（PRICE字段的索引为4）：

rs.getFloat(4);
//或者
rs.getFloat("PRICE");

如果要取出ResultSet中的所有记录，可以采用下面的循环语句：

while (rs.next()){
    String col1 = rs.getString(1);
    String col2 = rs.getString(2);
    String col3 = rs.getString(3);
    float col4 = rs.getFloat(4);

    System.out.println("ID="+col1+"NAME="+col2+"TITLE="+col3+"PRICE="+col4);
}

2.2 编写访问数据库程序的步骤

在Java程序中，通过JDBC API访问数据库包含以下步骤。

1. 获得要访问的数据库的JDBC驱动器的类库，把它放到classpath中。

例如，MySQL的Java JDBC驱动：点击打开链接。安装后，解压并把对应的jar包加入到工程中，建立依赖，才能正常使用。

2. 在程序中加载并注册JDBC驱动器，其中JDBC-ODBC驱动器是在JDK中自带的，默认为已经注册。以下分别给出了加载并注
册SQLServer驱动器、Oracle驱动器和MySQL驱动器的代码：

        //加载SQLServerDriver类
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        //注册SQLServerDriver类
        java.sql.DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
        //加载OracleDriver类
        Class.forName("Oracle.jdbc.driver.OracleDriver");
        //注册OracleDriver类
        java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        //加载MySQL Driver类
        Class.forName("com.mysql.jdbc.Driver");
        //注册MySQL Driver类 
        java.sql.DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //不是必要步骤

注意：必须已经安装好对应的Driver的库到你的编译环境中，才能够正常地注册到JDBC驱动管理器中。

有些驱动器的Driver类在被加载的时候，能自动创建本身的实例，然后调用DriverManager.registerDriver()方法来注册自己，例如对于MySQL的驱动器类com.mysql.jdbc.Driver，当Java虚拟机加载这个类时，会执行它的如下静态代码块：

    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

所以在Java应用中，只需要通过Class.forname()方法来加载MySQL Driver类即可，而不需要手动注册该类。

3.建立与数据库的连接

        Connection connection = java.sql.DriverManager.getConnection(dburl,user,password);

在getConnection()方法中有三个参数，分别是连接数据库的JDBC URL、连接数据库的用户名和口令。

JDBC URL的一般形式为： jdbc:drivertype:driversubtype://parameters， drivertype表示驱动器的类型，driversubtype是可选参数，表示驱动器的子类。parameter通常用来设定数据库服务器的ip地址、端口号和数据库的名称。以下给出了一种常用的数据库的JDBC URL形式。

Oracle ： jdbc:oracle:thin@localhost:1521:sid
SQLServer : jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=BookDB

MySQL :　jdbc:mysql://localhost:3306/BookDB

4.创建Statement对象，准备执行SQL语句：

Statement stmt = connection.createStatement();

5.执行SQL语句：

String sql = "select * from BOOKS";
ResultSet rs = stmt.executeQuery(sql);

6.访问ResultSet中的记录集

while (rs.next()){
    String col1 = rs.getString(1);
    String col2 = rs.getString(2);
    String col3 = rs.getString(3);
    float col4 = rs.getFloat(4);

    System.out.println("ID="+col1+"NAME="+col2+"TITLE="+col3+"PRICE="+col4);
}

7.依次关闭ResultSet、Statement和Connection对象。

rs.close();
stmt.close();
connection.close();

下面演示一次完整的使用：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        //注册MySQL Driver类
        java.sql.DriverManager.registerDriver(new com.mysql.jdbc.Driver());;
        Connection connection = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/BookDB?useSSL=true","root","password");
        Statement statement = connection.createStatement();
        String sql = "select * from BOOKS";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            String col1 = resultSet.getString(1);
            String col2 = resultSet.getString(2);
            String col3 = resultSet.getString(3);
            float col4 = resultSet.getFloat(4);

            System.out.println("ID="+col1+",NAME="+col2+",TITLE="+col3+",PRICE="+col4);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

如果已经安装好了mysql的Driver，将上面的代码中建立Connection的用户名和密码换成自己的，就可以正常运行了。

2.3 事务处理

在数据库操作中，一项事务是指由一条或多条操控数据库的SQL语句锁组成的一个不可分割的工作单元。只有当事务中的所有操作都正常完成，整个事务才能被提交到数据库；如果有一项操作没有完成，就必须撤销整个事务。

例如在银行转账事务中，假定张三从自己的账号上把1000元转到李四的账号，相关的SQL语句如下：

update ACCOUNT set MONEY=MONEY-1000 where NAME='zhangsan';
update ACCOUNT set MONEY=MONEY+1000 where NAME='lisi';

这两条SQL语句必须作为一个完整的事务来处理，也就是说只有当两条都成功执行才能提交整个事务，否则整个数据库的数据就混乱了。

在Connection接口中提供了3个控制事务的方法。

setAutoCommit(boolean autoCommit)：设置是否自动提交事务。
commit():提交事务。
rollback()：撤销事务。

在JDBC API中，默认为自动提交事务。也就是说，每一条操纵数据库的SQL语句代表一个事务，如果操作成功，数据库系统将自动提交事务，否则就自动撤销事务。可以通过调用Connection接口的setAutoCommit(false)来禁止自动提交事务。然后就可以把多条操作数据库的SQL语句作为一个事务，在所有操作完成后，调用Connection接口的commit()进行整体提交。倘若其中一项SQL操作失败，就会产生相应的SQLException，此时就可以在捕获异常的代码块中调用Connection接口中的rollback()方法来撤销事务。

通过JDBC访问数据库的JSP范例：

以下是一个JSP访问数据库的范例程序。在这个程序中建立了和数据库的连接，并向BOOKS表中加入了一条记录，然后将BOOS中的所有记录以表格的方式显示出来，最后删除新加的记录。

dbacess.jsp

<%@page import="java.io.*" %>
<%@page import="java.util.*" %>
<%@page import="java.sql.*" %>
<%@page contentType="text/html;charset=utf-8" %>

<html>
<head>
    <title>dbaccess.jsp</title>
</head>
<body>
<%  
    try {
        Connection connection;
        Statement statement;
        ResultSet resultSet;
        Class.forName("com.mysql.jdbc.Driver");
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        String dburl  = "jdbc:mysql://localhost:3306/BookDB?useUnicode=true&characterEncoding=utf-8&useSSL=true";
        String dbusername = "root";
        String dbpassword = "qAz1131035030";
        connection = DriverManager.getConnection(dburl,dbusername,dbpassword);
        statement = connection.createStatement();
        statement.executeUpdate("INSERT INTO BOOKS (ID,NAME,TITLE,PRICE) VALUES ('999','Tom','Tomcat Bible',44.5)");
        resultSet = statement.executeQuery("SELECT ID,NAME,TITLE,PRICE FROM BOOKS");
        out.println("<table border=1 width=400>");
        while (resultSet.next()) {
            String col1 = resultSet.getString(1);
            String col2 = resultSet.getString(2);
            String col3 = resultSet.getString(3);
            float col4 = resultSet.getFloat(4);

            out.println("<tr><td>"+col1+"<tr><td>"+col2+"</td><td>"+col3+"</td><td>"+col4+"</td></tr>");
        }
        out.println("</table>");

        statement.executeUpdate("DELETE FROM BOOKS WHERE ID='999'");
        resultSet.close();
        statement.close();
        connection.close();

        }catch (Exception e){out.println(e.getMessage());}
%>
</body>
</html>

如果上述程序运行后有乱码部分，这是因为编码格式的设置不正确。为了方便，我们需要设置自己的MySQL的编码格式为utf-8，修改方法：修改MySQL编码。修改之后：

因此我们可以很方便地在程序与MySQL交互中使用中文，因为utf-8编码是支持中文的。只需要把JSP网页内容编码格式和与MySQL连接时设置的编码格式均写为utf-8就可以了。