1. Succinct
JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language. JDBC provides a benchmark by which more advanced tools and interfaces can be built to enable database developers to write database applications
Java is strong, safe, easy to use, easy to understand and can be automatically downloaded from the network. It is an outstanding language for writing database applications. All you need is a way for Java applications to talk to different databases.
JDBC can use Java on various platforms, such as Windows, Mac OS and various versions of UNIX.
Role of JDBC:
- Connect to the database.
- Create SQL or MySQL statements.
- Execute SQL or MySQL queries in the database.
- View and modify generated records.
2. JDBC architecture
JDBC API supports two-tier and three-tier processing models for database access, but generally, JDBC architecture consists of two layers:
- JDBC API: This provides an application connection 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 the list of database drivers. Use the communication sub protocol to match the connection request from the java application with the appropriate database driver.
Driver: this interface handles the communication with the database server. We rarely interact directly with the driver object. Instead, use the DriverManager object to manage this type of object.
Connection: this interface has all the methods used to contact the database. The connection object represents the communication context, that is, all communication with the database only through the connection object
Statement: submit SQL statements to the database using objects created from this interface. In addition to executing stored procedures, some derived interfaces also accept parameters.
ResultSet: after executing SQL queries using Statement objects, these objects save the data retrieved from the database. As an iterator, it allows us to move its data.
SQLException: this class handles any errors that occur in the database application
4. Introduction to CRUD syntax
SQL Is a standardized language that allows you to perform operations on the database, such as creating projects, querying content, updating content, and deleting entries.
Create, Read, Update, and Delete are commonly referred to as CRUD operations.
The CREATE DATABASE statement is used to create a new database:
create database database_name;
The DROP DATABASE statement is used to delete an existing database
DROP DATABASE DATABASE_NAME;
The CREATE TABLE statement is used to create a new table. Grammar is
CREATA TABLE EMPLOYEES( ID INT NOT NULL; AGE INT NOT NULL; NAME VARCHER (255); BIRTHDAY TIMEDATE(); PRIMARY KEYY (ID); );
The DROP TABLE statement is used to drop an existing table.
DROP TABLE TABLE_NAME;
The syntax of INSERT is similar to the following, where column1, column2, etc. represent the new data to be displayed in the corresponding column
INSERT INTO table_name VALUES (column1, column2, ...);
The SELECT statement is used to retrieve data from a database. The syntax of SELECT is
SELECT column_name, column_name, ... FROM table_name WHERE conditions;
WHERE clause can use comparison operators, such as =,! =, <, ><= And > =, and BETWEEN and LIKE operators.
The UPDATE statement is used to UPDATE data.
UPDATE table_name SET column_name = value, column_name = value, ... WHERE conditions;
The DELETE statement is used to DELETE data from a table.
DELETE FROM table_name WHERE conditions
5. Use steps
Building a JDBC application involves the following six steps:
- Import package: a package containing JDBC classes required for database programming. In most cases, use import Java sql.* That's enough
- Register JDBC Driver: you are required to initialize the driver so that you can open the communication channel with the database.
- Open Connection: drivermanager is required The getconnection () method creates a Connection object that represents a physical Connection to the database.
- Execute query: you need to use an object of type Statement to build and submit SQL statements to the database
- Extract data from the result set: you need to use the corresponding resultset The getxxx () method retrieves data from the result set.
- Free resources: you need to explicitly shut down all database resources without relying on JVM garbage collection.
6. JDBC connection steps
The programming involved in establishing a JDBC connection is quite simple. Here are four simple steps
- Import JDBC package: add the import statement of Java language to the Java code to import the required classes.
- Register JDBC Driver: this step will cause the JVM to load the required driver implementation into memory so that it can meet your JDBC request.
- -Register JDBC Driver: this step will cause the JVM to load the required driver implementation into memory so that it can meet your JDBC request.
- Create a connection object: finally, call the getConnection () method of the DriverManager object to establish the actual database connection.
Class.forName();
The most common way to register drivers is to use Java's class The forname() method dynamically loads the class file of the driver into memory and automatically registers it
try {
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
DriverManager.registerDriver()
The second method is to use static drivermanager Registerdriver() method.
try {
Driver myDriver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver( myDriver );
}catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Database URL configuration
After loading the driver, you can use drivermanager The getconnection () method establishes a connection. For ease of reference, let me list three overloaded drivermanagers Getconnection() method-
- getConnection(String url)
- getConnection(String url，Properties prop)
- getConnection(String url，String user，String password)
Create database connection object
String URL = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC"; String USER = "username"; String PASS = "password" Connection conn = DriverManager.getConnection(URL, USER, PASS);
Complete connection address:
jdbc:mysql://localhost:3306 / database name? useSSL=false&useUnicode=true&characterEncoding=UTF-8
jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC
Close database connection
To ensure that the connection is closed, you can provide a "finally" block in your code. A finally block is always executed regardless of whether an exception occurs. To close the connection opened above, you should call the close () method as follows
coon.close();
6.1 JDBC executes SQL statements
Once the connection is obtained, we can interact with the database. The JDBC Statement and PreparedStatement interfaces define methods and properties that enable you to send SQL commands and receive data from the database.
Used for general access to the database. It is useful when using static SQL statements at run time. The Statement interface cannot accept parameters.
Use when you plan to use SQL statements multiple times. The PreparedStatement interface accepts input parameters at run time.
6.2statement
Create statement object
Before using the Statement object to execute SQL statements, you need to use the createStatement() method of the Connection object to create one, as shown in the following example:
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
After creating the Statement object, you can use it to execute an SQL Statement with three execution methods
- boolean execute (String SQL): if the ResultSet object can be retrieved, a Boolean value of true will be returned; Otherwise, false is returned. Use this method to execute SQL DDL statements or when you need to use real dynamic SQL.
- int executeUpdate (String SQL): returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements that are expected to affect multiple rows, such as INSERT, UPDATE, or DELETE statements.
- ResultSet executeQuery (String SQL): returns a ResultSet object. Use this method when you want to get the result set, just like using the SELECT statement.
Close the Statement object
Just as we close a Connection object to save database resources, for the same reason, we should also close the Statement object.
A simple call to the close () method will execute the job. If you close the Connection object first, it also closes the Statement object. However, you should always explicitly close the Statement object to ensure proper cleanup.
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
stmt.close();
}
6.3 SQL injection
By inserting the SQL command into the WEB form to submit or enter the query string of the domain name or page request, we can finally deceive the server to execute the malicious SQL command. Specifically, it is the ability to use existing applications to inject (malicious) SQL commands into the background database engine for execution. It can get a database on a website with security vulnerabilities by entering (malicious) SQL statements in a WEB form, rather than executing SQL statements according to the designer's intention. For example, many previous film and television websites leaked VIP member passwords mostly through WEB forms, and the query characters were exposed. Such forms are particularly vulnerable to SQL injection attacks.
String username ="admin"; String password=" 'abc' or 1=1 "; String sql="select * from users where username= '"+username+"' and password= "+password;
7. Preparedstatement (pre state channel)
The PreparedStatement interface extends the Statement interface, which provides you with a general Statement object with two advantages and additional functions.
This statement allows you to provide parameters dynamically.
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
All parameters in JDBC are controlled by? Symbols, which are called parameter markers. You must provide a value for each parameter before executing the SQL statement.
The setXXX() method binds the value to the parameter, where XXX represents the Java data type to be bound to the value of the input parameter. If you forget to provide a value, you will receive an SQLException.
Each parameter tag is referenced by its sequential position. The first mark represents position 1, the next position 2, and so on. This method is different from Java array index, starting from 0.
Close the PreparedStatement object
A simple call to the close () method will execute the job. If you close the Connection object first, it also closes the PreparedStatement object. However, you should always explicitly close the PreparedStatement object 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 status channel will compile sql statements first and then execute them, which is more efficient than statement execution
- (3) Pre status channels support placeholders?, When assigning a value to a placeholder, the position starts with 1
- (4) The pre status channel can prevent sql injection. Reason: the pre status channel processes the value in the form of string
8,Resultset
The SELECT statement is a standard way to SELECT rows from a database and view rows in a result set. The Java sql. The interface in the resultset represents the result set database query.
The ResultSet object maintains a cursor pointing to the current row in the result set. The term "result set" refers to the row and column data contained in the ResultSet object.
| type | describe |
|---|---|
| ResultSet.TYPE_SCROLL_INSENSITIVE | The cursor can scroll forward and backward, and the result set is not sensitive to other changes to the database that occur after the result set is created. |
| ResultSet.TYPE_SCROLL_SENSITIVE. | The cursor can scroll forward and backward, and the result set is sensitive to changes made by other databases after the result set is created. |
| ResultSet.TYPE_FORWARD_ONLY | The cursor can only move forward in the result set. |
9. JAVA operation relation table
Four kinds: two-way, one to one, one to many, many to one, many to many
Multi table relation processing data
(1) The database establishes the relationship between two tables through foreign keys
(2) Entity classes establish two table relationships through attributes
Entity class requirements:Class name=Table name, column name=Attribute name
9.1 one to many (teacher - > student)
1 create data table
CREATE TABLE `student` (
`stuid` int(11) NOT NULL AUTO_INCREMENT,
`stuname` varchar(255) DEFAULT NULL,
`teacherid` int(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', 'aaa', '3');
INSERT INTO `student` VALUES ('2', 'bb', '1');
INSERT INTO `student` VALUES ('3', 'cc', '3');
INSERT INTO `student` VALUES ('4', 'dd', '1');
INSERT INTO `student` VALUES ('5', 'ee', '1');
INSERT INTO `student` VALUES ('6', 'ff', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', 'Miss Zhang San');
INSERT INTO `teacher` VALUES ('2', 'Miss Li Si');
INSERT INTO `teacher` VALUES ('3', 'Wang Wu');
2 create entity class
public class Teacher {
private int tid;
private String tname;
private List<Student> list=new ArrayList<Student>();
public List<Student> getList() {
return list;
}
public void setList(List<Student> list) {
this.list = list;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
}
public class Student {
private int stuid;
private String stuname;
//Foreign key columns generally do not generate attributes
// private int teacherid;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
}
3. Define dao interface
public interface TeacherDao {
//Define operation method
//1. Define a method to query teacher information (student information) according to teacher id
public Teacher getById(int tid);
}
4. Define implementation classes
public class TeacherDaoImpl implements TeacherDao {
@Override
public Teacher getById(int tid) {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from student s,teacher t where
s.teacherid=t.tid and t.tid=?";
pps = connection.prepareStatement(sql);
//Assign a value (subscript, content) to the placeholder, starting with 1
pps.setInt(1,tid);
//Execute sql
resultSet = pps.executeQuery();
Teacher teacher = new Teacher();
List<Student> students=new ArrayList<Student>();
while (resultSet.next()){
//1. Take out their respective information
teacher.setTid(resultSet.getInt("tid"));
teacher.setTname(resultSet.getString("tname"));
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
//2. Establish the relationship between students and teachers
students.add(student);
}
teacher.setStudentList(students);
return teacher;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
5 define test class
public class Demo1 {
public static void main(String[] args) {
TeacherDao dao= new TeacherDaoImpl();
Teacher teacher = dao.getById(1);
System.out.println("Teacher name:"+teacher.getTname());
List<Student> studentList = teacher.getStudentList();
for (Student student : studentList) {
System.out.println("\t studentname="+student.getStuName());
}
}
}
9.2 many to one (student teacher)
Entity class
public class Student {
private int stuid;
private String stuname;
//Foreign key columns generally do not generate attributes
// private int teacherid;
private Teacher teacher;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
}
public class Teacher {
private int tid;
private String tname;
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
}
Interface
//Query all students (including teacher information) public List<Student> getAll();
Implementation class
public List<Student> getAll() {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from student s,teacher t where
s.teacherid=t.tid";
pps = connection.prepareStatement(sql);
//Execute sql
resultSet = pps.executeQuery();
List<Student> students=new ArrayList<>();
while (resultSet.next()){
//1. Take out respective information
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
Teacher teacher = new Teacher();
teacher.setTid(resultSet.getInt("tid"));
teacher.setTname(resultSet.getString("tname"));
//2. Establish the relationship between students and teachers
student.setTeacher(teacher);
students.add(student);
}
return students;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
Test class
public static void main(String[] args) {
TeacherDao dao= new TeacherDaoImpl();
List<Student> students = dao.getAll();
for (Student student : students) {
System.out.println(student.getStuName()+","+student.getTeacher().getTname());
}
}
9.3 one to one
public class Husband {
private int husid;
private String husname;
private Wife wife;
public int getHusid() {
return husid;
}
public void setHusid(int husid) {
this.husid = husid;
}
public String getHusname() {
return husname;
}
public void setHusname(String husname) {
this.husname = husname;
}
public Wife getWife() {
return wife;
}
public void setWife(Wife wife) {
this.wife = wife;
}
}
public class Wife {
private int wifeid;
private String wifeName;
private Husband husband;
public int getWifeid() {
return wifeid;
}
public void setWifeid(int wifeid) {
this.wifeid = wifeid;
}
public String getWifeName() {
return wifeName;
}
public void setWifeName(String wifeName) {
this.wifeName = wifeName;
}
public Husband getHusband() {
return husband;
}
public void setHusband(Husband husband) {
this.husband = husband;
}
}
Interface
public interface WifeDao {
//Query wife information (including husband information)
public Wife getWife(int wid);
//Query husband information (including wife information)
public Husband getHus(int hid);
}
Implementation class
public class WifeDaoImpl implements WifeDao {
@Override
public Wife getWife(int wid) {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from wife w,husband h where w.hid=h.husid and
w.wifeid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,wid);
//Execute sql
resultSet = pps.executeQuery();
Wife wife = new Wife();
while (resultSet.next()){
//1. Take out their respective information
wife.setWifeId(resultSet.getInt("wifeid"));
wife.setWifeName(resultSet.getString("wifename"));
Husband husband = new Husband();
husband.setHusId(resultSet.getInt("husid"));
husband.setHusName(resultSet.getString("husname"));
//2. Establish the relationship between wife and husband
wife.setHusband(husband);
}
return wife;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public Husband getHus(int hid) {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from wife w,husband h where w.hid=h.husid and
h.husid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,hid);
//Execute sql
resultSet = pps.executeQuery();
Husband husband = new Husband();
while (resultSet.next()){
//1. Take out their respective information
Wife wife = new Wife();
wife.setWifeId(resultSet.getInt("wifeid"));
wife.setWifeName(resultSet.getString("wifename"));
husband.setHusId(resultSet.getInt("husid"));
husband.setHusName(resultSet.getString("husname"));
//2. Establish the relationship between wife and husband
husband.setWife(wife);
}
return husband;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
Test class
public static void main(String[] args) {
WifeDaoImpl wifeDao = new WifeDaoImpl();
Wife wife = wifeDao.getWife(1);
System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
Husband hus = wifeDao.getHus(1);
System.out.println(hus.getHusName()+","+hus.getWife().getWifeName());
}
9.4 many to many (subject – student)
Entity class
public class Subject {
private int subid;
private String subname;
private List stulist;
public int getSubid() {
return subid;
}
public void setSubid(int subid) {
this.subid = subid;
}
public String getSubname() {
return subname;
}
public void setSubname(String subname) {
this.subname = subname;
}
public List getStulist() {
return stulist;
}
public void setStulist(List stulist) {
this.stulist = stulist;
}
}
public class Student {
private int stuid;
private String stuname;
//Foreign key columns generally do not generate attributes
// private int teacherid;
private Teacher teacher;
private List<Subject> subjects;
public List<Subject> getSubjects() {
return subjects;
}
public void setSubjects(List<Subject> subjects) {
this.subjects = subjects;
}
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
}
Interface
public interface SubjectDao {
//Query a student's information (find the subject studied)
public Student findById(int id);
//Query a subject and the corresponding student name
public Subject findBySubId(int subId);
}
Implementation class
public class SubjectDaoImpl implements SubjectDao {
@Override
public Student findById(int id) {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from student s,subject su,middle m where
s.stuid=m.stuid and su.subid=m.subid and s.stuid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,id);
//Execute sql
resultSet = pps.executeQuery();
Student student = new Student();
List<Subject> subjects=new ArrayList<>();
while (resultSet.next()){
//1. Take out their respective information
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
Subject subject = new Subject();
subject.setSubId(resultSet.getInt("subid"));
subject.setSubName(resultSet.getString("subname"));
subjects.add(subject);
}
//2. Establish the relationship between students and subjects
student.setSubjects(subjects);
return student;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public Subject findBySubId(int subId) {
//Operation database
Connection connection =null;
PreparedStatement pps =null;
ResultSet resultSet =null;
try {
//1. Load drive
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get links
String userName="root";
String passWord="123456";
String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3. Define sql and create pre status channels (send sql statements)
String sql="select * from student s,subject su,middle m where
s.stuid=m.stuid and su.subid=m.subid and su.subid=?";
pps = connection.prepareStatement(sql);
pps.setInt(1,subId);
//Execute sql
resultSet = pps.executeQuery();
Subject subject = new Subject();
List<Student> studentList=new ArrayList<>();
while (resultSet.next()){
//1. Take out their respective information
Student student = new Student();
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
studentList.add(student);
subject.setSubId(resultSet.getInt("subid"));
subject.setSubName(resultSet.getString("subname"));
}
//2. Establish the relationship between students and subjects
subject.setStudentList(studentList);
return subject;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
//5. Close resources
if (resultSet != null) {
resultSet.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
Test class
public static void main(String[] args) {
SubjectDaoImpl subjectDao = new SubjectDaoImpl();
/* Student student = subjectDao.findById(1);
System.out.println(student.getStuName());
List<Subject> subjects = student.getSubjects();
for (Subject subject : subjects) {
System.out.println("\t"+subject.getSubName());
}*/
Subject subject = subjectDao.findBySubId(2);
System.out.println(subject.getSubName());
List<Student> studentList = subject.getStudentList();
for (Student student : studentList) {
System.out.println("\t"+student.getStuName());
}
}
10. Database things
->A set of SQL statements that either execute successfully or fail at the same time. It is an execution unit of database operation.
10.1 transaction overview
Transaction started on
- Connect to the database and execute a DML statement insert, update, or delete
- After the previous transaction, another DML statement is entered
Transaction ended on
- Execute a commit or rollback statement.
- Execute a DDL statement, such as the create table statement. In this case, the commit statement will be executed automatically-
- Execute a DDL statement, such as grant statement. In this case, commit will be executed automatically-
- Disconnect from database
- A DML statement is executed but fails. In this case, a rollback statement is executed for the invalid DML statement
10.2 four characteristics of things
Atomicity
Indicates that all operations within a transaction are a whole, either all successful or all failed-
Consistency
Indicates that when an operation fails in a transaction, all changed data must be rolled back to the state before modification-
Isolation
When a transaction views data, the state of the data is either the state before another concurrent transaction modifies it or the state after another transaction modifies it. The transaction will not view the data in the intermediate state-
Durability
After a persistent transaction is completed, its impact on the system is permanent.
10.3 application of things in JDBC
If the JDBC connection is in auto submit mode, by default, each SQL statement will be submitted to the database after completion.
Transactions enable you to control whether and when changes are applied to the database. It treats a single SQL statement or a group of SQL statements as a logical unit. If any statement fails, the whole transaction will fail.
To enable manual transaction support instead of the automatic commit mode used by the JDBC driver by default, use the setAutoCommit() method of the Connection object. If you pass boolean false to setAutoCommit(), auto commit will be turned off. We can reopen it by passing a Boolean value of true.
10.4 submission and rollback
After completing the change, we will submit the change and then call the commit () method on the connection object, as shown below:
conn.commit();
Otherwise, to roll back the update using a database named conn, use the following code
conn.rollback( );
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
// If there is no error.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback();
}
11.JDBC batch processing
11.1 statement batch processing
The following is a typical sequence of steps for batch processing using statement objects-
- Use the createStatement () method to create a Statement object-
- Use setAutoCommit() to set auto commit to false-
- Use the addBatch () method to add your favorite SQL statements to the batch on the created statement object-
- Use the executeBatch() method to execute all SQL statements on the created statement object-
- Finally, commit all changes using the commit () method.
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
//sql1
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia',
'Ali', 30)";
stmt.addBatch(SQL);
//sql2
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj',
'Kumar', 35)";
stmt.addBatch(SQL);
//sql3
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();
11.2 Preparedstatement batch processing
- Create SQL statements using placeholders.
- Use the preparestatement () method to create a preparestatement object.
- Use setAutoCommit() to set auto commit to false.
- Use the addBatch () method to add your favorite SQL statements to the batch on the created statement object.
- Use the executeBatch() method to execute all SQL statements on the created statement object.
- Finally, commit all changes using the commit () method.
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();