What is JDBC and why do you want to learn JDBC?
Java Database Connectivity Java Database Connectivity is a technology that uses the Java language /Java programs to connect to and access databases, although previously we could use CMD or Navicat To connect to a database, you can also operate on databases, tables, table records, and so on. However, in the future development is more through the program to connect to the database, if it is the Java language, through the Java program to connect to the database, you must learn JDBC this technology.How to connect to MySQL database through Java program? Create a test class: com.tedu.jdbctest01
//2. Get the database connection //3. Get the transporter //4. Sends the SQL statement to the server for execution and returns the result //5. Release resourcesSummary of the JDBC API
1) Class. Class.forname (". Com. Mysql. Cj. JDBC Driver "); When you load the "com.mysql. cj.jdbc.driver "class from the MySQL Driver package into memory, the static code block in the Driver class will be executed. In the static code block of the Driver class, there is a line of code that registers the Driver. Registration driver: The mysql driver to the JDBC program management, in order to use the function after JDBC4.0 versions, this step can be omitted, but still recommended plus 2) DriverManager. GetConnection (url, user, password) Url: specify the location of which is to connect which a library JDBC: mysql: / / localhost: 3306 / jt_db? CharacterEncoding = utf-8 & serverTimezone = Asia/Shanghai port is 3306, if the database connection port can be omitted don't write: JDBC: mysql: / / localhost/jt_db? � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �? The CharacterEncoding = UTF-8 & ServerTimeZone =Asia/Shanghai getConnection method returns a Connection object used to represent a Connection between the Java program and the database server. Conn. CreateStatement () conn. CreateStatement () -- fetches the transport object STAT.EXECUTEQuery (SQL) that sends SQL statements to the database -- ResultSet stat. Execute Update(SQL) -- SQL statements for type add, delete, modify, and return an int value. 4) rs.next() -- Used to move the arrow to the data row down one row and returns a Boolean value (true or false). True means that the arrow to the data row has data after moving the arrow down one row; False means that the arrow moves down one row to point to a row with no data; 5) Methods to obtain data are provided on the ResultSet ResultSet object, the common ones are rs.getInt(ColName); rs.getInt( colCount ); rs.getString( colName ); rs.getString( colCount ); rs.getDobule( colName ); rs.getDobule( colCount ); . rs.getObject( colName ); rs.getObject( colCount );
Two, JDBC add, delete and change check
New: 1, a record is added to the account table: name of 'Lucy', the amount is 3500 2, modified: modify the table name is' Lucy 'amount, will amount to 2000 3, delete: delete the account name in the table for' Lucy 'records3. JUnit unit testing framework
JUnit (unit testing framework): To execute a method without adding a main function or creating an instance of a class, a method that can be executed with unit tests must meet the following conditions: 1) Method must be public 2) Method must be non-static 3) Method must have no return value (void) 4) Method must be parameterless If the method executed does not meet any of the above conditions, the following error is reported: java.lang.Exception: No tests found matching... Three common annotations for JUnit (the unit testing framework) : @test, @before, @after @test: methods that use this annotation can be executed each time you select the method name, right click -->Run as-- --> JUnit Test. @before: methods that use this annotation will be executed each time Before the method marked by @test. That is, every time the @test method is executed Before the @test method @after: method that uses the annotation is executed After the @test method. That is, every time the @test tagged method is executed, the @after tagged method is executedPreparedStatement object
Statement Transport PreparedStatement Transport object1. Simulate the user login case
A PreparedStatement object is a child of a Statement transport object. PreparedStatement objects are more secure than Statement objects and perform more efficiently in some ways! Login below to a simulated case to explain the PreparedStatement object -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- please login: please enter your user name: Select * from user where username=' Tom '#' and password='' -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- please login: please enter your user name: zhang fei 'or' 1 = 1 please enter a password: Select * from user where username=' Tom 'or '1' =' 1' and password=' 1' Password = "Congratulations! ----------------------------------------------------------2. SQL injection attack
Causes of SQL injection attacks: Since the parameters in the SQL statement are concatenated, and the value of the parameter (username and password) is submitted by the user, if the user mixes some SQL keywords or special characters (or, #, --, /* */, etc.) in the parameter submission, the semantics of the SQL statement may be tampered. Delete from user where id=1 or 1=1; delete from user where id=1 or 1=1;How to solve the problem of SQL injection attack
1) You can verify the parameters submitted by the user (for example, check the user name and password through regular expression). If there are symbols like OR, #, -- in the user name or password, you will no longer log in. The user will be directly reminded that the input is illegal, please login again! 2) or use the PreparedStatement object provided in JDBC to solve the SQL injection attack problem! How do PreparedStatement objects resolve SQL injection attacks? 1) PreparedStatement object is to first send the skeleton of SQL statement (without parameters) to the server for compilation and determination. String sql = "select * from user where username=? and password=?" ; PreparedStatement stat = conn.prepareStatement(sql); SetString (1, user); stat. SetString (1, user); stat.setString( 2, psw ); ResultSet rs = stat. ExecuteQuery (); Since the skeleton of the previous SQL statement has been determined, so even if the SQL parameter contains SQL keywords or special symbols, will not affect the skeleton or semantics of the SQL statement, will only be processed by the current ordinary text, so it can prevent SQL injection!1. What is a connection pool?
Pools: A pool in a constant pool, a thread pool, a connection pool, and so on is a container. A spatial connection pool in memory: a group of connection resources stored in a container. The purpose is to achieve connection reuse, reduce the number of connection creation and closure, in order to improve the efficiency of program execution!2. Why use connection pooling?
In the traditional way, each time a connection is needed, a connection is created (object/resource), the database is accessed based on the connection created, and finally the connection is closed! Each "create connection" and "close connection" consumes a lot of time and resources compared to the use of connection, resulting in very inefficient program execution! In order to improve the efficiency of program execution, we can create a batch of connections in a connection pool at the start of the program, for the entire program to share. When a user needs a connection, he/she does not need to create a connection, but directly obtains a connection from the connection pool for use. When the connection is finished, he/she does not need to close the connection, but directly returns the connection to the connection pool. In this way, all the connections in the connection pool can be used, so the reuse of connections can be achieved and the number of connections created and closed can be reduced. Improve the efficiency of program execution!How to use C3P0 connection pool?
DBCP/c3p0 / druid/hikari due to all the connection pool technology to realize the DataSource interface provided by the SUN So the connection pool is also called "data source" 01 step: in the program to create a c3p0 connection pool objects (container storage connection) ComboPooledDataSource pool = new ComboPooledDataSource(); Step 02: set the basic information of the connection database (four parameters) way one: will connect the parameters of the database through the setXXX method directly through the Java code written in the program pool. SetDriverClass ("com.mysql. cj.jdbc.driver "); pool.setJdbcUrl("jdbc:mysql:///jt_db? characterEncoding=utf-8&serverTimezone=Asia/Shanghai"); pool.setUser("root"); pool.setPassword("root"); This method is not recommended, because this method will connect the parameters written in the program, once the parameters change in the future, we need to change the program, after the change need to recompile, package, deploy, run the project, will increase the maintenance cost! Extract the parameters of the connection to the database into the c3p0.properties file (the file name is fixed) and place this file in the source root directory (SRC root directory). The file contents are as follows: ----------------------------------------- c3p0.driverClass=com.mysql.cj.jdbc.Driver c3p0.jdbcUrl=jdbc:mysql:///jt_db? characterEncoding=utf-8&serverTimezone=Asia/Shanghai c3p0.user=root c3p0.password=root ----------------------------------------- Again: The file location and name are fixed, because the underlying c3p0 will find the name of the specified file to the specified location, if not in accordance with the requirement to store files or not in accordance with the requirements to the specified file name, will cause c3p0 cannot find the file, also won't be able to read the configuration information, will inevitably lead to not even to the database! Method 3: Extract the parameters connected to the database into the c3p0-config. XML (the file name is also fixed) file and need to put this file in the source root directory (SRC root directory), the contents of the file are as follows: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- <? The XML version = "1.0" encoding = "utf-8"? > <c3p0-config> <default-config> <property name="driverClass"> com.mysql.cj.jdbc.Driver </property> <property name="jdbcUrl"> jdbc:mysql:///jt_db? characterEncoding=utf-8& serverTimezone=Asia/Shanghai </property> <property name="user">root</property> <property name="password">root</property> < / default config > - < / c3p0 - config > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - again: The file location and name are fixed, because c3p0 underlying will find the name of the specified file to the specified location, if not in accordance with the requirement to store files or not in accordance with the requirements to the specified file name, will cause c3p0 cannot find the file, also won't be able to read the configuration information, will inevitably lead to not even to the database! Connection conn = pool.getConnection(); conn = pool.getConnection(); Step 05: Return the used connection object to the pool conn. Close (); If there are no use any connection pool, the program needs to connect via DriverManager. GetConnection (create) a connection, after finished, call conn. Close close () is to connect resources. If connection pooling is used, the getConnection method is called from the connection pool object to get a connection object that has already been modified. When it is done, calling the conn. Close () method returns the connection to the pool. That is, the close method on the connection object retrieved from the connection pool is transformed to still connect to the connection pool!4. Supplementary content: Differences between XML files and properties files
Similarity: Both of these files can be used as configuration files in enterprise development, and they are especially used in many different ways: 1) Disadvantages of XML files: There are many configuration information, which makes it more troublesome to write. If you need to read through Java programs, the code is also more troublesome. 2) Advantages of XML file: it is possible to save structured data (for example, all the provinces in China and the cities contained in the province can be saved in XML file) 3) Disadvantages of properties file: configuration information structure is key/value, it is impossible to save structured data 4) properties file Advantages: The configuration information is simple and easy to read if you need to read it through a Java program.= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = a, what is a transaction?
Transactions: In simple terms, a transaction is a bunch of SQL statements bound together and executed with the result that either all of them succeed or all of them fail. And it is all successful to be successful, if there is a failure to execute, it will be handled by the whole failure! For example: Update set money=money-100 where name=' moneys '; update set money=money-100 where name=' moneys '; Update set money=money+100 where name=' money '; update set money=money+100 where name=' money '; -- 1000 -- Commit/rollback transaction Example: Online shopping -- start a transaction -- insert into the order table an order information (user, order number, item information, item data amount, unit price, total amount, etc.) ; UPDATE UPDATE UPDATE SET COUNT = COUNT -2 WHERE... -- commit transaction/rollback transaction2. Four characteristics of transactions (important)
1, Atomicity: means that all operations (SQL) in a transaction are a whole, cannot be divided, either all executed successfully, or all failed! 2. Consistency: the sum of the business data before and after the transaction is consistent. Before the transfer operation, the sum of the amount of Zhang San's account (1000) and Li Si's account (1000) is 2000 Yuan. After the transfer operation, the sum of the amount of Zhang San's and Li Si's accounts is still 2000 Yuan, no matter whether the transaction is committed or rolled back. 3, Isolation: all transactions are isolated, in a transaction can not see the state of another transaction in progress! Transaction 1: query (1000) and B (1000) account the total amount of the transaction 2: Mysql > open transaction A minus $100 -- A:900 -- B:1100 -- commit transaction/rollback transaction Updates to the data are persisted in the database only after the transaction commits -- open transaction A ($1000) minus $100 -- A:900 -- B ($1000) plus $100 -- B:1100 -- commit/rollback transactions3. MySQL transactions
By default, an SQL statement in MySQL is a transaction. Start transaction/begin; start transaction/begin; start transaction/begin; start transaction/begin; start transaction/begin; start transaction/begin Example: Use transfer to demonstrate how to start and end a transaction in MySQL. Update ACC set money-100 where name='A'; update ACC set money-100 where name='A'; UPDATE ACC SET MONEY = MONEY +100 WHERE NAME ='B'; UPDATE ACC SET MONEY = MONEY +100 WHERE NAME ='B'; select * from acc; - rollback transaction | to commit the transaction rollback; | commit; select * from acc;