jdbc模板

单独在一个类上实现jdbc

package com.bjpowernode.jdbc;

import com.mysql.jdbc.Driver;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;

public class JDBCTest04 {
    public static void main(String[] args) {
        // 初始化一个界面
        Map<String, String> userLoginInfo = initUI();
        //验证登录名和密码
        boolean loginSuccess = login(userLoginInfo);
        System.out.println(loginSuccess ? "登录成功" : "登陆失败");
    }

    /**
     *  用户登录
     * @author:luo
     * @date: 2021/8/5
     * @description:
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        //添加标记
        boolean loginSuccess = false;

        String loginName = userLoginInfo.get("loginName");
        String loginPaw = userLoginInfo.get("loginPassword");

        //JDBC代码
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        //反射机制实现数据读取
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        try{
            try {
                //1、注册驱动
                //Class.forName("com.mysql.jdbc.Driver");
                Class.forName(driver);

                //2、获取连接mysql数据库
                //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=UTC","root","asddsa12321");
                conn = DriverManager.getConnection(url,user,password);

                //3、获取数据ku操作对象
                stmt = conn.createStatement();

                //4、执行SQL语句
                String sql = "select * from t_user where loginName = '"+ loginName +"' and loginPaw = '"+ loginPaw +"'";
                //int count = stmt.executeUpdate(sql);
                //5、处理查询结果
                rs = stmt.executeQuery(sql);
                if (rs.next()){
                    //登录成功
                    loginSuccess = true;
                }
                /*用户名：
                fdsa
                密码：
                fdsa' or '1'='1
                登录成功*/


            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally{
            //6、释放资源
            //从小到大依次关闭
            try{
                if ( rs != null)
                    rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            try{
                if ( stmt != null)
                    stmt.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            try{
                if ( conn != null)
                    conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return loginSuccess;
    }

    /**
     *  初始化界面
     * @author:luo
     * @date: 2021/8/5
     * @description:
     */
    private static Map<String, String> initUI() {
        Scanner scanner = new Scanner(System.in);
        System.out.println("用户名：");
        String loginName = scanner.nextLine();

        System.out.println("密码：");
        String loginPassword = scanner.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName",loginName);
        userLoginInfo.put("loginPassword",loginPassword);
        return userLoginInfo;
    }

}

使用工具类

将重复使用的代码封装成一个工具类

package com.bjpowernode.utils;

import java.sql.*;

/**
*  
* @author:luo
* @date: 2021/8/6
* @description:jdbc工具类，简化jdbc编程
*/
public class DBUtil {
    private DBUtil(){}
// 静态方法 在类加载的时候执行一次，并且是只执行一次
    static {
        try {
            // 1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @return 连接数据库
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=UTC"
        ,"root","asddsa12321");
    }

    /**
     * 释放资源
     * @param conn  连接对象
     * @param ps  数据库操作对象
     * @param rs    结果集
     */

    public static void close(Connection conn, Statement ps,ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

对应存在一个类负责调用工具类

package com.bjpowernode.jdbc;

import com.bjpowernode.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCTest11 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

//            ？号表示一个占位符
            String sql = "update emp set sal = sal*1.1 where job = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"manager");
            int count = ps.executeUpdate();
            System.out.println(count);
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,null);
        }
    }
}

通是可以使用反射机制+配置性文本实现数据的传入，这样当需要改变数据的时候只用改配置性文本中就可以了，不必再改动源代码

 //反射机制实现数据读取
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

//2、获取连接mysql数据库
                //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=UTC","root","asddsa12321");
                conn = DriverManager.getConnection(url,user,password);