Admin: 1 версия

2010-06-01T05:06:00Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== Blob and Clob data type ==



<source lang="java">
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BlobClobEx {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
Statement stmt = conn.createStatement();
createBlobClobTables(stmt);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");
File file = new File("blob.txt");
FileInputStream fis = new FileInputStream(file);
pstmt.setBinaryStream(1, fis, (int) file.length());
file = new File("clob.txt");
fis = new FileInputStream(file);
pstmt.setAsciiStream(2, fis, (int) file.length());
fis.close();
pstmt.execute();
ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
rs.next();
java.sql.Blob blob = rs.getBlob(2);
java.sql.Clob clob = rs.getClob(3);
byte blobVal[] = new byte[(int) blob.length()];
InputStream blobIs = blob.getBinaryStream();
blobIs.read(blobVal);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
bos.write(blobVal);
blobIs.close();
char clobVal[] = new char[(int) clob.length()];
Reader r = clob.getCharacterStream();
r.read(clobVal);
StringWriter sw = new StringWriter();
sw.write(clobVal);
r.close();
conn.close();
}
public static void createBlobClobTables(Statement stmt) throws Exception {
String Sql = "CREATE TABLE BlobClob(Id NUMBER(3), b BLOB, c CLOB)";
try {
stmt.executeUpdate("DROP TABLE BlobClob");
} catch (SQLException se) {
if (se.getErrorCode() == 942)
System.out.println("Error dropping BlobClob table:" + se.getMessage());
}
if (stmt.executeUpdate(Sql) == 0)
System.out.println("BlobClob table created...");
}
}</source>



== Getting and Inserting Binary Data into an Database Table ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO mysql_all_table (col_binarystream) VALUES(?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
byte[] buffer = "some data".getBytes();
pstmt.setBytes(1, buffer);
pstmt.executeUpdate();
pstmt.close();
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery("SELECT * FROM mysql_all_table");
while (resultSet.next()) {
byte[] bytes = resultSet.getBytes("col_binarystream");
}
}
}</source>



== Getting BLOB Data from a Database Table: how to retrieves bytes from a BLOB. ==



<source lang="java">
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");
if (rs.next()) {
Blob blob = rs.getBlob("col_blob");
long blobLength = blob.length();
int pos = 1; // position is 1-based
int len = 10;
byte[] bytes = blob.getBytes(pos, len);
InputStream is = blob.getBinaryStream();
int b = is.read();
}
}
}</source>



== Insert an Image ==



<source lang="java">
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
public static void main(String[] argv) throws Exception {
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@//server.local:1521/prod", "scott", "tiger");
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("insert into images values (?,?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int) file.length());
ps.executeUpdate();
ps.close();
fis.close();
}
}</source>



== Read BLOBs data from database ==



<source lang="java">
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
static String username = "username";
static String password = "welcome";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "SELECT name, description, image FROM pictures ";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
String description = resultSet.getString(2);
File image = new File("D:\\java.gif");
FileOutputStream fos = new FileOutputStream(image);
byte[] buffer = new byte[1];
InputStream is = resultSet.getBinaryStream(3);
while (is.read(buffer) > 0) {
fos.write(buffer);
}
fos.close();
}
conn.close();
}
}</source>



== Read CLOBs data from database ==



<source lang="java">
import java.io.File;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
private static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
private static String username = "java";
private static String password = "welcome";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement stmt = conn.prepareStatement("SELECT name, description, data FROM documents ");
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
String description = resultSet.getString(2);
File data = new File("C:\\a.txt");
Reader reader = resultSet.getCharacterStream(3);
FileWriter writer = new FileWriter(data);
char[] buffer = new char[1];
while (reader.read(buffer) > 0) {
writer.write(buffer);
}
writer.close();
}
conn.close();
}
}</source>



== Retrieve an Image ==



<source lang="java">
import java.awt.Image;
import java.awt.Toolkit;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.ResultSet;
public class Main {
public static void main(String[] argv) throws Exception {
ResultSet rset = null;
InputStream stream = rset.getBinaryStream(1);
ByteArrayOutputStream output = new ByteArrayOutputStream();
int a1 = stream.read();
while (a1 >= 0) {
output.write((char) a1);
a1 = stream.read();
}
Image myImage = Toolkit.getDefaultToolkit().createImage(output.toByteArray());
output.close();
}
}</source>



== Store BLOBs data into database ==



<source lang="java">
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
static String username = "username";
static String password = "welcome";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
String sql = "INSERT INTO pictures (name, description, image) VALUES (?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "java.gif");
stmt.setString(2, "Java Official Logo");
File image = new File("D:\\a.gif");
FileInputStream fis = new FileInputStream(image);
stmt.setBinaryStream(3, fis, (int) image.length());
stmt.execute();
conn.rumit();
fis.close();
conn.close();
}
}</source>



== Store CLOBs data into database? ==



<source lang="java">
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
private static String username = "yourDatabase";
private static String password = "welcome";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
String sql = "INSERT INTO documents (name, description, data) VALUES (?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "a.txt");
stmt.setString(2, "b");
File data = new File("C:\\a.txt");
FileReader reader = new FileReader(data);
stmt.setCharacterStream(3, reader, (int) data.length());
stmt.execute();
conn.rumit();
reader.close();
conn.close();
}
}</source>

← Предыдущая	Версия 05:06, 1 июня 2010
(нет различий)

Java Tutorial/Database/Blob Clob - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010