Java jdbc tutorial sql insert select update and delete examples
/:sptth/www.theitroad.com
Here are some examples of how to perform common SQL operations (insert, select, update, and delete) using JDBC in Java:
- Insert a record into a table
// 1. Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver"); // 2. Create a connection to the database Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password"); // 3. Create a statement and execute the SQL insert statement Statement stmt = conn.createStatement(); String sql = "INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.0)"; int rows = stmt.executeUpdate(sql); System.out.println("Inserted " + rows + " row(s)"); // 4. Clean up resources stmt.close(); conn.close();
- Select records from a table
// 1. Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver"); // 2. Create a connection to the database Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password"); // 3. Create a statement and execute the SQL select statement Statement stmt = conn.createStatement(); String sql = "SELECT * FROM employees WHERE salary > 50000.0"; ResultSet rs = stmt.executeQuery(sql); // 4. Process the result set while (rs.next()) { String name = rs.getString("name"); double salary = rs.getDouble("salary"); System.out.println(name + " earns $" + salary); } // 5. Clean up resources rs.close(); stmt.close(); conn.close();
- Update a record in a table
// 1. Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver"); // 2. Create a connection to the database Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password"); // 3. Create a statement and execute the SQL update statement Statement stmt = conn.createStatement(); String sql = "UPDATE employees SET salary = 55000.0 WHERE name = 'John Doe'"; int rows = stmt.executeUpdate(sql); System.out.println("Updated " + rows + " row(s)"); // 4. Clean up resources stmt.close(); conn.close();
- Delete a record from a table
// 1. Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver"); // 2. Create a connection to the database Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password"); // 3. Create a statement and execute the SQL delete statement Statement stmt = conn.createStatement(); String sql = "DELETE FROM employees WHERE name = 'John Doe'"; int rows = stmt.executeUpdate(sql); System.out.println("Deleted " + rows + " row(s)"); // 4. Clean up resources stmt.close(); conn.close();
Note that you may need to modify the connection URL and login credentials to match your database configuration. Also, be aware that using string concatenation to build SQL statements can be vulnerable to SQL injection attacks, so it's generally recommended to use prepared statements instead.