JDBC Series: (3) Execute SQL statements using PreparedStatement

interface for executing SQL statements

Interface	function
Statement interface	Used to execute a static SQL statement
PreparedStatement interface	Used to execute precompiled SQL statements
CallableStatement interface	SQL statement for execution of stored procedures (call XXX)

PreparedStatement Vs Statement

Serial Number	different	Description
1	Different syntax	PreparedStatement can use precompiled SQL, while statment can only use static SQL
2	Different efficiency	PreparedStatement can use SQL buffers, which is more efficient than statment
3	Different security	PreparedStatement can effectively prevent SQL injection, and statment does not prevent SQL injection.

1. Establish db.properties file

Url=jdbc:mysql://localhost:3306/testdbuser=rootpassword=rootdriverclass=com.mysql.jdbc.driver

2. JDBC Tool class: Jdbcutil.java

package com.rk.db.utils;import java.io.ioexception;import java.io.inputstream;import  java.util.properties;import java.sql.connection;import java.sql.drivermanager;import  JAVA.SQL.RESULTSET;IMPORT JAVA.SQL.SQLEXCEPTION;IMPORT JAVA.SQL.STATEMENT;/** * JDBC's Tool class  *  @author  rk * */public class jdbcutil{private static final  string url;private static final string user;private static final  String password;private static final String driverClass;/** *  Static code block (only one time)  */static{try{//read Db.properties file inputstream instream =  JDBCUtil.class.getClassLoader (). getResourceAsStream ("db.properties"); Properties props = new properties ();//Load File Props.load (instream);//Read Information url =  Props.getproperty ("url"), User = props.getproperty ("user");p assword = props.getproperty (" PasSword ");d riverclass = props.getproperty (" Driverclass ");//Register Driver Class.forName (driverclass);} catch  (ioexception e) {System.out.println ("Error reading database configuration file"); Throw new runtimeexception (e);} catch  (classnotfoundexception e) {System.out.println ("Error in Database driver registration");throw new  RuntimeException (e);}} /** *  getting connections to databases  *  @return   database connections  */public static Connection  Getconnection () {try{return drivermanager.getconnection (Url,user,password);} catch  (sqlexception e) {System.out.println ("Error getting database connection"); Throw new runtimeexception (e);}} /** *  close connection, statement, and resultset *  @param  conn  database connections  *  @param  stmt commands for executing SQL statements  *  @param  rs  result set  */public static void close ( Connection conn,statement stmt,resultset rs) {closequietly (RS); closequietly (stmt); closeQuietly ( conn);} /** *  quiet shut down database resources  *  @param  AC  object  */public static void closequietly (AUTOCLOSEABLE AC) {if (AC) that implements the Autocloseable interface  != null) {try{ac.close ();} catch  (exception e) {e.printstacktrace ();}}}}

3. Execute INSERT statement

Package com.rk.db.c_prepared;import java.sql.connection;import java.sql.sqlexception;import  java.sql.PreparedStatement;import com.rk.db.utils.JDBCUtil;/** *  Use PreparedStatement to execute INSERT statements  *  @author  RK */public class Demo01{public  Static void main (String[] args) {connection conn = null; PREPAREDSTATEMENT PSTMT = NULL;TRY{//1. Get connection Conn = jdbcutil.getconnection ();//2. Ready to Precompile sqlstring sql =  "Insert into t_persons (username,pwd)  values (?,?)"; /3. Executing a precompiled SQL statement (check syntax) pstmt = conn.preparestatement (SQL)//4. Setting parameter values:  parameter location    Starting from 1 pstmt.setstring (1,  "Earth Man");p stmt.setstring (2,  "987");//5. Send parameters, execute sqlint count =  Pstmt.executeupdate (); System.out.println ("Affects the +count+" line!) ");} catch  (sqlexception e) {e.printstacktrace ();} finally{//Close Resource Jdbcutil.close (Conn, pstmt, null);}}

4. Execute UPDATE statement

Package com.rk.db.c_prepared;import java.sql.connection;import java.sql.sqlexception;import  java.sql.PreparedStatement;import com.rk.db.utils.JDBCUtil;/** *  Use PreparedStatement to execute UPDATE statements  *  @author  RK */public class Demo02{public  Static void main (String[] args) {connection conn = null; PREPAREDSTATEMENT PSTMT = NULL;TRY{//1. Get connection Conn = jdbcutil.getconnection ();//2. Ready to precompile sqlstring sql =  "Update t_persons set username=?, pwd=? where  id=? "; /3. Executing a precompiled SQL statement (check syntax) pstmt = conn.preparestatement (SQL)//4. Setting parameter values:  parameter location    Starting from 1 pstmt.setstring (1,  "Mars Man");p stmt.setstring (2,  "456");p Stmt.setint (3, 5);//5. Send parameters, execute Sqlint  count = pstmt.executeupdate (); System.out.println ("Affects the +count+" line!) ");} catch  (sqlexception e) {e.printstacktrace ();} finally{//Close Resource Jdbcutil.close (conn, pstmt,  null);}}} 

5. Execute DELETE statement

package com.rk.db.c_prepared;import java.sql.connection;import  java.sql.sqlexception;import java.sql.preparedstatement;import com.rk.db.utils.jdbcutil;/**  *  use PreparedStatement to execute a DELETE statement  *  @author  rk */public class demo03{ Public static void main (String[] args) {connection conn = null; PREPAREDSTATEMENT PSTMT = NULL;TRY{//1. Get connection Conn = jdbcutil.getconnection ();//2. Ready to Precompile sqlstring sql =  "delete from t_persons where id=?"; /3. Executing a precompiled SQL statement (check syntax) pstmt = conn.preparestatement (SQL)//4. Setting parameter values:  parameter location    Starting from 1 pstmt.setint (1, 5);//5. Send parameters, execute sqlint count = pstmt.executeupdate (); System.out.println ("Affects the +count+" line!) ");} catch  (sqlexception e) {e.printstacktrace ();} finally{//Close Resource Jdbcutil.close (Conn, pstmt, null);}} 

6. Execute SELECT statement

package com.rk.db.c_prepared;import java.sql.connection;import java.sql.resultset;import  java.sql.sqlexception;import java.sql.preparedstatement;import com.rk.db.utils.jdbcutil;/** *   Use PreparedStatement to execute SELECT statements  *  @author  rk */public class demo04{public  static void main (String[] args) {connection conn = null; preparedstatement pstmt = null; Resultset rs = null;try{//1. Getting the connection conn = jdbcutil.getconnection ();//2. Preparing the precompiled SqlString  sql =  "Select * from t_persons";//3. Execute Precompiled SQL statements (check syntax) pstmt =  Conn.preparestatement (SQL);//4. Execute SQL statement to get the returned result Rs = pstmt.executequery ();//5. Output while (Rs.next ()) {int  id = rs.getint ("id"); String username = rs.getstring ("UserName"); String pwd = rs.getstring ("pwd"); System.out.println (id +  "\ t"  + userName +  "\ T" &nbsP;+ PWD);}} catch  (sqlexception e) {e.printstacktrace ();} finally{//Close Resource Jdbcutil.close (CONN, PSTMT, RS);}}

7. Mind Mapping

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/F8/wKioL1czKejhkEiuAADsVB6cT98372.png "width=" 1200 " height= "979" alt= "Wkiol1czkejhkeiuaadsvb6ct98372.png"/>

JDBC Series: (3) Execute SQL statements using PreparedStatement