JDBC is an API for executing sql statements, which enables developers to operate different databases. Let's introduce my study of JDBC. Don't spray when Xiaobai is learning.
I General steps for using JDBC
1. Import the jar package. You can download it from the corresponding database official website. Copy the jar package to the directory and right-click add as librar y
2. Register driver class forName("com.mysql.jdbc.Driver");
3. Get the connection object of the database. DriverManager.getConnection("url"，"user"，"password")
url: Specify the path syntax of the connection to mysql take as an example jdbc:mysql://ip address (domain name): port number / database name
Local machine url Can be abbreviated jdbc:mysql:///Database nameUser: the user name of the database
Password: the password of the database
4. Write sql statement String sql = "sql statement";
5. Create an object that wants to send sql statements to the database
It is recommended to learn createsttement(); Learn preparestitem(); Study from shallow to deep
6. Execute sql statement
7. Release resources
close()
2, JDBC tool class (presumably, we have also found that the code repetition rate is too high in the process of learning JDBC, which leads to the poor readability of the code. Therefore, JDBC tool class plays an important role in learning java.)
Every time you use JDBC, you have to write lengthy code segments, which is not in line with the concept of reuse. Therefore, you need to write a separate class and write the general JDBC operations into a class, which is convenient for reuse and code simplification.
1. Traditional jdbc tool class (DBUtil.java) (when changing the database, the loading driver code should be changed. The code should also be modified when different accounts are used. The cross platform type is not high and is not recommended)
import java.sql.*;
public class DBUtil {
public static Connection getcon() {
Connection con = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql:///b","root","123456");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void close(Connection con, Statement stmt) {
if (stmt != null){
try {
stmt.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
if (con != null) {
try {
con.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
public static void close(Connection con, Statement stmt , ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
if (con != null) {
try {
con.close();
} catch (SQLException exception) {
exception.printStackTrace();
}
}
}
}
}
}2. Standard jdbc tool class (when changing the database, there is no need to modify the code, just modify the content in the configuration file. Recommended)
1) Register drivers and get links
First, create a file and put it into the project. When you want to change other connections, you only need to modify the configuration file without modifying the class file and recompiling. The contents of the accompanying documents are as follows
driver=Database driven path url=url Connection string user=user name password=password
Then you need to take out the value in the key value pair in the configuration file and register the driver. Because it only takes one time to drive and take out the configuration file, it is written into the static code block and handled at the beginning of the program operation, without redundant execution in the future.
// Special reminder: when defining variables, they should be defined as static variables
private static String url;
private static String user;
private static String password;
private static String driver;
// Each time we connect to the database, we only use the static variable of the driver once, so we recommend using the static code to register the driver and assign a value to the static variable
static{
try {
// Create Properties collection class
Properties pro = new Properties();
// Get the file under src path and use ClassLoader classloader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
// The URL locates the absolute path of the file
URL res = classLoader.getResource("jdbc.properties");
// Get string path
String path = res.getPath();
// read file
pro.load(new FileReader(path));
// Assign values to static variables
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
// 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);
}2) Release resources
"Since the execution of sql statements can be divided into two cases: whether there is a result set or not, the release of resources can be divided into two cases, and then there are two overload forms.
public static void close(Statement stmt, Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}