The previous section describes how to get database connection through JDBC, but the Connection object can not directly execute the SQL statement, so it needs to introduce the Statement report object to operate the SQL. The Statement object is obtained by Connection's createStatement method, which mainly provides the following two methods:
ExcuteUpdate: Execute database management statements, mainly including table building, table structure modification, deletion, adding records, modifying records, deleting records and so on. Its return value is an integer, which stores the number of operation records of the current statement, such as how many records have been deleted, how many records have been updated, and so on.
executeQuery: Execute database query statements, dedicated to select commands. Its return value is ResultSet type, and the result set of the query can be obtained through the ResultSet object.
For the management type of SQL instructions, the complete operation process is divided into three steps:
1. Get the database connection: This step calls the getConnection method of the DriverManager class to get the connection object.
2. Create the execution report of the connection: This step calls the createStatement method of the Connection object to get the execution report.
3. The command report executes the SQL statement: This step calls the executeUpdate method of the report object to execute the SQL statement.
Connecting the above three steps in series, we can get the following database operation template:

		// Get the database connection first, then create the execution report of the connection
		try (Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
				Statement stmt = conn.createStatement()) {
				String sql = "Here is to be executed. SQL Sentence";
				stmt.executeUpdate(sql); // Execution Processing Statement
//			createTable(stmt); //Create tables
//			Insert Record (stmt); //Insert Record
//			updateRecord(stmt); // Update Record
		} catch (SQLException e) {
			e.printStackTrace();
		}

Next, let's look at some specific examples of SQL execution. First, we create a new table named teacher. Then we write the following table building code:

	// Create tables
	private static void createTable(Statement stmt) throws SQLException {
		String sql = "create table teacher (" // Table building statement
				+ "	gonghao INT NOT NULL,"
				+ "	name VARCHAR(32) NOT NULL,"
				+ "	birthday DATE NULL,"
				+ "	sex INT NOT NULL,"
				+ "	course VARCHAR(32) NOT NULL,"
				+ "	PRIMARY KEY (gonghao))"
				+ "comment = 'Teacher Information Table';";
		int count = stmt.executeUpdate(sql); // Execution Processing Statement
		System.out.println("The return result of the table-building statement is"+count);
	}

The createTable method was called inside the previous try code, and the following output log was observed after running the test program.

The return result of the table-building statement is 0.

Since the build statement itself does not affect any records, the executeUpdate method returns 0 when the table is built. Then open the MySQL workbench and you can see the teacher table on the left side of the workbench as shown in the figure.

After the table is built and several records are added to it, the following insert code is written:

	// insert record
	private static void insertRecord(Statement stmt) throws SQLException {
		List<String> sqlList = Arrays.asList( // Insert a record for each of the following statements
				"insert into teacher (gonghao, name, birthday, sex, course) VALUES ('1', 'Mr. Zhang', '1983-03-03', 1, 'Chinese')",
				"insert into teacher (gonghao, name, birthday, sex, course) VALUES ('2', 'Miss Li', '1984-04-04', 0, 'Mathematics')",
				"insert into teacher (gonghao, name, birthday, sex, course) VALUES ('3', 'Mr. Wang', '1985-05-05', 1, 'English?')",
				"insert into teacher (gonghao, name, birthday, sex, course) VALUES ('4', 'Miss Zhao', '1986-06-06', 0, 'Physics')",
				"insert into teacher (gonghao, name, birthday, sex, course) VALUES ('5', 'Miss Liu', '1987-07-07', 1, 'Chemistry')");
		for (String sql : sqlList) {
			int count = stmt.executeUpdate(sql); // Execution Processing Statement
			System.out.println("The result of adding a record statement is"+count);
		}
	}

The insertRecord method is also called in the try code, and the following log text is observed by running the test program.

The return result of adding a record statement is 1
The return result of adding a record statement is 1
The return result of adding a record statement is 1
The return result of adding a record statement is 1
The return result of adding a record statement is 1

Because each insert statement inserts a record, executeUpdate returns a number of 1 when inserting a table. When you go back to the workbench and query all the records of the teacher table, you can see five records as shown in the figure.

Then prepare to modify the record field and let all female teachers teach English. The method code of the update statement is as follows:

	// Update records
	private static void updateRecord(Statement stmt) throws SQLException {
		String sql = "update teacher set course='English?' where sex='1'"; // Record Update Statement
		int count = stmt.executeUpdate(sql); // Execute processing statements. Returns the number of records updated
		System.out.println("The return result of the update record statement is"+count);
	}

The updateRecord method is called in try code, and the following log information is observed after running the test program.

The return result of the update record statement is 3

As can be seen from the log, this update statement updates three database records, so the executeUpdate method returns a value of 3 at this time. Returning to MySQL workbench and re-querying the teacher table, the result of the record is shown in the figure. Sure enough, all the three female teachers'teaching courses have turned into English.

See more Java technology articles< Java Development Notes (Preface) Chapter Program Directory>