fred kitu

Greenhorn

+ Follow

news 5 Posts 2 Threads

since Apr 02, 2013

Merit badge: grant badges

For More

Cows and Likes

Cows

Total received

In last 30 days

Forums and Threads

Recent posts by fred kitu

passing parameter from jsp view page tp jsp edit page (crud)

thanks bear
im just now taking webapp class and i will rem that
do you have a sample servlet code i can refer to?

11 years ago

JSP

passing parameter from jsp view page tp jsp edit page (crud)

i can generate users from database to a view page
how do i pass the contact.getID value to a jsp edit page so it can populate that user to allow me to edit

here is my code

displaycontacts.jsp

<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>display contacts</title>
</head>
<body>
<%
List<Contact> cont = (List<Contact>)request.getAttribute("cont");
boolean useDataSource = false;
if (null == cont) {
useDataSource = Boolean.valueOf(request.getParameter("useDataSource"));
cont = ContactService.getAllContacts(useDataSource);
}
%>
<h1>Listing of contacts - <%= (useDataSource ? "DataSource Pool" : "DriverManager") %></h1>
<table>
<tr>
<td>Contact Id |</td>
<td>First Name |</td>
<td>Last Name |</td>
<td>WORK_PHONE |</td>
<td>HOME_PHONE |</td>
<td>WORK_EMAIL |</td>
<td>HOME_EMAIL |</td>
<td>ANNIVERSARY |</td>
<td>DATE |</td>
<td>EDIT |</td>
<td>DELETE |</td>
</tr>
<%
for (Contact contact : cont) {
%>
<tr>
<td><%=contact.getID() %></td>
<td><%=contact.getFirstName() %></td>
<td><%=contact.getLastName()%></td>
<td><%=contact.getPhone().getWorkPhone()%></td>
<td><%=contact.getPhone().getHomePhone()%></td>
<td><%=contact.getEmail().getWorkEmail()%></td>
<td><%=contact.getEmail().getHomeEmail()%></td>
<td><%=contact.getAnniversary().getBirthday()%></td>
<td><%=contact.getAnniversary().getMbirthday() %></td>
<td><a href="editContacts.jsp?contactId=<%= contact.getID() %>">Edit</a></td>
<td><a href="deleteContact?contactId=<%= contact.getID() %>">Delete</a></td>
</tr>
<%
}
%>
</table>
<p><a href="index.jsp">Click here to add a customer</a></p>
<p><a href="contact.jsp">Click here to add a contact</a></p>
<p><a href="http://<%= request.getServerName()%>:<%=request.getLocalPort() %>/Assignment_3/displaycontacts.jsp?useDataSource=true">With DataSource</a><br />
<a href="http://<%= request.getServerName()%>:<%=request.getLocalPort() %>/Assignment_3/displaycontacts.jsp?useDataSource=false">With DriverManager</a></p>
</body>
</html>
editContacts.jsp
[code=java]
<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1" import="java.util.*"%>
<%@ page import="java.util.List,
com.assign4.model.Contact,
com.assign4.model.Phone,
com.assign4.model.Email,
com.assign4.model.Anniversary,
com.assign4.service.ContactService " %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
<%!
int id ;
String firstName="";
String lastName="";
String workPhone="";
String homePhone="";
String workEmail="";
String homeEmail="";
String birthDay="";
String mbirthDay="";
List cont=null;
%>
<%
if(request.getAttribute("cont")!=null && request.getAttribute("cont")!="")
{
cont = (ArrayList)request.getAttribute("cont");
int id = Integer.parseInt(("contact.getID()").toString());
//contactId = Integer.parseInt(cont.get(contactId).toString());
String firstName = cont.get(1).toString();
String lastName = cont.get(2).toString();
String workPhone =cont.get(3).toString();
String homePhone = cont.get(4).toString();
String workEmail = cont.get(5).toString();
String homeEmail = cont.get(6).toString();
String birthDay = cont.get(7).toString();
String mbirthDay = cont.get(8).toString();
}
%>
<form action="EditContact" method="post">
<p>
Id : <input type="text" name="id" value="<%=d%>"/><br />
First Name : <input type="text" name="firstName" value="<%=firstName%>"/><br />
Last Name : <input type="text" name="lastName" value="<%=lastName%>"/><br />
Work Phone : <input type="text" name="workPhone" value="<%=workPhone%>"/><br />
Home Phone : <input type="text" name="workPhone" value="<%=workPhone%>"/><br />
WORK EMAIL : <input type="text" name="workEmail" value="<%=workEmail%>" /><br />
HOME EMAIL : <input type="text" name="homeEmail" value="<%=homeEmail%>"/><br />
Anniversary : <input type="text" name="birthDay" value="<%=birthDay%>"/><br />
Date : <input type="text" name="mbirthDay" value="<%=mbirthDay%>" /><br />
</p>
<p> <input type="submit" value="Next" /></p>
</form>
</body>
</html>

[/code]

11 years ago

JSP

error: Parameter index out of range (3 > number of parameters, which is 2).

thanks vannesa
i more question
im trying to set the foreign key with SELECT_LAST_ID()
SELECT_LAST_ID() can only work from the last auto increment entry...
how do i modify that to get from previous entries or a specific ? foreign key the remainder of the table happens to be CONTACT_ID
i get this error

11 years ago

JDBC and Relational Databases

error: Parameter index out of range (3 > number of parameters, which is 2).

i apologize in advance

this has tags in my dev kit page but when i paste it here they no longer there

dont how to add them on this page yet...in the meantime here is the error log

apologies once again

Apr 02, 2013 5:07:02 AM org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jdk1.7.0_13\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:/Program Files/Java/jre7/bin/client;C:/Program Files/Java/jre7/bin;C:/Program Files/Java/jre7/lib/i386;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\ThinkPad\Bluetooth Software\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files\Common Files\Lenovo;C:\Program Files\Common Files\Ulead Systems\MPEG;C:\Program Files\Lenovo\Access Connections\;C:\Program Files\Lenovo\Client Security Solution;C:\METRO STATE FILES\ICS 425\software\eclipse;;.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:Assignment3' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:assign3' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:cs425s13-lab5' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:Assignment_3' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:assign3- original' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:servlet' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:assign4' did not find a matching property.
Apr 02, 2013 5:07:02 AM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-bio-8080"]
Apr 02, 2013 5:07:02 AM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-bio-8009"]
Apr 02, 2013 5:07:02 AM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 1014 ms
Apr 02, 2013 5:07:02 AM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Apr 02, 2013 5:07:02 AM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.35
Apr 02, 2013 5:07:05 AM org.apache.catalina.core.StandardContext resourcesStart
SEVERE: Error starting static Resources
java.lang.IllegalArgumentException: Document base C:\Users\User\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\Assignment3 does not exist or is not a readable directory
at org.apache.naming.resources.FileDirContext.setDocBase(FileDirContext.java:138)
at org.apache.catalina.core.StandardContext.resourcesStart(StandardContext.java:4912)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5092)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)

Apr 02, 2013 5:07:05 AM org.apache.catalina.core.StandardContext startInternal
SEVERE: Error in resourceStart()
Apr 02, 2013 5:07:05 AM org.apache.catalina.core.StandardContext startInternal
SEVERE: Error getConfigured
Apr 02, 2013 5:07:05 AM org.apache.catalina.core.StandardContext startInternal
SEVERE: Context [/Assignment3] startup failed due to previous errors
Apr 02, 2013 5:07:05 AM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-bio-8080"]
Apr 02, 2013 5:07:05 AM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["ajp-bio-8009"]
Apr 02, 2013 5:07:05 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 2255 ms
Apr 02, 2013 5:07:15 AM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/Assignment_3] has started
Apr 02, 2013 5:07:15 AM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/Assignment_3] is completed
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3840)
at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3784)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:120)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:120)
at com.assign4.service.ContactService.persistContact(ContactService.java:59)
at com.assign4.controllers.CreateContactServlet.doPost(CreateContactServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3840)
at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3784)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:120)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:120)
at com.assign4.service.ContactService.persistContact(ContactService.java:59)
at com.assign4.controllers.CreateAnniversaryServlet.doPost(CreateAnniversaryServlet.java:49)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
java.sql.SQLException: Access denied for user 'assign3'@'localhost' (using password: NO)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:925)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1704)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1250)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
at java.sql.DriverManager.getConnection(DriverManager.java:579)
at java.sql.DriverManager.getConnection(DriverManager.java:221)
at com.assign4.service.ContactService.getAllContacts(ContactService.java:132)
at org.apache.jsp.displaycontacts_jsp._jspService(displaycontacts_jsp.java:84)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:487)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:412)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:339)
at com.assign4.controllers.CreateAnniversaryServlet.doPost(CreateAnniversaryServlet.java:53)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)

11 years ago

JDBC and Relational Databases

error: Parameter index out of range (3 > number of parameters, which is 2).

im stuck on this problem where i can generate entries from the db but i cant do inputs
here is the service class and the above mentioned is the error im getting
i have triple checked the variables and they seem to be fine
help please. thanks

kitu

package com.assign4.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.assign4.model.Contact;
import com.assign4.model.Email;
import com.assign4.model.Phone;
import com.assign4.model.Anniversary;
public class ContactService {
public static void persistContact(Contact contact) {
Connection conn = null;
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/ics425");
conn = ds.getConnection();
conn.setAutoCommit(false);
String contactInsert = "insert into contact(f_name, l_name) values(?,?)";
String phoneInsert = "insert into phone(work_phone, home_phone) values(?,?)";
String emailInsert = "insert into email(work_email, home_email) values(?,?)";
String anniversaryInsert = "insert into anniversary(birthday, mothers_birthday) values(?,?)";
Phone phone = contact.getPhone();
Email email = contact.getEmail();
Anniversary anniversary = contact.getAnniversary();
ps = conn.prepareStatement(contactInsert);
ps.setString(1, contact.getFirstName());
ps.setString(2, contact.getLastName());
ps.executeUpdate();
// if (true) throw new Exception();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()FROM CONTACT");
if (rs.next()) {
int i = rs.getInt(1);
ps = conn.prepareStatement(phoneInsert);
ps.setString(1, phone.getWorkPhone());
ps.setString(2, phone.getHomePhone());
ps.setInt(3, i);
ps.executeUpdate();
}
else{
throw new IllegalStateException("Unable to retrieve last insert id");
}
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT LAST_INSERT_ID() from CONTACT");
if (rs.next()) {
int i = rs.getInt(1);
ps = conn.prepareStatement(emailInsert);
ps.setString(1, email.getWorkEmail());
ps.setString(2, email.getHomeEmail());
ps.setInt(3, i);
ps.executeUpdate();
}
else
{
throw new IllegalStateException("Unable to retrieve last insert id");
}
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT LAST_INSERT_ID() from CONTACT");
if (rs.next()) {
int i = rs.getInt(1);
ps = conn.prepareStatement(anniversaryInsert);
ps.setString(1, anniversary.getBirthday());
ps.setString(2, anniversary.getMbirthday());
ps.setInt(3, i);
ps.executeUpdate();
}
else
{
throw new IllegalStateException("Unable to retrieve last insert id");
}
conn.commit();
} catch (Exception e) {
if (null != conn) {
try {
conn.rollback();
} catch (SQLException se) {
se.printStackTrace();
}
}
e.printStackTrace();
} finally {
try {
if (null != ps) ps.close();
if (null != conn) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static List<Contact> getAllContacts(boolean useDataSource) {
Connection conn = null, connDS = null;
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<Contact> contList = new ArrayList<Contact>();
try {
if (useDataSource) {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/ics425");
conn = ds.getConnection();
} else {
String dbURL = "jdbc:mysql://127.0.0.1:3306/assign3";
String userName = "assign3";
String password = "";
conn = DriverManager.getConnection(dbURL, userName, password);
}
String selectAll = "select F_NAME, L_NAME, WORK_PHONE, HOME_PHONE, WORK_EMAIL, HOME_EMAIL, BIRTHDAY, MOTHERS_BIRTHDAY " +
" from contact c inner join phone p on p.contact_ID = C.ID INNER JOIN email e on e.contact_ID = c.ID " +
" inner join anniversary a on a.contact_ID = c.ID";
stmt = conn.createStatement();
rs = stmt.executeQuery(selectAll);
while (rs.next()) {
Contact cont = new Contact();
Email em =new Email();
Phone ph =new Phone();
Anniversary ann =new Anniversary();
cont.setFirstName(rs.getString("F_NAME"));
cont.setLastName(rs.getString("L_NAME"));
ph.setWorkPhone(rs.getString("WORK_PHONE"));
ph.setHomePhone(rs.getString("HOME_PHONE"));
em.setWorkEmail(rs.getString("WORK_EMAIL"));
em.setHomeEmail(rs.getString("HOME_EMAIL"));
ann.setBirthday(rs.getString("BIRTHDAY"));
ann.setMbirthday(rs.getString("MOTHERS_BIRTHDAY"));
cont.setPhone(ph);
cont.setEmail(em);
cont.setAnniversary(ann);
contList.add(cont);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != ps) ps.close();
if (null != conn) conn.close();
if (null != connDS) connDS.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return contList;
}
}
HERE IS THE mySQL ddl
create table USER (
ID int not null auto_increment,
FIRST_NAME varchar(50),
LAST_NAME varchar(50),
USERNAME varchar(30),
PASSWORD varchar(30),
primary key (ID)
);
create table ADDRESS (
ID int not null auto_increment,
STREET varchar (50),
CITY varchar (20),
STATE varchar (20),
ZIP varchar(8),
USER_ID int,
constraint primary key (ID),
constraint foreign key (USER_ID) references User (ID)
);
create table CONTACT (
ID int not null auto_increment,
F_NAME varchar(50),
L_NAME varchar(50),
constraint primary key (ID)
);
create table PHONE (
ID int not null auto_increment,
WORK_PHONE varchar(50),
HOME_PHONE varchar(50),
CONTACT_ID int,
constraint primary key (ID),
constraint foreign key (CONTACT_ID) references CONTACT (ID)
);
create table EMAIL (
ID int not null auto_increment,
WORK_EMAIL varchar(50),
HOME_EMAIL varchar(50),
CONTACT_ID int,
constraint primary key (ID),
constraint foreign key (CONTACT_ID) references CONTACT (ID)
);
create table ANNIVERSARY (
ID int not null auto_increment,
BIRTHDAY varchar(50),
MOTHERS_BIRTHDAY varchar(50),
CONTACT_ID int,
constraint primary key (ID),
constraint foreign key (CONTACT_ID) references CONTACT (ID)
);

11 years ago

JDBC and Relational Databases