【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);//把结果集转成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("蓝精灵"));
	}*/
}