用dbutils组件实现对数据库的增删改查等操作

实例化查询接口

 QueryRunner qr = new QueryRunner();//实例化查询接口

添加

 /** 添加电影 */   public int insert(Movie movie) {    Object[] params = {movie.getName(), movie.getType(), movie.getCountry(), movie.getYear(),      movie.getMonth(), movie.getIntro(), movie.getPic()};    try {     return qr.update(getConn(), "insert into t_movie(name,type,country,year,month,intro,pic)" +       " values(?,?,?,?,?,?,?)", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }

删除

 /** 根据PK删除电影 */   public int delete(Integer id) {    Object[] params = {id};    try {     return qr.update(getConn(), "delete from t_movie where id=?", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }

修改

 /** 根据PK修改电影 */   public int update(Movie movie) {    Object[] params = {movie.getName(), movie.getType(), movie.getCountry(), movie.getId()};    try {     return qr.update(getConn(), "update t_movie set name=?,type=?,country=? where id=?", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }

查询

 /** 根据条件(默认一张表所有数据)返回多条记录 */   public List<Movie> list(String kw) {    List<Movie> list = null;    Object[] params = {};//代入的参数列表    String sqlWhere = "";    String sql = "select * from t_movie where 1=1 ";    if(kw!=null && !kw.equals("")) {     sqlWhere = " and name like '%"+kw+"%'";    }    sql += sqlWhere;    ResultSetHandler<List<Movie>> rsh = new BeanListHandler<Movie>(Movie.class);//把结果集转成BeanList    try {     list = qr.query(getConn(), sql, rsh, params); //调用查询接口的查询函数    } catch (SQLException e) {     e.printStackTrace();    }    return list;   }

匹配

 /** 根据PK显示单条电影信息 */   public Movie getOne(Integer id) {    Movie movie = null;    Object[] params = {id};    ResultSetHandler<Movie> rsh = new BeanHandler<Movie>(Movie.class);//把单条结果集封装成一个Bean实例    try {     movie = qr.query(getConn(), "select * from t_movie where id=?", rsh, params);     } catch (SQLException e) {     e.printStackTrace();    }    return movie;   }

唯一性验证

 /** 检测同名电影是否添加过 */   public Long validateMovieName(String name) {    Map<String, Object> map = null;    Object[] params = {name};    ResultSetHandler<Map<String, Object>> rsh = new MapHandler();//把单条结果集封装成一个Map    try {     map = qr.query(getConn(), "select count(1) nums from t_movie where name=?", rsh, params);     } catch (SQLException e) {     e.printStackTrace();    }    return (Long)map.get("nums");   }

BaseDAO.java文件中

 Connection conn;

 /** 返回一个Connection */   public Connection getConn(){    try {     Properties pro = new Properties();     try {      Class.forName("com.mysql.jdbc.Driver").newInstance();      pro.load(BaseDAO.class.getResourceAsStream("/db.properties"));     } catch (Exception e) {      // TODO Auto-generated catch block      System.out.println("属性文件未找到");     }     String u = pro.getProperty("user");     String password = pro.getProperty("password");     String url = pro.getProperty("url");//关于连接Oracle的两种方式：thin和oci     conn = DriverManager.getConnection(url, u, password);    } catch (SQLException e) {     e.printStackTrace();    }    return conn;   }

MovieDAO.java

package com.app.dao;    import java.sql.SQLException;  import java.util.List;  import java.util.Map;    import org.apache.commons.dbutils.QueryRunner;  import org.apache.commons.dbutils.ResultSetHandler;  import org.apache.commons.dbutils.handlers.BeanHandler;  import org.apache.commons.dbutils.handlers.BeanListHandler;  import org.apache.commons.dbutils.handlers.MapHandler;    import com.app.domain.Movie;    public class MovieDAO extends BaseDAO {      QueryRunner qr = new QueryRunner();//实例化查询接口      /** 添加电影 */   public int insert(Movie movie) {    Object[] params = {movie.getName(), movie.getType(), movie.getCountry(), movie.getYear(),      movie.getMonth(), movie.getIntro(), movie.getPic()};    try {     return qr.update(getConn(), "insert into t_movie(name,type,country,year,month,intro,pic)" +       " values(?,?,?,?,?,?,?)", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }      /** 根据PK删除电影 */   public int delete(Integer id) {    Object[] params = {id};    try {     return qr.update(getConn(), "delete from t_movie where id=?", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }      /** 根据PK修改电影 */   public int update(Movie movie) {    Object[] params = {movie.getName(), movie.getType(), movie.getCountry(), movie.getId()};    try {     return qr.update(getConn(), "update t_movie set name=?,type=?,country=? where id=?", params);    } catch (SQLException e) {     e.printStackTrace();    }    return 0;   }      /** 根据条件(默认一张表所有数据)返回多条记录 */   public List<Movie> list(String kw) {    List<Movie> list = null;    Object[] params = {};//代入的参数列表    String sqlWhere = "";    String sql = "select * from t_movie where 1=1 ";    if(kw!=null && !kw.equals("")) {     sqlWhere = " and name like '%"+kw+"%'";    }    sql += sqlWhere;    ResultSetHandler<List<Movie>> rsh = new BeanListHandler<Movie>(Movie.class);//把结果集转成BeanList    try {     list = qr.query(getConn(), sql, rsh, params); //调用查询接口的查询函数    } catch (SQLException e) {     e.printStackTrace();    }    return list;   }      /** 根据PK显示单条电影信息 */   public Movie getOne(Integer id) {    Movie movie = null;    Object[] params = {id};    ResultSetHandler<Movie> rsh = new BeanHandler<Movie>(Movie.class);//把单条结果集封装成一个Bean实例    try {     movie = qr.query(getConn(), "select * from t_movie where id=?", rsh, params);     } catch (SQLException e) {     e.printStackTrace();    }    return movie;   }      /** 检测同名电影是否添加过 */   public Long validateMovieName(String name) {    Map<String, Object> map = null;    Object[] params = {name};    ResultSetHandler<Map<String, Object>> rsh = new MapHandler();//把单条结果集封装成一个Map    try {     map = qr.query(getConn(), "select count(1) nums from t_movie where name=?", rsh, params);     } catch (SQLException e) {     e.printStackTrace();    }    return (Long)map.get("nums");   }      /*public static void main(String[] args) {    MovieDAO movieDAO = new MovieDAO();    System.out.println(movieDAO.validateMovieName("蓝精灵"));   }*/  }