为了快捷开发，避免代码重复量，使用JDBC工具类
需要两个jar包，一个文件
package com.west.jdbcutil;import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;public class JDBCUtilsConfig {
private static Connection con;private static String driverClass;private static String url;private static String username;private static String password;static {try {readConfig();Class.forName(driverClass);con=DriverManager.getConnection(url, username, password);} catch (Exception e) {throw new RuntimeException("数据库连接失败");}}private static void readConfig() throws IOException {InputStream in=JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("base.properties");Properties pro = new Properties();pro.load(in);driverClass=pro.getProperty("driverClass");url=pro.getProperty("url");username=pro.getProperty("username");password=pro.getProperty("password");}public static Connection getConnection() {return con;}}
package com.west.demo;import java.sql.Connection;
import java.sql.SQLException;import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;import com.west.jdbcutil.JDBCUtilsConfig;/** 使用QueryRunner类，实现对数据表的操作* insert delete update* 调用QueryRunner类的方法update(Connection con,String sql,Object param)* Object param 可变参数，Object类型，SQL语句会出现？占位符* 数据库连接对象，自定义工具类*/
public class QueryRunnerDemo {
private static Connection con=JDBCUtilsConfig.getConnection();public static void main(String[] args) throws Exception {//delete();// update();insert();}/** 定义方法，使用QueryRunner类的方法delete将表数据删除* */public static void delete() throws Exception {//创建QueryRunner类对象QueryRunner qr=new QueryRunner();String sql="DELETE FROM sort WHERE sid=?";//调用QueryRunner方法updateint row=qr.update(con, sql,2);System.out.println(row);/** 判断insert,update,delete执行是否成功* 对返回值row判断* if(row>0) 执行成功*/DbUtils.closeQuietly(con);}/** 定义方法，使用QueryRunner类的方法update将表数据修改* */public static void update() throws Exception {QueryRunner qr=new QueryRunner();String sql="UPDATE sort SET sname=?,sprice=?,sdesc=? WHERE sid=?";Object[] params= {
"花卉",100.88,"情人节玫瑰花",4};int row=qr.update(con, sql, params);System.out.println(row);DbUtils.closeQuietly(con);}/** 定义方法，使用QueryRunner类的方法update向表中添加数据**/public static void insert() throws SQLException {QueryRunner qr=new QueryRunner();String sql="INSERT INTO sort (sid,sname,sprice,sdesc) VALUES (?,?,?,?)";Object[] params= {
8,"鼠标",123,"玩游戏"};int row = qr.update(con, sql, params);System.out.println(row);DbUtils.closeQuietly(con);}}
QueryRunner实现数据库查询操作：
package com.west.demo;public class Sort {
private int sid;private String sname;private double sprice;private String sdesc;public Sort(int sid, String sname, double sprice, String sdesc) {this.sid = sid;this.sname = sname;this.sprice = sprice;this.sdesc = sdesc;}public Sort(){}public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public double getSprice() {return sprice;}public void setSprice(double sprice) {this.sprice = sprice;}public String getSdesc() {return sdesc;}public void setSdesc(String sdesc) {this.sdesc = sdesc;}@Overridepublic String toString() {return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";}
}
package com.west.demo;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;import com.west.jdbcutil.JDBCUtilsConfig;/** QueryRunner数据库查询操作* 调用QueryRunner类方法query(Connection con,String sql,ResultSetHandler r,Object..params)* ResultSetHandle r结果集的处理方式，传递ResultSetHandler接口实现类* Object...params SQl语句中的占位符* 注意：query 方法返回值，返回的是T泛型*/
public class QueryRunnerDemo1 {
private static Connection con =JDBCUtilsConfig.getConnection();public static void main(String[] args) throws SQLException {//arrayHandle();
// arrayListHandler();//beanHandler();//beanListHander();
// columnListHandler();
// scalarHandler();mapListHandler();}/** 结果集第八种处理方法,MapListHandler* 将结果集每一行存储到Map集合,键:列名,值:数据* Map集合过多,存储到List集合*/public static void mapListHandler()throws SQLException{QueryRunner qr = new QueryRunner();String sql = "SELECT * FROM sort";//调用方法query,传递结果集实现类MapListHandler//返回值List集合, 存储的是Map集合List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler());//遍历集合listfor( Map<String,Object> map : list ){for(String key : map.keySet()){System.out.print(key+"..."+map.get(key));}System.out.println();}}/** 结果集第七种处理方法,MapHandler* 将结果集第一行数据,封装到Map集合中* Map<键,值> 键:列名 值:这列的数据*/public static void mapHandler()throws SQLException{QueryRunner qr = new QueryRunner();String sql = "SELECT * FROM sort";//调用方法query,传递结果集实现类MapHandler//返回值: Map集合,Map接口实现类, 泛型Map<String,Object> map = qr.query(con, sql, new MapHandler());//遍历Map集合for(String key : map.keySet()){System.out.println(key+".."+map.get(key));}}/** 结果集第六种处理方法,ScalarHandler* 对于查询后,只有1个结果*/public static void scalarHandler() throws SQLException {QueryRunner qr=new QueryRunner();String sql="select count(*) from sort";long count =qr.query(con,sql,new ScalarHandler<Long>());System.out.println(count);}/** 结果集第五种处理方法,ColumnListHandler* 结果集,指定列的数据,存储到List集合* List<Object> 每个列数据类型不同*/public static void columnListHandler()throws SQLException{QueryRunner qr = new QueryRunner();String sql = "SELECT * FROM sort "; //调用方法 query,传递结果集实现类ColumnListHandler//实现类构造方法中,使用字符串的列名List<Object> list = qr.query(con, sql, new ColumnListHandler<Object>("sprice"));for(Object obj : list){System.out.println(obj);}}/** 结果集第四种处理方法, BeanListHandler* 结果集每一行数据,封装JavaBean对象* 多个JavaBean对象,存储到List集合*/public static void beanListHander()throws SQLException{QueryRunner qr = new QueryRunner();String sql = "SELECT * FROM sort ";//调用方法query,传递结果集处理实现类BeanListHandlerList<Sort> list = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));for(Sort s : list){System.out.println(s);}}/** 结果集第三种处理方法，BeanHandler* 将结果集的第一行数据，封装成javaBean对象* 注意: 被封装成数据到JavaBean对象, Sort类必须有空参数构造* */public static void beanHandler()throws SQLException{QueryRunner qr = new QueryRunner();String sql = "SELECT * FROM sort ";//调用方法,传递结果集实现类BeanHandler//BeanHandler(Class<T> type)Sort s=qr.query(con, sql,new BeanHandler<Sort>(Sort.class));System.out.println(s);}/** 结果集第二种处理方法,ArrayListHandler* 将结果集的每一行,封装到对象数组中, 出现很多对象数组* 对象数组存储到List集合*/public static void arrayListHandler() throws SQLException {QueryRunner qr=new QueryRunner();String sql = "SELECT * FROM sort";List<Object[]> result=qr.query(con, sql,new ArrayListHandler());//集合的遍历for(Object[] obj:result) {//遍历对象数组for(Object objs : obj){System.out.print(objs+" ");}System.out.println();}}/** 结果集的第一种处理方式，ArrayHandler* 将结果集的第一行存储到对象数组中 Object[]* */public static void arrayHandle() throws SQLException {QueryRunner qr=new QueryRunner();String sql="SELECT * FROM sort";Object[] result=qr.query(con,sql,new ArrayHandler());for(Object objs:result) {System.out.println(objs);}}}