JDBC学习(二)---将JDBC优化为工具包/数据库的增删改查

JDBC学习(二)—将JDBC优化为工具包/数据库的增删改查

01 JDBC的优化

为了优化对JDBC的使用，避免代码的重复率，将创建JDBC的繁琐过程简化为工具包中的各个方法。具体步骤如下：

• 新建配置文件：内容包括创建连接时所需的驱动，数据库的用户名和密码，以及url

  • 在src下建立db.properties，内容如下

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

• 在src下新建包，这里命名为utils，在该包下新建类，这里命名为JDBCUtiles

  • 注意事项：为了方便使用，工具包的成员变量和方法一般采用static修饰

  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 {
              //以输入流的形式加载配置文件
              InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
              Properties properties = new Properties();
              properties.load(stream);
              //读取配置文件
              driver = properties.getProperty("driver");
              username = properties.getProperty("username");
              password = properties.getProperty("password");
              url = properties.getProperty("url");
              //加载数据库驱动
              Class.forName(driver);
          } catch (IOException e) {
              e.printStackTrace();
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
          }
      }
  
      //获取数据库连接
      public static Connection getConnect() throws SQLException {
          return DriverManager.getConnection(url,username,password);
      }
  
      //释放资源
      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 数据库的增删改查

为了方便测试，这里引入Junit。Junit的导包十分方便，在IDEA类的编辑界面输入：@Test，第一次使用Junit该语句会报红，按alt+enter选择add 'Junit4'to classpath,IDEA会自动下载Junit并且导入lib目录

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.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(5,'wangwu','123456','wangwu@sina.com','1979-12-04');";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数
            //5.查看结果
            System.out.println("增加了"+i+"行数据");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }

    }

    @Test
    public void delete() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "delete from users where id = 4";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数
			//5.查看结果
            if (i>0){
                System.out.println("删除成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }

    }

    @Test
    public void update() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "update users set name = 'lily' where id = 4";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数
			//5.查看结果
            if (i>0){
                System.out.println("修改成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }


    }

    @Test
    public void query() {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();

            //3.编写Sql语句
            String sql = "select * from users";
            //4.执行sql语句
            resultSet = statement.executeQuery(sql);
			//5.查看结果
            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.释放资源
            JDBCUtils.closeAll(resultSet,statement,connection);
        }

    }

}

---