18.数据库操作

18.数据库操作

数据库系统是由数据库、数据库管理系统和应用系统、数据库管理员构成。

数据库管理系统简称DBMS，是数据库系统的关键组成部分，包括数据库定义、数据查询、数据维护等。

而JDBC技术是连接数据库和应用程序的纽带。

18.1数据库基础知识

如今现在常用的高级数据库有SQL Server、MySQL、Oracle等。

18.1.1什么是数据库

数据库是一种存储接口，允许使用各种格式输入、处理和检索数据。主要特点如下：

1.数据共享。所有用户可同时存取数据库中数据。

2.减少数据冗余度。相比文件系统，避免用户格子间里应用文件。

3.数据独立性。数据库逻辑结构和应用程序相互独立，物理结构变化不影响数据的逻辑结构。

4.数据集中控制。利用数据库可以对数据集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。

5.数据的一致性和可维护性。（安全性控制、完整性控制、并发控制、故障的发现和恢复。

数据库的基本结构分为三个层次：

1.物理数据层：数据库最内层，物理存储设备上实际存储的数据集合。

2.概念数据层：数据库中间层，数据库的整体逻辑表示，指出了每个数据的逻辑定义及数据间的逻辑联系，存储记录的集合。

3.逻辑数据层：用户所看到和使用的数据库，一个或一些特定用户使用的数据集合，即逻辑记录的集合。

18.1.2数据库的种类及功能

数据库系统一般基于某种数据模型，可以分为层次型、网状型、关系型及面向对象型，目前最流行的是关系型。

以MySQL为例，MySQL数据库是开发源代码的软件，具有功能性墙、使用简便、管理方便、运行速度快、安全可靠性强等优点，MySQL是具有客户机/服务器体系结构的分布式数据库管理系统，也是一个完全网络化的跨平台关系型数据库系统。

MySQL在UNIX/Linux系统上以多线程方式运行，在Windows NT上以系统服务运行，在Windows95/98上以普通进程方式运行。

从JDK6开始，JDK的安装目录中新增加了一个db的目录，即Java DB。这是要给纯Java实现的、开源的数据库关系系统（DBMS），源于Apache软件基金会（ASF）名下的项目Derby。

18.1.3SQL语言

SQL(Structure Query Language,结构化查询语言)被广泛地应用于大多数数据库中，依赖于关系模型。SQL语言主要由以下几部分组成：

1.数据定义语言（Data Definition Language,DDL)如create、alter、drop等。

2.数据操纵语言（Data Manipulation Language,DML)如select\insert\update\delete等。

3.数据控制语言（Data Control Language,DCL),如grant、revoke等。

4.事务控制语言（Transaction Control Language),如commit、rollback等。

其中DDL是最常用的SQL语言，介绍如下：

1.select语句

SELECT 所选字段列表 FROM 数据表名
WHERE 条件表达式 GROUP BY 字段名 HAVING 条件表达式(指定分组的条件)
ORDER BY 字段[ASC|DESC]

2.insert语句

insert into 表名[(字段名1,字段名2...)]
values(属性值1，属性值2...)

3.update语句

UPDATE 数据表名 SET 字段名=新的字段值 WHERE 条件表达式

4.delete语句

delete from 数据表名 where 条件表达式

18.2JDBC概述

JDBC是用来执行SQL语句的API（Application Programming Interface,应用程序设计接口）,是连接数据库和java应用程序的纽带。

18.2.1JDBC-ODBC桥

JDBC-ODBC桥是一个JDBC驱动程序。完成了从JDBC操作到ODBC操作之间的转换工作，允许JDBC驱动程序用作ODBC的驱动程序，使用JDBC-ODBC桥连接数据库的步骤如下：

（1）首先加载JDBC-ODBC桥的驱动程序，代码如下：

Class.forName("sum.jdbc.JdbcOdbcDriver");

Class类是java.lang包中的一个类，通过该类的静态方法forName()可加载sun.jdbc.odbc包中的JdbcOdbcDriver类来建立JDBC-ODBC桥连接器。

（2）使用java.sql包中的Connection接口，并通过DriverManager类的静态方法getConnection()创建连接对象。代码如下：

Connection conn=DriverManager.getConnection("jdbc:odbc:数据源名字","user name","password");

数据源必须给出一个简短的描述名。加入没有设置user name和password，则要与数据源tom交换数据。建立Connection对象的代码如下：

Connection conn=DriverManager.getConnection("jdbc.odbc:tom",""."");

(3)向数据库发送SQL语句。使用Statement接口声明一个SQL语句对象，并通过刚才创建的链接数据库对象的conn的createStatement()方法创建这个SQL对象，代码如下：

Statement sql=conn.createStatement();

JDBC-ODBC桥作为链接数据库的过渡性技术，现在已经不再流行，但由于ODBC技术被广泛使用，使得Java可以利用JDBC-ODBC桥访问几乎所有的数据库。

JDBC-ODBC桥作为sun.jdbc.odbc包与JDK一起自动安装，无需额外配置。

18.2.2JDBC技术

JDBC全称是Java DataBase Connectivity,指定了同一的访问各种关系数据库的标准接口。

JDBC是一种底层的API,因此访问数据库时需要在业务逻辑层中镶入SQL语句。

JDBC不能直接访问数据库，必须依赖于数据库厂商提供的JDBC驱动程序。

JDBC总体结构由4个组件组成：应用程序、驱动程序管理器、驱动程序、数据源。

JDBC技术主要用来完成以下几个任务：

(1)与数据库建立一个连接。

(2)向数据库发送SQL语句。

(3)处理从数据库返回的结果。

18.2.3JDBC驱动程序的类型

JDBC驱动程序基本上分为4种：JDBC-ODBC桥、本地API、JDBC网络驱动、本地协议驱动。

其中JDBC网络驱动和本地协议驱动时JDBC访问数据库的首选。

18.3JDBC中常用的类和接口

java.sql包中定义了常用的访问和处理数据库的类。

18.3.1Connection接口

Connection接口代表与特定的数据库连接，在连接上下文中执行SQL语句并返回结果。

方法	功能描述
createStatement()	创建Statement对象
createStatement(int resultSetType,int resultSetConcurrency)	创建一个statement对象，该对象将生成具有给定类型、并发性和可保存性的ResultSet对象
preparedStatement()	创建预处理对象preparedStatement
isReadonly()	查看当前Conncetion对象的读取模式是否为只读形式
setReadOnly()	设置当前Connection对象的读写模式，默认是非只读模式
commit()	使所有上一次提交/回滚后进行的更改为持久更改，并释放此Conncetion对象当前持有的所有数据库锁
roolback()	取消在当前事务中进行的所有更改，并释放此Connection对象当前持有的所有数据库锁
close()	立即释放此Connection对象的数据库和JDBC资源，而不是等待他们被自动释放

18.3.2Statement接口

Statement接口用于在已经建立连接的基础上向数据库发送SQL语句。在JDBC中由3种Statement对象，分别是：

1.Statement对象，用于执行不带参数的简单SQL语句；

2.PrepareStatement对象，继承自Statement对象，用来执行动态SQL语句；

3.CallableStatement对象，继承自PrepareStatement对象，用于执行对数据库的存储过程的调用。

Statement接口常用方法如下所示：

方法	功能描述
execute(string sql)	执行静态的SELECT语句，该语句可能就返回多个结果集
exceteQuery(String sql)	执行给定的SQL语句，该语句返回单个ResultSet对象
clearBatch()	清空此Statment对象的当地球SQL命令列表
excuteBatch()	将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。数组元素的排序与SQL语句的添加顺序对应
addBatch(String sql)	将给定的SQL命令添加到此statement对象的当前命令列表中。如果驱动程序不支持批量处理，将抛出异常
close()	释放Statement实例占用的数据库和JDBC资源

18.3.3PreparedStatement接口

PreparedStatement接口用来动态地执行SQL语句。通过PrepareStatement实例执行的动态SQL语句，将被预编译并保存到PrepareStatement实例中，从而可以反复地执行该SQL语句。

PreparedStatement接口常用方法如下所示：

方法	功能描述
setInt(int index ,int k)	将指定位置的参数设置为对应的int值
setFloat(int index,float f)	将指定位置的参数设置为对应的float值
setLong(int index,ling l)	将指定位置的参数设置为对应的long值
setDouble(int index,double d)	将指定位置的参数设置为对应的double值
setBoolean(int index,boolean b)	将指定位置的参数设置为对应的boolean值
setDate(int index,date date)	将指定位置的参数设置为对应的date值
executeQuery()	再次PreparedStatement对象中执行SQL查询，并返回该查询生成的ResultSete对象
setString(int inex,String s)	将指定位置的参数设置为对应的String值
setNull(int index,int sqlType)	将制定位置的参数设置为SQL NULL
executeUpdate()	执行前面包含的参数的动态INSET、UPDATE或DELETE语句
clearParameters()	清除当前所有参数值

18.3.4DriverManager类

DriverManager类用来管理数据库中所有驱动程序，可以跟踪可以用的驱动程序，并在数据库的驱动程序之间建立连接。DriverManager类常用方法如下所示：

方法	功能描述
getConnection(String url,String user,String password)	指定三个入口参数（URL、用户名、密码）来获取与数据库的连接，不能连接则抛出SQLException异常
setLooginTimeout()	获取驱动程序视图登陆到某一数据库时可以等待的最长时间，单位为秒
println(Stirng message)	将一条消息打印到当前JDBC日志流中

18.3.5ResultSet接口

ResultSet接口类似于一个临时表，用来暂时存放数据库查询操作获得的结果集。ResultSet实例具有指向当前数据行的指针，指针开始的位置在第一条记录的前面，通过next()方法可以将指针向下移。

在JDK1.2之后，该接口添加了一组更新方法updateXXX()，该方法有两个重载方法，可以根据列的索引号和列的名称来更新指定列。但如果要将更新同步到数据库中，还需要执行updateRow()或insertRow()方法。

ResultSet接口常用方法如下所示：

方法	功能描述
getInt()	以int形式获取此ResultSet对象的当前行的指定列值，如果列值是NULL,则返回值是0
getFloat()	以Folat形式获取此ResultSet对象的当前行的指定列值，若列值是NULL,则返回值是0
getDate()	以date形式获取ResultSet对象的当前行的指定列值，如果列值是NULL,则返回null
getBoolean()	以boolean形式获取ResultSet对象的当前行的指定列值，如果列值是NULL,则返回null
getString()	以String形式获取ResultSet对象的当前行的指定列值，如果列值是NULL,则返回null
getObject()	以Object形式获取ResultSet对象的当前行的指定列值，如果列值是NULL,则返回null
first()	将指针移到当前记录的第一行
last()	将指针移到当前记录的最后一行
next()	将指针移向下一行
beforeFirst()	将指针移动到集合的开头（第一行位置)
afterLast()	将指针移动到集合的尾部（最后一行位置）
absolute(int index)	将指针移到ResultSet给定编号的行
isFirst()	判断指针是否位于当前ResultSet集合的第一行。返回boolean值
isLast()	判断指针是否位于当前ResultSet集合的最后一行。返回boolean值
updateInt()	用指定的int值更新指定列
updateFloat()	用指定的float值更新指定列
updateLong()	用指定的long值更新指定列
updateString()	用指定的String值更新指定列
updateObject()	用指定的Object值更新指定列
updateNull()	将指定的列值修改为NULL
updateDate()	用指定的date值更新指定列
updateDouble()	用指定的double值更新指定列
getRow()	查看当前行的索引号
insertRow()	将查日航的内容更新到数据库
updateRow()	将堂前行的内容同步到数据库
deleteRow()	删除当前行，但不同步到数据库，在执行close()方法后同步到数据库

18.4数据库操作

实操中遇到的的问题及解决方法：

1.IntelliJ IDEA导入mysql-connector-java-5.1.44-bin.jar
2.SSL连接警告

3.提示mysql时区错误

18.4.1连接数据库

访问数据库前需要先加载数据库驱动程序（加载一次即可），而后每次访问数据时创建一个Connection对象。在完成数据库操作后，再摧毁前面创建的Connection对象，释放与数据库的连接。

以下是一个连接数据库程序实例：

public class Conn {
    Connection con;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //forName()方法用来加载JDBC驱动程序
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            //加载驱动失败会抛出ClassFoundException异常
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/qiang_db?useSSL=false",
                    "root", "123");
            //java.sql包中的DriverManager类静态方法getConnection()用于建立数据库连接
            //getConnection(String url,String user,String password)
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            //连接失败抛出SQLException异常
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Conn c = new Conn();
        c.getConnection();

    }
}

运行结果：

18.4.2向数据库发送SQL语句

getConnection()只是获取了与数据库的连接，要想执行SQL语句还需要statement对象，通过Conncetion对象的createStatement()方法获取。举例如下:

try{
    Statement sql=con.createStatement();
    //con为上例中创建的Connection对象
}catch(SQLException e){
    e.printStackTrace();
}

18.4.3处理查询结果集

有了statement对象就可以调用相应的方法实现对数据库的查询和修改，并将查询结果存放在ResultSet类对象中。获取查询结果集代码如下：

ResultSet res=sql.executeQuery("select*from student");

运行结果返回一个ResultSet对象，该对象一次只可以看到结果集中的一行数据，使用该类的next()方法可以将光标从当前位置移向下一行。

18.4.4顺序查询

ResultSet类的next()方法返回值是boolean类型的数据，当光标移动到最后一行将返回false。以下是一个顺序查询的实例。

import java.sql.*;

public class Gradation {
    static Connection con;
    static Statement sql;
    static ResultSet res;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/qiang_db?useSSL=false"
                    , "root", "123");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Gradation gra = new Gradation();
        gra.getConnection();
        try {
            sql = con.createStatement();
            res = sql.executeQuery("select*from students");
            while (res.next()) {
                String id = res.getString("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
                String age = res.getString("age");
                System.out.println("编号:" + id);
                System.out.println("姓名" + name);
                System.out.println("性别" + sex);
                System.out.println("年龄" + age);
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

“id”在第一列，其中getString(“id”); 等价于 getString(1);

18.4.5模糊查询

SQL与剧中提供了LIKE操作符用于模糊查询，可以使用”%“来代替0个或者多个字符，”_”代表一个字符，例如需要查询姓名为王的同学的所有信息时，可以使用如下的SQL语句：

select*from students where name like"张%"；

举例如下：

public class Gradation {
    static Connection con;
    static Statement sql;
    static ResultSet res;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/qiang_db?useSSL=false"
                    , "root", "123");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Gradation gra = new Gradation();
        gra.getConnection();
        try {
            sql = con.createStatement();
            res = sql.executeQuery("select*from students where name like '王%'");
            //对于单一精确的对象，可以使用'='替代like，如‘select*from students where name='王刚'
            while (res.next()) {
                String id = res.getString("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
                String age = res.getString("age");
                System.out.print("编号:" + id);
                System.out.print(" 姓名" + name);
                System.out.print(" 性别" + sex);
                System.out.println(" 年龄" + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

18.4.6预处理语句

SQL解释器负责把SQL语句生成底层的内部命令，当提交大量SQL语句时，会增加SQL解释器负担，影响执行速度。

Connection对象的preparedStatement(String sql)方法对SQL语句进行预处理，生成数据库底层内部命令,从而减轻数据库负担，提高访问数据库速度。

对SQL预处理时可以使用通配符”？“来代替任何字段的值。

sql=con.prepareStatement("select*from students where id=?");

在执行预处理语句前，需要使用相应方法设置通配符所表示的值，例如：

sql.setInt(1,2);
//"1"表示第一个通配符，2表示设置的通配符的值

再通过setXXX()方法为SQL语句中参数赋值时，建议利用与参数匹配的方法，也可以利用setObject()方法为各种类型的参数赋值，如：

import java.sql.*;

public class Prep {
    static Connection con;
    static PreparedStatement sql;
    static ResultSet res;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/qiang_db?useSSL=false"
                    , "root", "123");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Prep pre = new Prep();
        pre.getConnection();
        try {
            sql = con.prepareStatement("select*from students where name=?");
            sql.setString(1,"王刚");
            res = sql.executeQuery();
            while (res.next()) {
                String id = res.getString("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
        String age = res.getString("age");
        System.out.println("编号:" + id+"\t姓名:" + name+"\t性别:" + sex+"\t年龄:" + age);
    }
} catch (SQLException e) {
        e.printStackTrace();
        }

    }
}

18.4.7添加、修改、删除记录

指定PreparedStatement类的参数可以动态地对数据表中原有数据进行修改，并通过executeUpdate()方法执行更新语句操作。

executeQuery()方法在PreparedStatement对象中执行SQL查询，并返回查询生成的ResultSet对象。

executeUpdate()方法在PreparedStatement对象中执行SQl语句，该语句需要是一个DDL语句（insert\update\delete）或者无返回内容的SQL语句（如DDL）；

import java.sql.*;

public class Prep {
    static Connection con;
    static PreparedStatement sql;
    static ResultSet res;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/qiang_db?useSSL=false"
                    , "root", "123");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Prep pre = new Prep();
        pre.getConnection();
        try {
            //输出原始数据
            sql = con.prepareStatement("select*from students");
            res = sql.executeQuery();
            System.out.println("初始数据为:");
            while (res.next()) {
                String id = res.getString("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
                String age = res.getString("age");
                System.out.println("编号:" + id + "\t姓名:" + name + "\t性别:" + sex + "\t年龄:" + age);
            }
            //添加数据
            sql = con.prepareStatement("insert into students values(?,?,?,?)");
            sql.setInt(1, 6);
            sql.setString(2, "吴芳");
            sql.setString(3, "女");
            sql.setInt(4, 20);
            sql.executeUpdate();
            //更新数据
            sql = con.prepareStatement("update students set age=? " +
                    "where id =5");
            sql.setInt(1, 3);
            sql.executeUpdate();
            //删除数据
            sql.executeUpdate("delete from students where id= 1");
            //查看修改后的数据
            sql = con.prepareStatement("select*from students");
            res = sql.executeQuery();
            System.out.println("更新后数据为:");
            while (res.next()) {
                String id = res.getString("id");
                String name = res.getString("name");
                String sex = res.getString("sex");
                String age = res.getString("age");
                System.out.println("编号:" + id + "\t姓名:" + name + "\t性别:" + sex + "\t年龄:" + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}