JDBC对数据库的基本操作

第N章 使用数据库

---

N.1 连接数据库

N.2 执行SELECT语句

N.3 执行DML语句

N.4 参数化SQL

N.5 使用存储过程

N.6 使用事务

N.7 操作大数据字段

---

N.1 连接数据库

连接数据库时，需要数据库厂商提供的JAVA引擎，这实在是一个比较无奈的事情。

连接数据库的语法如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
...
Class.forName("类名").newInstance();
Connection con = DriverManager.getConnection("连接字符串", "用户名", "口令");

下表提供了常用数据库的JAVA引擎及连接字符串

数据库	驱动提供者	驱动类	连接字符串
SQL Server	Microsoft	com.microsoft.jdbc.sqlserver.SQLServerDriver	jdbc:microsoft:sqlserver://服务器名或IP:端口号(默认为1433); DatabaseName=pubs; SelectMethod=cursor
	jTDS	net.sourceforge.jtds.jdbc.Driver	jdbc:jtds:sqlserver://服务器名或IP:端口号(默认为1433)/数据库名
ORACLE	Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@服务器名或IP:端口号(默认为1521):数据库SID
ODBC	Sun	sun.jdbc.odbc.JdbcOdbcDriver	jdbc:odbc:数据源名称

下面是一个连接本地SQL SERVER的pubs库的程序：

import java.sql.Connection;
import java.sql.DriverManager;
...
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection con = DriverManager.getConnection(
    "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs;SelectMethod=cursor", 
    "sa", 
    "sa");

N.1.1 SQL Server

如果要使用Windows 身份验证方式连接SQL Server，使用微软的开发包我还没找出来，使用jTDS的话，连接字符串可以这样写：

Connection connection = 
    DriverManager.getConnection(
        "jdbc:jtds:sqlserver://localhost:1433/pubs;domain=a", 
        "administrator", 
        "liulindong");

因为我的电脑不在域中，所在domain我是随便写的，但不能为空，后面两个参数是windows的用户名和口令，呵呵。

N.2 执行SELECT语句

下面是一个简单的例子，它连接本机的SQL SERVER pubs库，输出authors表的全部内容。

package example;

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

public class Test ...{
    
    public static void main(String[] args) throws Exception
    ...{
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
        Connection con = DriverManager.getConnection(
            "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs;SelectMethod=cursor", 
            "sa", 
            "sa");
        Statement st = con.createStatement();
        String sql = "select * from authors";
        ResultSet rs = st.executeQuery(sql);
        ResultSetMetaData meta_data = rs.getMetaData();
        
        for(int i_col = 1; i_col <= meta_data.getColumnCount(); i_col++)
        ...{
            System.out.print(meta_data.getColumnLabel(i_col) + " ");
        }
        System.out.println();
        
        while(rs.next())
        ...{
            for(int i_col = 1; i_col <= meta_data.getColumnCount(); i_col++)
            ...{
                System.out.print(rs.getString(i_col) + " ");
            }
            System.out.println();
        }
        rs.close();
        st.close();
    }

}

从上例中，我们可获知如下几点内容：

1. 使用java.sql.Statement.executeQuery来执行SELECT语句
2. 它返回一个ResultSet类型，使用next()方法遍历
3. 使用ResultSetMetaData来获取结果集的一些统计信息，如列名、列数、列类型等
4. 使用ResultSetMetaData和ResultSet的get*()类的成员函数时，如果以整数值作为索引，则索引值从1开始。

注意：ResultSet没有提供方法可以获取结果集的记录条数，所以，如果想要获取记录条数，需要使用count()函数自己去查。网上有些人提出的所谓解决方法并不好用，说明如下：

1. 使用ResltSet.last()和ResultSet.getRow()以获取记录条数，但很可惜，有些JDBC的驱动并不实现last()函数，例如JDBC-ODBC、SQL Server，其实这些JDBC驱动不实现除next()外的所有的定位操作，包括：first()、last()、previous()、absolute()等
2. 使用对next()方法进行计数，方法可行，但且不说效率如何，只说我们获取了结果集，并不是只是为了得到一个结果集记录条数的。循环完了，我们怎么回去呢，因为对某些JDBC驱动，可并不提供first()方法哦。

N.3 执行DML语句

使用Statement.executeUpdate()方法或Statement.execute()方法

N.4 参数化SQL

使用PreparedStatement对象

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

N.5 使用存储过程

N.5.1 带返回参数的存储过程

针对如下的表：

create table test_table(a int, b int) 

有如下存储过程

create procedure [sp_test]
    @a int, @b int, @count int output
with recompile
as
    set nocount on
    insert into test_table(a, b) values(@a, @b)
    select @count = count(*) from test_table
go

Java的示例代码如下所示：

import java.sql.*;
...
Connection conn = ...;    //获取Connection
String sql = "exec sp_test ?, ?, ?";
CallableStatement st = conn.prepareCall(sql);
st.setInt(1, 10);    //设置输入参数
st.setInt(2, 10);
st.registerOutParameter(3, Types.INTEGER);    //设置输出参数
st.execute();
System.out.println(st.getInt(3));        //获取输出参数的值

N.5.2 返回结果集的存储过程

这个简单，直接使用executeQuery()即可。也不一定用CallableStatement对象，Statement(如果没参数)和PrepareStatement均可。

N.6 使用事务

一般使用的步骤如下所示：

Connection conn = ...;     //获取Connection对象
conn.setAutoCommit(false);    //取消自动提交
try
...{
    ...(数据库操作)
    conn.commit();    //提交更改
}
catch(Exception ex)
...{
    conn.rollback();    //回滚事务
    ex.printStackTrace();
}
conn.setAutoCommit(true);    //恢复自动提交

N.7 操作大数据字段

大数据字段是指ORACLE下的BLOB、CLOB，SQL Server下的image、text等类型的字段。

N.7.1 示例表

假设有如下的SQL SERVER表

create table test_table(id int, content image)

N.7.2 引用的类

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

N.7.2 插入

Connection connection = ...    //获取连接
String sql;
sql = "insert into test_table(id, content) values(?, ?)";
PreparedStatement st = connection.prepareStatement(sql);
st.setInt(1, 20);
String file_path = "c:/1.xls";    //要读取的文件

//获取文件长度并分配空间
File f = new File(file_path);
byte[] data = new byte[(int)f.length()];

//读取文件内容并插入
FileInputStream fs = new FileInputStream("c:/1.xls");
fs.read(data);
st.setObject(2, data);
st.executeUpdate();
fs.close();

N.7.3 读取

Connection connection = ...    //获取连接
String sql = "select content from test_table where id = 10";
Statement st2 = connection.createStatement();
ResultSet rs = st2.executeQuery(sql);
rs.next();
InputStream outStream = rs.getBinaryStream(1);
FileOutputStream fw = new FileOutputStream("c:/2.xls", false);    //输出到c:.xls
int buf_len = 1024;
byte[] out_buf = new byte[buf_len];
int read_len = outStream.read(out_buf);
while(read_len > 0)
...{
    fw.write(out_buf, 0, read_len);            
    read_len = outStream.read(out_buf);
}
fw.flush();
fw.close();
outStream.close();