JDBC PreparedStatement – Update a row
A JDBC PreparedStatement
example to update a row.
RowUpdate.java
package com.mkyong.jdbc.preparestatement.row;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class RowUpdate {
private static final String SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
PreparedStatement preparedStatement = conn.prepareStatement(SQL_UPDATE)) {
preparedStatement.setBigDecimal(1, new BigDecimal(999.99));
preparedStatement.setString(2, "mkyong");
int row = preparedStatement.executeUpdate();
// rows affected
System.out.println(row);
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
P.S Tested with PostgreSQL 11 and Java 8
pom.xml
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
</dependency>
Download Source Code
$ git clone https://github.com/mkyong/java-jdbc.git
Your code will always show: Record is updated to DBUSER table even though it doesn’t update. you have to get the int value that is returned from executeUpdate() and throws exception if the return value is 0. Your try catch only check SQL syntax.
You should close the preparedStatement and the conn objects in a finally block for completeness. Otherwise, you leak resources everytime this is called.
Thank you. Was helpful to do my project
What does this line do: Class.forName(DB_DRIVER)?
Why does it neither return anything nor create a new instance of a driver like: Class.forName(DB_DRIVER).newIntsance()?
What driver the DriverManager will use when calling getConnection()?
Thank you
Boy, you do a great job! Thanks
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package javaapplication1;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
import java.util.Scanner;
/**
*
* @author 3ce75
*/
public class Main {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
Class.forName(“org.apache.derby.jdbc.ClientDriver”);
} catch (ClassNotFoundException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
String url=”jdbc:derby://localhost:1527/db”;
String un=”d”;
String pwd=”b”;
try {
Connection cn = DriverManager.getConnection(url, un, pwd);
int choice;
System.out.println(“enter your choice”);
Scanner sn=new Scanner(System.in);
choice=sn.nextInt();
String query;
switch(choice)
{
case 1:
query=”insert into db (id,name,mobile) values(?,?,?)”;
PreparedStatement ps =cn.prepareStatement(query);
ps.setInt(1,1);
ps.setString(2,”kush”);
ps.setInt(3,12);
ps.executeUpdate();
ps.close();
break;
case 2:
query=”update db set id=? where id=1″;
PreparedStatement ps1 =cn.prepareStatement(query);
ps1.setInt(1,4);
ps1.executeUpdate();
ps1.close();
break;
case 3:
query=”delete from db where mobile=12″;
PreparedStatement ps2 =cn.prepareStatement(query);
ps2.executeUpdate();
ps2.close();
break;
case 4:
query=”select * from db”;
PreparedStatement ps3 =cn.prepareStatement(query);
ResultSet rs=ps3.executeQuery();
while(rs.next())
{
System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getInt(3));
}
ps3.close();
break;
}
cn.close();
} catch (SQLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
this website is my favorite website i really say thanks to this web site developers .
“update signupform ”
+ “set ? = ? ”
+ “where ”
+ “contact = ? “;
Will it work??