dao实现层通用增删改查方法

package com.bjsxt.bbs2009.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DB {
public static Connection createConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/bbs2009", "root", "bjsxt");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

public static PreparedStatement prepare(Connection conn, String sql) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}

public static void close(Connection conn) {

try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement stmt) {
try {
stmt.close();
stmt = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}

package com.bjsxt.bbs2009.service;

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

import com.bjsxt.bbs2009.model.Category;
import com.bjsxt.bbs2009.util.DB;

public class CategoryService {
public void add(Category c) {
Connection conn = DB.createConn();
String sql = "insert into _category values (null, ?, ?)";
PreparedStatement ps = DB.prepare(conn, sql);
try {
ps.setString(1, c.getName());
ps.setString(2, c.getDescription());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DB.close(ps);
DB.close(conn);
}

public List<Category> list() {
Connection conn = DB.createConn();
String sql = "select * from _category";
PreparedStatement ps = DB.prepare(conn, sql);
List<Category> categories = new ArrayList<Category>();
try {
ResultSet rs = ps.executeQuery();
Category c = null;
while(rs.next()) {
c = new Category();
c.setName(rs.getString("name"));
c.setDescription(rs.getString("description"));
categories.add(c);
}
} catch (SQLException e) {
e.printStackTrace();
}
DB.close(ps);
DB.close(conn);
return categories;
}

public void delete(Category c) {
deleteById(c.getId());
}

public void deleteById(int id) {
Connection conn = DB.createConn();
String sql = "delete from _category where id = ?";
PreparedStatement ps = DB.prepare(conn, sql);
try {
ps.setInt(1, id);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DB.close(ps);
DB.close(conn);
}

public void update(Category c) {
Connection conn = DB.createConn();
String sql = "update _category set name = ?, description = ? where id = ?";
PreparedStatement ps = DB.prepare(conn, sql);
try {
ps.setString(1, c.getName());
ps.setString(2, c.getDescription());
ps.setInt(3, c.getId());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DB.close(ps);
DB.close(conn);
}
}

package com.bjsxt.bbs2009.model;

public class Category {
private int id;
private String name;
private String description;
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 String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}

}