Dalam tutorial ini, kita akan mempelajari operasi basis data dasar (CRUD - Create, Retrieve, Update and Delete) menggunakan JDBC (Java Database Connectivity) API Show Operasi CRUD ini setara dengan pernyataan CREATE, SELECT, UPDATE dan DELETE dalam bahasa SQL. Meskipun sistem basis data targetnya adalah MySQL, teknik yang sama dapat diterapkan untuk sistem basis data lain juga karena sintaks kueri yang digunakan adalah SQL standar yang didukung oleh semua sistem basis data relasional. Video Tutorial ini dijelaskan dalam Video Youtube di bawah ini. Berlanggananlah ke saluran youtube saya untuk mempelajari lebih lanjut tentang boot Musim Semi di Java Guides - Saluran YouTube Mari kita mulai dengan membuat tabel pengguna dan kemudian kita akan melakukan operasi INSERT, UPDATE, SELECT, dan DELETE Buat Contoh TabelMari gunakan antarmuka Pernyataan untuk membuat tabel - Objek pernyataan yang digunakan untuk mengeksekusi pernyataan SQL statis dan mengembalikan hasil yang dihasilkannya.
create table users( id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20) ); Berikut adalah program Java JDBC lengkap untuk membuat tabel pengguna dalam database package com.javaguides.jdbc.statement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * Create Statement JDBC Example * @author Ramesh Fadatare * */ public class CreateStatementExample { private static final String createTableSQL = "create table users (\r\n" + " id int(3) primary key,\r\n" + " name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" + " password varchar(20)\r\n" + " );"; public static void main(String[] argv) throws SQLException { CreateStatementExample createTableExample = new CreateStatementExample(); createTableExample.createTable(); } public void createTable() throws SQLException { System.out.println(createTableSQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object Statement statement = connection.createStatement();) { // Step 3: Execute the query or update query statement.execute(createTableSQL); } catch (SQLException e) { // print SQL exception information printSQLException(e); } // Step 4: try-with-resource statement will auto close the connection. } public static void printSQLException(SQLException ex) { for (Throwable e: ex) { if (e instanceof SQLException) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } }_ Pelaksana Pernyataan Sisipkan - Sisipkan Contoh RekamanDalam contoh ini, kami menggunakan antarmuka PreparedStatement untuk menyisipkan catatan pengguna. Seperti yang kita ketahui, antarmuka PreparedStatement meningkatkan kinerja seperti pernyataan SQL yang dikompilasi sebelumnya dan disimpan dalam objek PreparedStatement. Objek ini kemudian dapat digunakan untuk mengeksekusi pernyataan ini beberapa kali secara efisien package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Insert PrepareStatement JDBC Example * * @author Ramesh Fadatare * */ public class InsertPStatementExample { private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);"; public static void main(String[] argv) throws SQLException { InsertPStatementExample createTableExample = new InsertPStatementExample(); createTableExample.insertRecord(); } public void insertRecord() throws SQLException { System.out.println(INSERT_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) { preparedStatement.setInt(1, 1); preparedStatement.setString(2, "Tony"); preparedStatement.setString(3, "[email protected]"); preparedStatement.setString(4, "US"); preparedStatement.setString(5, "secret"); System.out.println(preparedStatement); // Step 3: Execute the query or update query preparedStatement.executeUpdate(); } catch (SQLException e) { // print SQL exception information printSQLException(e); } // Step 4: try-with-resource statement will auto close the connection. } public static void printSQLException(SQLException ex) { for (Throwable e: ex) { if (e instanceof SQLException) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } } Menjalankan Pernyataan SELECT - Pilih Contoh CatatanSejauh ini kami telah membuat tabel pengguna dan memasukkan beberapa catatan ke dalamnya. Jadi sekarang kita akan menanyakan dan mengambil pengguna unik dari tabel database berdasarkan id package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * Select PreparedStatement JDBC Example * * @author Ramesh Fadatare * */ public class SelectPStatementExample { private static final String QUERY = "select id,name,email,country,password from users where id =?"; public static void main(String[] args) { // using try-with-resources to avoid closing resources (boiler plate code) // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) { preparedStatement.setInt(1, 1); System.out.println(preparedStatement); // Step 3: Execute the query or update query ResultSet rs = preparedStatement.executeQuery(); // Step 4: Process the ResultSet object. while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String email = rs.getString("email"); String country = rs.getString("country"); String password = rs.getString("password"); System.out.println(id + "," + name + "," + email + "," + country + "," + password); } } catch (SQLException e) { printSQLException(e); } // Step 4: try-with-resource statement will auto close the connection. } public static void printSQLException(SQLException ex) { for (Throwable e: ex) { if (e instanceof SQLException) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } } Keluaran
Menjalankan pernyataan UPDATE - Perbarui Contoh RekamanKita harus menggunakan antarmuka PreparedStatement untuk melakukan operasi SQL Update. Dalam contoh ini, kami memperbarui nama dengan id. package com.javaguides.jdbc.preparestatement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Update PreparedStatement JDBC Example * @author Ramesh Fadatare * */ public class UpdatePStatementExample { private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;"; public static void main(String[] argv) throws SQLException { UpdatePStatementExample updateStatementExample = new UpdatePStatementExample(); updateStatementExample.updateRecord(); } public void updateRecord() throws SQLException { System.out.println(UPDATE_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) { preparedStatement.setString(1, "Ram"); preparedStatement.setInt(2, 1); // Step 3: Execute the query or update query preparedStatement.executeUpdate(); } catch (SQLException e) { // print SQL exception information printSQLException(e); } // Step 4: try-with-resource statement will auto close the connection. } public static void printSQLException(SQLException ex) { for (Throwable e: ex) { if (e instanceof SQLException) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } }_ Baca lebih lanjut contoh update JDBC SQL di JDBC PreparedStatement - Update a Record Example Menjalankan Pernyataan DELETE - Hapus Contoh RekamanDi sini kami memiliki tabel pengguna di database dan kami akan menghapus catatan dengan mengikuti program JDBC package com.javaguides.jdbc.statement.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * Delete Statement JDBC Example * @author Ramesh Fadatare * */ public class DeleteStatementExample { private static final String DELETE_USERS_SQL = "delete from users where id = 3;"; public static void main(String[] argv) throws SQLException { DeleteStatementExample deleteStatementExample = new DeleteStatementExample(); deleteStatementExample.deleteRecord(); } public void deleteRecord() throws SQLException { System.out.println(DELETE_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object Statement statement = connection.createStatement();) { // Step 3: Execute the query or update query int result = statement.executeUpdate(DELETE_USERS_SQL); System.out.println("Number of records affected :: " + result); } catch (SQLException e) { // print SQL exception information printSQLException(e); } // Step 4: try-with-resource statement will auto close the connection. } public static void printSQLException(SQLException ex) { for (Throwable e: ex) { if (e instanceof SQLException) { e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) { System.out.println("Cause: " + t); t = t.getCause(); } } } } } Keluaran _Baca lebih lanjut contoh JDBC SQL Delete di Pernyataan JDBC - Contoh Hapus Data . Ini adalah kode sumber lengkap dan refactored dari tutorial ini. Anda bisa mendapatkan kode sumber tutorial ini dari repositori GitHub saya |