JDBC工具类与登录案例

• SELECT练习
  定义一个方法，查询表的数据将其封装为对象，然后装载集合，并返回
  1.定义一个类
  2.定义方法 public List<表名> findAll(){}
  3.实现方法select * from 表名

package MyJdbc;

public class Emp {
    private String ID;
    private int MATH;
    private int ENGLISH;
    private int CHINESE;
    public String getID() {
        return ID;
    }

    public void setID(String ID) {
        this.ID = ID;
    }

    public int getMATH() {
        return MATH;
    }

    public void setMATH(int MATH) {
        this.MATH = MATH;
    }

    public int getENGLISH() {
        return ENGLISH;
    }

    public void setENGLISH(int ENGLISH) {
        this.ENGLISH = ENGLISH;
    }

    public int getCHINESE() {
        return CHINESE;
    }

    public void setCHINESE(int CHINESE) {
        this.CHINESE = CHINESE;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "ID='" + ID + '\'' +
                ", MATH=" + MATH +
                ", ENGLISH=" + ENGLISH +
                ", CHINESE=" + CHINESE +
                '}';
    }
}

package MyJdbc;

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

//查询所有Empty对象
public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        List<Emp> list= new JDBCDemo1().findAll();
        System.out.println(list);
        System.out.println(list.size());
    }
    public List<Emp> findAll() throws SQLException {
        Statement stmt = null;
        Connection conn=null;
        ResultSet rs = null;
        List<Emp> list= null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql:///jdb","root","123");
            //3.定义sql
            String sql = "select * from grade";
            //4.获取执行sql的对象
            stmt = conn.createStatement();
            //5.执行sql
            rs = stmt.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while (rs.next()){
                String id=rs.getString(1);
                int math=rs.getInt(2);
                int english=rs.getInt(3);
                int chinese=rs.getInt(4);
                //创建对象，并复制
                emp=new Emp();
                emp.setID(id);
                emp.setMATH(math);
                emp.setENGLISH(english);
                emp.setCHINESE(chinese);
                //装载对象
                list.add(emp);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }
}

• 抽取JDBC工具类：JDBCUtils
  目的：简化书写操作
  JDBC一二步可直接用代码conn=JDBCUtils.getConnection();代替，释放资源可简化为JDBCUtils.close(stmt,conn,rs);

//工具类
package Utils;

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

//JDBC工具类
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    //读取资源文件，获取值
    static {
        //1.创建properties集合类
        Properties pro=new Properties();
        try {
            //2.加载文件,获取src路径下的文件的方式——ClassLoader类加载器
            ClassLoader cll=JDBCUtils.class.getClassLoader();
            URL url1=cll.getResource("jdbc.properties");
            String path = url1.getPath();
            pro.load(new FileReader(path));
            //3.获取数据
            url= pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver= pro.getProperty("driver");
            //4.注册驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    //@return连接对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //释放资源
    public static void close(Statement stmt, Connection conn,ResultSet rs){
        if (rs != null) {//为了防止空指针异常
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

//配置文件
url=jdbc:mysql:///jdb
user=root
password=123
driver=com.mysql.cj.jdbc.Driver

• 登录案例

package MyJdbc;

import Utils.JDBCUtils;

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

public class JDBCDemo2 {
    public static void main(String[] args) {
        Scanner key= new Scanner(System.in);
        String username = key.nextLine();
        String password= key.nextLine();
        boolean flag=new JDBCDemo2().login(username,password);
        if (flag){
            System.out.println("Login Victory!");
        }else {
            System.out.println("Login Defeat");
        }
    }
    public boolean login(String username, String password){
        ResultSet rs=null;
        Statement stmt=null;
        Connection conn=null;
        if(username==null && password==null){
            return false;
        }
        //连接数据库
        try {
            conn= JDBCUtils.getConnection();
            //2.定义sql
            String sql="select * from user where name='"+username+"' and password='"+password+"'";
            //3.获取sql对象
            stmt = conn.createStatement();
            rs=stmt.executeQuery(sql);
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(stmt,conn,rs);
        }

        return false;
    }
}