JDBC连接mysql数据库

一、创建java项目，导入jar包，配置properties文件(src路径下)

druid-1.2.8.jar
mysql-connector-java-8.0.25.jar

#   ????key-value???
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名
username=数据库用户名
password=数据库用户密码
# init size
initialSize=5
# max size
maxActive=10
maxWait=3000

二、根据数据库建好的表创建实体类（如：Student）

package day2.Test;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private int age;
    private Date birthday;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }

    public Student(String name, int age, Date birthday) {
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Student() {
    }

    public Student(int id, String name, int age, Date birthday) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }
}

三、创建数据库连接工具类JDBCUtil

package day2.Test;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    //  全局定义动态变量


    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    private static DataSource dataSource = null;
    //  加载驱动
    static{
        Properties prop = new Properties();
        InputStream ress = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            prop.load(ress);
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    //  建立数据库连接
    public static void getConnection() {
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //  创建sql对象
    //  增删改
    public static int update(String sql,Object obj[]) {
        //  连接数据库
        getConnection();
        try {
            //  创建sql对象
            ps = conn.prepareStatement(sql);
            //  注入数据
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);
            }
            //  执行sql语句
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close();
        }
    }

    //  查
    public static ResultSet query(String sql,Object obj[]) {
        getConnection();
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);
            }
            rs = ps.executeQuery();
            return rs;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //  释放资源
    public static void close() {
        if(rs!=null){   //  防止空指针异常
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


}

四、创建StudentDao类根据自身需求编写不同sql语句

package day2.Test;

import day2.Test.JDBCUtil;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDao {
    //  增
    public int addStudent(Student student){
        String sql = "insert into student values(null,?,?,?)";

        Object obj[] = {student.getName(),student.getAge(),student.getBirthday()};
        int num = JDBCUtil.update(sql,obj);
        return num;
    }
    //  改
    public int updateStudent(Student student){
        String sql = "update student set name=?,age=?,birthday=? where id=?";
        Object obj[] = {student.getName(),student.getAge(),student.getBirthday(),student.getId()};
        int num = JDBCUtil.update(sql,obj);
        return num ; //
    }
    //  删
    public int deleteStudent(int id){
        String sql ="delete from student where id = ?";
        Object obj[] = {id};
        int num = JDBCUtil.update(sql,obj);
        return num;
    }
    //  查
    public List<Student> findStudents(){
        String sql = "select * from student";
        Object obj[] = {};
        List<Student> list = new ArrayList<>();
        ResultSet res = JDBCUtil.query(sql, obj);
        while(true){
            try {
                if(!res.next()){
                    break;
                }
                int id = res.getInt(1);
                String name = res.getString(2);
                int age = res.getInt(3);
                Date birthday = res.getDate(4);
                list.add(new Student(id,name,age,birthday));
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCUtil.close();
        return list;
    }
}

五、创建测试类测试查询方法

public class TestDao {
    public static void main(String[] args) {
        List<Student> list= new StudentDao().findStudents();
        for (Student student: list) {
            System.out.println(student);
        }
    }
}

文件目录样式（不包含StudentUI）