Preparedstatement la gi
Bài viết được sự cho phép của tác giả Giang Phan Show Trong bài này tôi sẽ hướng dẫn các bạn sử dụng các API của JDBC để thực thi các câu lệnh truy vấn dữ liệu. 1. Chuẩn bịTrước khi đi vào chi tiết từng JDBC API, chúng ta cần chuẩn bị cơ sở dữ liệu để sử dụng làm demo. Để đơn giản, tôi sẽ sử dụng cơ sở dữ liệu MySQL, các bạn có thể sử dụng bất kỳ cơ sở dữ liệu nào khác nếu thích. Xem thêm việc làm Java hấp dẫn trên TopDev Sau khi cài đặt cơ sở dữ liệu, chúng ta tạo database và 1 bảng user với cấu trúc sau: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `createdDate` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Tạo một lớp tiện ích hỗ trợ mở Connection đến Database (DB). ConnectionUtils.java package com.gpcoder; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectionUtils { private static final String hostName = "localhost"; private static final String dbName = "jdbcdemo"; private static final String userName = "root"; private static final String password = ""; // jdbc:mysql://hostname:port/dbname private static final String connectionURL = "jdbc:mysql://" + hostName + ":3306/" + dbName; public static Connection openConnection() throws SQLException { // 1. Load Driver // Class.forName("com.mysql.jdbc.Driver"); DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver()); // 2. Open connection return DriverManager.getConnection(connectionURL, userName, password); } }2. StatementStatement interface trong java cung cấp các phương thức để thực thi các câu lệnh truy vấn với cơ sở dữ liệu SQL. Statement cung cấp phương thức để tạo ra đối tượng ResultSet. Theo mặc định, tại cùng một thời điểm chỉ có một đối tượng ResultSet có thể được mở cho mỗi đối tượng Statement. Vì thế, nếu hoạt động đọc một đối tượng ResultSet bị chen ngang bởi hoạt động đọc đối tượng khác, thì đối tượng khác này phải được tạo bởi đối tượng Statement khác. Statement cung cấp một số phương thức để thực thi truy vấn SQL:
Ví dụ: Chạy chương trình, ta có kết quả sau: Affected rows after inserted: 1 Affected rows after updated: 1 Affected rows after deleted: 13. ResultSetResultSet là một đối tượng Java, nó được trả về khi truy vấn (query) dữ liệu. Sử dụng resultSet.next() để di chuyển con trỏ tới các bản ghi (record/ row) tiếp theo. Tại một record nào đó, sử dụng các method resultSet.getXxx() để lấy ra các giá trị tại các cột. Các cột được đánh với thứ tự với bắt đầu là 1. Mặc định các ResultSet khi duyệt dữ liệu chỉ có thể chạy từ trên xuống dưới, từ trái sang phải. chúng ta có thể làm cho đối tượng này di chuyển hướng chuyển tiếp và ngược lại bằng cách chỉ định loại ResultSet sử dụng. Statement.createStatement(resultSetType, resultSetConcurrency)Trong đó, ResultSetType là:
ResultSetConcurrency là:
Một số phương thức được hỗ trợ bởi ResultSet :
Ví dụ: package com.gpcoder; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class StatementExecuteQueryExample { public static void main(String[] args) throws SQLException { String sqlSelect = "SELECT * FROM user"; try ( Connection con = ConnectionUtils.openConnection(); Statement st = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery(sqlSelect); ) { while (rs.next()) { showUserInfo(rs); } System.out.println("\n=== Move to previous row ==="); while (rs.previous()) { showUserInfo(rs); } System.out.println("\n=== Move to last row ==="); rs.last(); showUserInfo(rs); System.out.println("\n=== Move to first row ==="); rs.first(); showUserInfo(rs); } } private static void showUserInfo(ResultSet rs) throws SQLException { System.out.println("Id: " + rs.getInt(1)); System.out.println("UserName: " + rs.getString(2)); System.out.println("Password: " + rs.getString("password")); System.out.println("CreatedDate: " + rs.getDate("createdDate")); System.out.println("---"); } }Chạy chương trình trên, ta có kết quả sau: Id: 1 UserName: gpcoder1 Password: 123 CreatedDate: 2019-09-15 --- Id: 2 UserName: gpcoder2 Password: 123 CreatedDate: 2019-09-15 --- Id: 3 UserName: gpcoder3 Password: 123 CreatedDate: 2019-09-15 --- === Move to previous row === Id: 3 UserName: gpcoder3 Password: 123 CreatedDate: 2019-09-15 --- Id: 2 UserName: gpcoder2 Password: 123 CreatedDate: 2019-09-15 --- Id: 1 UserName: gpcoder1 Password: 123 CreatedDate: 2019-09-15 --- === Move to last row === Id: 3 UserName: gpcoder3 Password: 123 CreatedDate: 2019-09-15 --- === Move to first row === Id: 1 UserName: gpcoder1 Password: 123 CreatedDate: 2019-09-15 ---4. PreparedStatementPreparedStatement là một sub-interface của Statement. PreparedStatement sử dụng để chuẩn bị trước các câu lệnh SQL, và tái sử dụng nhiều lần, giúp cho chương trình thực hiện nhanh hơn. Ví dụ: String sql = "INSERT INTO user VALUES(?, ?, ?)";Như bạn thấy, chúng ta truyền tham số (?) cho các giá trị. Giá trị của nó sẽ được cài đặt bằng cách gọi các phương thức setter của PreparedStatement. Ví dụ: package com.gpcoder; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class PreparedStatementExample { public static void main(String[] args) throws SQLException { String sqlInsert = "INSERT INTO user(username, password, createdDAte) " + " VALUE(?, ?, ?);"; try ( Connection con = ConnectionUtils.openConnection(); PreparedStatement pstm = con.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); ) { // Set parameter values pstm.setString(1, "gpcoder-test-1"); pstm.setString(2, "pwd123"); pstm.setDate(3, new java.sql.Date(System.currentTimeMillis())); // Executes the SQL statement pstm.execute(); // Get generated key try (ResultSet rs = pstm.getGeneratedKeys();) { int idValue = 0; if (rs.next()) { idValue = rs.getInt(1); } System.out.println("Auto-generated id: " + idValue); } } } }Output chương trình: Auto-generated id: 4Tùy thuộc vào giá trị tự tăng của table user có thể có kết quả là một id khác. 5. CallableStatementCallableStatement được xây dựng để gọi một thủ tục (procedure) hoặc hàm (function) của SQL. Ví dụ: Đầu tiên chúng ta cần một tạo hàm hoặc một thủ tục trong DB. Chẳng hạn, chúng ta có một procedure tìm kiếm user theo tên. Kết quả trả về của procedure này là danh sách user. CREATE PROCEDURE find_user_by_name(name Varchar(50)) BEGIN SELECT * FROM user WHERE username LIKE CONCAT('%', name, '%'); ENDChương trình java: package com.gpcoder; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; public class CallableStatementExample { public static void main(String[] args) throws SQLException { String sql = "{call find_user_by_name(?)}"; try ( Connection con = ConnectionUtils.openConnection(); CallableStatement cstm = con.prepareCall(sql); ) { // Set parameter values cstm.setString(1, "gpcoder1"); // Executes the Procedure statement try (ResultSet rs = cstm.executeQuery();) { while (rs.next()) { showUserInfo(rs); } } } } private static void showUserInfo(ResultSet rs) throws SQLException { System.out.println("Id: " + rs.getInt(1)); System.out.println("UserName: " + rs.getString(2)); System.out.println("Password: " + rs.getString("password")); System.out.println("CreatedDate: " + rs.getDate("createdDate")); System.out.println("---"); } }Output chương trình: Id: 1 UserName: gpcoder1 Password: 123 CreatedDate: 2019-09-15 ---6. Transaction ManagementGiao dịch (Transaction) là một khái niệm quan trọng trong SQL. Transaction đảm bảo tính toàn vẹn dữ liệu. Các câu lệnh trong Transaction được gọi là thành công nếu cả câu lệnh thành công. Ngược lại chỉ cần 1 trong các câu lệnh lỗi thì coi như giao dịch không thành công, phải rollback lại trạng thái ban đầu. Các thuộc tính ACID miêu tả rõ ràng nhất về Transaction. Trong đó:
Ví dụ: Người A chuyển một khoản tiền 1000$ vào tài khoản người B như vậy trong Database diễn ra 2 quá trình:
Nếu không có transaction thì một trong 2 quá trình lỗi gây tổn thất cho phía ngân hàng hoặc cho người dùng. Connection Interface cung cấp các phương thức sau để quản lý transaction:
Ví dụ bên dưới thực hiện insert 2 user vào database. User 1 insert thành công, User không insert được do bị lỗi SQL. Nếu không có transaction thì User 1 được lưu vào database. Chúng ta không mong muốn điều này. Hãy xem cách sử dụng Transaction: package com.gpcoder; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class TransactionExample { public static void main(String[] args) throws SQLException { try (Connection con = ConnectionUtils.openConnection(); Statement st = con.createStatement(); ) { con.setAutoCommit(false); // 1. Disable individualtransaction try { // This user will be inserted st.executeUpdate("INSERT INTO user(username, password, createdDAte) " + "VALUE('user-1', '123', now());"); System.out.println("Inserted user-1 successfully"); // This is an error sql. Cannot insert user st.executeUpdate("INSERT INTO user2(username, password, createdDAte) " + "VALUE('user-2', '123', now());"); System.out.println("Inserted user-2 successfully"); con.commit(); // 2. commit data to database if all command are success } catch (Exception e) { e.printStackTrace(); con.rollback(); // 2. roll-back data if one of command are failed System.out.println("Rollback data"); } } } }Chạy chương trình trên, chúng ta sẽ thấy log như sau: Inserted user-1 successfully java.sql.SQLSyntaxErrorException: Table 'jdbcdemo.user2' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.gpcoder.TransactionExample.main(TransactionExample.java:21) Rollback dataKiểm tra dữ liệu trong bảng user, bạn sẽ thấy user 1 không được lưu vào. 7. Batch ProcessingBatch Processing là nhóm các lệnh có liên quan vào trong một batch và thực thi chúng. Trong đó, các thao tác lệnh của chương trình được thực thi liên tiếp nhau mà không cần sự can thiệp của người dùng. Việc ứng dụng Batch Processing trong cơ sở dữ liệu là rất tiện lợi. Khi bạn gửi một số lệnh SQL cùng một lúc, bạn đã giảm được chi phí thời gian giao tiếp và vì thế nâng cao được hiệu suất. Một số phương thức hỗ trợ Batch Processing:
Ví dụ: Một trong những trường hợp thường sử dụng tính năng này là import dữ liệu. Giả sử chúng ta có một file csv chứa danh sách user, sau khi đọc file chúng ta sẽ insert danh sách này vào database. Chúng ta có thể call executeUpdate() cho từng user. Tuy nhiên, nó sẽ gặp vấn đề về performance nếu danh sách user của chúng ta rất lớn. Một trong những cách rất hay và thường được sử dụng là Batch Processing. package com.gpcoder; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import lombok.AllArgsConstructor; import lombok.Data; @Data @AllArgsConstructor class User { String username; String password; java.sql.Date createdDate; } public class BatchProcessingExample { public static void main(String[] args) throws SQLException { String sqlInsert = "INSERT INTO user(username, password, createdDAte) " + " VALUE(?, ?, ?);"; try ( Connection con = ConnectionUtils.openConnection(); PreparedStatement pstm = con.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); ) { try { con.setAutoCommit(false); List users = readUsersFromCsvFile(); for (User user : users) { pstm.setString(1, user.getUsername()); pstm.setString(2, user.getPassword()); pstm.setDate(3, user.getCreatedDate()); pstm.addBatch(); // Add user to batch } // Executes the SQL statement int[] counts = pstm.executeBatch(); System.out.println("Affected row [0] = " + counts[0]); System.out.println("Affected row [1] = " + counts[1]); con.commit(); } catch (Exception e) { e.printStackTrace(); con.rollback(); } } } private static List readUsersFromCsvFile () { List users = new ArrayList<>(); for (int i=1; i<=2; i++) { users.add(new User("user-" + 1, "pwd123", new java.sql.Date(System.currentTimeMillis()))); } return users; } }Thực thi chương trình trên, chúng ta có kết quả sau: Affected row [0] = 1 Affected row [1] = 1Kiểm tra trong bảng user, chúng ta thấy 2 user đã được lưu. 8. SQLExceptionTrong JDBC, lớp java.sql.SQLException cung cấp rất nhiều phương thức để xử lý các ngoại lệ xảy ra cho cả Driver và Database. Đây là một trong các lớp cơ bản của JDBC, và chịu trách nhiệm về xử lý các ngoại lệ. Ví dụ: package com.gpcoder; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class SQLExceptionExample { private static final String hostName = "localhost"; private static final String dbName = "jdbcdemo"; private static final String userName = "root"; private static final String password = ""; private static final String connectionURL = "jdbc:mysql://" + hostName + ":3306/" + dbName; public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(connectionURL, userName, password); Statement st = con.createStatement(); String sqlInsert = "INSERT INTO user(username, password, createdDate) " + " VALUE('gpcoder', '123', now());"; int numberRowsAffected = st.executeUpdate(sqlInsert); if (numberRowsAffected == 0) { System.out.println("insertion failed"); } else { System.out.println("inserted successfully : " + numberRowsAffected); } } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } }Thử đổi password không đúng, bạn sẽ nhận được log lỗi sau: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:827) at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:447) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at com.gpcoder.SQLExceptionExample.main(SQLExceptionExample.java:20)Nếu sai câu lệnh SQL bạn sẽ nhận được log lỗi tương tự sau: java.sql.SQLSyntaxErrorException: Table 'jdbcdemo.user2' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.gpcoder.SQLExceptionExample.main(SQLExceptionExample.java:26)Trường hợp Driver không tồn tại: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver2 at java.net.URLClassLoader.findClass(URLClassLoader.java:381) at java.lang.ClassLoader.loadClass(ClassLoader.java:424) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335) at java.lang.ClassLoader.loadClass(ClassLoader.java:357) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:264) at com.gpcoder.SQLExceptionExample.main(SQLExceptionExample.java:18)Bài viết đến đây là hết. Các bạn có thể tìm hiểu thêm về JDBC API ở các link tham khảo bên dưới. Tài liệu tham khảo:
Bài viết gốc được đăng tải tại gpcoder.com Có thể bạn quan tâm:
Xem thêm việc làm CNTT hấp dẫn trên TopDev |