JDBC Study ( Two )— take JDBC Optimized for toolkit / Add, delete, change and check the database

01 JDBC The optimization of the

In order to optimize the JDBC Use , Avoid code duplication , Will be created JDBC The tedious process is simplified into various methods in the toolkit . The specific steps are as follows ：

• New configuration file ： It includes the drivers needed to create the connection , User name and password of the database , as well as url

  • stay src Set up under the db.properties, The contents are as follows

  driver = com.mysql.jdbc.Driver
  username = root
  password = 123456
  url = jdbc:mysql://localhost:3306/jdbcstudy?useSSL=true
  

• stay src Next new package , It’s called utils, Create a new class under the package , It’s called JDBCUtiles

  • matters needing attention ： For ease of use , The member variables and methods of the toolkit generally adopt static modification

  package com.hooi.utils;
  import java.io.IOException;
  import java.io.InputStream;
  import java.sql.*;
  import java.util.Properties;
  public class JDBCUtils {
  public static String driver=null;
  public static String username=null;
  public static String password=null;
  public static String url=null;
  static {
  try {
  // Load the configuration file as an input stream
  InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
  Properties properties = new Properties();
  properties.load(stream);
  // Read configuration file
  driver = properties.getProperty("driver");
  username = properties.getProperty("username");
  password = properties.getProperty("password");
  url = properties.getProperty("url");
  // Load database driver
  Class.forName(driver);
  } catch (IOException e) {
  e.printStackTrace();
  } catch (ClassNotFoundException e) {
  e.printStackTrace();
  }
  }
  // Get database connection
  public static Connection getConnect() throws SQLException {
  return DriverManager.getConnection(url,username,password);
  }
  // Release resources
  public static void closeAll(ResultSet resultSet, Statement statement,Connection connection){
  if (resultSet!=null){
  try {
  resultSet.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  }
  if (statement!=null){
  try {
  resultSet.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  }
  if (connection!=null){
  try {
  resultSet.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  }
  }
  }
  

02 Add, delete, change and check the database

For testing purposes , Introduce here Junit.Junit The guide bag is very convenient , stay IDEA Class editor input ：@Test, For the first time Junit The statement will be red , Press alt+enter choice add 'Junit4'to classpath,IDEA Will automatically download Junit And the import lib Catalog

package com.hooi.demo;
import com.hooi.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDemo {
@Test
public void insert() {
Connection connection = null;
Statement statement = null;
try {
//1. Get database connection
connection = JDBCUtils.getConnection();
//2. establish statement object
statement = connection.createStatement();
//3. To write Sql sentence
String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(5,'wangwu','123456','[email protected]','1979-12-04');";
//4. perform sql sentence
int i = statement.executeUpdate(sql); // Returns the number of affected rows
//5. View results
System.out.println(" Added "+i+" Row data ");
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5. Release resources
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void delete() {
Connection connection = null;
Statement statement = null;
try {
//1. Get database connection
connection = JDBCUtils.getConnection();
//2. establish statement object
statement = connection.createStatement();
//3. To write Sql sentence
String sql = "delete from users where id = 4";
//4. perform sql sentence
int i = statement.executeUpdate(sql); // Returns the number of affected rows
//5. View results
if (i>0){
System.out.println(" Delete successful ");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void update() {
Connection connection = null;
Statement statement = null;
try {
//1. Get database connection
connection = JDBCUtils.getConnection();
//2. establish statement object
statement = connection.createStatement();
//3. To write Sql sentence
String sql = "update users set name = 'lily' where id = 4";
//4. perform sql sentence
int i = statement.executeUpdate(sql); // Returns the number of affected rows
//5. View results
if (i>0){
System.out.println(" Modification successful ");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1. Get database connection
connection = JDBCUtils.getConnection();
//2. establish statement object
statement = connection.createStatement();
//3. To write Sql sentence
String sql = "select * from users";
//4. perform sql sentence
resultSet = statement.executeQuery(sql);
//5. View results
while (resultSet.next()){
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
System.out.println(resultSet.getString("email"));
System.out.println(resultSet.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
JDBCUtils.closeAll(resultSet,statement,connection);
}
}
}