Shulou(Shulou.com)06/01 Report--

1 there are several shortcomings when using Statement to execute SQL statements with dynamic information: 1.1 due to the need to concatenate dynamic data into SQL statements, this leads to high complexity and error-prone programs

1.2 if the stitched data contains SQL grammatical content, it will lead to a change in the grammatical meaning of SQL after splicing, resulting in SQL injection.

1.3 when SQL with the same semantics is executed in large quantities, but contains dynamic data, it is inefficient 2 reasons why it is not good to execute SQL statements using Statement 2.1 when executing a SQL statement sent to the database, the database first parses the SQL and generates an execution plan (this process consumes resources and performance) If the same SQL statement is executed many times, the database will reuse the execution plan, but if the SQL with the same semantics but containing dynamic data is executed many times, the database will generate different execution plans, which will seriously affect the database overhead.

2.2 for example

Execute SELECT * FROM userifo_fury to generate an execution plan to execute SELECT * FROM userifo_fury again will reuse the above execution plan (because this is a static SQL statement

However, execute INSERT INTO userifo VALUES (1, 'JACK','122314','141234@QQ.COM','FURY',15600) to generate an execution plan, and then execute INSERT INTO userifo VALUES (2,' rose','122314','141234@QQ.COM','FURY',15600). Due to different contents, another execution plan will be generated again. If the INSERT in the above case is executed 1000 times, the database will generate 1000 execution plans. This seriously affects the efficiency of the database.

Therefore, Statement is only suitable for executing static SQL statements, not dynamic SQL statements.

3 using PreparedStatement instead of Statement to write simple SQL statements with the same semantics for batch execution without SQL injection will reuse the execution plan

1 package cn.xiangxu.entity; 2 3 import java.io.Serializable; 4 5 public class User implements Serializable {6 7 private static final long serialVersionUID =-5109978284633713580L; 8 9 private Integer id;10 private String name;11 private String pwd;12 public User () {13 super (); 14 / / TODO Auto-generated constructor stub15} 16 public User (Integer id, String name, String pwd) {17 super () 18 this.id = id;19 this.name = name;20 this.pwd = pwd;21} 22 @ Override23 public int hashCode () {24 final int prime = 31 result 25 int result = 1 result 26 result = prime * result + ((id = = null)? 0: id.hashCode ()); 27 return result 28} 29 @ Override30 public boolean equals (Object obj) {31 if (this = = obj) 32 return true;33 if (obj = = null) 34 return false;35 if (getClass ()! = obj.getClass ()) 36 return false;37 User other = (User) obj 38 if (id = = null) {39 if (other.id! = null) 40 return false;41} else if (! id.equals (other.id)) 42 return false;43 return true;44} 45 public Integer getId () {46 return id;47} 48 public void setId (Integer id) {49 this.id = id 50} 51 public String getName () {52 return name;53} 54 public void setName (String name) {55 this.name = name;56} 57 public String getPwd () {58 return pwd;59} 60 public void setPwd (String pwd) {61 this.pwd = pwd 62} 63 @ Override64 public String toString () {65 return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]"; 66} 67 68 69 70}

1 package testJDBC; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.util.ArrayList; 9 import java.util.List;10 11 import org.junit.Test;12 13 import cn.xiangxu.entity.User;14 15 public class TestCase {16 @ Test17 public void test01 () {18 Connection conn = null;19 PreparedStatement ps = null;20 ResultSet rs = null 21 try {22 Class.forName ("com.mysql.jdbc.Driver"); / / load database driver 23 24 conn = DriverManager.getConnection (/ / initialize connection object 25 "jdbc:mysql://localhost:3306/test", "root", "182838") 26 27 28 String sql = "SELECT * FROM user WHERE pwd =?"; / / concatenate SQL statements with position parameters using? Instead of 29 30 ps = conn.prepareStatement (sql); / / initialize the precompiled execution object 31 32 ps.setString (1, "182838"); / / set the position parameter in the SQL statement (note: it starts with 1, not 0) 33 34 rs = ps.executeQuery () / / execute the SQL statement 35 36 List users = new ArrayList (); / / create a collection to store the record object 37 while (rs.next ()) {/ / traverse the result set 38 / / System.out.println ("="); 39 / / System.out.println (rs.getInt ("id")) 40 / / System.out.println (rs.getString ("name")); 41 / / System.out.println (rs.getString ("pwd")); 42 User user = new User (); 43 user.setId (rs.getInt ("id")); 44 user.setName (rs.getString ("name")) 45 user.setPwd (rs.getString ("pwd")); 46 users.add (user); / / adding elements 47} 48 49 System.out.println (users) to the collection; / / printout collection 50 for (User user: users) {51 System.out.println (user) 52} 53 54 / / release resources 55 rs.close (); 56 ps.close (); 57 conn.close (); 58 59} catch (Exception e) {60 / / TODO Auto-generated catch block61 e.printStackTrace () 62} finally {63 if (rs! = null) {64 try {65 rs.close (); 66} catch (SQLException e) {67 / / TODO Auto-generated catch block68 e.printStackTrace () 69} 70} 71 if (ps! = null) {72 try {73 ps.close (); 74} catch (SQLException e) {75 / / TODO Auto-generated catch block76 e.printStackTrace () 77} 78} 79 if (conn! = null) {80 try {81 conn.close (); 82} catch (SQLException e) {83 / / TODO Auto-generated catch block84 e.printStackTrace () 85} 86} 87} 88 89} 90 91}

4 use Properties object to read information in properties configuration file 4.1 Properties inherits the Hashtable class, Properties object also uses key-value pairs to save data, but the keys and values of Properties object are string types

Class Properties extends Hashtable

4.2 main methods in the Properties class 4.2.1 public synchronized void load (InputStream inStream) throws IOException

Load the file input stream from the properties properties file into the Properties object

4.2.2 public void store (OutputStream out, String comments) throws IOException

Save the list of attributes in the Properties object to the output stream file

Note: the second parameter represents the comment information (note: Chinese cannot be used in the properties file). A time information will be automatically added after the comment information.

Note: the newly created file is under the root directory of the project (question: why is it not in eclipse, but can be found in the folder? )

4.2.3 public String getProperty (String key)

Gets the value of the property, and the parameter is the key of the property

4.2.4 public synchronized Object setProperty (String key, String value)

Modify the attribute value. Parameter 1 is the key of the property, and parameter 2 is the new value of the attribute.

4.3 cases

Requirements: read the total property values of the properties configuration file, modify the read property values and save them to another properties configuration file

1 package cn.xiangxu.entity; 2 3 import java.io.FileInputStream; 4 import java.io.FileOutputStream; 5 import java.io.InputStream; 6 import java.util.Iterator; 7 import java.util.Properties; 8 9 public class Test {10 public static void main (String [] args) {11 try {12 Properties prop = new Properties (); / / create Properties object 13 14 / / prop.load (new FileInputStream ("config.properties")) / / when using this method, the configuration file must be placed in the root directory of the project 15 InputStream is = Test.class.getClassLoader () .getResourceAsStream ("config/config.properties"); / / read property file 16 17 prop.load (is); / / load property list 18 19 Iterator it=prop.stringPropertyNames () .iterator () / / put all the key in the configuration file into an iterable object 20 while (it.hasNext ()) {/ / iterate 21 String key=it.next () using the iterator pattern; / / read the next element of the next iterative object 22 System.out.println (key+ ":" + prop.getProperty (key)) / / get the value value according to the key value (obtain attribute information) 23} 24 25 is.close (); / / close the input stream and release the resource 26 27 FileOutputStream oFile = new FileOutputStream ("b.properties", true); / / create an output stream file, and true means to open 28 prop.setProperty ("maxactive", "33") / / modify the attribute information 29 prop.store (oFile, "zhe shi yi ge xin de shu xing pei zhi wen jian."); / / put the contents of the Properties object into the file you just created to 30 oFile.close () / / close the output stream and release resources 31 32} catch (Exception e) {33 / / TODO Auto-generated catch block34 e.printStackTrace (); 35} 36} 37}

The location of the properties configuration file waiting to be read is shown in the following figure

5 database connection pool 5.1 what is database connection pool

When the program starts, it creates enough database connections and forms a connection pool, and the program automatically applies, uses, and releases the connections in the pool.

5.2 Operation mechanism of database connection pool

"create a connection pool when the program is initialized

"apply for an available database connection from the database connection pool when you need to manipulate the database

"return the database connection to the database connection pool after use (note: instead of closing the connection, give it to the connection pool)

"when the whole program exits, disconnect all connections and release resources (that is, all connections are closed after the thread managing the database connection pool is killed)

5.3 programming steps for database connection pooling 5.3.1 Guide package

5.3.2 declare ThreadLocal and BasicDataSource member variables (note: these two member variables are static)

5.3.3 instantiate the two member variables in the static code block, read the configuration file information through the Properties object, and use the configuration file information to initialize the BasicDataSource object

5.3.4 write static methods to create connections

Instantiate a connection object using BasicDataSource object

Put this connection object in the ThreadLocal object

5.3.5 write a static method to release a connection

Get the connection object from the ThreadLocal object

Clear the ThreadLocal object

Determine whether the connected object is released

6 use database connection pool to operate database

Project structure diagram

1 # zhe shi zhu shi, yi ban bu yong zhong wen 2 # deng hao liang bian mei you kong ge, mo wei mei you fen hao3 # hou mian bu neng you kong ge4 driverClassName=com.mysql.jdbc.Driver5 url=jdbc:mysql://localhost:3306/test6 username=root7 password=1828388 maxActive=1009 maxWait=3000

12 4.0.0 3 cn.xiangxu 4 testJDBC 5 0.0.1-SNAPSHOT 6 7 8 mysql 9 mysql-connector-java10 5.1.3711 12 13 junit14 junit15 4.1216 17 18 commons-dbcp19 commons-dbcp20 1.421 22 23

1 package cn.xiangxu.tools; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.Properties; 8 9 import org.apache.commons.dbcp.BasicDataSource 10 11 public class DBUtil {12 / * 13 * ThreadLocal is used to share data across threads. There is a Map inside 14 * ThreadLocal. Key is the thread itself that needs to share data, and value is the data it needs to share. 15 * / 16 private static ThreadLocal tl; / / declare something similar to a warehouse 17 private static BasicDataSource dataSource / / declare a database connection pool object 18 19 / / static code block, executed when the class is loaded, and only execute 20 static {21 tl = new ThreadLocal () once; / / instantiate warehouse object 22 dataSource = new BasicDataSource (); / / instance database connection pool object 23 24 Properties prop = new Properties () / / create a Properties object (which can be used to load the list of properties in the configuration file) 25 InputStream is = DBUtil.class.getClassLoader () .getResourceAsStream ("config/mysql.properties"); / / read configuration file information 26 try {27 prop.load (is) / / load the attribute list in the configuration file 28 29 String driverClassName = prop.getProperty ("driverClassName"); / / get attribute information 30 String url = prop.getProperty ("url"); 31 String username = prop.getProperty ("username"); 32 String password = prop.getProperty ("password") 33 Integer maxActive = Integer.parseInt (prop.getProperty ("maxActive")); 34 Integer maxWait = Integer.parseInt (prop.getProperty ("maxWait")); 35 36 dataSource.setDriverClassName (driverClassName); / / initialize the database connection pool (i.e., configure the prior pass parameter of the database connection pool) 37 dataSource.setUrl (url); 38 dataSource.setUsername (username) 39 dataSource.setPassword (password); 40 dataSource.setMaxActive (maxActive); 41 dataSource.setMaxWait (maxWait); 42 43 is.close (); / / close the input stream and release resources 44} catch (IOException e) {45 / / TODO Auto-generated catch block46 e.printStackTrace () 47} 48 49} 50 51 / * * 52 * create a connection object (Note: static methods can be called directly through the class name) 53 * @ return connection object 54 * @ throws Exception55 * / 56 public static Connection getConnection () throws Exception {57 try {58 Connection conn = dataSource.getConnection () / / create connection objects (created using database connection pool) 59 tl.set (conn); / / put connection objects in the warehouse 60 return conn; 61} catch (Exception e) {62 / / TODO Auto-generated catch block63 e.printStackTrace (); 64 throw e 65} 66} 67 68 / * * 69 * close the connection object (Note: static methods can be called directly through the class name) 70 * @ throws Exception71 * / 72 public static void closeConnection () throws Exception {73 Connection conn = tl.get (); / / remove the connection object 74 tl.remove () from the warehouse / / empty the warehouse 75 if (conn! = null) {/ / determine whether the connected object releases resources 76 try {77 conn.close (); 78} catch (Exception e) {79 / / TODO Auto-generated catch block80 e.printStackTrace (); 81 throw e 82} 83} 84} 85 86}

1 package testJDBC; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 7 import org.junit.Test; 8 9 import cn.xiangxu.tools.DBUtil;10 11 public class TestDBUtil {12 @ Test13 public void test01 () {14 try {15 Connection conn = DBUtil.getConnection (); / / create connection object 16 String sql = "SELECT * FROM user" / / stitching SQL statement 17 PreparedStatement ps = conn.prepareStatement (sql); / / creating execution object 18 ResultSet rs = ps.executeQuery (sql); / / executing SQL statement 19 while (rs.next ()) {/ / traversing result set 20 System.out.println (rs.getString ("name")) 21} 22} catch (Exception e) {23 e.printStackTrace (); 24} finally {/ / close the connection, releasing resources 25 try {26 DBUtil.closeConnection (); 27} catch (Exception e) {28 e.printStackTrace (); 29} 30} 31} 32}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.