Java/Database SQL JDBC/Blob Binary Data JDBC

Материал из Java эксперт
Перейти к: навигация, поиск

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>