网站手机版开发,论坛源码推荐,网站建设前期规划论文,做酱菜网站向MYSql的数据表中存入文件#xff0c;这看似没有什么大问题#xff0c;只要把文件存入longblob字段就行了。但是最近的实践发现看似简单的背后其实有很多陷阱。下面就来分享一下我遇到的问题及我的解决办法。先介绍一下我的应用#xff0c;只有两个页面upload.jsp和downloa…向MYSql的数据表中存入文件这看似没有什么大问题只要把文件存入longblob字段就行了。但是最近的实践发现看似简单的背后其实有很多陷阱。下面就来分享一下我遇到的问题及我的解决办法。先介绍一下我的应用只有两个页面upload.jsp和download.jsp。upload.jsp使用common file upload来上传文件并将文件存入mysql的数据表的longblob字段。download.jsp则是从数据库中取出该longblob字段的内容并写到浏览器客户端。数据库的连接我选用tomcat的连接池url为jdbc:mysql://localhost:3306/test?characterEncodinggbk。jsp页面的编码都为gbk。数据表的结构如下CREATE TABLE file (id int(11) NOT NULL AUTO_INCREMENT,filename varchar(255) CHARACTER SET utf8 NOT NULL,content longblob,filetype varchar(255) CHARACTER SET utf8 NOT NULL,size int(11) DEFAULT NULL,ip varchar(255) CHARACTER SET utf8 NOT NULL,date timestamp NOT NULL DEFAULT 0000-00-00 00:00:00 ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT15 DEFAULT CHARSETgbk上传文件的java代码如下request.setCharacterEncoding(gbk);if(POST.equalsIgnoreCase(request.getMethod())){DiskFileUpload upload new DiskFileUpload();upload.setHeaderEncoding(gbk);List list upload.parseRequest(request);for(FileItem fileItem : list){if(!fileItem.isFormField()){String filename fileItem.getName().replace(\\, /);filename filename.substring(filename.lastIndexOf(/) 1);Context envContext (Context)new InitialContext().lookup(java:/comp/env);DataSource ds (DataSource) envContext.lookup(mysqlTest);Connection conn ds.getConnection();PreparedStatement preStmt conn.prepareStatement(insert into file (filename, filetype, size, content, ip, date) values (?,?,?,?,?,?));preStmt.setString(1, filename);preStmt.setString(2, fileItem.getContentType());preStmt.setInt(3, (int)fileItem.getSize());preStmt.setString(5, request.getRemoteAddr());preStmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));preStmt.setBinaryStream(4, fileItem.getInputStream(),(int)fileItem.getSize());preStmt.executeUpdate();preStmt.close();conn.close();}}}下载文件的代码如下Context envContext (Context)new InitialContext().lookup(java:/comp/env);DataSource ds (DataSource) envContext.lookup(mysqlTest);conn ds.getConnection();preStmt conn.prepareStatement(select * from file where id ?);preStmt.setInt(1, id);rs preStmt.executeQuery();if(rs.next()){response.reset();response.setContentType(rs.getString(filetype));response.setContentLength(rs.getInt(size));InputStream ins null;OutputStream ous null;try{ins rs.getBinaryStream(content);ous response.getOutputStream();byte[] b new byte[1024];int len 0;while((len ins.read(b))! -1){ous.write(b, 0, len);}}finally{if(ous ! null) ous.close();if(ins ! null) ins.close();}开始上传一些文本文件没问题但是当上传的文件大于1M时就报出如下异常com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5553258 1048576). You can change this value on the server by setting the max_allowed_packet variable.com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3279)com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)org.apache.jsp.saveFile2Mysql_jsp._jspService(saveFile2Mysql_jsp.java:111)org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388)org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)为了解决这个问题没少废了周折。在网上看到一些解决办法但是在我这都不起作用。在Mysql的安装装目录的my.ini文件的【mysqld】节点下添加max_allowed_packet100M重启mysql服务后(可用net stop/start mysql)在mysql客户端查看时show variables llike “%packet%”显示max_allowed_packet 的大小依然是1M。用mysqld --max_allowed_packet100M结果依然如此。经过不断尝试发现了一个问题mysqld启动后会成为一个后台运行的进程当已经有一个mysqld进程在运行时再次调用mysqld将会被忽略。所以正确的做法是首先关闭mysql服务然后查看windows的任务管理器关闭正在运行的mysqld进程(如果存在)然后再用mysqld --max_allowed_packet100M来启动就没有问题了。然而这个办法在机器重启之后就没有效果了开机启动所使用的max_allowed_packet如何改没有找到解决办法。解决了上个问题在上传MP3文件时又遇到了一个异常同样让人头痛异常信息如下com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version forthe right syntax to use near 焲拲è¬?鞱覑?Y:r8閧釥\R㈧?^薇覆皟箧统廜矻å‰æ„¦;äº é‚ªè¶‘t at line 1sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)java.lang.reflect.Constructor.newInstance(Constructor.java:513)com.mysql.jdbc.Util.handleNewInstance(Util.java:411)com.mysql.jdbc.Util.getInstance(Util.java:386)com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)org.apache.jsp.saveFile2Mysql_jsp._jspService(saveFile2Mysql_jsp.java:111)org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388)org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)查资料发现问题的根源在连接数据库时使用的字符编码将url由 jdbc:mysql://localhost:3306/test?characterEncodinggbk改成url为jdbc:mysql://localhost:3306/test?characterEncodingutf8问题就解决了。
相关文章：
