This website requires JavaScript.

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()即可
0条评论
avatar