1. How to operate the database
To use client tools to access the database, you need to manually establish a connection, enter a user name and password to log in, write an SQL statement, click execute, and view the operation result (result set or number of affected rows).
2. JDBC
JDBC (Java Database Connectivity) is a specification (standard) for Java to connect to databases, and can use Java language to connect to databases to complete CRUD operations.
Java defines the interface for accessing the database, which can provide a unified access method for various relational databases. The driver implementation class (Driver database driver) is provided by the database vendor.
Environment construction
1. Open the project structure (Project Structure)
2. Select libraries, add jar package
3. Click apply in the lower right corner to check whether it is successful
3. JDBC programming
|
JDBC programming has standard steps (eight-legged essay)
|
3.1 Register driver
Use Class.forName(“com.mysql.jdbc.Driver”); to manually load the bytecode file into the JVM.
Class.forName("com.mysql.jdbc.Driver");//Load the driver
// The 8.0 driver needs to add cj
Class.forName("com.mysql.cj.jdbc.Driver");
3.2 Connecting to the database
-
Get the database connection object through DriverManager.getConnection(url,user,password)
-
URL: jdbc:mysql://localhost:3306/database
-
username:root
-
password: 123
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?useUnicode=true & amp;characterEncoding=utf8", "root","123");< /pre>
<p>PS: URL (Uniform Resource Locator) uniform resource locator: composed of protocol, IP, port, SID (program instance name)</p>
<h2 style="text-align:left;">3.3 Get the object to send SQL</h2>
<p>Obtain a Statement object through the Connection object for general access to the database.</p>
<pre>Statement statement = conn.createStatement();
3.4 Execute SQL statement
String sql ="INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA_Le','JAVA_Lecturer',4000,10000);";
int result = statement.executeUpdate(sql);//Execute the SQL statement and receive the result
-
Note: When writing DML statements, be sure to pay attention to the symbol of the string parameter is a single quote ‘value’
-
DML statement: when adding, deleting, or modifying, return the number of affected rows (int type).
-
DQL statement: When querying, return the result data (ResultSet result set).
3.5 Release resources
Follow the principle of opening before closing to release the used resource objects.
Use JDBC to insert data into the tb_user table:
create table tb_user( id int(11) primary key auto_increment comment 'user number', username varchar(10) comment 'username', password varchar(10) comment 'password', phone varchar(11) comment 'Mobile phone number', createTime date comment 'registration time', money double(10,2) comment 'account balance', sex int(1) comment 'Sex 1 male 2 female' );
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1 Load the driver
// ps: Use reflection technology to load the driver class into the JVM
Class.forName("com.mysql.jdbc.Driver");
// 2 Obtain the connection object through the driver management object
/**
* Parameter 1 url: the address of the database connection
* protocol://ip:port/library name
* Parameter 2 username: database username
* Parameter 3 password: database password
*/
String url = "jdbc:mysql://localhost:3306/java2217?useSSL=false &serverTimezone=UTC";
String username = "root";
String password = "123456";
Connection conn = DriverManager. getConnection(url, username, password);
// 3 Create an object that executes the sql statement by connecting the object
Statement statement = conn.createStatement();
// 4 By executing the statement object, execute sql, and get the result
String sql = "insert into tb_user (id, username, password, phone, createTime, money, sex) values (2,'root','123456','1122200','2022-11 -21',2000.0,2)";
// Execute the query, it is executeQuery()
// To perform additions, deletions, and modifications, it is executeUpdate(), which returns the number of affected rows
int num = statement. executeUpdate(sql);
if (num > 0) {
System.out.println("insert successfully!!" );
}
// 5 close current
statement. close();
conn. close();
}
4. Query result set ResultSet [important]
The query returns a virtual table. In Java’s JDBC, the result set (ResultSet) is used to encapsulate the virtual table. The result set is a collection, which stores the column name and each row of data inside. The key point of learning query is
-
get value from result set
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");
Statement statement = conn. createStatement( );
String sql = "select id, username, password from tb_user";
// The executeQuery method executeQuery, the return value of the method is ResultSet
ResultSet rs = statement. executeQuery(sql);
/**
* ResultSet internally contains the virtual table data returned by the entire query
* Internally provides methods to manipulate the result set
* boolean next(); Determine whether there is a next row of data in the result set, and return false, that is, there is no next row of data to return
* true means that there is the next row of data, and you can enter the value at this time
* Object getObject(int columnIndex) get data, return value is Object
* Object getObject(String columnLabel)
* getInt/getString/getDate() Get the data and return the corresponding data type
* --------------------------------------
* getXxx(int columnIndex) Obtain the data corresponding to the Xxx data type through the column subscript
* The subscript starts from 1, and the order is according to the order of the virtual table returned by the query
* getXxx(String columnLabel) Get the data corresponding to the Xxx data type through the column name
* According to the virtual table column name, if there is an alias, it is an alias
*/
while (rs. next()) {
// Get data by column subscript
// int id = rs. getInt(2);
// String username = rs. getString(1);
// Get data by column name [recommended]
int id = rs.getInt("id");
String username = rs. getString("username");
System.out.println(id + "-" + username);
}
statement. close();
conn. close();
}
5. ORM【Important】
5.1 What is ORM
At present, CRUD is completed using JDBC, but now it is CRUD. The method of adding, deleting and modifying needs to design many parameters, and the method of query needs to design a collection to return
In actual development, we need to encapsulate scattered data into objects for processing.
|
ORM (Object Relational Mapping) Object Relational Mapping It means that the database table has a relationship with the Java entity class and can be mapped
|
5.2 Entity class
Entity class: the carrier of scattered data in the data table, used to encapsulate the data.
-
Table name Design Class name
-
Design column names as attribute names
-
id –> id
-
create_time –> createTime (underscore to camel case)
-
Design the data type of the column as the data type of the attribute
-
Provide corresponding set get to the class
In a general project, a table will correspond to an entity class, and all entity classes will be placed in the model/entity/pojo/javabeen package structure
When writing projects in the future, after the database is designed and the project is built, the first thing to do is to create entity classes based on the table structure
6. SQL injection
6.1 What is SQL injection
There are SQL keywords in the data entered by the user, resulting in some abnormal situations when executing SQL statements. This is SQL injection!
6.2 Avoid SQL injection
The problem occurs when the user enters the data, there are keywords in it, and the combination of string splicing leads to SQL injection. Therefore, in order to avoid SQL injection, the SQL statement can be precompiled before the user enters the data into SQL. After preprocessing, JDBC You will know that this SQL needs several parameters, and then fill the parameters with the data entered by the user.
This is PreparedStatement
7. PreparedStatement【Key】
PreparedStatement is a subinterface of Statement, which is used to preprocess SQL statements
PreparedStatement uses
-
Write the SQL statement first, the parameters in the SQL statement cannot be spliced directly, but use ?
-
Use ps to preprocess the SQL statement, the processed? number, ps will know how many parameters the SQL statement needs
-
Then dynamically fill in the value for?
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter username:" );
String username = scanner. nextLine( );
System.out.println("Please enter the password:" );
String password = scanner. nextLine( );
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");
// Transform the SQL to turn splicing variables into? placeholders
String sql = "select * from tb_user where username = ? and password = ?";
System.out.println("before processing: " + sql);
// Change from the previous Statement to PreparedStatement
// Pass the modified SQL into the method
PreparedStatement ps = conn. prepareStatement(sql);
System.out.println("After processing: " + ps );
// Assign values to the processed placeholder parameters
// ps.setXxx() assigns a value to the specified Xxx type
// The first one?, the subscript is 1
ps. setString(1, username);
ps.setString(2,password);
System.out.println("After filling: " + ps );
//[Special attention!!!!] Here executeQuery does not need to pass in SQL parameters!!!
ResultSet rs = ps. executeQuery();
if (rs. next()) {
System.out.println("Successful login!!" );
} else {
System.out.println("Username or password is wrong!" );
}
rs. close();
ps. close();
conn. close();
}
8. DBUtil
DBUtil operates the tool class of the database, because it is found that every time the database is operated, the steps 1, 2, and 5 of JDBC are completely repeated, that is, loading the driver, obtaining the connection object, and the final flow is written every time, but every time all the same
Now design the tool class, simplify steps 1, 2, and 5
-
Design a method, call to get the connection object directly
-
Design a method to call to directly close all stream objects
Create jdbc.properties file under src
driverClass=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/java2301?useSSL=false username=root password=123456
// Create a Properties class object, dedicated to manipulating properties files
private static final Properties properties = new Properties();
/**
* The purpose of loading the driver is to have a sql running environment in the JVM
* There is only one copy of the environment, no need to load repeatedly
* ------------------------------------
* static static code block
* 1) Ensure that there is only one copy in memory
* 2) Guaranteed to be loaded with class loading, that is, the code block will be executed
*/
static {
// Obtain the bytecode file through reflection technology
// Then read the configuration file into an input stream through the bytecode file
InputStream inputStream = DBUtil.class.getResourceAsStream("/jdbc.properties");
try {
// Then get the data through the stream
properties.load(inputStream);
// Get the value from the properties object
Class.forName(properties.getProperty("driverClass"));
} catch (Exception e) {
System.out.println("Loading driver exception!!");
e.printStackTrace( );
}
}
/**
* Generally, the JDBC configuration information will be extracted to form a configuration file for easy maintenance
* The file type is a properties file, which is similar to a map, key-value pair type
* name jdbc.properties
* Location src/jdbc.properties
* content
*/
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username") ,properties.getProperty("password") );
} catch (Exception e) {
System.out.println("Obtain connection exception!!!");
e.printStackTrace();
}
return conn;
}
/**
* Close all streams
*/
public static void closeAll(Connection conn, Statement s) {
if (conn != null) {
try {
conn. close();
} catch (SQLException throwables) {
throwables. printStackTrace( );
}
}
if (s != null) {
try {
s. close();
} catch (SQLException throwables) {
throwables. printStackTrace( );
}
}
}
public static void closeAll(Connection conn, Statement s, ResultSet rs){
if (conn != null) {
try {
conn. close();
} catch (SQLException throwables) {
throwables. printStackTrace( );
}
}
if (s != null) {
try {
s. close();
} catch (SQLException throwables) {
throwables. printStackTrace( );
}
}
if (rs != null) {
try {
rs. close();
} catch (SQLException throwables) {
throwables. printStackTrace( );
}
}
}
}
There will be a more convenient and easy-to-use method in the future