一】概念:可以理解为对象和数据库的映射。
二】常用O-R Mapping映射工具
1)Hibernate(全自动框架)
2)lbatis(半自动框架)
3)Commons Dbutils(只是对JDBC的简单封装)
二、DBUtils框架(核心类:QueryRunner)
一】QueryRunner类
1》构造参数:
public QueryRunner();
public QueryRunner(DataSource dataSource);
2》update方法
1)无Connection参数(对应非空参构造):
int update(String sql, Object[] objs);
注意:内部已经关闭了连接数据库的流对象,不需要进行手动关闭(查看源码)
code:
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); String sql = " INSERT INTO meta.user(username,salary) VALUES(?, ?) "; qr.update(sql, new Object[]{"jack", 9700.5}); sql = " UPDATE meta.user SET salary = ? WHERE username = ?"; qr.update(sql, new Object[]{10000, "merry"}); sql = " DELETE FROM meta.user WHERE username = ? "; qr.update(sql, new Object[]{"xixi"});
2)有Connection参数(对应空参构造):
int update(Connection conn, String sql, Object[] objs);
注意:内部除Connection外的对象已经自动关闭,但Connection并没有自动关闭,
故,需要手动关闭Connection对象。
code:
QueryRunner qr = new QueryRunner(); QueryRunner qr = new QueryRunner(); Connection conn = C3P0Utils.getMySqlConnection(); String sql = " INSERT INTO meta.user(username, salary) VALUES(?, ?) "; qr.update(conn, sql, new Object[]{"hehe", 7000.3});
3》query()方法
1)同样有带有参数Connection和不带有参数Connnection两种
2)需要ResultSetHandler接口的实现类配合使用。
三】ResultSetHandler接口
实现类:
1)BeanHeadler/BeanListHandler:针对Bean
BeanHeadler:将查询结果的第一行结果封装成一个Bean对象
BeanListHandler:将所有行的查询结果封装成多个Bean对象,并将Bean对象存入List中
2)ArrayHandler/ArrayListHandler:针对数组
ArrayHandler:将查询结果的第一行结果添加到一个数组中
ArrayListHandler:将所有行的查询结果添加到多个数组中,并将数组存入List中
3)MapHandler/MapListHandler:针对Map
MapHandler:将查询结果的第一行结果,按照 <每列字段,每列结果> 的方式放入Map集合中
MapListHandler:将查询结果的多行结果,按照 <每列字段,每列结果> 的方式放入多个Map集合中,并将这些Map集合放入List中
4)ScalarHandler:针对Long
将查询结果的 第一行,第一列 的数据返回。
code:
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); String sql = " SELECT * FROM meta.user "; //BeanHandler: User user = (User) qr.query(sql, new BeanHandler(User.class));//1 : merry : 10000.0 System.out.println(user); //BeanListHandler: List<User> list = (List<User>) qr.query(sql, new BeanListHandler(User.class)); for (User u : list) { System.out.println(u); } /*1 : merry : 10000.0 1 : merry : 10000.0 2 : berry : 10000.0 3 : hehe : 7000.3*/ //ArrayHandler Object[] info = (Object[]) qr.query(sql, new ArrayHandler()); for (int i = 0; i < info.length; i++) { System.out.println(info[i]);//1 merry 10000.0 } //ArrayListHandler List<Object[]> list = (List<Object[]>) qr.query(sql, new ArrayListHandler()); for (Object[] obj : list) { for (int i = 0; i < obj.length; i++) { System.out.print(obj[i] + " : "); } System.out.println(); } /* 1 : merry : 10000.0 : 2 : berry : 10000.0 : 3 : hehe : 7000.3 : */ //MapHandler Map<String, Object> map = (Map<String, Object>) qr.query(sql, new MapHandler()); int id = (int) map.get("id"); String username = (String) map.get("username"); double salary = (double) map.get("salary"); System.out.println(id + " : " + username + " : " + salary);//1 : merry : 10000.0 //MapListHandler List<Map<String, Object>> list = (List<Map<String, Object>>) qr.query(sql, new MapListHandler()); for (Map<String, Object> map : list) { int id = (int) map.get("id"); String username = (String) map.get("username"); double salary = (double) map.get("salary"); System.out.println(id + " : " + username + " : " + salary); } /*1 : merry : 10000.0 2 : berry : 10000.0 3 : hehe : 7000.3*/ //ScalarHandler String sca_sql = " SELECT COUNT(*) FROM meta.user "; long cnt = (long) qr.query(sca_sql, new ScalarHandler()); System.out.println(cnt);//3
三、应用
1)一对多/多对一 关系中: (部门与员工)
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); //1,将部门插入表department中 String INSERT_DEPARTMENT = " INSERT INTO dbutils.department(name) VALUES(?) "; qr.update(INSERT_DEPARTMENT, de.getName()); //2,获取部门的主键,可用于表employee表的外键 String DEPART_PRIMARY_KEY = " SELECT id FROM dbutils.department WHERE name = ? "; int did = (int) qr.query(DEPART_PRIMARY_KEY, de.getName(), new ScalarHandler()); //3,将员工信息插入表employee中,并与表department产生关联关系 String INSERT_EMPLOYEE = " INSERT INTO dbutils.employee(name, did) VALUES(?, ?) "; List<Employee> list = de.getList(); for (Employee e : list) { qr.update(INSERT_EMPLOYEE, new Object[]{e.getName(), did}); }
2)多对多 关系中: (学生与老师)
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); //1,插入教师信息到teacher表中 String ADD_TEA_INFO = " INSERT INTO dbutils.teacher(name) values(?) "; qr.update(ADD_TEA_INFO, tea1.getName()); //2,获取插入的教师的id String QUERY_TEA_ID = " SELECT id FROM dbutils.teacher WHERE name = ? "; int tid = (int) qr.query(QUERY_TEA_ID, tea1.getName(), new ScalarHandler()); //3,插入学生信息到表student中 String ADD_STU_INFO = " INSERT INTO dbutils.student(name) VALUES(?) "; List<Student> stus = tea1.getStus(); for (Student stu : stus) { qr.update(ADD_STU_INFO, stu.getName()); } //4,获取所有学生id String ALL_STU_ID = " SELECT id FROM dbutils.student "; List<Object[]> list = (List<Object[]>) qr.query(ALL_STU_ID, new ArrayListHandler()); //5,将学生id和老师id对应插入到表middle中,建立相互联系 String BUILD_REALATION = " INSERT INTO dbutils.middle(sid, tid) VALUES(?, ?) "; for ( Object[] sids : list) { qr.update(BUILD_REALATION, new Object[]{sids[0], tid}); }
注意:数据库的多表查询会话费很多时间,所以有时候允许数据的冗余,以空间换取时间。