Languages
[Edit]
EN

Java - how to make MySQL insert query with JDBC?

5 points
Created by:
Keisha-Acosta
380

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:

Database state after SQL INSERT query with PDO class - HeidiSQL.
Database state after SQL INSERT query with JDBC class - HeidiSQL.

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');

See also

  1. Java - how to make MySQL select query with JDBC?
  2. Java - how to make MySQL update query with JDBC?
  3. Java - how to make MySQL delete query with JDBC?
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

Java - MySQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join