Preface

I don't know if you have written JDBC in your work. I went to a small company as an intern in my junior year. JDBC is used in it, but it encapsulates several tool classes. When writing code, you can still feel "this is real JDBC code"

Now the development is generally Mybatis, and there are Hibernate or Spring Data JPA used by the company. Many times, different projects are developed by different programmers, and the technology may not be completely unified at the company level. It is possible to use Mybatis and Hibernate for one project.

No matter what ORM framework is used, it encapsulates a layer on JDBC, so JDBC still needs to learn.

Object > relative datebase mapping, which establishes some mapping between Java objects and relational databases to realize direct access to Java objects.

Many students don't know how much JDBC needs to learn. Here I'll talk about the knowledge points of JDBC and what should be mastered.

JDBC Basics

What is JDBC? The full name of JDBC is Java Data Base Connectivity, which is a Java API that can execute SQL statements

Each kind of database has its own graphical interface. I can operate and execute database related matters in it. Why should we use JDBC?

• Who uses the data in the database? For the program. We use Java programming language, so we need to use Java program to link database to access data.
• There are many databases on the market. Originally, we need to learn different APIs according to different databases. In order to simplify this operation, sun company has defined the JDBC API [interface]. For us, the operation database is on the JDBC API [interface], using different databases as long as the database driver provided by the database manufacturer is used.

In fact, you can take a good look at JDBC and define the interface. Anyway, you can implement it for me. No matter how the database changes, it is the same set of API when you use it

Then we will briefly learn these interfaces: Connection, Statement, and ResultSet. Write the code that Xiaobai must learn to connect to the database in Java:

1. Import MySQL or Oracle driver package
2. Load database driver
3. Get connection to database
4. Get objects that can execute SQL statements
5. Execute SQL statement
6. Close connection

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {

    /*
    * There are two ways to load a driver
    *
    * 1: It will cause the driver to register twice, over rely on mysql api, and break away from mysql development package, the program cannot be compiled
    * 2: The driver can only be loaded once, and does not need to rely on specific drivers, so it is highly flexible
    *
    * We usually use the second way
    * */

    //1.
    //DriverManager.registerDriver(new com.mysql.jdbc.Driver());

    //2.
    Class.forName("com.mysql.jdbc.Driver");

    //Get the object connected to the database - Connetcion
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhongfucheng", "root", "root");

    //Get the statement object to execute the sql statement
    statement = connection.createStatement();

    //Execute sql statement to get result set
    resultSet = statement.executeQuery("SELECT * FROM users");

    //Traverse the result set to get the data
    while (resultSet.next()) {

        System.out.println(resultSet.getString(1));

        System.out.println(resultSet.getString(2));
    }

} catch (SQLException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} finally {

    /*
    * Close the resource and close the call first.
    *
    * Before closing, judge whether the object exists
    * */

    if (resultSet != null) {
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

After the basic process is completed, we will focus on the difference between the PreparedStatement interface and the Statement interface, and why to use PreparedStatement.

1. When Statement objects compile SQL statements, if there are variables in SQL statements, they need to be separated by separators. If there are many variables, SQL will become very complex. PreparedStatement can use placeholders to simplify SQL writing
2. Statement compiles SQL frequently. PreparedStatement can precompile SQL to improve efficiency. Precompiled SQL is stored in PreparedStatement object
3. PreparedStatement prevents SQL injection. (Statement uses the separator '+ +' to write the permanent equation, which can enter the database without password.)

Database connection pool

Why do we use database connection pooling? The establishment and closing of database connection is very resource consuming. Frequent opening and closing of connection results in poor system performance

Common database connection pools include C3P0, DBCP and Druid. You can use druid when studying. I once wrote a Demo with C3P0 and was dis (:

Druid is an open source project of Alibaba. It has Chinese documents. I believe it will not be too difficult to learn to connect to the database. GitHub can find "druid"

paging

When it comes to pagination, interview and work are very common. They are skills that must be mastered. Let's briefly talk about how Oracle and MySQL implement paging, and the corresponding explanation:

Oracle paging:

/*
      Oracle Pagination syntax:
        @lineSize---Number of data rows per page
        @currentPage----Current page

    */
    SELECT *FROM (
        SELECT Column names,Column names,ROWNUM rn
        FROM Table name
        WHERE ROWNUM<=(currentPage*lineSize)) temp
    WHERE temp.rn>(currentPage-1)*lineSize;

/*
Oracle Paging:
        Oracle ROWNUM is mainly used to generate row numbers.

      Pagination principle:
        1: Subquery finds the data of the first n rows, and ROWNUM generates the row number of the first n rows
        2: Use subquery to generate row number of ROWNUM, and filter out the desired data through external filtering

      Example:
        Now I have to display 5 lines of data per page [lineSize=5], I want to query the data on page 2 [currentPage=2]
        Note: [with reference to grammar]

      Realization:
        1: Sub query finds the first 10 data [rownum < = 10]
        2: The next 5 pieces of data are screened out externally [rownum > 5]
        3: So we get the next five pieces of data
    */  

MySQL paging:

    /*
      Mysql Pagination syntax:
      @start---Offset. If it is not set, it starts from 0 (i.e. (currentPage-1)*lineSize)
      @length---Length, how many rows of data

    */
    SELECT *
    FROM Table name
    LIMIT [START], length;

    /*
      Example:
        I'm now specifying 5 rows per page. I want to query the data on page 2

      Analysis:
        1: The data on page 2 is actually the data from item 6, taking 5 items

      Realization:
        1: start Is 5 [offset from 0]
        2: length For 5

*/

Conclusion:

• Mysql starts from (currentPage-1)*lineSize and takes lineSize data
• First, Oracle obtains currentPagelineSize data, starting from (currentPage-1)lineSize

DBUtils

I think DBUtils is a good component. Before learning about Hibernate and Mybatis, you can learn to use them. It can greatly simplify our JDBC code and is very convenient to use.

If you are in a hurry to write your graduation project, you don't have time to learn ORM framework. Using this tool to write DAO data access layer, I think it is a good choice.

You can simply look at the code:

/*
* Using the DbUtils framework to CRUD the database
* Batch processing
*
* */
public class Test {

    @org.junit.Test
    public void add() throws SQLException {

        //Create QueryRunner object
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (id,name) VALUES(?,?)";

        //We found that some of the query() methods need to be passed in the Connection object, and some do not need to be passed in
        //Difference: when you pass in a Connection object, you need to destroy the Connection. If you don't, the program will help you put the Connection back into the Connection pool
        queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});

    }

    @org.junit.Test
    public void query()throws SQLException {

        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT * FROM student";

        List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
        System.out.println(list.size());

    }

    @org.junit.Test
    public void delete() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM student WHERE id='100'";

        queryRunner.update(sql);
    }

    @org.junit.Test
    public void update() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE student SET name=? WHERE id=?";

        queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});
    }

    @org.junit.Test
    public void batch() throws SQLException {
        //Create QueryRunner object
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (name,id) VALUES(?,?)";

        Object[][] objects = new Object[10][];
        for (int i = 0; i < 10; i++) {
            objects[i] = new Object[]{"aaa", i + 300};
        }
        queryRunner.batch(sql, objects);
    }
}

Dry cargo

Now that we have been working for a while, why write JDBC

• I am a person who pursues typesetting. If you pay attention to my classmates in the early stage, you may find that my GitHub and read.me of article navigation will be changed frequently. Current GitHub Navigation didn't work for me (it was too long), and the early articles, to be honest, didn't work very well, so I decided to start over.
• My article will be distributed to several platforms, but no one may read the article after it is distributed, and the drawing bed is likely to hang up because of the anti-theft chain of the platform. And because a lot of readers asked me, "can you turn your article into PDF?"? "
• I've written a lot of series level articles, and these articles will hardly change much, so they are very suitable for "persistence".

For the above reasons, I decided to summarize my series of articles into a PDF/HTML/WORD document. To be honest, it took me a lot of time to create such a document. In order to prevent whoring, I will pay attention to my official account reply "888".

The contents of the document are all hand beaten, and you can ask me directly if you don't understand anything. Official account has my contact information.

Open source project covering all knowledge points of Java back-end (6 K star already exists): https://github.com/ZhongFuCheng3y/3y

If you want to follow my updated articles and shared dry goods in real time, wechat search java 3Y.

The contents of PDF documents are all hand beaten. You can directly ask me if you don't know anything. Official account has my contact information.