CODEWITHSUNDEEP
javamysqlprepared-statement
Fahim Parkar
Fahim Parkar

Reputation: 31647

Don't Update If There Is Data Earlier

I have form as below.

++++++++++++++++++++++++++++++++++++++++++++++++++
+   Id     :  TextBox                            +
+   Name   :  TextBox                            +
+   Mob    :  TextBox                            +
+   Photo  :  File Option                        +
+                                                +
+   Submit   Update                              +
++++++++++++++++++++++++++++++++++++++++++++++++++

What I want to do is update the data for the respective Id. For update I have query as below.

PreparedStatement pst = conn.prepareStatement("UPDATE myTable SET name=?, mob=?, photo=? WHERE id=?");
pst.setString(1, personName);
pst.setString(2, mobileNum);
pst.setBinaryStream(3, InputStreamData);
pst.setString(4, personId);
pst.executeUpdate();

I have problem. I will explain with scenario.

Suppose id is 1 and I already have data for personName & file. Now I enter mobile number only. . How could I avoid pst.setBinaryStream(4, InputStreamData); statement? I don't want to enter any data as data is already present. Is there any sql statement where I can insert data.

I can pre-populate only Name data BUT NOT file data.

Edit 1

Solution for this is, have many UPDATE statements. BUT the problem is I have many fields. I can't use so many UPDATE statements.

Edit 2

What I tried is read the photo data & set that data in setBinaryStream as below.

pst.setBinaryStream(4, rst.getBinaryStream(1));

But it give me ERROR as

java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setBinaryStream(ILjava/io/InputStream;)V

javax.faces.el.EvaluationException: java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setBinaryStream(ILjava/io/InputStream;)V
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542)

--

Caused by: java.lang.AbstractMethodError: com.mysql.jdbc.PreparedStatement.setBinaryStream(ILjava/io/InputStream;)V
at com.sac.databean.PersonalInformationDataBean.editPersonalInfo(PersonalInformationDataBean.java:1530)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at javax.el.BeanELResolver.invokeMethod(BeanELResolver.java:779)
at javax.el.BeanELResolver.invoke(BeanELResolver.java:528)
at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:257)

Upvotes: 3

Views: 236

Answers (4)

Fahim Parkar
Fahim Parkar

Reputation: 31647

pst.setBinaryStream(4, rst.getBinaryStream(1), (int) rst.getString(1).length()); did the trick.

Upvotes: 0

James
James

Reputation: 264

The error your getting as part of edit 2 is saying that method has not been implemented.

Try looking at MySQL-BLOB-setBinaryStream

Test 3 is passing in the length of the stream and this method seems to be implemented.

I am assuming your using the JDBC 3.0 which is used in those tests.

Upvotes: 0

Adam Dyga
Adam Dyga

Reputation: 8926

You can use two UPDATE statements. One for personal information, the other for file(s). The latter would be only executed when the user actually uploaded a new photo (it's easy to check when it's submitted via web form).

Alternatively you can have two UPDATE statements, one that only updates personal information, and another one that updates personal information AND file(s). As before, the latter would be executed when the user actually uploaded a new photo.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692231

When a user edits his personal information using the form, the form should be pre-popuated with his current name and phone number.

This way, if he changes only his phone number, he won't touch to the name input field containing his name, and the form will be submitted with his current name and his new phone number. And you can update all the fields in database.

It's a UI problem, not a database problem.

Upvotes: 2

Related Questions