Key required knowledge points of JDBC interview

Keywords: Database MySQL SQL

JDBC core knowledge points

1. What is JDBC?

JDBC is a set of specifications proposed by SUN company, which provides unified access to a variety of relational databases.

2. What are the components of JDBC?

JDBC is composed of a set of interfaces proposed by SUN company and implementation classes (driver jars) provided by various database manufacturers.

3. What are the core API s of the JDBC specification?

java.sql.DriverManager (tool class)Manage the driver jars (implementation classes) provided by different database manufacturers
java.sql.Connection (Interface)A Connection object represents a Connection to a database
java.sql.Statement (Interface)Send the sql command to the database server for execution
java.sql.ResultSet (Interface)Encapsulates the result set returned from the database query

4. How to implement the JDBC core steps?

1. Load the drive.

Class.forName(com.mysql.cj.jdbc.Driver);

2. Create database Connection object Connection

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc / database? useUnicode=true&characterEncoding=UTF-8  & useSSL=false & serverTimezone=Asia/Shanghai",username="root",password="root)";

3. Create Statement object

String sql="insert into user values(username=?,password=?) ";
PreparedStatement pstm=conn.Preparstatement(sql);

4. Execute Sql

pstm.setUsername(1,"Wang Hengjie")；
pstm.setPassword(2,"123456");
pstm.executeUpdate();

5. Processing result set

ResultSet rs=pstm.executeQuery();
If it is a query
while(rs.next){
String username=rs.getString("username");
String password=rs.getString("password");
}

6. Release resources

rs.close();
pstm.close();
conn.close();

5. How is the PreparedStatement object used?

1. Create: PreparedStatement pstm = conn.prepareStatement(sql);

2. Assign a value to the placeholder in the Sql command: pstm.setXXX(index,value);

3. Execute Sql:

3.1 execute query statement: pstm.executeQuery();

3.2 execute addition, deletion and modification statements: pstm.executeUpdate();

6. What is the difference between a Statement and a PreparedStatement?

1. Parent child relationship: Statement is the parent interface of PreparedStatement.

2. Differences in Sql execution:

2.1 there is a risk of Sql injection when the statement executes Sql.

2.2 PreparedStatement can execute parameterized Sql, effectively avoiding the risk of Sql injection.

7. How is the result set handled?

The while loop returns the value of rs.next() boolean type to judge whether the next row of data exists

If true is returned, this row of data is returned and the pointer moves down one bit. If there is only one row of data, you can use the if condition to judge.

Get the specific content of data rs.getXXX();

8. Why encapsulate JDBC tool classes?

1. For the code that can complete the function independently in the program and will be used more than twice, it needs to be encapsulated.

2. For the frequently changed strings involved in the program, they need to be extracted into the configuration file for definition.

package com.tjcu.JDBCUtils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author Wang Hengjie
 * @version 1.0
 * @date 2021/9/8 20:43
 * @email 1078993387@qq.com
 * @Address Tianjin
 * @Description: JDBCUtils(Performance optimized version)
 */
public class JdbcUtils3 {
    //Static attribute, which is convenient for other static methods
//    Create a Properties object to load the configuration file

    private static Properties p = new Properties();
    //Static code block

    static {
        //Get the byte stream. The default path of the byte stream obtained by this method is the location of JDBC utils
        InputStream is = JdbcUtils3.class.getResourceAsStream("com/tjcu/config/jdbc.properties");
        //Load the configuration file and handle exceptions
        try {
            p.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
//                 Load driver
            Class.forName(p.getProperty("driverClassName"));
            conn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"), p.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection conn, PreparedStatement pstm) {

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


}

9. What is ORM encapsulation?

1. ORM object relational mapping is called object relational mapping. In short, ORM is equivalent to relay data. It encapsulates the data queried in the database table into JAVA objects to facilitate subsequent processing operations.

10. How to encapsulate entity classes?

1. A table in the database corresponds to an entity class in Java

2. The entity class needs to implement the serialization interface

3. The attribute of the entity class corresponds to the field (column) of the table

4. Provide getter/setter methods exposed by corresponding attributes

5. Provide parametric construction method and nonparametric construction method

11. What is DAO?

DAO layer is called data access object, which mainly encapsulates CRUD operations for database tables.

12. What are the development steps of DAO layer?

1. Create table

2. Encapsulate entity classes

3. Define DAO interface

4. Write DAO implementation class

13. What is Service?

The Service layer, also known as the business layer, is used to encapsulate business function operations.

14. What is transaction control?

A transaction is a series of transactions executed as a single logical unit of work operation Transaction processing ensures that data oriented resources will not be permanently updated unless all operations within the transactional unit are successfully completed.

1. The default transaction in JDBC is that a Sql statement becomes a transaction by itself, that is, the transaction will be submitted automatically after the execution of a Sql statement; the integrity of business functions cannot be guaranteed.

Require programmers to manually control transactions:

1.1 set manual control transaction: conn.setAutoCommit(false);

1.2 manually commit transactions: conn.rollback();

1.3 manual rollback transaction: conn.rollback();

15. What does ThreadLocal do?

ThreadLocal is a thread binding object, which is mainly used to bind an object to the current thread.

Ensure that objects bound through threads, the same objects are obtained in one thread, and different objects are obtained in different threads.

package com.tjcu.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author Wang Hengjie
 * @version 1.0
 * @date 2021/9/11 12:38
 * @email 1078993387@qq.com
 * @Address Tianjin
 * @Description:
 */
public class JdbcUtils {
    //    Static Properties collection, equivalent to Properties

    private static Properties p = new Properties();
    //    Static ThreadLocal input thread binding object

    private static final ThreadLocal<Connection> t = new ThreadLocal();


    static {
        InputStream is = JdbcUtils.class.getResourceAsStream("/com/tjcu/config/jdbc.properties");
        try {
            p.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        //Get Connection from ThreadLocal
        Connection conn = t.get();
        try {
            if (conn == null) {
                Class.forName(p.getProperty("driver"));
                conn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"), p.getProperty("password"));
                t.set(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return t.get();
    }

    public static void close(ResultSet rs, PreparedStatement pstm, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
                t.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }
    public static void close(ResultSet rs, PreparedStatement pstm) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }

    public static void close(PreparedStatement pstm, Connection conn) {
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
                //After closing the link, remember to use the remove method to remove the closed link object in ThreadLocal
                t.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }

    //Close resource is used to close the connection
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                //After closing the link, remember to use the remove method to remove the closed link object in ThreadLocal
                t.remove();
            } catch (Exception e) {
            }
        }
    }
}

16. Three tier architecture

1. The three-tier architecture is a programming idea, which logically divides the application program into three layers and divides it in line with the idea of performing their respective duties.

2. Which layers does the three-tier architecture include

2.1 DAO layer: data model layer

  • It is mainly responsible for interacting with the database and completing CRUD operation

  • Receive data from Service layer

2.2 Service(BIZ) layer: business logic layer

  • It mainly completes business function operations

  • The DAO layer is called one or more times

  • Transaction control is required

  • Receive the data transmitted by the view layer and perform corresponding operation processing

2.3 View layer: view layer (table display layer)

  • It mainly interacts with users

  • Receive user information

  • Display the functions completed by the Service and the returned data to the user

Posted by achild on Fri, 08 Oct 2021 11:12:19 -0700

Hot Keywords

©2024 Programmer Group