Using JDBC to connect to the database some problems

Let’s start with a piece of code that uses JDBC

From the above code, we can see some problems
1. With JDBC, SQL statements are written in JAVA code. To modify SQL statements, you must modify the code. The coupling degree is too high
2. The parameters need to be set manually, and the results need to be converted manually if there are query results, which is not object-oriented
3. You need to manually shut down the connection pool resource, please
4. Too much code, too much duplicate code

Mybatis can solve these problems

What is Mybatis

MyBatis is a semi-ORM database persistence framework.
- Framework: A half-finished tool that consists of a bunch of JAR packages. So to use the framework, the first step is to import the JAR package
- Persistence: is to save the object data to the database, so as to achieve the purpose of power outages also exist, called persistence
- ORM: Object Relational Mapping (ORM for short) : It is a technology to solve the mismatch between object-oriented and Relational databases.
- Semi-orm (semi-mapping) : Instead of full mapping, you need to write your own SQL statements.
- Full ORM framework (full mapping) : JPA, Hibernate

Mybatis underlying or native JDBC code, the PACKAGING of JDBC code;
- JDBC says simple point is the only technology for Java to connect to the database, so Mybatis is actually a package of JDBC, let us use more simple
Mybatis origin: can be understood through Baidu encyclopedia
Mybatis advantages: – SQL statements extracted from Java code, convenient maintenance, modify SQL without modifying Java code – parameters and result sets can be through the object, Mybatis automatically help us parse, binding – Mybatis and spring framework integration, do not have to manually close resources

3. Introduction to Mybatis

– prepare

To use the framework, the first step is to import the JAR package

Other steps:
- Prepare corresponding test database, test form and test data
- Prepare the domain corresponding to the database table
- Prepare the dao layer interface and empty implementation corresponding to the entity class
- Prepare test packages and test classes

package cn.itsource.domain;

import java.math.BigDecimal;

public class Product {
	
	// id
	private Long id;
	
	// Product name
	private String productName;

	/ / brand
	private String brand;
	
	/ / supplier
	private String supplier;
	
	
	/ / retail price
	private BigDecimal salePrice;
	
	/ / purchase price
	private BigDecimal costPrice;
	
	/ / a discount
	private Double cutoff;
	
	// Product category number
	private Long dir_id;

	public Product(a) {
		super(a);// TODO Auto-generated constructor stub
	}

	public Product(Long id, String productName, String brand, String supplier, BigDecimal salePrice, BigDecimal costPrice, Double cutoff, Long dir_id) {
		super(a);this.id = id;
		this.productName = productName;
		this.brand = brand;
		this.supplier = supplier;
		this.salePrice = salePrice;
		this.costPrice = costPrice;
		this.cutoff = cutoff;
		this.dir_id = dir_id;
	}

	public Long getId(a) {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getProductName(a) {
		return productName;
	}

	public void setProductName(String productName) {
		this.productName = productName;
	}

	public String getBrand(a) {
		return brand;
	}

	public void setBrand(String brand) {
		this.brand = brand;
	}

	public String getSupplier(a) {
		return supplier;
	}

	public void setSupplier(String supplier) {
		this.supplier = supplier;
	}

	public BigDecimal getSalePrice(a) {
		return salePrice;
	}

	public void setSalePrice(BigDecimal salePrice) {
		this.salePrice = salePrice;
	}

	public BigDecimal getCostPrice(a) {
		return costPrice;
	}

	public void setCostPrice(BigDecimal costPrice) {
		this.costPrice = costPrice;
	}

	public Double getCutoff(a) {
		return cutoff;
	}

	public void setCutoff(Double cutoff) {
		this.cutoff = cutoff;
	}

	public Long getDir_id(a) {
		return dir_id;
	}

	public void setDir_id(Long dir_id) {
		this.dir_id = dir_id;
	}

	@Override
	public String toString(a) {
		return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", supplier=" + supplier
				+ ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id
				+ "]"; }}Copy the code

– Start Implementation step analysis

Implementation step analysis
- The code is ready to go, so you’re ready to use it
- Source of Study:
  
  1. Official website: mybatis.org/mybatis-3/z…
  
  2. Official documents
Mybatis: SqlSessionFactory, Mybatis: SqlSessionFactory, Mybatis: SqlSessionFactory
In the documentation or in the official introductory case, we have been told how to get this core object, so let’s go through it step by step
- To get SqlSessionFactory, we need to prepare a core Mybatis-config. XML file and create it with SqlSessionFactoryBuilder.
  
  (1) Create the Mybatis core configuration file (Mybatis-config.xml) and configure the database environment;
  
  (2) Load the core configuration file;
  
  Create an SqlSessionFactoryBuilder object.
  
  (4) Build an SqlSessionFactory object by SqlSessionFactoryBuilder;
  
  (5) Create and configure Mapper mapping files;
  
  (6) Obtain SqlSession through SqlSessionFactory to execute mapping SQL;

– Entry implementation:

Configure the core configuration file: Mybatis-config.xml

    
      
    <! DOCTYPEconfiguration
      PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
	<! Default: Which environment is used by default (must have a corresponding environment ID) -->
	<environments default="development">
		<! -- an environment ID: give the environment a unique id name -->
		<environment id="development">
		<! -- transaction management type:JDBC(support transaction)/MANAGED(do nothing) -->
		<transactionManager type="JDBC" />
		<!-- 数据源, 连接池  type(POOLED):MyBatis自带的连接池 -->
		<dataSource type="POOLED">
		<! Connect to database: write to die
			<property name="driver" value="com.mysql.jdbc.Driver" />
			<property name="url" value="jdbc:mysql:///mydb" />
			<property name="username" value="root" />
			<property name="password" value="admin" />     
	</environments>
   <! Mappers represent the corresponding ORM mapping file -->
	<mappers> 
		<mapper resource="cn/itsource/dao/ProductMapper.xml" /> 
	</mappers> 
</configuration> 
Copy the code

Preparing the mapping file:
- Our mapping file (also known as mapper file) is usually in the same package as its corresponding DAO interface;
- The name of this mapping file is usually xxxmapper.xml (Xxx stands for entity class name)
  - For example,
    - Entity classes are: cn) itsource) domain. The Product
    - Mapping file called: cn/itsource/dao/ProductMapper. XML
    The name of a namespace is usually the fully qualified name of the interface;
    
    All types except those supported by MyBatis are fully qualified


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<! SQL Mapper: root namespace: namespace: fully qualified name of interface -->
<mapper namespace="cn.itsource.dao.ProductDao">
	<! Select * from namespace where id = 'namespace'; select * from namespace where id = 'namespace'; cn.itsource.mybatis.day1._1_hello.IProductDao.get parameterType : The parameter type passed in is long: large long _long: small long (see the documentation for specific mappings). ResultType: resultType (object type returned by the first data) the object must be fully qualified class name -->
	<select id="get" parameterType="long" resultType="cn.itsource.domain.Product">
		select * from product where id = #{id}
	</select>
</mapper> 
Copy the code

Entry code:

    // Query all methods
    @Test
    public List<Product> findAll(a) {
        SqlSession sqlSession = null;
        try {
            //1 Prepare the configuration file ok
            Create SqlSessionFactory / / 2
            Reader reader = Resources.getResourceAsReader("MyBatis-Config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
         
            //3 Obtain the sqlSession and perform the operation
            sqlSession = sqlSessionFactory.openSession();

            // call the SQL statement (namespace+.+id) - copy, pass in the parameter, receive the return value
            return  sqlSession.selectList("cn.itsource.dao.ProductDao.findAllProduct");

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(sqlSession ! =null) { sqlSession.close(); }}return null; }}// Add, delete, change, etc.

Copy the code

Iv.Mybatis tool class extraction

– Why extract MybatisUtil utility class:

In fact, every Dao method should obtain SQLSession, and use to close, such as access and close operations should be handed over to tools;

– Extract ideas for analysis

To get the core configuration file stream and get the core object, we only need to get it once, so we can take advantage of the properties of static code blocks, write it in static code blocks, and load it once as the class loads
The easiest way to create utility classes is to use enumerated classes
Since every thread should have its own SqlSession instance, SqlSession instances cannot be shared and are not thread safe, so provide a method to obtain SqlSession, so that we need to obtain a new SqlSession every time

Note: Open SqlSession connection, be sure to close, close is very important

– Extraction implementation

package cn.itsource.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public enum MybatisUtil {
	INSTANCE;
	private static SqlSessionFactory sqlSessionFactory;
	
	static{
		try {
			InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
		} catch(IOException e) { e.printStackTrace(); }}public SqlSession getSqlSession(a) {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		returnsqlSession; }}Copy the code

– Complete CRUD using utility classes

5.SQL Mapper

– an overview

When we used Mybatis to operate the database, we wrote the implementation class as well as the interface, but in fact Mybatis provided us with the implementation through the dynamic proxy mechanism, so that we did not need to write the implementation class by ourselves
Traditional interfaces end with DAO, but Mybatis recommended specifications do not use DAO as suffix, but mapper as suffix, such as IProductDao -> ProductMapper. And the package name is mapper
- The SQL statement configuration file must be in the same package as the Mapper interface class

– Introduction to mapper

Mybatis provides us with mapper bytecode mapping. Mybatis provides us with mapper bytecode mapping.
Mapper implementation steps:
- Create Mapper interfaces related to the model as required, for example: ProductMapper;
- Compile a mapping file in the same package, for example, productmapper.xml.
  - The namespace of the Mapper mapping file must be the same as the fully qualified name of the interface.
  - Define SQL tag ID, must be the same as “interface method name”;
- Configure (or register) Mapper mapping file in Mybatis core profile;
- test
  
  Note: It is best not to use method overloading in mapper interface, because the ID of SQL tag is the name of the method.

– Two implementations:

Traditional implementation:

How the mapper is implemented: Basically, we give Mybatis the bytecode file of the interface we want to implement and ask it to create the entity class for us so that we can call the method directly using the entity class.
- Principle: Mybatis will help us create entity class through dynamic proxy mode, when we call entity class, actually Mybatis will find our entity class according to the Mapper we added in the core configuration file, and locate SQL statement according to the fully qualified name and interface name, and execute it

Vi.Mybatis usage details

– Returns a self-increasing ID when adding

Summary: in JDBC we know that we can get self-growing primary key ID when inserting data, so in Mybatis we can get self-growing primary key ID.
Two ways:

The first:

-useGeneratedKeys: true/false: This property means whether to enable the return of self-growing ID. -keyProperty: This property means which field in the object you insert after the return of self-growing ID. -keyColumn: What is the primary key ID in the database? If the primary key is the first column then you can omit it, if it is not the first column then you must write itCopy the code

XxxMapper.xml写法：
<!-- 插入数据并返回自增ID
有自增ID功能数据库可以采用useGeneratedKeys="true"开启判断是否是自增ID
keyProperty="id"  指定插入数据后自增ID返回时赋值给实体类的那个属性(这里是id属性)
-->
<!-- void insert1(Product product) -->
<insert id="insert"  useGeneratedKeys="true" keyProperty="id">
	insert into product(productName) values(#{productName})
</insert>
Copy the code

Second: it is no longer used

Xxxmapper. XML <! -- void insert2(Product product) --> <insert id="insert2"> <! SELECT LAST_INSERT_ID() from product (SELECT LAST_INSERT_ID() from product (SELECT LAST_INSERT_ID() from product (SELECT LAST_INSERT_ID() from product))); ResultType: specify the resultType of SELECT LAST_INSERT_ID(). The resultType attribute in selectKey specifies the data type that is expected to be returned by the primary key. The keyProperty property specifies the name of the field from which the entity class object receives the primary key. The ORDER property specifies whether the SQL statement is executed before or after the insert statement. Mysql to use after, ResultType ="java.lang.Long"> SELECT LAST_INSERT_ID() from LAST_INSERT_ID() </selectKey> insert into product(productName) values(#{productName}) </insert>Copy the code

– Mybatis run logs

Why logs are needed:
- We used to see the console log using system.out.println (), but this way to print the log will not print the runtime log, that is, Mybatis SQL statement. Let’s say we write an insert statement, we don’t know what the statement looks like when it’s executed, so we need the log to help us display it in the console or in a file, right
Common logging frameworks:
- System.out.print(“xxxx”)
- Write a logging framework
  - dblLog.jar(yhptest)
  - Log log = LogFactory.getLog(this.class)
  - log.error/Info/warn…
- Define the standard
  - The newly written logging framework follows logging standards;
  - Previous logging frameworks used adapters for adaptation;
- Standards: SLf4J (standard) — Commons.logging, log4j (implementation)
- The most commonly used logging framework is the Log4j logging framework
  - Log4j is mainly used to output log information. Information levels (fatal, error, Warning, debug, info) can be output in different ways (console, file, database) and formats.
  - Implementation steps:
    - Import log4j JAR packages (3).
    - Add log4j.properties to resources.
  - Log4j consists of:
    - Logger: Responsible for message output, provides a variety of different levels of output methods;
    - Appender: Controls how messages are exported, such as to the console, to files, etc.
    - Layout: controls the output format of messages;
  - Log4j configuration file configuration in several ways: this time we use console output + custom layout

# logger # output appender # layout #1 Console output # specifies logger output level and logger name #log4j.rootLogger=info, myConsole # specifies logger output . # log4j appenders. Myconsole = org.. Apache log4j. ConsoleAppender # specifies the layout #log4j.appender.myconsole.layout=org.apache.log4j.SimpleLayout #2. # the specified log file output. TXT of output level, and the name of the logger. # log4j rootLogger = error,. Myfile # # designated follower log4j appenders. Myfile = org.. Apache log4j. FileAppender . # log4j appenders. Myfile. The File = E: \ \ log4j. TXT # specify layout editor (general layout text output) # log4j. Appender. Myfile. Layout = org). Apache log4j. SimpleLayout # 3. # the specified log file output. HTML is the output of the level and the name of the logger. # log4j rootLogger = error,. Myhtml # # designated follower log4j appenders. Myhtml = org.. Apache log4j. FileAppender . # log4j appenders. Myhtml. The File = D: \ \ log4j HTML # specified layout editor (layout) # log4j. Appender. Myhtml) layout = org.. Apache log4j. HTMLLayout # 4. Console output + file output. TXT # Specify logger output level and logger name #log4j.rootLogger=error,con,file # specify logger #log4j.appender.con=org.apache.log4j.ConsoleAppender #log4j.appender.file=org.apache.log4j.FileAppender . # log4j appenders. File. The file = D \ \ \ log4j. TXT # specify layout editor (layout) # log4j. Appender. Con. The layout = org). Apache log4j. SimpleLayout #log4j.appender.file.layout=org.apache.log4j.SimpleLayout #5. Console output + custom layout log4j. RootLogger = DEBUG,. My # specified output device log4j appenders. My = org.. Apache log4j. ConsoleAppender # specify layout editor (custom layout) . # specify layout for custom layout log4j appenders. My. Layout = org.. Apache log4j. PatternLayout # specified in a custom layout format, % d, says the current system time, % t - threads execute the business name, %p -- the level of the diarist, -5 -- 5 for the number of output characters, and the symbol for right-aligned #%c -- for the fully qualified name (package name) of the class in which the business is specified. Class name), %m -- output additional information, % n - newline log4j. Appender. My. Layout. The ConversionPattern = [t] % % % d - 5 p [c] % % m % n # set package (can be customized bag) can also be the API output level log4j.logger.org.springframework=info log4j.logger.cn.itsource=debugCopy the code

– Alias is used in MyBatis

In SQL XML, our return value is our own class object, so we need to write the fully qualified name of the class, so why not parameter type long?
There are some built-in aliases in Mybatis

In addition to the built-in alias provided by Mybatis, we can also alias our own entity class, which needs to be configured in the Mybatis core configuration file

Method 1: Alias an entity class

<typeAliases>
	<! -- Single configuration: used when testing -->
	<typeAlias type="cn.itsource.domain.Product" alias="Product" />
</typeAliases>
Copy the code

Method 2: Configure aliases for all classes under a package

<typeAliases>
	<! -- package configuration: used in the project, after the package is added, the class name or the first letter of the class name is the alias -->
	<package name="cn.itsource.domain" />
</typeAliases>
Copy the code

– # differs from $& OGNL expression

# {}
- In Mybatis, use # to prevent SQL injection, because # will be replaced by? PreparedStament was used to set parameters, there was no SQL injection problem;
- # is recommended in most cases
The ${}
- In Mybatis, the $symbol will show the content as it is, which has the risk of SQL injection
- Note: Use # except $to concatenate SQL structures
OGNL expressions
- In fact, the way we use #{},${} has a name OGNL expression, on the page it is also called EL expression
Use OGNL expression to transform four attributes
- We can see in the official document that the four attributes are obtained through OGNL expression when the four attributes are configured in the configuration file. Therefore, we also obtain the connection attributes according to the official recommended way
Use steps:
- Modify the Mybatis core profile to set the connection properties to use the properties file


      
<! DOCTYPEconfiguration
  PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
  
<configuration>

    <! Add classpath: to the configuration file. Resource: imports resources in the classpath, that is, classpath, so you do not need to write classpath: url: imports resources in the network path or disk path -->
        <! -- If you use properties file, you need to import properties file -->
	<properties resource="db.properties"></properties>
	
	<! -- Alias tag -->
	<typeAliases>
	<! -- configure an alias for the entity class below that package. The alias is either the class name or the first letter of the class name.
		<package name="cn.itsource.domain"/>
	</typeAliases>
	
	
	<! Default: Which environment is used by default (must have a corresponding environment ID) -->
	<environments default="development">
		<! -- an environment ID: give the environment a unique id name -->
		<environment id="development">
		<! -- transaction management type:JDBC(support transaction)/MANAGED(do nothing) -->
		<transactionManager type="JDBC" />
		<!-- 数据源, 连接池  type(POOLED):MyBatis自带的连接池 -->
		<dataSource type="POOLED">
                <! -- Connect database parameters: how to use properties file -->
                    <property name="driver" value="${db.driver}" />
                    <property name="url" value="${db.url}" />
                    <property name="username" value="${db.username}" />
                    <property name="password" value="${db.password}" />
		</dataSource>
		</environment>
	</environments>
   <! Mappers represent the corresponding ORM mapping file -->
	<mappers> 
		<mapper resource="cn/itsource/dao/ProductMapper.xml" /> 
		<mapper resource="cn/itsource/mapper/ProductMapper.xml" /> 
	</mappers> 
</configuration> 
Copy the code

Add the properties file db.properties under Resources

db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql:///test0303
db.username=root
db.password=admin
Copy the code

Run a query test to check whether the property file mode is configured successfully

Mybatis foundation has been completed

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Three framework learning – Mybatis foundation

Using JDBC to connect to the database some problems

What is Mybatis

3. Introduction to Mybatis

– prepare

– Start Implementation step analysis

– Entry implementation:

Iv.Mybatis tool class extraction

– Why extract MybatisUtil utility class:

– Extract ideas for analysis

– Extraction implementation

– Complete CRUD using utility classes

5.SQL Mapper

– an overview

– Introduction to mapper

– Two implementations:

Vi.Mybatis usage details

– Returns a self-increasing ID when adding

– Mybatis run logs

– Alias is used in MyBatis

– # differs from $& OGNL expression

Three framework learning – Mybatis foundation

Using JDBC to connect to the database some problems

What is Mybatis

3. Introduction to Mybatis

– prepare

– Start Implementation step analysis

– Entry implementation:

Iv.Mybatis tool class extraction

– Why extract MybatisUtil utility class:

– Extract ideas for analysis

– Extraction implementation

– Complete CRUD using utility classes

5.SQL Mapper

– an overview

– Introduction to mapper

– Two implementations:

Vi.Mybatis usage details

– Returns a self-increasing ID when adding

– Mybatis run logs

– Alias is used in MyBatis

– # differs from $& OGNL expression

Related Posts

Advanced JAVA Development skills: Java8 new date and time API (4)JSR-310: Common computing tools)

My Douban movie review grab tour

K8s Series 14: Autoscaler