时间：2021-07-01 10:21:17 帮助过：2人阅读
public class BatchDemo {
/*
create database day16;
use day16;
create table t1(
id int primary key,
name varchar(100)
);
*/
//向数据库中插入2条记录，再把第一条删除掉
//Statement可以批处理语句不同的。
@Test
public void test1() throws Exception{
Connection conn = JdbcUtil.getConnection();
Statement stmt = conn.createStatement();
String sql1 = "insert into t1 values(1,‘aaa1‘)";
String sql2 = "insert into t1 values(2,‘aaa2‘)";
String sql3 = "delete from t1 where id=1";
stmt.addBatch(sql1);//Statement实例内部有一个List，sql语句加到List中了
stmt.addBatch(sql2);
stmt.addBatch(sql3);
int[] ii = stmt.executeBatch();//返回值是一个数组，数组的元素为每条语句影响到的行数
for(int i:ii)
System.out.println(i);
JdbcUtil.release(null, stmt, conn);
}
//插入10条记录。PreparedStatement 适合语句相同，只是参数不通的情况
@Test
public void test2() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t1 values (?,?)");
for(int i=0;i<10;i++){
stmt.setInt(1, i+1);
stmt.setString(2, "aaaa"+(i+1));
stmt.addBatch();
}
stmt.executeBatch();
JdbcUtil.release(null, stmt, conn);
}
//插入1000001条记录。PreparedStatement 适合语句相同，只是参数不通的情况
@Test
public void test3() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t1 values (?,?)");
for(int i=0;i<1000001;i++){
stmt.setInt(1, i+1);
stmt.setString(2, "aaaa"+(i+1));
stmt.addBatch();
if(i%1000==0){
stmt.executeBatch();
stmt.clearBatch();//清理缓存
}
}
stmt.executeBatch();
JdbcUtil.release(null, stmt, conn);
}
}
LOB：Large Object
Clob:Character Large Object字符（小说）
Blob:Binary Large Object二进制
//大数据的存取
public class LobDemo {
/*
create table t2(
id int,
content longtext
);
*/
@Test
public void test1() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t2 values (?,?)");
stmt.setInt(1, 1);
//以流的方式
File file = new File("src/jpm.txt");
Reader reader = new FileReader(file);
stmt.setCharacterStream(2, reader, (int)file.length());//PreparedStatement的实现是由数据库驱动提供的
//MySQL:setCharacterStream(int,Reader,long);根本没有实现。
//MySQL根本不支持那么大的数据。
stmt.executeUpdate();
JdbcUtil.release(null, stmt, conn);
}
//取大文本数据
@Test
public void test2() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("select * from t2 where id=1");
ResultSet rs = stmt.executeQuery();
if(rs.next()){
Reader r = rs.getCharacterStream("content");
//内容保存D盘的1.txt文件中
Writer w = new FileWriter("d:/1.txt");
int len = -1;
char c[] = new char[1024];
while((len=r.read(c))!=-1){
w.write(new String(c), 0, len);
}
r.close();
w.close();
}
JdbcUtil.release(rs, stmt, conn);
}
/*
create table t3(
id int,
content longblob
);
*/
@Test
public void test3() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into t3 values (?,?)");
stmt.setInt(1, 1);
//以流的方式
InputStream in = new FileInputStream("src/26.jpg");
stmt.setBinaryStream(2, in, in.available());
stmt.executeUpdate();
JdbcUtil.release(null, stmt, conn);
}
@Test
public void test4() throws Exception{
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("select * from t3 where id=1");
ResultSet rs = stmt.executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("content");
OutputStream out = new FileOutputStream("d:/wife.jpg");
int len = -1;
byte b[] = new byte[1024];
while((len=in.read(b))!=-1){
out.write(b,0,len);
}
in.close();
out.close();
}
JdbcUtil.release(null, stmt, conn);
}
}