Languages
[Edit]
EN

Java - how to make MySQL select query with JDBC?

16 points
Created by:
Kate_C
19790

In Java it is possible to make SQL SELECT query with JDBC in following way.

Note: read this article to know how to download and install JDBC driver proper way.

1. SELECT query with JDBC examples

1.1. Simple select example 

package com.dirask.examples;

import java.sql.Connection;
import java.sql.DriverManager;
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 = "SELECT * FROM `users`";
			
		try (
			// gets connection with database
			Connection connection = DriverManager.getConnection(DB_URL,
				DB_USER, DB_PASSWORD);
				
			// sends queries and receives results
			Statement statement = connection.createStatement();
		) {
			try (ResultSet result = statement.executeQuery(sql)) {
				System.out.print("[id]\t[name]\t[role]\n");
				
				while (result.next()) {
					int id = result.getInt("id");
					String name = result.getString("name");
					String role = result.getString("role");
					
					System.out.print(id + "\t" + name + "\t" + role + "\n");
				}
			}
		} catch (SQLException e) {
			// some logic depending on scenario
			// maybe LOGGER.log(e.getMessage()) and "result false"
			e.printStackTrace();
		}
	}
}

Result:

[id]	[name]	[role]
1	John	admin
2	Chris	moderator
3	Kate	user
4	Denis	moderator
5	Matt	moderator

1.2. SQL injection prevention example

package com.dirask.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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 = "SELECT * FROM `users` WHERE `role` = ?";
		
		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, "moderator");
			
			try (ResultSet result = statement.executeQuery()) {
				System.out.print("[id]\t[name]\t[role]\n");
				
				while (result.next()) {
					int id = result.getInt("id");
					String name = result.getString("name");
					String role = result.getString("role");
					
					System.out.print(id + "\t" + name + "\t" + role + "\n");
				}
			}
		} catch (SQLException e) {
			// some logic depending on scenario
			// maybe LOGGER.log(e.getMessage()) and "result false"
			e.printStackTrace();
		}
	}
}

Result:

[id]	[name]	[role]
2	Chris	moderator
4	Denis	moderator

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 load MySQL JDBC driver proper way?
  2. Java - how to close proper way resources / objects during MySQL queries with JDBC?
  3. Java - how to make MySQL insert query with JDBC?
  4. Java - how to make MySQL update query with JDBC?
  5. Java - how to make MySQL delete query with JDBC?
Native Advertising
50 000 ad impressions - 449$
🚀
Get your tech brand or product in front of software developers.
For more information contact us:
Red dot
Dirask - friendly IT community for everyone.

❤️💻 🙂

Join