EN
Java - how to make MySQL insert query with JDBC?
5
points
In Java it is possible to make SQL INSERT query with JDBC in following way.
Note: read this article to know how to download and install JDBC driver proper way.
1. INSERT 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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 = "INSERT INTO `users` (`name`, `role`) VALUES (?, ?)";
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,
Statement.RETURN_GENERATED_KEYS);
) {
// this way to prevent sql injection
statement.setString(1, "Matt");
statement.setString(2, "moderator");
int count= statement.executeUpdate();
if (count > 0) {
ResultSet result = statement.getGeneratedKeys();
if (result.next()) {
int id = result.getInt(1);
System.out.print("Last added user has id " + id + ".");
}
}
} catch (SQLException e) {
// some logic depending on scenario
// maybe LOGGER.log(e.getMessage()) and "result false"
e.printStackTrace();
}
}
}
Output:
Last added user has id 5.
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');