Article directory
- 1. Database Tools
- 2. JDBC – Java Database Connection
- 3. Execute the DML statement
- Fourth, execute the DQL statement
- 5. Association query
- 6. Execute precompiled SQL statements
- Seven, SELECT statement
- 8. Practice creating a table: student1
- 9. Insert 100 pieces of data into the student1 table
- 10. Delete the student whose name is Test20—Test100
- 11. Increase the age of all test students in the student1 table by 1 year
- 12. Modify a user’s password
1. Database tools
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* Database tool class, maintenance database driver loading, connection creation
*/
public class DBUtil {<!-- -->
static {<!-- -->
try {<!-- -->
//When DBUtil is loaded for the first time, load the database driver class
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {<!-- -->
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {<!-- -->
return DriverManager.getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
}
}
2. JDBC–Java database connection
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBC [Java DataBase Connectivity] JAVA database connection
* JDBC is a set of API interfaces provided by SUN, which uses a set of standard interfaces for connecting to databases using JAVA.
* Each database provides a set of JDBC implementation classes for connecting to its own DBMS. And provided this set
* The implementation class is also called the driver (Driver) connected to the DBMS
*
* 1: To load the driver provided by the DBMS manufacturer that needs to be operated (MAVEN directly loads dependencies)
* 2: Operate the database based on the standard JDBC operation process
*/
public class JDBCDemo1 {<!-- -->
public static void main(String[] args) throws ClassNotFoundException, SQLException {<!-- -->
/*
Standard process of JDBC connection to DBMS
1: Load the driver: Class.forName("Driver classes provided by different database vendors")
2: Establish a connection with the database: DriverManager.getConnection()
3: Create a Statement object for executing SQL statements through the Connection object
4: Execute the SQ statement through Statement
5: If you are executing DQL, you can traverse the query result set
*/
//1 The implementation classes of the Driver interface in JDBC have different package names and class names provided by different database vendors
Class.forName("com.mysql.cj.jdbc.Driver");//mysql fixed is this
//2
/*
The DriverManager.getConnection() method needs to pass in three parameters
1: The path to connect to the database (different database path formats are not exactly the same)
2: Username to connect to the database
3: Password to connect to the database
The return value of this method is an instance of Connection (Connection is an interface, which is
A core interface in JDBC used to represent a connection to a database. different database
The manufacturer provides the corresponding implementation class in the driver package)
*/
//URL format -> jdbc: Different databases have their own format part/database name? parameter
Connection connection = DriverManager. getConnection(
// Database name, equivalent to USE tedu
// ||||
// VVVV
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
System.out.println("The database connection is successful!");
/*
Obtain the execution object Statement used to execute the SQL statement through the Connection
The function of the Statement object is to execute the specified SQL statement to the database.
*/
Statement state = connection. createStatement();
/*
Create a table userinfo
Fields: id, username, password, nickname, age
*/
String sql = "CREATE TABLE userinfo(" +
"id INT AUTO_INCREMENT PRIMARY KEY," +
" username VARCHAR(30)," +
"password VARCHAR(30)," +
" nickname VARCHAR(30)," +
" age INT(3)" +
")";
/*
boolean execute(String sql)
This method can execute any type of SQL statement. But both DML and DQL have dedicated methods
For execution, so this method is generally used to specify DDL statements (CREATE, ALTER, DROP)
The return value of this method is true to indicate that there is a query result set after the SQL is executed.
*/
state. execute(sql);
System.out.println("SQL execution completed");
}
}
3. Execute DML statement
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Execute DML statements (INSERT, UPDATE, DELETE)
*/
public class JDBCDemo2 {<!-- -->
public static void main(String[] args) throws ClassNotFoundException {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
try(
Connection connection = DriverManager. getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
) {<!-- -->
Statement state = connection. createStatement();
String sql = "INSERT INTO student1(name,age,class_id) " +
" VALUES('Zhang San',12,1)";
/*
int executeUpdate(String sql)
A method specially used to execute DML statements, and the return value is how many records in the table are affected after executing the SQL
*/
int count = state.executeUpdate(sql);//INSERT inserts 1 piece of data
if(count>0){<!-- -->//At least one piece of data in the table is affected after execution
System.out.println("affected " + count + " pieces of data in the table");
}
System.out.println("Completed");
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
4. Execute DQL statement
package jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Execute DQL statement (SELECT)
*/
public class JDBCDemo3 {<!-- -->
public static void main(String[] args) {<!-- -->
/*
View the id, name, age, salary, bonus, gender of all teachers
SELECT id,name,age,salary,comm,gender
FROM teacher
*/
try(
Connection connection = DBUtil. getConnection();
){<!-- -->
Statement state = connection. createStatement();
/*
If a field in the SELECT clause is a calculation expression or function, then it should be the field
Specify an alias.
*/
String sql = "SELECT id,name,age,salary*12 sal,comm,gender" +
"FROM teacher";
System.out.println(sql);
/*
Statement
ResultSet executeQuery(String sql)
A method for a dedicated user to execute a DQL statement. The returned ResultSet indicates that after executing the DQL
The resulting query result set. Get the query content by traversing the result set
*/
ResultSet rs = state. executeQuery(sql);
/*
ResultSet traverses the important methods in the result set:
boolean next()
Let the result set pointer move down a piece of data, and return if the result set has the next piece of data
true otherwise returns false.
Note: The pointer is before the first record in the result set by default.
Get the method corresponding to the field value of the record in the result set currently pointed to by the pointer:
String getString(int c)
Get the value of the string type field, c indicates the number field of the record in the result set
1 means the first field, 2 means the second field and so on
String getString(String cname)
Get the value of the string type field, cname indicates the field value of the specified name of the record in the result set
int getInt(int c)
int getInt(String cname)
Get int type field value
There are also methods to obtain floating point types, date types, etc...
*/
while(rs.next()){<!-- -->
// get id
// int id = rs. getInt(1);
// String name = rs. getString(2);
// int age = rs. getInt(3);
// int salary = rs. getInt(4);
// int comm = rs. getInt(5);
// String gender = rs. getString(6);
int id = rs.getInt("id");
String name = rs. getString("name");
int age = rs.getInt("age");
//If the field of SELECT in DQL has an alias, here you need to use the alias to get the corresponding field of the result set
int salary = rs. getInt("sal");
int comm = rs. getInt("comm");
String gender = rs. getString("gender");
System.out.println(id + "," + name + "," + age + "," + salary + "," + comm + "," + gender);
}
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
5. Association query
package jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Association query
*/
public class JDBCDemo4 {<!-- -->
public static void main(String[] args) {<!-- -->
try (
Connection connection = DBUtil. getConnection();
){<!-- -->
Statement statement = connection. createStatement();
//Check the name, age, gender, class name, class teacher name of the students in class 1, grade 1
String sql = "SELECT s.name sname,s.age sage,s.gender sgender,c.name cname,t.name tname " +
"FROM student s" +
"JOIN class c ON s.class_id=c.id" +
"JOIN teacher t ON c.teacher_id=t.id" +
"WHERE c.name='Class 1, Grade 1'";
ResultSet rs = statement. executeQuery(sql);
while(rs.next()){<!-- -->
String sname = rs. getString("sname");
int sage = rs.getInt("sage");
String sgender = rs. getString("sgender");
String cname = rs. getString("cname");
String tname = rs. getString("tname");
System.out.println(sname + "," + sage + "," + sgender + "," + cname + "," + tname);
}
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
6. Execute precompiled SQL statements
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Execute precompiled SQL statements
* advantage:
* 1: Avoid the tedious splicing of SQL language
* 2: Avoid the problem of SQL injection
* 3: Good performance when executing SQL with the same semantics in large batches (same semantics, different values)
*
* In actual development, our CRUD recommends using precompiled SQL to execute
*/
public class JDBCDemo5 {<!-- -->
public static void main(String[] args) {<!-- -->
try (
Connection conn = DBUtil. getConnection();
){<!-- -->
//Insert data into the userinfo table
String sql = "INSERT INTO userinfo" +
"(username,password,nickname,age)" +
"VALUES" +
"(?,?,?,?)";//In precompiled SQL, "?" can be used to replace the value first
/*
At this point Connection will now send the SQL to the database to make it generate the SQL
Execution plan, indicating the semantics of the SQL.
The database understands that it is necessary to insert data into the userinfo table and insert content into the 4 fields
It's just that the SQL can't really be executed because there is no data yet.
*/
PreparedStatement ps = conn. prepareStatement(sql);
//Use PreparedStatement to set the proper values for the four reserved "?"
ps.setString(1,"Wang Kejing");//The value of the first? is "Wang Kejing" of string type
ps.setString(2,"123456");
ps.setString(3,"Kejing");
ps.setInt(4,18);//The value of the fourth? is the number 18 of type int
// At this time, only 4? corresponding values are passed to the database during execution
int count = ps. executeUpdate();
if(count>0){<!-- -->
System.out.println("insert successfully");
}
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
7. SELECT statement
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* SELECT statement
*/
public class JDBCDemo6 {<!-- -->
public static void main(String[] args) {<!-- -->
try (
Connection conn = DBUtil. getConnection();
){<!-- -->
String sql = "SELECT id, username, password, nickname, age " +
"FROM userinfo" +
"WHERE username=? AND password=?";
PreparedStatement ps = conn. prepareStatement(sql);
ps.setString(1,"Wang Kejing");
// ps.setString(2,"666666");
ps.setString(2,"' OR '1'='1");
ResultSet rs = ps. executeQuery();
if(rs.next()){<!-- -->
System.out.println("Successful login");
}else{<!-- -->
System.out.println("Login failed");
}
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
8. Practice creating table: student1
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Practice creating table: student1
* fields:
* id int
* name varchar
* age int
* class_id int
*
*/
public class Test {<!-- -->
public static void main(String[] args) throws ClassNotFoundException {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");//mysql fixed is this
//URL format -> jdbc: Different databases have their own format part/database name? parameter
try(
Connection connection = DriverManager. getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
) {<!-- -->
Statement state = connection. createStatement();
String sql = "CREATE TABLE student1(" +
"id INT AUTO_INCREMENT PRIMARY KEY," +
" name VARCHAR(30)," +
" age INT(3)," +
" class_id INT" +
")";
state. execute(sql);
System.out.println("Completed");
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
9. Insert 100 pieces of data into the student1 table
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
/**
* Insert 100 pieces of data into the student1 table. Name format: test1---test100
* Age between 10-15 years old. Class number between 1-10
*
* A random interval n, m n: offline m is online
* random.nextInt(m-n) + n does not include the upper line value m
* random.nextInt(m + 1-n) + n contains the upper limit m
*/
public class Test1 {<!-- -->
public static void main(String[] args) throws ClassNotFoundException {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
try(
Connection connection = DriverManager. getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
) {<!-- -->
Statement state = connection. createStatement();
Random r = new Random();
for(int i=1;i<=100;i ++ ) {<!-- -->
String name = "test" + i;
int age = r.nextInt(6) + 10;
int classId = r.nextInt(10) + 1;
String sql = "INSERT INTO student1(name,age,class_id) " +
"VALUES('" + name + "'," + age + "," + classId + ")";
System.out.println(sql);
/*
int executeUpdate(String sql)
A method specially used to execute DML statements, and the return value is how many records in the table are affected after executing the SQL
*/
int count = state.executeUpdate(sql);//INSERT inserts 1 piece of data
if (count > 0) {<!-- -->//At least one piece of data in the table is affected after execution
System.out.println("affected " + count + " pieces of data in the table");
}
}
System.out.println("Completed");
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
10. Delete students named Test20-Test100
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Delete the student whose name is Test20---Test100
*/
public class Test2 {<!-- -->
public static void main(String[] args) throws ClassNotFoundException {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
try(
Connection connection = DriverManager. getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
) {<!-- -->
Statement state = connection. createStatement();
String sql = "DELETE FROM student1" +
" WHERE name NOT LIKE 'test_' " +
" AND name NOT LIKE 'test1_'";
/*
int executeUpdate(String sql)
A method specially used to execute DML statements, and the return value is how many records in the table are affected after executing the SQL
*/
int count = state.executeUpdate(sql);//INSERT inserts 1 piece of data
if(count>0){<!-- -->//At least one piece of data in the table is affected after execution
System.out.println("affected " + count + " pieces of data in the table");
}
System.out.println("Completed");
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
11. Increase the age of all test students in the student1 table by 1 year
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Increase the age of all test students in the student1 table by 1 year
* UPDATE student1
* SET age=age + 1
* WHERE name LIKE 'test%'
*/
public class Test3 {<!-- -->
public static void main(String[] args) throws ClassNotFoundException {<!-- -->
Class.forName("com.mysql.cj.jdbc.Driver");
try(
Connection connection = DriverManager. getConnection(
"jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai & amp;rewriteBatchedStatements=true",
"root",
"root");
) {<!-- -->
Statement state = connection. createStatement();
String sql = "UPDATE student1 " +
"SET age=age + 1 " +
"WHERE name LIKE 'test%'";
/*
int executeUpdate(String sql)
A method specially used to execute DML statements, and the return value is how many records in the table are affected after executing the SQL
*/
int count = state.executeUpdate(sql);//INSERT inserts 1 piece of data
if(count>0){<!-- -->//At least one piece of data in the table is affected after execution
System.out.println("affected " + count + " pieces of data in the table");
}
System.out.println("Completed");
}catch(SQLException e){<!-- -->
e.printStackTrace();
}
}
}
12. Modify a user’s password
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Modify a user's password
* For example:
* Change the password of "Wang Kejing" to "666666"
*
* The corresponding precompiled SQL is:
* UPDATE user info
* SET password=?
* WHERE username=?
*/
public class Test4 {<!-- -->
public static void main(String[] args) {<!-- -->
try (
Connection conn = DBUtil. getConnection();
){<!-- -->
String sql = "UPDATE userinfo " +
"SET password=? " +
"WHERE username=? ";
PreparedStatement ps = conn. prepareStatement(sql);
ps.setString(1,"666666");
ps.setString(2,"Wang Kejing");
int count = ps. executeUpdate();
if(count>0){<!-- -->
System.out.println("modified successfully");
}else{<!-- -->
System.out.println("Modification failed");
}
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
