Today’s content
Basic JDBC concepts 2. Quick Start 3. Detailed description of JDBC interfaces and classesCopy the codeJDBC:
1. Concept: Java DataBase Connectivity, the Java language to operate the DataBase * JDBC nature: in fact, the official (Sun) defined a set of rules to operate all relational databases, namely the interface. Each database vendor to implement this interface, provide the database driver JAR package. We can program using this set of interfaces (JDBC), and the actual code that executes is the implementation class in the driver JAR package. 1. Import the driver JAR package mysql-connector-java-5.1.37-bin.jar 1. Copy mysql-connector-java-5.1.37-bin.jar to the project libs directory 2. 2. Register driver 3. 4. Define SQL 5. Statement 6. Execute the SQL Statement and accept the result 7. Release resources * code implementation: //1. Import driver JAR package //2. Register Driver class.forname (" com.mysql.jdbc.driver "); / / 3. Access to the database Connection object Connection conn = DriverManager. GetConnection (" JDBC: mysql: / / localhost: 3306 / db3 ", "root", "root"); String SQL = "Update account set balance = 500 WHERE ID = 1"; //5. Obtain the SQL execution object Statement Statement STMT = conn.createstatement (); //6. Execute SQL int count = stmt.executeUpdate(SQL); System.out.println(count); //8. Release the resource stmt.close(); conn.close(); DriverManager: DriverManaged object * Function: 1. RegisterDriver: tells the program which database Driver jar to use. Static void registerDriver: registers with the given Driver DriverManager. Class. ForName (" com.mysql.jdbc.driver "); By viewing the source code found: in com. Mysql.. JDBC Driver class exists in the static block static {try {Java. SQL. DriverManager. RegisterDriver (new Driver ()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!" ); }} Note: the driver JAR package after mysql5 can omit the step of registering the driver. Static Connection getConnection(String URL, String user, String password) static Connection getConnection(String URL, String user, String password) JDBC: mysql: / / IP address (domain name) : port/database name * example: JDBC: mysql: / / localhost: 3306 / db3 * details: if the connection is native mysql server, the default port is 3306, and the mysql service url can be abbreviated to: JDBC :mysql:/// Database name * user: user name * password: password 2. Connection: database Connection object 1. Statement createStatement() * PreparedStatement prepareStatement(String SQL) 2. SetAutoCommit (Boolean autoCommit) : call this method and set the parameter to false, i.e. start the transaction * commit the transaction: commit() * rollback the transaction: rollback() 3. Statement: Object executing SQL 1. SQL > execute(String SQL); SQL > execute(String SQL); DML (INSERT, UPDATE, delete) statements and DDL(CREATE, ALTER, drop) statements * Returned value: number of affected rows. You can determine whether the DML statement is successfully executed based on the number of affected rows. If the returned value is greater than 0, the DML statement is successfully executed. ResultSet executeQuery(String SQL) : Execute THE DQL (SELECT) statement 2. Statement STMT = null; Statement STMT = null; Connection conn = null; Try {//1. Register Driver class.forname (" com.mysql.jdbc.driver "); SQL = "insert into account values(null,' w5 ',3000)"; / / 3. To get Connection object conn = DriverManager. GetConnection (" JDBC: mysql: / / / db3 ", "root", "root"); //4. Obtain the SQL execution object Statement STMT = conn.createstatement (); //5. Execute SQL int count = stmt.executeUpdate(SQL); System.out.println(count); system.out.println (count); If (count > 0){system.out.println (" added successfully! ); }else{system.out.println (" Add failed! ") ); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { //stmt.close(); // Avoid null pointer exception if(STMT! = null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn ! = null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }} * Boolean next(): * getXxx(argument): Get data * Xxx: represents the data type such as: Int getInt(), String getString() * Parameters: 1. Int: indicates the column number, starting from 1. For example, getString(1) 2. For example: getDouble("balance") * Note: Move cursor down a row 2. Check whether there is data 3. Get data // Loop to check whether cursor is the end of the last row. Int id = rs.getint (1); int id = rs.getint (1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); } * Exercise: * Define a method to query the data of the EMP table and encapsulate it as an object, then load the collection and return. Public List<Emp> findAll(){} 3. Select * from emp; 1. SQL injection problem: When concatenating SQL, some SPECIAL SQL keywords are involved in concatenating strings. 1. Enter the user name and password: a' or 'a' = 'a' 2. select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a' 2. Create a PreparedStatement object for SQL injection. Precompiled SQL: parameter use? Step 4:1. Import the driver JAR package mysql-connector-java-5.1.37-bin.jar. Register driver 3. Obtain database Connection object 4. Define SQL * Note: SQL parameters use? As a placeholder. Select * from user where username =? and password = ? ; 5. Obtain the executing SQL statements PreparedStatement object Connection. The prepareStatement (String SQL) 6. Give? SetXxx (parameter 1, parameter 2) * parameter 1:? Position number starting from 1 * parameter 2:? 7. Execute SQL and accept return results without passing SQL statement 8. Release resources. 5. Note that all operations will be completed in PreparedStatement. Can prevent SQL injection 2. More efficientCopy the codeExtract the JDBC utility class: JDBCUtils
* Purpose: simplify writing * analysis: 1. Register driver also extract 2. Extract a method to get the connection object * Requirement: do not want to pass parameters (trouble), but also ensure the universality of the utility class. Jdbc.properties url= user= password= 3. Public class JDBCUtils {private static String url; private static String user; private static String password; private static String driver; /** * file read, only need to read once to get these values. Use a static code block */ static{// read the resource file to get the value. 1. Create the Properties collection class. Properties pro = new Properties(); / / SRC directory path for file - > this class loader this this = JDBCUtils. Class. GetClassLoader (); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); System.out.println(path); ///D:/IdeaProjects/itcast/out/production/day04_jdbc/jdbc.properties //2. Load file // pro.load(new FileReader("D:\ IdeaProjects\\itcast\\ day04_jdbc.src \\jdbc.properties")); pro.load(new FileReader(path)); //3. Get data, assign url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //4. Register driver class.forname (driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); @return Connection object */ public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password); } public static void close(Statement STMT,Connection conn){if(STMT! = null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if( conn ! = null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); Public static void close(ResultSet rs,Statement STMT, ResultSet rs,Statement STMT, Connection conn){ if( rs ! = null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if( stmt ! = null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if( conn ! = null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }}}} * Exercises: * Requirements: 1. Input user name and password by keyboard 2. * select * from user where username = "" and password = ""; * If the SQL query has a result, it succeeds; otherwise, it fails. CREATE TABLE user CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32), PASSWORD VARCHAR(32)); INSERT INTO USER VALUES(NULL,'zhangsan','123'); INSERT INTO USER VALUES(NULL,'lisi','234'); Public static void main(String[] args) {//1. Scanner sc = new Scanner(system.in); System.out.println(" Please enter username: "); String username = sc.nextLine(); System.out.println(" Please enter password: "); String password = sc.nextLine(); Boolean flag = new JDBCDemo9().login(username, password); //2. If (flag){system.out. println(" Login succeeded!" ); }else{system.out.println (" Wrong username or password! ") ); }} / * * * * / login method public Boolean login (String username, String password) {if (username = = null | | password = = null) { return false; } // Connect to the database to check whether the login is successful. Statement stmt = null; ResultSet rs = null; Conn = jdbCutils.getConnection (); //1. SQL = "select * from user where username = '"+username+"' and password = '"+password+ '"; STMT = conn.createstatement (); STMT = conn.createstatement (); Rs = stmt.executeQuery(SQL); /* If (rs.next()){return true if(rs.next()); }else{ return false; }*/ return rs.next(); // If there is a next line, return true} catch (SQLException e) {e.prinintstackTrace (); }finally { JDBCUtils.close(rs,stmt,conn); } return false; }}Copy the codeJDBC control transactions:
1. Transaction: A business operation involving multiple steps. If the business operation is transaction-managed, these steps will either succeed or fail at the same time. Operation: 1. Start transaction 2. Commit transaction 3. SetAutoCommit (Boolean autoCommit) : Call this method and set the parameter to false, i.e. start transaction * Start transaction before SQL execution * Commit transaction: Commit () * When all SQL has executed commit transaction * Rollback transaction: rollback() * Rollback transaction in catch 4. Public class JDBCDemo10 {public static void main(String[] args) {Connection conn = null; PreparedStatement pstmt1 = null; PreparedStatement pstmt2 = null; Conn = jdbCutils.getConnection (); //1. // Enable the transaction conn.setautoCommit (false); Sql1 = "update account set balance = balance -? where id = ?" ; Sql2 = "Update account set balance = balance +? where id = ?" ; SQL object pstmt1 = conn.prepareStatement(sqL1); pstmt2 = conn.prepareStatement(sql2); //4. Set the pstmt1.setDouble(1,500); Pstmt1. SetInt (2, 1); Pstmt2. SetDouble (1500); Pstmt2. SetInt (2, 2); //5. Run SQL pstmt1.executeupdate (); Int I = 3/0; pstmt2.executeUpdate(); // Commit transaction conn.mit (); } catch (Exception e) {// try {if(conn! = null) { conn.rollback(); } } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally { JDBCUtils.close(pstmt1,conn); JDBCUtils.close(pstmt2,null); }}}Copy the code