servlet连接mysql数据库问题

Question

把代码贴出来吧，肯定是写的有问题，和servlet，数据库应该没关系

Answer 1

文件：User_Manager.java
package cn.edu.bit.business;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;import cn.edu.bit.DBQuery.DataProcess;
import cn.edu.bit.Object.Users;public class User_Manager {

public static boolean CheckUser(String username,String password)
{
String query = "select count(*) from user where user.username='"+username+
"' and user.psw='"+password+"'";

int count = DataProcess.nCount(query);


if(count>0)
{
return true;
}
else
return false;
}

public static boolean HasAdmin(String username)
{
String query = "select count(*) from user where user.username='"+username+"'"; int count = DataProcess.nCount(query);
if(count>0)
{
return true;
}
else
return false;
}

public static void add_User(String username,String password,String password1,String useremail,String userzhenshiname,String identify,String usersex,String bodyname,String shouhuodizhi,String youbian,String usertel)
{

String query = "insert into user(username,psw，psw1,useremail,userzhenshiname,identify,usersex,bodyname,shouhuodizhi,youbian,usertel) values('"+username+
"','"+password+"','"+password1+"','"+useremail+"','"+userzhenshiname+"','"+identify+"','"+usersex+"','"+bodyname+"','"+shouhuodizhi+"','"+youbian+"','"+usertel+"')";

try
{
DataProcess.ExeQuery(query);
}
catch(Exception e){
e.printStackTrace();
}
}

public static void Edit_User(String username,String password,String password1,String useremail,String userzhenshiname,String identify,String usersex,String bodyname,String shouhuodizhi,String youbian,String usertel)
{
String query = "update user set user.psw='"+password+"'，user.psw1='"+password1+"'，user.useremail='"+useremail+"'，user.userzhenshiname='"+userzhenshiname+"'，user.identify='"+identify+"'，user.usersex='"+usersex+"'，user.bodyname='"+bodyname+"'，user.shouhuodizhi='"+shouhuodizhi+"'，user.youbian='"+youbian+"'，user.usertel='"+usertel+"' where user.username='"+username+"'";

try
{
DataProcess.ExeQuery(query);
}
catch(Exception e){
e.printStackTrace();
}
}

public static void Delete_User(String username)
{
String query = "delete * from user where user.username='"+username+"'";
System.out.println(query);
try
{
DataProcess.ExeQuery(query);
}
catch(Exception e){
e.printStackTrace();
}
}


public static Users getManager(String username)
{
String selectStr = "select * from user where user.username='"+username+"'";

Connection con = DataProcess.getConnection();

Users ma = Users.getInstance();

try{

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(selectStr);

if(rs.next())
{
String password = rs.getString("user.psw");

ma.setUsername(username);

ma.setPassword(password);
}

rs.close();

stmt.close();

con.close();

return ma;

}
catch(Exception e)
{
e.printStackTrace();

return null;
}
}    //得到所有记录
public static ArrayList getManagerList()
{
String query = "select * from user";

Connection con = DataProcess.getConnection();

ArrayList al = new ArrayList();

try{

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);

while(rs.next())
{
String username = rs.getString("user.username");

String password = rs.getString("user.psw");

Users ma = Users.getInstance();

ma.setUsername(username);

ma.setPassword(password);

al.add(ma);
}

rs.close();

stmt.close();

con.close();

return al;

}
catch(Exception e)
{
e.printStackTrace();

return null;
}

}

}

Answer 2

数据库文件：DataProcess.javapackage cn.edu.bit.DBQuery;
import java.sql.*;public class DataProcess {

DataProcess(){

}

public static Connection getConnection()
{
String CLASSFORNAME="org.gjt.mm.mysql.Driver";    String SERVANDDB = "jdbc:mysql://localhost:3306/lab?user=root&password=root&useUnicode=true&characterEncoding=gb2312";


Connection con; 

try
{
Class.forName(CLASSFORNAME);

con = DriverManager.getConnection(SERVANDDB);

return con;
}
catch(Exception e)
{
e.printStackTrace();

return null;
}   }

public static int OpenSql(String sql) {

int iMaxid=1; 
  
try {
Connection con = getConnection();
Statement stmt = con.createStatement();
ResultSet re = stmt.executeQuery(sql);
if(re.next()){
iMaxid = re.getInt("id")+1;
re.close();
}

stmt.close();

con.close();
} 
catch(SQLException ex) { 
ex.printStackTrace(); 
}
return iMaxid;
}

public static int nCount(String SQL)
{
Connection myConnection = getConnection();
int count = 0;

try
{
Statement stm = myConnection.createStatement();

ResultSet result = stm.executeQuery(SQL);

if(result.next())
{
count = result.getInt(1);

result.close();
}

stm.close();

myConnection.close();

}
catch(Exception e)
{
e.printStackTrace();
}

return count;
}

/**插入或删除语句*/
public static void ExeQuery(String SQL)
{
Connection con = getConnection();
try
{
Statement stmt = con.createStatement();

stmt.executeUpdate(SQL);

stmt.close();

con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}

public static ResultSet getResult(String SQL,Connection con)
{
ResultSet rs = null;
 
try{
Statement stmt = con.createStatement();

rs = stmt.executeQuery(SQL);

}catch(SQLException e)
{
e.printStackTrace();
}

return rs;

}



public static void CloseConnection(Connection con)
{
try
{
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}

public static void CloseResultSet(ResultSet rs)
{
try
{
rs.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}}

Answer 3

文件：Users.javapackage cn.edu.bit.Object;public class Users {
int id;
private String username;
private String password;
private String password1;
private String useremail;
private String userzhenshiname;
private String identify;
private String usersex;
private String bodyname;
private String shouhuodizhi;
private String youbian;
private String usertel;
Users(){}

Users(int id,String username,String password,String password1,String useremail,String userzhenshiname,String identify,String usersex,String bodyname,String shouhuodizhi,String youbian,String usertel)
{
this.id = id;
this.username = username;
this.password = password;
this.password1 = password1;
this.useremail = useremail;
this.userzhenshiname = userzhenshiname;
this.identify = identify;
this.usersex = usersex;
this.bodyname = bodyname;
this.shouhuodizhi = shouhuodizhi;
this.youbian = youbian;
this.usertel = usertel;
}

    public static Users getInstance()
    {
     return new Users();
    }
    
    public void setId(int id)
    {
     this.id = id; 
    }
    
    public int getId()
    {
     return id;
    }
    
    public void setUsername(String username)
    {
     this.username =username;
    }
    
    public String getUsername()
    {
     return username;
    }
    
    public void setPassword(String password)
    {
     this.password = password;
    }
    
    public String getPassword()
    {
     return password;
    } public String getBodyname()
{
return bodyname;
} public void setBodyname(String bodyname)
{
this.bodyname = bodyname;
} public String getIdentify()
{
return identify;
} public void setIdentify(String identify)
{
this.identify = identify;
} public String getPassword1()
{
return password1;
} public void setPassword1(String password1)
{
this.password1 = password1;
} public String getShouhuodizhi()
{
return shouhuodizhi;
} public void setShouhuodizhi(String shouhuodizhi)
{
this.shouhuodizhi = shouhuodizhi;
} public String getUseremail()
{
return useremail;
} public void setUseremail(String useremail)
{
this.useremail = useremail;
} public String getUsersex()
{
return usersex;
} public void setUsersex(String usersex)
{
this.usersex = usersex;
} public String getUsertel()
{
return usertel;
} public void setUsertel(String usertel)
{
this.usertel = usertel;
} public String getUserzhenshiname()
{
return userzhenshiname;
} public void setUserzhenshiname(String userzhenshiname)
{
this.userzhenshiname = userzhenshiname;
} public String getYoubian()
{
return youbian;
} public void setYoubian(String youbian)
{
this.youbian = youbian;
}
}

Answer 4

insert into 的语句不能用executeExec()
应该用 executeUpdate();

Answer 5

数据库文件：DataProcess.java中用的是：stmt.executeUpdate(SQL);

Answer 6

对不起，刚才说错了。不过你的方法名字确实很不贴切。
最好你能把出错的EXCEPTION贴出来。还有你的代码问题很多，比如连接释放的问题。
如果有一个resultset关闭出错，statement 和 connection 都不会被关闭了。

Answer 7

servlet文件：AddUserServlet.javapackage cn.edu.bit.servlet;import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import cn.edu.bit.business.User_Manager;public class AddUserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
    HttpSession ses = request.getSession();
    String username = (String)request.getParameter("username");
    String password = (String)request.getParameter("psw");
    String password1 = (String)request.getParameter("psw1");
    String useremail = (String)request.getParameter("useremail");
    String userzhenshiname = (String)request.getParameter("userzhenshiname");
    String identify = (String)request.getParameter("identify");
    String usersex = (String)request.getParameter("usersex");
    String bodyname = (String)request.getParameter("bodyname");
    String shouhuodizhi = (String)request.getParameter("shouhuodizi");
    String youbian = (String)request.getParameter("youbian");
    String usertel = (String)request.getParameter("usertel");
    User_Manager.add_User(username,password,password1,useremail,userzhenshiname,identify,usersex,bodyname,shouhuodizhi,youbian,usertel);
    RequestDispatcher requestDispatcher = request.getRequestDispatcher("login/success.jsp");
    requestDispatcher.forward(request,response);
    
} public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request,response);
}}

Answer 8

UserManager.add_User 里的异常最好抛出来，别自已捕获自已处理，要让调用程序能知道出错了。哪怕是返回个true false呢。
你现在调用的程序根本就不知道调用出错，所以还会往下正常执行，没有报错。

Answer 9

这是什么错误，是不是数据库不匹配？
java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:935)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:873)
at cn.edu.bit.DBQuery.DataProcess.ExeQuery(DataProcess.java:100)
at cn.edu.bit.business.User_Manager.add_User(User_Manager.java:52)
at cn.edu.bit.servlet.AddUserServlet.doGet(AddUserServlet.java:28)
at cn.edu.bit.servlet.AddUserServlet.doPost(AddUserServlet.java:36)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)

Answer 10

你在调试时可以把你执行的SQL语句打出来，方便查错。

Answer 11

你试试程序执行别的插入语句行不行
然后把你打出来的SQL语句放到工具里运行看看行不
插入语句一点一点加字段执行，找到有问题的字段。
或者你的数据库有什么约束你没有遵守。还有，你贴在这里的SQL语句中有一个中文标点（逗号），你的代码里也是这样的吗？