1. What is JDBC?
JDBC full name:
Java DataBase Connection
Copy the codeThis means using Java code to connect to the database.
But here’s the problem. There are a bunch of databases in the world like Mysql, Oracle, mongodb, etc., and they all use different methods.
Do I need to write different Java programs to connect to different databases? It’s too much trouble.
To solve this problem, SUN, the company that developed the JDK, decided to specify an interface that was the specification for using Java to connect to a database, essentially JDBC.
The JDBC interface implementation class is written by the database vendor, and they package the implementation class into JAR packages after writing.
This JAR package is a driver (for example, the computer needs to install the network adapter driver to connect to the Internet). After downloading this driver, we put it into our project.
Then write Java code to complete the database connection information required by the JAR package, and then connect to the database corresponding to the driver.
We learned earlier from object-oriented studies that interfaces embody polymorphism. SUN designed a JDBC specification without paying too much attention to how database vendors implement the interface, which greatly improved the scalability of the program.
2. Import the JAR package
Here we take connecting to Mysql as an example.
- Place the JAR package that connects to the mysql database in the lib directory.
Note: The JAR package is the driver that connects to the database. Each database vendor packages its own CODE implementing JDBC into JAR packages.
- Add jar packages as libraries to your project.
Note: Since the JAR package is the compiled Java code, this is equivalent to adding Java code to the project.
Select the lib directory and right-click Add as a Library.
3. Development steps
- Load the database driver
Class.forName("com.mysql.jdbc.Driver");
Copy the codeNote: The driver name must correspond to the database version.
Mysql5 version:
Class.forName("com.mysql.jdbc.Driver");
Copy the codeMysql8 version:
Class.forName("com.mysql.cj.jdbc.Driver");
Copy the code- Create a connection to the database
// 2. Obtain the database connection
String url = "jdbc:mysql://localhost:3306/course_price";
String user = "root";
String password = "12345678";
Connection conn = DriverManager.getConnection(url, user, password);
Copy the codeThree parameters are required to create a connection to the database:
url,user,password
Copy the codeTo connect to the database, the first need to know what type of database is the database, the second need to know the connection of the database name. So the url format is:
JDBC: database type ://localhost:3306/ database nameCopy the codeWith the URL, you also need to know the database account password. So with these three parameters, you can connect to the database through a Java program.
- Test the connection
public static void main(String[] args) {
Connection conn = null;
try {
// 1. Load the database driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Obtain the database connection
String url = "jdbc:mysql://localhost:3306/course_price";
String user = "root";
String password = "12345678";
Create a connection to the database
conn = DriverManager.getConnection(url, user, password);
if(null! =conn){ System.out.println("Database connection successful");
}else{
System.out.println("Database connection failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
// release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeTest results:
Note: Connection, PrepareStatement, and ResultSet all occupy resources and need to be released/closed.
- Common mistakes
The account or password is incorrect
Access denied for user 'root'@'localhost' (using password: YES)
Copy the codeDatabase name error
Unknown database 'course_pric'
Copy the codeDatabase type error in URL
No suitable driver found for jdbc:mysq://localhost:3306/course_price
Copy the code4. Read the configuration file information
When we are developing, we usually write the database connection information into the configuration file. This way, if the connection information changes, just change the information in the configuration file.
4.1 Creating the jdbc.properties file
url=jdbc:mysql://localhost:3306/course_price
user=root
password=12345678
driver:com.mysql.jdbc.Driver
Copy the code
4.2 Loading configuration File Information
// 1. Load the configuration file
Properties pro=new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url=pro.getProperty("url");
String user=pro.getProperty("user");
String password=pro.getProperty("password");
String driver=pro.getProperty("driver");
Copy the code4.3 Complete Code
public static void main(String[] args) {
Connection conn = null;
try {
// 1. Load the configuration file
Properties pro=new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url=pro.getProperty("url");
String user=pro.getProperty("user");
String password=pro.getProperty("password");
String driver=pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
conn = DriverManager.getConnection(url, user, password);
if(null! =conn){ System.out.println("Database connection successful");
}else{
System.out.println("Database connection failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
//5. Release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the code5. Add, delete, modify and check
5.1 Database Operation Objects
After connecting to the database, we can use Java procedures to operate the database and ravage the data in the database.
So how do you manipulate data in a database? In essence, you use Java programs to execute SQL statements and return the results of the execution.
The database connection object we created, Connection, has a little brother called a database action object, which is dedicated to executing SQL statements and returning execution results.
There are two types of this guy:
The Statement and PrepareStatement.Copy the codeStatement is used to concatenate SQL statements and then compile them. Therefore, SQL injection may occur.
A PreparedStatement is a prepared SQL statement (SQL with placeholders) that is then assigned a value to prevent SQL injection.
Because PreparedStatement is efficient and can prevent SQL injection, we use the PreparedStatement object for adding, deleting, modifying, and querying.
5.2 Querying Data
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1. Load the configuration file
Properties pro = new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String driver = pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
conn = DriverManager.getConnection(url, user, password);
// 5. SQL statements
String sql = "select * from user";
// 6. Create an object to execute SQL
ps = conn.prepareStatement(sql);
// 7. Execution result
rs = ps.executeQuery();
while (rs.next()) {
System.out.println("id:" + rs.getString("id"));
System.out.println("name:" + rs.getString("name"));
System.out.println("-- -- -- -- -- -"); }}catch (Exception e) {
e.printStackTrace();
} finally {
//8. Release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}if(ps ! =null) {
try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}if(rs ! =null) {
try {
rs.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeNote:
-
- ResultSet indicates the result of execution
-
- While (resultset.next ()) means that if there is data in the result of the execution, the data is iterated continuously.
Execution Result:
5.3 Adding Data
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. Load the configuration file
Properties pro = new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String driver = pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
conn = DriverManager.getConnection(url, user, password);
// 5. SQL statements
String sql = "insert into user(id,name) values(? ,?) ";
// 6. Create an object to execute SQL
ps = conn.prepareStatement(sql);
/ / 7. Give? The assignment
ps.setInt(1.5);
ps.setString(2."Zhang Wuji");
Execute SQL
int count = ps.executeUpdate();
if (count > 0) {
System.out.println("Added successfully");
} else {
System.out.println("Add failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
//9. Release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}if(ps ! =null) {
try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeExecution Result:
5.4 Modifying Data
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. Load the configuration file
Properties pro = new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String driver = pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
conn = DriverManager.getConnection(url, user, password);
// 5. SQL statements
String sql = "update user set name = ? where id = ?";
// 6. Create an object to execute SQL
ps = conn.prepareStatement(sql);
/ / 7. Give? The assignment
ps.setString(1."Zhou Zhiruo");
ps.setInt(2.5);
Execute SQL
int count = ps.executeUpdate();
if (count > 0) {
System.out.println("Modified successfully");
} else {
System.out.println("Modification failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
//9. Release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}if(ps ! =null) {
try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeExecution Result:
5.5 Deleting Data
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. Load the configuration file
Properties pro = new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String driver = pro.getProperty("driver");
// 3. Load the database driver
Class.forName(driver);
// 4. Create a database connection
conn = DriverManager.getConnection(url, user, password);
// 5. SQL statements
String sql = "delete from user where id = ?";
// 6. Create an object to execute SQL
ps = conn.prepareStatement(sql);
/ / 7. Give? The assignment
ps.setInt(1.4);
Execute SQL
int count = ps.executeUpdate();
if (count > 0) {
System.out.println("Deleted successfully");
} else {
System.out.println("Delete failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
//9. Release resources
if(conn ! =null) {
try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}if(ps ! =null) {
try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeExecution Result:
6. Tools
From the above example, we can see that each operation has to write a bunch of information to connect to the database, and then release the resources after the operation. Can we simplify this?
Yes, encapsulate database connections as utility classes.
Static methods can be called using the class name, so they are particularly useful for utility classes.
JDBC utility classes:
public class JDBCUtils {
private static String user;
private static String password;
private static String url;
private static String driver;
static {
// The static code block only needs to be loaded once to read the resource file
try {
// 1. Load the configuration file
Properties pro = new Properties();
pro.load(new FileReader("resource/jdbc.properties"));
// 2. Obtain the database connection information from the configuration file
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
// 3. Create a database connection driver
Class.forName(driver);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch(ClassNotFoundException e) { e.printStackTrace(); }}// 4. Obtain the connection object
public static Connection getConnection(a) throws SQLException {
return DriverManager.getConnection(url, user, password);
}
// 5. Release resources
public static void close(PreparedStatement ps, Connection conn) {
close(null, ps, conn);
}
// 6. Free resources (overload)
public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
if (null! = rs) {try {
rs.close();
} catch(SQLException e) { e.printStackTrace(); }}if (null! = ps) {try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}if (null! = conn) {try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeWith the JDBC connection utility class, the code can be very clean, for example:
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. Obtain the database connection object
conn = JDBCUtils.getConnection();
// 2. SQL statement
String sql = "delete from user where id = ?";
// 3. Create an object to execute SQL
ps = conn.prepareStatement(sql);
/ / 4 to? The assignment
ps.setInt(1.4);
// 5. Execute SQL
int count = ps.executeUpdate();
if (count > 0) {
System.out.println("Deleted successfully");
} else {
System.out.println("Delete failed"); }}catch (Exception e) {
e.printStackTrace();
} finally {
// 6. Release resourcesJDBCUtils.close(ps, conn); }}Copy the code