DBUtils完成增删改
编写工具类C3P0Utils02
首先：导入相关jar包--mysql-connector-java-5.1.37-bin.jar、commons-dbutils-1.6.jar、c3p0-0.9.1.2.jar、mchange-commons-java-0.2.11.jar
其次：导入配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config><!-- 默认配置 --> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day04</property> <property name="user">root</property> <property name="password">123</property> <property name="initialPoolSize">10</property> </c3p0-config>
编写工具类
public class C3P0Utils02 { private static ComboPooledDataSource ds = new ComboPooledDataSource(); public static DataSource getDataSource(){ return ds; } //static代码块设置数据库连接四大要素 public static Connection getConnection() throws SQLException{ //获取连接,不要自己去DriverManager获取,而是从C3P0连接池获取 return ds.getConnection(); } //关闭所有资源的统一代码 public static void closeAll(Connection conn,Statement st,ResultSet rs){ //负责关闭 if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(st != null){ try { st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(rs != null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
测试
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner; /** * DBUtils框架的介绍 * * DBUtils类:主要负责关闭连接,释放资源,开启事务等操作 * * QueryRunner:负责我们对象数据库的CURD操作(核心类) * * ResultSetHandler:结果集处理类,帮我我们处理结果集(帮助我们封装数据的) * * QueryRunner类的使用: * 构造: * public QueryRunner(DataSource ds);//需要一个连接池，它自动从连接池中拿连接 * public QueryRunner();//不需要连接,用来进行事务操作,明天说 * 方法: * int update(String sql, Object... params);//主要执行增删改 * query(String sql, ResultSetHandler<T> rsh, Object... params);//主要执行查询 * * * @author yingpeng * */ public class DBUtilsDemo { public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub // insert(); // delete(); update(); } //增 public static void insert() throws SQLException{ QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //插入，可变参数params=obj进行赋值 Object[] obj = {16,"皮鞋类"}; int rows = qr.update("insert into category (cid,cname) values (?,?)", obj); System.out.println(rows); // System.out.println( new QueryRunner(C3P0Utils02.getDataSource()). // update("insert into category (cid,cname) values (?,?)", // new Object[]{17,"成人用品类"})); } //删 public static void delete() throws SQLException{ //1.创建QueryRunner对象,给定连接池 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行 int rows = qr.update("delete from category where cid = ?", new Object[]{17}); System.out.println(rows); } //修改 public static void update() throws SQLException{ //1.创建QueryRunner对象,给定连接池 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行 int rows = qr.update("update category set cname = ? where cid = ?", new Object[]{"运动鞋类",16}); System.out.println(rows); } //查询 public static void query(){ } }
专门用来进行查询操作
1 编写Javabean用来存储参数
public class Category { private String cid; private String cname; public String getCid() { return cid; } public void setCid(String cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } @Override public String toString() { return "Category [cid=" + cid + ", cname=" + cname + "]"; } public Category() { super(); // TODO Auto-generated constructor stub } public Category(String cid, String cname) { super(); this.cid = cid; this.cname = cname; } }
DBUtils进行查询操作
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.itheima.domain.Category; /** * 专门用来执行数据库的查询操作 * 1.QueryRunner(DataSource) * 2.query(String sql, ResultSetHandler<T> rsh, Object... params);//主要执行查询 * * ResultSetHandler:结果集处理类 * 我们发现ResultSetHandler是一个接口, * 而框架中给我们定义了一堆实现类 * ArrayHandler: * ArrayListHandler * **BeanHandler * **BeanListHandler * ColumnListHandler * KeyedHandler * MapHandler * MapListHandler * **ScalarHandler * * * * @author yingpeng * */ public class DBUtilsDemo02 { public static void main(String[] args) throws SQLException { demo08(); } //ArrayHandler处理类的使用 public static void demo01() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; Object[] objs = qr.query(sql, new ArrayHandler()); //3.打印 for (Object obj : objs) { System.out.println(obj); } } //ArrayListHandler处理类的使用 public static void demo02() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; List<Object[]> list = qr.query(sql, new ArrayListHandler()); //3.打印 for (Object[] objects : list) { System.out.println(objects[0]+"\t"+objects[1]); } } //BeanHandler处理类的使用 public static void demo03() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; Category c = qr.query(sql, new BeanHandler<Category>(Category.class)); //3 System.out.println(c); } //BeanListHandler处理类的使用 public static void demo04() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; List<Category> list = qr.query(sql, new BeanListHandler<Category>(Category.class)); //3 for (Category category : list) { System.out.println(category); } } //ColumnListHandler处理类的使用 public static void demo05() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; List<Object> ids = qr.query(sql, new ColumnListHandler<Object>("cname")); //3 System.out.println(ids); } //MapHandler处理类的使用 public static void demo06() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; Map<String, Object> map = qr.query(sql,new MapHandler()); //3 System.out.println(map); } //MapListHandler处理类的使用 public static void demo07() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select * from category"; List<Map<String, Object>> maps = qr.query(sql,new MapListHandler()); //3 System.out.println(maps); } //ScalarHandler处理类的使用 public static void demo08() throws SQLException{ //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource()); //2.执行查询 String sql = "select count(*) from category"; Object count = qr.query(sql,new ScalarHandler<Object>()); //3 System.out.println(count); } }
