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.
xxxxxxxxxx
1
package com.dirask.examples;
2
3
import java.sql.Connection;
4
import java.sql.DriverManager;
5
import java.sql.ResultSet;
6
import java.sql.SQLException;
7
import java.sql.Statement;
8
9
public class Program {
10
11
private static final String DB_NAME = "test";
12
private static final String DB_HOST = "127.0.0.1"; // 'localhost'
13
private static final String DB_USER = "root";
14
private static final String DB_PASSWORD = "root";
15
16
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC";
17
18
public static void main(String[] args) throws ClassNotFoundException {
19
20
String sql = "SELECT * FROM `users`";
21
22
try (
23
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
24
Statement statement = connection.createStatement();
25
ResultSet result = statement.executeQuery(sql);
26
) {
27
System.out.print("[id]\t[name]\t[role]\n");
28
29
while (result.next()) {
30
int id = result.getInt("id");
31
String name = result.getString("name");
32
String role = result.getString("role");
33
34
System.out.print(id + "\t" + name + "\t" + role + "\n");
35
}
36
} catch (SQLException e) {
37
// Some logic depending on scenario here ...
38
// Maybe: LOGGER.log(e.getMessage()) and "return false"
39
e.printStackTrace();
40
}
41
}
42
}
Result:
xxxxxxxxxx
1
[id] [name] [role]
2
1 John admin
3
2 Chris moderator
4
3 Kate user
5
4 Denis moderator
6
5 Matt moderator
xxxxxxxxxx
1
package com.dirask.examples;
2
3
import java.sql.Connection;
4
import java.sql.DriverManager;
5
import java.sql.PreparedStatement;
6
import java.sql.ResultSet;
7
import java.sql.SQLException;
8
9
public class Program {
10
11
private static final String DB_NAME = "test";
12
private static final String DB_HOST = "127.0.0.1"; // 'localhost'
13
private static final String DB_USER = "root";
14
private static final String DB_PASSWORD = "root";
15
16
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC";
17
18
public static void main(String[] args) throws ClassNotFoundException {
19
20
String sql = "SELECT * FROM `users` WHERE `role` = ?";
21
22
try (
23
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
24
PreparedStatement statement = connection.prepareStatement(sql);
25
) {
26
// this way to prevent sql injection
27
statement.setString(1, "moderator");
28
29
try (ResultSet result = statement.executeQuery()) {
30
System.out.print("[id]\t[name]\t[role]\n");
31
32
while (result.next()) {
33
int id = result.getInt("id");
34
String name = result.getString("name");
35
String role = result.getString("role");
36
37
System.out.print(id + "\t" + name + "\t" + role + "\n");
38
}
39
}
40
} catch (SQLException e) {
41
// Some logic depending on scenario here ...
42
// Maybe: LOGGER.log(e.getMessage()) and "result false"
43
e.printStackTrace();
44
}
45
}
46
}
Result:
xxxxxxxxxx
1
[id] [name] [role]
2
2 Chris moderator
3
4 Denis moderator
create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE `users` (
2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3
`name` VARCHAR(100) NOT NULL,
4
`role` VARCHAR(15) NOT NULL,
5
PRIMARY KEY (`id`)
6
)
7
ENGINE=InnoDB;
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO `users`
2
(`name`, `role`)
3
VALUES
4
('John', 'admin'),
5
('Chris', 'moderator'),
6
('Kate', 'user'),
7
('Denis', 'moderator');