使用PreparedStatement实现增删改查

package com.power.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.text.MessageFormat;import com.power.util.DBUtil;/** * @author chengwei * @date 2017年7月13日下午4:09:44 * @description: */public class StudentDao {/** * 添加 */public void insert(Student student) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "INSERT INTO student(student_id, name, gender, age) VALUES(?,?,?,?)";try {connection = DBUtil.getConnection();// 注意此处需要传sqlpreparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, student.getId());preparedStatement.setString(2, student.getName());preparedStatement.setString(3, student.getGender());preparedStatement.setInt(4, student.getAge());// 注意这个地方不需要传sqlint resultNum = preparedStatement.executeUpdate(); if (resultNum > 0) {System.out.println("新增记录成功!");}} catch (SQLException e) {System.out.println("数据库访问异常");throw new RuntimeException(e);} finally {try {if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {System.out.println("释放资源发生异常");}}}/** * 删除 */public void delete(Integer studentId) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "DELETE FROM student WHERE student_id = ?";try {connection = DBUtil.getConnection();preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, studentId);int resultNum = preparedStatement.executeUpdate();if (resultNum > 0) {System.out.println("删除记录成功!");}} catch (SQLException e) {System.out.println("数据库访问异常");throw new RuntimeException(e);} finally {try {if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {System.out.println("释放资源发生异常");}}}/** * 分页查询 */public void list(int start, int size) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "SELECT s.student_id, s.name, s.gender, s.age FROM student s LIMIT ?,?";try {connection = DBUtil.getConnection();preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, start);preparedStatement.setInt(2, size);ResultSet result = preparedStatement.executeQuery();while (result.next()) {Student student = new Student(result.getInt("student_id"), result.getString("name"),result.getString("gender"), result.getInt("age"));System.out.println(student);}} catch (SQLException e) {System.out.println("数据库访问异常");throw new RuntimeException(e);} finally {try {if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {System.out.println("释放资源发生异常");}}}/** * 测试 */public static void main(String[] args) {StudentDao dao = new StudentDao();Student student = new Student(106, "赵六", "W", 26);dao.insert(student);dao.list(0, 5);dao.delete(106);}}/** * 实体类 */class Student {/** 主键 */private Integer id;/** 姓名 */private String name;/** 性别 */private String gender;/** 年龄 */private Integer age;public Student(Integer id, String name, String gender, Integer age) {super();this.id = id;this.name = name;this.gender = gender;this.age = age;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age + "]";}}