用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("藍精靈"));
    }*/
}