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));
}
}
结果通过,可以进行下一步的操作…