【SQL】JDBC之运用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);//把结果集转成BeanListtry {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();//把单条结果集封装成一个Maptry {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 blockSystem.out.println("属性文件未找到");}String u = pro.getProperty("user");String password = pro.getProperty("password");String url = pro.getProperty("url");//关于连接Oracle的两种方式：thin和ociconn = 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);//把结果集转成BeanListtry {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();//把单条结果集封装成一个Maptry {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("蓝精灵"));}*/}