How to save image files to mysql with sql code and how to display them with java?

Question

I want to save image files to database by java, file chooser.

My db name is "gallery_images", table name is "image_table", and the columns are "image id(int)"autoincrement, "image(longblob)", and "username(varchar2(45))".

I can add images to my mysql database by workbench to a longblob (right click on longblob cell, "Load Value From File"), and when Ii execute it, it gives me this code:

INSERT INTO `image_table`.`gallery_images` (`image`, `username`) VALUES (?, 'viktor');

The "?" should be the image, so I didn't learn much by this code. When I read a File by java, how could I insert it into my table (with jdbc)?

And when I done with this, I want to query every data from that table:

SELECT * FROM image_table;

I want to save the images from the DB to a List:

private List<Image> images;

I think, I should use the "while(resultset.next()", but how should I get the image from the resultset in the while? Can any one help me with this? Thank you!

(Why) Do you need to save an image in a database? http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — Andreas, Mar 05 '13 at 19:55
I want to create a JAR program to my friend, who should read and insert datas from/to DB, such as like images — victorio, Mar 05 '13 at 19:56
See [`What is the best way to serialize an image (compatible with Swing) from Java to Android?`](http://stackoverflow.com/a/10004271/597657). — Eng.Fouad, Mar 05 '13 at 19:57
This Tutorial will help you understand How to insert an Image and load an Image file from database http://www.youtube.com/watch?v=AZE4A5sT5q4 — Azad, Mar 05 '13 at 20:42

score 1 · Answer 1 · answered Mar 05 '13 at 21:30

Images insertion (from JPG files on local disk) into MySQL db using JDBC:

package dbTest;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.DriverManager;

import com.example.pompeymenu.Dish;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class DishAdder {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub

        int bytesRead;

        try {
                //setting filenames
                File[] filesd = new File[6];
                String fileName[] = new String[] {"1.JPG", "2.JPG", "3.JPG", "4.JPG", "5.JPG", "6.JPG"};
                String dishName[] = new String[] {
                        "Cake with cherries",
                        "Tiramisu",
                        "Cheese cake",
                        "Pana Cotta",
                        "Ice cream",
                        "Apple Pie"
                };

                Connection conn = null;
                String userName = "your_user_name";
                String password = "your_db_password";
                String url = "jdbc:mysql://your_db_url";
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = (Connection) DriverManager.getConnection(url, userName, password);
                //Statement s = null;
                PreparedStatement s = null;
                String qryInsert = "INSERT INTO MENU (DISH_NAME, DISH_IMG) VALUES (?, ?)";                  

                //for (int i=5; i<6; i++) {
                    int i=5;
                    ByteArrayOutputStream bos = new ByteArrayOutputStream();
                    filesd[i] = new File("E:/Android/Images/" + fileName[i]);
                    InputStream is = new FileInputStream(filesd[i]);
                    byte[] b = new byte[8096];
                    while ((bytesRead = is.read(b)) != -1) {
                           bos.write(b);
                    }
                    byte[] bytes = bos.toByteArray();

                    //save byte[] to DB

                        s = (PreparedStatement) conn.prepareStatement(qryInsert);
                        s.setString(1, dishName[i]);
                        s.setBytes(2, bytes);
                        s.executeUpdate();  

                //}

                s.close();
                conn.close();

        } catch (Exception e) { e.printStackTrace(); }  


    }

}

http://benignosales.wordpress.com/2011/10/24/jsf-2-primefaces-pgalleria-exemplo-de-galeria-de-fotos/ — victorio, Mar 06 '13 at 14:42

score 1 · Answer 2 · answered Mar 05 '13 at 21:34

Read images from MySQL db using JDBC, works for me on Android:

import java.sql.DriverManager;
import java.util.ArrayList;

import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import com.mysql.jdbc.Blob;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

/**
 * 
 * Loads products from external MySql database, registered on db4free.net
 * 
 * @author Rodion Altshuler
 *
 */
public class CatalogDBSource implements CatalogSource{

    Connection conn=null;
    String userName = "???";
    String password = "???";
    String url = "jdbc:mysql://???";

     /**
     * Set this flag=true before calling getProducts(), for example, in order to make several requests to DB
     * without re-opening connection each time
     */
    public boolean flag_closeConnection=true; //if set to false, connection after getProducts() will retain opened 


    /**gets products from external DB
     * needs INTERNET permission and MySQL driver       
    */
    @Override
    public ArrayList<Product> getProducts() {


        Thread getProductsThread = new Thread(new Runnable(){

            @Override
            public void run(){

                try {

                    if (conn==null) {
                            Class.forName("com.mysql.jdbc.Driver").newInstance();
                            conn = (Connection) DriverManager.getConnection(url, userName, password);
                    }


                    Statement s = (Statement) conn.createStatement();
                    //String qry = "SELECT _ID, DISH_ID, DISH_NAME, DISH_CATEGORY, DISH_IMG FROM MENU";     
                    String qry = "SELECT _ID, DISH_NAME, DISH_IMG FROM MENU";       
                    s.executeQuery(qry);

                    ResultSet rs = null;
                    rs = (ResultSet) s.getResultSet();

                    while (rs.next()) {
                        int id = rs.getInt("_ID");
                        String productName = rs.getString("DISH_NAME");
                        Blob b = (Blob) rs.getBlob("DISH_IMG");
                        Bitmap productImg = bitmapFromBlob(b);
                        Product p = new Product(id, productName, productImg, "");
                        //adding new item in ArrayList<Product> products, declared in interface CatalogSource
                        products.add(p);
                    }


        rs.close();      
        s.close();


        if (flag_closeConnection) {
            conn.close();
        }


      } catch (Exception e) {
            e.printStackTrace();                     
      }

      }
    });

    getProductsThread.start();

    try {
        getProductsThread.join();
    } catch (InterruptedException e1) {
        e1.printStackTrace();
    }   

        return products;
    }

    /**
     * Converts Blob to Bitmap 
     * @param b Blob object should be converted to Bitmap
     * @return Bitmap object
     */
    static Bitmap bitmapFromBlob(Blob b)  {


        Bitmap bitmap=null;

        try {
            byte[] temp = b.getBytes(1,(int) b.length());   
            bitmap = BitmapFactory.decodeByteArray(temp,0, temp.length);            
        } catch (Exception e) {
            e.printStackTrace();
        }

        return bitmap;

    }


}

score 1 · Answer 3 · answered Mar 05 '13 at 22:11

package fileChooser;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
public class FileChooserDialog {
  public static void main(String[] args) throws IOException, SQLException {
    JFileChooser fileopen = new JFileChooser();
    FileFilter filter = new FileNameExtensionFilter("c files", "c");
    fileopen.addChoosableFileFilter(filter);

    int ret = fileopen.showDialog(null, "Open file");

    if (ret == JFileChooser.APPROVE_OPTION) {
      File file = fileopen.getSelectedFile();
      System.out.println("file()===>>>"+file);
      String url = "jdbc:mysql://localhost:3306/test";
      String username="root";
      String password="root";
      Connection con=null;
      con = DriverManager.getConnection(url,username,password);
      String sql = "INSERT INTO image (id, image) VALUES (?, ?)";
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setInt(1, 1);
      //stmt.setString(2, "Java Official Logo");

      FileInputStream   fis = new FileInputStream(file);
      stmt.setBinaryStream(2, fis, (int) file.length());
      stmt.execute();
      fis.close();
      con.close();
    }
  }
}

score 0 · Answer 4 · answered Mar 07 '13 at 01:35

0

If you want to add just one image I guess you can use:

INSERT INTO image_table (username, image) VALUES ('viktor',load_file('path/image.jpg'));

answered Mar 07 '13 at 01:35

Kashtan

25
1
4

How to save image files to mysql with sql code and how to display them with java?

4 Answers4