Top community members
All Wiki Articles Create Wiki Article

Welcome to Dirask IT community! ❤ 💻
We are community of people that helps each other.

If you are beginner in IT field, you are more then welcome to ask questions, it will help you to learn faster. We are here to help you.

We are always beginner in something, we just need to remember it along the way.

there are no wrong questions - Ask Question

Java - how to make MySQL select query with JDBC?

0 contributions
16 points

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