My code does not compile when I inserted the other tables and Im not really good at SQL statements and such. Is it possible if I can use a single query for inserting into multiple tables?
Code below
What I have tried:
<%@ page import ="java.sql.*" %>
<%
String id = request.getParameter("accid");
String surname = request.getParameter("SURNAME");
String firstname = request.getParameter("FIRSTNAME");
String middlename = request.getParameter("MIDDLENAME");
String nameextension = request.getParameter("NAMEEXTENSION");
String birthdate = request.getParameter("BIRTHDATE");
String birthplace = request.getParameter("BIRTHPLACE");
String sex= request.getParameter("SEX");
String civilstatus= request.getParameter("CIVILSTATUS");
String weight = request.getParameter("WEIGHT");
String height = request.getParameter("HEIGHT");
String bloodtype = request.getParameter("BLOODTYPE");
String gsisno = request.getParameter("GSISNO");
String pagibigidno = request.getParameter("PAGIBIGIDNO");
String philhealthno = request.getParameter("PHILHEALTHNO");
String sssno = request.getParameter("SSSNO");
String tinno = request.getParameter("TINNO");
String agencyemployeeno = request.getParameter("AGENCYEMPLOYEENO");
String citizenship = request.getParameter("CITIZENSHIP");
String residentialaddress = request.getParameter("RESIDENTIALADDRESS");
String razipcode = request.getParameter("RAZIPCODE");
String permanentaddress = request.getParameter("PERMANENTADDRESS");
String pazipcode = request.getParameter("PAZIPCODE");
String telephoneno = request.getParameter("TELEPHONENO");
String mobileno = request.getParameter("MOBILENO");
String emailaddress = request.getParameter("EMAILADDRESS");
String spousefirstname = request.getParameter("spouseFIRSTNAME");
String spousemiddlename = request.getParameter("spouseMIDDLENAME");
String spousesurname = request.getParameter("spouseSURNAME");
String spouseoccupation = request.getParameter("spouseOCCUPATION");
String spouseebn = request.getParameter("spouseEBN");
String spouseba = request.getParameter("spouseBA");
String spousetelephone = request.getParameter("spouseTELEPHONE");
String childname = request.getParameter("childNAME");
String childbirthdate = request.getParameter("childBIRTHDATE");
String fatherfirstname = request.getParameter("fatherFIRSTNAME");
String fathermiddlename = request.getParameter("fatherMIDDLENAME");
String fathersurname = request.getParameter("fatherSURNAME");
String fathernameextension = request.getParameter("fatherNAMEEXTENSION");
String motherfirstname = request.getParameter("motherFIRSTNAME");
String mothermiddlename = request.getParameter("motherMIDDLENAME");
String mothersurname = request.getParameter("motherSURNAME");
String graduate = request.getParameter("gsname");
String college = request.getParameter("collegename");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee_database",
"root", "");
PreparedStatement ps = null;
String query= "INSERT INTO personal_information(SURNAME, FIRSTNAME, MIDDLENAME, NAMEEXTENSION, BIRTHDATE, BIRTHPLACE, SEX, CIVILSTATUS, WEIGHT, HEIGHT, BLOODTYPE, GSISNO, PAGIBIGIDNO, PHILHEALTHNO, SSSNO, TINNO, AGENCYEMPLOYEENO, CITIZENSHIP, RESIDENTIALADDRESS, RAZIPCODE, PERMANENTADDRESS, PAZIPCODE, TELEPHONENO, MOBILENO, EMAILADDRESS, spouseFIRSTNAME, spouseMIDDLENAME, spouseSURNAME, spouseOCCUPATION, spouseEBN, spouseBA, spouseTELEPHONE, childNAME, childBIRTHDATE, fatherFIRSTNAME, fatherMIDDLENAME, fatherSURNAME, fatherNAMEEXTENSION, motherFIRSTNAME, motherMIDDLENAME, motherSURNAME) "
+ "VALUES('"+surname+"','"+firstname+"','"+middlename+"','"+nameextension+"','"+birthdate+"','"+birthplace
+"','"+sex+"','"+civilstatus+"','"+weight+"','"+height+"','"+bloodtype+"','"+gsisno+"','"+pagibigidno+"','"+philhealthno+"','"+sssno+"','"+tinno+"','"+agencyemployeeno+"','"+citizenship+"','"+residentialaddress+"','"+razipcode+"','"+permanentaddress+"','"+pazipcode+"','"+telephoneno+"','"+mobileno+"','"+emailaddress+"','"+spousefirstname+"','"+spousemiddlename+"','"+spousesurname+"','"+spouseoccupation+"','"+spouseebn+"','"+spouseba+"','"+spousetelephone+"','"+childname+"','"+childbirthdate+"','"+fatherfirstname+"','"+fathermiddlename+"','"+fathersurname+"','"+fathernameextension+"','"+motherfirstname+"','"+mothermiddlename+"','"+mothersurname+"')";
"INSERT INTO graduatestudies(gsname) " + "VALUES ('"+graduate+"')";
"INSERT INTO college(collegename) " + "VALUES('"+college+"')";
ps = con.prepareStatement(query);
try{
int i = ps.executeUpdate(query);
if (i > 0){
session.setAttribute("loginid", surname);
response.sendRedirect("adminclientmanagement.jsp");
} else {
response.sendRedirect("clientsignuperror.jsp");
}
} catch(SQLIntegrityConstraintViolationException duplicate){
response.sendRedirect("clientsignuperror.jsp");
}
%>