Java/Database SQL JDBC/Blob Binary Data JDBC
Содержание
- 1 Blob and JDBC: Image
- 2 Blob: Image 2
- 3 Blob: image 3
- 4 Blob: JDBC deals with Binary Data
- 5 Demo Display Binary Data From Database
- 6 Getting and Inserting Binary Data into an Database Table
- 7 Getting BLOB Data from a Database Table: how to retrieves bytes from a BLOB.
- 8 Insert an Image
- 9 Inserting Image in Database Table
- 10 Insert picture to MySQL
- 11 Materialize binary data onto client
- 12 Read BLOBs data from database
- 13 Read CLOBs data from database
- 14 Retrieve an Image
- 15 Store and retrieve an object from a table
- 16 Store BLOBs data into database
- 17 Store CLOBs data into database?
Blob and JDBC: Image
<source lang="java">
/* MySQL and Java Developer"s Guide Mark Matthews, Jim Cole, Joseph D. Gradecki Publisher Wiley, Published February 2003, ISBN 0471269239
- /
import java.awt.Container; import java.awt.FlowLayout; import java.awt.Graphics2D; import java.awt.GridLayout; import java.awt.Image; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; import java.awt.geom.AffineTransform; import java.awt.image.BufferedImage; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.Vector; import javax.swing.ImageIcon; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JList; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; import javax.swing.JTextField; public class IDlookGetStream extends JFrame {
private JButton getAccountButton, updateAccountButton, insertAccountButton, nextButton, previousButton, lastButton, firstButton; private JList accountNumberList; private JTextField accountIDText, nailFileText, thumbIDText; private JTextArea errorText; private Connection connection; private Statement statement; private ResultSet rs; private ImageIcon icon = null; private ImageIcon iconThumbnail = null; JLabel photographLabel; public IDlookGetStream() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) { System.err.println("Unable to find and load driver"); System.exit(1); } } private void loadAccounts() { Vector v = new Vector(); try { rs = statement.executeQuery("SELECT * FROM thumbnail"); while (rs.next()) { v.addElement(rs.getString("acc_id")); } } catch (SQLException e) { displaySQLErrors(e); } accountNumberList.setListData(v); } private void buildGUI() { Container c = getContentPane(); c.setLayout(new FlowLayout()); accountNumberList = new JList(); loadAccounts(); accountNumberList.setVisibleRowCount(2); JScrollPane accountNumberListScrollPane = new JScrollPane( accountNumberList); //Do Get Account Button getAccountButton = new JButton("Get Account"); getAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { rs.beforeFirst(); while (rs.next()) { if (rs.getString("acc_id").equals( accountNumberList.getSelectedValue())) break; } if (!rs.isAfterLast()) { accountIDText.setText(rs.getString("acc_id")); thumbIDText.setText(rs.getString("thumb_id")); Blob blob = rs.getBlob("pic"); int b; InputStream bis = rs.getBinaryStream("pic"); FileOutputStream f = new FileOutputStream("pic.jpg"); while ((b = bis.read()) >= 0) { f.write(b); } f.close(); bis.close(); icon = new ImageIcon(blob.getBytes(1L, (int) blob .length())); createThumbnail(); photographLabel.setIcon(iconThumbnail); } } catch (Exception selectException) { displaySQLErrors(selectException); } } }); //Do Update Account Button updateAccountButton = new JButton("Update Account"); updateAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.updateBytes("thumbnail.pic", bytes); rs.updateRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); //Do insert Account Button insertAccountButton = new JButton("Insert Account"); insertAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.moveToInsertRow(); rs.updateInt("thumb_id", Integer.parseInt(thumbIDText .getText())); rs.updateInt("acc_id", Integer.parseInt(accountIDText .getText())); rs.updateBytes("pic", bytes); rs.updateObject("sysobject", null); rs.updateTimestamp("ts", new Timestamp(0)); rs.updateTimestamp("act_ts", new Timestamp( new java.util.Date().getTime())); rs.insertRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); photographLabel = new JLabel(); photographLabel.setHorizontalAlignment(JLabel.CENTER); photographLabel.setVerticalAlignment(JLabel.CENTER); photographLabel.setVerticalTextPosition(JLabel.CENTER); photographLabel.setHorizontalTextPosition(JLabel.CENTER); JPanel first = new JPanel(new GridLayout(4, 1)); first.add(accountNumberListScrollPane); first.add(getAccountButton); first.add(updateAccountButton); first.add(insertAccountButton); accountIDText = new JTextField(15); thumbIDText = new JTextField(15); errorText = new JTextArea(5, 15); errorText.setEditable(false); JPanel second = new JPanel(); second.setLayout(new GridLayout(2, 1)); second.add(thumbIDText); second.add(accountIDText); JPanel third = new JPanel(); third.add(new JScrollPane(errorText)); nailFileText = new JTextField(25); c.add(first); c.add(second); c.add(third); c.add(nailFileText); c.add(photographLabel); setSize(500, 500); show(); } public void connectToDB() { try { connection = DriverManager .getConnection("jdbc:mysql://192.168.1.25/identification?user=spider&password=spider"); statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } catch (SQLException connectException) { System.out.println(connectException.getMessage()); System.out.println(connectException.getSQLState()); System.out.println(connectException.getErrorCode()); System.exit(1); } } private void displaySQLErrors(Exception e) { errorText.append("Exception: " + e.getMessage() + "\n"); // errorText.append("State: " + e.getSQLState() + "\n"); // errorText.append("VendorError: " + e.getErrorCode() + "\n"); } private void init() { connectToDB(); } private void createThumbnail() { int maxDim = 350; try { Image inImage = icon.getImage(); double scale = (double) maxDim / (double) inImage.getHeight(null); if (inImage.getWidth(null) > inImage.getHeight(null)) { scale = (double) maxDim / (double) inImage.getWidth(null); } int scaledW = (int) (scale * inImage.getWidth(null)); int scaledH = (int) (scale * inImage.getHeight(null)); BufferedImage outImage = new BufferedImage(scaledW, scaledH, BufferedImage.TYPE_INT_RGB); AffineTransform tx = new AffineTransform(); if (scale < 1.0d) { tx.scale(scale, scale); } Graphics2D g2d = outImage.createGraphics(); g2d.drawImage(inImage, tx, null); g2d.dispose(); iconThumbnail = new ImageIcon(outImage); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { IDlookGetStream id = new IDlookGetStream(); id.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); id.init(); id.buildGUI(); }
}
</source>
Blob: Image 2
<source lang="java">
/* MySQL and Java Developer"s Guide Mark Matthews, Jim Cole, Joseph D. Gradecki Publisher Wiley, Published February 2003, ISBN 0471269239
- /
import java.awt.Container; import java.awt.FlowLayout; import java.awt.Graphics2D; import java.awt.GridLayout; import java.awt.Image; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; import java.awt.geom.AffineTransform; import java.awt.image.BufferedImage; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.Vector; import javax.swing.ImageIcon; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JList; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; import javax.swing.JTextField; public class IDlookBlob extends JFrame {
private JButton getAccountButton, updateAccountButton, insertAccountButton, nextButton, previousButton, lastButton, firstButton; private JList accountNumberList; private JTextField accountIDText, nailFileText, thumbIDText; private JTextArea errorText; private Connection connection; private Statement statement; private ResultSet rs; private ImageIcon icon = null; private ImageIcon iconThumbnail = null; JLabel photographLabel; public IDlookBlob() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) { System.err.println("Unable to find and load driver"); System.exit(1); } } private void loadAccounts() { Vector v = new Vector(); try { rs = statement.executeQuery("SELECT * FROM thumbnail"); while (rs.next()) { v.addElement(rs.getString("acc_id")); } } catch (SQLException e) { displaySQLErrors(e); } accountNumberList.setListData(v); } private void buildGUI() { Container c = getContentPane(); c.setLayout(new FlowLayout()); accountNumberList = new JList(); loadAccounts(); accountNumberList.setVisibleRowCount(2); JScrollPane accountNumberListScrollPane = new JScrollPane( accountNumberList); //Do Get Account Button getAccountButton = new JButton("Get Account"); getAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { rs.beforeFirst(); while (rs.next()) { if (rs.getString("acc_id").equals( accountNumberList.getSelectedValue())) break; } if (!rs.isAfterLast()) { accountIDText.setText(rs.getString("acc_id")); thumbIDText.setText(rs.getString("thumb_id")); Blob b = rs.getBlob("pic"); icon = new ImageIcon(b.getBytes(1L, (int) b.length())); createThumbnail(); photographLabel.setIcon(iconThumbnail); } } catch (SQLException selectException) { displaySQLErrors(selectException); } } }); //Do Update Account Button updateAccountButton = new JButton("Update Account"); updateAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.updateBytes("thumbnail.pic", bytes); rs.updateRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); //Do insert Account Button insertAccountButton = new JButton("Insert Account"); insertAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.moveToInsertRow(); rs.updateInt("thumb_id", Integer.parseInt(thumbIDText .getText())); rs.updateInt("acc_id", Integer.parseInt(accountIDText .getText())); rs.updateBytes("pic", bytes); rs.updateObject("sysobject", null); rs.updateTimestamp("ts", new Timestamp(0)); rs.updateTimestamp("act_ts", new Timestamp( new java.util.Date().getTime())); rs.insertRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); photographLabel = new JLabel(); photographLabel.setHorizontalAlignment(JLabel.CENTER); photographLabel.setVerticalAlignment(JLabel.CENTER); photographLabel.setVerticalTextPosition(JLabel.CENTER); photographLabel.setHorizontalTextPosition(JLabel.CENTER); JPanel first = new JPanel(new GridLayout(4, 1)); first.add(accountNumberListScrollPane); first.add(getAccountButton); first.add(updateAccountButton); first.add(insertAccountButton); accountIDText = new JTextField(15); thumbIDText = new JTextField(15); errorText = new JTextArea(5, 15); errorText.setEditable(false); JPanel second = new JPanel(); second.setLayout(new GridLayout(2, 1)); second.add(thumbIDText); second.add(accountIDText); JPanel third = new JPanel(); third.add(new JScrollPane(errorText)); nailFileText = new JTextField(25); c.add(first); c.add(second); c.add(third); c.add(nailFileText); c.add(photographLabel); setSize(500, 500); show(); } public void connectToDB() { try { connection = DriverManager .getConnection("jdbc:mysql://192.168.1.25/identification?user=spider&password=spider"); statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } catch (SQLException connectException) { System.out.println(connectException.getMessage()); System.out.println(connectException.getSQLState()); System.out.println(connectException.getErrorCode()); System.exit(1); } } private void displaySQLErrors(SQLException e) { errorText.append("SQLException: " + e.getMessage() + "\n"); errorText.append("SQLState: " + e.getSQLState() + "\n"); errorText.append("VendorError: " + e.getErrorCode() + "\n"); } private void init() { connectToDB(); } private void createThumbnail() { int maxDim = 350; try { Image inImage = icon.getImage(); double scale = (double) maxDim / (double) inImage.getHeight(null); if (inImage.getWidth(null) > inImage.getHeight(null)) { scale = (double) maxDim / (double) inImage.getWidth(null); } int scaledW = (int) (scale * inImage.getWidth(null)); int scaledH = (int) (scale * inImage.getHeight(null)); BufferedImage outImage = new BufferedImage(scaledW, scaledH, BufferedImage.TYPE_INT_RGB); AffineTransform tx = new AffineTransform(); if (scale < 1.0d) { tx.scale(scale, scale); } Graphics2D g2d = outImage.createGraphics(); g2d.drawImage(inImage, tx, null); g2d.dispose(); iconThumbnail = new ImageIcon(outImage); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { IDlookBlob id = new IDlookBlob(); id.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); id.init(); id.buildGUI(); }
}
</source>
Blob: image 3
<source lang="java">
/* MySQL and Java Developer"s Guide Mark Matthews, Jim Cole, Joseph D. Gradecki Publisher Wiley, Published February 2003, ISBN 0471269239
- /
import java.awt.Container; import java.awt.FlowLayout; import java.awt.Graphics2D; import java.awt.GridLayout; import java.awt.Image; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; import java.awt.geom.AffineTransform; import java.awt.image.BufferedImage; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.Vector; import javax.swing.ImageIcon; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JList; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; import javax.swing.JTextField; public class IDlook extends JFrame {
private JButton getAccountButton, updateAccountButton, insertAccountButton, nextButton, previousButton, lastButton, firstButton; private JList accountNumberList; private JTextField accountIDText, nailFileText, thumbIDText; private JTextArea errorText; private Connection connection; private Statement statement; private ResultSet rs; private ImageIcon icon = null; private ImageIcon iconThumbnail = null; JLabel photographLabel; public IDlook() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) { System.err.println("Unable to find and load driver"); System.exit(1); } } private void loadAccounts() { Vector v = new Vector(); try { rs = statement.executeQuery("SELECT * FROM thumbnail"); while (rs.next()) { v.addElement(rs.getString("acc_id")); } } catch (SQLException e) { displaySQLErrors(e); } accountNumberList.setListData(v); } private void buildGUI() { Container c = getContentPane(); c.setLayout(new FlowLayout()); accountNumberList = new JList(); loadAccounts(); accountNumberList.setVisibleRowCount(2); JScrollPane accountNumberListScrollPane = new JScrollPane( accountNumberList); //Do Get Account Button getAccountButton = new JButton("Get Account"); getAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { rs.beforeFirst(); while (rs.next()) { if (rs.getString("acc_id").equals( accountNumberList.getSelectedValue())) break; } if (!rs.isAfterLast()) { accountIDText.setText(rs.getString("acc_id")); thumbIDText.setText(rs.getString("thumb_id")); icon = new ImageIcon(rs.getBytes("pic")); createThumbnail(); photographLabel.setIcon(iconThumbnail); } } catch (SQLException selectException) { displaySQLErrors(selectException); } } }); //Do Update Account Button updateAccountButton = new JButton("Update Account"); updateAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.updateBytes("thumbnail.pic", bytes); rs.updateRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); //Do insert Account Button insertAccountButton = new JButton("Insert Account"); insertAccountButton.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText .getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.moveToInsertRow(); rs.updateInt("thumb_id", Integer.parseInt(thumbIDText .getText())); rs.updateInt("acc_id", Integer.parseInt(accountIDText .getText())); rs.updateBytes("pic", bytes); rs.updateObject("sysobject", null); rs.updateTimestamp("ts", new Timestamp(0)); rs.updateTimestamp("act_ts", new Timestamp( new java.util.Date().getTime())); rs.insertRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch (SQLException insertException) { displaySQLErrors(insertException); } catch (Exception generalE) { generalE.printStackTrace(); } } }); photographLabel = new JLabel(); photographLabel.setHorizontalAlignment(JLabel.CENTER); photographLabel.setVerticalAlignment(JLabel.CENTER); photographLabel.setVerticalTextPosition(JLabel.CENTER); photographLabel.setHorizontalTextPosition(JLabel.CENTER); JPanel first = new JPanel(new GridLayout(4, 1)); first.add(accountNumberListScrollPane); first.add(getAccountButton); first.add(updateAccountButton); first.add(insertAccountButton); accountIDText = new JTextField(15); thumbIDText = new JTextField(15); errorText = new JTextArea(5, 15); errorText.setEditable(false); JPanel second = new JPanel(); second.setLayout(new GridLayout(2, 1)); second.add(thumbIDText); second.add(accountIDText); JPanel third = new JPanel(); third.add(new JScrollPane(errorText)); nailFileText = new JTextField(25); c.add(first); c.add(second); c.add(third); c.add(nailFileText); c.add(photographLabel); setSize(500, 500); show(); } public void connectToDB() { try { connection = DriverManager .getConnection("jdbc:mysql://192.168.1.25/identification?user=spider&password=spider"); statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } catch (SQLException connectException) { System.out.println(connectException.getMessage()); System.out.println(connectException.getSQLState()); System.out.println(connectException.getErrorCode()); System.exit(1); } } private void displaySQLErrors(SQLException e) { errorText.append("SQLException: " + e.getMessage() + "\n"); errorText.append("SQLState: " + e.getSQLState() + "\n"); errorText.append("VendorError: " + e.getErrorCode() + "\n"); } private void init() { connectToDB(); } private void createThumbnail() { int maxDim = 350; try { Image inImage = icon.getImage(); double scale = (double) maxDim / (double) inImage.getHeight(null); if (inImage.getWidth(null) > inImage.getHeight(null)) { scale = (double) maxDim / (double) inImage.getWidth(null); } int scaledW = (int) (scale * inImage.getWidth(null)); int scaledH = (int) (scale * inImage.getHeight(null)); BufferedImage outImage = new BufferedImage(scaledW, scaledH, BufferedImage.TYPE_INT_RGB); AffineTransform tx = new AffineTransform(); if (scale < 1.0d) { tx.scale(scale, scale); } Graphics2D g2d = outImage.createGraphics(); g2d.drawImage(inImage, tx, null); g2d.dispose(); iconThumbnail = new ImageIcon(outImage); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { IDlook id = new IDlook(); id.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); id.init(); id.buildGUI(); }
}
</source>
Blob: JDBC deals with Binary Data
<source lang="java">
/* Database Programming with JDBC and Java, Second Edition By George Reese ISBN: 1-56592-616-1 Publisher: O"Reilly
- /
import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; /**
* Example 4.2. */
public class Blobs {
public static void main(String args[]) { if (args.length != 1) { System.err.println("Syntax: <java Blobs [driver] [url] " + "[uid] [pass] [file]"); return; } try { Class.forName(args[0]).newInstance(); Connection con = DriverManager.getConnection(args[1], args[2], args[3]); File f = new File(args[4]); PreparedStatement stmt; if (!f.exists()) { // if the file does not exist // retrieve it from the database and write it to the named file ResultSet rs; stmt = con.prepareStatement("SELECT blobData " + "FROM BlobTest " + "WHERE fileName = ?"); stmt.setString(1, args[0]); rs = stmt.executeQuery(); if (!rs.next()) { System.out.println("No such file stored."); } else { Blob b = rs.getBlob(1); BufferedOutputStream os; os = new BufferedOutputStream(new FileOutputStream(f)); os.write(b.getBytes(0, (int) b.length()), 0, (int) b .length()); os.flush(); os.close(); } } else { // otherwise read it and save it to the database FileInputStream fis = new FileInputStream(f); byte[] tmp = new byte[1024]; byte[] data = null; int sz, len = 0; while ((sz = fis.read(tmp)) != -1) { if (data == null) { len = sz; data = tmp; } else { byte[] narr; int nlen; nlen = len + sz; narr = new byte[nlen]; System.arraycopy(data, 0, narr, 0, len); System.arraycopy(tmp, 0, narr, len, sz); data = narr; len = nlen; } } if (len != data.length) { byte[] narr = new byte[len]; System.arraycopy(data, 0, narr, 0, len); data = narr; } stmt = con.prepareStatement("INSERT INTO BlobTest(fileName, " + "blobData) VALUES(?, ?)"); stmt.setString(1, args[0]); stmt.setObject(2, data); stmt.executeUpdate(); f.delete(); } con.close(); } catch (Exception e) { e.printStackTrace(); } }
}
</source>
Demo Display Binary Data From Database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class DemoDisplayBinaryDataFromDatabase {
public static Connection getConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:databaseName"; String username = "name"; String password = "password"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String args[]) throws Exception { Connection conn = null; ResultSet rs = null; PreparedStatement pstmt = null; String query = "SELECT raw_column, long_raw_column FROM binary_table WHERE id = ?"; try { conn = getConnection(); Object[] results = new Object[2]; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0001"); rs = pstmt.executeQuery(); rs.next(); // materialize binary data onto client results[0] = rs.getBytes("RAW_COLUMN"); results[1] = rs.getBytes("LONG_RAW_COLUMN"); } finally { rs.close(); pstmt.close(); conn.close(); } }
}
</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>
Inserting Image in Database Table
<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 { String url = "jdbc:mysql://localhost:3306/"; String dbName = "javatutorial"; String userName = "root"; String password = "root"; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url + dbName, userName, password); File imgfile = new File("images.jpg"); FileInputStream fin = new FileInputStream(imgfile); PreparedStatement pre = con.prepareStatement("insert into Image values(?,?,?)"); pre.setInt(1, 5); pre.setString(2, "A"); pre.setBinaryStream(3, fin, (int) imgfile.length()); pre.executeUpdate(); pre.close(); con.close(); }
}
</source>
Insert picture to MySQL
<source lang="java">
/* Defining the Table: Oracle and MySql create table MyPictures (
id INT PRIMARY KEY, name VARCHAR(0), photo BLOB
);
- /
import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class InsertPictureToMySql {
public static void main(String[] args) throws Exception, IOException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root"); String INSERT_PICTURE = "insert into MyPictures(id, name, photo) values (?, ?, ?)"; FileInputStream fis = null; PreparedStatement ps = null; try { conn.setAutoCommit(false); File file = new File("myPhoto.png"); fis = new FileInputStream(file); ps = conn.prepareStatement(INSERT_PICTURE); ps.setString(1, "001"); ps.setString(2, "name"); ps.setBinaryStream(3, fis, (int) file.length()); ps.executeUpdate(); conn.rumit(); } finally { ps.close(); fis.close(); } }
}
</source>
Materialize binary data onto client
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import javax.swing.JPanel; public class DemoDisplayBlobFromDatabase extends JPanel {
public static Connection getConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:databaseName"; String username = "name"; String password = "password"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static void main(String args[]) throws Exception { Connection conn = null; ResultSet rs = null; PreparedStatement pstmt = null; String query = "SELECT blob_column FROM blob_table WHERE id = ?"; try { conn = getConnection(); pstmt = conn.prepareStatement(query); pstmt.setString(1, "0001"); rs = pstmt.executeQuery(); rs.next(); // materialize binary data onto client java.sql.Blob blob = rs.getBlob(1); } finally { rs.close(); pstmt.close(); conn.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 and retrieve an object from a table
<source lang="java">
import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; import java.io.Serializable; 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 args[]) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String URL = "jdbc:odbc:dbname"; Connection dbConn = DriverManager.getConnection(URL, "user", "passw"); Employee employee = new Employee(42, "AA", 9); ByteArrayOutputStream baos = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(baos); oos.writeObject(employee); byte[] employeeAsBytes = baos.toByteArray(); PreparedStatement pstmt = dbConn .prepareStatement("INSERT INTO EMPLOYEE (emp) VALUES(?)"); ByteArrayInputStream bais = new ByteArrayInputStream(employeeAsBytes); pstmt.setBinaryStream(1, bais, employeeAsBytes.length); pstmt.executeUpdate(); pstmt.close(); Statement stmt = dbConn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT emp FROM Employee"); while (rs.next()) { byte[] st = (byte[]) rs.getObject(1); ByteArrayInputStream baip = new ByteArrayInputStream(st); ObjectInputStream ois = new ObjectInputStream(baip); Employee emp = (Employee) ois.readObject(); } stmt.close(); rs.close(); dbConn.close(); }
} class Employee implements Serializable {
int ID; String name; double salary; public Employee(int ID, String name, double salary) { this.ID = ID; this.name = name; this.salary = salary; }
}
</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>