JDBC练习题—Dao练习

骑猪看日落 2022-06-06 03:42 217阅读 0赞

VO：Person类

package com.test.vo; import java.util.Date; /** * 对应person表的所有字段 * @author dingshuangen * */ public class Person { private Integer personId; private String personName; private String degree; private Date birth; private Integer sal; public Integer getPersonId() { return personId; } public void setPersonId(Integer personId) { this.personId = personId; } public String getPersonName() { return personName; } public void setPersonName(String personName) { this.personName = personName; } public String getDegree() { return degree; } public void setDegree(String degree) { this.degree = degree; } public Date getBirth() { return birth; } public void setBirth(Date birth) { this.birth = birth; } public Integer getSal() { return sal; } public void setSal(Integer sal) { this.sal = sal; } public Person() { super(); } public Person(String personName, String degree, Date birth, Integer sal) { super(); this.personName = personName; this.degree = degree; this.birth = birth; this.sal = sal; } @Override public String toString() { return "Person [personId=" + personId + ", personName=" + personName + ", degree=" + degree + ", birth=" + birth + ", sal=" + sal + "]"; } }

BaseDao类

package com.test.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 数据库的通用操作类 * @author dingshuangen * */ public class BaseDao { /** * 获得连接 * @return */ public Connection getConnection() { Connection conn=null; try { conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","0325"); return conn; }catch(Exception e) { e.printStackTrace(); } return conn; } /** * 关闭实现AutoCloseable接口的资源 * @param auto */ public void close(AutoCloseable auto) { if(auto!=null) { try { auto.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 根据给定的sql及参数执行sql语句 * @param sql * @param objs参数 */ public void executeSql(String sql,Object...objs) { Connection conn=this.getConnection(); PreparedStatement ps=null; try { ps=conn.prepareStatement(sql); //为sql语句中的？占位符设置具体参数 for(int i=0;i<objs.length;i++) { ps.setObject(i+1,objs[i]); } //执行 ps.execute(); System.out.println("*****执行( "+sql+" )成功*****"); }catch(Exception e) { e.printStackTrace(); }finally { this.close(ps); this.close(conn); } } /** * 获得查询出来的数据表 * @param sql * @param objs * @return */ public List<Map<String,Object>> executQuery(String sql,Object...objs){ List<Map<String,Object>> list=new ArrayList<Map<String,Object>>(); Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; try { //获得连接 conn=this.getConnection(); //创建声明 ps=conn.prepareStatement(sql); //设置参数 for(int i=0;i<objs.length;i++) { ps.setObject(i+1, objs[i]); } //获得查询得到的集合 rs=ps.executeQuery(); //获得元数据集合 ResultSetMetaData rsmd=rs.getMetaData(); while(rs.next()) { //创建一个Map用来存储查询到的信息，列名为键，数据为值 Map<String,Object> map=new HashMap<String,Object>(); for(int i=1;i<=rsmd.getColumnCount();i++) { //将数据添加到map中 map.put(rsmd.getColumnLabel(i), rs.getObject(i)); } //将map添加到list中 list.add(map); } return list; }catch(Exception e) { e.printStackTrace(); }finally { this.close(rs); this.close(ps); this.close(conn); } return list; } }

Dao接口：

package com.test.dao; import java.util.List; import com.test.vo.Person; public interface PersonDao { /** * 插入一个人员 * @param person：被插入的人员对象 */ public void insert(Person person); /** * 修改一个人员： * @param person：被修改的人员对象，人员对象只含有人员编号，修改此编号的员工信息 */ public void update(Person person); /** * 根据人员编号删除多个人员 * @param ids：被删除的人员编号数组 */ public void delete(int[] ids); /** * 查询所有的人员，将查询出的人员存储在List中 * @return :所有人员对象的集合 */ public List<Person> getAll(); /** * 查询某一页的人员 * @param pagesize：每页的记录条数 * @param pageOrder：页号（即第几页） * @return */ public List<Person> getPersonForPage(int pagesize,int pageOrder); /** * 根据人员编号，查询出此编号对应的人员 * @return */ public Person getPersonById(int id) ; }

Dao接口的实现类：

package com.test.dao; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import com.test.vo.Person; public class PersonDaoImpl extends BaseDao implements PersonDao { @Override public void insert(Person person) { this.executeSql("insert into Person values(null,?,?,?,?)", person.getPersonName(),person.getDegree(),person.getBirth(),person.getSal()); } @Override public void update(Person person) { this.executeSql("update person set personname=?,degree=?,birth=?,sal=? where personid=?", person.getPersonName(),person.getDegree(),person.getBirth(),person.getSal(),person.getPersonId()); } @Override public void delete(int[] ids) { for(int i=0;i<ids.length;i++) { this.executeSql("delete from person where personid=?", ids[i]); } } @Override public List<Person> getAll() { List<Map<String,Object>> l=this.executQuery("select * from person order by personid"); return this.convert(l); } @Override public List<Person> getPersonForPage(int pagesize, int pageOrder) { return this.convert(this.executQuery("select * from person limit ?,? ", (pageOrder-1)*pagesize,pagesize)); } @Override public Person getPersonById(int id) { Person p=null; List<Person> list= this.convert(this.executQuery("select * from person where personid=?", id)); if(list!=null) { p=list.get(0); } return p; } /** * 转换方法，将List<Map<String,Object>>转换成List<Person> * @param l 查询得到的初始数据 * @return */ private List<Person> convert(List<Map<String,Object>> l){ List<Person> list=new ArrayList<Person>(); for (Map<String, Object> map : l) { Person p=new Person(); //将所有的数据添加到一个Person中 p.setPersonId((Integer)map.get("personid")); p.setPersonName((String)map.get("personname")); p.setBirth((Date)map.get("birth")); p.setDegree((String)map.get("degree")); p.setSal((Integer)map.get("sal")); list.add(p); } return list; } }

测试方法：

package com.test.test; import java.sql.Date; import com.test.dao.PersonDao; import com.test.dao.PersonDaoImpl; import com.test.vo.Person; public class PersonDaoTest { public static void main(String[] args) { PersonDao dao=new PersonDaoImpl(); System.out.println("当前所有的数据：\n"+dao.getAll()); System.out.println("-------------------------------------------------"); Date d=Date.valueOf("1997-02-20"); dao.insert(new Person("小花","大专",d,5820)); System.out.println("-------------------------------------------------"); dao.insert(new Person("小强","硕士",Date.valueOf("1998-03-18"),9852)); System.out.println("-------------------------------------------------"); System.out.println("插入新的数据之后：\n"+dao.getAll()); System.out.println("-------------------------------------------------"); System.out.println("根据id查找：\n"+dao.getPersonById(2)); System.out.println("-------------------------------------------------"); System.out.println("更新信息："); Person p=new Person("小红","高中",Date.valueOf("2000-8-9"),6300); p.setPersonId(2); dao.update(p); System.out.println("根据id查找：\n"+dao.getPersonById(2)); System.out.println("-------------------------------------------------"); System.out.println("分页查找："); System.out.println(dao.getPersonForPage(5, 2)); System.out.println("-------------------------------------------------"); System.out.println("执行批量删除："); dao.delete(new int[]{1,2,3}); System.out.println("-------------------------------------------------"); System.out.println("删除数据之后所有数据：\n"+dao.getAll()); System.out.println("-------------------------------------------------"); } }

执行测试方法结果：

当前所有的数据： [Person [personId=1, personName=gggg, degree=博士, birth=1996-03-15, sal=9690] , Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900] , Person [personId=3, personName=iiii, degree=本科, birth=1990-06-08, sal=7800] , Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800] , Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000] , Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] ] ------------------------------------------------- *****执行( insert into Person values(null,?,?,?,?) )成功***** ------------------------------------------------- *****执行( insert into Person values(null,?,?,?,?) )成功***** ------------------------------------------------- 插入新的数据之后： [Person [personId=1, personName=gggg, degree=博士, birth=1996-03-15, sal=9690] , Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900] , Person [personId=3, personName=iiii, degree=本科, birth=1990-06-08, sal=7800] , Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800] , Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000] , Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=15, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=16, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] ] ------------------------------------------------- 根据id查找： Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900] ------------------------------------------------- 更新信息： *****执行( update person set personname=?,degree=?,birth=?,sal=? where personid=? )成功***** 根据id查找： Person [personId=2, personName=小红, degree=高中, birth=2000-08-09, sal=6300] ------------------------------------------------- 分页查找： [Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000] , Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820] ] ------------------------------------------------- 执行批量删除： *****执行( delete from person where personid=? )成功***** *****执行( delete from person where personid=? )成功***** *****执行( delete from person where personid=? )成功***** ------------------------------------------------- 删除数据之后所有数据： [Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800] , Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000] , Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] , Person [personId=15, personName=小花, degree=大专, birth=1997-02-20, sal=5820] , Person [personId=16, personName=小强, degree=硕士, birth=1998-03-18, sal=9852] ] -------------------------------------------------