本文共 5280 字，大约阅读时间需要 17 分钟。
1.xml配置
com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/hive hive hive 5 20 oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:XE a 123
2.JDBC
package com.hwua.util;import java.sql.Connection;import java.sql.SQLException;import org.apache.commons.dbutils.QueryRunner;import com.mchange.v2.c3p0.ComboPooledDataSource;/** * 1. 获取数据源 * 2. 获取连接对象 * 3. 返回dbutils的QueryRunner对象 */public class JDBCUtil { private static ComboPooledDataSource dataSource = null; static { dataSource = new ComboPooledDataSource(); } /** * 1.获取数据库连接对象 （事务），这个连接对象需要手动释放 * @return */ public static Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } /** * 2.获取dbutils的QueryRunner对象（非事务），资源会自动释放 * @return */ public static QueryRunner getQueryRunner() { return new QueryRunner(dataSource); } } 3.各类查询写法
1.查询对象 public static void main(String[] args) throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select * from t_emp where id=?";//一个对象 String sql2 = "select * from t_emp where id in(?,?,?)"; Emp emp = qr.query(sql, new BeanHandler (Emp.class),2); List list = qr.query(sql2, new BeanListHandler (Emp.class),2,3,5); System.out.println(emp); System.out.println("-----------------"); for (Emp emp2 : list) { System.out.println(emp2); } }//1.根据用户名查询用户是否存在 @Override public User findUserByName(String name) throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select * from t_user where uname=?"; return qr.query(sql, new BeanHandler (User.class),name); } //2.添加用户 @Override public int addUser(User user) throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "insert into t_user(uname,pwd,mobile,utype) values(?,?,?,?)"; Object [] params = {user.getUname(),user.getPwd(),user.getMobile(),user.getUtype()}; return qr.update(sql,params); } //3.登录验证用户名 @Override public User findUserForLogin(User user) throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select * from t_user where uname=? and pwd=? and utype=?"; Object [] params = {user.getUname(),user.getPwd(),user.getUtype()}; return qr.query(sql, new BeanHandler (User.class),params); }2. //1.查询所有 @Test //单元测试 public void demo1() throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select * from t_emp"; List list = qr.query(sql, new BeanListHandler (Emp.class)); for (Emp emp : list) { System.out.println(emp); } //BeanListHandler对象的集合 } //2.查询指定的条件（一个或多个条件） @Test public void demo2() throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); /*String sql = "select * from t_emp where id=?"; Emp emp = qr.query(sql, new BeanHandler (Emp.class),3L); System.out.println(emp);*/ String sql2 = "select * from t_emp where deptid=? and gender=?"; List list = qr.query(sql2, new BeanListHandler (Emp.class),1L,"男"); for (Emp emp2 : list) { System.out.println(emp2); } } //3.查询指定的列 @Test public void demo3() throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select ename from t_emp"; List enames = qr.query(sql, new ColumnListHandler ()); for (String ename : enames) { System.out.println(ename); } String sql2 = "select ename,salary from t_emp"; List list = qr.query(sql2, new BeanListHandler (Emp.class)); for (Emp emp : list) { System.out.println(emp); } System.out.println("----------------------------"); List enames = qr.query(sql2, new ColumnListHandler ("ename")); for (String ename : enames) { System.out.print(ename+"\t"); } System.out.println("-----------------------------"); List salarys = qr.query(sql2, new ColumnListHandler ("salary")); for (Double salary : salarys) { System.out.print(salary+"\t"); } } //4.组函数查询 @Test public void demo4() throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select count(*) from t_emp"; Long count = qr.query(sql, new ScalarHandler ()); System.out.println(count); String sql2 = "select max(salary),min(salary),avg(salary) as avg from t_emp"; Double max = qr.query(sql2, new ScalarHandler (1)); Double min = qr.query(sql2, new ScalarHandler (2)); Double avg = qr.query(sql2, new ScalarHandler ("avg")); 或 Double avg = qr.query(sql2, new ScalarHandler (3)); System.out.println(max); System.out.println(min); System.out.println(avg); } // 4.1查询商品总记录数 @Override public Long findCount(String keyword) throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select count(*) from t_product where info like ?"; return qr.query(sql, new ScalarHandler (), "%" + keyword + "%"); } //5.多表查询 @Test public void demo5() throws SQLException { QueryRunner qr = JDBCUtil.getQueryRunner(); String sql = "select e.*,d.dname from t_emp e,dept d where e.deptid=d.id"; List
转载地址：http://zwnxi.baihongyu.com/