Apache DBUtils使用总结.doc

Apache DBUtils使用总结DBUtils是个小巧的JDBC轻量级封装的工具包，其最核心的特性是结果集的封装，可以直接将查询出来的结果集封装成JavaBean，这就为我们做了最枯燥乏味、最容易出错的一大部分工作。在使用DBUtils之前，应该注意一些问题：1、DBUtils是JDBC的简单封装，可以和JDBC混合使用。2、DBUtils对结果集自动封装为JavaBean是有着苛刻要求的：必须满足JavaBean的规范，其次Bean的getter与setter方法的名字与结果集的列名一一对应，而不要求JavaBean的私有成员与表结果集列名一一对应。比如：person表中有个字段叫：address，那么对应的JavaBean的Person类中必须有getAddress和setAddress两个方法，而Person类中可以将address属性命名为add，这是没问题的。3、DBUtils可以将结果集封装为各种类型，主要有：Bean/List，Map/List/Map，数组/List，列/List，这些类型。对于Map的类型使用KeyedHandler作为结果集处理器，内层的Map是“列名-值对，外层的Map是“主键-内层Map的引用”，但此处的主键不一定就是数据库的主键，可以随意指定，比如：ResultSetHandler h = new KeyedHandler(id);Map found = (Map) queryRunner.query(select id, name, age from person, h);Map jane = (Map) found.get(new Long(1); / janes id is 1 String janesName = (String) jane.get(name);Integer janesAge = (Integer) jane.get(age);4、DBUtils执行插入操作的时候，无法返回自增主键，这是一个很严重的问题，当然不能怪DBUtils，可以通过变通的方法来实现，比如在MySQL中，执行完了一个插入SQL后，接着执行SELECT LAST_INSERT_ID()语句，就可以获取到自增主键。5、DBUtils的性能和JDBC性能是一样，测试过程中没发现性能损失，拥有了很高性能的同时，而不失JDBC的灵活性。6、对于JavaBean的成员类型定义，有一条原则那就是：尽可能使用包装类型，而不要使用基本类型。很多人不理解为什么，包括我见到一些傻B（，我提出让改为包装类型，他还信誓旦旦的说他的代码多牛多叼），都干好多年了，这个简单的道理还不知道:/错误int a1 = (Integer) null;boolean x1 = (Boolean)null;/正确Integer a2 = (Integer) null;Boolean x2 = (Boolean)null;实际上就是为了保证在查询结果为null的时候，也不会因为给基本类型赋null值而发生错误。下面给出一个简单例子，作为以后写代码时候有点参考：环境：MySQL5.4JDK1.5建表SQL：CREATETABLEperson (id bigint(20) NOT NULL AUTO_INCREMENT,namevarchar(24) DEFAULT NULL,age int(11) DEFAULT NULL,address varchar(120) DEFAULT NULL,PRIMARYKEY(id) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=gbkJava代码：publicclassPerson privateLong id;privateString sdf;privateString address2;privateInteger age;publicPerson() publicPerson(String sdf) this.sdf = sdf;publicPerson(String sdf, Integer age, String address) this.sdf = sdf;this.age = age;this.address2 = address;publicLong getId() returnid;publicvoidsetId(Long id) this.id = id;publicString getSdf() returnsdf;publicvoidsetSdf(String sdf) this.sdf = sdf;publicInteger getAge() returnage;publicvoidsetAge(Integer age) this.age = age;publicString getAddress() returnaddress2;publicvoidsetAddress(String address2) this.address2 = address2;测试packagecom.lavasoft.dbstu;mon.DBToolkit;mons.dbutils.QueryRunner;mons.dbutils.handlers.BeanHandler;mons.dbutils.handlers.BeanListHandler;mons.dbutils.handlers.MapHandler;mons.dbutils.handlers.ScalarHandler;importjava.sql.Connection;importjava.sql.SQLException;importjava.util.List;importjava.util.Map;/* * Created by IntelliJ IDEA. * * author leizhimin 2010-1-25 21:00:29 */publicclassPersonDAOImpl implements PersonDAO privatestaticPersonDAOImpl instance = new PersonDAOImpl();publicstaticPersonDAOImpl getInstance() returninstance;publicstaticvoidmain(String args) /错误 inta1 = (Integer) null;booleanx1 = (Boolean) null;/正确 Integer a2 = (Integer) null;Boolean x2 = (Boolean) null;getInstance().save(null);/ getInstance().save(null); / getInstance().save(null); / getInstance().save(null); / getInstance().save(null); getInstance().update(null);getInstance().load(null);getInstance().load4Map(null);OverridepublicLong save(String sql) Long id = null;String ins_sql = INSERT INTO person (NAME, age, address) VALUES (aaa, 21, address001);Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();tryqr.update(conn, ins_sql);/获取新增记录的自增主键 id = (Long) qr.query(conn, SELECT LAST_INSERT_ID(), new ScalarHandler(1); catch (SQLException e) e.printStackTrace(); finally DBToolkit.closeConnection(conn);returnid;Overridepublicintdelete(Long id) intx = 0;Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();tryx = qr.update(conn, DELETE FROM person WHERE id = ?, id); catch (SQLException e) e.printStackTrace(); finally DBToolkit.closeConnection(conn);returnx;Overridepublicintupdate(Person person) intx = 0;Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();tryx = qr.update(conn, UPDATE person SET NAME = ?, age = ?, address = ? WHERE id = ?, xxx, 23, ttt, 5); catch (SQLException e) e.printStackTrace(); finally DBToolkit.closeConnection(conn);returnx;OverridepublicPerson load(Long id) Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();tryPerson person = (Person) qr.query(conn, SELECT * FROM person where id = ?, new BeanHandler(Person.class), 3L);System.out.println(person.getId() + t + person.getSdf() + t + person.getAge() + t + person.getAddress(); catch (SQLException e) e.printStackTrace();returnnull;OverridepublicList findPerson(String sql) Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();tryList pset = (List) qr.query(conn, SELECT * FROM person, new BeanListHandler(Person.class);for(Person person : pset) System.out.println(person.getId() + t + person.getSdf() + t + person.getAge() + t + person.getAddress(); catch (SQLException e) e.printStackTrace();returnnull;publicPerson load4Map(Long id) Connection conn = DBToolkit.getConnection();QueryRunner qr = new QueryRunner();try/先将两个字段置为null qr.update(conn, update person set age = null,address =null where id =1);Map map = qr.query(conn, SELECT * FROM person where id = ?, new MapHandler(), 1L);Person person = new Person();person.setId(Long) map.get(id);person.setSdf(String) map.get(name);person.setAge(Integer) map.get(age);person.setAddress(String) map.get(address);System.out.println(person.getId() + t + person.getSdf() +