一、使用JDBC处理大数据(Large Objects)
在实际开发中,程序需要把大文本或二进制数据保存到数据库(例如一个电影、一个很长很长的大文本要存入数据库)中,用JDBC该怎么操作呢?
1、基本概念
大数据也称之为LOB(Large Objects),LOB又分为:clob和blob
l clob用于存储大文本。
l blob用于存储二进制数据,例如图像、声音、二进制文等。
特别注意:对MySQL而言只有blob,而没有clob,MySQL存储大文本采用的是Text,Text和blob分别又分为:
l TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
l TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
2、处理大文本数据
案例1,向数据库的表中插入或读取大文本数据。
准备:
l com.hbsi.utils包下的DBManager类负责获得连接以及资源的释放。
l 属性文件db.properties保存了建立连接时需要的参数。
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demodb
username=root
password=root
l MySQL的驱动
l src目录下放置一个文本文件1.txt
l 创建数据库、表
create database demodb;
use demodb;
create table testclob
(
id int primary key auto_increment,
resume text
);
编写代码:Demo1.java
public void insert(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql = "insert into testclob(resume) values(?)";
st = conn.prepareStatement(sql);
//st.setCharacterStream()方法的第二个参数需要一个流对象,第三个但是需要大文本文件的长度,因此通过Demo1.class调用getcLassLoader()方法得到类加载器后没有直接得到输入流对象,而是先得到相关文件的路径和文件名,再构建一个File对象,利用该File对象得到相关文件的长度
//该方法自动从流中读取数据存入表格中
String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();
File f = new File(path);
st.setCharacterStream(1, new FileReader(path),(int)f.length() );
//setCharacterStream()方法在jdk1.6之前的版本第三个参数只能是int类型,jdk1.6之后可以是long类型(重载方法)
int num = st.executeUpdate();
if(num>0){
System.out.println("插入成功!!");
}
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}
public void find(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql = "select resume from testclob where id=1";
st = conn.prepareStatement(sql);
rs = st.executeQuery();
if(rs.next()){
Reader reader = rs.getCharacterStream("resume");
char buffer[] = new char[1024];//缓冲区不能太大,大文本一点一点地流进来
int len = 0;
FileWriter writer = new FileWriter("c:\\1.txt");
while((len=reader.read(buffer))>0){
writer.write(buffer,0,len);
}
reader.close();
writer.close();
}
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}
代码段如果写成这样:
if(rs.next()){
String result=rs.getString(“resume”);
System.out.println(result);
}
也能执行,可在控制台窗口看到大文本的输出。可是在实际的操作中千万不要这样写!因为这样做是将数据库中的大文本读到内存的字符串中,如果该大文本是非常大的,500M或1G,系统就立刻在内存中开辟500M或1G的内存,系统立即就崩溃了,死机。这时一定要用流,一点一点地将数据库的大文本内容流进来。
3、大二进制数据
案例2,向数据库的表中插入或获取大二进制数据。
准备:
l com.hbsi.utils包下的DBManager类负责获得连接以及资源的释放。
l 属性文件db.properties保存了建立连接时需要的参数。
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demodb
username=root
password=root
l MySQL的驱动
l src目录下一个图片文件1.jpg
l 创建表
create table testblob
(
id int primary key auto_increment,
images blob
);
编写代码:Demo2.java
public void insert(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql = "insert into testblob(images) values(?)";
st = conn.prepareStatement(sql);
String path = Demo1.class.getClassLoader().getResource("1.jpg").getPath();
File f = new File(path);
st.setBinaryStream(1, new FileInputStream(path), (int) f.length());
int num = st.executeUpdate();
if(num>0){
System.out.println("插入成功!!");
}
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}
代码运行后,在mysql中输入select * from testblob;语句查看图片,看不了,这时即使能看也是乱码,而且停不了,很难退出来。可以用mysql的工具软件来查看。或是通过如下的代码查看:
public void find(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql = "select images from testblob where id=1";
st = conn.prepareStatement(sql);
rs = st.executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("images");
byte buffer[] = new byte[1024];
int len = 0;
FileOutputStream out = new FileOutputStream("c:\\1.jpg");
while((len=in.read(buffer))>0){
out.write(buffer, 0, len);
}
in.close();
out.close();
}
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}
注意:今天给大家讲的大数据的处理在实际的项目开发中不能用。同学们想一想如果真的向数据库中存入大数据,比如1G的电影,需要通过连接向数据库发送1G的数据,或是从数据库中读取1G的数据,这就导致该用户占用连接的时间超长,连接是数据库非常宝贵的资源,你占用了连接,别人就占用不了。因此在实际的开发中,为了提高数据库的访问性能,我们通常不会将大数据存入到数据库中,而是把它们存到硬盘中,数据库中只存储大数据文件的路径。
二、批处理
1、业务场景:当需要向数据库发送一批SQL语句执行时,应避免向数据库一条条的发送执行,而应采用JDBC的批处理机制,以提升执行效率。
2、实现批处理有两种方式。
l 第一种方式:Statement.addBatch(sql) (Statement中有一个集合属性list存储所有的sql),执行批处理SQL语句
executeBatch()方法:执行批处理命令
clearBatch()方法:清除批处理命令
案例3,批处理。
准备:
l com.hbsi.utils包下的DBManager类负责获得连接以及资源的释放。
l 属性文件db.properties保存了建立连接时需要的参数。
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demodb
username=root
password=root
l MySQL的驱动
l 创建表
create table testbatch
(
id int primary key,
name varchar(100)
) ;
代码编写:Demo3.java
public void test1() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql1 = "insert into testbatch(id,name) values(1,'aa')";
String sql2 = "insert into testbatch(id,name) values(2,'bb')";
String sql3 = "delete from testbatch where id=1";
st = conn.createStatement();
st.addBatch(sql1);//把sql语句加入到批中
st.addBatch(sql2);
st.addBatch(sql3);
//该方法返回值为int[],返回的是每条sql语句执行后对表中记录的影响行数
st.executeBatch();
st.clearBatch();
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}
l 采用Statement.addBatch(sql)方式实现批处理的优点:可以向数据库发送多条不同的SQL语句。缺点一是SQL语句没有预编译,二是当向数据库发送多条语句相同,但仅参数不同的SQL语句时,需重复写上很多条SQL语句。例如:
Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);
l 实现批处理的第二种方式:PreparedStatement.addBatch()
案例4,向数据库的表中插入1亿条记录。
public void test2() {
long starttime = System.currentTimeMillis();
Connection conn = null;
PreparedStatement st = null; //list
ResultSet rs = null;
try{
conn = DBManager.getConnection();
String sql = "insert into testbatch(id,name) values(?,?)";
st = conn.prepareStatement(sql);
for(int i=0;i<10000004;i++){
st.setInt(1, i);
st.setString(2, "aa" + i);
st.addBatch();
if(i%1000==0){
st.executeBatch();
st.clearBatch();
}
}
st.executeBatch();//为了保证最后那4条sql语句也会被提交
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
long endtime = System.currentTimeMillis();
System.out.println("共花了: " + (endtime-starttime)/1000 + "秒");
}
代码段:conn = DBManager.getConnection();
String sql = "insert into testbatch(id,name) values(?,?)";
st = conn.prepareStatement(sql);//预编译一下sql语句
st.setInt(1, 1);
st.setString(2, "aa");
//写到这在st中就有一条完整的sql语句了,因此可以加入批中
st.addBatch();
我们可以将上面的三条语句加入循环中即可向st中添加多条sql语句,所以我们可以写一个循环,循环1亿次,添加1亿条记录。即:
for(int i=0;i<10000000;i++){
st.setInt(1, i);
st.setString(2, "aa" + i);
st.addBatch();
}
这样写完后执行,发现数据库的表中并没有我们想象的1亿条记录。为什么呢?
st语句对象中有一个list对象保存加入的sql语句,每条sql语句是要占一定内存的(8~9个字节),当你加1亿条sql语句时会占多少内存?内存一下就崩溃了。因此千万不要直接把这一亿条sql语句都加入到批中,使用如下的代码:
for(int i=0;i<10000000;i++){
st.setInt(1, i);
st.setString(2, "aa" + i);
st.addBatch();
if(i%1000==0){//把1000条记录做成一批
st.executeBatch();//提交给mysql去执行
st.clearBatch();//提交后,要把批中的sql语句清掉
}
}
这样还有问题:如果是循环1千万零4次,那最后的4条sql语句会提交给MySQL执行吗?不会!因此在该循环结束后还要有一条st.executeBatch()语句的执行。
这个程序写完后运行时花费的时间会很长,差不多3-4个小时。而在oracle下会很快,几分钟吧。
l 采用PreparedStatement.addBatch()实现批处理
• 优点:发送的是预编译后的SQL语句,执行效率高。
• 缺点:只能应用在SQL语句相同,但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据,或批量更新表的数据。
三、如何获取数据库自动生成的主键
这部分内容用在哪里呢?例如,老公表和老婆表
老公表
id | name |
|
… | … |
|
4 | 张三 |
|
|
|
|
老婆表
id | name | h_id |
1 | aaa | 4 |
|
|
|
|
|
|
老公表中id是自动增长的,向老公表中插入一条记录,姓名为“张三”,id自动增长为4。这时向老婆表中插入一条记录,姓名为“aaa”,她老公为“张三”,这时我们就需要获取老公表中自动增长的id值去更新老婆表中的记录。
案例:
create database jdbc character set utf8 collate utf8_general_ci;
use jdbc;
create table users(
id int primary key auto_increment,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
)
现在向该表中插入一行记录,然后得到自动增长的id。
insert into users(name,password,email,birthday) values('zs','1234','zs@sina.com','1990-1-1');
insert into users(name,password,email,birthday) values('lisi','1234','lisi@sina.com','1990-3-1');
编写Java代码:Demo4.java
Connection conn = JdbcUtil.getConnection();
String sql = "insert into user(name,password,email,birthday)
values('abc','123','abc@sina.com','1978-08-08')";
PreparedStatement st = conn.
prepareStatement(sql,Statement.RETURN_GENERATED_KEYS );
//该Statement.RETURN_GENERATED_KEYS参数可带可不带,不带该参数时也可得到自动增长的id,因为MySQL的驱动默认就是可以返回自动增长的id的。如果得不到自动增长的id,你就要小心了,就需要传递该参数。该参数的取值还可以是Statement.NO_GENERATED_KEY,意思相反。但是当我们真的设置为NO_GENERATED_KEY时,还是可以返回自动增长的id,这时因为JDBC只是一种规范,mysql有没有按照规范去实现?从这里可以看出mysql根本没有安装规范去实现。
st.executeUpdate();
//上面的语句执行完,就向数据库的表中添加了一条记录,我们想获得刚添加的记录自动增长的id,方法:
ResultSet rs = st.getGeneratedKeys(); //得到插入行的主键,结果集中只有一条记录
if(rs.next())
System.out.println(rs.getObject(1)); //rs.getInt(1)
四、JDBC调用存储过程
存储过程类似java中的函数或方法,数据库是用于存储数据的,我们可以在数据库中编写函数或方法对数据进行处理,这些函数或方法就是存储过程。现在java存储过程用的不多了。主要原因是分层的结构出现,业务逻辑在service层处理,我们就不用在数据库中编写存储过程来对数据进行处理。以前我们在数据库中编写存储过程,在java程序中我们直接就调用存储过程来取数据。现在在金融证券业中,存储过程用的还是比较多的,为什么呢,因为它不敢让你去写数据的处理。比如银行,算利息,它不敢让程序员去写处理数据的方法,一旦出错就是大错,即使让程序员去写,也得经过专家委员会的评估等。因此这些数据的处理都是在数据库的存储过程中去做。
可以写一个简单的存储过程:
delimiter $$
create procedure demoSp(in inputParam varchar(255),inout inOutParam varchar(255))
begin
Select concat("zyxw---",inputParam) into inOutParam;
end $$
delimiter ;
public static void main(String[] args) {
Connection conn = null;
CallableStatement st = null; //list
ResultSet rs = null;
try{
conn = DBManager.getConnection();
st = conn.prepareCall("{call demoSp(?,?)}");
st.setString(1, "aaaaaaaaaaaa");
st.registerOutParameter(2, Types.VARCHAR);
st.execute();
System.out.println(st.getString(2));
}catch (Exception e) {
throw new RuntimeException(e);
}finally{
DBManager.release(conn, st, rs);
}
}