-
Inserting image into mySQL
Hi,
I wonder if any one might be able to help, i am trying to insert a .jpg into a mySQL data base, so far i have tried inserting the file as byte[], i keep on recieveing an error message, stating that the syntax i have used is incorrect,
is nay one able to help me? i have provided a copy of the create table SQL used as well,
Thanks in advance.
public void putFile(int FoodID, File infile){
try{
System.out.println(infile.getName());
FileInputStream is=new FileInputStream(infile);
byte[] b=new byte[(int)infile.length()];
is.read(b);
bt=b;
//is.close();
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO photo VALUES("+FoodID+",'"+infile.getName()+"',"+bt+")");
stmt.close();
con.commit();
}catch (Exception e){
System.out.println("error: " + e);
}
}
Create Table Photo(
Foodin_id INT NOT NULL,
photoName VARCHAR(150) NOT NULL,
PRIMARY KEY(Foodin_id, photoName),
photo MEDIUMBLOB NOT NULL,
Foreign Key (Foodin_id)
References Food_Inspect(Foodin_id)) TYPE=INNODB;
-
Blobs must be treated as streams
Check this code, it inserts an image into a mySql db like.
Here is the table definition
Code:
create table image_tab
(
image_name VARCHAR(42) not null,
image_content LONG VARBINARY ,
primary key (image_name)
);
here is the java code
Code:
import java.sql.*;
import java.io.*;
public class StoreBinary {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private Statement stmt = null;
private Connection conn = null;
public StoreBinary() {
}
public void storeImageFile(String fileName) throws Exception {
if (!connect("test", "root", "")) {
return;
}
FileInputStream in = new FileInputStream(fileName);
int len=in.available();
PreparedStatement pStmt = conn.prepareStatement("insert into image_tab values (?,?)");
pStmt.setString(1, fileName);
pStmt.setBinaryStream(2, in, len);
pStmt.executeUpdate();
in.close();
System.out.println("Stored: "+fileName+", length: "+len);
}
public boolean connect(String dbName, String dbUser, String dbPassword) {
try {
Class.forName(driverName);
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
return false;
}
try {
conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
dbUser,
dbPassword);
//stmt = conn.createStatement();
}
catch (SQLException ex1) {
ex1.printStackTrace();
return false;
}
return true;
}
/**
* MAIN ***********************
*/
public static void main(String[] args) {
StoreBinary sb = new StoreBinary();
try {
sb.storeImageFile("c:\\tmp\\f128.jpg");
}
catch (Exception ex) {
ex.printStackTrace();
}
}
}
eschew obfuscation
-
Thanks i will give it try
-
Hi
Thanks for the code it worked!i wownder if you might have similar code to retrieve the image from the db? in any case thanks for your help
-
Rest assured that I would never have posted it if I wasn't sure that it worked 
Here it is, I have made some minor modifications; the table now have three columns, the name, the length and the blob, and I only store the base filename.
Code:
import java.sql.*;
import java.io.*;
import java.awt.*;
import java.awt.Image;
public class StoreBinary {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private Statement stmt = null;
private Connection conn = null;
public StoreBinary() {
}
public static String getBaseName(String fileName) {
int ix=fileName.lastIndexOf("\\");
if (ix < 0) return fileName;
return fileName.substring(ix+1);
}
public boolean storeImageFile(String fileName) throws Exception {
if (!connect("test", "root", "")) {
return false;
}
FileInputStream in = new FileInputStream(fileName);
int len=in.available();
String baseName=StoreBinary.getBaseName(fileName);
PreparedStatement pStmt = conn.prepareStatement("insert into image_tab values (?,?,?)");
pStmt.setString(1, baseName);
pStmt.setInt(2,len);
pStmt.setBinaryStream(3, in, len);
pStmt.executeUpdate();
in.close();
System.out.println("Stored: "+baseName+", length: "+len);
return true;
}
public Image getImageFile(String fileName) throws Exception {
String baseName=StoreBinary.getBaseName(fileName);
ResultSet rs=stmt.executeQuery("select * from image_tab where image_name='"+baseName+"'");
if (!rs.next()) {
System.out.println("Image:"+baseName+" not found");
return null;
}
int len=rs.getInt(2);
byte [] b=new byte[len];
InputStream in = rs.getBinaryStream(3);
in.read(b);
in.close();
Image img=Toolkit.getDefaultToolkit().createImage(b);
System.out.println("Image: "+baseName+" retrieved ok, size: "+len);
return img;
}
public boolean connect(String dbName, String dbUser, String dbPassword) {
try {
Class.forName(driverName);
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
return false;
}
try {
conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
dbUser,
dbPassword);
stmt = conn.createStatement();
}
catch (SQLException ex1) {
ex1.printStackTrace();
return false;
}
return true;
}
/**
* MAIN ***********************
*/
public static void main(String[] args) {
String fileName="c:\\tmp\\f128.jpg";
StoreBinary sb = new StoreBinary();
try {
if (sb.storeImageFile(fileName)) {
// stored ok, now get it back again
Image img=sb.getImageFile(fileName);
}
}
catch (Exception ex) {
ex.printStackTrace();
}
}
}
eschew obfuscation
-
Very good,I also need it.
Most onlie HTML editor only support upload the images into directory,don't support insert them into database
Similar Threads
-
By James Graham in forum .NET
Replies: 7
Last Post: 07-16-2013, 11:47 PM
-
Replies: 6
Last Post: 02-03-2006, 12:55 PM
-
By raihanul in forum VB Classic
Replies: 1
Last Post: 01-04-2006, 07:38 PM
-
Replies: 3
Last Post: 08-30-2001, 11:45 AM
-
By Mahyar in forum VB Classic
Replies: 0
Last Post: 09-12-2000, 01:24 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|