Java JDBC CRUD with MySQL using Maven
Java JDBC CRUD với MySQL bằng Maven
Bảng student:
Cấu trúc source code:
File pom.xml quản lý các dependencies:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.fpt</groupId>
<artifactId>javadocfast</artifactId>
<version>1.0</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
</dependencies>
</project>
Kết nối với MySQL ở file DBConnection.java
package com.fpt.common;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
private static final String URL = "jdbc:mysql://localhost:3306/javadocfast";
private static final String USER = "root";
private static final String PASSWORD = "";
public static Connection getConnect() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
}
}
public static void main(String[] args) {
Connection connection = DBConnection.getConnect();
try {
if(connection != null && !connection.isClosed()) {
System.out.println("Yes");
} else {
System.out.println("No");
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
Model theo bảng student trong database Student.java
package com.fpt.model;
import java.util.Date;
public class Student {
private String id;
private String fullName;
private Date birthday;
public Student() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getFullName() {
return fullName;
}
public void setFullName(String fullName) {
this.fullName = fullName;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
File StudentService.java xử lý CRUD
package com.fpt.service;
import com.fpt.common.DBConnection;
import com.fpt.model.Student;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class StudentService {
public List<Student> getAll() {
try {
List<Student> students = new ArrayList<Student>();
String sql = "select * from student";
PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Student student = new Student();
student.setId(resultSet.getString("id"));
student.setFullName(resultSet.getString("fullname"));
student.setBirthday(resultSet.getDate("birthday"));
students.add(student);
}
return students;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
}
}
public Student getById(String id) {
try {
Student student = new Student();
String sql = "select * from student where id = ?";
PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
preparedStatement.setString(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
student.setId(resultSet.getString("id"));
student.setFullName(resultSet.getString("fullname"));
student.setBirthday(resultSet.getDate("birthday"));
}
return student;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
}
}
public boolean insert(Student student) {
try {
String sql = "insert into student(id, fullname, birthday) values(?, ?, ?)";
PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
preparedStatement.setString(1, student.getId());
preparedStatement.setString(2, student.getFullName());
preparedStatement.setDate(3, new Date(student.getBirthday().getTime()));
return preparedStatement.executeUpdate() > 0;
} catch (Exception e) {
System.out.println(e.getMessage());
return false;
}
}
public boolean update(Student student) {
try {
String sql = "update student set fullname = ?, birthday = ? where id = ?";
PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
preparedStatement.setString(1, student.getFullName());
preparedStatement.setDate(2, new Date(student.getBirthday().getTime()));
preparedStatement.setString(3, student.getId());
return preparedStatement.executeUpdate() > 0;
} catch (Exception e) {
System.out.println(e.getMessage());
return false;
}
}
public boolean delete(String id) {
try {
String sql = "delete from student where id = ?";
PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
preparedStatement.setString(1, id);
return preparedStatement.executeUpdate() > 0;
} catch (Exception e) {
System.out.println(e.getMessage());
return false;
}
}
}
File Main.java để test các chức năng:
Lấy tất cả dữ liệu từ bảng student:
package com.fpt.main;
import com.fpt.model.Student;
import com.fpt.service.StudentService;
import java.util.List;
public class Main {
private static StudentService studentService = new StudentService();
public static void main(String[] args) {
List<Student> students = studentService.getAll();
System.out.printf("\n%-5s %-20s %-20s", "ID", "Full Name", "Birthday");
for(Student student : students) {
System.out.printf("\n%-5s %-20s %-20s", student.getId(), student.getFullName(), student.getBirthday());
}
}
}
Kết quả:
package com.fpt.main;
import com.fpt.model.Student;
import com.fpt.service.StudentService;
public class Main {
private static StudentService studentService = new StudentService();
public static void main(String[] args) {
Student student = studentService.getById("s001");
System.out.printf("\n%-5s %-20s %-20s", "ID", "Full Name", "Birthday");
System.out.printf("\n%-5s %-20s %-20s", student.getId(), student.getFullName(), student.getBirthday());
}
}
Kết quả:
Thêm một sinh viên:
package com.fpt.main;
import com.fpt.model.Student;
import com.fpt.service.StudentService;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Main {
private static StudentService studentService = new StudentService();
public static void main(String[] args) throws ParseException {
Student student = new Student();
student.setId("s003");
student.setFullName("vo nhat vu");
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = dateFormat.parse("1996-01-01");
student.setBirthday(date);
studentService.insert(student);
}
}
Kết quả:
Update một sinh viên:
package com.fpt.main;
import com.fpt.model.Student;
import com.fpt.service.StudentService;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Main {
private static StudentService studentService = new StudentService();
public static void main(String[] args) throws ParseException {
Student student = new Student();
student.setId("s003");
student.setFullName("huynh ngoc hue");
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = dateFormat.parse("1996-12-21");
student.setBirthday(date);
studentService.update(student);
}
}
Kết quả:
package com.fpt.main;
import com.fpt.service.StudentService;
import java.text.ParseException;
public class Main {
private static StudentService studentService = new StudentService();
public static void main(String[] args) throws ParseException {
studentService.delete("s003");
}
}
Nhận xét
Đăng nhận xét