/**
 * 獲取數(shù)據(jù)庫連接對象
 */
public Connection getConnection() throws Exception {  //獲取連接對象方法
    //加載配置文件
    Properties properties = new Properties();
    properties.load(Files.newInputStream(Paths.get("D:\\druid.properties")));
 
    //創(chuàng)建一個指定參數(shù)的數(shù)據(jù)庫連接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    return dataSource.getConnection();  //返回連接對象
}

driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/student?useServerPrepStmts=true
username = root
password = root
initialSize = 10
maxActive = 30
maxWait = 1000

/**
 * 關(guān)閉數(shù)據(jù)庫連接
 * @param conn 數(shù)據(jù)庫連接
 * @param stmt PreparedStatement預(yù)處理對象
 * @param rs 結(jié)果集
 */
public void closeAll(Connection conn, PreparedStatement stmt, ResultSet rs) {  //關(guān)閉連接方法
        // 若結(jié)果集對象(RresuleSet)不為空，則關(guān)閉
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // 若預(yù)處理對象(PreparedStatement)不為空，則關(guān)閉
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // 若數(shù)據(jù)庫連接對象(Connection)不為空，則關(guān)閉
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

/**
 * 增、刪、改的操作
 * @param preparedSql 預(yù)編譯的 SQL 語句
 * @param param 參數(shù)的字符串?dāng)?shù)組
 * @return 影響的行數(shù)
 */
public int updateRow (String preparedSql, Object[] param) throws Exception {  //增刪改操作的方法
    PreparedStatement preparedStatement = null;  //創(chuàng)建預(yù)處理對象并初始為空(通過預(yù)處理可以防止SQL惡意注入)
    int row = 0;  //初始受影響行數(shù)(當(dāng)受影響行數(shù)大于0時,說明執(zhí)行成功數(shù)據(jù)則更新)
    Connection connection = getConnection();  //調(diào)用getConnection()方法獲取數(shù)據(jù)庫連接
    try {
        preparedStatement = connection.prepareStatement(preparedSql);  //對sql形參預(yù)處理
        if (param != null) {  //形參數(shù)組(注入?yún)?shù))不為空時,循環(huán)遍歷對預(yù)處理sql注入?yún)?shù)
            for (int i = 0; i < param.length; i++) {
                //為預(yù)編譯sql設(shè)置參數(shù)
                preparedStatement.setObject(i + 1, param[i]);
            }
        }
        row = preparedStatement.executeUpdate();  //執(zhí)行sql語句并得到受影響的行數(shù)
        closeAll(connection,preparedStatement,null);  //調(diào)用closeAll()方法關(guān)閉資源,因?yàn)閳?zhí)行操作不含查詢,則將結(jié)果集的實(shí)參傳空
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return row;  //返回受影響行數(shù)
}

public class Student {
    //初始字段
    private int id;
    private String name;
    private int age;
 
    public Student(int id, String name, int age) {  //構(gòu)造函數(shù)
        this.id = id;
        this.name = name;
        this.age = age;
    }
 
    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;
    }
 
    @Override
    public String toString() {  //toString方法
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

public List<Student> queryData (String sql, Object[] param) throws Exception {  //查詢操作的方法
    PreparedStatement preparedStatement = null;  //創(chuàng)建預(yù)處理對象并初始為空(通過預(yù)處理可以防止SQL惡意注入)
    Connection connection = getConnection();  //調(diào)用getConnection()方法獲取數(shù)據(jù)庫連接
    preparedStatement = connection.prepareStatement(sql);  //調(diào)用getConnection()方法獲取數(shù)據(jù)庫連接
    if (param != null) {  //形參數(shù)組(注入?yún)?shù))不為空時,循環(huán)遍歷對預(yù)處理sql注入?yún)?shù)
        for (int i = 0; i < param.length; i++) {
            //為預(yù)編譯sql設(shè)置參數(shù)
            preparedStatement.setObject(i + 1, param[i]);
        }
    }
    ResultSet resultSet = preparedStatement.executeQuery();  //得到返回的結(jié)果集
    List<Student> list = new ArrayList<Student>();  //創(chuàng)建List接口對象,泛型為指定Student對象類型,代表每個實(shí)體對象都是一行數(shù)據(jù)
    while (resultSet.next()) {  //循環(huán)遍歷結(jié)果集,將每行數(shù)據(jù)封裝成Student對象,并添加到List集合中
        Student student= new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3));  //獲取結(jié)果集中的數(shù)據(jù)存入Student對象中
        list.add(student);  //將Student對象存入list接口中
    }
    closeAll(connection,preparedStatement,resultSet);  //調(diào)用closeAll()方法關(guān)閉數(shù)據(jù)庫連接
    return list;  //返回List集合對象 
}

//增刪改測試
BaseDao baseDao = new BaseDao();  //創(chuàng)建BaseDao對象
String sql = "insert into student(id,name,age) values(?,?,?)";  //定義預(yù)處理sql
Object[] list = {6, "老八", 35};  //將sql需要的參數(shù)存入至Object類型的數(shù)組
int add = baseDao.updateRow(sql, list); //調(diào)用增刪改的方法并接收返回的受影響行數(shù)
if (add > 0) {  //受影響行數(shù)大于0則說明數(shù)據(jù)更新成功
    System.out.println("新增成功!受影響行數(shù)：" + add);
}
/* 刪除、修改也是一樣 */

//查詢測試
BaseDao baseDao = new BaseDao();  //創(chuàng)建BaseDao對象
String sql = "select * from student";  //定義預(yù)處理sql
List<Student> list = baseDao.queryData(sql, null);  //創(chuàng)建List接口,泛型為Student對象類型,用于存儲查詢返回的數(shù)據(jù)
for (Student student : list) {
    System.out.println(student.getId() + "\t\t" + student.getName() + "\t\t" + student.getAge());  //輸出數(shù)據(jù)
}