这篇文章是我对李刚老师的《疯狂Java讲义》第三版第十三章：MySQL 数据库与JDBC编程的学习总结。适合有一定SQL基础的同学，如果你还不是很了解SQL，建议去: w3school 熟悉一下SQL的概念和基本语法。本文更专注于数据库在Java程序中的实践。最近刚学会用Github和Hexo来搭建自己的个人博客，有兴趣的朋友可以关注一下，一起探讨技术。
SQL语句基础
| 语句 | 内容 |
|---|---|
| 查询语句 | select |
| DML(Data Manipulation Language) | insert, update, delete |
| DDL(Data Definition Language) | create, alter, drop, truncate |
| DCL(Data Control Language) | grant, revoke |
JDBC 常用类
| 接口／类 | 解释 |
|---|---|
| Driver Manager | 用于管理JDBC驱动 |
| Connection | 数据库连接对象 |
| Statement | 用于执行SQL语句的工具接口 |
| PreparedStatement | 预编译的Statement对象 |
| CallableStatement | 用于调用储存过程 |
| ResultSet | 结果集对象 |
JDBC 编程步骤
加载数据库驱动: <code>Class.forName("com.mysql.jdbc.Driver");</code>
通过<code>DriverManager</code>获取数据库连接，获得<code>Connection</code>对象：
<code>jdbc:mysql://hostname:port/databasename</code>通过<code>Connection</code>对象创建<code>Statement</code>对象
使用Statement对象执行SQL语句
- <code>execute()</code> 可执行任何SQL语句
- <code>executeUpdate()</code> 用于执行DML和DDL语句。在执行DML语句返回受影响行数，执行DDL语句返回0。
- <code>executeQuery()</code> 只执行查询语句，返回<code>ResultSet</code>对象
- 操作结果集
- 程序可通过 <code>next()</code>, <code>previous()</code>, <code>first()</code>, <code>last()</code>, <code>beforeFirst()</code>, <code>beforeLast()</code>, <code>afterLast()</code>, <code>absolute()</code>等方法移动记录指针
- 通过<code>getXXX(column index)</code>获取记录指针指向行、列的值。
- 回收数据库资源：关闭<code>ResultSet</code>、<code>Statement</code>、<code>Connection</code>等资源。
1. 用Properties类来加载属性文件
1.1 属性文件格式
drv = com.mysql.jdbc.Driver
url = jdbc:mysql://hostname:port/databasename
usr = username
pwd = password
例如下面的配置建立的连接会占用电脑的3306端口，生成一个people_list数据库的<code>Connection</code>对象，我们把这个配置文件命名为<code>mysql.ini</code>并将其放置在项目的根目录中：
drv = com.mysql.jdbc.Driver
url = jdbc:mydql://127.0.0.1:3306/people_list
usr = enzeM
pwd = 1234567
需要注意的是 mysql 在计算机中的默认端口是3306，除非更改mysql的默认端口值，否则把配置文件端口设为其他端口会出现mysql连接异常。
1.2 在Java程序中加载属性文件
private String drv;
private String url;
private String usr;
private String pwd;
void initConnFileds(String fileName) throws Exception {
Properties p = new Properties();
p.load(new FileInputStream(fileName));
drv = p.getProperties("drv");
url = p.getProperties("url");
usr = p.getProperties("usr");
pwd = p.getProperties("pwd");
//step1: load the database
Class.forName("mysql.ini")
}
2. 通过JDBC实现数据库的基本操作
2.1 用Statement和execute()写一个可以执行任何SQL语句的函数
使用情境:
- 在不清楚SQL语句类型的情况下可以使用。
- 使用getString()方法可以取得除Blob之外的任意类型列的值。
void executeSQL(String query) throws Exception {
//step2: get Connection object
try(Connection conn = DriverManager.getConnection(url, usr, pwd);
Statement stmt = conn.createStatement()) {//step3: get Statement object
//step4: get ResutSet object
boolean hasResultSet = stmt.execute(query);
if(hasResultSet) {
try(ResultSet rs = stmt.getResutSet()) {
//use metadata object to visit all possible info from tables
ResultSetMetaData rsmd = rs.getMetaData();
int colunmCount = rsmd.getColumnCount();
//print result after execute the query
while(rs.next()) {
for(int i=0; i<colunmCount; i++) {
System.out.print(rs.getString(i+1) + "\t");
}
System.out.println();
}
}
} else {
System.out.println("this query effect "
+ stmt.getUpdateCount() + " record(s) in the database");
}
}
}
上面的代码使用<code>execute()</code>来执行SQL语句返回了一个<code>boolean</code>值，它表明SQL是否返回了<code>ResultSet</code>对象。然后程序用<code>getResutSet()</code> 获取<code>Statement</code>执行查询语句后的<code>RsultSet</code>。使用<code>getUpdateCount()</code>获取<code>Statement</code> DML语句所影响的记录行数。
2.2 用PreparedStatement执行SQL语句
使用情境：
- 适用于经常需要反复执行的一条语句
- 使用占位符<code>?</code>代替需要输入的参数
- 预编译SQL语句，性能更好
- 防止SQL注入攻击，安全性高
<code>PreparedStatement</code>对象储存了预编译SQL语句，因此可以高效的反复执行该语句。
若清楚表格的参数类型，我们可以使用</code>setXXX(int index, XXX value)</code>的方法传入参若不清楚表格的参数类型，我们可以使用<code>setObject()</code>的方法传入参数。接下来，我们在数据库里建一个Employees，然后完成：1）加入新的employee record、2）根据ID找出Employee在Table里记录的讯息。
CREATE TABLE Employees (
ID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Occupation VARCHAR(255),
Salary VARCHAR(255)
)
- 加入新的 employee record:
void insertNewEmployee(String name, String occupation, int salary) throws Exception {
try(Connection conn = DriverManager.getConnection(url, usr, pwd);
PreparedStatement pstmt = conn.preparedStatement("INSERT INTO Employee VALUES(null, ?, ?, ?)")) {
//pstmt.setXXX(index, value)
pstmt.setString(1, "Tom");
pstmt.setString(2, "Java Enginner");
pstmt.setInt(3, 20000);
pstmt.executeUpdate();
}
}
这里的index指的是占位符<code>?</code>的位置，跟数组不同的是，SQL中的index相关的起始位置为1而不是0，比如column也是从0开始的。上面的代码用<code>PreparedStatement</code>的<code>setXXX()</code>方法添加了新的employee record并且在最后使用<code>executeUpdate()</code>更新了Table, 下面的代码实现了根据ID找出Employee在数据库里面的讯息
- 根据ID找出Employee在Table里记录的讯息:
void findEmployeeBy(int id) throws Exception {
try(Connection conn = DriverManager.getConnection(url, usr, pwd);
PreparedStatement pstmt = conn.preparedStatement("SELECT * FROM Employee WHERE ID=?")) {
pstmt.setInt(1, id); //replace ? to id parameter
ResultSet rs = pstmt.executeQuery(); //get result set
//if we know number of column in the table
/*
if(rs.next()) {
for(int i=0; i<4; i++) {
System.out.print(rs.getString(i+1)+"\t");
}
System.out.println();
}*/
//if we do not know abut the table, we use metadata
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
for(int i=0; i<columnCount; i++) {
System.out.print(rs.getString(i+1)+"\t");
}
System.out.println();
}
}
}
2.3 用CallableStatement调用储存过程（Procedure）
使用场景：
- 需要调用数据库中定义的procedure来处理数据
例子：建立一个储存过程:
delimiter //
create procedure diff_salary(a int, b int, out diff int)
begin
set diff = abs(a - b)
end;
以上例子在数据库里定义了一个procedure diff_salary 来计算两个input的差值。我们要通过程序调用这个procedure，需要使用<code>CallableStatement</code>，以下是具体步骤：
- 使用<code>CallableStatement</code>调用 procedure:
- 用<code>Connection</code>对象的<code>prepareCall()</code>方法生成<code>CallableStatement</code>对象
- 用<code>CallableStatment</code>的<code>setXXX()</code>为procedure传入参数
- 用<code>rigisterOutParameter()</code>的方法来注册获取储存过程的值
- 用<code>CallStatement</code>的<code>execute()</code>方法来执行储存过程
- 用<code>getXXX(int index)</code>获取指定参数的值
void callDiffSalaryProc(int a, int b) throws Exception {
try(Connection conn = DriverManager.getConnection(url, usr, pwd);
CallableStatment cstmt = conn.prepareCall("{call diff_salary(?, ?, ?)}")) { //注意这里使用的格式
cstmt.setInt(1, a);
cstmt.setInt(2, b);
cstmt.registerOutParameter(3, TYPE.INTEGER);
cstmt.execute();
System.out.println("result is:"+cstmt.getInt(3));
}
}
管理结果集(to be continue)
- 文章还会继续更新，有不足的地方请指教，或者在我的博客留言。
