JavaEE Beginner Level 4--Beginners must see

Long live your health and innocent business. Hello, I'm Laity and your brother J.

Introduction to JavaWeb

Use Java technology to solve the technology stack in the related web Internet domain (in fact, to develop a website)

Java has a long history with a wide range of api s. (Background Development Engineer major database and JavaWeb program development)

2. Database

2.1 Mysql

2.1. 1 Database

2.1. 2 Database Management System

Large software for managing databases (MYSQL is DBMS)

2.1.3 SQL

Programming Language for Operating Databases (Operating All Relational Databases on the Market)

Relational Database
- Mysql
- Oracle
- SQLite
- SQL Server
Non-relational database
- MangoDb

2.1.4 Mysql Installation

MySQL :: Download MySQL Community Server (Archived Versions) Mysql5.7.24

Unzip it--configure environment traversal--system variables--

New MYSQL_HOME = D:...

Path -- %MYSQL_HOME%\bin

Create a my in the installation directory. Ini

 [mysql]
 default-character-set=utf8
 
 [mysqld]
 character-set-server=utf8
 default-storage-engine=INNODB
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,EEROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.1. 5 Initialize Mysql

Administrator window in cmd

  mysqld --initialize-insecure

c/windows/system32/cmd.exe

2.1. 6 Register Mysql's services

cmd

 mysqld -install

2.1. 7 Start Mysql Service

 net start mysql   // Start Services
 net stop mysql  // Out of Service

2.1. 8 View local services (windows)

 services.msc

2.1. 9 Modify default account password

 mysqladmin -uroot password 1234

2.1. 10 login parameters

 mysql -u User name -p Password -h To connect mysql Server's ip address(Default 127.0.0.1) -P port(Default 3306)
 mysql -ulaity -pLaity123? -h39.106.227.23 -P3306

2.1.11 cmd import data

 First create the database
 create database mynewblog
 
 Uploaded sql Import into database
 pass:take sql File upload to root lower
 
 Follow these three steps to quickly import this sql file
 
 mysql>use mynewblog;
 mysql>set names utf8;
 mysql>source /root/database.sql;
 
  
 
 Then the screen keeps rolling and finally prompts for successful import.
 
 Finally, remember to database.sql delete
 
 Determines whether the data table was created successfully, that is, whether the data file was imported successfully.
 Execute Command show tables；View the tables under the database.
 
 Delete database
 drop database <Database Name>;
 
 If you encounter Unknown collation: 'utf8mb4_0900_ai_ci'
 • Put all in the file utf8mb4_0900_ai_ci Replace with utf8_general_ci
 • as well as utf8mb4 Replace with utf8
 Otherwise it will report COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

-- Insert data into a table
CREATE TABLE Table Name {
	id ...
	username ...
	password ...
};
INSERT INTO Table Name(id,username,password) value(1,"Laity", 123456);

2.1. 12 Constraints

Concepts:

Constraints are rules that act on columns in a table to restrict data that is added to the table.
The existence of constraints ensures the correctness, validity and integrity of the data in the database.

Classification of constraints:

Constraint Name	describe	Keyword
Non-empty constraint	Ensure that all data in a column cannot have null values	NOT NULL
Unique Constraint	Make sure all the data in the column is different	UNIQUE
Primary Key Constraint	Primary key is the unique identification of a row of data, which requires non-empty and unique	PRIMARY KEY
Check Constraints	Ensure that the values in the column satisfy a condition	CHECK
Default Constraints	When saving data, default values are used when no values are specified	DEFAULT
Foreign Key Constraints	Foreign keys are used to connect data between two tables to ensure uniqueness and integrity of the data	FOREIGN KEY

MYSQL does not support check constraints

auto_increment: number type and unique constraint when column

2.1. 13 Detail foreign key constraints

Concepts: Foreign keys are used to link data between two tables to ensure data consistency and integrity

-- Add foreign key constraints when creating tables
create table Table Name {
     Column Name Data Type
     ...
     [constraint] [Foreign Key Name] foreign key(Foreign Key Name) references Main table(Primary Table Column Name)
 };
 
-- Add foreign key constraints after the table is built
alter table Table Name add constraint Foreign Key Name foreign key (Foreign Key Field Name) references Main table name(Primary table column name);
 
-- Delete foreign keys
alter table Table Name drop foreign key Foreign Key Name

2.1. 14 Database Design

One-to-many
One-on-one
Many-to-many

2.1. 15 multi-table queries

Cartesian product: all combinations of sets A and B
Multi-table Query: Query data from multiple tables
- join query
  - Internal join: equivalent to querying A B intersected data
  - External connection:
    - Left outer join: equivalent to querying all data of table A and intersecting some data
    - Right outer join: equivalent to querying all data of Table B and intersecting part of the data
- Subquery

join query

Internal connection

 -- Implicit internal connection
 select Field List from Table 1, Table 2... where condition;
 
 -- Display inner connection
 select Field List from Table 1 [inner] join Table 2 on condition;
 
 -- An inner join is equivalent to a query A B Tables intersect data

External Connection

 -- Left Outer Connection
 select Field List from Table 1 left [inner] join Table 2 on condition;
 
 -- Right Outer Connection
 select Field List from Table 1 right [inner] join Table 2 on condition;
 
 
 -- Left outer join: equivalent to query A Table All Data and Intersection Partial Data
 -- Right outer join: equivalent to query B Table All Data and Intersection Partial Data

Subquery

 -- Concepts: Nested queries in queries are called subqueries
 -- Subqueries work differently depending on the results of the query:
 -- Single row, single column: as a condition value, use = != > < And so on to make conditional judgments
 select Field List from surface where Field name = (Subquery);
 -- Multiple rows, single column: as a condition value, use in Conditional judgment with equal keywords
 select Field List from surface where Field name in (Subquery);
 -- Multiple rows and columns: as virtual tables
 select Field List from (Subquery) where condition;

2.1. 16 Transactions

 -- Transactions: Widely used in banking systems, order systems, etc.     The engine type of the table must be  innodb Types can use transactions, which is MySQL Default Engine
 Transaction is an operating system,Either all,Either none is executed, it is an indivisible unit.
 Four characteristics of transactions:(ACID)
     1.Atomicity(A)  :All operations in the entire transaction are either successful or unsuccessful
     2.Uniformity(C)  : The result of the final execution is the same problem in the middle and no data loss will occur
     3.Isolation(I)  : When a transaction is open: changes made by one transaction are not visible to other transactions until they are finally committed(One sql Execution will not affect another sql)
     4.Persistence(D)  :Once the transaction commits commit,Then the changes will be permanently saved to the database (At this point, even if the database system crashes, the modified data will not be lost)
 -- Transaction open: begin; / start transaction;  Middle: sql Sentence    Transaction committed, end: commit;   Rollback transaction: Discard data changed in cache: rollback;

2.2 JDBC

Concepts: A set of API s for manipulating relational databases through Java code

2.2.1 JDBC drives jar package download

MySQL :: Download Connector/J
The operating system selects Platform Independent, two records in the list below, with the suffix tar.gz is the Linux version. zip is the version of windows. I'm using 5.1. 48

Download the jdbc driver for windows version 8.0 here. 18

Skip login and click directly on the content in the red box to download it

2.2.2 JDBC Quick Start

Create a project and import the driver jar package
Register Driver
1. Class.forName("com.mysql.jdbc.Driver");
Get Connections
1. Connection conn = DriverManager.getConnection(url,username,password);
Define SQL Statement
1. String sql = "update...";
Get SQL Object
1. Statement stmt = conn.createStatement();
Execute SQL
1. stmt.executeUpdate(sql);
Processing Return Results
Release Resources

package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC Quick Start)
  */
 public class JDBCTest {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
         // 1. Registration drivers can be omitted from not writing
         Class.forName("com.mysql.jdbc.Driver");
         // 2. Get the three connection parameters url username password
         // 2.1 jdbc:mysql://Fixed syntax
         // 2.2 Short for jdbc:mysql:///
         String url = "jdbc:mysql://127.0.0.1:3306/java";  // ?useSSL=false
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // 3. Define a SQL statement
         String sql = "update account set money = 2000 where id = 1";
         // 4. Get the object that executes sql, Statement
         Statement stmt = conn.createStatement();
 
         // 5. Execute sql
         int count = stmt.executeUpdate(sql);  // Number of rows affected
 
         // 6. Processing results
         System.out.println(count);
 
         // 7. Release Resources
         stmt.close();
         conn.close();
     }
 }

2.2.3 JDBC API Details

JDBC API Details: DriverManager

 package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC API Details: DriverManager)
  */
 public class JDBCTest2_DriverManager {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
         // 1. Registration drivers can be omitted from not writing
         // Class.forName("com.mysql.jdbc.Driver");
         // 2. Get the three connection parameters url username password
         // 2.1 jdbc:mysql://Fixed syntax
         // 2.2 Short for jdbc:mysql:///
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // 3. Define a SQL statement
         String sql = "update account set money = 2000 where id = 1";
         // 4. Get the object that executes sql, Statement
         Statement stmt = conn.createStatement();
 
         // 5. Execute sql
         int count = stmt.executeUpdate(sql);  // Number of rows affected
 
         // 6. Processing results
         System.out.println(count);
 
         // 7. Release Resources
         stmt.close();
         conn.close();
     }
 }

JDBC API Details: Connection (Transaction)

package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC API Details: Connection)
  */
 public class JDBCTest3_Connection {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
         // 1. Registration drivers can be omitted from not writing
         // Class.forName("com.mysql.jdbc.Driver");
         // 2. Get the three connection parameters url username password
         // 2.1 jdbc:mysql://Fixed syntax
         // 2.2 Short for jdbc:mysql:///
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // 3. Define a SQL statement
         String sql1 = "update account set money = 3000 where id = 1";
         String sql2 = "update account set money = 3000 where id = 2";
         // 4. Get the object that executes sql, Statement
         Statement stmt = conn.createStatement();
 
         // Open Transaction
         conn.setAutoCommit(false);
         try {
             // 5. Execute sql
             int count1 = stmt.executeUpdate(sql1);  // Number of rows affected
             // 6. Processing results
             System.out.println(count1);
             int count2 = stmt.executeUpdate(sql2);  // Number of rows affected
             System.out.println(count2);
             // Submit Transaction
             conn.commit();
         } catch (Exception throwables) {
             // Rollback transaction
             conn.rollback();
 
             throwables.printStackTrace();
         }
 
         // 7. Release Resources
         stmt.close();
         conn.close();
     }
 }

JDBC API Details: Statement

package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language operations database (JDBC API details: Statement)
  */
 public class JDBCTest4_Statement {
     public static void main(String[] args) throws Exception {
         // testDML();
         testDDL();
     }
 
     /**
      * Execute DML statement
      *
      * @throws Exception
      */
     public static void testDML() throws Exception {
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // 3. Define a SQL statement
         String sql1 = "update account set money = 3000 where id = 1";
         // 4. Get the object that executes sql, Statement
         Statement stmt = conn.createStatement();
 
         // 5. Execute sql
         int count1 = stmt.executeUpdate(sql1);  // Number of rows affected after DML execution
         // 6. Processing results
         if (count1 > 0) {
             System.out.println("Successful modification~");
         } else {
             System.out.println("Modification failed~");
         }
 
         // 7. Release Resources
         stmt.close();
         conn.close();
     }
 
     /**
      * Execute DDL statement
      *
      * @throws Exception
      */
     public static void testDDL() throws Exception {
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         String sql1 = "create databases javadb";
         Statement stmt = conn.createStatement();
         // 5. Execute sql
         int count1 = stmt.executeUpdate(sql1);  // Number of rows affected after DDL execution
         // 6. Processing results
         if (count1 > 0) {
             System.out.println("Successful modification~");
         } else {
             System.out.println("Modification failed~");
         }
 
         // 7. Release Resources
         stmt.close();
         conn.close();
     }
 }

JDBC API Details: ResultSet

package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC API Details: ResultSet)
  */
 public class JDBCTest5_ResultSet {
     public static void main(String[] args) throws Exception {
         // testDML();
         testResult();
     }
 
     /**
      * Execute Result Statement DQL Query Statement
      *
      * @throws Exception
      */
     public static void testResult() throws Exception {
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         String sql = "select * from account";
 
         Statement stmt = conn.createStatement();
 
         ResultSet rs = stmt.executeQuery(sql);
         // Traversal result
         // Move the cursor down one row and determine if the current row has data
         while (rs.next()) {
             // get data
             int id = rs.getInt(1);  // Writable name id
             String name = rs.getString(2);  // name
             double money = rs.getDouble(3); // money
             System.out.println(id);
             System.out.println(name);
             System.out.println(money);
 
             System.out.println("-----------------------");
         }
         // Release Resources
         rs.close();
         stmt.close();
         conn.close();
     }
 }

JDBC API Details: PrepareStation

Effect
- Preventing sql injection
sql inject
- Is to modify pre-defined sql statements by manipulating input to achieve the method of executing code to cock the server.

-- normal
 select * from tb_user where username = "zhangsan" and password="123";
 -- ' or '1' = '1
 select * from tb_user where username='fafafadfaf' and password ='' or '1' = '1'
 -- How to prevent sql injection
 -- Is to escape sensitive characters
 -- String url = "jdbc:mysql:///java? UseSSL=false&useServerPrepStmts=true;//useServerPrepStmts=true to turn on precompilation

 // sql injection case
 package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC API Details: SQL Injection)
  */
 public class JDBCTest6_UserLogin {
     public static void main(String[] args) throws Exception {
         testUserLogin();
         testLogin_Inject();
     }
 
     /**
      * User Login
      *
      * @throws Exception
      */
     public static void testUserLogin() throws Exception {
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // Receive input username and password
         String name = "laity";
         String pwd = "123";
         String sql = "select * from tb_user where username='" + name + "' and password ='" + pwd + "'";
 
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql);
         // Determine whether the login was successful or not
         if (rs.next()) {
             System.out.println("Login Successful");
         } else {
             System.out.println("Logon Failure");
         }
         // Release Resources
         rs.close();
         stmt.close();
         conn.close();
     }
 
     /**
      * User Logon Injection
      *
      * @throws Exception
      */
     public static void testLogin_Inject() throws Exception {
         String url = "jdbc:mysql:///java?useSSL=false";
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // Receive input username and password
         String name = "fafafadfaf";
         String pwd = "' or '1' = '1";
         String sql = "select * from tb_user where username='" + name + "' and password ='" + pwd + "'";
 
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql);
         // Determine whether the login was successful or not
         if (rs.next()) {
             System.out.println("Login Successful");
         } else {
             System.out.println("Logon Failure");
         }
         // Release Resources
         rs.close();
         stmt.close();
         conn.close();
     }
 }

 // Resolving sql injection through prepareStaement
 package com.SqlJDBC.JDBCdemo1;
 
 import java.sql.*;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.JDBCdemo1
  * @date Date : 2021 December 10, 14:05
  * @Description: Java Language Operations Database (JDBC API Details: SQL Injection)
  */
 public class JDBCTest7_ProparedStatement {
     public static void main(String[] args) throws Exception {
         testLogin_Inject();
     }
     /**
      * User Logon Injection
      *
      * @throws Exception
      */
     public static void testLogin_Inject() throws Exception {
         String url = "jdbc:mysql:///java?UseSSL=false&useServerPrepStmts=true;// UseServerPrepStmts=true to turn on precompilation
         String username = "root";
         String password = "wang9264";
         Connection conn = DriverManager.getConnection(url, username, password);
         // Receive input username and password
         String name = "fafafadfaf";
         String pwd = "' or '1' = '1";
         String sql = "select * from tb_user where username= ? and password = ? ";
         PreparedStatement pstmt = conn.prepareStatement(sql);
         pstmt.setString(1, name);
         pstmt.setString(2, pwd);
 
         System.out.println(sql);
         ResultSet rs = pstmt.executeQuery();
         // Determine whether the login was successful or not
         if (rs.next()) {
             System.out.println("Login Successful");
         } else {
             System.out.println("Logon Failure");
         }
         // Release Resources
         rs.close();
         pstmt.close();
         conn.close();
     }
 }

2.2. 4 Database Connection Pool

Concept: A container responsible for distributing and managing database connections

benefit
- resource reuse
- Increase system response speed
- Avoid database connection vulnerabilities

Database Connection Pool Implementation

Standard interface: DataSource
- The official (SUN) database connection pool standard interface, implemented by a third-party organization.
- Function: Get Connections

 Connection getConnection();

Common database connection pools:
- DBCP
- C3P0
- Druid
Druid (Druid)
- Druid Connection Pool is an open source database connection pool project for Alibaba.
- Powerful and excellent performance, it is one of the best database connection pools in the Java language.

Druid uses

Download (1.1.12)
- Ali Warehouse Download: https://mvnrepository.com/artifact/com.alibaba/druid/1.1.12
Configuration of Druid Profile
- After importing the jar package, copy the configuration file to the src folder under the project
Once copied, double-click Open Configuration File to modify the parameters inside it.
Basic Profile

 driverClassName=com.mysql.jdbc.Driver
 url=jdbc:mysql:///java?useSSL=false&useServerPrepStmts=true
 username=root
 password=wang9264
 # Number of Initial Connections
 initialSize=5
 # Maximum number of connections
 maxActive=10
 # Maximum wait time ms
 maxWait=3000

Refer to my blog Druid Detailed Configuration

 package com.SqlJDBC.druid;
 
 import com.alibaba.druid.pool.DruidDataSourceFactory;
 
 import javax.sql.DataSource;
 import java.io.FileInputStream;
 import java.sql.Connection;
 import java.util.Properties;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.SqlJDBC.druid
  * @date Date : 2021 December 10 21:59
  * @Description: Druid Use of database connection pool
  */
 public class DruidDemo {
     public static void main(String[] args) throws Exception {
         System.out.println(System.getProperty("user.dir"));  // Print the current path
         // 1. Importing jar packages
 
         // 2. Define the configuration file
 
         // 3. Load Profile
         Properties prop = new Properties();
         prop.load(new FileInputStream("FrontEndDirection/src/druid.properties"));
 
         // 4. Get Connection Pool Objects
         DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
 
         // 5. Get Database Connection
         Connection connection = dataSource.getConnection();
         System.out.println(connection);
     }
 }

Note: In entity classes, the basic data type suggests its corresponding packaging type

2.3 Maven

Maven is a tool for managing and building Java projects. Its main functions are:
- Provide a standardized set of project structures (eclipse, idea common code)
- Provides a standardized set of building processes (compilation, testing, packaging, publishing...)
- Provide a set of dependency management mechanisms (jar packages, plug-ins, etc.)

2.3.1 Maven Installation Configuration

Note: When coordinates are used in a project to introduce a corresponding dependent jar package, the first step is to find out if there is a corresponding jar package in the local warehouse:
- If so, reference between projects;
- If not, the corresponding jar package will be downloaded from the central warehouse to the local warehouse.
Download warehouse address: maven Download
Configure environment variables:

Next set in path:

Then run: mvn-version When the following appears, the environment variable is successfully configured

Configure local repository: modify conf/settings. <localReponsitory>in XML is a specified directory

Configure Ali Cloud's Private Servers: Modify conf/settings. Add the <mirrors>tag to the XML with the following subtags: (Download fast)

 <mirror>
     <id>alimaven</id>
     <mirrorOf>central</mirrorOf>
     <name>aliyun maven</name>
     <url>http://maven.aliyun.com/nexus/content/groups/public</url>
 </mirror>

2.3. Basic use of 2 Maven

2.3. 2.1 Common Commands for Maven

Compile: compile
Clean: clean
Test: test
Package: package (generate jar package)
- Windows java-jar XXX Run
- liunx nohup java -jar xxx.jar &
- If you want to stop jar running, ps-ef looks at the process (or grep if there are many), finds the corresponding java process, and kill s it.
- jar xvf test.jar unpacking
Install: install (store jar packages in the local repository)

2.3.2.2 Maven's life cycle

The Maven build project life cycle describes how many events a build process has gone through
Maven's life cycle for project building is three sets
- clean: cleaning up
- default: core work such as compilation, testing, packaging, installation, etc
- site: generate reports, publish sites, etc.
During the same life cycle, the following commands are executed, and all previous commands are executed automatically (complie->clean->test->package->install)

2.3.2.3 IDEA Configuration Maven Environment

Select File in IDEA --> Settings
Search for maven
Set IDEA to use locally installed Maven and modify profile path

2.3.2.4 Maven coordinate details

Coordinates in Maven are unique identifiers of resources
Use coordinates to define the project or introduce the dependencies needed in the project
The main components of Maven coordinates
- groupld: Defines the name of the organization under which the current Maven project belongs (usually a reversal of the domain name, for example: com.itlaity)
- artifactld: Defines the name of the current Maven project (usually the name of the module, for example: order-server, goods-server)
- Version: Define the version number of the current project

2.3. 2.5 Create a Maven Project

Reference resources: IntelliJ IDEA 15 Create maven project - Tom1997 - Blog Park

2.3.2.5 IDEA Import Maven Project

2.3.2.6 Maven Plugin

Configure the Maven-Helper plug-in

Select File ->Settings in IDEA
Select Plugins
Search for Maven, now the first Maven Helper, click Install to install
Restart IDEA

2.3. 3 Dependency Management

Importing jar packages using coordinates

In pom. Writing <dependencies>tags in XML
Use <dependency>to introduce coordinates in the <dependencies>tag
groupld, artifatld, version defining coordinates
Click the refresh button to make the coordinates take effect.

     <!--Import mysql drive jar package-->
     <dependencies>
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.48</version>
         </dependency>
 
         <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
         <dependency>
             <groupId>com.alibaba</groupId>
             <artifactId>druid</artifactId>
             <version>1.1.12</version>
         </dependency>
     </dependencies>

2.4 MyBatis

Mybatis is an excellent persistence layer framework for simplifying JDBC development by supporting customized SQL, stored procedures, and advanced mapping.
- The persistence layer is the layer of code responsible for saving data to the database.
- JavaEE three-tier architecture: presentation, business, and persistence.
- Presentation layer: Page display.
- Business layer: Used for logical processing.
MyBatis was originally one of apache's Open Source Project iBatis, 2010 This project Migrated from apache software foundation to google code And renamed MyBatis. Migration to November 2013 Github.
English Web
Chinese net
mybatis-spring

2.4.1 MyBatis Quick Start

pom.xml content

 <?xml version="1.0" encoding="UTF-8"?>
 <project xmlns="http://maven.apache.org/POM/4.0.0"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
 
 
     <!--Coordinates of the current item-->
     <groupId>com.itlaity</groupId>
     <artifactId>maven-demo</artifactId>
     <version>1.0-SNAPSHOT</version>
 
     <properties>
         <maven.compiler.source>8</maven.compiler.source>
         <maven.compiler.target>8</maven.compiler.target>
     </properties>
 
     <!--Import mysql drive jar package-->
     <dependencies>
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.48</version>
         </dependency>
 
         <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
         <dependency>
             <groupId>com.alibaba</groupId>
             <artifactId>druid</artifactId>
             <version>1.1.12</version>
         </dependency>
         <!--Coordinates for unit tests-->
         <dependency>
             <groupId>junit</groupId>
             <artifactId>junit</artifactId>
             <version>4.13</version>
             <scope>test</scope>
         </dependency>
         <!-- mybatis rely on -->
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
             <version>3.5.5</version>
         </dependency>
 
         <!-- Add log support -->
         <!-- log4j Logging System -->
         <dependency>
             <groupId>log4j</groupId>
             <artifactId>log4j</artifactId>
             <version>1.2.17</version>
         </dependency>
         <!-- simple logg facade for java(slf4-api.jar)Log interface and log4j Adapters between specific log systems -->
         <dependency>
             <groupId>org.slf4j</groupId>
             <artifactId>slf4j-api</artifactId>
             <version>1.7.20</version>
         </dependency>
         <!-- Add to logback-classic rely on -->
         <dependency>
             <groupId>ch.qos.logback</groupId>
             <artifactId>logback-classic</artifactId>
             <version>1.2.3</version>
         </dependency>
 
         <!-- install logback-core rely on-->
         <dependency>
             <groupId>ch.qos.logback</groupId>
             <artifactId>logback-core</artifactId>
             <version>1.2.3</version>
         </dependency>
     </dependencies>
 </project>

Log profile logback placed in src/main/resources/ XML

 <?xml version="1.0" encoding="UTF-8"?>
 <!-- Level from high to low OFF , FATAL , ERROR , WARN , INFO , DEBUG , TRACE , ALL -->
 <!-- Log Output Rules Based on Current ROOT Level, higher than log output root Output at default level -->
 <!-- For each of the following configurations filter Is to filter out the output file, there will be high-level files, still low-level log information, through filter Filter logs at this level only -->
 <!-- scan When this property is set to true When the configuration file changes, it will be reloaded, and the default value is true.  -->
 <!-- scanPeriod Set a time interval to monitor whether the configuration file has been modified or, if no time unit is given, the default unit is milliseconds. When scan by true This property takes effect when. The default time interval is 1 minute. -->
 <!-- debug When this property is set to true Will print out logback Internal log information, real-time viewing logback Running state. Default value is false.  -->
 <configuration scan="true" scanPeriod="60 seconds"
     debug="false">
     <!-- Dynamic Log Level -->
     <jmxConfigurator />
     <!-- Define log file output location -->
     <property name="log_dir" value="../logs" />
     <!-- <property name="log_dir" value="/home/data/logs/src" /> -->
     <!-- Maximum Log History 30 Days -->
     <property name="maxHistory" value="30" />
     <!-- ConsoleAppender Console Output Log -->
     <appender name="console"    class="ch.qos.logback.core.ConsoleAppender">
         <encoder>
             <pattern>
                 <!-- Format Log Output -->
                 [%d{yyyy-MM-dd HH:mm:ss.SSS}][%logger:%line]%-5level -- %msg%n
             </pattern>
         </encoder>
     </appender>
 
     <logger name="com.itlaity" level="DEBUG" additivity="false">
         <appender-ref ref="console"/>
     </logger>
 
     <!-- Scroll to record files, log to specified files first, and log to other files when a condition is met RollingFileAppender -->
     <appender name="file"   class="ch.qos.logback.core.rolling.RollingFileAppender">
         <!-- Filter, record only WARN Level Log -->
         <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
             <level>ERROR,INFO,WARN,DEBUG</level>
         </filter>
         <!-- The most commonly used scrolling strategy, which is based on time.Responsible for both scrolling and starting scrolling -->
         <rollingPolicy  class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
             <!--Log output locations can be relative, and absolute -->
             <fileNamePattern>
                 ${log_dir}/%d{yyyy-MM-dd}/logback.log
             </fileNamePattern>
             <maxHistory>${maxHistory}</maxHistory>
         </rollingPolicy>
         <encoder>
             <pattern>
                 <!-- Format Log Output -->
                 [%d{yyyy-MM-dd HH:mm:ss.SSS}][%logger:%line]%-5level -- %msg%n
             </pattern>
         </encoder>
     </appender>
     <root>
         <!-- Print TRACE Level log and above -->
         <level value="DEBUG" />
         <!-- console output -->
         <appender-ref ref="console" />
         <!-- File Output -->
         <appender-ref ref="file" />
     </root>
 </configuration>

Color log bogback. XML (Understanding)

 <?xml version="1.0" encoding="UTF-8"?>
 <configuration scan="true" scanPeriod="60 seconds" debug="false">
     <property name="APP_NAME" value="MY_APP_NAME" />
     <property name="LOG_DIR" value="logs" />
     <property name="FILE_LOG_PATTERN" value="%d{yyyy-MM-dd HH:mm:ss.SSS}  %-5level [%thread] %logger{15} - %msg%n" />
     <!-- Color Log Format -->
     <property name="CONSOLE_LOG_PATTERN" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %highlight(%-5level) %boldYellow([%thread])  %cyan(%logger{15}) %msg%n"/>
  
     <contextName>${APP_NAME}</contextName>
  
     <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
         <encoder>
             <pattern>${CONSOLE_LOG_PATTERN}</pattern>
         </encoder>
     </appender>
  
     <appender name="FILE" class="ch.qos.logback.core.FileAppender">
         <file>${LOG_DIR}/logFile.log</file>
         <append>true</append>
         <encoder>
             <pattern>${FILE_LOG_PATTERN}</pattern>
         </encoder>
     </appender>
  
     <appender name="RollingFile" class="ch.qos.logback.core.rolling.RollingFileAppender">
         <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
             <fileNamePattern>${LOG_DIR}/dayLogFile.%d{yyyy-MM-dd}.log</fileNamePattern>
             <maxHistory>30</maxHistory>
         </rollingPolicy>
         <encoder>
             <pattern>${FILE_LOG_PATTERN}</pattern>
         </encoder>
     </appender>
  
     <!-- Use root Of appender-ref -->
     <logger name="com.example.Logger1" level="DEBUG" additivity="true">
     </logger>
  
     <!-- Do not use root Of appender-ref -->
     <logger name="com.example.Logger2" level="DEBUG" additivity="false">
     </logger>
  
     <logger name="com.example.Logger3" level="DEBUG" additivity="false">
         <appender-ref ref="STDOUT"/>
     </logger>
  
     <root level="DEBUG">
         <appender-ref ref="STDOUT" />
         <appender-ref ref="FILE" />
         <appender-ref ref="RollingFile" />
     </root>
 </configuration>

Mybatis-config to src/main/resources/ XML

 <?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">
                 <!--Database Connection Information-->
                 <property name="driver" value="com.mysql.jdbc.Driver"/>
                 <property name="url" value="jdbc:mysql:///java?useSSL=false"/>
                 <property name="username" value="root"/>
                 <property name="password" value="wang9264"/>
             </dataSource>
         </environment>
     </environments>
     <mappers>
         <!--Load sql Map File Build-in-->
         <mapper resource="UserMapper.xml"/>
     </mappers>
 </configuration>

Mapped SQL statement file to src/main/resources/

Note that the file name must be identifiable, for example, by manipulating UserMapper on a user table. Xml, etc

 <?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:Namespace
 resultType:The package name corresponding to the one you created
 id: Unique identity cannot be duplicated
 -->
 <mapper namespace="test">
     <select id="selectAll" resultType="com.itlaity.pojo.User">
         select * from mybatis_user;
     </select>
 </mapper>

Start writing the code src\main\java\comitlaity\pojo\User. Java

 package com.itlaity.pojo;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.itlaity
  * @date Date : 2021 December 12 1:37
  * @Description: Entity Class
  */
 
 // Press and hold atl+left mouse button to edit whole column
 public class User {
     private Integer id;
     private String username;
     private String password;
     private char gender;
     private String addr;
 
     // Add get and set methods
 
     /**
      * In java, to ensure data security,
      * We define data as private, encapsulated, etc.
      * set() and get methods or constructor methods are used if you want to call them.
      * The first method, set() and get(), is described here.
      */
     public Integer getId() {
         return id;
     }
 
     public void setId(Integer id) {
         this.id = id;
     }
 
     public String getUsername() {
         return username;
     }
 
     public void setUsername(String username) {
         this.username = username;
     }
 
     public String getPassword() {
         return password;
     }
 
     public void setPassword(String password) {
         this.password = password;
     }
 
     public char getGender() {
         return gender;
     }
 
     public void setGender(char gender) {
         this.gender = gender;
     }
 
     public String getAddr() {
         return addr;
     }
 
     public void setAddr(String addr) {
         this.addr = addr;
     }
 
     @Override
     public String toString() {
         return "User{" +
                 "id=" + id +
                 ", username='" + username + '\'' +
                 ", password='" + password + '\'' +
                 ", gender=" + gender +
                 ", addr='" + addr + '\'' +
                 '}';
     }
 }

Test class src\main\javacomitlaity\MyBatisDemo. Java

 package com.itlaity;
 
 import com.itlaity.pojo.User;
 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.InputStream;
 import java.util.List;
 
 /**
  * @author : Laity
  * @Project: JavaLaity
  * @Package com.itlaity
  * @date Date : 2021 December 12 1:55
  * @Description: Test Class
  */
 public class MyBatisDemo {
     public static void main(String[] args) throws Exception {
         // 1. Load the core configuration file of mybatis to get objects of the SqlSessionFactory class
         String resource = "mybatis-config.xml";
         // Return Byte Input Stream
         InputStream inputStream = Resources.getResourceAsStream(resource);
         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 
         // 2. Get SqlSession object to execute sql statement
         SqlSession sqlSession = sqlSessionFactory.openSession();
 
         // 3. Execute sql
         List<User> users = sqlSession.selectList("test.selectAll");
 
         System.out.println(users);
 
         // Release Resources
         sqlSession.close();
     }
 }

If you think it's troublesome

Classmate I give you a sentence: today is wine, today is drunk. Come tomorrow, come tomorrow.

2.4.2 Mapper Agent Development

Resolve hard coding in native methods
Simplify late execution of SQL

Set the namespace of the SQL mapping file

Define a mapper interface with the same name as the sql mapping file and place the Mapper interface and the sql mapping file in the same directory (create directory for/create)

Property names Mapper interface permissions
Define a method in the mapper interface whose name is the id of the SQL statement in the SQL mapping file and keeps the parameter type and return value type identical
- ```
 List<User> selectAll();
```
Code
- Getting the proxy object of the Mapper interface through the getMapper method of SqlSession
- Call corresponding method to complete sql execution

 <?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:Namespace
 resultType:The package name corresponding to the one you created
 id: Unique identity cannot be duplicated
 -->
 <!--Set up SQL Mapping file namespace Attribute is Mapper Interface Permission Naming-->
 <mapper namespace="com.itlaity.mapper.UserMapper">
     <select id="selectAll" resultType="com.itlaity.pojo.User">
         select *
         from mybatis_user;
     </select>
 </mapper>

2.4. 3 Special Character Processing

 // In writing sql statements
 parameter placeholder
     1.#{} : implement sql When the#{} placeholder replaced by?, Set parameter values automatically in the future
     2.${} : Pinyin sql，Will exist sql Injection problem.
 
 parameterType: 
     Used to set the parameter type, which can be omitted.
     
 < What can I do if I can't write it?
     1.Escape Character
     2.CDATA area

2.4. 4 Comments Complete Additions and Deletions

insert
- Note the default open transaction: transaction sqlSession needs to be committed manually. Commit();
- Or add a parameter true when getting SqlSession
- Primary key return: After data is added successfully, you need to get the value of the primary key inserted into the database
- You only need to participate in two attributes in the sql statement: useGeneratedKeys and KeyProperty
- Return result void
Modify (updata)
- You've already learned how to modify the JDBC above all fields
- Modify dynamic fields to write dynamic SQL statements
  - <set><if></if> where id = #{id}</set>

Delete (delete)

Delete one
- delete from table where id = #{id}

Delete Multiple

The void deleteByids(@Param("ids") int[] ids) /@Param("ids") annotation changes the name of the default key for the map collection

Delete front table where id in <foreach collection="ids" item="id" separator=","open="("close=")> #{id} </foreach>

Conclusion: Most parameters are encapsulated in a Map collection, and the @Param annotation can be used to replace the default arg key name, param, in a Map collection.

2.4. 5 Dynamic SQL

 // In writing sql statements (multi-conditional queries)
 dynamic sql(Search adds a parameter that requires three parameters, but the user enters only one parameter)
     1.We need to make a conditional judgment.
     2.<if test="parameter != null and parameter!= '' "> Execution Requirement Parameters </if>
     3.problem: The first condition does not require a logical operator
         1. Solution: Identity 1=1/ <where> replace where Keyword
 // Single Conditional Query
 <choose> <!--Amount to switch--> 
     <when> <!--Amount to case-->
     </when>
     <otherwise><!--default-->
         1 = 1    
     </otherwise>
 </choose>

2.4. 6 Note Development

Query: @Select
Add: @Insert
Modify: @Updata
Delete: @Delete
Tip: Note completes simple functions, profile completes complex functions.

 // Write in maaper interface
 @select("sql Sentence")
 User selectById();
 // UserMapper. You don't need to write in XML

Follow JavaEE Beginner Level Complete Tutorial 1--Beginners must see

Follow JavaEE Beginner Level 2--Beginners must see

Follow JavaEE Beginner Level 3--Beginners must see

After the J avaWeb finishes learning the basics of HTML, which was published before Article

Or look at my other Blog

Keywords: Java MySQL JavaEE Maven Mybatis

Added by Big_Rog on Sun, 12 Dec 2021 19:17:01 +0200

Programming VIP