Detailed explanation of what is JDBC? How is JDBC used?

This article brings you a detailed explanation of what is JDBC? How is JDBC used? . It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

What is JDBC

JDBC (Java Database Connectivity), that is, Java database connection, is a Java API used to execute SQL statements , which can provide the same access to multiple relational databases. It consists of a set of classes and interfaces written in Java language. JDBC provides a baseline against which more advanced tools and interfaces can be built, enabling database developers to write database applications. All in all, JDBC does three things:

1. Establish a connection to the database

2. Send statements to operate the database

3. Processing Result

JDBC Simple Example

The following code demonstrates how to exploit JDBC queries several pieces of data that meet the requirements from the database, and the database used is MySql.

1. Create a database and a table. My habit is to create a .sql file under CLASSPATH to store sql statements

create database school;

use school;

create table student
(
    studentId            int                 primary key    auto_increment    not null,
    studentName        varchar(10)                                                            not null,
    studentAge        int,
    studentPhone    varchar(15)
)

insert into student values(null,'Betty', '20', '00000000');
insert into student values(null,'Jerry', '18', '11111111');
insert into student values(null,'Betty', '21', '22222222');
insert into student values(null,'Steve', '27', '33333333');
insert into student values(null,'James', '22', '44444444');
commit;

2. Create a .properties file for Stores several properties of the MySql connection. Why create .properties instead of hard-coding it in the code? Since this is not a classification of Java design patterns, I won’t go into details. Just remember: From a design perspective, write the content in the configuration It's always better to have it in a file than hard-coded in code.

mysqlpackage=com.mysql.jdbc.Driver
mysqlurl=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8
mysqlname=root
mysqlpassword=root

3. Create entity classes based on table fields

public class Student
{
    private int        studentId;
    private String    studentName;
    private int        studentAge;
    private String    studentPhone;
    
    public Student(int studentId, String studentName, int studentAge,
            String studentPhone)
    {
        this.studentId = studentId;
        this.studentName = studentName;
        this.studentAge = studentAge;
        this.studentPhone = studentPhone;
    }
    
    public int getStudentId()
    {
        return studentId;
    }

    public String getStudentName()
    {
        return studentName;
    }

    public int getStudentAge()
    {
        return studentAge;
    }

    public String getStudentPhone()
    {
        return studentPhone;
    }

    public String toString()
    {
        return "studentId = " + studentId + ", studentName = " + studentName + ", studentAge = " +
                studentAge + ", studentPhone = " + studentPhone;
    }
}

4. Write a DBConnection class specifically to provide external database connections. I use MySql here, so there is only one mysqlConnection. If Oracle is also used, of course, an oracleConnection can be provided externally. Some people may wonder whether there are thread safety issues in making these connections global. This is a good question. That's because we only read a PreparedStatement from the Connection and will not write it. Reading only without modification will not cause thread safety issues. In addition, setting the Connection to static ensures that there is only one copy of the Connection in the memory and will not occupy much resources. It will be fine if you do not call the close() method to close it after each use.

public class DBConnection
{    
    private static Properties properties = new Properties();
    
    static
    {
        /** 要从CLASSPATH下取.properties文件，因此要加"/" */
        InputStream is = DBConnection.class.getResourceAsStream("/db.properties");
        try
        {
            properties.load(is);
        } 
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
    
    /** 这个mysqlConnection只是为了用来从里面读一个PreparedStatement，不会往里面写数据，因此没有线程安全问题，可以作为一个全局变量 */
    public static Connection mysqlConnection = getConnection();
    
    public static Connection getConnection()
    {
        Connection con = null;
        try
        {
            Class.forName((String)properties.getProperty("mysqlpackage"));
            con = DriverManager.getConnection((String)properties.getProperty("mysqlurl"), 
                    (String)properties.getProperty("mysqlname"), 
                    (String)properties.getProperty("mysqlpassword"));
        } 
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } 
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return con;
    }
}

5. Create a tool class to write various methods specifically to interact with the database. It is best to make this kind of tool class a singleton, so that you don’t have to create new every time (in fact, I don’t see any benefits of new), and save resources

package com.xrq.test11;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentManager
{
    private static StudentManager instance = new StudentManager();
    
    private StudentManager()
    {
        
    }
    
    public static StudentManager getInstance()
    {
        return instance;
    }
    
    public List<Student> querySomeStudents(String studentName) throws Exception
    {
        List<Student> studentList = new ArrayList<Student>();
        Connection connection = DBConnection.mysqlConnection;
        PreparedStatement ps = connection.prepareStatement("select * from student where studentName = ?");
        ps.setString(1, studentName);
        ResultSet rs = ps.executeQuery();
        
        Student student = null;
        while (rs.next())
        {
            student = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
            studentList.add(student);
        }
        
        ps.close();
        rs.close();
        return studentList;
    }
}

6. Write a main Call the function

List<Student> studentList = StudentManager.getInstance().querySomeStudents("Betty");
for (Student student : studentList) {
    System.out.println(student);
}

7. Look at the running results. They are the same as those in the database. Success

studentId = 1, studentName = Betty, studentAge = 20, studentPhone = 00000000
studentId = 3, studentName = Betty, studentAge = 21, studentPhone = 22222222

Why use placeholders "?"

Look at point 5. You must have noticed that the "?" placeholder is used when writing SQL statements. Of course, there are factors to beautify the code. If you don't use placeholders, you must put them in parentheses. Write " " to splice parameters. If there are too many parameters to be spliced, the code will definitely not look good and the readability will not be strong. But in addition to this reason, there is another important reason, which is to avoid a security issue. Assuming that we do not use placeholders to write SQL statements, then the "querySomeStudents(String name) throws Exception" method should be written like this:

public List<Student> querySomeStudents(String studentName) throws Exception
{
    List<Student> studentList = new ArrayList<Student>();
    Connection connection = DBConnection.mysqlConnection;
    PreparedStatement ps = connection.prepareStatement("select * from student where studentName = '" + studentName + "'");
    ResultSet rs = ps.executeQuery();
        
    Student student = null;
    while (rs.next())
    {
        student = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
        studentList.add(student);
    }
        
    ps.close();
    rs.close();
    return studentList;
}

The above main function can also obtain two pieces of data, but here comes the problem. What if I call it like this:

public static void main(String[] args) throws Exception
    {
        List<Student> studentList = new ArrayList<Student>();
        studentList = StudentManager.getInstance().querySomeStudents("' or '1' = '1");
        for (Student student : studentList)
            System.out.println(student);
    }

Look at the running results:

studentId = 1, studentName = Betty, studentAge = 20, studentPhone = 00000000
studentId = 2, studentName = Jerry, studentAge = 18, studentPhone = 11111111
studentId = 3, studentName = Betty, studentAge = 21, studentPhone = 22222222
studentId = 4, studentName = Steve, studentAge = 27, studentPhone = 33333333
studentId = 5, studentName = James, studentAge = 22, studentPhone = 44444444

Why? Just look at the sql statement after splicing and you will know:

select * from student where studentName = '' or '1' = '1'

'1'='1' is always true, so the previous query conditions are useless. This kind of problem has application scenarios and is not just written casually. Java is used more and more on the Web. Since it is the Web, when querying, there is a situation where the user enters a condition, the query condition is obtained in the background, and the SQL statement is spliced ​​to query the database. Experienced users can enter a "' '' or '1' = '1", so you can get all the data in the library.

The relationship and difference between Statement and PreparedStatement.

Relationship: PreparedStatement inheritance Since Statement, both interfaces
Difference: PreparedStatement can use placeholders, is precompiled, and batch processing is more efficient than Statement

JDBCTransaction

What is a transaction: A transaction is a set of operations for a set of database operations. If a set of processing steps either all occur or none are performed, we call the reorganization process a transaction.

Basic characteristics of transactions: atomicity, consistency, isolation, and durability.

Atomicity: Atomicity means that a transaction is an indivisible unit of work, and all operations in the transaction either occur or none occur.

Consistency: Consistency means that the integrity constraints of the database are not violated before the transaction starts and after the transaction ends. This means that database transactions cannot destroy the integrity of relational data and the consistency of business logic.

If A transfers money to B, regardless of whether the transfer transaction operation is successful or not, the total deposits of the two will remain unchanged.

Isolation: When multiple transactions access concurrently, the transactions are isolated, and one transaction should not affect the running effects of other transactions.

In a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space . Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transactions. When a transaction views data updates, the state of the data is either the state before another transaction modified it, or the state after another transaction modified it. The transaction will not view the data in the intermediate state.

The most complex problems in transactions are caused by transaction isolation. Complete isolation is unrealistic. Complete isolation requires the database to only execute one transaction at a time, which will seriously affect performance.

Persistence: means that after the transaction is completed, the changes made by the transaction to the database will be persistently saved in the database and will not be recalled. roll.

Summary: The above is the entire content of this article, I hope it will be helpful to everyone's study. For more related tutorials, please visit Java video tutorial, java development graphic tutorial, bootstrap video tutorial!

The above is the detailed content of Detailed explanation of what is JDBC? How is JDBC used?. For more information, please follow other related articles on the PHP Chinese website!