MySQL(四)之JDBC小封装

一、获取数据库连接

连接数据库的四个要素，

1. 相应的数据库驱动
2. URL地址
3. 账号
4. 密码

//一、加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");

//二、数据库的URL地址
String url = "jdbc:mysql://127.0.0.1:3306/student_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";

//三、数据库的账号
String user = "root";

//四、数据库的密码
String password = "111";

//用驱动管理器获取连接返回连接对象
Connection connection = DriverManager.getConnection(url,user,passworg);

二,封装JDBCUtil

可以在项目中直接掉方法即可,省略大量繁琐代码,
比如连接数据库就可以省略掉

package pro.util;

import java.sql.*;

/*
  自定义jdbc工具类
*/
public class JDBCUtil {
    //在web项目中加载一次
    static {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){

        }
    }

    public static Connection getconnection() throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/student_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        Connection connection = DriverManager.getConnection(url, "root", "root");
        return connection;
    }

    /*用于执行 新增,修改,删除sql*/
    public static void executeUpdatesql(PreparedStatement ps,Object[] objs) throws SQLException {
        if (objs != null){
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i+1,objs[i]);
            }
        }
        ps.executeUpdate();
    }

    /*用于执行新增所生成的主键*/
    public static int executeUpdatesqlReturnKey(PreparedStatement ps,Object[] objs) throws SQLException {
        if (objs != null){
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i+1,objs[i]);
            }
        }
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        int id = 0;
        if (rs.next()){//只有一个结果
            id = rs.getInt(1);
        }
        rs.close();
        return id;
    }

    public static void closeAll(Connection connection, PreparedStatement ps, ResultSet rs) throws SQLException {
        if (connection != null){
            connection.close();
        }
        if (ps !=null){
            ps.close();
        }
        if (rs !=null){
            rs.close();
        }
    }
}

三、查询操作

找到主键id跟着这个步骤就好了,无论验证登录还是查询信息都是一样子的,登录返回账号密码,查询返回多个信息,一个判定是否查到,一个将信息传递到页面或者GUI上面,原理相同,

 Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    public List<tStudent> findStudentList() throws SQLException {
        List<tStudent> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getconnection();
            String sql = "";
            ps = connection.prepareStatement(sql);
            //执行ps
            rs = ps.executeQuery();
            //判断
            while (rs.next()) {
                tStudent tstudent = new tStudent();
                tstudent.setID(rs.getString("ID"));
                tstudent.setSname(rs.getString("sname"));
                tstudent.setSex(rs.getString("sex"));
                tstudent.setBirthday(rs.getString("birthday"));
                tstudent.setMobile(rs.getString("mobile"));
                tstudent.setAddress(rs.getString("address"));
                tstudent.setOper_time(rs.getString("oper_time"));
                tstudent.setGname(rs.getString("gname"));
                tstudent.setCname(rs.getString("cname"));
                tstudent.setAccount(rs.getString("account"));
                list.add(tstudent);
            }
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
        return list;
    }

四、增删改操作

我这里以增为例,删除和增一样,只需要找到id,然后直接用sql删除就好了,改简单的理解就是先查在删在增,
因为没有返回值,所以就放在一起了

  public void saveStudent(String id, String sname, String sex, String birthday,
                            String mobile, String address, String gradeID, String[] course, String adminid) throws SQLException {
        try {
            connection = JDBCUtil.getconnection();
            connection.setAutoCommit(false);
            //保存信息到学生表
            String sql = "INSERT INTO t_student(ID,NAME,sex,birthday,mobile,address,gradeID,adminID,oper_time)" +
                    "  VALUES(?,?,?,?,?,?,?,?,?)";
            //执行后返回生成的主键
            ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            Object[] objects = {id, sname, sex, birthday, mobile, address, gradeID, adminid, new Date()};
            //获得刚刚保存的学生id
            JDBCUtil.executeUpdatesql(ps, objects);
            //保存学生和课程的关系
            sql = "INSERT INTO student_course VALUES(?,?)";
            ps = connection.prepareStatement(sql);
            for (String courseID : course) {
                Object[] objects1 = {id, courseID};
                JDBCUtil.executeUpdatesql(ps, objects1);
            }
            connection.commit();
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
    }