- 一.下载:http://commons.apache.org/dbutils/
- 二.使用实例
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.util.List;
- import java.util.Map;
- import org.apache.commons.dbutils.DbUtils;
- import org.apache.commons.dbutils.QueryRunner;
- import org.apache.commons.dbutils.handlers.BeanListHandler;
- import org.apache.commons.dbutils.handlers.MapListHandler;
- public class DBUtil {
- public static void main(String[] args) {
- DBUtil dBUtil=new DBUtil();
- List result=dBUtil.queryReturnMapList("select * from user_v_o");
- for (int i = 0; i < result.size(); i++) {
- Map map = (Map) result.get(i);
- System.out.println(map);
- }
- List beans=dBUtil.queryReturnBeanList("select user_code as userCode,user_name as UserName,user_desc as userDesc from user_v_o", User.class);
- for (int i = 0; i < beans.size(); i++) {
- User user = (User) beans.get(i);
- System.out.println("BeanList:"+user);
- }
- }
- /**
- * 获取数据库连接
- * @return
- */
- public Connection getConnection() {
- Connection conn = null;
- String jdbcDriver = "com.mysql.jdbc.Driver";
- String jdbcURL = "jdbc:mysql://localhost:3306/test";
- String user = "root";
- String password = "root";
- try {
- DbUtils.loadDriver(jdbcDriver);
- conn = DriverManager.getConnection(jdbcURL, user, password);
- } catch (SQLException e) {
- // handle the exception
- e.printStackTrace();
- }
- return conn;
- }
- /**
- * 它把数据库中的每一行映射成一个Map,其中列名作为Key,该列对应的值作为Value存放,查询的所有的数据一起放在一个List里
- * @param sqlString
- * @param clazz
- * @return
- */
- public List queryReturnMapList(String sqlString) {
- List maps = null;
- Connection conn = null;
- try {
- conn = getConnection();
- QueryRunner qRunner = new QueryRunner();
- maps =
- (List) qRunner.query(
- conn,
- sqlString,
- new MapListHandler());
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- DbUtils.closeQuietly(conn);
- }
- return maps;
- }
- /**
- * 查找多个对象,将ResultSet中所有的数据转化成List,List中存放的是类对象,类对象的属性要与数据库的属性名相同,这样才能对应上.
- * 对于一些数据表中的属性可能还有一些特殊字符,与javaBean中的属性名语法不符,可以在sql中加上别名,如数据字段 as javabean属性名
- * @param sqlString
- * @param clazz
- * @return
- */
- public List queryReturnBeanList(String sqlString, Class clazz) {
- List beans = null;
- Connection conn = null;
- try {
- conn = getConnection();
- QueryRunner qRunner = new QueryRunner();
- beans =
- (List) qRunner.query(
- conn,
- sqlString,
- new BeanListHandler(clazz));
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- DbUtils.closeQuietly(conn);
- }
- return beans;
- }
- /**
- * 查找对象,取得其中的一个对象
- * @param sqlString
- * @param clazz
- * @return
- */
- public Object get(String sqlString, Class clazz) {
- List beans = null;
- Object obj = null;
- Connection conn = null;
- try {
- conn = getConnection();
- QueryRunner qRunner = new QueryRunner();
- beans =
- (List) qRunner.query(
- conn,
- sqlString,
- new BeanListHandler(clazz));
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- DbUtils.closeQuietly(conn);
- }
- if(beans!=null && !beans.isEmpty()){ //注意这里
- obj=beans.get(0);
- }
- return obj;
- }
- /**
- * 执行更新的sql语句,插入,修改,删除
- * @param sqlString
- * @return
- */
- public boolean update(String sqlString) {
- Connection conn = null;
- boolean flag = false;
- try {
- conn = getConnection();
- QueryRunner qRunner = new QueryRunner();
- int i = qRunner.update(conn,sqlString);
- if (i > 0) {
- flag = true;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- DbUtils.closeQuietly(conn);
- }
- return flag;
- }
- }
- User.java:
- public class User {
- private String userCode;
- private String userName;
- private String userDesc;
- .....
- }
利用dbutils简化数据库操作
最新推荐文章于 2023-07-14 22:04:10 发布