Java Database Operation

Database Operation （数据库操作）

Database Overview

数据库是以某种文件结构存储的一些列信息表

可以通过某些语句访问这些表、选择表中的行或者列、对表进行排序、以及根据各种条件对表中的内容进行查询

每一个表中都存储了对一类对象的数据描述，其中每一行是对一个对象的具体描述，每一列描述了对象的一个属性(field 或 column)

SQL 语言

SQL (Structure Query Language，结构化查询语言) 是操纵数据库的标准语言。其语法非常简单，但是功能强大，可以进行复杂的数据库操作

SQL 语言是一个标准，没有任何两个数据库管理系统的实现完全相同。每种数据库的 SQL 实现都有自己的特性与扩展功能。

非过程化语言-命令式语言，将要求交给系统，自动完成全部工作，一次处理的是一个记录集合

SQL 语言的特点

1. 统一语言，既是自含式语言又是嵌入式语言

• 可以独立地用于联机交互的使用方式，用户可以在终端上直接输入 SQL 命令对数据库进行操作
• SQL 可以嵌入许多高级语言开发工具中，供程序员设计程序时使用

2. 关系数据库的公共语言

• 目前流行的数据库产品，几乎全部支持 SQL 语言
• 大多数数据库的存取语言和标准接口
• 使不同的数据库之间有了相互操作的基础

SQL 的命令

SQL 功能	命令动词
数据查询	Select
插入记录	Insert
更新记录	Update
删除记录	Delete
定义基本表或索引	Create
删除基本表或索引	Drop
修改表结构	Alter
授权	Grant
收回权限	Revoke

• Create

定义基本表，格式：CREATE <表名> (<列名><数据类型>, <表名><数据类型>,...)

比如我们创建一个学生表：

CREATE TABLE Student(StuNum CHAR(5) NOT NULL UNIQUE, StuName CHAR(20), StuSex, CHAR(1), StuAge INT, StuDept CHAR(15));

• Select

查询，取出特定记录，格式：SELECT[DISTINCT|ALL]<目标列表达式>[.<目标列表达式>...]FROM<表名>[,<表名>,...][WHERE<条件表达式>][GROUP BY <列名1>][HAVING<条件表达式>][ORDER BY <列名2>[ASC|DESC]]

比如查询全体学生的学号与姓名：

SELECT StuNum,StuName FROM Student

查询计算机系年龄大于 20 岁的学生的姓名、出生年月

SELECT StuName,'Year of Birth:', 2022-StuAge From Student WHERE StuAge > 20 AND Studept = 'CS'

查询结果：

Sname   Year of Birth:    2022-Sage  
李勇      Year of Birth:    2002
刘晨      Year of Birth:    2001

• Insert

添加一条新纪录，格式：INSERT INTO<表名>[(<属性列1>)][,<属性列2>] VALUES(<常量1>[,<常量2>]...)

例如将一个新学生记录（学号：202120；姓名：陈冬；性别：男；所在系：CS;年龄：19岁）插入到 Student 中

INSERT INTO Student VALUES(‘202120’,’陈冬’，‘男’,’CS’,19)

• Update

数据更新，改变特定记录的值，格式：UPDATE<表明> SET<列名>=<表达式>[,<列名>=[表达式]]...[WHERE<条件>];

例如将学生 202120 的年龄加 1

UPDATE Student SET StuAge=StuAge+1 WHERE StuNum=202120

• Dlelete

删除特定的记录，格式：DELETE FROM <表名> WHERE <条件>

例如，删除计算机系所有学生的选课记录

DELETE FROM SC WHERE ‘CS’=(SELECT StuDept FROM Student WHERE Student.Sno=SC.Sno);

常用查询条件

查询条件	运算符或谓语
比较	=, >, >=, <=, !=, <>, !>, !<, NOT+上述比较符
确定范围	BETWEEN, AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件	AND,OR

JDBC

JDBC 是一种可用于执行 SQL 语句的 Java API(Application Programming Interface 应用程序设计接口) 是 Java 和数据库之间的标准接口，使用 JDBC 作为 Java 的数据库访问规范，可以实现数据库访问的平台无关性。

它由一些 Java 语言编写的类和接口组成

通过使用 JDBC ，开发人员可以很方便地将 SQL 语句传送给几乎任何一种数据库。也就是说，开发人员可以不必写一个程序访问 Sybase，写另一个程序访问 Oracle，再写一个程序访问 Microsoft 的 SQLServer

JDBC 的任务

1. 与一个数据库建立连接
2. 向数据库发送 SQL 语句
3. 处理数据库返回的结果

JDBC 是一种低级的 API

JDBC 具体使用

所有数据库的 JDBC 驱动程序都应该实现的重要接口：

• java.sql.DriverManager

是一个类，调用其静态方法处理驱动程序的调入和对驱动程序的管理

• java.sql.Connection

其对象代表对特定数据库的一个连接，连接对象由 DriverManager 的静态方法 getConnection() 创建

• java.sql.Statement

代表一个特定的容器，用以执行 SQL 语句

• java.sql.ResultSet

其对象代表执行一个 Select 语句后的结果集合，用以控制结果的输出格式

数据库的连接

1. 注册数据库驱动

即将驱动程序类装入 JVM 的过程。常使用类调用器(ClassLoader)

为了让 JDBC 与平台无关，JDBC 提供了“驱动程序管理器”，如果你需要链接 3 种不同厂商的数据库，那么就需要三个不同的驱动器对象。以下语句装载了两个不同的数据库驱动

Class.forName("sum.jdbc.odbc.jdbcOdbcDriver")
// Access 数据库的驱动类
Class.forName("com.mysql.JDBCDriver");
// MySQL 数据库的驱动类

2. 创建数据库连接

DriverManager 类，负责管理已经注册的驱动程序集合，实质上就是提取使用驱动程序的细节，而不用程序员直接处理。

• 构造 dbUrl
• dbUrl 构造好以后调用 static 方法：DriverManager.gerConnection()，传入 dbUrl，完成数据库的连接，该方法放回一个 connection 对象

Connection con;
con = DriverManager.getConnection(dbUrl);
con = DriverManager.getConnection(dbUrl,user,password);

dbUrl: JDBC 采用 URL 表示数据库，用一个 dbURL 字符串请求数据库连接，标准格式如下：

jdbc:<子协议>:<数据库标识>
// 形如：jdbc:drivertype:driversubtype://parameter

jdbc：表示所使用的是 JDBC 协议

子协议：驱动程序的名称或数据库连接机制的名称，也叫 subprotocal

driversubtype：可选的参数

数据库标识符 parameter：随所用的数据库驱动程序而有所不同，但通常会指定一个逻辑名称——通常用来设置数据库服务器的 IP 地址、端口号和数据库的名称

例如：

String dbUrl = "jdbc:odbc:people";
String dbUrl = "jdbc:rmi://192.168.170.27:1099/jdbc:cloudscape:db";
				子协议      服务器名        端口号 数据库标识

对于 MySQL，数据库 URL 为

jdbc:mysql://localhost:3306/dbName

3. 建立 Statement 对象，准备调用 SQL 语句

Statement stmt = con.createStatement();

4. 准备并执行调用 SQL 语句

String sql = “select * from Employees”;
ResultSet rs = stmt.executeQuery(sql);

5. 处理 ResultSet 中的记录集

while(rs.next()) { 
    String col1=rs.getString(1);
    String col2=rs.getString(fname);
    float col3=rs.getFloat(3);
}

6. 依次关闭 ResultSet、Statement和connection对象

rs.close();
stmt.close();
con.close();

嵌入式数据库 SQLite

嵌入式数据库是指在本机上运行，不用启动服务端的情形数据库，它与应用程序紧密集成，被应用程序所启动，并伴随应用程序的退出而终止。

嵌入式数据库与非嵌入式数据库的差别，在于运行模式的差别

似乎所有单机数据库都可以算嵌入式数据库，比如 Access、Paradox、DBF，因为他们都不用启动数据库服务器即可使用。但通常不将上述数据库归入嵌入式数据库，而只将他们归入“桌面数据库”。因为这些数据库的完备性、存储容量以及性能方面存在着较大的缺陷。嵌入式数据库支持的数据都是 TB 文件级别。

嵌入式数据库三雄：SQLite、Birkeley DB、Firebird

SQLite

SQLite 是一个嵌入到应用程序中的数据库引擎，没有服务端，没有单独管理数据库的服务器进程。应用程序通过函数调用和数据库引擎进程交互，而不是将消息发送给单独的进程或线程。

样例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Test {
    public static void main(String[] args) {
        Connection c = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:test.db");

            System.out.println("Opened database successfully!"); // 连接数据库

            Statement statement = null; // 创建一个 table
            statement = c.createStatement();
            String sql = "CREATE TABLE COMPANY" +
                         "(ID INT PRIMARY KEY       NOT NULL," +
                         "NAME            TEXT      NOT NULL," +
                         "AGE             INT       NOT NULL," +
                         "ADDRESS         CHAR(50)," +
                         "SALARY          REAL)";
            statement.executeUpdate(sql);
            statement.close();
            c.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Table created successfully!");
    }
}

下面我们插入数据：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Test {
    public static void main(String[] args) {
        Connection c = null;
        Statement statement = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully!");

            statement = c.createStatement();
            String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                         "VALUES (1,'Paul', 32, 'California', 20000.00);";
            statement.executeUpdate(sql);

            sql =   "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                    "VALUES (2, 'Allen', 25, 'Texas', 15000.00)";
            statement.executeUpdate(sql);

            sql =   "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                    "VALUES (3, 'Teddy', 23, 'Norway', 20000.00)";
            statement.executeUpdate(sql);

            sql =   "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                    "VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00)";
            statement.executeUpdate(sql);

            statement.close();
            c.commit();
            c.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Records created successfully");
    }
}

SELECT 查询操作

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

public class Test {
    public static void main(String[] args) {
        Connection c = null;
        Statement statement = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully!");

            statement = c.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM COMPANY;");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String address = resultSet.getString("address");
                float salary = resultSet.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Operation done successfully");
    }
}

操作完毕！我们就掌握了数据库的基础操作。