dbutils,使用commons-dbutils简化JDBC操作(一)

 commons-dbutils 是Apache提供的一系列通用类库中的一人,主要是用来简化JDBC的操作,我个人感觉最核心的就是其中的转换器.
核心类:
org.apache.commons.dbutils.DbUtils 主要负责加载驱动功能
DbUtils.loadDriver(CLASSNAME);代替原来的Class.forName(CLASSNAME);
org.apache.commons.dbutils.QueryRunner
用来执行SQL语句的,可以是带参数的
org.apache.commons.dbutils.handlers.BeanListHandler
ResultSet转JavaBean List的处理器
示例:
DBM.java
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import org.apache.commons.dbutils.DbUtils; public class DBM { private static final String CLASSNAME="com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static final String URL="jdbc:sqlserver://127.0.0.1:1433;databasename=MybookShop"; private static final String UID="sa"; private static final String PWD=""; private Connection conn; public Connection getConn() { return conn; } public DBM() throws SQLException{ DbUtils.loadDriver(CLASSNAME); conn=DriverManager.getConnection(URL,UID,PWD); } public void colseConn(){ try { DbUtils.close(conn); } catch (SQLException e) { e.printStackTrace(); } } }
实体类 UserRoleVo.java
public class UserRoleVo { int id; String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "UserRoleVo [id=" + id + ", name=" + name + "]"; } }
数据访问层类UserRoleDAO.java:
 import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import com.accp.db.DBM; import com.accp.vo.UserRoleVo; public class UserRoleDao { public List<UserRoleVo> findAll() throws SQLException{ DBM dbm=new DBM(); QueryRunner ql=new QueryRunner(); BeanListHandler handler=new BeanListHandler(UserRoleVo.class); List<UserRoleVo> list=(List<UserRoleVo>) ql.query(dbm.getConn(), "select * from userRoles",handler); dbm.colseConn(); return list; } public List<UserRoleVo> findAllByName(String name) throws SQLException{ DBM dbm=new DBM(); QueryRunner ql=new QueryRunner(); BeanListHandler handler=new BeanListHandler(UserRoleVo.class); List<UserRoleVo> list=(List<UserRoleVo>) ql.query(dbm.getConn(), "select * from userRoles where name like ?","%"+name+"%",handler); dbm.colseConn(); return list; } public void add(UserRoleVo role) throws SQLException{ DBM dbm=new DBM(); QueryRunner qr=new QueryRunner(); qr.update(dbm.getConn(),"insert into userRoles values (?)",role.getName()); dbm.colseConn(); } }
 如果有多个参数需要使用Object[]来处理参数:
 public UserVo login(String name,String pwd) throws SQLException{ DBM dbm=new DBM(); QueryRunner ql=new QueryRunner(); BeanListHandler handler=new BeanListHandler(UserRoleVo.class); Object[] paras=new Object[]{name,pwd}; List<UserVo> list=(List<UserVo>) ql.query(dbm.getConn(), "select * from users where loginid=? and loginpw=?",paras,handler); dbm.colseConn(); return (list.size()==1)?liset.get(0):null; }
 自己写测试类,程序应该没有什么问题!

延伸阅读

• 2011-6-26-- dbutils,使用commons-dbutils简化JDBC操作(二) dbutils+反射
• 2011-2-21-- dbutils,Jakarta Commons——DBUtils
• 2010-12-21-- dbutils,dbutils框架
• 2011-3-27-- dao数据库编程,采用Apache dbutils操作数据库的DAO类,...