问题描述
我是个Java开发的初学者，最近开始自学Servlet/JSP编程，参考的是孙鑫老师编的《Servlet/Jsp深入详解——基于Tomcat的Web开发》。在学习“第4章数据库访问”时遇到了问题，我按照书上的代码编写了一个CreateDBServlet.java用于连接数据库，编译是没问题的。进行了部署和配置后，访问时出错了，浏览器显示的错误信息如下：HTTP Status 500 - --------------------------------------------------------------------------------type Exception reportmessage description The server encountered an internal error () that prevented it from fulfilling this request.exception javax.servlet.ServletException: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)org.sunxin.ch04.servlet.CreateDBServlet.doGet(CreateDBServlet.java:61)javax.servlet.http.HttpServlet.service(HttpServlet.java:617)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)root cause java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:298)com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)java.sql.DriverManager.getConnection(DriverManager.java:525)java.sql.DriverManager.getConnection(DriverManager.java:171)org.sunxin.ch04.servlet.CreateDBServlet.doGet(CreateDBServlet.java:44)javax.servlet.http.HttpServlet.service(HttpServlet.java:617)javax.servlet.http.HttpServlet.service(HttpServlet.java:717)note The full stack trace of the root cause is available in the Apache Tomcat/6.0.18 logs.--------------------------------------------------------------------------------Apache Tomcat/6.0.18CreateDBServlet代码如下：package org.sunxin.ch04.servlet;import java.io.IOException;import java.io.PrintWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class CreateDBServlet extends HttpServlet{private String userUrl;private String userName;private String password;public void init() throws ServletException{String driverClass=getInitParameter("driverClass");userUrl=getInitParameter("userUrl");userName=getInitParameter("userName");password=getInitParameter("password");try{Class.forName(driverClass);}catch (ClassNotFoundException ce){throw new ServletException("加载数据库驱动失败！");}}public void doGet(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{Connection conn=null;Statement stmt=null;try{conn=DriverManager.getConnection(userUrl,userName,password);stmt=conn.createStatement();stmt.executeUpdate("create database bookstore");stmt.executeUpdate("use bookstore");stmt.executeUpdate("create table bookinfo(id INT not null primary key,title VARCHAR(50) not null,author VARCHAR(50) not null,bookconcern VARCHAR(50) not null,publish_date DATE not null,price FLOAT(4,2) not null,amount SMALLINT,remark VARCHAR(200))ENGINE=InnoDB");stmt.addBatch("insert into bookinfo values(1,'Java Web 详解','孙鑫','电子工业出版社','2006-4-20',99.00,35,null)");stmt.addBatch("insert into bookinfo values(2,'Struts 2深入详解','孙鑫','电子工业出版社','2008-6-15',79.00,20,null)");stmt.addBatch("insert into bookinfo values(3,'Servlet/JSP详解','孙鑫','电子工业出版社','2008-7-1',79.00,10,null)");stmt.executeBatch();resp.setContentType("text/html;charset=GBK");PrintWriter out=resp.getWriter();out.println("数据库创建成功！");out.close();}catch (SQLException se){throw new ServletException(se);}finally{if(stmt!=null){try{stmt.close();}catch (SQLException se){se.printStackTrace();}stmt=null;}if(conn!=null){try{conn.close();}catch (SQLException se){se.printStackTrace();}conn=null;}}}}web.xml文件如下：<?xml version="1.0" encoding="gb2312"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"><servlet><servlet-name>createDB</servlet-name><servlet-class>org.sunxin.ch04.servlet.CreateDBServlet</servlet-class> <init-param><param-name>driverClass</param-name><param-value>com.mysql.jdbc.Driver</param-value></init-param><init-param><param-name>userUrl</param-name><param-value>jdbc:mysql://localhost:3306</param-value></init-param><init-param><param-name>userName</param-name><param-value>root</param-value></init-param><init-param><param-name>passord</param-name><param-value>12345678</param-value></init-param></servlet><servlet-mapping><servlet-name>createDB</servlet-name><url-pattern>/createdb</url-pattern></servlet-mapping></web-app> 问题补充：我自己弄了弄，上面的问题已经解决了，好像是密码设置的问题，但是具体怎么回事还是不太清楚。但又遇到了新问题：每调用CreateDBServlet一次都会创建数据库，因为我是学习，所以总要不断调试，熟悉操作流程。所以，等第一次连接数据库（创建数据库）成功后，若再次调用CreateDBServlet（再次创建数据库）肯定会抛出异常：exception javax.servlet.ServletException: java.sql.SQLException: Can't create database 'bookstore'; database existsorg.sunxin.ch04.servlet.CreateDBServlet.doGet(CreateDBServlet.java:63)孙鑫的书中提供了一个解决方法，在CreateDBServlet代码的46行stmt.executeUpdate("create database bookstore"); 前面加上stmt.executeUpdate("drop database if exits bookstore");,但是添加这个代码之后，却会抛出这个异常exception javax.servlet.ServletException: 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 'exits bookstore' at line 1org.sunxin.ch04.servlet.CreateDBServlet.doGet(CreateDBServlet.java:63)
解决方案
引用exception javax.servlet.ServletException: 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 'exits bookstore' at line 1 这个是在你写的sql语句中有语法错误。逻辑是先判断有删除 你的exits用法不对。
解决方案二：
web.xml里passord拼错了，应该是password
解决方案三：
两处都是平写错误。。。引用<param-name>passord</param-name>web.xml里passord拼错了，应该是password引用stmt.executeUpdate("drop database if exits bookstore"); exits拼错了，应该是exists
解决方案四：
exits 拼错了,是exists建议你还是在数据库里先建好了表,在去操作数据,不要每次都去建库,建表
解决方案五：
CreateDbServlet中的从45到52行代码都可以注释掉的。这个不影响你连接数据库的。如果连接不成功的话 它还是会报错的。或者你直接把连接数据库的那个给写成一个类的一个static方法，在要连接的时候直接用类名去调用就ok了。不知道我把问题看清楚了没，你看看。。。
解决方案六：
这个好解决，重新设置密码。一些设置密码指令：http://www.debian-administration.org/articles/442
解决方案七：
引用javax.servlet.ServletException: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)数据库访问被拒了，检查一下帐号密码是否正确？