1. History of database operation framework
1,JDBC
JDBC (Java database connection) is a Java API used to execute database SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language. JDBC provides a benchmark, which can build more advanced tools and interfaces to enable database developers to write database applications.
Advantages: operation period: fast and efficient.
Disadvantages: coding period: large amount of code, cumbersome exception handling, and does not support database cross platform.
JDBC core API:
1. DriverManager: responsible for connecting to the database.
2. Connection: Abstract flag to connect to the database.
3. Station: responsible for executing the real Sql statement.
4. ResultSet: get the real data result set.
2,DBUtils
DbUtils is an open source tool class library that simply encapsulates JDBC provided by Apache organization. Using it can simplify the development of JDBC applications without affecting the performance of the program.
DBUtils has three core functions:
1. QueryRunner is an api that provides operations on sql statements.
2. ResultSetHandler interface is used to define how to encapsulate the result set after the operation of select statement.
3. The DBUtils class is a tool class that defines the methods to close resources and transactions.
3,Hibernate
Hibernate: Hibernate is an open source object relational mapping framework. It encapsulates JDBC with very lightweight objects. It establishes a mapping relationship between POJO and database tables. It is a fully automatic orm framework. Hibernate can automatically generate SQL statements and execute them automatically, So that Java programmers can use object-oriented programming thinking to manipulate the database at will. Hibernate can be used in any situation where JDBC is used, not only in Java client programs, but also in Servlet/JSP Web applications. The most revolutionary thing is that hibernate can replace CMP in the Java EE architecture of EJB and complete the task of data persistence.
ORM: (object relational mapping), object relational mapping.
Hibernate benefits
1. You can create your own table and maintain the table structure. pojo is the same as python's odoo framework.
2. Fully object-oriented operation database.
3. Perfect cross database platform. Because accessing the database is also cross database and object-oriented, you basically only need to replace the database driver when migrating the project.
Hibernate disadvantages
1. Optimizing sql is difficult because sql is encapsulated.
2. Code coupling is too high.
3. It's hard to find bug s.
4. Batch data operations require a lot of memory space and too many objects during execution.
4,JDBCTemplate
JdbcTemplate is Spring's encapsulation of JDBC to make JDBC easier to use. The JdbcTemplate is part of Spring. The JdbcTemplate handles the creation and release of resources. It helps us avoid some common mistakes, such as forgetting to always close the connection. It runs the core JDBC workflow, such as the establishment and execution of Statement, and we only need to provide SQL statements and extract results.
JDBC template benefits
Runtime: efficient, embedded Spring framework, supporting declarative transactions based on AOP.
Disadvantages of JDBC template
It must be combined with the Spring framework, does not support database cross platform, and has no cache by default.
5,Mybatis
Mybatis is an excellent persistence layer / semi-automatic framework, which supports custom sql, stored procedures and advanced mapping. Mybatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets. Mybatis can configure and map primitive types, interfaces and Java POJO s (plain old Java objects) as records in the database through simple xml or annotations.
Mybatis benefits
1. Compared with JDBC, the amount of code is reduced by 50%.
2. The simplest persistence layer framework is easy to learn.
3. Sql code is separated from program code and can be reused.
4. Provide xml tags to support writing dynamic SQL.
5. Provide mapping labels to support the mapping between objects and ORM fields in the database.
6. Support caching, connection pool, database migration, etc.
Disadvantages of Mybatis
1. The workload of Sql statement writing is large and the proficiency is high.
2. Database portability is poor. If you need to switch databases, Sql will be very different.
2. Quickly build Mybatis project
2.1. Create a maven project and import pom dependencies
<!-- mybatis Core drive -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- Import mysql Driver of corresponding version (according to the version installed by yourself) -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.24</version>
</dependency>
How to find the connection dependency suitable for your installed mysql: just follow the figure below.
Enter the mysql official website mysql official website address
2.2. Configure a SqlSessionFactory file
Create an xml file to configure the core of mybatis
<?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">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
Quick view of drive parameters
Quick view url parameters
3. Simple use of mybatis
Create an entity class Emp:
package cool.ale.pojo;
/**
* @author dujlc
*/
public class Emp {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
Create an empmapper XML file, write sql statement
<?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 If it is based on statementId The way to execute SQL You can write whatever you want
If it is based on interface binding, you need to enter the fully qualified name of the corresponding interface-->
<mapper namespace="cool.ale.mapper.EmpMapper">
<select id="selectEmp" resultType="cool.ale.pojo.Emp">
select * from emp where id = #{id}
</select>
</mapper>
3.1. Call SQL based on statementId
Introduce empmapper.com into mybatis configuration file XML for subsequent creation of SqlSessionFactory
<mappers>
<mapper resource="EmpMapper.xml"/>
</mappers>
Test class
package cool.ale.tests;
import cool.ale.pojo.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class Test {
/**
* Execute SQL based on statementId
* @throws IOException
*/
@org.junit.Test
public void Test01() throws IOException {
// Building SqlSessionFactory from xml
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// The first way to get data
try (SqlSession session = sqlSessionFactory.openSession()) {
// The first parameter here is the namespace in EmpMapper + the id of the specific sql statement
// The second is the parameter passed in to sql
Emp emp = (Emp) session.selectOne("cool.ale.pojo.EmpMapper.selectEmp", 101);
System.out.println(emp);
}
}
}
3.2. Call SQL based on interface binding
1. Create an interface EmpMapper that calls sql
package cool.ale.mapper;
import cool.ale.pojo.Emp;
public interface EmpMapper {
/**
* Query sql by id
* @param id id parameter
* @return
*/
Emp selectEmp(Integer id);
}
2. Modify empmapper The namespace in the XML file is the namespace of the above interface
<?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">
<mapper namespace="cool.ale.mapper.EmpMapper">
<select id="selectEmp" resultType="cool.ale.pojo.Emp">
select * from emp where id = #{id}
</select>
</mapper>
3. Modify EmpMapper The import method of XML file needs to be introduced with mapper attribute in mybatis file. The EmpMapper interface is created above
<mappers>
<mapper class="cool.ale.mapper.EmpMapper"></mapper>
</mappers>
4. In particular, our EmpMapper interface file must be the same as EmpMapper The XML file is in the same directory, otherwise the file cannot be found. If we are a maven project, we can create the same file directory level by level here, as shown below:
5. Create test class
package cool.ale.tests;
import cool.ale.mapper.EmpMapper;
import cool.ale.pojo.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class Test {
/**
*
* Interface binding based approach
* @throws IOException
*/
@org.junit.Test
public void Test02() throws IOException {
// Building SqlSessionFactory from xml
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// The second way to get data
try (SqlSession session = sqlSessionFactory.openSession()) {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.selectEmp(101);
System.out.println(emp);
}
}
}
3.3. Call Sql based on annotation
1. Write the corresponding SQL on the method of the interface
package cool.ale.mapper;
import cool.ale.pojo.Emp;
import org.apache.ibatis.annotations.Select;
public interface EmpMapper {
/**
* Query sql by id
* @param id id parameter
* @return
*/
@Select("select * from emp where id = #{id}")
Emp selectEmp(Integer id);
}
2. Not in mapper The corresponding SQL is written in the XML file. Here, I will comment out the SQL of the second method, because the two methods cannot be shared
<?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 If it is based on statementId The way to execute SQL You can write whatever you want
If it is based on interface binding, you need to enter the fully qualified name of the corresponding interface-->
<mapper namespace="cool.ale.mapper.EmpMapper">
<!--<select id="selectEmp" resultType="cool.ale.pojo.Emp">
select * from emp where id = #{id}
</select>-->
</mapper>
3. Other configurations remain unchanged, and write the corresponding test class
/**
*
* Annotation based approach
* @throws IOException
*/
@org.junit.Test
public void Test03() throws IOException {
// Building SqlSessionFactory from xml
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// The second way to get data
try (SqlSession session = sqlSessionFactory.openSession()) {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.selectEmp(101);
System.out.println(emp);
}
}
4. Addition, deletion, modification and query of mybatis
4.1 preparations
1. Build a maven project and import the following jar package. For the version of mysql driven jar package, it is necessary to see Chapter 2.1 of this blog
<!-- mybatis Core drive -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- Import mysql Driver of corresponding version (according to the version installed by yourself) -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.24</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
2. Build the configuration file of Mybatis and name it Mybatis XML, mapper, the fully qualified name of the corresponding interface introduced in step 3
<?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">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="EmpMapper.xml"/>-->
<mapper class="cool.ale.mapper.EmpMapper"></mapper>
</mappers>
</configuration>
3. Define the corresponding interface to call sql statements and name EmpMapper
package cool.ale.mapper;
import cool.ale.pojo.Emp;
public interface EmpMapper {
/**
* Query sql by id
* @param id id parameter
* @return
*/
Emp selectEmp(Integer id);
}
4. Define the corresponding entity class according to the database table
package cool.ale.pojo;
/**
* @author dujlc
*/
public class Emp {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
5. Configure the corresponding sql writing file and name it empmapper XML, where the namespace must be the fully qualified name of the interface
<?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 If it is based on statementId The way to execute SQL You can write whatever you want
If it is based on interface binding, you need to enter the fully qualified name of the corresponding interface-->
<mapper namespace="cool.ale.mapper.EmpMapper">
<select id="selectEmp" resultType="cool.ale.pojo.Emp">
select * from emp where id = #{id}
</select>
</mapper>
There are three steps in the next work
1,EmpMapper. Write the corresponding Sql in the XML file.
2. Write the corresponding interface in the interface file.
3. Write out the test class call.
4.2 specific operation
<?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 If it is based on statementId The way to execute SQL You can write whatever you want
If it is based on interface binding, you need to enter the fully qualified name of the corresponding interface-->
<mapper namespace="cool.ale.mapper.EmpMapper">
<select id="selectEmp" resultType="cool.ale.pojo.Emp">
select * from emp where id = #{id}
</select>
<insert id="insertEmp">
INSERT INTO `mybatis`.`emp`(`id`, `name`) VALUES (#{id}, #{name});
</insert>
<update id="updateEmp">
UPDATE emp SET name = #{name} WHERE id = #{id};
</update>
<delete id="deleteEmp">
DELETE FROM emp WHERE id = #{id};
</delete>
</mapper>
package cool.ale.mapper;
import cool.ale.pojo.Emp;
public interface EmpMapper {
/**
* Query sql by id
* @param id id parameter
* @return
*/
Emp selectEmp(Integer id);
/**
* Insert Emp information
* @param emp Pass in the emp object to be inserted
* @return
*/
Integer insertEmp(Emp emp);
/**
* Objects that need to be updated
* @param emp Pass in the emp object that needs to be updated
* @return
*/
Integer updateEmp(Emp emp);
/**
* Objects to be deleted
* @param id id of emp object to be deleted
* @return
*/
Integer deleteEmp(Integer id);
}
package cool.ale.tests;
import cool.ale.mapper.EmpMapper;
import cool.ale.pojo.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class MybatisCRUDTest {
private SqlSession session = null;
@Before
public void before() throws IOException {
// Building SqlSessionFactory from xml
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// true is to set automatic submission
session = sqlSessionFactory.openSession(true);
}
@Test
public void select() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.selectEmp(101);
System.out.println(emp);
}
@Test
public void Insert() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setId(102);
emp.setName("Zhang San");
Integer result = mapper.insertEmp(emp);
System.out.println(result.toString());
}
@Test
public void Update() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setId(102);
emp.setName("Li Si");
Integer result = mapper.updateEmp(emp);
System.out.println(result.toString());
}
@Test
public void Delete() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Integer id = 102;
Integer result = mapper.deleteEmp(id);
System.out.println(result.toString());
}
}