If we want to update a record in a database from our Java application , the JDBC API provides facilities for the same.Here we are discussing about Updating database in Java .
Updating database in Java
We are using MySQL server community version as database .We can download it from here. For updating some record in database table , we should have a database and a table with some contents. So we need to create data base and database table. The following queries will do the same.You can run those queries either from the default command line client of MySQL client or from an object explorer like SQLyog. The community version of SQLyog is available for learning purpose .We can download it from here.
1)Create database
CREATE DATABASE mydb;
2)Create database table
USE mydb;
CREATE TABLE testtable ( number INT, DATA VARCHAR(100) );
Now let us see the Java example to update the existing record.For this code the MySQL connector jar should be there in class path.We can download the MySQL connector from here.
Our Java class DBUpdateSample.java is having two methods apart from the main method.The insert() method inserts a record to the table. The update() method updates the existing record. The database name and password mentioned in the connection url is the user name and password of our database.The insert() method is having the insert query and the update() method is having the update query.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUpdateSample {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public DBUpdateSample() {
}
public void insert() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb", "username", "password");
statement = connection.createStatement();
int result = statement
.executeUpdate("insert into testtable values('1','cat')");
if (result >= 0) {
String query = "select * from testtable";
resultset = statement.executeQuery(query);
System.out.println("Inserted record");
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (resultset != null)
resultset.close();
if (statement != null)
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void update() {
try {
statement = connection.createStatement();
int result = statement
.executeUpdate("update testtable SET data='animal' where number='1' ");
if (result >= 0) {
String query = "select * from testtable";
resultset = statement.executeQuery(query);
System.out.println("updated record");
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (resultset != null)
resultset.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
DBUpdateSample sample = new DBUpdateSample();
sample.insert();
sample.update();
}
}
Now let us see the output.
Output
Inserted record
Number = 1 ; Data = cat
updated record
Number = 1 ; Data = animal