EN
Java - how to make MySQL select query with JDBC?
19
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 (
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Statement statement = connection.createStatement();
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 here ...
// Maybe: LOGGER.log(e.getMessage()) and "return 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 (
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
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 here ...
// 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');