1-1 introduction
it is a tool for java programs to connect and access databases. It is also a manual, which specifies a unified format,
the real addition, deletion, modification and query operation needs to be implemented by the database driver package (jar package) (jar package is provided by different manufacturers, which contains compiled Class files, and there are many tool classes to implement GRUD). At the same time, there are different jar packages and different APIs, resulting in the problem of high learning cost. Later, sun company provided a set of interfaces, requiring all manufacturers to implement this interface, realizing the unification of APIs and providing specifications. This specification is jdbc
the jar package of jdbc has been included in java, but most of them are interface specifications. Specific functions need to be implemented and the driver package corresponding to the database needs to be imported
Implementation flow chart:
2-1. Introduction case
1. After preparing the data to query, import the package, create the lib directory (Folder), import the mysql driver package (download), and create a reference (right click – > build path -- > add to build path)
2. Write JDBC code after the package import is completed
public static void main(String[] args) throws Exception { //1. Registering the database driver will create a mysql driver package reflection object, which will be dynamically placed in the DriverManager for management Class.forName("com.mysql.cj.jdbc.Driver"); //2. Obtain the database connection, obtain the connection through the method provided by the DriverManager class, and set the port, database, character set and time zone Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8&serverTimezone=Asia/Shanghai" ,"root","root"); System.out.println("Connection succeeded!"); //3. Get the transmitter, / / station is the interface, and specifies the format, such as the name of the unified method Statement stat = connection.createStatement(); //4. Send sql to the database for execution and return the execution result. The specific method implemented by mysql Driver, ResultSet rs = stat.executeQuery("select * from account"); //5. Processing results (output the query results to the console line by line) while(rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); double money = rs.getDouble("money"); System.out.println(id+ "," + name+ "," + money); } /* Add, delete and modify: //5-1,Add operation int rows = stat.executeUpdate("insert into account value(null,'hellen',3500)"); //5-2,Delete operation int rows = stat.executeUpdate("delete from account where name = 'hellen' "); //5-3,Modify operation int rows = stat.executeUpdate("update account set money =5000 where name = 'hellen' "); System.out.println("The number of affected rows is: "+ rows); conn.close(); stat.close(); */ //6. Release resources rs.close(); stat.close(); connection.close(); }
Supplementary: transaction (multiple sql statements are bound and executed together, and the test interview is required)
1. Transaction has four characteristics:
Atomicity: transactions are inseparable as the smallest unit. If an sql statement reports an error, the whole sql cannot be executed
Consistency: the sum of business data before and after transaction execution remains unchanged
Isolation: when transactions are concurrent, transactions are isolated from each other
Persistence: after the transaction is committed, the database is persisted in the disk
2. Transaction operation:
By default, each sql is a separate transaction, and submission is automatically enabled
The transaction contains multiple sql and needs to be started and ended manually
After starting the transaction in begin, you need to commit the transaction before you can really modify the data. If you change it to rollback, it will be rolled back
Thinking summary
Through the introduction case, it is not difficult to find that JDBC has the following disadvantages: 1. A large number of duplicate codes. Register the connection transmitter to release resources2. It is troublesome to process the result set of query statements, and you need to obtain columns one by one (refer to the introductory case)
3. Writing SQL statements and connection parameters in the program is difficult for on-line maintenance in the later stage In addition, although JDBC, as a traditional underlying method, has faster access speed than the third-party framework, it does not have a connection pool (similar to a constant pool, which is directly taken from the pool after creation, without creating a connection itself), resulting in the need to create and close a connection each time, which is time-consuming and inefficient Aiming at the shortcomings of JDBC source code, this paper introduces an excellent persistence layer (the part of the program connecting to the database) framework - Mybatis 2. Mybatis (encapsulating simplified JDBC)
2-1. Introduction case (IDE: Eclipse: Simple maven Project)
Connection relationship: empmapper.xml < - MybatisDemo01.java - > mybatis config. XML - > empmapper.xml - > pojo.emp.java
1,MybatisDemo01
- The two configuration file names and Emp class names can be modified,
public class MybatisDemo01 { public static void main(String[] args) throws Exception { //1. Read the configuration information in the mybatis core configuration file (mybatis config. XML) InputStream in = Resources.getResourceAsStream( "mybatis-config.xml" ); //2. Obtain the SqlSessionFactory object (factory) based on the configuration information read above SqlSessionFactory factory = new SqlSessionFactoryBuilder().build( in ); //3. Open the connection to the database (that is, obtain the SqlSession object through the factory object) SqlSession session = factory.openSession(); //4. Find and execute the SQL statement through namespace+id, and return the processed results //EmpMapper.xml, List<Emp> List<Emp> list = session.selectList( "EmpMapper.findAll" ); //5. Output results for (Emp emp : list) { System.out.println( emp ); } } }
2,POJO.Emp
public class Emp { //Provide private properties private Integer id; private String name; private String job; private Double salary; //Provide get, set, toString Methods
3-1,EmpMapper.xml
- namespace: name mapper, id: name sql operation.
Call: List = session. Selectlist ("EmpMapper.findAll");
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace: Used to identify the current mapper file(It's a name) stay mybatis The program needs to use this name to locate the current mapper file adopt namespace value+id Value can locate which one to execute SQL sentence --> <mapper namespace="EmpMapper"> <!-- adopt select,insert,update,delete Label to store the to be executed SQL --> <!-- Exercise 01: query emp All employee information in the table --> <!-- id attribute:Request the current in this file id Value must be unique(Cannot repeat) resultType attribute: Specifies the type of object in which the query results are stored --> <select id="findAll" resultType="com.java.pojo.Emp"> select * from emp </select> </mapper>
3-2,mybatis-config.xml
- default: select the environment with the specified id. id: uniquely identify each environment.
type: configure transaction management and connection pool. value: basic database information. resource: import the specified file under the class directory
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!-- MyBatis Global profile for --> <configuration> <!-- 1.Configure development environment --> <environments default="dev"> <environment id="dev"> <!-- 1.1.Configure transaction management mode JDBC: Delegate transactions to JDBC Administration(mybatis The transaction is automatically started,However, manual submission is required) MANAGED: Manage transactions manually by yourself --> <transactionManager type="JDBC"></transactionManager> <!-- 1.2.Configure connection pool information, type Value of: JNDI: Deprecated UNPOOLED: Do not use connection pool POOLED: Use connection pool(You can reduce the number of connections created,Improve execution efficiency) --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///yonghedb?characterEncoding=utf-8&serverTimezone=Asia/Shanghai"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!-- 2.Import XxxMapper.xml file(If mapper Multiple files, You can use multiple mapper Label import) resource Property is directly to the class directory(classes)Go down and find the file in the specified location --> <mappers> <mapper resource="EmpMapper.xml"/> </mappers> </configuration>
4,pom.xml
- Four driver packages, plus the dependency of the driver package, are eight jar packages, and the maven local warehouse is not checked
<dependencies> <!-- junit unit testing --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.9</version> </dependency> <!-- mysql drive --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.2.8</version> </dependency> <!-- integration log4j --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.6.4</version> </dependency> </dependencies>