java 增、删、改、查 实际案例

1、Connection ThreadLocal 类
2、java bean
3、DAO 接口
      3.1、公共部分可以通过定义泛型接口
      3.2、接口继承
4、PreparedStatement 增删改查
5、数据层工厂
6、业务层接口
      6.1、业务层实现类
7、业务层采用动态代理
8、业务层工厂
9、junit 单元测试
package com.curtis.mysql.dbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {

      private DatabaseConnection() {}
      private static final String DBDRIVER = "com.mysql.jdbc.Driver";
      private static final String DBURL = "jdbc:mysql://localhost:3306/c";
      private static final String DBUSER = "root";
      private static final String DBPASSWORD = "";

      private static ThreadLocal<Connection> threadLocal=new ThreadLocal<>();

      private static Connection rebuildConnection() {
             try {
                   Class.forName(DBDRIVER);
                   return DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
             } catch (Exception e) {
                   e.printStackTrace();
             }
             return null;
      }
      public static Connection getConnection() {
             Connection conn = threadLocal.get();
             if (conn == null) {
                   conn = rebuildConnection();
                   threadLocal.set(conn);
             }
             return conn;
      }
      public static void close() {
             Connection conn = threadLocal.get();
             if (conn != null) {
                   try {
                          conn.close();
                   } catch (SQLException e) {
                          e.printStackTrace();
                   }
                   threadLocal.remove();
             }
      }

      public static void main(String[] args) {
             System.out.println(DatabaseConnection.getConnection());
      }
}
package com.curtis.mysql.bean;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class Member implements Serializable {
      private String mid;
      private String name;
      private Integer age;
      private String phone;
      private Date birthday;
      private String note;
      public Member() {
             // TODO Auto-generated constructor stub
      }
      public String getMid() {
             return mid;
      }
      public void setMid(String mid) {
             this.mid = mid;
      }
      public String getName() {
             return name;
      }
      public void setName(String name) {
             this.name = name;
      }
      public Integer getAge() {
             return age;
      }
      public void setAge(Integer age) {
             this.age = age;
      }
      public String getPhone() {
             return phone;
      }
      public void setPhone(String phone) {
             this.phone = phone;
      }
      public Date getBirthday() {
             return birthday;
      }
      public void setBirthday(Date birthday) {
             this.birthday = birthday;
      }
      public String getNote() {
             return note;
      }
      public void setNote(String note) {
             this.note = note;
      }
      @Override
      public String toString() {
             return "Member [mid=" + mid + ", name=" + name + ", age=" + age + ", phone=" + phone + ", birthday=" + birthday
                          + ", note=" + note + "]";
      }
}
package com.curtis.mysql.dao;
import java.util.List;
import java.util.Set;
public interface IDAO<K,V> {

      public boolean doCreate(V vo) throws Exception;
      public boolean doUpdate(V vo) throws Exception;
      public boolean doRemoveBatch(Set<K> ids) throws Exception;
      public V findById(String id) throws Exception;
      public V findByPhone(K phone) throws Exception;
      public List<V> findAll() throws Exception;
      public long getAllCount() throws Exception;
}
package com.curtis.mysql.dao;
import com.curtis.mysql.bean.Member;
public interface IMemberDAO extends IDAO<String,Member> {

      public Member findByPhone(String phone) throws Exception;
}
package com.curtis.mysql.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import com.curtis.mysql.bean.Member;
import com.curtis.mysql.dao.IMemberDAO;
import com.curtis.mysql.dbc.DatabaseConnection;
public class MemberDAOImpl implements IMemberDAO {

      private Connection conn;
      private PreparedStatement pstmt;

      public MemberDAOImpl() {
             this.conn = DatabaseConnection.getConnection();
      }
      @Override
      public boolean doCreate(Member vo) throws Exception {
             String sql = "INSERT INTO member(mid,name,age,phone,birthday,note) VALUES(?,?,?,?,?,?)";
             this.pstmt = this.conn.prepareStatement(sql);
             this.pstmt.setString(1, vo.getMid());
             this.pstmt.setString(2, vo.getName());
             this.pstmt.setInt(3, vo.getAge());
             this.pstmt.setString(4, vo.getPhone());
             this.pstmt.setDate(5, new java.sql.Date(vo.getBirthday().getTime()));
             this.pstmt.setString(6, vo.getNote());
             return this.pstmt.executeUpdate() > 0;
      }
      @Override
      public boolean doUpdate(Member vo) throws Exception {
             String sql = "UPDATE member SET name=?,age=?,phone=?,birthday=?,note=? WHERE mid=?";
             this.pstmt = this.conn.prepareStatement(sql);
             this.pstmt.setString(1, vo.getName());
             this.pstmt.setInt(2, vo.getAge());
             this.pstmt.setString(3, vo.getPhone());
             this.pstmt.setDate(4, new java.sql.Date(vo.getBirthday().getTime()));
             this.pstmt.setString(5, vo.getNote());
             this.pstmt.setString(6, vo.getMid());
             return this.pstmt.executeUpdate() > 0;
      }
      @Override
      public boolean doRemoveBatch(Set<String> ids) throws Exception {
             StringBuffer buf = new StringBuffer();
             buf.append("DELETE FROM member WHERE mid in(");
             Iterator<String> iter = ids.iterator();
             while (iter.hasNext()) {
                   buf.append("'").append(iter.next()).append("'").append(",");
             }
             buf.delete(buf.length() - 1, buf.length()).append(")");
             this.pstmt = this.conn.prepareStatement(buf.toString());
             return this.pstmt.executeUpdate() == ids.size();
      }
      @Override
      public Member findById(String id) throws Exception {
             Member vo=null;
             String sql="SELECT mid,name,age,phone,birthday,note FROM member WHERE mid=?";
             this.pstmt=this.conn.prepareStatement(sql);
             this.pstmt.setString(1,id);
             ResultSet rs=this.pstmt.executeQuery();
             if(rs.next()) {
                   vo=new Member();
                   vo.setMid(rs.getString(1));
                   vo.setName(rs.getString(2));
                   vo.setAge(rs.getInt(3));
                   vo.setPhone(rs.getString(4));
                   vo.setBirthday(rs.getDate(5));
                   vo.setNote(rs.getString(6));
             }
             return vo;
      }
      @Override
      public Member findByPhone(String phone) throws Exception {
             Member vo=null;
             String sql="SELECT mid,name,age,phone,birthday,note FROM member WHERE phone=?";
             this.pstmt=this.conn.prepareStatement(sql);
             this.pstmt.setString(1,phone);
             ResultSet rs=this.pstmt.executeQuery();
             if(rs.next()) {
                   vo=new Member();
                   vo.setMid(rs.getString(1));
                   vo.setName(rs.getString(2));
                   vo.setAge(rs.getInt(3));
                   vo.setPhone(rs.getString(4));
                   vo.setBirthday(rs.getDate(5));
                   vo.setNote(rs.getString(6));
             }
             return vo;
      }
      @Override
      public List<Member> findAll() throws Exception {
             List<Member> list=new ArrayList<>();
             String sql="SELECT mid,name,age,phone,birthday,note FROM member";
             this.pstmt=this.conn.prepareStatement(sql);
             ResultSet rs=this.pstmt.executeQuery();
             while(rs.next()) {
                   Member vo=new Member();
                   vo.setMid(rs.getString(1));
                   vo.setName(rs.getString(2));
                   vo.setAge(rs.getInt(3));
                   vo.setPhone(rs.getString(4));
                   vo.setBirthday(rs.getDate(5));
                   vo.setNote(rs.getString(6));
                   list.add(vo);
             }
             return list;
      }
      @Override
      public long getAllCount() throws Exception {
             String sql = "SELECT COUNT(*) FROM member";
             this.pstmt = this.conn.prepareStatement(sql);
             ResultSet rs = this.pstmt.executeQuery();
             if (rs.next()) {
                   return rs.getLong(1);
             }
             return 0L;
      }
}
package com.curtis.mysql.factory;
public class DAOFactory {

      private DAOFactory() {}

      public static <T> T getInstance(Class<T> cls) {
             try {
                   return cls.newInstance();
             } catch (Exception e) {
                   e.printStackTrace();
             }
             return null;
      }
}
package com.curtis.mysql.service;
import java.util.List;
import java.util.Set;
import com.curtis.mysql.bean.Member;
public interface IMemberService {
      public boolean add(Member vo) throws Exception;
      public boolean edit(Member vo) throws Exception;
      public boolean remove(Set<String> ids) throws Exception;
      public Member getById(String id) throws Exception;
      public List<Member> list() throws Exception;
}
package com.curtis.mysql.service.proxy;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import com.curtis.mysql.dbc.DatabaseConnection;
public class ServiceProxy implements InvocationHandler {
      private Object target;
      @SuppressWarnings("unchecked")
      public <T> T bind(Class<T> cls) {
             try {
                   this.target = cls.newInstance();
                   return (T) Proxy.newProxyInstance(this.target.getClass().getClassLoader(),
                                this.target.getClass().getInterfaces(), this);
             } catch (Exception e) {
                   e.printStackTrace();
             }
             return null;
      }
      @Override
      public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
             try {
                   Object ret = null;
                   String methodName = method.getName();
                   if (methodName.startsWith("add") || methodName.startsWith("edit") || methodName.startsWith("remove")) {
                          try {
                                DatabaseConnection.getConnection().setAutoCommit(false);
                                ret = method.invoke(this.target, args);
                                DatabaseConnection.getConnection().commit();
                          } catch (Exception e) {
                                e.printStackTrace();
                                DatabaseConnection.getConnection().rollback();
                          }
                   } else {
                          ret = method.invoke(this.target, args);
                   }
                   return ret;
             } catch (Exception e) {
                   throw e;
             } finally {
                   DatabaseConnection.close();
             }
      }
}
package com.curtis.mysql.factory;
import com.curtis.mysql.service.proxy.ServiceProxy;
public class ServiceFactory {

      private ServiceFactory() {}

      public static <T> T getInstance(Class<T> cls) {
             try {
                   return new ServiceProxy().bind(cls);
             } catch (Exception e) {
                   e.printStackTrace();
             }
             return null;
      }
}
package com.curtis.mysql.service.impl;
import java.util.List;
import java.util.Set;
import com.curtis.mysql.bean.Member;
import com.curtis.mysql.dao.IMemberDAO;
import com.curtis.mysql.dao.impl.MemberDAOImpl;
import com.curtis.mysql.factory.DAOFactory;
import com.curtis.mysql.service.IMemberService;
public class MemberServiceImpl implements IMemberService {
      @Override
      public boolean add(Member vo) throws Exception {
             IMemberDAO dao = DAOFactory.getInstance(MemberDAOImpl.class);
             if (dao.findById(vo.getMid()) == null) {// 如果id 不存在再判断电话
                   if (dao.findByPhone(vo.getPhone()) == null) {// 电话唯一，所以相同电话不允许再次写入
                          if (vo.getAge() <= 0) {
                                vo.setAge(-1);
                          }
                          return dao.doCreate(vo);
                   }
             }
             return false;
      }
      @Override
      public boolean edit(Member vo) throws Exception {
             IMemberDAO dao = DAOFactory.getInstance(MemberDAOImpl.class);
             Member temp = dao.findByPhone(vo.getPhone());
             if (temp == null) {
                   return dao.doUpdate(vo);
             } else {
                   if (vo.getMid().equals(temp.getMid())) {
                          return dao.doUpdate(vo);
                   }
             }
             return false;
      }
      @Override
      public boolean remove(Set<String> ids) throws Exception {
             if (ids.size() == 0 || ids == null) {
                   return false;
             }
             return DAOFactory.getInstance(MemberDAOImpl.class).doRemoveBatch(ids);
      }
      @Override
      public Member getById(String id) throws Exception {
             return DAOFactory.getInstance(MemberDAOImpl.class).findById(id);
      }
      @Override
      public List<Member> list() throws Exception {
             return DAOFactory.getInstance(MemberDAOImpl.class).findAll();
      }
}
package com.curtis.mysql.test.junit;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.junit.jupiter.api.Test;
import com.curtis.mysql.bean.Member;
import com.curtis.mysql.factory.ServiceFactory;
import com.curtis.mysql.service.IMemberService;
import com.curtis.mysql.service.impl.MemberServiceImpl;
class IMemberServiceTest {
      @Test
      void testAdd() throws Exception {
             // 新增
             Member mem = new Member();
             mem.setMid("1001");
             mem.setName("Curtis");
             mem.setAge(27);
             mem.setPhone("15012981111");
             mem.setBirthday(new Date());
             mem.setNote("...");
             IMemberService service = ServiceFactory.getInstance(MemberServiceImpl.class);
             System.out.println(service.add(mem));
      }
      @Test
      void testEdit() throws Exception {
             // 修改
             Member mem = new Member();
             mem.setMid("1001");
             mem.setName("Curtis");
             mem.setAge(27);
             mem.setPhone("15012980000");
             mem.setBirthday(new Date());
             mem.setNote("...");
             IMemberService service = ServiceFactory.getInstance(MemberServiceImpl.class);
             System.out.println(service.edit(mem));
      }
      @Test
      void testRemove() throws Exception {
             // 删除
             IMemberService service = ServiceFactory.getInstance(MemberServiceImpl.class);
             Set<String> ids = new HashSet<String>();
             ids.add("1001");
             System.out.println(service.remove(ids));
      }
      @Test
      void testGet() throws Exception {
             // 根据id 查询
             Member mem = new Member();
             mem.setMid("1001");
             IMemberService service = ServiceFactory.getInstance(MemberServiceImpl.class);
             System.out.println(service.getById(mem.getMid()));
      }
      @Test
      void testList() throws Exception {
             // 查询所有记录
             try {
                   IMemberService service = ServiceFactory.getInstance(MemberServiceImpl.class);
                   List<Member> list = service.list();
                   System.out.println(list.toString());
             } catch (Exception e) {
                   e.printStackTrace();
             }
      }
}