使用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();
			// 注意此处需要传sql
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, student.getId());
			preparedStatement.setString(2, student.getName());
			preparedStatement.setString(3, student.getGender());
			preparedStatement.setInt(4, student.getAge());
			// 注意这个地方不需要传sql
			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 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;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age + "]";
	}
}