preface
🦁 entity+Dao Model - very important Dao-Table name Dao-Add, delete, modify and query a table - it will be simplified into an interface later entity-Corresponding to the entity table in the database (table name, attribute, method) 🐅IDEA Bring your own Database
JDBC Foundation
(1) JDBC principle
-
JDBC (Java Database Connectivity): Java Database Connectivity technology. All Java programs need JDBC. The mainstream framework encapsulates JDBC. In fact, JDBC must be used at the bottom.
The JDBC API is mainly located in Java. Net in the JDK In the SQL package, the extended content is located in javax SQL package. (x stands for expansion, and all contents of JDBC during native + expansion) -
JDBC is between Java Application and Oracle, MySQL and SQL Server, i.e. JDBC API + JDBC Driver Manager+ Oracle JDBC Driver
Among them, the driver is developed by manufacturers of different databases according to the specification of JDBC - ∈ interface oriented programming
JDBC Driver Manager defines specifications and standards. When producing different drivers, it is uncertain what database will be connected - reflection mechanism
(2) JDBC configuration in IDEA
-
mysql 5.7 can be driven by version 8.0 and version 5.1, but not 5.5, 5.6 and 5.7.
Here is my MySQL 5 7，JDBC5.1.38.
Open IDEA, create a new project, create a new folder lib under the project (LIB folder is usually used to store. jar package), copy the downloaded JDBC and paste it under lib folder.
(External Libraries store the items that have been referenced by the project. At this time, only jdk13. Is referenced in the project - different versions or projects) -
Reference JDBC into project
Right click the file copied into lib and select Add as Library
You can choose where to put the jar into the modified project (global, this project, module)
=>Can be opened after success
-
completion
When the Java application, database table and jdbc driver reference are completed, you only need to complete the JDBC API and JDBC Driver Marager to successfully connect the application and database.
4. The rest
① Java application
② Database
(3) Precompiled
1. SQL injection
The query exercise in JDBC code is changed to the following code:
List<Student> studentList = studentDao.query("' or 1=1 or '");
It can be found that the sql statement and query results are shown in the following figure:
Because JDBC queries organize SQL statements in the form of concatenated strings, when the user's name is' or 1=1 or ', a constant SQL statement is formed, and all information can be queried. This is SQL injection, which can be avoided by precompiling.
2. Precompile concept
① The principle of precompiling - first send the SQL statement template to the database to compile into a function, and then pass the data to the database as a parameter, so that the keywords in the data such as or 1=1 will not be understood as a part of the SQL statement, thus avoiding SQL injection.
② The advantage of precompiling -- using precompiling can also improve the execution efficiency of sql. The same sql statement will only be compiled once in the database. However, execution can be called multiple times.
- Statement -- string splicing completes SQL statement, which is not safe
- PreparedStatement is a subclass of Statement.
- Precompiled is compiled first and then passed in parameters
3 . Use of precompiled
1. Use PreparedStatement instead of Statement. Parameter usage? Placeholder. 2. Send the sql statement to the database when creating the PreparedStatement object. 3. Use setter method to assign values to parameters.- Core code:
preparedStatement = conn.prepareStatement("insert into student (stu_name, stu_age) values (?,?)");
preparedStatement.setString(1,"david");
preparedStatement.setInt(2,20);
ret = preparedStatement.executeUpdate();
1>preparedStatement = conn.prepareStatement("insert into student (stu_name, stu_age) values (?,?)");
=> conn first execute prepareStatement and insert the SQL statement into student (stu_name, stu_age) values (?,?) Send to server preparedStatement
2> preparedStatement.setString(1,"david");
preparedStatement.setInt(2,20);
=> set two values
3>ret = preparedStatement.executeUpdate();
=> when executeUpdate() is called, the value is sent to the database
At this time, the SQL statement is sent to the database, and the database precompiles the SQL statement. Encounter? When, regard this as the parameter of the method, and replace the parameter (1, 2) in the value with? Run at.
Note: when assigning values to parameters, the subscript starts from 1.
(4) Six steps of JDBC connecting to database
1. Step content
- Register driver (only once)
- Establish connection
- Create a statement to run SQL
- Run statement
- Process run results (ResultSet)
- Release resources
Statement can be DML or DQL. When the statement is DML, the number of affected items is returned; When the statement is DQL, the ResultSet is returned. ResultSet depends on connection.
When the connection is closed, the Statement and ResultSet will be destroyed. Temporary variable
Therefore, when the obtained ResultSet cannot be used directly, it needs to be transferred to ArrayList.
two ❤ Operation ❤ 👶
1>. Register driver (only once)
reflex
try {
//Register driver (only once)
Class.forName(driver);// **Reflection**
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
2>. Establish connection
①
Higher versions need to add parameters after the url. Without parameters, there will be problems with the time and text (incorrect time, garbled code...)
②
String driver ="";//Drivers of different databases and different versions of the same database are different
String url="";//Database connection
String name="root";//Login user, usually hot
String pass="";//Password of your own database
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
Connection connection=DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
——Connect to database Scott user
3>. Create a statement to run S QL
4>. Run statement
String driver ="";//Drivers of different databases and different versions of the same database are different
String url="";//Connect to what database
String name="root";//Login user, usually hot
String pass="123456";//Password of your own database
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
Connection connection=DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
//Create a statement to run SQL
PreparedStatement statement=connection.prepareStatement("insert into student (student_name,student_no,sex) values(?,?,?)");
statement.setObject(1 ,"king");
statement.setObject(2 ,"2022228");
statement.setObject(3 ,"male");
//Run statement
int ret = statement.executeUpdate();
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
be careful:
Executequry -- DQL query operation, return the result set resultSet; executeUpdate - DML addition, deletion and modification, returns resultInt, and affects the number of entries
5>. Process run results (ResultSet)
Note: processing the running result (ResultSet) -- this step is only available for query operations, but not for addition, deletion and modification operations6>. Release resources
① Resources to be closed: connection, statement, (resultset)
② It is not appropriate to close in try catch, and the resource should be finally closed.
Because the scope of connection and statement is: blue area
Cannot close in an area outside of range.
Change scope:
③ Release
......
}finally {
//Release resources (connection, statement, resultset)
try {
//The object does not exist and cannot be closed. try/catch needs to be added
connection.close(); //?
statement.close();
}catch (SQLException throwables){
throwables.printStackTrace();
}
}
Detail modification
① Multiple catch must add a parent class at last
② Return result return
int ret=-1；
Because 0 and 1 are normal return results
result
import java.sql.*;
public class Text {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String driver ="com.mysql.jdbc.Driver";
//Drivers of different databases and different versions of the same database are different
String url="jdbc:mysql://localhost:3306/scott?useSSL=false "; / / connect to the what database
String name="root";//Login user, usually hot
String pass="123456";//Password of your own database
Connection connection = null;
PreparedStatement statement = null;
int ret=-1；
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
connection=DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
//Create a statement to run SQL
statement=connection.prepareStatement("insert into student (student_name,student_no,sex) values(?,?,?)");
statement.setObject(1 ,"king");
statement.setObject(2 ,"2022228");
statement.setObject(3 ,"male");
//Run statement
ret = statement.executeUpdate();
System.out.println(ret);
//Processing run results (ResultSet) -- only available when querying, but not when adding, deleting or modifying
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (Exception e) { //Multiple catch must add a parent class at last
e.printStackTrace();
}finally {
//Release resources (connection, statement, resultset)
try {//The object does not exist and cannot be closed. try/catch needs to be added
connection.close();
statement.close();
}catch (SQLException throwables){
throwables.printStackTrace();
}
}
return ret；
}
}
supplement
Characters may be garbled, as shown in the figure
3. 📕 Dao+entity📕 🧑
1>. Package build. Including insert, delete, modify and query methods
1> > insert 🚩
public int insert(){//Insertion method
String driver ="com.mysql.jdbc.Driver";
//Drivers of different databases and different versions of the same database are different
String url="jdbc:mysql://localhost:3306/scott?useSSL=false "; / / connect to the what database
String name="root";//Login user, usually hot
String pass="123456";//Password of your own database
Connection connection = null;
PreparedStatement statement = null;
int ret =-1;
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
connection= DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
//Create a statement to run SQL
statement=connection.prepareStatement("insert into student (student_name,student_no,sex) values(?,?,?)");
statement.setObject(1 ,"lily");
statement.setObject(2 ,"202205");
statement.setObject(3 ,"female");
//Run statement
ret = statement.executeUpdate();
System.out.println(ret);
//Processing run results (ResultSet) -- only available when querying, but not when adding, deleting or modifying
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (Exception e) { //Multiple catch, and finally add a parent class
e.printStackTrace();
}finally {
//Release resources (connection, statement, resultset)
try {//The object does not exist and cannot be closed. try/catch needs to be added
connection.close();
statement.close();
}catch (SQLException throwables){
throwables.printStackTrace();
}
}
return ret;
}
2> > Modify
public int update(){ //Modification method
String driver ="com.mysql.jdbc.Driver";
//Drivers of different databases and different versions of the same database are different
String url="jdbc:mysql://localhost:3306/scott?useSSL=false "; / / connect to the what database
String name="root";//Login user, usually hot
String pass="123456";//Password of your own database
Connection connection = null;
PreparedStatement statement = null;
int ret =-1;
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
connection= DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
//Create a statement to run SQL
statement=connection.prepareStatement("update student set address = ? where id = ?");
statement.setObject(1 ,"Beijing");
statement.setObject(2 ,"4");
//Run statement
ret = statement.executeUpdate();
System.out.println(ret);
//Processing run results (ResultSet) -- only available when querying, but not when adding, deleting or modifying
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (Exception e) { //Multiple catch, and finally add a parent class
e.printStackTrace();
}finally {
//Release resources (connection, statement, resultset)
try {//The object does not exist and cannot be closed. try/catch needs to be added
connection.close();
statement.close();
}catch (SQLException throwables){
throwables.printStackTrace();
}
}
return ret;
}
3> > delete omitted
4> > query 🚩
- Storage space
① The return value type of the query is the List result set, which stores a model
entiey (class - entity attribute - relationship between column classes - relationship correspondence between entities)
Generate tables and corresponding getter & setter methods (one-to-one correspondence with the database)
The toString() method is overridden to print data instead of memory addresses
[see test call]
② Encapsulate data
- Query code
① column label -- column name; column index -- subscript of column
② Core code
Every time there is a record in the result set, instantiate the record object, and add the corresponding item in the record to the attribute of the class (corresponding to the column and attribute) - after encapsulation, it represents a student
Put students into the result set, and the final result set is the query result
public List<Student> queryAllStudent(){//Query method
String driver ="com.mysql.jdbc.Driver";
//Drivers of different databases and different versions of the same database are different
String url="jdbc:mysql://localhost:3306/scott?useSSL=false "; / / connect to the what database
String name="root";//Login user, usually hot
String pass="123456";//Password of your own database
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs =null;
List<Student> list = new ArrayList<>();//list plus generics
try {
//Register driver (only once)
Class.forName(driver);//reflex
//Establish connection
connection= DriverManager.getConnection (url,name,pass);//Four parameters are required to establish a connection
//Create a statement to run SQL
statement=connection.prepareStatement("select * from student");
//Run statement
rs = statement.executeQuery();
//Processing run results (ResultSet) -- only available when querying, but not when adding, deleting or modifying
while (rs.next()){//Do not know the number of records, do not know the number of cycles - while. When the pointer of the result set can move down one step
Student student = new Student();//Instantiate object
//Put every value in rs into Student
student.setId(rs.getInt(1));
student.setStudent_name(rs.getString("student_name"));
list.add(student);
}
}catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (Exception e) { //Multiple catch, and finally add a parent class
e.printStackTrace();
}finally {
//Release resources (connection, statement, resultset)
try {//The object does not exist and cannot be closed. try/catch needs to be added
connection.close();
statement.close();
}catch (SQLException throwables){
throwables.printStackTrace();
}
}
return list;
}
2>. Test call
forEach
At this time, the memory address of the object is printed. To print the object itself, override the toString() method
Text. Testing in Java
import dao.StudentDao;
public class Text {
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
studentDao.queryAllStudent().forEach(System.out::println);
}
}
result