jdbc小白入手初级

1. 设置mysql允许远程连接

切换mysql数据库：

select host,user from user;

-- 打开远程链接

update user set host ='%' where user='root';

-- 关闭远程连接

update user set host ='localhost' where user='root';

-- 刷新配置：

flush privileges;

2. java数据库连接JDBC[java Database Connection]

我们之前操作数据库--都是通过图形化界面来对mysql进行操作，我们实际开发是我们应该通过java代码来完成对数据库的操作。

3. java如何连接数据库

JAVA: OOP面向对象编程语言

SQL: 结构化查询语言

思考: java和sql是两种不同的编程语言。连接必须请翻译。翻译程序---每个数据库厂商都提高了翻译软件---打包jar。---我们的java代码引入jar就可完成与数据库的沟通。网上可以下载。

1.创建一个java工程并把jar放入lib目录下

**2.把jar build构建（解压）到本项目 **

基本框架：

1.加载驱动: Class.forName("com.mysql.cj.jdbc.Driver")

2.获取连接对象: Connection con=DriverManager.getConnection(url,user,password);

url: jdbc:mysql://localhost:3306/qy174

user: root

password: root

3.获取执行sql语句的对象: Statement st=con.createStatement();

4.执行增删改sql语句： st.executeUpdate(sql);

5.关闭资源: st.close() conn.close();

public class Test01 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动--默写:[纸上]
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接数据库的对象--
        /**
         *  String url,数据库服务器的地址
         *     jdbc:mysql://localhost:3306/qy174
         *     协议:数据库类型://数据库服务器ip:端口号/数据库名
         *  String user,数据库账号
         *  String password：数据库密码
         */
        String url="jdbc:mysql://localhost:3306/qy174";
        String user="root";
        String password="root";
        Connection conn= DriverManager.getConnection(url,user,password);
        //3. 获取执行sql语句的对象
        Statement st=conn.createStatement();
        //4. 执行sql语句: 执行增删改的sql
        String sql="insert into dept values(60,'研发部','郑州')";
        st.executeUpdate(sql);
        //5. 关闭资源
        st.close();
        conn.close();
    }
}

package jdbc_;

import java.sql.*;

/**
 * @program: testjdbc
 * @description: java连接数据库-增删改查操作
 * @author: 王佳瑶
 * @create: 2024-04-25 15:05
 **/
public class Test {
    public static void main(String[] args) {
        String url="jdbc:mysql://localhost:3306/dept";
        String a1="insert into dept values(90,'研发部','北京')";
        String d1="delete from dept where deptno=70;";
        String u1="update dept set loc='上海' where deptno=60";
        String s1="select * from dept";
        //add(a1,url);
        //delete(url,d1);
        //update(url,u1);
        select(url,s1);
    }
    //数据库-----增加
    public static void add(String sql,String url){
        //1.加载驱动
        Connection con = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接数据库的对象
            con = DriverManager.getConnection(url,"root","root");
            //3.获取执行sql语句的对象
            statement = con.createStatement();
            //4.执行sql语言：执行增删改的sql语句
            statement.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                //5.关闭资源
                if (con != null) {
                    con.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        
    }
    //数据库----删除
    public static void delete(String url,String sql){
        Connection connection = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接数据库
            connection = DriverManager.getConnection(url,"root","root");
            //获取sql语句的对象
            statement = connection.createStatement();
            //sql语句操作
            statement.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                //关闭资源
                if (connection != null) {
                    connection.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //数据库----修改
    public static void update(String url,String sql){
        Connection connection = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接数据库
            connection = DriverManager.getConnection(url,"root","root");
            //声明sql对象
            statement = connection.createStatement();
            //sql语句执行
            statement.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                //关闭资源
                if (connection != null) {
                    connection.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    
    //数据库---查询
    public static  void select(String url,String sql){
        Connection connection= null;
        Statement statement= null;
        ResultSet res = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(url,"root","root");
            statement = connection.createStatement();
//执行查询的sql语句 ResultSet:把数据库表返回的记录都封装到ResultSet结果集对象中
            res = statement.executeQuery(sql);
//遍历结果集 next():判断下一条是否有记录，如果有记录返回true并且指针下移。
            while (res.next()){
                //获取当前行的指定列的值
                int deptno=res.getInt("deptno");
                String dname=res.getString("dname");
                String loc=res.getString("loc");
                System.out.println("部门编号："+deptno+"部门名："+dname+"地址："+loc);
            }
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());;
        } finally {
            //关闭资源
            try {
                if (res != null) {
                    res.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

        }
    }
}

4. 常见的错误

第一种：

1. 自己把驱动名写错了。
2. 没有引入mysql的驱动包
3. 没有构建jar

第二种：

1. 服务的ip写错
2. mysql服务没开

第三种：

账号密码错误

第四种：

sql错误----sql在navicat运行一下

5. sql注入安全问题

public class Test02 {
    public static void main(String[] args) throws Exception {
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入账号:");
        //nextLine()根据换行结束输入
        //next()根据空格结束
        String name=sc.nextLine();
        System.out.print("请输入密码:");
        String pwd=sc.nextLine();
        boolean flag = login(name, pwd);
        if(flag==true){
            System.out.println("登录成功");
        }else{
            System.out.println("账号或密码错误");
        }
    }

    //登录功能
    public static boolean login(String name, String pwd) {
        ResultSet rs = null;
        Statement st = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/qy174";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
            st = conn.createStatement();
            String sql="select * from t_user where username='"+name+"' and password='"+pwd+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                return true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(st!=null){
                try {
                    st.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return false;
    }
}

只要通过sql条件语句or:满足一个条件即可破解密码。

我们输入的账号和密码在数据库中压根不存在，但是显示登录成功。出现了sql注入安全隐患问题。 由于Statement类的问题。

后来找到PreparedStatement它是Statement的子类，解决了这一问题，根据占位符？

public static boolean login2(String name, String pwd) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/qy174";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
            //?占位符
            String sql="select * from t_user where username=? and password=?";
            ps = conn.prepareStatement(sql);
            //为占位符赋值 1表示第一个占位符的值
            ps.setObject(1,name);
            ps.setObject(2,pwd);

            rs = ps.executeQuery();
            while (rs.next()){
                return true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return false;
    }

以后使用都有preparedStatement

6. 企业中开发模式

1.对一张表的操作会封装到对应的Dao类中，Dao(Data access Object)数据访问对象层。

例子:t_user表---UserDao类中，tb_dept==DeptDao. Emp表----EmpDao类中。

2.表中的记录封装到一个实体类entity中。

类名---表名 属性---表的列名 表的一条记录--->实体类的对象。

观察: 数据库中一张表对应两个类。

一个是操作类: 操作表中记录的。【CRUD】 dao包 表名Dao

一个是实体类: 封装表中一条记录。 entity包 表名

包: com.名字缩写.dao com.名字缩写.entity

类名: User Dept UserDao

方法: 小写。insert update delete czsy

package com.wjy.entity;

import java.util.Date;

/**
 * @program: testjdbc
 * @description: stuinfo表的实体类:用来封装表中记录的数据
 * @author: 王佳瑶
 * @create: 2024-04-26 15:18
 **/
public class Stuinfo {
    private int id;//id编号
    private String name;//名字
    private String sex;//性别
    private Date birthdate;//出生日期
    private String address;//地址
    private String tel;//电话
    private int chineseScore;//语文成绩
    private String classRoom_id;//班级编号

    @Override
    public String toString() {
        return "Stuinfo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthdate='" + birthdate + '\'' +
                ", address='" + address + '\'' +
                ", tel='" + tel + '\'' +
                ", chineseScore=" + chineseScore +
                ", classRoom_id='" + classRoom_id + '\'' +
                '}';
    }

    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 getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(Date birthdate) {
        this.birthdate = birthdate;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public int getChineseScore() {
        return chineseScore;
    }

    public void setChineseScore(int chineseScore) {
        this.chineseScore = chineseScore;
    }

    public String getClassRoom_id() {
        return classRoom_id;
    }

    public void setClassRoom_id(String classRoom_id) {
        this.classRoom_id = classRoom_id;
    }
}

package com.wjy.dao;

import com.wjy.entity.Stuinfo;

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

/**
 * @program: testjdbc
 * @description: 对表stuinfo的增删改查操作
 * 每一个操作封装为一个对应的方法。
 * @author: 王佳瑶
 * @create: 2024-04-26 15:17
 **/
public class StuinfoDao {
    private String url = "jdbc:mysql://localhost:3306/student";
    private String user = "root";
    private String pwd = "root";
    Connection con = null;
    PreparedStatement ps = null;

    /**
     * 对stuinfo表的增加操作
     * @param name 名字
     * @param sex 性别
     * @param birthdate 出生日期
     * @param address 地址
     * @param tel 电话
     * @param chineseScore 语文成绩
     * @param classRoom_id 班级号
     * @return 添加的条数
     */
    public int add(String name, String sex, Date birthdate, String address, String tel, int chineseScore, String classRoom_id){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(url,user,pwd);
            String sql = "insert into stuinfo values(null,?,?,?,?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,sex);
            ps.setObject(3,birthdate);
            ps.setObject(4,address);
            ps.setObject(5,tel);
            ps.setObject(6,chineseScore);
            ps.setObject(7,classRoom_id);
            return ps.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return 0;
    }

    /**
     * 删除操作
     * @param id 要删除的编号
     * @return 影响的行数
     */
    public int delete(int id){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(url,user,pwd);
            String sql = "delete from stuinfo where id = ?";
            ps = con.prepareStatement(sql);
            ps.setObject(1,id);
            return ps.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return 0;
    }

    /**
     * 修改信息
     * @param name 名字
     * @param sex 性别
     * @param birthdate 出生日期
     * @param address 地址
     * @param tel 电话
     * @param chineseScore 语文成绩
     * @param classRoom_id 班级号
     * @return 影响的条数
     */
    public int update(int id,String name, String sex, Date birthdate, String address, String tel, int chineseScore, String classRoom_id){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(url,user,pwd);
            String sql = "update stuinfo set name = ?,sex = ?,birthdate = ?,address = ?,tel = ?,chineseScore = ?,classRoom_id = ? where id = ?";
            ps = con.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,sex);
            ps.setObject(3,birthdate);
            ps.setObject(4,address);
            ps.setObject(5,tel);
            ps.setObject(6,chineseScore);
            ps.setObject(7,classRoom_id);
            ps.setObject(8,id);
            return ps.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return 0;
    }

    /**
     * 查询所有
     * @return 数组--把所有的数据存放在一个数组中，循环遍历输出全部
     */
    public List<Stuinfo> selectAll(){
        ResultSet res = null;
        List<Stuinfo> list= new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(url,user,pwd);
            String sql = "select * from stuinfo";
            ps = con.prepareStatement(sql);
            res = ps.executeQuery();
            while (res.next()){
                Stuinfo stuinfo = new Stuinfo();
                stuinfo.setId(res.getInt("id"));
                stuinfo.setName(res.getString("name"));
                stuinfo.setSex(res.getString("sex"));
                stuinfo.setBirthdate(res.getDate("birthdate"));
                stuinfo.setAddress(res.getString("address"));
                stuinfo.setTel(res.getString("tel"));
                stuinfo.setChineseScore(res.getInt("chineseScore"));
                stuinfo.setClassRoom_id(res.getString("classRoom_id"));
                list.add(stuinfo);
            }
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (res != null) {
                    res.close();
                }
                ps.close();
            con.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        }
        return list;
    }

    /**
     * 根据id查询信息
     * @param id 要查询的id信息
     * @return 返回要查找的数据
     */
    public List<Stuinfo> selectID(int id){
        ResultSet res = null;
        List<Stuinfo> list= new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(url,user,pwd);
            String sql = "select * from stuinfo where id = ?";
            ps = con.prepareStatement(sql);
            ps.setObject(1,id);
            res = ps.executeQuery();
            while (res.next()){
                Stuinfo stuinfo = new Stuinfo();
                stuinfo.setId(res.getInt("id"));
                stuinfo.setName(res.getString("name"));
                stuinfo.setSex(res.getString("sex"));
                stuinfo.setBirthdate(res.getDate("birthdate"));
                stuinfo.setAddress(res.getString("address"));
                stuinfo.setTel(res.getString("tel"));
                stuinfo.setChineseScore(res.getInt("chineseScore"));
                stuinfo.setClassRoom_id(res.getString("classRoom_id"));
                list.add(stuinfo);
            }
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (res != null) {
                    res.close();
                }
                ps.close();
                con.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return list;
    }

}

package com.wjy.test;

import com.wjy.dao.StuinfoDao;
import com.wjy.entity.Stuinfo;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

/**
 * @program: testjdbc
 * @description: 对stuinfo表的操作测试
 * @author: 王佳瑶
 * @create: 2024-04-26 15:20
 **/
public class Test {
    private static Scanner in = new Scanner(System.in);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    static StuinfoDao stuinfoDao = new StuinfoDao();
    public static void main(String[] args) {
        boolean i = true;
        while (i){
            //Scanner in = new Scanner(System.in);
            System.out.println("--------------对student数据库中stuinfo表的操作-------------");
            System.out.println("\t\t\t1.增加信息");
            System.out.println("\t\t\t2.删除信息");
            System.out.println("\t\t\t3.修改信息");
            System.out.println("\t\t\t4.查询信息");
            System.out.println("\t\t\t5.根据ID查询信息");
            System.out.println("\t\t\t6.结束操作");
            System.out.print("请选择要操作的内容:");
            int a = in.nextInt();
            switch (a){
                case 1:
                    add();
                    break;
                case 2:
                    delete();
                    break;
                case 3:
                    update();
                    break;
                case 4:
                    selectAll();
                    break;
                case 5:
                    selectID();
                    break;
                case 6:
                    i = false;
                    break;
                default:
                    System.out.println("无效输入！");
                    break;
            }
        }
    }
    //1.增加
    public static void add(){
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //Scanner in = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String name = in.next();
        System.out.print("请输入性别：");
        String sex = in.next();
        System.out.print("请输入出生日期：");
        Date birthdate = null;
        try {
            birthdate = sdf.parse(in.next());
        } catch (ParseException e) {
            System.out.println(e.getMessage());
        }
        System.out.print("请输入地址：");
        String address = in.next();
        System.out.print("请输入手机号：");
        String tel = in.next();
        System.out.print("请输入语文分数：");
        int chineseScore = in.nextInt();
        System.out.print("请输入班级号：");
        String classRoom_id = in.next();
        //StuinfoDao stuinfoDao = new StuinfoDao();
        int row = stuinfoDao.add(name,sex,birthdate,address,tel,chineseScore,classRoom_id);
        if (row > 0){
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
    }
    //2.删除
    public static void  delete(){
        //Scanner in = new Scanner(System.in);
        System.out.print("请输入要删除的id号：");
        int id = in.nextInt();
        //StuinfoDao stuinfoDao = new StuinfoDao();
        int row = stuinfoDao.delete(id);
        if (row > 0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
    //3.修改-------------------------------有问题
    public static void update(){
        //Scanner in = new Scanner(System.in);
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        System.out.println("请输入要修改的id：");
        int id = in.nextInt();
        System.out.println("请输入姓名：");
        String name = in.next();
        System.out.println("请输入性别：");
        String sex = in.next();
        System.out.println("请输入出生日期：");
        Date birthdate = null;
        try {
            birthdate = sdf.parse(in.next());
        } catch (ParseException e) {
            System.out.println(e.getMessage());
        }
        System.out.println("请输入地址：");
        String address = in.next();
        System.out.println("请输入手机号：");
        String tel = in.next();
        System.out.println("请输入语文成绩：");
        int chineseScore = in.nextInt();
        System.out.println("请输入班级编号：");
        String classRoom_id = in.next();
        //StuinfoDao stuinfoDao = new StuinfoDao();
        int update = stuinfoDao.update(id,name,sex,birthdate,address,tel,chineseScore,classRoom_id);
        System.out.println(update>0?"修改成功":"修改失败");
    }
    //4.查询所有
    public static void selectAll(){
        //StuinfoDao stuinfoDao = new StuinfoDao();
        List<Stuinfo> list = stuinfoDao.selectAll();
        System.out.println("id\tname\tsex\tbirthdate\taddress\ttel\tchineseScore\tclassRoom_id");
        for (Stuinfo s:list){
            System.out.println(s.getId()+"\t"+s.getName()+"\t"+s.getSex()+"\t"+s.getBirthdate()+"\t"+s.getAddress()+"\t"+s.getTel()+"\t"+s.getChineseScore()+"\t"+s.getClassRoom_id());
        }
    }
    //根据id查询信息
    public static void selectID(){
        //Scanner in = new Scanner(System.in);
        System.out.print("请输入要查询的id号：");
        int id = in.nextInt();
        //StuinfoDao stuinfoDao = new StuinfoDao();
        List<Stuinfo> list = stuinfoDao.selectID(id);
        System.out.println("id\tname\tsex\tbirthdate\taddress\ttel\tchineseScore\tclassRoom_id");
        for (Stuinfo s:list){
            System.out.println(s.getId()+"\t"+s.getName()+"\t"+s.getSex()+"\t"+s.getBirthdate()+"\t"+s.getAddress()+"\t"+s.getTel()+"\t"+s.getChineseScore()+"\t"+s.getClassRoom_id());
        }
    }
}

7. 把对所有的dao层类的操作---抽取到一个父类

public class BaseDao {
    //public: 公共 该工程下任何位置都可以访问到    protected:本包以及不同包下的子类访问到    默认:本包下的类访问到     private:本类访问
    protected Connection conn=null;
    protected PreparedStatement ps=null;
    protected ResultSet rs=null;
    private String driverName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/qy174";
    private String name="root";
    private String password="root";

    //获取连接对象的方法
    public void getConn() throws Exception{
        //1.加载驱动
        Class.forName(driverName);
        //2.获取连接对象
        conn= DriverManager.getConnection(url,name,password);
    }
    //关闭所有的资源
    public void closeAll(){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //增删改公共方法
    public int edit(String sql,Object... params){
        int row = 0;
        try {
            getConn();
            ps=conn.prepareStatement(sql);
            //为占位符赋值。
            for(int i=0;i<params.length;i++){
                ps.setObject(i+1,params[i]);
            }
            row = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return row;
    }
}

8. 把数据源信息放入属性文件properties中

必须放在src下

#里面的内容格式key=value

driverName=com.mysql.cj.jdbc.Driver

url=jdbc:mysql://localhost:3306/qy174

username=root

password=root

   //静态代码块: 随着类的加载而被加载，而且只会加载一次。
    static{
        try {
            //读取属性文件封装了一个类Properties
            Properties properties=new Properties();
            //加载属性文件
//            InputStream is=BaseDao.class.getResourceAsStream("db.properties");
            InputStream is=BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(is);
            //读取相应key的值
            driverName=properties.getProperty("driverName");
            url=properties.getProperty("url");
            name=properties.getProperty("username");
            password=properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

9. 引用数据源--连接池

连接池: 该池子中存储的是连接对象。预先存放一些连接对象。当想获取连接对象时，只需要从连接池中获取，使用完毕后归还到池子中。

种类: druid(阿里巴巴的德鲁伊) c3p0等

引入druid的jar，放入工程

#里面的内容格式key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/qy174
username=root
password=root
#最大的活跃数
maxActive=10
#初始的大小
initialSize=5
#最大的等待时间
maxWait=5000

# key的名称必须是如上

public class BaseDao {
    //public: 公共 该工程下任何位置都可以访问到    protected:本包以及不同包下的子类访问到    默认:本包下的类访问到     private:本类访问
    protected Connection conn = null;
    protected PreparedStatement ps = null;
    protected ResultSet rs = null;
    private static  DataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(is);
            //创建连接池对象---默认读取名称DriverClassName url  username password的值
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接对象的方法
    public void getConn() throws Exception {
        //从连接池中获取连接对象
        conn = dataSource.getConnection();
    }

    //关闭所有的资源
    public void closeAll() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //增删改公共方法
    public int edit(String sql, Object... params) {
        int row = 0;
        try {
            getConn();
            ps = conn.prepareStatement(sql);
            //为占位符赋值。
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            row = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return row;
    }

}

10. 最终版JDBC

案例：

dao层父类，通用

package com.wjy.dao;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @program: jdbcfood
 * @description: 所有其他dao类的父类
 * @author: 王佳瑶
 * @create: 2024-05-05 15:26
 **/
public class BaseDao {
    protected Connection connection;//数据库的连接对象
    protected PreparedStatement preparedStatement;//声明sql语句的对象
    protected ResultSet resultSet;//查询中使用的结果集
    private static DataSource dataSource;//数据库资源库
    //static静态类--类加载完成之后立即执行的
    static{
        try {
            //实例化文件对象
            Properties properties = new Properties();
            InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
            //是一个数据流要先引用指定过来
            properties.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    //sql连接类
    public void getConn() throws SQLException {
        //这里的异常可以先抛出去，留给每个调用者（原因是由于调用者还要处理别的异常）
        connection = dataSource.getConnection();
    }
    //关闭资源类
    public void closeAll(){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //增删改通用操作封装
    public int edit(String sql,Object...objects){
        int row = 0;
        try {
            getConn();//调用数据连接
            preparedStatement = connection.prepareStatement(sql);//调用sql执行语句
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i+1,objects[i]);//参数1：id序号   参数2：每个值
            }//循环遍历每个对象
            row = preparedStatement.executeUpdate();//返回执行结果
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();//最后调用关闭所有资源
        }
        return row;
    }
}

UserDao类，添加和根据密码用户名查找

package com.wjy.dao;

import com.wjy.entity.User;

import java.sql.SQLException;

/**
 * @program: jdbcfood
 * @description: 对用户的数据库操作
 *   ----- 增删改都可调用edit方法、查询操作要单独设置----
 *      1.用户的注册--数据库的增加操作
 *      2.用户的登录--数据库的查找操作
 *                  查询用户名和密码是否存在并且一致
 *                  根据条件（密码和用户名）查找--条件查找
 * @author: 王佳瑶
 * @create: 2024-05-05 15:18
 **/
public class UserDao extends BaseDao{
    //1.增加--把定义好的方法的参数直接返回给父类的edit方法进行执行操作
    public int add(String uName,String password,String tel,double money,String tName){
        String sql = "insert into user values(null,?,?,?,?,?)";
        return edit(sql,uName,password,tel,money,tName);//与数据的数据一一对应
    }
    //2.条件查询--返回user类型，返回用户的信息
   public User selectByNameAndPassword(String name,String password){
        User user = null;
       try {
           //获取连接对象
           getConn();
           //声明sql对象
           String sql = "select * from user where uName = ? and password = ?";
           preparedStatement = connection.prepareStatement(sql);
           //为占位符赋值
           preparedStatement.setObject(1,name);
           preparedStatement.setObject(2,password);
           //执行sql结果给结果集
           resultSet = preparedStatement.executeQuery();
           //根据结果集指针指向当前查找的数据的信息
           while (resultSet.next()){
               user = new User();
               user.setId(resultSet.getInt("id"));
               user.setuName(resultSet.getString("uName"));
               user.setPassword(resultSet.getString("password"));
               user.setTel(resultSet.getString("tel"));
               user.setMoney(resultSet.getDouble("money"));
               user.settName(resultSet.getString("tName"));
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           closeAll();
       }
       return user;
   }
}

UserDao类，添加和根据密码用户名查找--封装版

package com.wjy.dao;

import com.wjy.entity.User;

import java.util.ArrayList;
import java.util.List;

/**
 * @program: jdbcwork
 * @description: user表的操作
 * @author: 王佳瑶
 * @create: 2024-04-29 14:55
 **/
public class UserDao extends BaseDao{
    //注册(增加)
    public int  add(String uName,String password,String tel,double money,String tName){
        String sql = "insert into user values (null,?,?,?,?,?)";
        return edit(sql,uName,password,tel,money,tName);
    }

    //根据姓名和密码查找
    public User selectAll(String uname,String password) {
        String sql = "select * from user where uname = ? and password = ?";
        List<User> list = selectUser(sql,uname,password);
        for (User user:list){
            if (user.getuName().equals(uname) && user.getPassword().equals(password)){
                return user;
            }
        }
        return null;
    }

    //user查询操作
    public List<User> selectUser(String sql, Object ...params){
        List<User> list = new ArrayList<>();
        try {
            getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setuName(rs.getString("uName"));
                user.setPassword(rs.getString("password"));
                user.setTel(rs.getString("tel"));
                user.setMoney(rs.getInt("money"));
                user.settName(rs.getString("tName"));
                list.add(user);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return list;
    }
}

FoodDao类，查找全部和模糊查找

package com.wjy.dao;

import com.wjy.entity.Food;

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

/**
 * @program: jdbcfood
 * @description: 对食物的数据库操作
 *  ----- 增删改都可调用edit方法、查询操作要单独设置----
 *
 * @author: 王佳瑶
 * @create: 2024-05-05 15:19
 **/
public class FoodDao extends BaseDao{
    /**
     * 查询所有食物
     * @return 所有食物
     */
    public List<Food> selectAll(){
        List<Food> list = new ArrayList<>();
        try {
            getConn();
            String sql = "select * from food";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Food food = new Food();
                food.setId(resultSet.getInt("id"));
                food.setfName(resultSet.getString("fName"));
                food.setPrice(resultSet.getDouble("price"));
                food.setDescription(resultSet.getString("description"));
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    /**
     * 模糊查找--根据上面查找全部修改而来
     * @param name 菜名
     * @return 查找到的所有菜名
     */
    public List<Food> selectByName(String name){
        List<Food> list = new ArrayList<>();
        try {
            getConn();
            String sql = "select * from food where fName like ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,"%"+name+"%");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Food food = new Food();
                food.setId(resultSet.getInt("id"));
                food.setfName(resultSet.getString("fName"));
                food.setPrice(resultSet.getDouble("price"));
                food.setDescription(resultSet.getString("description"));
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
}

FoodDao类，查找全部和模糊查找---封装版

package com.wjy.dao;

import com.wjy.entity.Food;

import java.util.ArrayList;
import java.util.List;

/**
 * @program: jdbcwork
 * @description: food表的操作
 * @author: 王佳瑶
 * @create: 2024-04-29 14:55
 **/
public class FoodDao extends BaseDao{
    //根据菜名查找
    //精确查找
    public Food selectfName1(String fName){
        String sql = "select * from food where fName = ?";
        List<Food> list = selectFood(sql,fName);
        for (Food f:list){
            if (f.getfName().equals(fName)){
                return f;
            }
        }
        return null;
    }

    //模糊查找
    public List<Food> selectfName(String fName){
        String sql = "select * from food where fName like concat('%',?,'%')";
        List<Food> list = selectFood(sql,fName);
        return list;
    }
    //查询全部食物信息
    public List<Food> selectAll(){
        String sql = "select * from food";
        return selectFood(sql);
    }

    //food查找操作
    public List<Food> selectFood(String sql, Object ...params){
        List<Food> list = new ArrayList<>();
        try {
            getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                Food food = new Food();
                food.setId(rs.getInt("id"));
                food.setfName(rs.getString("fName"));
                food.setPrice(rs.getDouble("price"));
                food.setDescription(rs.getString("description"));
                list.add(food);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeAll();
        }
        return list;
    }
}

测试1

package com.wjy;

import com.wjy.dao.FoodDao;
import com.wjy.dao.UserDao;
import com.wjy.entity.Food;
import com.wjy.entity.User;

import java.sql.SQLOutput;
import java.util.List;
import java.util.Scanner;

/**
 * @program: jdbcfood
 * @description:
 * @author: 王佳瑶
 * @create: 2024-05-05 16:51
 **/
public class Test {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        UserDao userDao = new UserDao();
        FoodDao foodDao = new FoodDao();
        System.out.println("1.登录2.注册");
        System.out.print("请选择：");
        int choose = input.nextInt();
        if (choose==1){
            System.out.println("请输入账户：");
            String uName = input.next();
            System.out.println("请输入密码：");
            String password = input.next();

            User user = userDao.selectByNameAndPassword(uName,password);
            if (user==null){
                System.out.println("账号或密码错误");
            }else {
                System.out.println("登录成功！你的信息如下;");
                System.out.println("登录名："+user.getuName());
                System.out.println("电话："+user.getTel());
                System.out.println("真实姓名："+user.gettName());
                System.out.println("账户余额："+user.getMoney());
               while (true){
                   System.out.println("--------菜单如下----------");
                   List<Food> foods = foodDao.selectAll();
                   System.out.println("食物名称\t食物价格");
                   //遍历食物的信息
                   for (Food f:foods) {
                       System.out.println(f.getfName()+"\t"+f.getPrice());
                   }

                   System.out.println("------请点菜-------");
                   System.out.println("请输入菜名：");
                   String cm = input.next();
                   List<Food> foods1 = foodDao.selectByName(cm);
                   if (foods1.size()==0){
                       System.out.println("该餐厅没有此菜");
                   }else {
                       for (Food f:foods1) {
                           System.out.println("-----------\n食物名称:"+f.getfName()+"\n食物价格:"+f.getPrice()+"\n食物描述:"+f.getDescription()+"\n"+"------------");
                       }
                       break;
                   }
               }
            }
        }else {
            System.out.print("请输入账号：");
            String name = input
                    .next();
            System.out.print("请输入密码：");
            String password = input.next();
            System.out.print("请输入手机号：");
            String tel = input.next();
            System.out.print("请输入余额：");
            Double money = input.nextDouble();
            System.out.print("请输入真实姓名：");
            String tName = input.next();
            int row = userDao.add(name, password, tel, money, tName);
            if (row>0){
                System.out.println("注册成功");
            }else {
                System.out.println("注册失败");
            }
        }
    }
}

测试2

package com.wjy.test;

import com.wjy.dao.FoodDao;
import com.wjy.dao.UserDao;
import com.wjy.entity.Food;
import com.wjy.entity.User;

import java.util.List;
import java.util.Objects;
import java.util.Scanner;

/**
 * @program: jdbcwork
 * @description: 登录验证
 * @author: 王佳瑶
 * @create: 2024-04-29 15:08
 **/
public class Test {
    static Scanner input = new Scanner(System.in);
    static UserDao userDao = new UserDao();
    static FoodDao foodDao = new FoodDao();
    public static void main(String[] args) {
        System.out.println("---------您需要登录后才能查看本系统---------");
        System.out.println("1.注册");
        System.out.println("2.登录");
        System.out.print("请选择您的操作：");
        int a = input.nextInt();
        switch (a){
            case 1:
                zc();
                break;
            case 2:
                dl();
                break;
            default:
                break;
        }
    }
    //注册
    public static void zc(){
        System.out.println("请输入注册名：");
        String name = input.next();
        System.out.println("请输入密码：");
        String pwd = input.next();
        System.out.println("请输入电话号码：");
        String tel = input.next();
        System.out.println("请输入金额：");
        double money = input.nextInt();
        System.out.println("请输入真实姓名：");
        String tName = input.next();
        int a = userDao.add(name,pwd,tel,money,tName);
        System.out.println(a>0?"注册成功":"注册失败");
    }
    //登录
    public static void dl(){
        System.out.println("请输入登录名：");
        String name = input.next();
        System.out.println("请输入登录密码：");
        String pwd = input.next();
        User user = userDao.selectAll(name,pwd);
        List<Food> flist = foodDao.selectAll();
        if (user!=null){
            System.out.println("登录成功！您的信息如下：");
            System.out.println("登录名："+user.getuName());
            System.out.println("密码："+user.getPassword());
            System.out.println("电话："+user.getTel());
            System.out.println("真实姓名："+user.gettName());
            System.out.println("账户余额："+user.getMoney());
            System.out.println("------------菜单如下------------");
            System.out.println("食品名称\t\t食品价格");
            for (Food f:flist){
                System.out.println(f.getfName()+"\t\t\t"+f.getPrice());
            }
            mue();
        }else {
            System.out.println("登录失败，是否要重新登录：(是：y/否：任意字符)");
            String a = input.next();
            if (Objects.equals(a, "y")){
                dl();
            }else {
                System.out.println("感谢您的使用！");
            }

        }
    }

    //点菜功能
    public static void mue(){
        System.out.println("------------请点菜--------");
        System.out.print("请输入菜名：");
        String name = input.next();
        List<Food> food = foodDao.selectfName(name);
        Food food1= foodDao.selectfName1(name);
        if (food.size()!=0){
            for (Food f:food){
                System.out.println("菜名："+f.getfName());
                System.out.println("价格："+f.getPrice());
                System.out.println("改菜的描述："+f.getDescription());
                mue();
            }
        } else if (food1!=null) {
            System.out.println("菜名："+food1.getfName());
            System.out.println("价格："+food1.getPrice());
            System.out.println("改菜的描述："+food1.getDescription());
        } else {
            System.out.println("没有您要点的菜！");
            System.out.println("是否还需要继续点菜：（是：y/否:任意字符）");
            String b = input.next();
            if (b.equals("y")){
                mue();
            }else {
                System.out.println("欢迎您再次使用！！");
            }
        }
    }
}