java jdbc 更新操作,Java: 使用jdbc 连接MySQL数据库(二):插入、更新、删除操作以及预处理语句...

除了预处理语句外其他没什么好说明的,具体看代码

插入记录

import java.sql.*;

/**

*

* @author www.luv2code.com

*

*/

public class JdbcInsertDemo {

public static void main(String[] args) throws SQLException {

Connection myConn = null;

Statement myStmt = null;

ResultSet myRs = null;

String dbUrl = "jdbc:mysql://localhost:3306/demo";

String user = "student";

String pass = "student";

try {

// 1\. Get a connection to database

myConn = DriverManager.getConnection(dbUrl, user, pass);

// 2\. Create a statement

myStmt = myConn.createStatement();

// 3\. Insert a new employee

System.out.println("Inserting a new employee to database\n");

int rowsAffected = myStmt.executeUpdate(

"insert into employees " +

"(last_name, first_name, email, department, salary) " +

"values " +

"('Wright', 'Eric', 'eric.wright@foo.com', 'HR', 33000.00)");

// 4\. Verify this by getting a list of employees

myRs = myStmt.executeQuery("select * from employees order by last_name");

// 5\. Process the result set

while (myRs.next()) {

System.out.println(myRs.getString("last_name") + ", " + myRs.getString("first_name"));

}

}

catch (Exception exc) {

exc.printStackTrace();

}

finally {

if (myRs != null) {

myRs.close();

}

if (myStmt != null) {

myStmt.close();

}

if (myConn != null) {

myConn.close();

}

}

}

}

更新记录

import java.sql.*;

/**

*

* @author www.luv2code.com

*

*/

public class JdbcUpdateDemo {

public static void main(String[] args) throws SQLException {

Connection myConn = null;

Statement myStmt = null;

ResultSet myRs = null;

String dbUrl = "jdbc:mysql://localhost:3306/demo";

String user = "student";

String pass = "student";

try {

// Get a connection to database

myConn = DriverManager.getConnection(dbUrl, user, pass);

// Create a statement

myStmt = myConn.createStatement();

// Call helper method to display the employee's information

System.out.println("BEFORE THE UPDATE...");

displayEmployee(myConn, "John", "Doe");

// UPDATE the employee

System.out.println("\nEXECUTING THE UPDATE FOR: John Doe\n");

int rowsAffected = myStmt.executeUpdate(

"update employees " +

"set email='john.doe@luv2code.com' " +

"where last_name='Doe' and first_name='John'");

// Call helper method to display the employee's information

System.out.println("AFTER THE UPDATE...");

displayEmployee(myConn, "John", "Doe");

}

catch (Exception exc) {

exc.printStackTrace();

}

finally {

close(myConn, myStmt, myRs);

}

}

private static void displayEmployee(Connection myConn, String firstName, String lastName) throws SQLException {

PreparedStatement myStmt = null;

ResultSet myRs = null;

try {

// Prepare statement

myStmt = myConn

.prepareStatement("select last_name, first_name, email from employees where last_name=? and first_name=?");

myStmt.setString(1, lastName);

myStmt.setString(2, firstName);

// Execute SQL query

myRs = myStmt.executeQuery();

// Process result set

while (myRs.next()) {

String theLastName = myRs.getString("last_name");

String theFirstName = myRs.getString("first_name");

String email = myRs.getString("email");

System.out.printf("%s %s, %s\n", theFirstName, theLastName, email);

}

} catch (Exception exc) {

exc.printStackTrace();

} finally {

close(myStmt, myRs);

}

}

private static void close(Connection myConn, Statement myStmt,

ResultSet myRs) throws SQLException {

if (myRs != null) {

myRs.close();

}

if (myStmt != null) {

myStmt.close();

}

if (myConn != null) {

myConn.close();

}

}

private static void close(Statement myStmt, ResultSet myRs)

throws SQLException {

close(null, myStmt, myRs);

}

}

删除记录

import java.sql.*;

/**

*

* @author www.luv2code.com

*

*/

public class JdbcDeleteDemo {

public static void main(String[] args) throws SQLException {

Connection myConn = null;

Statement myStmt = null;

ResultSet myRs = null;

String dbUrl = "jdbc:mysql://localhost:3306/demo";

String user = "student";

String pass = "student";

try {

// Get a connection to database

myConn = DriverManager.getConnection(dbUrl, user, pass);

// Create a statement

myStmt = myConn.createStatement();

// Call helper method to display the employee's information

System.out.println("BEFORE THE DELETE...");

displayEmployee(myConn, "John", "Doe");

// DELETE the employee

System.out.println("\nDELETING THE EMPLOYEE: John Doe\n");

int rowsAffected = myStmt.executeUpdate(

"delete from employees " +

"where last_name='Doe' and first_name='John'");

// Call helper method to display the employee's information

System.out.println("AFTER THE DELETE...");

displayEmployee(myConn, "John", "Doe");

}

catch (Exception exc) {

exc.printStackTrace();

}

finally {

close(myConn, myStmt, myRs);

}

}

private static void displayEmployee(Connection myConn, String firstName, String lastName) throws SQLException {

PreparedStatement myStmt = null;

ResultSet myRs = null;

try {

// Prepare statement

myStmt = myConn

.prepareStatement("select last_name, first_name, email from employees where last_name=? and first_name=?");

myStmt.setString(1, lastName);

myStmt.setString(2, firstName);

// Execute SQL query

myRs = myStmt.executeQuery();

// Process result set

boolean found = false;

while (myRs.next()) {

String theLastName = myRs.getString("last_name");

String theFirstName = myRs.getString("first_name");

String email = myRs.getString("email");

System.out.printf("Found employee: %s %s, %s\n", theFirstName, theLastName, email);

found=true;

}

if (!found) {

System.out.println("Employee NOT FOUND: " + firstName + " " + lastName);

}

} catch (Exception exc) {

exc.printStackTrace();

} finally {

close(myStmt, myRs);

}

}

private static void close(Connection myConn, Statement myStmt,

ResultSet myRs) throws SQLException {

if (myRs != null) {

myRs.close();

}

if (myStmt != null) {

myStmt.close();

}

if (myConn != null) {

myConn.close();

}

}

private static void close(Statement myStmt, ResultSet myRs)

throws SQLException {

close(null, myStmt, myRs);

}

}

预处理语句(Prepared Statements)

预处理语句是一个预编译的SQL句子,有以下好处

设置SQL参数更方便

防止SQL注入

由于语句是预编译的关系,可能会增加某些程序的性能

import java.sql.*;

public class Driver {

public static void main(String[] args) throws SQLException {

Connection myConn = null;

PreparedStatement myStmt = null;

ResultSet myRs = null;

try {

// 1\. Get a connection to database

myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "student" , "student");

// 2\. Prepare statement

myStmt = myConn.prepareStatement("select * from employees where salary > ? and department=?");

// 3\. Set the parameters

myStmt.setDouble(1, 80000);

myStmt.setString(2, "Legal");

// 4\. Execute SQL query

myRs = myStmt.executeQuery();

// 5\. Display the result set

display(myRs);

//

// Reuse the prepared statement: salary > 25000, department = HR

//

System.out.println("\n\nReuse the prepared statement: salary > 25000, department = HR");

// 6\. Set the parameters

myStmt.setDouble(1, 25000);

myStmt.setString(2, "HR");

// 7\. Execute SQL query

myRs = myStmt.executeQuery();

// 8\. Display the result set

display(myRs);

}

catch (Exception exc) {

exc.printStackTrace();

}

finally {

if (myRs != null) {

myRs.close();

}

if (myStmt != null) {

myStmt.close();

}

if (myConn != null) {

myConn.close();

}

}

}

private static void display(ResultSet myRs) throws SQLException {

while (myRs.next()) {

String lastName = myRs.getString("last_name");

String firstName = myRs.getString("first_name");

double salary = myRs.getDouble("salary");

String department = myRs.getString("department");

System.out.printf("%s, %s, %.2f, %s\n", lastName, firstName, salary, department);

}

}

}

同样的如果是插入,删除,更新动作的话直接用executeUpdate()替换executeQuery()即可