博客

抽取JDBC工具类：JDBCUtils

创建工具类 JDBCUtils

package cn.itcast.utils;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    /*
        不想传递参数，还得保证工具类的通用性。
        配置文件：jdbc.properties
            url=
            user=
            password=

            文件的读取，只需要读取一次即可拿到这些值。使用静态代码块

     */
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {


        try {
            //读取资源文件，获取值

            //1.Properties集合类
            Properties pro = new Properties();

            //获取src路径下的文件方式---->ClassLoader类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            System.out.println(path);//获取绝对路径
            //2.加载文件:
            //写绝对路径不合适;
            // pro.load(new FileReader("src/jdbc.properties"));

            pro.load(new FileReader(path));

            //3.获取数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");

            //4.注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }


    }

    /*
        获取连接
     */
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url, user, password);
    }

    /*
        释放资源
     */
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }

            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();
                }

                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }
    }
}

创建资源文件

jdbc.properties

url=jdbc:mysql://localhost:3306/learn?&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true
user=root
password=root
driver=com.mysql.cj.jdbc.Driver

数据库表：

列名：id，NAME,balance

创建数据库表的对象

package cn.itcast.main;

public class account1 {
    private int id;
    private String NAME;
    private String balance;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getNAME() {
        return NAME;
    }

    public void setNAME(String NAME) {
        this.NAME = NAME;
    }

    public String getBalance() {
        return balance;
    }

    public void setBalance(String balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "account1{" +
                "id=" + id +
                ", NAME='" + NAME + '\'' +
                ", balance='" + balance + '\'' +
                '}';
    }
}

执行Demo

package cn.itcast.main;

import cn.itcast.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
   定义一个方法： 查询emp表的数据，封装为对象，然后装载集合，返回


 */
public class JDBCDemo09_insert {
    public static void main(String[] args) {
        List<account1> list=new JDBCDemo09_insert().findAll2();
        System.out.println(list);
    }

    /*
        演示JDBC的工具类
     */
        public List<account1> findAll2()  {
            Connection conn=null;
            Statement stmt=null;
            ResultSet rs=null;
            List<account1> list=null;

            try {
                //注册驱动，获取连接
                conn=JDBCUtils.getConnection();
                //3.定义sql
                String sql="select *from account1";
                //4.获取执行SQL的对象
                 stmt=conn.createStatement();
                //5.执行SQL对象
                 rs=stmt.executeQuery(sql);
                //6.b遍历结果集，封装对象，装载集合

                account1 acc1=null;
                list=new ArrayList<account1>();
                while (rs.next()){
                    //获取数据
                    int id=rs.getInt("id");
                    String NAME=rs.getString("NAME");
                    String balance=rs.getString("balance");



                    //创建emp对象,并赋值
                    acc1=new account1();
                    acc1.setId(id);
                    acc1.setNAME(NAME);
                    acc1.setBalance(balance);




                    //装载集合
                    list.add(acc1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                //释放资源
                JDBCUtils.close(rs,stmt,conn);
            }
            return list;
        }
}

运行结果

练习：登录案例

package cn.itcast.main;

import cn.itcast.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCDemo09 {
    public static void main(String[] args) {
        //1.键盘录入，接受用户名和密码
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username=sc.next();
        System.out.println("请输入密码：");
        String password=sc.next();
        //2.调用方法
        boolean flag=new JDBCDemo09().login(username,password);
        //3.判断结果，输出不同语句
        if (flag){
            System.out.println("登录成功");
        }else{
            System.out.println("用户名或密码错误");
        }

    }
    /*
        登录方法
     */
    public boolean login(String username,String password){
        if (username==null||password==null){
            return false;
        }
        //连接数据库判断是否登录成功
        //1.获取连接
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
             conn= JDBCUtils.getConnection();
            //2.定义sql  拼接字符串
            String sql="select *from user where username='"+username+"'and password='"+password+"'";
            //3.获取执行的sql
             stmt=conn.createStatement();
            //执行查询
             rs=stmt.executeQuery(sql);
//            if (rs.next()){
//                return true;
//            }else{
//                return false;
//            }
            return rs.next();//如果有下一行，返回true

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,stmt,conn);
        }

        return false;


    }
}