Hello, today for you to share JDBC, quickly take out a small notebook to write down
1, the introduction of
JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It provides unified access to multiple relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark against which more advanced tools and interfaces can be built to enable database developers to write database applications
Java is robust, secure, easy to use, easy to understand, and automatically downloadable from the network, making it the master of database applications
Out of the language. All you need is a way for your Java application to talk to various databases.
JDBC can be used with Java on a variety of platforms, such as Windows, Mac OS, and various versions of UNIX.
The JDBC library includes apis for each of the tasks mentioned below that are generally related to database use.
Connect to the database.
Create SQL or MySQL statements.
Perform SQL or MySQL queries in the database.
View and modify generated records.
2. JDBC architecture
The JDBC API supports two – and three-tier processing models for database access, but typically, the JDBC architecture consists of two layers:
JDBC API: This provides application connectivity to the JDBC manager.
JDBC driver API: This supports JDBC manager-to-driver connections.
The JDBC API uses the driver manager and database-specific drivers to provide transparent connections to heterogeneous databases.
3. JDBC core components
DriverManager: This class manages a list of database drivers. Connection requests from Java applications are processed using communication subprotocols
Matches the appropriate database driver.
Driver: This interface handles communication with the database server, and we rarely interact directly with Driver objects. It USES
DriverManager object to manage objects of this type.
Connection: This interface has all the methods for contacting the database. The connection object represents the communication context, that is, with the database
All communication is through connection objects only.
Statement: Submits SQL statements to the database using objects created from this interface. In addition to executing stored procedures, some pies
The birth interface also accepts parameters.
ResultSet: After SQL queries are executed using Statement objects, these objects hold data retrieved from the database. It is a
Iterators that allow us to move its data.
SQLException: This class handles any errors that occur in the database application
4. Introduction to CRUD grammar
SQL is a standardized language that allows you to perform operations on a database, such as creating items, querying content, updating content, and deleting items.
Create, Read, Update, and Delete are commonly referred to as CRUD operations.
5. Use steps
Building a JDBC application involves the following six steps:
Import package: The package that contains the JDBC classes required for database programming is required. In most cases, using import java.sql.* is sufficient
.
Register the JDBC driver: You are required to initialize the driver so that you can open a communication channel with the database.
SQL> CREATE DATABASE DATABASE_NAME;
SQL> DROP DATABASE DATABASE_NAME;
SQL> CREATE TABLE Employees (
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);
SQL> DROP TABLE table_name;
SQL> INSERT INTO table_name VALUES (column1, column2…) ;
SQL> SELECT column_name, column_name,… FROM table_name WHERE conditions;
SQL> UPDATE table_name
SET column_name = value, column_name = value,…
WHERE conditions;
SQL> DELETE FROM table_name WHERE conditions;
Open the Connection, need to use the DriverManager. GetConnection () method to create a Connection object, the object table
Shows the physical connection to the database.
Execute a query: You need an object of type Statement to build and submit SQL statements to the database.
Extract data from the ResultSet: you need to retrieve data from the ResultSet using the corresponding resultset.getxxx () method.
Free resources: You need to explicitly shut down all database resources, independent of the JVM’s garbage collection.
6. JDBC connection steps
The programming involved in establishing a JDBC connection is fairly simple. It’s a simple four-step process
Import JDBC packages: Add import statements from the Java language to the Java code to import the required classes.
Register the JDBC driver: This step will cause the JVM to load the required driver implementation into memory so that it can satisfy your JDBC
The request.
Database URL configuration: This is to create a well-formed address pointing to the database to connect to.
Create a connection object: Finally, call the getConnection () method of the DriverManager object to establish the actual database connection
Pick up.
Class.forName();
The most common way to register a driver is to use Java’s class.forname () method to dynamically load the driver’s Class file into memory
And automatically register them
The second method is to use static DriverManager. RegisterDriver () method.
After load driver, you can use the DriverManager. GetConnection () method to establish the connection. And just for reference, let me
List three overloaded DriverManager. GetConnection () method
GetConnection (String URL)
GetConnection (String URL, Properties prop)
GetConnection (String URL, String user, String password)
| | an RDBMS JDBC driver name | | url format
|————|————————–|————-|
|MYSQL8| com.mysql.cj.jdbc.Driver| jdbc:mysql://hostname:3306/databaseName? serverTimezone=UTC|
MySQL |com.mysql.jdbc.Driver | jdbc:mysql://hostname:3306/databaseName |
Create a database connection object
Version 2:
jdbc:mysql://localhost:3306/yhp2? serverTimezone=UTC
Using the database URL and attribute object DriverManager. GetConnection () method of the third form requires a database URL and a Properties object
DriverManager.getConnection(String url, Properties info);
conn.close();
6.1 JDBC Executing SQL Statements
Once we have a connection, we can interact with the database. The JDBC Statement and PreparedStatement interfaces define enabling
Methods and properties that enable you to send SQL commands and receive data from the database.
6.2 the Statement
Create statement object
Before the Statement object can be used to execute the SQL Statement, it needs to be created using the createStatement () method of the Connection object
One, as shown in the following example:
Boolean execute (String SQL) : Returns a Boolean true if a ResultSet object can be retrieved; Otherwise the return
Return false. Use this method to execute SQL DDL statements or when you need to use true dynamic SQL.
Int executeUpdate (String SQL) : Returns the number of rows affected by the execution of the SQL statement. Using this method to perform the expected impact
Multiple row SQL statements, such as INSERT, UPDATE, or DELETE statements.
ResultSet executeQuery (String SQL) : Returns a ResultSet object. Enable when you want to obtain a result set
Using this method is just like using the SELECT statement.
Closing the Statement object
Just as we closed a Connection object to save database resources, we should close the Statement object for the same reason.
A simple call to the close () method will perform the job. If the Connection object is closed first, it also closes the Statement object. However, you should always explicitly close the Statement object to ensure proper cleanup.
6.3 SQL injection
By inserting SQL commands into Web form submissions or entering query strings for domain names or page requests, the server can be tricked
Run a malicious SQL command. Specifically, it takes advantage of an existing application and injects (malicious) SQL commands into a back-end database engine
The ability to execute by typing (malicious) SQL statements into a Web form to retrieve data from a compromised website
Library, rather than executing SQL statements as the designer intended. For example, many film and television websites leak VIP members password is mostly through
WEB forms are particularly vulnerable to SQL injection attacks when the submission query character bursts.
7. PreparedStatement(PreparedStatement)
The PreparedStatement interface extends the Statement interface, which provides you with two generic Statement objects
Plus features.
This statement allows you to supply parameters dynamically.
The setXXX() method binds a value to the parameter, where XXX represents the Java data type of the value to be bound to the input parameter. If you forget to provide a value, you will receive a SQLException.
Each parameter marker is referenced by its sequential position. The first marker represents position 1, the next position 2, and so on. This method, unlike Java array indexes, starts at 0.
Close the PreparedStatement object
Just as you closed the Statement object, you should close the PreparedStatement object for the same reason.
A simple call to the close () method will perform the job. If the Connection object is closed first, it will also be closed
A PreparedStatement object. However, the PreparedStatement object should always be explicitly closed to ensure proper cleanup.
Compare statement and PreparedStatement;
(1) Statement belongs to the state channel, and PreparedStatement belongs to the pre-state channel
(2) The pre-state channel compiles SQL statements before executing them, which is more efficient than statement execution
(3) Does the pre-status channel support placeholders? When assigning placeholders, the position starts at 1
(4) The pre-state channel can prevent SQL injection, because: the pre-state channel processes values as strings
8 the ResultSet.
A SELECT statement is a standard way to SELECT rows from a database and view them in the result set. The interface representation in java.sql.ResultSet
Result set database query.
The ResultSet object maintains a cursor pointing to the current row in the ResultSet. The term “ResultSet” refers to the number of rows and columns contained in a ResultSet object
According to.
If you do not specify any ResultSet type, you will automatically get a TYPE_FORWARD_ONLY.
