JDBC中级实现--数据库连接四要素的抽取与动态获取

1.数据库连接四要素不应该写死在代码中，扩展性不高，应该抽取到配置文件中动态读取：

扩展文件名.properties

DRIVER_CLASS_NAME = com.mysql.jdbc.Driver
URL = jdbc:mysql:///mysql_jdbc
USER = root
PASSWORD = root

2.动态读取操作，只需要执行一次，所以解析代码写在静态代码块

  Class.forName(),注册也只需要执行一次，所以也放在静态代码块

static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("jdbc.properties"));
            DRIVER_CLASS_NAME = properties.getProperty("DRIVER_CLASS_NAME");
            URL = properties.getProperty("URL");
            USER  = properties.getProperty("USER");
            PASSWORD = properties.getProperty("PASSWORD");
//            System.out.println(DRIVER_CLASS_NAME+"-"+URL+"-"+USER+"-"+PASSWORD);
            Class.forName(DRIVER_CLASS_NAME);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.为数据库的连接专门写个工具类用于获取连接和释放资源,以及公共代码

/*
    工具类封装步骤：
        1.jdbc中每次都要写注册获取连接和资源释放
        2.参数数据，不应该直接写在代码中，直接抽取到最上面，便于后期开发维护
        3.数据库连接四要素，不应该写死在代码中，扩展性不高，应该抽取到配置文件中动态读取
        4.动态读取操作，只需要执行一次，所以解析代码写在静态代码块
        5.class.forName(),注册也只需要执行一次，所以也放在静态代码块
 */
public class JdbcUtil {

    public static  String DRIVER_CLASS_NAME ;
    public static  String URL ;
    public static  String USER ;
    public static  String PASSWORD ;

    //如果要保证一部分代码仅仅动态读取一次，则使用静态代码块
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("jdbc.properties"));

             DRIVER_CLASS_NAME = properties.getProperty("DRIVER_CLASS_NAME");
             URL = properties.getProperty("URL");
             USER  = properties.getProperty("USER");
             PASSWORD = properties.getProperty("PASSWORD");
//            System.out.println(DRIVER_CLASS_NAME+"-"+URL+"-"+USER+"-"+PASSWORD);
            Class.forName(DRIVER_CLASS_NAME);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }


    //获取连接
    public static Connection getConnection()  {
        Connection connection = null;
        try {
            //ctrl+Alt+C抽取
            connection = DriverManager.getConnection(URL, USER,PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }


    //资源释放
    public static  void release(ResultSet resultSet, Statement statement,Connection connection){
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            resultSet = null;//避免内存泄漏
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            statement = null;//避免内存泄漏
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connection = null;//避免内存泄漏
        }
    }
}

4.用户登录实例

package Login_01;

import java.sql.*;
import java.util.Scanner;

public class LoginDemo {
    public static void main(String[] args) throws SQLException {
        //提醒用户输入用户名
        System.out.println("请输入用户名：");
        Scanner scanner = new Scanner(System.in);
        String username = scanner.nextLine();

        System.out.println("请输入登录密码：");
        Scanner scanner1 = new Scanner(System.in);
        String password = scanner1.nextLine();
        //提醒用户输入密码


        Connection connection = JdbcUtil.getConnection();
        Statement statement = connection.createStatement();

        String sql = "SELECT count(*) FROM USER WHERE username = '"+username+"' AND password = '+password+' ;";
        ResultSet resultSet = statement.executeQuery(sql);
       
        int count = 0;
        while (resultSet.next()) {
            count = resultSet.getInt("count(*)");
        }
        System.out.println(count >0 ? "登录成功" :" 登录失败");
        JdbcUtil.release(resultSet,statement,connection);
    }
}

简单测试DemoTest: 

import util.JDBCUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcUtilTest {
    public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "Update user set password = '54321'";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        JDBCUtils.release(null,statement,connection);
    }
}