JDBC的正确使用方式

dbconfig.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/schooldb
jdbc.username=root
jdbc.password=admin123

JDBCUtils.java

package com.qianfeng.dao.utils;

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

public class JDBCUtils {

    static Properties prop = new Properties();

    static{
        try {
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取Connection对象
    public static Connection getConnection(){
        String driver = prop.getProperty("jdbc.driver");
        String url = prop.getProperty("jdbc.url");
        String username = prop.getProperty("jdbc.username");
        String password = prop.getProperty("jdbc.password");
        Connection conn = null;

        try {
        	//1. 加载数据库驱动
            Class.forName(driver);
            //2. 获取数据库连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //数据库回收操作
    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
            if(pstmt != null){
                pstmt.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //数据库回收操作
    public static void release(Connection conn, PreparedStatement pstmt){

        try {
            if(pstmt != null){
                pstmt.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

StudentDAOImpl.java

package com.qianfeng.dao.impl;

import com.qianfeng.dao.StudentDAO;
import com.qianfeng.dao.utils.JDBCUtils;
import com.qianfeng.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl implements StudentDAO {
    @Override
    public List<Student> queryAllStudent(Integer pagesize, Integer pagenum) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();
        try {
        	//获取数据库连接
            conn = JDBCUtils.getConnection();
            //编写sql语句
            String sql = "select * from student limit ?,?";
            //处理SQL语句，并填入参数
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,pagesize*(pagenum-1));
            pstmt.setInt(2,pagesize);
            //通过SQL语句获得结果指针
            rs =pstmt.executeQuery();
            //通过结果指针获取结果集
            while (rs.next()){
                Student s = new Student();
                s.setsId(rs.getString(1));
                s.setsName(rs.getString(2));
                s.setsSex(rs.getString(4));
                s.setsBirth(rs.getString(3));
                s.setsGid(rs.getInt(5));
                list.add(s);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            JDBCUtils.release(conn,pstmt,rs);
        }
    }

    @Override
    public Integer delaStudent(String sId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "delete from student where s_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,sId);
            int num = pstmt.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn,pstmt);
        }
        return 0;
    }

    @Override
    public Student queryStudentById(String sid) {
        Student stu = new Student();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from student where s_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,sid);
            rs = pstmt.executeQuery();
            rs.next();
            stu.setsId(rs.getString(1));
            stu.setsName(rs.getString(2));
            stu.setsBirth(rs.getString(3));
            stu.setsSex(rs.getString(4));
            stu.setsGid(rs.getInt(5));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }

    @Override
    public int updateStudent(String sid, String sname, String ssex, String sbirth, int gradeid) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "update student set s_name=?,s_birth=?,s_sex=?,s_gid=? where s_id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,sname);
            pstmt.setString(2,sbirth);
            pstmt.setString(3,ssex);
            pstmt.setInt(4,gradeid);
            pstmt.setString(5,sid);
            int num = pstmt.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn,pstmt);
        }
        return 0;

    }

    @Override
    public Integer queryStudentsNumber() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "select count(1) from student";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            Integer nums = rs.getInt(1);
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            JDBCUtils.release(conn,pstmt,rs);
        }
    }

    @Override
    public Integer addaStudent(String sid, String sname, String ssex, String sbirth, Integer gradeid) {
            Connection conn = null;
            PreparedStatement pstmt = null;

            try {
                conn = JDBCUtils.getConnection();
                String sql = "insert into student values(?,?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1,sid);
                pstmt.setString(2,sname);
                pstmt.setString(3,sbirth);
                pstmt.setString(4,ssex);
                pstmt.setInt(5,gradeid);
                int num = pstmt.executeUpdate();
                return num;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.release(conn,pstmt);
            }
            return 0;
    }


}