Top community members
All Wiki Articles Create Wiki Article

In the past we had a dream,
to have place where we could share IT knowledge,
to ask questions without fear that someone will judge us.

Now we are a group of people who make this dream come true. ❀ đŸ’»

If you think sharing knowledge and helping other is valuable.

join our community - Click here

Java - how to make MySQL insert query with JDBC?

0 contributions
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:

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?
0 contributions

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey 👋
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more