2022/2/28
1, Basic concepts of JDBC
1. Concept: Java database connectivity, Java database connection, Java language operation database
2.JDBC essence: in fact, it is an officially defined set of rules for operating all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package
2, Quick start
package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/*
JDBC quick get start
Steps:
1.Import driver jar package
1.Copy the jar package to the self created folder of the project
2.Right click the folder -- > Add as self created folder
2.Register driver
3.Get database Connection object Connection
4.Define sql
5.Gets the object Statement that executes the sql Statement
6.Execute sql and receive the returned results
7.Processing results
8.To release the acquired resources
*/
public class jdbcDemo01 {
public static void main(String[] args) throws Exception {
//Import driver package
//Register driver
Class.forName("com.mysql.jdbc.Driver");
//Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
// Connection conn = DriverManager. getConnection("jdbc: mysql:///db1 "," root "," 123456 "); / / the local host and port number can also be omitted
//Define sql statement
String sql = "update student set age = 22 where id = 1";
//Get the object executing sql, Statement
Statement stmt = conn.createStatement();
//Execute sql
int count = stmt.executeUpdate(sql);
//Processing results
System.out.println(count);
//Release the resources of the acquired object
stmt.close();
conn.close();
}
}3, Explain each interface and class in JDBC in detail
1.DriverManager: drive management object
Function:
1) register driver: tell the program which database driver jar to use
static void registerDriver(Driver driver): register with the given driver DriverManager
Write code using: class forName("com.mysql.jdbc.Driver");
By looking at the source code, I found that: on COM mysql. jdbc. Static code block in driver class
Static code block features: when this class is used for the first time, the static code block executes only once.
Static content always takes precedence over non static, so static code blocks take precedence over construction methods.
Note: the driver jar package after mysql5 can omit the steps of registering the driver. It comes with it.
2) get database connection
1.1 method: static Connection getConnection(String ur1,String user,String password)
1.2 parameters:
1) ur1: specify the connection path
1. Syntax: jdbc:mysql://ip Address (domain name): port number / database name
Example: dbc:mysql:///db1
Details: if the local mysql server is connected, and the default port of the server is 3306
It can be abbreviated as jdbc:mysql: / / / database name
2.user: user name
3.password: password
2.Connection: database connection object
Function:
1. Get the object executing sql1:
Statement createStatement()
PreparedStatement preparedStatement(String sql)
2. Management practices:
1) start transaction: setAutoCommit(boolean sutoCommit)
Call this method and set the parameter to false to start the transaction
2) commit transaction:
3) rollback transaction: rollback()
3.Statement: object to execute sql
1. Execute sql
1) boolean execute(String sql): sql that can be executed arbitrarily
2) int executeupdate (string SQL): execute DML (insert,update,delete) statements
DDL(create,alter,drop) statement
Return value: the number of rows affected, which can be used to judge whether the DML statement is executed successfully
3) ResultSet executeQuery(String sql): execute DQL (Select) statement
Case exercise:
package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
//student table, add a record insert statement
public class JDBCDemo02 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1. Driver registration
Class.forName("com.mysql.jdbc.Driver");
//2. Define sql
String sql = "insert into student(id,namee,sorce,sex,age) values(4,'Zhang San',98,'nan',25)";
//3. Get the Connection object
conn = DriverManager.getConnection("jdbc:mysql:///db1","root","123456");
//4. Get the object Statement executing sql
stmt = conn.createStatement();
//5. Execute sql
int count = stmt.executeUpdate(sql); //count is the number of rows affected
//6. Treatment results
System.out.println(count);
if(count>0){
System.out.println("Added successfully!");
}else{
System.out.println("Failed to add!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
//student table, modify a record statement update
public class JDBCDemo02 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1. Driver registration
Class.forName("com.mysql.jdbc.Driver");
//2. Define sql
String sql = "update student set namee='Li Si' where id = 4";
//3. Get the Connection object
conn = DriverManager.getConnection("jdbc:mysql:///db1","root","123456");
//4. Get the object Statement executing sql
stmt = conn.createStatement();
//5. Execute sql
int count = stmt.executeUpdate(sql); //count is the number of rows affected
//6. Treatment results
System.out.println(count);
if(count>0){
System.out.println("Modification succeeded!");
}else{
System.out.println("Modification failed!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
//student table, delete a record statement delete
public class JDBCDemo02 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1. Driver registration
Class.forName("com.mysql.jdbc.Driver");
//2. Define sql
String sql = "delete from student where id = 4";
//3. Get the Connection object
conn = DriverManager.getConnection("jdbc:mysql:///db1","root","123456");
//4. Get the object Statement executing sql
stmt = conn.createStatement();
//5. Execute sql
int count = stmt.executeUpdate(sql); //count is the number of rows affected
//6. Treatment results
System.out.println(count);
if(count>0){
System.out.println("Delete succeeded!");
}else{
System.out.println("Deletion failed!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
//DDL creates a table (DDL has no return value, only one 0)
public class JDBCDemo02 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1. Driver registration
Class.forName("com.mysql.jdbc.Driver");
//2. Define sql
String sql = "create table stu(id int,name varchar(20))";
//3. Get the Connection object
conn = DriverManager.getConnection("jdbc:mysql:///db1","root","123456");
//4. Get the object Statement executing sql
stmt = conn.createStatement();
//5. Execute sql
int count = stmt.executeUpdate(sql); //DDL has no return value, only 0
//6. Treatment results
System.out.println(count);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}4.ResultSet: result set object, encapsulating query results
1)boolean next(): move the cursor down one line (the cursor defaults to the top field line at the beginning), and judge whether the current line is the last line (whether there is data). If so, return false; if not, return true.
2) getXxx (parameter): get data
1) Xxx: represents the data type, such as int getInt(), String getString();
2) parameters:
1.1int: represents the number of the column, starting from 1. For example: getString(1)
1.2String: represents the name of the column. For example: getdouble ('sorce ')
3) use steps:
1. Move cursor down one line
2. Judge whether there is data
3. get data
package ln.javatest.day01.demo01;
import java.sql.*;
public class JDBCDemo03 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//Register driver
Class.forName("com.mysql.jdbc.Driver");
//Defining sql objects
String sql = "select * from student";
//Get connection object
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "123456");
//Get and execute sql object
stat = conn.createStatement();
//Execute sql(DQL is executed with executeQuery())
rs = stat.executeQuery(sql);
//Processing results
//Loop to determine whether the cursor is at the end of the last line
while(rs.next()){
//get data
int id = rs.getInt(1);
String namee = rs.getString("namee");
int sorce = rs.getInt(1);
String sex = rs.getString("sex");
int age = rs.getInt(1);
System.out.println(id+"--"+namee+"--"+sorce+"--"+sex+"--"+"age");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
if(stat!= null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!= null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(rs!= null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}practice:
package ln.javatest.day01.demo01;
/*
practice:
Define a method to query the data of the Student table and encapsulate it into an object, then load the collection and return.
1.Define a Student class
2.Define method public list < student > findall() {}
3.Implementation method: select * from student;
*/
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo04 {
public static void main(String[] args) {
//Member variable, need new object to use
//For static methods, you can click the class name directly
JDBCDemo04 jd4 = new JDBCDemo04();
List<Student> list1 =jd4.findAll();
System.out.println(list1);
}
//Query all Student objects
public List<Student> findAll(){ //Member variable, need new object to use
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
List<Student> list = null;
try {
//Register driver
Class.forName("com.mysql.jdbc.Driver");
//Define sql
String sql = "Select * from Student";
//Get connection
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "123456");
//Get the object executing sql
stat = conn.createStatement();
//Execute sql
rs = stat.executeQuery(sql);
//Traverse the result set, encapsulate the object, and load the set
Student s = null;
list = new ArrayList<>();
while(rs.next()){
//get data
int id = rs.getInt(1);
String namee = rs.getString("namee");
int sorce = rs.getInt(1);
String sex = rs.getString("sex");
int age = rs.getInt(1);
//Create a Student object and assign a value
s = new Student();
s.setId(id);
s.setNamee(namee);
s.setSorce(sorce);
s.setSex(sex);
s.setAge(age);
//Mount collection
list.add(s);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return list;
}
}Note: you can extract the JDBC tool class: JDBC utils
1) purpose: to simplify writing
2) analysis:
1. Extract a registered driver
Requirements: do not want to pass parameters (trouble), but also ensure the universality of tool classes
Resolution: Profile
jdbc.properties
ur1=
user=
password=
2. Extract a method to get the connection
3. Extract a method to release resources
ur1=jdbc:mysql:///db1 user=root password=123456 driver=com.mysql.jdbc.Driver
package ln.javatest.day01.demo01;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
//JDBC tool class
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
//The file can be read only once to get these values, using static code blocks.
static{
try {
//Read the resource file and get the value
//1. Create Properties collection class
Properties pro = new Properties();
//Get the path of classloader -- >
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2. Load file
pro.load(new FileReader(path));
//3. Obtain data source and assign value
url=pro.getProperty("ur1");
user=pro.getProperty("user");
password=pro.getProperty("password");
driver=pro.getProperty("driver");
//4. Register driver
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//Get connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//Release resources
public static void close(Statement stat, Connection conn){
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stat, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}package ln.javatest.day01.demo01;
/*
Demonstrate jdbc tool classes
*/
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo05 {
public static void main(String[] args) {
//Member variable, need new object to use
//For static methods, you can click the class name directly
JDBCDemo05 jd4 = new JDBCDemo05();
List<Student> list1 =jd4.findAll();
System.out.println(list1);
}
//Query all Student objects
public List<Student> findAll(){ //Member variable, need new object to use
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
List<Student> list = null;
try {
/*//Register driver
Class.forName("com.mysql.jdbc.Driver");
//Get connection
conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "123456");*/
conn = JDBCUtils.getConnection();
//Define sql
String sql = "Select * from Student";
//Get the object executing sql
stat = conn.createStatement();
//Execute sql
rs = stat.executeQuery(sql);
//Traverse the result set, encapsulate the object, and load the set
Student s = null;
list = new ArrayList<>();
while(rs.next()){
//get data
int id = rs.getInt(1);
String namee = rs.getString("namee");
int sorce = rs.getInt(1);
String sex = rs.getString("sex");
int age = rs.getInt(1);
//Create a Student object and assign a value
s = new Student();
s.setId(id);
s.setNamee(namee);
s.setSorce(sorce);
s.setSex(sex);
s.setAge(age);
//Mount collection
list.add(s);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
/*if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}*/
JDBCUtils.close(rs,stat,conn);
}
return list;
}
}practice:
1. Enter the user name and password through the keyboard
2. Judge whether the user logs in successfully
ur1=jdbc:mysql:///db1 user=root password=123456 driver=com.mysql.jdbc.Driver
package ln.javatest.day01.demo01;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
//JDBC tool class
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
//The file can be read only once to get these values, using static code blocks.
static{
try {
//Read the resource file and get the value
//1. Create Properties collection class
Properties pro = new Properties();
//How to get the file under src path -- > classloader classloader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2. Load file
pro.load(new FileReader(path));
//3. Obtain data source and assign value
url=pro.getProperty("ur1");
user=pro.getProperty("user");
password=pro.getProperty("password");
driver=pro.getProperty("driver");
//4. Register driver
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//Get connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//Release resources
public static void close(Statement stat, Connection conn){
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stat, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/*
Login exercise:
1.Enter the user name and password through the keyboard
2.Judge whether the user logs in successfully
*/
public class JDBCDemo06 {
public static void main(String[] args) {
//1. Keyboard entry, accept user name and password
Scanner sc = new Scanner(System.in);
System.out.println("Please enter user name:");
String username = sc.next();
System.out.println("Please input a password");
String password = sc.next();
//2. Call method
boolean flag = new JDBCDemo06().login(username, password);
//3. Judge the result and output different statements
if(flag){
//Login successful
System.out.println("Login successful");
}else{
System.out.println("User or password error");
}
}
//Login method
public boolean login(String username,String password){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
if(username==null || password==null){
return false;
}
//Connect to the database to determine whether the login is successful
//1. Get connection
try {
conn = JDBCUtils.getConnection();
//2. Define sql
String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
//3. Get the object executing sql
stat = conn.createStatement();
//4. Execute query
rs = stat.executeQuery(sql);
//5. Judgment
return rs.next(); //The next line returns true
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(rs,stat,conn);
}
return false;
}
} 5.PreparedStatement: the object that executes sql
1) SQL injection problem:
When splicing sql, some special keywords of sql participate in string splicing. It will cause security problems
1. Enter user name and password: a 'or' a '='a
2.sql:select * from user where username = 'dhiwndfb' and password = a' or 'a' = 'a
2) solve the problem of sql injection: use the PreparedStatement object to solve it
3) precompiled SQL: parameter usage? As placeholder
4) steps:
1. Import driver jar package
1. Copy the jar package to the self created folder of the project
2. Right click the folder -- > Add as self created folder
2. Register driver
3. Get database Connection object Connection
4. Define sql
Note: the parameters of sql use? As placeholder
5. Gets the object PreparedStatement that executes the sql statement
6. Here? assignment
Method: setXxx (parameter 1, parameter 2)
Parameter 1:? Position number of the, starting from 1
Parameter 2:? Value of
7. Execute sql and receive the returned results
8. Processing results
9. To release the acquired resources
package ln.javatest.day01.demo01;
import java.sql.*;
import java.util.Scanner;
/*
Implementation using PreparedStatement interface
Login exercise:
1.Enter the user name and password through the keyboard
2.Judge whether the user logs in successfully
*/
public class JDBCDemo07 {
public static void main(String[] args) {
//1. Keyboard entry, accept user name and password
Scanner sc = new Scanner(System.in);
System.out.println("Please enter user name:");
String username = sc.next();
System.out.println("Please input a password");
String password = sc.next();
//2. Call method
boolean flag = new JDBCDemo07().login(username, password);
//3. Judge the result and output different statements
if(flag){
//Login successful
System.out.println("Login successful");
}else{
System.out.println("User or password error");
}
}
//Login method
public boolean login(String username,String password){
Connection conn = null;
PreparedStatement pstat = null;
ResultSet rs = null;
if(username==null || password==null){
return false;
}
//Connect to the database to determine whether the login is successful
//1. Get connection
try {
conn = JDBCUtils.getConnection();
//2. Define sql
String sql = "select * from user where username = ? and password = ?";
//3. Get the object executing sql
pstat = conn.prepareStatement(sql);
//Here? assignment
pstat.setString(1,username);
pstat.setString(2,password);
//4. Execute query
rs = pstat.executeQuery();
//5. Judgment
return rs.next(); //The next line returns true
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(rs,pstat,conn);
}
return false;
}
}JDBC control transaction:
1. Transaction: a business operation with multiple steps. If this business operation is managed by transactions, multiple steps will either succeed or fail at the same time.
2. Operation:
1) start transaction
2) submit transaction
3) rollback transaction
3. Use the Connection object to manage transactions
1) start transaction: setAutoCommit (boolean autoCommit)
Call this method and set the parameter to false to start the transaction
Start the transaction before executing sql again
2) commit transaction:
When all sql has completed the commit transaction
3) rollback transaction: rollback ()
Rolling back transactions in catch
package ln.javatest.day01.demo01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
//Transaction operation
public class JDBCDemo08 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstat1 = null;
PreparedStatement pstat2 = null;
try {
//1. Get connection
//Open transaction
conn.setAutoCommit(false);
conn = JDBCUtils.getConnection();
//2. Define sql
//2.1 sheet 3-500
String sql1= "update account set balance= balance - ? where id = ?";
//2.2 Li Si + 500
String sql2= "update account set balance= balance + ? where id = ?";
//3. Get and execute sql objects
pstat1 = conn.prepareStatement(sql1);
pstat2 = conn.prepareStatement(sql2);
//4. Set parameters
pstat1.setDouble(1,500);
pstat1.setInt(2,1);
pstat2.setDouble(1,500);
pstat2.setInt(2,2);
//5. Execute sql
pstat1.executeUpdate();
//Manual manufacturing exception
int i= 3/0;
pstat2.executeUpdate();
//Commit transaction
conn.commit();
} catch (SQLException throwables) {
//Transaction rollback
try {
if(conn != null){
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JDBCUtils.close(pstat1,conn);
JDBCUtils.close(pstat2,null);
}
}
}