@[TOC]

PreparedStatement interface

1.1 Inheritance structure and Functions:

PreparedStatement is a subinterface of the Statement interface that inherits all methods from the parent interface. It is a pre-compiled SQL statement

1.2 Execution principle of PreparedSatement

1) Because of the pre-compiled function, improve the efficiency of SQL execution. 2) Can effectively prevent SQL injection problems, higher security.

1.3 Connection Create a PreparedStatement object

PreparedStatement prepareStatement(String SQL) Specifies a pre-compiled SQL statement that uses placeholders. Create a statement object

1.4 Methods in PreparedStatement interface:

Int executeUpdate() performs DML operations, increments, deletions, and changes, and returns the number of rows affected. ResultSet executeQuery() performs DQL, query operations, and returns a ResultSet

1.5 Benefits of PreparedSatement

1. PrepareStatement () sends the SQL statement to the database for precompilation. Preparedstatements refer to the precompiled results. You can pass different parameters to the PreparedStatement object multiple times and execute it. Reduce SQL compilation times and improve efficiency. 2. Higher security, no HIDDEN danger of SQL injection. 3. Improve the readability of the program

1.6 Steps for using a PreparedStatement:

1) Write SQL statement, unknown content use? SELECT * FROM user WHERE name=? AND password=?” ; 2) Get the PreparedStatement object 3) set the actual parameters: setXxx(placeholder position, real value) 4) execute the parameterized SQL statement 5) close the resource

Void setDouble(int parameterIndex, double x) Sets the specified parameter to the given Java double value. Void setFloat(int parameterIndex, float X) sets the specified parameter to the given Java REAL value. Void setInt(int parameterIndex, int x) Sets the specified parameter to the specified Java int value. Void setLong(int parameterIndex, long x) sets the specified parameter to the given Java long value. Void setObject(int parameterIndex, Object x) Sets the value of the specified parameter using the specified Object. Void setString(int parameterIndex, String x) Sets the specified parameter to the given Java String value.

Buy a ticket to rewrite the logon above using a PreparedStatement to see if SQL injection happens. Package com.itheima;

import com.itheima.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Scanner;

/ * *

  • Using a PreparedStatement * /

Public class Demo8Login {// User name and password entered from the console public static void main(String[] args) throws SQLException {Scanner sc = new Scanner(System.in); System.out.println(” Please enter username: “); String name = sc.nextLine(); System.out.println(” Please enter password: “); String password = sc.nextLine(); login(name, password); }

/ * *

  • Login method

  • @param name

  • @param password */

private static void login(String name, String password) throws SQLException { Connection connection = JdbcUtils.getConnection(); String SQL = “select * from user where name=? and password=?” ; / / get the statement object PreparedStatement ps = connection. The prepareStatement (SQL); // Set ps.setString(1, name); ps.setString(2,password); ResultSet resultSet = ps.executeQuery(); If (resultSet.next()) {system.out.println (” resultSet.next “+ name); } else {system.out.println (” login failed “); } // Release resources, the subinterface directly to the parent interface jdbCutils. close(connection,ps,resultSet); }}

1.7 Relationship between tables and classes

1.7.1 Case: Query a piece of data in a PreparedStatement and encapsulate it as a Student object

package com.itheima;

import com.itheima.entity.Student; import com.itheima.utils.JdbcUtils;

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; Public class Demo9Student {public static void main(String[] args) throws SQLException {// Create Student object Student = new Student();

Connection connection = JdbcUtils.getConnection(); PreparedStatement ps = connection.prepareStatement(“select * from student where id=?” ); // set ps.setint (1,2); ResultSet resultSet = ps.executeQuery(); If (resultSet.next()) {// Encapsulate a student object student.setid (resultSet.getint (“id”)); student.setName(resultSet.getString(“name”)); student.setGender(resultSet.getBoolean(“gender”)); student.setBirthday(resultSet.getDate(“birthday”)); } // Release the resource jdbCutils. close(connection,ps,resultSet);

System.out.println(student); }}

1.7.2 Case: Encapsulate multiple records into a collection List, where each element is a JavaBean entity class

Queues: Queries all student classes, encapsulating them into a List, and returns a code to buy something anyway:

package com.itheima;

import com.itheima.entity.Student; import com.itheima.utils.JdbcUtils;

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;

Public class Demo10List {public static void main(String[] args) throws SQLException {// Create a set List students = new ArrayList<>(); Connection connection = JdbcUtils.getConnection(); PreparedStatement ps = connection.prepareStatement(“select * from student”); // There is no argument to replace ResultSet ResultSet = ps.executeQuery(); While (resultset.next ()) {// Each loop is a Student object Student = new Student(); // Encapsulate a student object student.setid (resultSet.getint (“id”));

student.setName(resultSet.getString(“name”)); student.setGender(resultSet.getBoolean(“gender”)); student.setBirthday(resultSet.getDate(“birthday”)); // Put the data into the collection student.add (student); } // Close the connection jdbCutils. close(connection,ps,resultSet); For (Student stu: Students) {system.out.println (stu); }}}

1.8 PreparedStatement Perform DML operations

package com.itheima;

import com.itheima.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException;

public class Demo11DML { public static void main(String[] args) throws SQLException { //insert(); //update(); delete(); }

Private static void insert() throws SQLException {Connection Connection = jdbCutils.getConnection (); PreparedStatement ps = connection.prepareStatement(“insert into student values(null,? ,? ,?) “); Ps. SetString (1,” white dragon “); ps.setBoolean(2, true); ps.setDate(3,java.sql.Date.valueOf(“1999-11-11″)); int row = ps.executeUpdate(); System.out.println(” insert “+ row + row “); JdbcUtils.close(connection,ps); }

Private static void update() throws SQLException {Connection Connection = jdbCutils.getConnection (); PreparedStatement ps = connection.prepareStatement(“update student set name=? , birthday=? where id=?” ); Ps. SetString (1,” Black Bear “); ps.setDate(2,java.sql.Date.valueOf(“1999-03-23″)); Ps. SetInt (3, 5); int row = ps.executeUpdate(); System.out.println(” update “+ row + row “); JdbcUtils.close(connection,ps); }

Private static void delete() throws SQLException {Connection Connection = jdbCutils.getConnection (); private static void delete() throws SQLException {Connection = jdbCutils.getConnection (); PreparedStatement ps = connection.prepareStatement(“delete from student where id=?” ); Ps. SetInt (1, 5); int row = ps.executeUpdate(); System.out.println(” delete “+ row + row “); JdbcUtils.close(connection,ps); }}

Before the JDBC transaction processing section 9, we used MySQL commands to manipulate transactions. Next we use JDBC to handle transactions for bank transfers.

2.1 Preparing Data

CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); INSERT INTO account (NAME, balance) VALUES (‘Jack’, 1000), (‘Rose’, 1000);

2.2 API is introduced

Void setAutoCommit(Boolean autoCommit) The parameter is true or false. If this parameter is set to false, automatic commit is disabled. Void commit() commit the transaction void rollback() rollback the transaction

2.3 Development Procedure

1) Get the connection 2) start the transaction 3) get PreparedStatement 4) Perform two update operations using PreparedStatement 5) Commit the transaction normally 6) Roll back the transaction if an exception occurs 7) Finally shut down the resource queue case code Package com.itheima;

import com.itheima.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException;

public class Demo12Transaction {

Public static void main(String[] args) {public static void main(String[] args) {

//1) Register driver Connection = null; PreparedStatement ps = null; Try {//2) Fetch connection = jdbCutils.getConnection (); //3) Enable transaction connection.setautoCommit (false); / / 4) access to the PreparedStatement from jack buckles money / / ps = connection. The prepareStatement (” update account set the balance = the balance -? where name=?” ); ps.setInt(1, 500); ps.setString(2,”Jack”); ps.executeUpdate(); System.out.println(100/0); / / add money to rose ps = connection. The prepareStatement (” update account set the balance = the balance +? where name=?” ); ps.setInt(1, 500); ps.setString(2,”Rose”); ps.executeUpdate(); // submit transaction connection.mit (); System.out.println(” transfer successful “); } catch (Exception e) { e.printStackTrace(); Try {// transaction rollback connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } system.out.println (” transfer failed “); } finally {//7) close the resource jdbcutils. close(connection,ps); }

}}