JDBC and MyBatis from basics to practice

1. Introduction to JDBC

JDBC (Java Database Connectivity) is a Java database connection. It is an application program interface used in the Java language to standardize how client programs access the database, and provides methods such as querying and updating data in the database. It consists of a set of classes and interfaces written in the Java language, allowing Java programs to interact with various relational databases, such as MySQL, Oracle, SQL Server, etc. Through JDBC, developers can execute SQL statements in Java code, process database transactions, obtain query results, etc.

2. Use JDBC to query user information

Here is a simple example code for querying user information using JDBC:

import ;
import ;
import ;
import ;
import ;
public class JDBCUserQuery {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";
        try (Connection connection = (url, user, password);
             Statement statement = ();
             ResultSet resultSet = ("SELECT * FROM users")) {
            while (()) {
                int id = ("id");
                String username = ("username");
                String email = ("email");
                ("ID: " + id + ", Username: " + username + ", Email: " + email);
            }
        } catch (SQLException e) {
            ();
        }
    }
}

In the above code, first pass()Method to get the database connection and then createStatementObject is used to execute SQL statements and finally executeexecuteQuery()Method ObtainResultSetThe result set and traverse the result set to output user information.

3. ResultSet result set

ResultSetIt is an object in JDBC that stores database query results. It is like a table, each row represents a record and each column represents a field.ResultSetProvides a series of methods to access the data in it, such asgetInt()、getString()、getDouble()etc. Get the corresponding value according to the data type of the field. at the same time,ResultSetThere is a cursor that is located before the first line by default, throughnext()The method can move the cursor to the next row, and the data of the row can be obtained when the cursor points to a valid row. When the cursor moves to the end of the result set (i.e.next()Method returnfalse) means that the result set traversal ends.

4. Precompiled SQL - SQL injection problem

SQL injection is a common security vulnerability. Attackers insert malicious SQL code into input parameters to change the logic of the original SQL statement, obtain sensitive information or perform illegal operations. For example, in a scenario where user login verification, if you use normal SQL statements to splice user input:

String username = ("username");
String password = ("password");
String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If the user entersusernamefor' OR '1'='1, then the spliced SQL statement becomesSELECT * FROM users WHERE username = '' OR '1'='1' AND password = '', so that you can log in successfully regardless of whether the password is correct or not.

Precompiled SQL can effectively prevent SQL injection problems. Precompiled SQL is to send SQL statements to the database for compilation, and then pass parameters to the compiled statements. In JDBC, usePreparedStatementTo implement precompiled SQL:

String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String password = "password";
String username = ("username");
String password = ("password");
try (Connection connection = (url, user, password);
     PreparedStatement preparedStatement = ("SELECT * FROM users WHERE username =? AND password =?")) {
    (1, username);
    (2, password);
    ResultSet resultSet = ();
    // Process the result set} catch (SQLException e) {
    ();
}

In the above code, use?As a placeholder, then passsetString()Set parameters such as methods, so that the database will process the parameters as normal values without parsing them into SQL code, thus avoiding SQL injection problems.

5. Precompiled SQL - higher performance

In addition to preventing SQL injection, precompiled SQL also has higher performance. When using ordinaryStatementWhen executing SQL statements, the database needs to perform syntax parsing, query optimization and other operations on the SQL statements every time. Precompiled SQL will compile the SQL statement first. If SQL statements with the same structure are executed in the future, you only need to pass parameters. The database does not need to parse syntax and query optimization again, thereby improving execution efficiency. Especially in scenarios where SQL statements with the same structure need to be frequently executed, the performance advantages of precompiled SQL are more obvious.

6. JDBC add, delete and modify operations

The following is a sample code for adding, deleting and modifying operations using JDBC:

Insert data：

import ;
import ;
import ;
import ;
public class JDBCInsert {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";
        try (Connection connection = (url, user, password);
             PreparedStatement preparedStatement = ("INSERT INTO users (username, email, password) VALUES (?,?,?)")) {
            (1, "newuser");
            (2, "newuser@");
            (3, "newpassword");
            int rowsInserted = ();
            if (rowsInserted > 0) {
                ("A new user was inserted successfully!");
            }
        } catch (SQLException e) {
            ();
        }
    }
}

Update data：

import ;
import ;
import ;
import ;
public class JDBCUpdate {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";
        try (Connection connection = (url, user, password);
             PreparedStatement preparedStatement = ("UPDATE users SET password =? WHERE username =?")) {
            (1, "newpassword123");
            (2, "newuser");
            int rowsUpdated = ();
            if (rowsUpdated > 0) {
                ("User password was updated successfully!");
            }
        } catch (SQLException e) {
            ();
        }
    }
}

Delete data：

import ;
import ;
import ;
import ;
public class JDBCDelete {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";
        try (Connection connection = (url, user, password);
             PreparedStatement preparedStatement = ("DELETE FROM users WHERE username =?")) {
            (1, "newuser");
            int rowsDeleted = ();
            if (rowsDeleted > 0) {
                ("User was deleted successfully!");
            }
        } catch (SQLException e) {
            ();
        }
    }
}

7. Introduction to MyBatis

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manual setting parameters and obtaining result sets. It can use simple XML or annotations to configure and map native information, mapping interfaces and Java's POJOs (Plain Old Java Objects) into records in the database. MyBatis encapsulates JDBC, making database operations more concise and efficient, while also improving the maintainability and scalability of the code.

8. MyBatis Beginner Program

Here is a simple example of getting started with MyBatis:

Introduce dependencies：

On the Maven projectAdd MyBatis and database driver dependencies to the file:

<dependencies>
    <dependency>
        <groupId></groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.9</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.26</version>
    </dependency>
</dependencies>

Create entity class：

public class User {
    private int id;
    private String username;
    private String email;
    private String password;
    // Omit getters and setters}

Create SQL Mapping File ()：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-////DTD Mapper 3.0//EN"
        "/dtd/">
<mapper namespace="">
    <select  parameterType="int" resultType="">
        SELECT * FROM users WHERE id = #{id}
    </select>
</mapper>

Configure MyBatis core file ()：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-////DTD Config 3.0//EN"
        "/dtd/">
<configuration>
    <environments default="development">
        <environment >
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value=""/>
                <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
                <property name="username" value="root"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource=""/>
    </mappers>
</configuration>

Writing test code：

import ;
import ;
import ;
import ;
import ;
import ;
public class MyBatisTest {
    public static void main(String[] args) throws IOException {
        String resource = "";
        InputStream inputStream = (resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try (SqlSession sqlSession = ()) {
            UserMapper userMapper = ();
            User user = (1);
            (());
        }
    }
}

9. MyBatis auxiliary configuration - SQL prompts

In order to get better SQL prompts during the development process, you can use some IDE plug-ins, such as installing MyBatis Log Plugin in IntelliJ IDEA. These plug-ins can print out SQL statements executed by MyBatis and highlight and prompt the SQL syntax to facilitate developers to debug and optimize SQL statements. At the same time, it can also be used in SQL mapping files.Comment, explain the functions and logic of SQL statements, and improve the readability of the code.

10. MyBatis auxiliary configuration - log output

MyBatis supports configuring log output to better understand the execution of the program. Can be passed inConfigure a log factory in the file to implement it. For example, use Log4j as the log framework:

Introducing Log4j dependencies：

<dependency>
    <groupId>.log4j</groupId>
    <artifactId>log4j-api</artifactId>
    <version>2.14.1</version>
</dependency>
<dependency>
    <groupId>.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.14.1</version>
</dependency>

Configure Log4j configuration file ()

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:} [%t] %-5level %logger{36} - %msg%n"/>
        </Console>
    </Appenders>
    <Loggers>
        <Root level="error">
            <AppenderRef ref="Console"/>
        </Root>
        <Logger name="" level="debug"/>
    </Loggers>
</Configuration>

Enable logging in MyBatis configuration file：

&lt;configuration&gt;
    &lt;settings&gt;
        &lt;setting name="logImpl" value="LOG4J2"/&gt;
    &lt;/settings&gt;
    &lt;!-- Other configurations --&gt;
&lt;/configuration&gt;

In this way, the SQL statements and other information executed by MyBatis will be output according to the configured log level.

11. MyBatis database connection pool

MyBatis supports the use of different database connection pools, such asPOOLEDConnection pool (default),UNPOOLEDConnection pool, etc.POOLEDThe connection pool will manage a certain number of database connections. When a connection is needed, it will be retrieved from the connection pool and returned to the connection pool after use. This can avoid frequent creation and destruction of database connections and improve performance. existConfiguration in the filePOOLEDAn example of a connection pool is as follows:

<environment >
    <transactionManager type="JDBC"/>
    <dataSource type="POOLED">
        <property name="driver" value=""/>
        <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </dataSource>
</environment>

In addition, third-party connection pools can also be used, such as HikariCP, C3P0, etc., you only need to introduce corresponding dependencies into the project and configure them in the MyBatis configuration file.

12. MyBatis adds, deletes, modify and checks - deletes users

Here is an example of using MyBatis to delete a user:

Add delete statements in SQL map file()：

&lt;mapper namespace=""&gt;
    &lt;!-- Other statements --&gt;
    &lt;delete  parameterType="int"&gt;
        DELETE FROM users WHERE id = #{id}
    &lt;/delete&gt;
&lt;/mapper&gt;

Add the corresponding method in interface()：

public interface UserMapper {
    // Other methods    int deleteUserById(int id);
}

Writing test code：

import ;
import ;
import ;
import ;
import ;
import ;
public class MyBatisDeleteTest {
    public static void main(String[] args) throws IOException {
        String resource = "";
        InputStream inputStream = (resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try (SqlSession sqlSession = ()) {
            UserMapper userMapper = ();
            int rowsDeleted = (1);
            if (rowsDeleted > 0) {
                ("User was deleted successfully!");
            }
            ();
        }
    }
}

In the above code,UserMapperInterfacedeleteUserById()The method performs a deletion operation and finally calls()Method commit transaction.

Through the above detailed introduction and sample code of JDBC and MyBatis, I hope it can help readers better understand and master these two database operation techniques and choose the appropriate method for database development in actual projects.

This is the article about JDBC and MyBatis from basic to practical. For more detailed explanations about JDBC and MyBatis, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!