使用Statement对数据库进行增删改查

package com.power.dao;

import java.sql.Connection;
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日下午2:15:11
 * @description: 使用Statement对数据库进行增删改查
 * 					Statement.execute() 用于执行DDL语句
 * 					Statement.executeUpdate() 用于执行INSERT, UPDATE, DELETE
 * 					Statement.executeQuery() 用于执行SELECT
 * 
 */
public class StudentDao {

	/**
	 * 添加
	 */
	public void insert(Student student) {
		Connection connection = null;
		Statement statement = null;
		String sql = MessageFormat.format(
				"INSERT INTO student(student_id, name, gender, age) VALUES({0},''{1}'',''{2}'',{3})", student.getId(),
				student.getName(), student.getGender(), student.getAge());
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			int resultNum = statement.executeUpdate(sql);
			if (resultNum > 0) {
				System.out.println("新增记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常");
			throw new RuntimeException(e);
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}

	}

	/**
	 * 删除
	 */
	public void delete(Integer studentId) {
		Connection connection = null;
		Statement statement = null;
		String sql = "DELETE FROM student WHERE student_id =" + studentId;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			int resultNum = statement.executeUpdate(sql);
			if (resultNum > 0) {
				System.out.println("删除记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常");
			throw new RuntimeException(e);
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}

	}

	/**
	 * 分页查询
	 */
	public void list(int start, int size) {
		Connection connection = null;
		Statement statement = null;
		String sql = MessageFormat.format("SELECT s.student_id, s.name, s.gender, s.age FROM student s LIMIT {0},{1}",
				start, size);
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			ResultSet result = statement.executeQuery(sql);
			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 (statement != null) {
					statement.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(102, "李四", "W", 26);
		dao.insert(student);
		dao.list(0, 5);
		dao.delete(102);
	}

}

/**
 * 实体类
 */
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 + "]";
	}

}