Luis Villamarin

I'm trying to user Apache Commons Streaming API to get the information from an excel spreadsheet. I keep on getting the following error:

WARNING: StandardWrapperValve[ReportServlet]: PWC1406: Servlet.service() for servlet ReportServlet threw exception java.io.IOException: Unable to read entire header; 13 bytes read; expected 512 bytes at org.apache.poi.poifs.storage.HeaderBlock.alertShortRead(HeaderBlock.java:226) at org.apache.poi.poifs.storage.HeaderBlock.readFirst512(HeaderBlock.java:207) at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104) at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:138) at eBooks.controller.ReportServlet.processRequest(ReportServlet.java:54) at eBooks.controller.ReportServlet.doPost(ReportServlet.java:161) at javax.servlet.http.HttpServlet.service(HttpServlet.java:688) at javax.servlet.http.HttpServlet.service(HttpServlet.java:770) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231) at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:722) SEVERE: org.apache.commons.fileupload.FileUploadBase$InvalidContentTypeException: the request doesn't contain a multipart/form-data or multipart/mixed stream, content type header is null at org.apache.commons.fileupload.FileUploadBase$FileItemIteratorImpl.<init>(FileUploadBase.java:908) at org.apache.commons.fileupload.FileUploadBase.getItemIterator(FileUploadBase.java:331) at org.apache.commons.fileupload.servlet.ServletFileUpload.getItemIterator(ServletFileUpload.java:148) at eBooks.controller.ReportServlet.processRequest(ReportServlet.java:45) at eBooks.controller.ReportServlet.doPost(ReportServlet.java:161) at eBooks.controller.ReportServlet.doGet(ReportServlet.java:153) at javax.servlet.http.HttpServlet.service(HttpServlet.java:668) at javax.servlet.http.HttpServlet.service(HttpServlet.java:770) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231) at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:722) SEVERE: org.apache.commons.fileupload.FileUploadBase$InvalidContentTypeException: the request doesn't contain a multipart/form-data or multipart/mixed stream, content type header is null at org.apache.commons.fileupload.FileUploadBase$FileItemIteratorImpl.<init>(FileUploadBase.java:908) at org.apache.commons.fileupload.FileUploadBase.getItemIterator(FileUploadBase.java:331) at org.apache.commons.fileupload.servlet.ServletFileUpload.getItemIterator(ServletFileUpload.java:148) at eBooks.controller.ReportServlet.processRequest(ReportServlet.java:45) at eBooks.controller.ReportServlet.doPost(ReportServlet.java:161) at eBooks.controller.ReportServlet.doGet(ReportServlet.java:153) at javax.servlet.http.HttpServlet.service(HttpServlet.java:668) at javax.servlet.http.HttpServlet.service(HttpServlet.java:770) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231) at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:722)
This is the code of ReportServlet that process the information:

package eBooks.controller; /** * @author L10 */ import eBooks.business.Account; import eBooks.business.Report; import eBooks.business.User; import java.io.*; import java.util.logging.Level; import java.util.logging.Logger; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.http.*; import org.apache.commons.fileupload.FileItemIterator; import org.apache.commons.fileupload.FileItemStream; import org.apache.commons.fileupload.FileUploadException; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class ReportServlet extends HttpServlet { protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { // Check that we have a file upload request boolean isMultipart = ServletFileUpload.isMultipartContent(request); //Create a new file upload Handler ServletFileUpload upload = new ServletFileUpload(); //Parse the request FileItemIterator iter = upload.getItemIterator(request); while(iter.hasNext()) { FileItemStream item = iter.next(); String name = item.getFieldName(); InputStream stream = item.openStream(); POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); System.out.println(wb.getSheetName(0)); // session HttpSession session = request.getSession(); //Define define values obatained from the document Report report = new Report(); User user = new User(); Account account = new Account(); int reportType = report.getReportType(); String emailAddress = user.getEmailAddress(); String account_id = account.getAccount_id(); String accountName = account.getAccountName(); String userRole = user.getUserRole(); String reportName = report.getReportName(); // is the spreadsheet Name String reportDate = report.getReportDate(); // is the period on the report double totalUS = report.getMonthTotalUS(); double totalEUR = report.getMonthTotalEUR(); double grandTotal = report.getMonthGrandTotal(); double averagePrice = report.getAverageSalePrice(); int totalUnitsSold = report.getTotalUnitsSold(); double monthlyAverageUnitsSold = report.getMonthlyAverageUnitsSold(); double dailyAverageUnitsSold = report.getDailyAverageUnitsSold(); int totalUnitsRefunded = report.getTotalUnitsRefunded(); double monthlyAverageUnitsRefunded = report.getMonthlyAverageUnitsRefunded(); double dailyAverageUnitsRefuded = report.getDailyAverageUnitsRefuded(); double monthlyAverageRoyalties = report.getMonthlyAverageRoyalties(); // set values for the user, account & report objects report.setReportType(reportType); user.setEmailAddress(emailAddress); account.setAccount_id(account_id); account.setAccountName(accountName); user.setUserRole(userRole); report.setReportName(reportName); // is the spreadsheet Name report.setReportDate(reportDate); // is the period on the report report.setMonthTotalUS(totalUS); report.setMonthTotalEUR(totalEUR); report.setMonthGrandTotal(grandTotal); report.setAverageSalePrice(averagePrice); report.setTotalUnitsSold(totalUnitsSold); report.setMonthlyAverageUnitsSold(monthlyAverageUnitsSold); report.setDailyAverageUnitsSold(dailyAverageUnitsSold); report.setTotalUnitsRefunded(totalUnitsRefunded); report.setMonthlyAverageUnitsRefunded(monthlyAverageUnitsRefunded); report.setDailyAverageUnitsRefuded(dailyAverageUnitsRefuded); report.setMonthlyAverageRoyalties(monthlyAverageRoyalties); // TODO: Add information to the database // set Session for the user, account & report session.setAttribute("User", user); session.setAttribute("Account", account); session.setAttribute("Report", report); Cookie emailCookie = new Cookie("emailCookie", emailAddress); emailCookie.setMaxAge(60*60*24*365*2); emailCookie.setPath("/"); response.addCookie(emailCookie); String url = "/WEB-INF/view/Dashboard.jsp"; RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(url); dispatcher.forward(request, response); } } catch (FileUploadException ex) { Logger.getLogger(ReportServlet.class.getName()).log(Level.SEVERE, null, ex); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } } This how my form looks: [code=java] <form action="ReportServlet" method="post" enctype="multipart/form-data"> <input type="file" name="file"/> <br /> <input type="submit" value="Upload Report" name="upload_report_btn" /> </form>[/code]
I'm using glassfish. Any ideas why I keep on getting this message?

I'm trying to get my login form to with a MySQL Database and keep on getting the following error:

WARNING: WEB9102: Web Login Failed: com.sun.enterprise.security.auth.login.common.LoginException: Login failed: Security Exception
WARNING: WEB9102: Web Login Failed: com.sun.enterprise.security.auth.login.common.LoginException: Login failed: Security Exception
WARNING: WEB9102: Web Login Failed: com.sun.enterprise.security.auth.login.common.LoginException: Login failed: Security Exception

That's all I get from the stack trace. I'm following this tutorial: http://blog.eisele.net/2011/01/jdbc-security-realm-and-form-based.html

This is my mapping on glassfish-web.xml:

<security-role-mapping> <role-name>user</role-name> <group-name>user</group-name> </security-role-mapping>

This is my web.xml:

<security-constraint> <display-name>user</display-name> <web-resource-collection> <web-resource-name>Users Area</web-resource-name> <description>Protected Users Area</description> <url-pattern>/user/*</url-pattern> <http-method>GET</http-method> <http-method>PUT</http-method> <http-method>HEAD</http-method> <http-method>POST</http-method> <http-method>OPTIONS</http-method> <http-method>TRACE</http-method> <http-method>DELETE</http-method> </web-resource-collection> <auth-constraint> <description/> <role-name>user</role-name> </auth-constraint> </security-constraint> <login-config> <auth-method>FORM</auth-method> <realm-name>jdbcRealm</realm-name> <form-login-config> <form-login-page>/login.jsp</form-login-page> <form-error-page>/login-error.jsp</form-error-page> </form-login-config> </login-config> <security-role> <description>user security role</description> <role-name>user</role-name> </security-role>

This is my login form:

<form action="j_security_check" class="form" method="post"> <p class="email"> <input type="text" name="j_username" id="j_username"/> <label for="j_username">E-mail / Username</label> </p> <p class="pwd"> <input type="password" name="j_password" id="j_password" /> <label for="j_password">Password</label> </p> <p class="submit"> <input name="login" type="submit" value="Login" /> <input name="reset_pwd" type="submit" value="Reset Password"> </p> </form>

This a screenshot of my realm setup in the server. I didn't use encryption to simplify things for the moment:


Since I have nothing else in the stack but what I posted above. I'm really not sure what's wrong. I would appreciate if anyone can me a push in the right direction.

Problem solved. I had to update Netbeans to 7.2 from 7.0 and solved the issue, now I was able to add the file from add new file > Glassfish > Other > glassfish-web.xml / xml descriptor.

I know that this is the servlet forum but I didn't find the Glassfish/Netbeans related to this issue.

I'm currently trying to set up a secured folder with a few pages within my Java Web App to allow only registered users to access the pages in that folder.

However, while trying to do the setup/mapping of the groups I have been having the issue that the sun-web.xml is missing. I'm using Netbeans IDE.

Any ideas why this happened and how to fix it? I've done research and haven't really found a solution for the issue.

Thanks in advance

That suggests to me that the security constraints are preventing the CSS from being downloaded. Have you done some browser debugging to see whether that is the case?

I debugged and found that the css is not being downloaded. I'm new to Java Web development would you be able to let me know how to enable the css to be download to the app when it accesses the pages in the secured folder?

Thanks in advance.

Hello,

I'm having a silly problem with my css and one of my jsps. I created a restricted folder where people who are registered only can access the pages inside.

For some reason the CSS disappears when I go to any of the pages inside the secured folder, the login.jsp (located outside the secure folder) or login-error.jsp (also outside the secure folder).

I've tried using absolute and relative links to link back to the CSS but still none seems to work.

If I remove the security constraint then everything start to work again.

Any ideas on how to solve this?

Jeanne and Chris,

Thanks for the responses.

@Chris - I used MySQL Workbench to create the DB and I have three tables that come into action for the problem that I'm presenting. The field account_type_account_name is the name of the 'account_name' in the table 'account_type'. Therefore the field is account_type_account_name is a FK in the account table (which is the one I'm trying to populate).

@Chris and @Jeanne - The User table PK is emailAddress, and the Account table PK is account ID which generated using java.util.UUID, and the account_type table PK is account_name. The account_name is FK in the account table and the emailAddress is FK in the account table. The account_type table already has information stored in it. For the moment the account_type table has only 3 Rows.

I have worked the insert method and I added some other fields and referenced the fields that were missing. This is how it looks right now, however the 2nd table is still no populated with data after the form was filled. It is not giving me any stacktrace.

public static int insert(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; PreparedStatement ps2 = null; String queryUser = "INSERT INTO user (fName,lName, emailAddress, password, dateOfBirth, " + "phone, address, city, state, country, zip, role_userRole) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; String queryAccount = "INSERT INTO account (account_id, account_type_account_name, user_role_userRole, ) " + "registration_date, expiration_date, rennovation_date " + "VALUES (?, ?, ?, ?, ?, ?, ?) "; try { ps = connection.prepareStatement(queryUser); ps.setString(1, user.getfName()); ps.setString(2, user.getlName()); ps.setString(3, user.getEmailAddress()); ps.setString(4, user.getPassword()); ps.setString(5, user.getDateOfBirth()); ps.setString(6, user.getPhone()); ps.setString(7, user.getAddress()); ps.setString(8, user.getCity()); ps.setString(9, user.getState()); ps.setString(10, user.getCountry()); ps.setString(11, user.getZip()); ps.setString(12, user.getUserRole()); ps.executeUpdate(); ps2 = connection.prepareStatement(queryAccount); ps2.setString(1, account.getAccount_id()); ps2.setString(2, account.getAccountName()); ps2.setString(3, user.getUserRole()); ps2.setString(4, user.getEmailAddress()); ps2.setString(5, account.getRegistrationDate()); ps2.setString(6, account.getExpirationDate()); ps2.setString(7, account.getRenovationDate()); return ps2.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); DBUtil.closePreparedStatement(ps2); pool.freeConnection(connection); } }

Thanks for the response. I have followed your suggestions however the application still only saving information to one table. Would it make sense to create another class for the second time?

Here is an updated version of the code:

public static int insert(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; PreparedStatement ps2 = null; String queryUser = "INSERT INTO user (fName,lName, emailAddress, password, dateOfBirth, " + "phone, address, city, state, country, zip, role_userRole) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; String queryAccount = "INSERT INTO account (account_id, account_type_account_name) " + "VALUES (?, ?) "; try { ps = connection.prepareStatement(queryUser); ps.setString(1, user.getfName()); ps.setString(2, user.getlName()); ps.setString(3, user.getEmailAddress()); ps.setString(4, user.getPassword()); ps.setString(5, user.getDateOfBirth()); ps.setString(6, user.getPhone()); ps.setString(7, user.getAddress()); ps.setString(8, user.getCity()); ps.setString(9, user.getState()); ps.setString(10, user.getCountry()); ps.setString(11, user.getZip()); ps.setString(12, user.getUserRole()); ps.executeUpdate(); ps2 = connection.prepareStatement(queryAccount); ps2.setString(1, account.getAccount_id()); ps2.setString(2, account.getAccountName()); return ps2.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); DBUtil.closePreparedStatement(ps2); pool.freeConnection(connection); } }

I have two different tables in a MySQL DB. I have successfully inserted data to to the parent table. However, I 'm having problems to the second table which has the FK from the first table.

I'm new to JDBC and haven't been able to find documentation on how to add data to two tables using the same insert method. I suppose I have to use two different preparedStatement within the same Insert method but I'm not really, plus it doesn't work.

Any ideas on how to solve this where I can research to find the solution.

This is my insert method:
public static int insert(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; PreparedStatement ps2 = null; String queryUser = "INSERT INTO user (fName,lName, emailAddress, password, dateOfBirth, " + "phone, address, city, state, country, zip, role_userRole) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; String queryAccount = "INSERT INTO account (account_id, account_type_account_name) " + "VALUES (?, ?) "; try { ps = connection.prepareStatement(queryUser); ps.setString(1, user.getfName()); ps.setString(2, user.getlName()); ps.setString(3, user.getEmailAddress()); ps.setString(4, user.getPassword()); ps.setString(5, user.getDateOfBirth()); ps.setString(6, user.getPhone()); ps.setString(7, user.getAddress()); ps.setString(8, user.getCity()); ps.setString(9, user.getState()); ps.setString(10, user.getCountry()); ps.setString(11, user.getZip()); ps.setString(12, user.getUserRole()); ps2 = connection.prepareStatement(queryAccount); ps2.setString(13, account.getAccount_id()); ps2.setString(14, account.getAccountName()); return ps.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } }

Hey Wendy – Thanks for the suggestion. I thought there was something specific to servlets to pass int values.

I solved the issue using the regular Integer.parseInt.

In case others have the same question this is what I did:
int typeAcct=Integer.parseInt(request.getParameter("typeAcct"));

Thanks again.

Hi I worked on the database and effectively there was a relationship that was not correctly set. However, what I did, didn't fully fixed the problem this what I'm getting now as an error:


SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ebooksalesdb_schema`.`user`, CONSTRAINT `fk_user_account1` FOREIGN KEY (`account_account_id`, `account_account_type_account_name`) REFERENCES `account` (`account_id`, `account_type_account_na)

I noticed that at the end of the error it says `account_type_account_na, instead of `account_type_account_name'). I checked for the name inconsistency and couldn't find anywhere that `account_type_account_na. Could this be the reason ?


I rebuilt the database and I'm still getting the same error.

Do you have idea why this keeps on happening?

Could this be that the FK was not properly created when the DB/Table was created?

Hello,

I'm trying to store data to from a registration form (html). everything runs smoothly until I try to store the the Foreign Keys. When I test the form I get the following error:

SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ebooksalesdb_schema`.`user`, CONSTRAINT `fk_user_account_type1` FOREIGN KEY (`account_type_account_name`) REFERENCES `account_type` (`account_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)

After running the application the stack trace indicates that the error is located at:
at eBooks.data.UserDB.insert(UserDB.java:47)
at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:71)


This is the code for the RegisterUserServlet:
package eBooks.controller; import com.sun.xml.registry.uddi.bindings_v2_2.Phone; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.RequestDispatcher; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import javax.servlet.http.Cookie; import eBooks.business.User; import eBooks.business.Account; import eBooks.data.UserDB; import java.util.Date; /** * @author L10 */ public class RegisterUserServlet extends HttpServlet { public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { HttpSession session = request.getSession(); String fName = request.getParameter("fName"); String lName = request.getParameter("lName"); String emailAddress = request.getParameter("emailAddress"); String password = request.getParameter("password"); String dateOfBirth = request.getParameter("dateOfBirth"); String phone = request.getParameter("phone"); String address = request.getParameter("address"); String city = request.getParameter("city"); String country = request.getParameter("country"); String state = request.getParameter("state"); String zip = request.getParameter("zip"); String userRole = "User"; String accountName = request.getParameter("accountType"); User user = new User(); user.setfName(fName); user.setlName(lName); user.setEmailAddress(emailAddress); user.setPassword(password); user.setDateOfBirth(dateOfBirth); user.setPhone(phone); user.setAddress(address); user.setCity(city); user.setCountry(country); user.setState(state); user.setZipcode(zip); user.setUserRole(userRole); Account account = new Account(); account.setAccountName(accountName); // Add information to the database if(UserDB.emailExists(emailAddress)) UserDB.update(user, account); else UserDB.insert(user, account); session.setAttribute("User", user); Cookie emailCookie = new Cookie("emailCookie", emailAddress); emailCookie.setMaxAge(60*60*24*365*2); emailCookie.setPath("/"); response.addCookie(emailCookie); String url = "/WEB-INF/view/registration_confirmation.jsp"; RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(url); dispatcher.forward(request, response); } }


This is the code for the UserDB class:
package eBooks.data; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import eBooks.business.User; import eBooks.business.Account; import eBooks.util.DBUtil; /** * @author L10 */ public class UserDB { public static int insert(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; String query = "INSERT INTO user (fName,lName, emailAddress, password, dateOfBirth, " +"phone, address, city, state, country, zip, account_type_account_name, role_userRole) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; try { ps = connection.prepareStatement(query); ps.setString(1, user.getfName()); ps.setString(2, user.getlName()); ps.setString(3, user.getEmailAddress()); ps.setString(4, user.getPassword()); ps.setString(5, user.getDateOfBirth()); ps.setString(6, user.getPhone()); ps.setString(7, user.getAddress()); ps.setString(8, user.getCity()); ps.setString(9, user.getState()); ps.setString(10, user.getCountry()); ps.setString(11, user.getZipcode()); ps.setString(12, account.getAccountName()); ps.setString(13, user.getUserRole()); return ps.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } } public static int update(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; String query = "UPDATE user SET " + "fName = ?, " + "lName = ?, " + "password = ?, " + "dateOfBirth = ?, " + "phone = ?, " + "address = ?, " + "city = ?, " + "state = ?, " + "country = ?, " + "zip = ?, " + "account_type_account_name = ?, " + "role_userRole = ? " + "WHERE emailAddress = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, user.getfName()); ps.setString(2, user.getlName()); ps.setString(3, user.getEmailAddress()); ps.setString(4, user.getPassword()); ps.setString(5, user.getDateOfBirth()); ps.setString(6, user.getPhone()); ps.setString(7, user.getAddress()); ps.setString(8, user.getCity()); ps.setString(9, user.getState()); ps.setString(10, user.getCountry()); ps.setString(11, user.getZipcode()); ps.setString(12, account.getAccountName()); ps.setString(13, user.getUserRole()); return ps.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } } public static int delete(User user, Account account) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; String query = "DELETE FROM user " + "WHERE emailAddress = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, user.getEmailAddress()); ps.setString(2, account.getAccountName()); ps.setString(3, user.getUserRole()); return ps.executeUpdate(); } catch(SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } } public static boolean emailExists(String emailAddress) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String query = "SELECT emailAddress FROM user "+ "WHERE emailAddress = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, emailAddress); rs = ps.executeQuery(); return rs.next(); } catch(SQLException e) { e.printStackTrace(); return false; } finally { DBUtil.closeResultSet(rs); DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } } public static User selectUser(String emailAddress) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String query = "SELECT * FROM user "+ "WHERE emailAddress = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, emailAddress); rs = ps.executeQuery(); User user = null; Account account = null; if (rs.next()) { user = new User(); account = new Account(); user.setfName(rs.getString("fName")); user.setlName(rs.getString("lName")); user.setEmailAddress(rs.getString("emailAddress")); user.setPassword(rs.getString("password")); user.setDateOfBirth(rs.getString("dateOfBirth")); user.setPhone(rs.getString("phone")); user.setAddress(rs.getString("address")); user.setCity(rs.getString("city")); user.setState(rs.getString("state")); user.setCountry(rs.getString("country")); user.setZipcode(rs.getString("zip")); account.setAccountName(rs.getString("account_type_account_name")); user.setUserRole(rs.getString("account_role_userRole")); } return user; } catch(SQLException e) { e.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } } }

Kunal thanks for the response.

I have changed that to a string now. However, I would still like to ask you how to pass an integer from form to a servlet to a DB?

Thanks in advance.

Luis

Hello,

In my database I have a int field called accountType. The data to be stored in that field is coming from select element (html) with different 3 different options as follows:
<p class="acct_type"> <select name="accountType" id="accntTypeList"> <option value="${param.accountType}">Free</option> <option value="${param.accountType}">Premier</option> <option value="${param.accountType}">Pro</option> </select> <label for="accntTypeList">Select Account</label> </p>

I have a servlet that gets the parameter from the html elements. For a string I know I have to use something like String zip = request.getParameter("zip"); , However I'm a little confused about how to get the selected option value as an int so I can store it as int using a DB class.

I will appreciate any suggestions on how to approach this.

Thanks in advance.