八、JDBC入门

JDBC入门

在Web开发中，不可避免地要使用数据库来存储和管理数据。为了在Java语言中提供对数据库访问地支持，Sun公司与1996年提供了一套访问数据库地标准Java类库，即JDBC。

JDBC概述

什么是JDBC
JDBC的全称是Java数据库连接(Java Database Connectivity)，它是一套用于执行SQL语句的JavaAPI。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新、删除等操作。
应用程序使用JDBC访问特定数据库时，需要与不同的数据库驱动进行连接。因为不同的数据库厂商提供的数据库不同，因此，为了使应用程序与数据库真正建立连接，JDBC不仅需要提供访问数据库的API，还需要封装与各种数据库服务器通信的细节。
JDBC的实现包括三部分：
1、JDBC驱动管理器：负责注册特定的JDBC驱动器，主要通过java.spl.DriverManager类实现。
2、JDBC驱动器API：由Sun公司负责制定，其中最主要的接口时java.spl.Driver接口。
3、JDBC驱动器：它是一种数据库驱动，由数据库厂商创建，也称为JDBC驱动程序。JDBC驱动器实现了JDBC驱动器API，负责与特定的数据库连接，以及处理通信细节。
JDBC常用API
在开发JDBC程序前，首先了解一下JDBC常用的API。JDBC API主要位于java.sql包中，该包的接口如下
1、Driver接口
Driver接口是所有JDBC驱动程序必须实现的接口，该接口专门提供给数据库厂商使用。在编写JDBC程序时，必须要把指定数据库驱动程序或类库加载到项目的classpath中。
2、DriverManager类
DriverManager类用于加载JDBC驱动并且创建于数据库的连接。

方法名称	功能描述
registerDriver(Driver driver)	该方法用于向DriverManager中注册给定的JDBC驱动程序
getConnection(String url,String user,String pwd)	该方法用于建立和数据库的连接，并返回表示连接的Connection对象

3、Connection接口
Connection接口代表Java接口和数据库的连接，在Connection接口中，定义了一系列方法

方法名称	功能描述
getMetaData()	该方法用于返回表示数据库的元数据的DatabaseMetaData对象
createStatement()	用于创建一个Statement对象来将SQL语句发送到数据库
prepareStatement(String sql)	用于创建一个PreparedStatement对象来将参数化的SQL语句发送到数据库
prepareCall(String sql)	用于创建一个CallableStatement对象来调用数据库存储过程

4、Statement接口
Statement接口用于向数据库发送SQL语句，在Statement接口中，提供了三个执行SQL语句的方法

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

5、PreparedStatement接口
PreparedStatement是Statement的子接口，用于执行预编译的SQL语句

方法名称	功能描述
executeUpdate()	在此PreparedStatement对象中执行SQL语句，该语句必须是一个DML语句或者无返回内容的SQL语句。比如DDL语句。
executeQuery()	在此PreparedStatement对象中执行SQL查询，该方法返回的是ResultSet对象。
setInt(int parameterIndex,int x)	将指定参数设置为给定的int值
setFloat(int parameterIndex, float x)	将指定参数设置为给定的float值
setString(int parameterIndex,String x)	将指定参数设置为给定的Data值
addBatch()	将一组参数添加到此PreparedStatement对象的批处理命令中
setCharacterStream(parameterIndex,reader,length)	将指定的输入流写入数据库的文本字段
setBinaryStream(parameterIndex,x,length)	将二进制的输入流数据写入到二进制字段中

需要注意的是：setDate()方法可以设置日期内容，但参数Date的类型是java.sql.Date，而不是java.util.Date。
6、CallableStatement接口
CallableStatement接口是PreparedStatement的子接口。

方法名称	功能描述
registerOutParameter(int parameterIndex,int sqlType)	按顺序位置将OUT参数注册为SQL类型。其中，parameterIndex表示顺序位置，slqType表示SQL类型
setNull(String parameterName,int sqlType)	将指定参数设置为SQL类型的NULL
setString(String parameterName,String x)	将指定参数设置为给定Java类型的String值
wasNull()	查询最后一个读取的OUT参数是否为SQL类型的NULL值
getInt(int parameterIndex)	以java语言中int值的形式获取指定的数据库中INTEGER类型参数的值

需要注意的是：由于CallableStatement接口是PreparedStatement的子接口，PreparedStatement是Statement的子接口，因此CallableStatement接口除了拥有自己特有的方法，也同时拥有了这两个父接口中的方法。
7、ResultSet接口
ResultSet接口表示select查询语句的得到的结果集，该结果集封装在一个逻辑表格中。在ResultSet接口内部有一个指向表格数据行的游标，ResultSet对象初始化时，游标在表格的第一行之前。
ResultSet接口中定义了大量的getXxx()方法，采用哪种getXxx()方法取决于字段的书记类型。程序既可以通过字段的名称来获取指定数据，也可以通过字段的索引来获取指定的数据，字段的索引是从1开始编号的。

方法名称	功能描述
getString(int columnIndex)
getString(String columnName)	用于获取指定字段的String类型的值，参数columnName表示字段名称
getInt(int columnIndex)
getInt(String columnName)	用于获取指定字段的int类型的值，参数columnName表示字段名称
getDate(int columnIndex)
getDate(String columnName)	用于获取指定字段的Date类型的值，参数columnName表示字段名称
next()	将游标从当前位置向下移一行
absolute(int row)	将游标移动到此ResultSet对象的指定行
afterLast()	将游标移动到此ResultSet对象的末尾
beforeFirst()	将游标移动到此ResultSet对象的开头
previous()	将游标移动到此ResultSet对象的上一行
last()	将游标移动至此ResultSet对象的最后一行

连接数据库

获取mysql安装包
https://downloads.mysql.com/archives/community/

安装mysql
安装后打开任务管理器，找到后台进程mysqld，说明安装成功。

获取对应版本的数据库连接jar包

http://static.runoob.com/download/mysql-connector-java-5.1.39-bin.jar
将jar包导入环境

JDBC编程步骤

1、加载并注册数据库驱动

DriverManager.registerDriver(Driver driver);

2、通过DriverManager获取数据库连接

Connection conn=DriverManager.getConnection(String url,String user,String pass);

从上述方式可以看出，方法有三个参数，分别表示数据库url，登录数据库的用户名和密码
数据库url通常遵循如下形式的写法

jdbc:subprotocol:subname

上面url写法中jdbc部分是固定的，subprotocol指定连接到特定数据库的驱动程序，而subname部分则很不固定，不同数据库的url形式可能存在较大差异，以Mysql数据库url为例

jdbc:mysql://hostname:port/databasename

3、通过Connection对象获取Statement对象

Connection创建Statement对象有如下三种：
1）createStatement():创建基本的Statement对象
2）prepareStatement():创建PreparedStatement对象
3）prepareCall():创建CallableStatement对象
以创建基本的Statement对象为例

Statement stmt=conn.createStatement();

4、使用Statement执行SQL语句
所有的Statement都有三种方式来执行SQL语句
1）execute():可以执行任何SQL语句
2）executeQuery():通常执行查询语句，执行后返回代表结果集的ResultSet对象
3）executeUpdata():主要用于执行DML和DDL语句。执行DML语句，返回受SQL语句影响的行数，执行DDL语句返回0

ResultSet rs=stmt.executeQuery(sql);

5、操作ResultSet结果集
ResultSet对象主要分为一下两类
1）next()、previous()等移动记录指针的方法
2）getXxx()获取指针指向行，特定列的值
6、回收数据库资源
关闭数据库连接、释放资源、包括关闭ResultSet、Statement和Connection等资源。

第一个JDBC程序

1、搭建实验环境
在Mysql中创建一个名为chapter01的数据库，然后在该数据库创建一个users表

create database chapter01;
use chapter01;
create table users(
	id int primary key auto_increMent,
	name varchar(40),
	password varchar(40),
	email varchar(60),
	birthday date
)character set utf8 collate utf8_general_ci;

数据库和表创建成功后，再插入三条数据

insert into users(name,password,email,birthday)
value('zs','123456','zs@sina.com','1980-12-04');
insert into users(name,password,email,birthday)
value('lisi','123456','lisi@sina.com','1981-12-04');
insert into users(name,password,email,birthday)
value('wangwu','123456','wangwu@sina.com','1979-12-04');

查看users表

2、导入数据库驱动
上文已经导入
3、编写JDBC程序
Ex01.java

package JDBC;

import java.sql.*;

public class Ex01 {
	public static void main(String[] args)throws SQLException{
		//1、注册数据库驱动
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		//2、获取数据库连接
		String url="jdbc:mysql://localhost:3306/chapter01";
		String username="root";
		String password="123456";
		Connection conn=DriverManager.getConnection(url, username, password);
		//3、获取Statement对象
		Statement stmt=conn.createStatement();
		//4、执行sql
		String sql="select * from users";
		ResultSet rs=stmt.executeQuery(sql);
		System.out.println("id|name|password|birthday");
		while(rs.next()){
			int id=rs.getInt("id");
			String name=rs.getString("name");
			String psw=rs.getString("password");
			String email=rs.getString("email");
			Date birthday=rs.getDate("birthday");
			System.out.println(id+"|"+name+"|"+psw+"|"+email+"|"+birthday);
		}
	}
}

需要注意的是，程序需要两个方面来改进
1、注册程序
在注册驱动时，虽然DriverManager.registerDriver(new com.mysql.jdbc.Driver())可以完成，但会使数据库驱动被注册两次。这是因为Driver类的源码中，已经在静态代码块中完成了数据库驱动的注册。所以为了避免数据库驱动被重复注册，只需在程序中加载驱动类即可

Class.forName("com.mysql.jdbc.Driver");

2、释放资源
由于数据库资源非常宝贵，数据库允许的并发访问连接数量有限，因此，当数据库资源使用完毕后，一定要记得释放资源。为了保证资源的释放，在Java程序中，应该将最终必须要执行的操作放在finally代码块中。

if(rs!=null){
	try{
		rs.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	rs=null;
}
if(stmt!=null){
	try{
		stmt.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	stmt=null;
}
if(conn!=null){
	try{
		conn.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	conn=null;
}

PreparedStatement对象

SQL语句的执行是通过Statement对象实现的。Statement对象每次执行SQL语句时，都会对其进行编译。当相同的SQL语句执行多次时，Statement对象就会使数据库频繁编译相同的SQL语句，从而降低数据库的访问效率。
为了解决以上问题，Statement提供了一个子类PreparedStatement。该对象可以对SQL语句进行预编译，当执行相同的SQL语句时，数据库只需使用缓冲区数据，而不需要对SQL语句重新编译，从而提高访问效率。
Ex02.java

package JDBC;

import java.sql.*;

public class Ex02 {
	public static void main(String[] args)throws SQLException{
		Connection conn=null;
		PreparedStatement preStmt=null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/chapter01";
			String username="root";
			String password="123456";
			conn=DriverManager.getConnection(url, username, password);
			String sql="insert into users(name,password,email,birthday)"
					+"value(?,?,?,?)";
			preStmt=conn.prepareStatement(sql);
			preStmt.setString(1, "z1");
			preStmt.setString(2, "123456");
			preStmt.setString(3, "z1@sina.com");
			preStmt.setString(4, "1789-12-23");
			preStmt.executeUpdate();
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try{
					conn.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				conn=null;
			}
			if(preStmt!=null){
				try{
					preStmt.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				preStmt=null;
			}
		}
	}
}

首先通过Connection对象的prepareStatement()方法生成prepareStatement对象，然后调用prepareStatement对象的setXxx()方法，给SQL语句中的参数赋值，最后通过调用executeUpdate()方法执行SQL语句。
查询数据库查看结果

CallableStatement对象

CallableStatement接口是用于执行SQL存储过程的接口，它继承自PreparedStatement接口。JDBC API提供了一个存储过程SQL转义语法，该语法允许对所有关系数据库管理系统使用标准方法调用存储过程语法有一个包含结果参数的形式和一个不包含结果参数的形式

{?=call<procedure-name>[{<arg1>,<arg2>,...}]}
{call<procedure-name>[{<arg1>,<arg2>,...}]}

上述语法中arg1，arg2等有三种不同的形式
1、in类型：此类型是用于参数从外部传递给存储过程使用
2、out类型：此类型是存储过程执行过程中的返回值
3、in、out混合类型：此类型是参数传入，然后返回
如果使用结果参数，则必须将其注册为OUT参数。其他参数可用于输入、输出或者同时用于二者。参数是根据编号按顺序引用的，第一个参数编号为1
in参数值是使用继承自PreparedStatement的setXxx()方法设置的。在执行存储过程之前，必须注册所有out参数类型；他们的值是使用继承自PreparedStatement的getXxx()方法获取的。
CallableStatement可以返回一个或多个ResultSet对象，多个ResultSet对象是通过继承Statement来处理的。
在数据库中创建一个简单的存储过程

delimiter //
create procedure add_pro(a int,b int,out sum int)
begin
set sum=a+b;
end//
delimiter;

package JDBC;

import java.sql.*;

public class Ex03 {
	public static void main(String[] args)throws Exception{
		Connection conn=null;
		CallableStatement cstmt=null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/chapter01";
			String username="root";
			String password="123456";
			conn=DriverManager.getConnection(url, username, password);
			cstmt=conn.prepareCall("call add_pro(?,?,?)");
			cstmt.setInt(1, 4);
			cstmt.setInt(2, 5);
			cstmt.registerOutParameter(3, Types.INTEGER);
			cstmt.execute();
			System.out.println("执行结果是:"+cstmt.getInt(3));
		}finally{
			if(cstmt!=null){
				cstmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
	}
}

ResultSet对象

在之前所讲解的ResultSet操作中，ResultSet主要用于存储结果集，并且只能通过next()方法由前向后逐个获取结果集中的数据。但是如果想获取结果集中任意位置的数据，则需要在创建Statement对象时，设置两个ResultSet定义的常量

Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=st.excuteQuery(sql);

ResultSet.TYPE_SCROLL_INSENITIVE表示结果集可滚动
ResultSet.CONCUR_READ_ONLY表示结果集以只读的形式打开

JDBC批处理

在实际开发中，经常需要向数据库发送多条SQL语句，这时，如果逐条执行这些SQL语句，效率会很低。Statement和PreparedStatement都实现了批处理
Statement批处理
当向数据库发送多条不同的SQL语句时，可以使用Statement实现批处理。Statement通过addBatch()方法添加一条SQL语句，executeBatch()方法批量执行SQL语句。
为了方便连接数据库，可以编写一个JDBCUtils类来封装代码。

package JDBC;

import java.sql.*;

public class JDBCUtils {
	public static Connection getConnection()throws SQLException,ClassNotFoundException{
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/chapter01";
		String username="root";
		String password="123456";
		Connection conn=DriverManager.getConnection(url, username, password);
		return conn;
	}
	public static void release(Statement stmt,Connection conn){
		if(stmt!=null){
			try{
				stmt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			stmt=null;
		}
		if(conn!=null){
			try{
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			conn=null;
		}
	}
	public static void release(ResultSet rs,Statement stmt,Connection conn){
		if(rs!=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			rs=null;
		}
		if(stmt!=null){
			try{
				stmt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			stmt=null;
		}
		if(conn!=null){
			try{
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			conn=null;
		}
	}
}

package JDBC;

import java.sql.*;

public class Ex10 {
	public static void main(String[] args)throws Exception{
		Connection conn=null;
		Statement stmt=null;
		try{
			conn=JDBCUtils.getConnection();
			stmt=conn.createStatement();
			String sql1="drop table if exists school";
			String sql2="create table school(id int,name varchar(20))";
			String sql3="insert into school values(2,'xiaoming')";
			String sql4="update school set id=1";
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			stmt.addBatch(sql3);
			stmt.addBatch(sql4);
			stmt.executeBatch();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(null, stmt, conn);
		}
	}
}

PreparedStatement批处理

当向同一个数据表中批量更新数据时，如果使用Statement，需要书写很多sql语句，可以使用 PreparedStatement实现批处理可以避免重复的代码书写。与Statement相比PreparedStatement灵活很多，它既可以使用完整的SQL，也可以使用带参数的不完整的SQL。

package JDBC;

import java.sql.*;

public class Ex11 {
	public static void main(String[] args)throws Exception{
		Connection conn=null;
		PreparedStatement preStmt=null;
		try{
			conn=JDBCUtils.getConnection();
			String sql="insert into users(name,password,email,birthday)"
					+"value(?,?,?,?)";
			preStmt=conn.prepareStatement(sql);
			for(int i=0;i<5;i++){
				preStmt.setString(1, "name"+i);
				preStmt.setString(2, "password"+i);
				preStmt.setString(3, "email"+i);
				preStmt.setDate(4, Date.valueOf("1989-02-19"));
				preStmt.addBatch();
			}
			preStmt.executeBatch();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(null, preStmt, conn);
		}
	}
}

批处理执行select时会报错。因为Statement和PreparedStatement的executeBatch方法的返回值都是int[]类型，所以，能够进行批处理的SQL语句必须是返回值是int类型的sql语句。

大数据处理

大数据处理主要指的是对CLOB和BLOB类型数据的操作。
1、处理CLOB数据
CLOB(Character Large Object)译为字符大对象，用于储存大文本数据，但是对mysql而言，大文本数据的存储是通过TEXT类型表示的。
1）首先在数据库中，创建一个数据表testclob

create table testclob(
	id int primary key auto_increment,
	resume text
);

2）在工程中新建一个类CLOBDemo01，该类实现了向数据库写入大文本数据的功能。
CLOBDemo01.java

package JDBC;

import java.sql.*;
import java.io.*;
import JDBC.JDBCUtils;

public class CLOBDemo01 {
	public static void main(String[] args){
		Connection conn=null;
		PreparedStatement prestmt=null;
		try{
			conn=JDBCUtils.getConnection();
			String sql="insert into testclob values(?,?)";
			prestmt=conn.prepareStatement(sql);
			File file=new File("D:/test.txt");
			Reader reader=new InputStreamReader(new FileInputStream(file),"utf-8");
			prestmt.setInt(1, 1);
			prestmt.setCharacterStream(2, reader,(int)file.length());
			prestmt.executeLargeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(null, prestmt, conn);
		}
	}
}

3）在工程中新建一个CLOBDemo02用于读取表中的数据。
CLOBDemo02.java

package JDBC;

import java.sql.*;
import java.io.*;
import JDBC.JDBCUtils;

public class CLOBDemo02 {
	public static void main(String[] args){
		Connection conn=null;
		PreparedStatement prestmt=null;
		ResultSet rs=null;
		try{
			conn=JDBCUtils.getConnection();
			String sql="select * from testclob";
			prestmt=conn.prepareStatement(sql);
			rs=prestmt.executeQuery();
			if(rs.next()){
				Reader reader=rs.getCharacterStream("resume");
				Writer out=new FileWriter("resume.txt");
				int tmp;
				while((tmp=reader.read())!=-1){
					out.write(tmp);
				}
				out.close();
				reader.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(rs, prestmt, conn);
		}
	}
}

2、处理BLOB数据
BLOB(Binary Large Object)译为二进制大对象，用于存放二进制数据的类型，如电影、图片等。
1）在数据库中新建testBLOB表

create table testblob(
	id int primary key auto_increment,
	img blob
);

2）在工程下新建BLOBDemo01用于将图片写入testblob中

package JDBC;

import java.sql.*;
import java.io.*;
import JDBC.JDBCUtils;

public class BLOBDemo01 {
	public static void main(String[] args){
		Connection conn=null;
		PreparedStatement prestmt=null;
		try{
			conn=JDBCUtils.getConnection();
			String sql="insert into testblob values(?,?)";
			prestmt=conn.prepareStatement(sql);
			prestmt.setInt(1, 1);
			File file=new File("D:/test.png");
			InputStream in=new FileInputStream(file);
			prestmt.setBinaryStream(2, in,(int)file.length());
			prestmt.executeLargeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(null, prestmt, conn);
		}
	}
}

3）在工程中新建BLOBDemo02用于读取数据

package JDBC;

import java.sql.*;
import java.io.*;
import JDBC.JDBCUtils;

public class BLOBDemo02 {
	public static void main(String[] args){
		Connection conn=null;
		PreparedStatement prestmt=null;
		ResultSet rs=null;
		try{
			conn=JDBCUtils.getConnection();
			String sql="select * from testblob";
			prestmt=conn.prepareStatement(sql);
			rs=prestmt.executeQuery();
			if(rs.next()){
				InputStream in=new BufferedInputStream(rs.getBinaryStream("img"));
				OutputStream out=new BufferedOutputStream(new FileOutputStream("img.png"));
				int tmp;
				while((tmp=in.read())!=-1){
					out.write(tmp);
				}
				out.close();
				in.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.release(rs, prestmt, conn);
		}
	}
}