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，不會往里面寫數(shù)據(jù)，因此沒有線程安全問題，可以作為一個全局變量?*/
????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!