Update query for java JDBC +mysql database

Question

I'm trying to run update query on table doctors. The primary key of the table is defined as a composite primary key (deptid, docid). What I'm trying to do is to update field designation, qualification and time based on deptid and docid (by another query). I believe I'm doing something very silly but I'm not able to find it. Can someone help?

String did= request.getParameter("text1");
String dname = request.getParameter("text2");
String desig = request.getParameter("text3");
String qualification = request.getParameter("text4");
String time = request.getParameter("text5");

String className = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.10.13";
String user = "root";
String password = "";

PreparedStatement ps;
ResultSet rs;

try {
    Class.forName(className);                
    Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/webhospital","root","");
    //        PreparedStatement prepStmt = (PreparedStatement) conn.prepareStatement("Select * from tbl_userinfo");
    ps = (com.mysql.jdbc.PreparedStatement) con.prepareStatement("update doctors set Designation=?,Qualification=?,Time= ? where deptid =? and docid IN(select docid from doctors where doctorname='dname';)");
    ps.setString(1, did);
    ps.setString(3,desig);
    ps.setString(4,qualification);
    ps.setString(5,time);
    ps.executeUpdate();
}  catch (ClassNotFoundException cx) {
    out.println(cx);
} catch (SQLException ex) {
    Logger.getLogger(MysqlInsertServlet.class.getName()).log(Level.SEVERE, null, ex);
}

erhun · Accepted Answer · 2015-03-28 09:53:21Z

3

 ps = (com.mysql.jdbc.PreparedStatement) con.prepareStatement("update doctors set Designation=?,Qualification=?,Time= ? where deptid =? and docid IN(select docid from doctors where doctorname='dname';)");
            ps.setString(1, did);
            ps.setString(3,desig);
            ps.setString(4,qualification);
            ps.setString(5,time);

You have 4 question mark but set in wrong order why you don't set like :

ps.setString(1, desig);
                ps.setString(2,qualification);
                ps.setString(3,time);
                ps.setString(4,deptId);

Supplying Values for PreparedStatement Parameters

You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named updateSales:

updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey());

The first argument for each of these setter methods specifies the question mark placeholder. In this example, setInt specifies the first placeholder and setString specifies the second placeholder.

answered Mar 28, 2015 at 9:53

erhun

3,6532 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user232803 Over a year ago

you have no idea how much you helped by pointing out that order! thanks a lot. Its working now

AJ. · Accepted Answer · 2015-03-28 08:32:30Z

0

One change required in your query is

"where doctorname='dname';)" ==>> "where doctorname='"+dname+"';)"

answered Mar 28, 2015 at 8:32

AJ.

4,5355 gold badges32 silver badges43 bronze badges

2 Comments

user232803 Over a year ago

did not work .. ps = (com.mysql.jdbc.PreparedStatement) con.prepareStatement("update doctors set Designation=?,Qualification=?,Time= ? where deptid =? and docid IN(select docid from doctors where doctorname='"+dname+"';)");

AJ. Over a year ago

If you are not getting any compiletime/runtime eror than it menas your query is responsible for the update. Check your query only.

Kavindu Gayan · Accepted Answer · 2016-04-06 06:53:04Z

I think without editing your code it is good to show you an simple example.

    PrintWriter out = response.getWriter();
    String Title = request.getParameter("Title");
    String Artist = request.getParameter("Artist");
    String Country = request.getParameter("Country");
    String price = request.getParameter("price");
    String Year = request.getParameter("Year");

    try {
        //loading driver 

        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

        //creating connection with the database 
        Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");

        PreparedStatement ps = con.prepareStatement("update COMPACT_DISK set TITLE=?,ARTIST=?,COUNTRY=?,PRICE=?,YEARS=? where TITLE=?");

        ps.setString(1, Title);
        ps.setString(2, Artist);
        ps.setString(3, Country);
        ps.setString(4, price);
        ps.setString(5, Year);
        ps.setString(6, Title);
        int i = ps.executeUpdate();
        if (i > 0) {
            out.println("Compact disk successfully inserted");
        }
    } catch (Exception se) {
        out.println("Error Occured : \n" + se.getLocalizedMessage());
        se.printStackTrace();
    }

Collectives™ on Stack Overflow

Update query for java JDBC +mysql database

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related