com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'bookshop.bookcategory' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
at com.bsw.bookcategory.BookCategoryDAO.queryBookCategory(BookCategoryDAO.java:55)
at com.bsw.servlet.common.WelcomeControl.doPost(WelcomeControl.java:79)
at com.bsw.servlet.common.WelcomeControl.doGet(WelcomeControl.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
null
java.sql.SQLException: Bad format for DATE '2012430' in column 12.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:261)
at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:238)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2185)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2147)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2212)
at com.bsw.user.UserDAO.queryUser(UserDAO.java:117)
at com.bsw.servlet.common.LoginControl.doPost(LoginControl.java:91)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
INSERT INTO user(name,pass,role,realName,gender,age,phone,email,address,registerTime)VALUES(?,?,?,?,?,?,?,?,?,?)
java.sql.SQLException: Field 'id' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
at com.bsw.user.UserDAO.insertUser(UserDAO.java:160)
at com.bsw.servlet.common.RegisterControl.doPost(RegisterControl.java:107)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
INSERT INTO user(name,pass,role,realName,gender,age,phone,email,address,registerTime)VALUES(?,?,?,?,?,?,?,?,?,?)
java.sql.SQLException: Field 'id' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
at com.bsw.user.UserDAO.insertUser(UserDAO.java:160)
at com.bsw.servlet.common.RegisterControl.doPost(RegisterControl.java:108)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
at com.bsw.bookcategory.BookCategoryDAO.queryBookCategory(BookCategoryDAO.java:55)
at com.bsw.servlet.common.WelcomeControl.doPost(WelcomeControl.java:79)
at com.bsw.servlet.common.WelcomeControl.doGet(WelcomeControl.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
null
java.sql.SQLException: Bad format for DATE '2012430' in column 12.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:261)
at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:238)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2185)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2147)
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2212)
at com.bsw.user.UserDAO.queryUser(UserDAO.java:117)
at com.bsw.servlet.common.LoginControl.doPost(LoginControl.java:91)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
INSERT INTO user(name,pass,role,realName,gender,age,phone,email,address,registerTime)VALUES(?,?,?,?,?,?,?,?,?,?)
java.sql.SQLException: Field 'id' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
at com.bsw.user.UserDAO.insertUser(UserDAO.java:160)
at com.bsw.servlet.common.RegisterControl.doPost(RegisterControl.java:107)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
INSERT INTO user(name,pass,role,realName,gender,age,phone,email,address,registerTime)VALUES(?,?,?,?,?,?,?,?,?,?)
java.sql.SQLException: Field 'id' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
at com.bsw.user.UserDAO.insertUser(UserDAO.java:160)
at com.bsw.servlet.common.RegisterControl.doPost(RegisterControl.java:108)
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:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
null
java.sql.SQLException: Field 'id' doesn't have a default valueid有問題
前台和后台的数据没办法互通
在mysql中添加的数据,前台不能用
在前台注册的,表中也查找不到?
是为什么?
2.java.sql.SQLException: Bad format for DATE '2012430' in column 12.
3.java.sql.SQLException: Field 'id' doesn't have a default value
觉得问题很多的。
表有问题
sql语句有问题。
也有可能是第一个问题,造成了后面的问题,源码贴出来看看把。数据库配置、数据库连接代码
1:表bookshop中bookcategory不存在,看看是不是映射的时候 字段名写错了
2:java.sql.SQLException: Bad format for DATE '2012430' in column 12.
类型错误了,2012430不是DATE类型 需要楼主转换下
3:java.sql.SQLException: Field 'id' doesn't have a default value
这是user表中ID没有默认值,楼主肯定将id设置为not null 的,但是insert的时候,id没有加进去。可以将id设置为自增或者sql语句中将id值也加进去。
id int(4) not null,
name varchar(20) not null,
pass varchar(20) not null,
role int(1) not null,
realName varchar(20),
gender varchar(2),
age int(3) ,
phone varchar(11),
email varchar(32),
address varchar(32),
postcode varchar(6),
registerTime varchar(20),
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=gb2312;
这是mysql的脚本package com.bsw.user;import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Locale;import com.bsw.db.DBManager;
/**
* User(用户) 管理类
* 可以从数据库 查询,插入,删除 订单
* @author Student
* @version 1.0.0
* @see User
*
*/
public class UserDAO { /**
* Statement对象
*/
private Statement state =null; /**
* PreparedStatement对象
*/
private PreparedStatement preState=null ; /**
* 结果集对象
*/
private ResultSet resultSet=null ; /**
* 数据库连接类对象
*/
private Connection connection =null; /**
* 数据库连接的包装类对象
*/
private DBManager dbManager=null ; /**
* 构造函数
*/
public UserDAO() {
//dbManager = new DBManager(); // 得到一个数据库管理对象
}
public ArrayList queryUser() {
connection = DBManager.getConnection();
String sqlState = "SELECT * FROM user";
User user = new User();
ArrayList arrayList = new ArrayList();
try { state = connection.createStatement();
resultSet = state.executeQuery(sqlState); // 执行sql查询
while (resultSet.next()) {
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPass(resultSet.getString("pass"));
user.setRole(resultSet.getInt("role"));
user.setRealName(resultSet.getString("realName"));
user.setAge(resultSet.getInt("age"));
user.setGender(resultSet.getString("gender"));
user.setPhone(resultSet.getString("phone"));
user.setEmail(resultSet.getString("email"));
user.setAddress(resultSet.getString("address"));
user.setRegisterTime(resultSet.getDate("registerTime"));
arrayList.add(user);
}
return arrayList;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DBManager.closeAll(connection, state, resultSet);
}
}
/**
* 用给定的用户名查询用户
*
* @param name 给定的name
* @return 要查询的User
*/
public User queryUser(String name) { connection = DBManager.getConnection();
String sqlState = "SELECT * FROM user WHERE name = '" + name + "'";
User user = null;
try { state = connection.createStatement();
resultSet = state.executeQuery(sqlState); // 执行sql查询
while (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPass(resultSet.getString("pass"));
user.setRole(resultSet.getInt("role"));
user.setRealName(resultSet.getString("realName"));
user.setAge(resultSet.getInt("age"));
user.setGender(resultSet.getString("gender"));
user.setPhone(resultSet.getString("phone"));
user.setEmail(resultSet.getString("email"));
user.setAddress(resultSet.getString("address"));
// user.setRegisterTime(resultSet.getDate("registerTime"));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DBManager.closeAll(connection, state, resultSet);
}
}
/**
* 向数据库插入一个用户
* 如果插入成功 返回 true,否则返回 false
*
* @param order 给定要插入的order对象
* @return true 或 false
*/
public boolean insertUser(User user) { connection = DBManager.getConnection(); // 得到数据库连接 StringBuffer sqlState = new StringBuffer();
// id int not null auto_increment primary key
sqlState.append("INSERT INTO user(name,pass,role,");
sqlState.append("realName,gender,age,phone,email,address,registerTime");
sqlState.append(")VALUES(?,?,?,?,?,?,?,?,?,?)");
//sqlState.append(")VALUES(?,?,?,?,?,?,?,?,?,?,?)");
System.out.println(sqlState);
try { preState = connection.prepareStatement(sqlState.toString()); // 采用预处理方式 preState.setString(1, user.getName()); // 设置每个要插入的的属性
preState.setString(2, user.getPass());
preState.setInt(3, user.getRole());
preState.setString(4, user.getRealName());
preState.setString(5, user.getGender());
preState.setInt(6, user.getAge());
preState.setString(7, user.getPhone());
preState.setString(8, user.getEmail());
preState.setString(9, user.getAddress());
preState.setDate(10, user.getRegisterTime());
// preState.execute("begin"); // 事务开始
preState.executeUpdate();
// preState.execute("commit"); // 提交插入操作
return true;
} catch (SQLException e) { /*try {
preState.execute("rollback"); // 回滚
} catch (SQLException e1) {
e1.printStackTrace();
}*/
e.printStackTrace();
return false;
} finally {
DBManager.closeAll(connection, preState, resultSet);
}
}
/**
* 删除一个指定编号的用户
* 如果删除成功返回true,否则返回 false
*
* @param id给定的User的编号
* @return true或false
*/
public boolean deleteUser(int id) { try {
connection = DBManager.getConnection(); // 获得一个数据库连接
state = connection.createStatement(); state.execute("begin"); // 事务开始
state.executeUpdate("DELETE FROM user WHERE id='" + id+ "'");
System.out.println(id);
state.execute("commit"); // 提交事务
return true;
} catch (Exception e) { try {
state.execute("rollback"); // 如果出现异常数据库回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
return false;
} finally {
DBManager.closeAll(connection, state, resultSet);
}
} /**
* @param args
* @throws ParseException
* @throws ParseException
*/
public static void main(String[] args) throws ParseException {
//Calendar.getInstance().getTimeInMillis();
DateFormat df = DateFormat.getDateInstance(DateFormat.LONG, Locale.FRANCE);
DateFormat d = DateFormat.getDateInstance();
Date date = new Date(d.parse("2012-03-3").getTime());
//Date date = new Date(Calendar."2012-03-3");
// (String name, String pass, int role,
// String realName, int age, String gender, String phone, String email,
// String address, String postcode, Date registerTime)
User user = new User("12","12",0,"12",23,"女","12345",
"123","123","12345",date);
UserDAO userDAO = new UserDAO();
userDAO.insertUser(user);
Date address = ((User)(userDAO.queryUser().get(0))).getRegisterTime();
System.out.println(address);
System.out.println("fadf");
}}
2.java.sql.SQLException: Bad format for DATE '2012430' in column 12.
3.java.sql.SQLException: Field 'id' doesn't have a default value你一个一个的看把,第一个'bookshop.bookcategory 你看你的配置文件有没有这个对应属性。
第二个 Bad format for DATE '2012430' 你不要用时间类型了,直接换成string的,
第三个Field 'id' doesn't have a default value,给id一个sequence吧,让ID自动生成.
第二个 Bad format for DATE '2012430' 你不要用时间类型了,直接换成string的,
可以用时间类型但需要转换一下 simpldatafrome第三个Field 'id' doesn't have a default value,给id一个sequence吧,让ID自动生成.
有两个原因 看看你数据库中有没有给id自动增长设置,还有就是配置文件的问题了。
2:java.sql.SQLException: Bad format for DATE '2012430' in column 12.
类型错了,2012430不是DATE类型 数据库这个字段是DATE类型,建议数据库直接改成varchar
3:java.sql.SQLException: Field 'id' doesn't have a default value
插入时主键不能为空,你没有设置标识,没有默认,报错,建议设置成自增