向Mysql5.5存入图片Blob数据出现问题了

滔哥

public static void write(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
String url ="jdbc:mysql://localhost:3306/mydb2";
String user ="root";
String password = "123456";
conn = DriverManager.getConnection(url, user, password);
// 创建sql语句
//String sql = "insert into test_blob(id,content) values(?,?)";
String sql = "insert into test_blob(id,content) values(?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, UUID.randomUUID().toString());
// 类加载器加载资源
URL uri = BlobTest.class.getClassLoader().getResource("cn/zengfansheng/blob/money.jpg");
File file = new File(URLDecoder.decode(uri.getPath(), "utf-8"));
InputStream is = new FileInputStream(file);
pstmt.setBinaryStream(2, is, (int)file.length());
// 执行sql语句
int i = pstmt.executeUpdate();
System.out.println(i>0?"数据写入数据库成功":"数据写入数据库失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} finally{
// 关闭资源，从里到外
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

复制代码

为什么会报这个错误，前面我存Clob时候代码也差不多，但是没有报错。

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 '????—??”????è????????é??é??Rè??U???[????—?é…??‰?.q???V??°8RMFè¤?????è?????p
7' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at cn.zengfansheng.blob.BlobTest.write(BlobTest.java:50)
at cn.zengfansheng.blob.BlobTest.main(BlobTest.java:20)

复制代码

CrazyProgram · CrazyProgram

我也发一份代码给你看看把

//将BLOB类型数据存入数据库
public static void write() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "insert into test_blob(id,content) values(?,?)";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
//绑定ID
pstmt.setString(1,UUID.randomUUID().toString());
//取得图片的路径
URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg");
//封装成File对象
File file = new File(url.getPath());
//取得字节输入流
InputStream is = new FileInputStream(file);
//绑定CONTENT
//参数1占位符的编号，从1开始
//参数2文件字节输入流
//参数3文件的大小
pstmt.setBinaryStream(2,is,(int)file.length());
int i = pstmt.executeUpdate();
System.out.println(i>0?"成功":"失败");
} catch (Exception e) {
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
//将BLOB类型数据从数据库中取出
public static void read() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from test_blob";
InputStream is = null;
OutputStream os = null;
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
is = rs.getBinaryStream("content");
}
} catch (Exception e) {
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
try {
os = new FileOutputStream("d:\\d1.jpg");
int len = 0;
byte[] buf = new byte[1024];
while( (len=is.read(buf))>0 ){
os.write(buf,0,len);
}
} catch (Exception e) {
}finally{
if(is!=null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(os!=null){
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

复制代码

CrazyProgram · CrazyProgram

原因就是路径中不能有中文，，你可以放在英文的目录下，这样问题可以解决的

hacket · hacket

冯超发表于 2013-4-5 16:23
兄弟看看·我写的存入与读取

我的路径时中文路径，不一样的，你的是英文路径。

冯超 · 冯超

兄弟看看·我写的存入与读取

public static void creat2() throws SQLException, IOException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream in = null;
try {
//2.连接
conn = (Connection) JdbcUtils.getConnection();
//3.创建语句
File file = new File("f:\\qq.jpg");
in = new BufferedInputStream(new FileInputStream(file));
String sql = "insert into blob_test(blob_tes) values(?)";
ps = conn.prepareStatement(sql);
ps.setBinaryStream(1, in, (int)file.length());
//4.执行
ps.executeUpdate();
//5.处理结果
System.out.println("插入成功！");
} finally {
in.close();
JdbcUtils.free(rs, ps, conn);
}
}
public static void read2() throws SQLException, IOException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream in = null;
try {
//2.连接
conn = (Connection) JdbcUtils.getConnection();
//3.创建语句
String sql = "select blob_tes from blob_test";
ps = conn.prepareStatement(sql);
//4.执行语句
rs = ps.executeQuery();
//5.处理
FileOutputStream out = new FileOutputStream(new File("F:\\123.jpg"));
while(rs.next()) {
//Clob clob = rs.getClob(1);
InputStream ins = rs.getBinaryStream(1); //in --->read
byte buf[] = new byte[1024];
int len = ins.read(buf);
while(len != -1) {
out.write(buf, 0, len);
len = ins.read(buf);
}
out.close();
ins.close();
System.out.println("读取OK！");
}
} finally {
JdbcUtils.free(rs, ps, conn);
}

复制代码

ㄨ____陌生 · ㄨ____陌生

出错了要看报错的信息，根据这个去修改代码。在SQL 语句里有问题。应该不是数据库连接的问题。改下你的SQL代码、
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

帐号		自动登录	找回密码
密码			加入黑马

向Mysql5.5存入图片Blob数据出现问题了

评分

5 个回复

评分

黑马骑士勋章