我感觉非常简单的一个测试,因为以前没接触过blob。。所以。。哎。。
代码如下:
package jdbc.blob;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import util.ConnectionFactory;
import util.JdbcUtil;public class MysqlBlobWrite {
public static void main(String[] args){
FileInputStream fis=null;
Connection conn=null;
PreparedStatement pstmt=null;
try {
fis = new FileInputStream("d:\\pic.jpg");
conn=ConnectionFactory.getConnection();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into pblob (pid,name,pic) values (?,?,?)");
pstmt.setInt(1,1001); //把传过来的第一个参数设为文件名
pstmt.setString(2, "illu");
// pstmt.setBinaryStream(2,fis,(int)file.length()); //这种方法原理上会丢数据,因为file.length()返回的是long型
pstmt.setBinaryStream(3,fis,fis.available()); //第二个参数为文件的内容
pstmt.executeUpdate();
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis!=null) try {fis.close();} catch (IOException e) { e.printStackTrace();}
JdbcUtil.close(null, pstmt, conn);
}
}
}sql脚本
create table pblob (
pid int primary key,
name varchar(20),
pic blob
);
报错:
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '蘴H櫐s?c?瞷n坪nQ?<睇肟绎?j齒顧隚瞨雘m贙?0n躾竱υFCki0O+久裯?? at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at jdbc.blob.MysqlBlobWrite.main(MysqlBlobWrite.java:27)我感觉blob对象不就是010101 么 怎么能出现语法错误呢。。数据中不可能有 ' “ 这些符号把请高手指点我下 非常感谢
代码如下:
package jdbc.blob;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import util.ConnectionFactory;
import util.JdbcUtil;public class MysqlBlobWrite {
public static void main(String[] args){
FileInputStream fis=null;
Connection conn=null;
PreparedStatement pstmt=null;
try {
fis = new FileInputStream("d:\\pic.jpg");
conn=ConnectionFactory.getConnection();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into pblob (pid,name,pic) values (?,?,?)");
pstmt.setInt(1,1001); //把传过来的第一个参数设为文件名
pstmt.setString(2, "illu");
// pstmt.setBinaryStream(2,fis,(int)file.length()); //这种方法原理上会丢数据,因为file.length()返回的是long型
pstmt.setBinaryStream(3,fis,fis.available()); //第二个参数为文件的内容
pstmt.executeUpdate();
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis!=null) try {fis.close();} catch (IOException e) { e.printStackTrace();}
JdbcUtil.close(null, pstmt, conn);
}
}
}sql脚本
create table pblob (
pid int primary key,
name varchar(20),
pic blob
);
报错:
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '蘴H櫐s?c?瞷n坪nQ?<睇肟绎?j齒顧隚瞨雘m贙?0n躾竱υFCki0O+久裯?? at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at jdbc.blob.MysqlBlobWrite.main(MysqlBlobWrite.java:27)我感觉blob对象不就是010101 么 怎么能出现语法错误呢。。数据中不可能有 ' “ 这些符号把请高手指点我下 非常感谢
解决方案 »
- 关于Java和asp
- 程序不知道哪里出错了?
- Java的数据结构!!!!!
- 有关3D地图实景演示.
- 什么是方法的默认行为?
- 我晕啊,includeaction ,,翻遍古今,国内国外的所有STRUTS的书,都是介绍的这个类,但是没有写怎么使用,怎么使用啊?帮忙啊?
- 可以用两个JSplitPane把一个Jpanel分成三栏吗?我试了好多次都不可以,还有别的方法吗?知道的告诉下 谢谢了
- dispose() & exit()
- "\t\n\r ".indexOf ( c ) != -1 ;是干什么的?
- 为什么我的java文件运行时会报错:Exception in thread "main" java.lang.NoClassDefFoundError: java/HelloWorld
- 关于waitj和notify
- java 字符串替换
将代码:
// pstmt.setBinaryStream(2,fis,(int)file.length()); //这种方法原理上会丢数据,因为file.length()返回的是long型
pstmt.setBinaryStream(3,fis,fis.available()); //第二个参数为文件的内容
改为:
byte[] buf=new byte[(int)file.length()];
fis.read(buf);//图片数据
pstmt.setBytes(3, buf);//插入图片
我读写从来都只用 InputStream和OutputStream,还没发现什么处理不了的情况
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class MysqlBlobWriter { /**
* @param args
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void main(String[] args) throws ClassNotFoundException, SQLException {
FileInputStream fis=null;
Class.forName("com.mysql.jdbc.Driver");
Connection conn=null;
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
PreparedStatement pstmt=null;
try {
fis = new FileInputStream("org//leelin//test//christmas.gif");
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into pblob (pid,name,pic) values (?,?,?)");
pstmt.setInt(1,1001); //把传过来的第一个参数设为文件名
pstmt.setString(2, "illu");
pstmt.setBinaryStream(3, fis);
pstmt.executeUpdate();
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis!=null) try {fis.close();} catch (IOException e) { e.printStackTrace();}
conn.close();
} }}
package org.leelin.test;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;public class MysqlBlobReader { /**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
InputStream fis=null;
FileOutputStream fos=new FileOutputStream("org//leelin//test//christmas_copy.gif");
Class.forName("com.mysql.jdbc.Driver");
Connection conn=null;
ResultSet rs=null;
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
PreparedStatement pstmt=null;
try {
fis = new FileInputStream("org//leelin//test//christmas.gif");
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("select *from pblob");
rs=pstmt.executeQuery();
if(rs.next()){
fis=rs.getAsciiStream("pic");
int data;
while((data=fis.read())!=-1){
fos.write(data);
}
}
conn.commit();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis!=null) try {fis.close();} catch (IOException e) { e.printStackTrace();}
if (fos!=null) try {fos.close();} catch (IOException e) { e.printStackTrace();}
rs.close();
conn.close();
} }}