JDBC connection pool and dynamic SQL processing, jdbc dynamic SQL

JDBC connection pool and dynamic SQL processing, jdbc dynamic SQL
Review: 1. Create a properties configuration file first.

ClasssName = oracle. jdbc. driver. oracleDriverurl = jdbc: oracle: thin: @ Server IP: Port: Name user = set Username password = set password maxActive = maximum number of connections maxWait = maximum wait time

2. Load and read the configuration file

package day02;

Import java. io. fileInputStream; import java. io. fileNotFoundException; import java. io. IOException; import java. SQL. connection; import java. SQL. driverManager; import java. util. properties;

Import org. apache. commons. dbcp. BasicDataSource;

/*** This class is used to manage database connections * @ author adminitartor **/public class DBUtil {// database connection pool private static BasicDataSource ds;

Static {Properties prop = new Properties (); try {prop. load (new FileInputStream ("config. properties "); String className = prop. getProperty ("classname"); String url = prop. getProperty ("url"); String username = prop. getProperty ("username"); String password = prop. getProperty ("password"); int maxActive = Integer. parseInt (prop. getProperty ("maxactive"); int maxWait = Integer. parseInt (prop. getProperty ("maxwait"); // initialize the connection pool ds = new BasicDataSource (); // set the information required for the JDBC connection to the connection pool. // Class. forName (...) ds. setDriverClassName (className); // DriverManager. getConnection (...) ds. setUrl (url); ds. setUsername (username); ds. setPassword (password); // set the maximum number of connections in the connection pool ds. setMaxActive (maxActive); // sets the maximum wait time ds. setMaxWait (maxWait);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace () ;}}/*** get database Connection * @ return */public static Connection getConnection () throws Exception {return ds. getConnection ();}/*** close the specified Connection * @ param conn */public static void closeConnection (Connection conn) {try {conn. close ();} catch (Exception e) {e. printStackTrace ();}}

}

3. Process dynamic SQL statements

Java. SQL. PreparedStatement this interface is a subinterface of Statement. It is designed to execute dynamic SQL statements. Such an SQL statement is called a pre-compiled SQL statement, which uses dynamic information "? "Replace, placeholder first. Then, the SQL statement is sent to the database to generate an execution plan. Then, when you want to execute this SQL statement, you only need? You can pass the required actual data to the database again.
* 1: Because the SQL statement is first sent to the database and an execution plan is generated (the semantics has been determined), the SQL syntax (SQL Injection Attack) cannot be changed due to splicing SQL statements.
* 2: Because the execution plan has been generated, you only need to set? Indicates the actual value passed in, the database will reuse the execution plan, which reduces the pressure on the server.

    Connection conn = null;    try {        conn = DBUtil.getConnection();

// Use PreparedStatement
String SQL = "INSERT INTO userinfo"
+ "(Id, username, password, email, nickname, account )"
+ "VALUES"
+ "(Seq_userinfo_id.NEXTVAL ,?,?,?,?,?) ";
PreparedStatement ps
= Conn. prepareStatement (SQL );

Ps. setString (1, "liucan ");
Ps. setString (2, "123456 ");

Ps. setString (3, "liu@qq.com ");
Ps. setString (4, "hello ");
Ps. setDouble (5, 5000.0 );

Int d = ps.exe cuteUpdate ();
If (d> 0 ){
System. out. println ("inserted successfully! ");
}

    } catch (Exception e) {        e.printStackTrace();    } finally{        if(conn != null){            DBUtil.closeConnection(conn);        }    }}`