package com.yucheng.jdbc;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.print.attribute.standard.OutputDeviceAssigned;
import org.junit.Test;
public class JdbcTest {
/**
* 读取Blob数据
*/
@Test
public void testReadBlob(){
Connection conn =null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
String sql="select * from testBlob where id=1";
preStatement=conn.prepareStatement(sql);
resultSet=preStatement.executeQuery();
if(resultSet.next()){
int id=resultSet.getInt(1);
String name=resultSet.getString(2);
int age=resultSet.getInt(3);
Blob pic=resultSet.getBlob(4);
InputStream in=pic.getBinaryStream();
OutputStream out=new FileOutputStream("out.jpg");
byte[] buffer =new byte[1024];
int len=0;
while((len=in.read(buffer))!=-1){
out.write(buffer);
out.flush();
}
out.close();
in.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
}
/**
* 插入Blob类型的数据,使用的是PrepareStatement的setBlob(index,stream)方法
*/
@Test
public void testInsertBlob(){
Connection conn =null;
PreparedStatement preStatement=null;
try {
conn=JDBCTools.getConnecition();
String sql="insert into testBlob(name,age,pic) values(?,?,?)";
preStatement=conn.prepareStatement(sql);
preStatement.setString(1, "魅力");
preStatement.setInt(2, 22);
InputStream pic=new FileInputStream("hello.jpg");
preStatement.setBlob(3,pic);
preStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, null);
}
}
/**
* 得到主键值
*/
@Test
public void testGetKeyValue() {
Connection conn=null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
String sql="insert into person(name) values(?)";
preStatement=conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
preStatement.setString(1, "meimei");
preStatement.executeUpdate();
resultSet=preStatement.getGeneratedKeys();
if(resultSet.next()){
System.out.println(resultSet.getObject(1));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
}
}
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.print.attribute.standard.OutputDeviceAssigned;
import org.junit.Test;
public class JdbcTest {
/**
* 读取Blob数据
*/
@Test
public void testReadBlob(){
Connection conn =null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
String sql="select * from testBlob where id=1";
preStatement=conn.prepareStatement(sql);
resultSet=preStatement.executeQuery();
if(resultSet.next()){
int id=resultSet.getInt(1);
String name=resultSet.getString(2);
int age=resultSet.getInt(3);
Blob pic=resultSet.getBlob(4);
InputStream in=pic.getBinaryStream();
OutputStream out=new FileOutputStream("out.jpg");
byte[] buffer =new byte[1024];
int len=0;
while((len=in.read(buffer))!=-1){
out.write(buffer);
out.flush();
}
out.close();
in.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
}
/**
* 插入Blob类型的数据,使用的是PrepareStatement的setBlob(index,stream)方法
*/
@Test
public void testInsertBlob(){
Connection conn =null;
PreparedStatement preStatement=null;
try {
conn=JDBCTools.getConnecition();
String sql="insert into testBlob(name,age,pic) values(?,?,?)";
preStatement=conn.prepareStatement(sql);
preStatement.setString(1, "魅力");
preStatement.setInt(2, 22);
InputStream pic=new FileInputStream("hello.jpg");
preStatement.setBlob(3,pic);
preStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, null);
}
}
/**
* 得到主键值
*/
@Test
public void testGetKeyValue() {
Connection conn=null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
String sql="insert into person(name) values(?)";
preStatement=conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
preStatement.setString(1, "meimei");
preStatement.executeUpdate();
resultSet=preStatement.getGeneratedKeys();
if(resultSet.next()){
System.out.println(resultSet.getObject(1));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
}
}