JDBC简单实现mysql增删查改(1)

1.准备工作：

• 导包
  目的：取得与数据库的联系
  工具：mysql-connector-java-5.1.38.jar
• 建立连接工具
  目的：获取连接对象

public class ConnectionUtil {
   //完整的地址URL信息；localhost处为IP地址；3306为端口；
   //useSSL=true：表示程序与数据库信息交互采用加密方式传输
   //characterEncoding=UTF-8：表示数据传输过程中进行信息编码转换
	private static String URL = "jdbc:mysql://localhost:3306/demo?useSSL=true&useUnicode=true&characterEncoding=UTF-8";
	private static String DRIVER = "com.mysql.jdbc.Driver";
	private static String USER = "root";
	private static String PASSWORD = "123456";
	private static Connection conn;
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static  Connection getConnection() {
		if (conn==null) {
			try {
				return conn =DriverManager.getConnection(URL, USER, PASSWORD);
			} catch (SQLException e) {
			}
		}
		return null;		
	}
	public static void close() {
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}	
}

2.Modle层的代码书写

• DAO层代码：
  （1.）公共接口

/**
 * @param <K> 对应的实体类的类型对应在实体数据表中的主键值类型integer、string...
 * @param <T> 对应的实体类的类型emp类，dept类..
 */
public interface IDAO<K,T>{
   //插入数据
	public int add(T t) throws Exception;
	//更新数据
	public int update(T t) throws Exception;
	//删除数据
	public int delete(K empno) throws Exception;
	//查看数据
	public Set<T> select(K deptno) throws Exception;
}

（2.）一种数据表接口继承 此接口

public interface IEmpDAO extends IDAO<Integer, Emp>{
	//此处可以定义自己接口特有的标准；
}

（3.）写DButil工具

public class DButil {
	private static PreparedStatement ps;
//增加一条数据	
	public static int insert(Connection conn,Emp e,String sql) throws Exception {
		 ps = conn.prepareStatement(sql);
		 Class<? extends Emp> clz = e.getClass();
		 Field[] fields = clz.getDeclaredFields();
		 for (int i = 0; i < fields.length; i++) {
			String fieldname=fields[i].getName();
			Method getMethod = clz.getDeclaredMethod("get"+toUpase(fieldname));
			Object obj = getMethod.invoke(e);
			ps.setObject(i+1, obj);
		}
		  return ps.executeUpdate();	
	}
//	根据编号删除一条数据
	public static int delete(Connection conn,String sql,Integer empno) throws Exception{
		ps=conn.prepareStatement(sql);
		ps.setObject(1, empno);		
		return ps.executeUpdate();		
	}
//	更新成给定的vo类中对应的的信息
	public static int update(Connection conn,String sql,Emp e) throws Exception {
		ps=conn.prepareStatement(sql);
		Class<? extends Emp> clz = e.getClass();
		String[] s = sql.split("SET")[1].split("WHERE")[0].split(",");
		for (int i = 0; i < s.length; i++) {
			String name = s[i].split("=")[0].trim();
			String getMethodName="get"+toUpase(name);
			Method method = clz.getDeclaredMethod(getMethodName);
			Object obj = method.invoke(e);
			ps.setObject(i+1, obj);
		}
		String empno = sql.split("WHERE")[1].split("=")[0].trim();
		String getMethodName="get"+toUpase(empno);
		Method method = clz.getDeclaredMethod(getMethodName);
		Object obj = method.invoke(e);
		ps.setObject(s.length+1, obj);
		return ps.executeUpdate();		
	}
//	选择出指定部门编号的vo类组成的集合
	public static Set<Emp> selectByDepno(Connection conn,String sql,Class<Emp> clz,Integer deptno) throws Exception{
		Set<Emp> s = new TreeSet<Emp>(new Comparator<Emp>() {
			@Override
			public int compare(Emp o1, Emp o2) {				
				double sum1 = o1.getDeptno()-o2.getDeptno();
				double sum2 = (sum1==0)?o1.getComm()-o2.getComm():sum1;
				double sum3 = (sum2==0)?o1.getSal()-o2.getSal():sum2;
				double sum4 = (sum3==0)?o1.getHiredate().compareTo(o2.getHiredate()):sum3;	
				double sum5 = (sum4==0)?o1.getMgr()-o2.getMgr():sum4;
				double sum6 = (sum5==0)?o1.getJob().compareTo(o2.getJob()):sum5;
				double sum7 = (sum6==0)?o1.getEname().compareTo(o2.getEname()):sum6;
				double sum8 = (sum7==0)?o1.getEmpno()-o2.getEmpno():sum7;
				return sum8>0?1:(sum8==0?0:-1);
			}
		});		
		ps= conn.prepareStatement(sql);
		ps.setObject(1, deptno);		
		ResultSet rs = ps.executeQuery();		
//		读取返回的每一条结果
		while (rs.next()) {
			Emp e = clz.newInstance();
//			反射获取vo类的每个属性，以便获取的SET方法
			Field[] fields = clz.getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
//				获取到每个属性的名字，例如String类型的ename...
				String name = fields[i].getName();
//				得到SET方法的全名：setEname、setEmpno...
				String methodName="set"+toUpase(name);
//				从ResultSet中获取数据库中返回的例如deptno、ename对应的结果
				Object obj = rs.getObject(name);
//				获取到对应属性值的SET方法,注意这里set方法是有参数的fields[i].getType()
				Method setMethod = clz.getDeclaredMethod(methodName, fields[i].getType());
//				使用set方法将数据库中返回结果保存到vo类中
				try {
					setMethod.invoke(e, obj);
				} catch (Exception e2) {
				}
			}
//			每条结果保存到一个vo类对象后，将其添加到SET集合中
			s.add(e);
		}
		return s;		
	}
	public static String toUpase(String fieldname) {
		return fieldname.substring(0, 1).toUpperCase()+fieldname.substring(1);
		
	}
}

（3.）接口实现类，这里调用了DButil工具

public class EmpDAOimpl implements IEmpDAO {
	private  Connection conn;	
	public EmpDAOimpl() {
		super();
	}
	public EmpDAOimpl(Connection conn) {
		super();
		this.conn=conn;		
	}
	
	@Override
	public  int add(Emp e) throws Exception{
		String sql="INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(?,?,?,?,?,?,?,?)";				
		return DButil.insert(conn, e, sql);
	}
	@Override
	public int delete(Integer empno) throws Exception {
		String sql="DELETE FROM emp WHERE empno=?";		
		return DButil.delete(conn, sql, empno);
	}
	@Override
	public int update(Emp e) throws Exception {
		String sql="UPDATE emp SET ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? WHERE empno=?";
		return DButil.update(conn, sql, e);
	}
	@Override
	public Set<Emp> select(Integer deptno) throws Exception {
		String sql = "SELECT * FROM emp WHERE deptno=?";
		return DButil.selectByDepno(conn, sql, Emp.class, deptno);
	}	
}

• SERIVE层代码：
  （1.）公共接口

/**
 * @author Administrator
 * @param <K> Integer类型
 * @param <V> VO类
 */
public interface ISERIVE<K,V>{
	public boolean insertMethod(V v) throws Exception;
	public boolean deleteMethod(K k) throws Exception;
	public boolean updateMethod(V v) throws Exception;
	public boolean selectMethod(K k) throws Exception;	
}

（2.）继承此接口的某一接口

public interface IEmpSERIVE extends ISERIVE<Integer, Emp>{
}

（3.）接口实现类

public class EmpSeriveimpl implements IEmpSERIVE{
	private Connection conn = ConnectionUtil.getConnection();
	private EmpDAOimpl ed = new EmpDAOimpl(conn);	
	public EmpSeriveimpl() {
		super();
	}
	@Override
	public boolean insertMethod(Emp v) throws Exception  {
		try {
			return ed.add(v)>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			conn.close();
		}
		return false;
	}

	@Override
	public boolean deleteMethod(Integer k) throws Exception{
		try {
			return ed.delete(k)>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			conn.close();
		}
		return false;
	}

	@Override
	public boolean updateMethod(Emp v) throws Exception{
		try {
			return ed.update(v)>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			conn.close();
		}
		return false;
	}

	@Override
	public boolean selectMethod(Integer k) throws Exception{
		try {
			return ed.select(k).size()>0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			conn.close();
		}
		return false;
	}	
}

写到这里可以用JUnit单元测试检查一下我们的方法：

public class ISERIVETest {
	private EmpSeriveimpl es = new EmpSeriveimpl();
	@Test
	public void testInsertMethod() throws Exception {
		Emp e = new Emp(5656, "艾斯黛拉", "程序员", 7788, new Date(), 9999.0, 5000.0, 30);		
		TestCase.assertTrue(es.insertMethod(e));
	}

	@Test
	public void testDeleteMethod() throws Exception {
		
		TestCase.assertTrue(es.deleteMethod(6666));
	}

	@Test
	public void testUpdateMethod() throws Exception {
		Emp e = new Emp(7788, "张宇", "程序员", 7788, new Date(), 4444.0, 5000.0, 30);
		TestCase.assertTrue(es.updateMethod(e));
	}

	@Test
	public void testSelectMethod() throws Exception {
		TestCase.assertTrue(es.selectMethod(30));
	}

}

结果通过，可以进行下一步的操作…