EN
Java - how to make MySQL update query with JDBC?
1
points
In Java it is possible to make SQL UPDATE query with JDBC in following way.
Note: read this article to know how to download and install JDBC driver proper way.
1. UPDATE query with JDBC example
Note: this approach prevents of SQL Injection attack.
package com.dirask.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Program {
private static final String DB_NAME = "test";
private static final String DB_HOST = "127.0.0.1"; // 'localhost'
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "root";
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/"
+ DB_NAME + "?serverTimezone=UTC";
public static void main(String[] args) throws ClassNotFoundException {
String sql = "UPDATE `users` SET `name` = ?, `role` = ? WHERE `id` = ?";
try (
// gets connection with database
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// sends queries and receives results
PreparedStatement statement = connection.prepareStatement(sql);
) {
// this way to prevent sql injection
statement.setString(1, "Matt");
statement.setString(2, "admin");
statement.setInt(3, 3);
int count= statement.executeUpdate();
System.out.print("Number of updated rows is " + count + ".");
} catch (SQLException e) {
// some logic depending on scenario
// maybe LOGGER.log(e.getMessage()) and "result false"
e.printStackTrace();
}
}
}
Output:
Number of updated rows is 1.
Database:
2. Data base preparation
create_tables.sql file:
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`role` VARCHAR(15) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
insert_data.sql file:
INSERT INTO `users`
(`name`, `role`)
VALUES
('John', 'admin'),
('Chris', 'moderator'),
('Kate', 'user'),
('Denis', 'moderator');