java通信簿

package net.service; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import net.entity.Contact; public class AddressBookService { private static String driver = "org.apache.derby.jdbc.ClientDriver"; private static String url = "jdbc:derby://localhost:1527/address;create=true"; private static String user = "robin"; private static String password = "robin"; /** * 数据库连接管理方法 * * @return 数据库连接 * @throws StoreException * 数据库连接失败 */ public static Connection getConnection() throws StoreException { try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (ClassNotFoundException e) { e.printStackTrace(); throw new StoreException("驱动没有安装!", e); } catch (SQLException e) { e.printStackTrace(); throw new StoreException("连接失败!", e); } } public int add(Contact contact) throws ServiceException { Connection conn = null; try { conn = getConnection(); conn.setAutoCommit(false); System.out.println(conn.getClass().getName()); String idSql = "select max(id)+1 as nextId from contact"; Statement st = conn.createStatement(); System.out.println(st.getClass().getName()); ResultSet rs = st.executeQuery(idSql); int id = 1; while (rs.next()) { id = rs.getInt("nextId"); // id = rs.getInt(1); } contact.setId(id); rs.close(); st.close(); String sql = "insert into contact " + "(id, name, address, phone, qq) " + "values (?,?,?,?,?)"; // String sql = "insert into contact (id, name, address, phone, qq) // values (?,?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, contact.getId()); ps.setString(2, contact.getName()); ps.setString(3, contact.getAddress()); ps.setString(4, contact.getPhone()); ps.setString(5, contact.getQq()); ps.executeUpdate(); conn.commit(); return contact.getId(); } catch (Exception e) { if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); } e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public void remove(Contact contact) throws ServiceException { remove(contact.getId()); } public void remove(Integer id) throws ServiceException { // delete from contact where id=? Connection conn = null; try { conn = getConnection(); String sql = "delete from contact where id=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, id.intValue()); ps.executeUpdate(); } catch (StoreException e) { e.printStackTrace(); throw new ServiceException(e); } catch (SQLException e) { e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public void update(Contact contact) throws ServiceException { // update contact ... Connection conn = null; try { conn = getConnection(); String sql = "update contact set name=?, address=?, phone=?, qq=? " + "where id=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, contact.getName()); ps.setString(2, contact.getAddress()); ps.setString(3, contact.getPhone()); ps.setString(4, contact.getQq()); ps.setInt(5, contact.getId()); ps.executeUpdate(); } catch (StoreException e) { e.printStackTrace(); throw new ServiceException(e); } catch (SQLException e) { e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public Contact findById(Integer id) throws ServiceException { Connection conn = null; try { conn = getConnection(); Contact contact = null; String sql = "select id, name, address, phone, qq from contact " + "where id=? "; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); while (rs.next()) { contact = new Contact(); contact.setId(new Integer(rs.getInt("id"))); contact.setName(rs.getString("name")); contact.setAddress(rs.getString("address")); contact.setPhone(rs.getString("phone")); contact.setQq(rs.getString("qq")); } return contact; } catch (StoreException e) { e.printStackTrace(); throw new ServiceException(e); } catch (SQLException e) { e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public List findByName(String name) throws ServiceException { Connection conn = null; try { conn = getConnection(); List list = new ArrayList();// LinkedList() Vector() String sql = "select id, name, address, phone, qq from contact " + "where name like ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, "%" + name + "%"); ResultSet rs = ps.executeQuery(); while (rs.next()) { Contact contact = new Contact(); contact.setId(new Integer(rs.getInt("id"))); contact.setName(rs.getString("name")); contact.setAddress(rs.getString("address")); contact.setPhone(rs.getString("phone")); contact.setQq(rs.getString("qq")); list.add(contact); } return list; } catch (StoreException e) { e.printStackTrace(); throw new ServiceException(e); } catch (SQLException e) { e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public List<Contact> findAll() throws ServiceException { Connection conn = null; try { conn = getConnection(); List<Contact> list = new ArrayList<Contact>();// LinkedList() // Vector() String sql = "select id, name, address, phone, qq from contact "; PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs.next()) { Contact contact = new Contact(); contact.setId(new Integer(rs.getInt("id"))); contact.setName(rs.getString("name")); contact.setAddress(rs.getString("address")); contact.setPhone(rs.getString("phone")); contact.setQq(rs.getString("qq")); list.add(contact); } return list; } catch (StoreException e) { e.printStackTrace(); throw new ServiceException(e); } catch (SQLException e) { e.printStackTrace(); throw new ServiceException(e); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }