创建项目和QueryRunner
创建项目:
数据库的设计
商品表: goods
列名 |
类型 |
说明 |
备注 |
id |
int |
id |
主键 |
name |
varchar |
名称 |
|
cover |
varchar |
封面地址 |
|
image1 |
varchar |
详情图片1 |
|
image2 |
varchar |
详情图片2 |
|
price |
floatprice |
价格 |
|
intro |
varchar |
简介 |
|
stock |
int |
库存 |
|
type_id |
int |
类目Id |
外键 |
商品必须属于某一个类目。
类目表: type
列名 |
类型 |
说明 |
备注 |
id |
int |
id |
主键 |
name |
varchar |
名称 |
|
商品推荐表: recommend
列名 |
类型 |
说明 |
备注 |
id |
int |
id |
主键 |
type |
tinyint |
推荐类型(1条幅/2热销/3新品) |
|
goods_id |
int |
产品id |
|
商品表与商品推荐表是一对多的关系:一个商品与对应着多种类型
创建c3p0配置文件:
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"> <![CDATA[jdbc:mysql://127.0.0.1:3306/cakeshop?useUnicode=true&characterEncoding=UTF-8&useSSL=true]]> </property> <property name="user">root</property> <property name="password">root</property> </default-config> </c3p0-config>
数据库goods的内容:
通过出c3p0查询出来的结果:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package learning_something; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class Test { public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 Connection con=ds.getConnection(); PreparedStatement ps=con.prepareStatement("select*from goods"); ResultSet rs=ps.executeQuery(); while(rs.next()) { System.out.println(rs.getString("name")); } rs.close(); ps.close(); con.close(); } }
使用QueryRunner做查询的初步
在数据库中id=3的商品信息:
使用QueryRunner做查询的效果:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package learning_something; import java.sql.SQLException; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.MapHandler; import com.mchange.v2.c3p0.ComboPooledDataSource; public class TestQueryRunner { public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); String sql="select* from goods where id=?"; Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 for(String key:map.keySet()) { System.out.println(key+":"+map.get(key)); } } }
使用QueryRunner做增加、删除和修改操作
使用QueryRunner实现数据库删除
在一个表中如果 有外键时得需要先删除相关联的外键
没有删除之前数据库的内容:
执行代码删除之后:
显示出的2,表示有2条记录受到了影响
删除了两条11和12
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package learning_something; 2 3 import java.sql.SQLException; 4 import java.util.Map; 5 6 import javax.sql.DataSource; 7 8 import org.apache.commons.dbutils.QueryRunner; 9 import org.apache.commons.dbutils.handlers.MapHandler; 10 11 import com.mchange.v2.c3p0.ComboPooledDataSource; 12 13 public class TestQueryRunner { 14 15 public static void main(String[] args) throws SQLException { 16 DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 17 18 19 QueryRunner r=new QueryRunner(ds); 20 // String sql="select* from goods where id=?"; 21 // Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 22 // for(String key:map.keySet()) { 23 // 24 // System.out.println(key+":"+map.get(key)); 25 // } 26 27 String sql="delete from type where id in(?,?)"; 28 int count=r.update(sql,11,12); 29 System.out.println(count); 30 31 32 33 } 34 35 }
使用QueryRunner实现数据库更新
我们想把该数据库中的‘节日系列’改为‘生日系列’,其效果如下:
显示出来的1表示有一行受到影响
数据库经过更新后的结果:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package learning_something; import java.sql.SQLException; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.MapHandler; import com.mchange.v2.c3p0.ComboPooledDataSource; public class TestQueryRunner { public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); // String sql="select* from goods where id=?"; // Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 // for(String key:map.keySet()) { // // System.out.println(key+":"+map.get(key)); // } // String sql="delete from type where id in(?,?)"; // int count=r.update(sql,11,12); // System.out.println(count); String sql="update type set name='生日系列' where id=?"; int count=r.update(sql,8); System.out.println(count); } }
使用QueryRunner实现数据库插入
在使用QueryRunner实现更新时我们把‘节日系列’改为‘生日系列’,那么我们就使用QueryRunner插入‘节日系列’,执行代码后,数据库的内容变为:
表示有一条内容插入数据库中;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package learning_something; 2 3 import java.sql.SQLException; 4 import java.util.Map; 5 6 import javax.sql.DataSource; 7 8 import org.apache.commons.dbutils.QueryRunner; 9 import org.apache.commons.dbutils.handlers.MapHandler; 10 11 import com.mchange.v2.c3p0.ComboPooledDataSource; 12 13 public class TestQueryRunner { 14 15 public static void main(String[] args) throws SQLException { 16 DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 17 18 19 QueryRunner r=new QueryRunner(ds); 20 // String sql="select* from goods where id=?"; 21 // Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 22 // for(String key:map.keySet()) { 23 // 24 // System.out.println(key+":"+map.get(key)); 25 // } 26 27 // String sql="delete from type where id in(?,?)"; 28 // int count=r.update(sql,11,12); 29 // System.out.println(count); 30 31 32 // String sql="update type set name='生日系列' where id=?"; 33 // int count=r.update(sql,8); 34 // System.out.println(count); 35 // 36 37 38 String sql="insert into type(name) values(?)"; 39 int count=r.update(sql,"节日系列"); 40 System.out.println(count); 41 42 43 44 } 45 46 }
ArrayHandler和ArrayListHandler处理结果集
goods数据表中的数据:
使用ArrayHandler只能查询到一组数据:
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package learning_something; 2 3 import java.sql.SQLException; 4 import java.util.Map; 5 6 import javax.sql.DataSource; 7 8 import org.apache.commons.dbutils.QueryRunner; 9 import org.apache.commons.dbutils.handlers.ArrayHandler; 10 import org.apache.commons.dbutils.handlers.MapHandler; 11 12 import com.mchange.v2.c3p0.ComboPooledDataSource; 13 14 public class TestQueryRunner { 15 16 public static void main(String[] args) throws SQLException { 17 DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 18 19 20 QueryRunner r=new QueryRunner(ds); 21 22 String sql="select * from goods"; 23 Object[] res=r.query(sql, new ArrayHandler()); 24 for(Object o:res) { 25 System.out.println(o); 26 } 27 28 29 } 30 31 }
使用ArrayListHandler可以查询到整个表中的数据:
以查询goods表为例,,其查询的结果如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package learning_something; import java.sql.SQLException; import java.util.List; import java.util.Map; import javax.sql.DataSource; 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.MapHandler; import com.mchange.v2.c3p0.ComboPooledDataSource; public class TestQueryRunner { public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); String sql="select * from goods"; List<Object[]> list=r.query(sql, new ArrayListHandler()); for(Object[] objs:list ) { for(Object o:objs) { System.out.println(o+""); } System.out.println(); } } }
BeanHandler得到一条记录:
BeanHandler: 把结果集转为一个 Bean, 并返回. Bean 的类型在创建 BeanHandler 对象时以 Class 对象的方式传入,可以进行自行组拼,要求写的属性要与数据库的属性一一对应,并且需要提供空的构造方法。
goods.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package learning_something; public class Goods { private int id; private String name; private String cover; private String image1; private String image2; private float price; private String intro; private int stock; private int type_id; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getCover() { return cover; } public void setCover(String cover) { this.cover = cover; } public String getImage1() { return image1; } public void setImage1(String image1) { this.image1 = image1; } public String getImage2() { return image2; } public void setImage2(String image2) { this.image2 = image2; } public float getPrice() { return price; } public void setPrice(float price) { this.price = price; } public String getIntro() { return intro; } public void setIntro(String intro) { this.intro = intro; } public int getStock() { return stock; } public void setStock(int stock) { this.stock = stock; } public int getType_id() { return type_id; } public void setType_id(int type_id) { this.type_id = type_id; } @Override public String toString() { return "Goods [id=" + id + ", name=" + name + ", cover=" + cover + ", image1=" + image1 + ", image2=" + image2 + ", price=" + price + ", intro=" + intro + ", stock=" + stock + ", type_id=" + type_id + "]";//进行组拼 } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package learning_something; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 import java.util.Map; 6 7 import javax.sql.DataSource; 8 9 import org.apache.commons.dbutils.QueryRunner; 10 import org.apache.commons.dbutils.handlers.ArrayHandler; 11 import org.apache.commons.dbutils.handlers.ArrayListHandler; 12 import org.apache.commons.dbutils.handlers.BeanHandler; 13 import org.apache.commons.dbutils.handlers.MapHandler; 14 15 import com.mchange.v2.c3p0.ComboPooledDataSource; 16 17 public class TestQueryRunner { 18 19 public static void main(String[] args) throws SQLException { 20 DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 21 22 23 QueryRunner r=new QueryRunner(ds); 24 // String sql="select* from goods where id=?"; 25 // Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 26 // for(String key:map.keySet()) { 27 // 28 // System.out.println(key+":"+map.get(key)); 29 // } 30 31 // String sql="delete from type where id in(?,?)"; 32 // int count=r.update(sql,11,12); 33 // System.out.println(count); 34 35 36 // String sql="update type set name='生日系列' where id=?"; 37 // int count=r.update(sql,8); 38 // System.out.println(count); 39 // 40 41 42 // String sql="insert into type(name) values(?)"; 43 // int count=r.update(sql,"节日系列"); 44 // System.out.println(count); 45 // 46 // String sql="select * from goods"; 47 // Object[] res=r.query(sql, new ArrayHandler()); 48 // for(Object o:res) { 49 // System.out.println(o); 50 // } 51 52 53 // 54 55 56 String sql="select * from goods"; 57 Goods goods=r.query(sql, new BeanHandler<Goods>(Goods.class)); 58 System.out.println(goods); 59 60 } 61 62 }
BeanListHandler、MapListHandler、ScalarHandler等其他Handler
BeanListHandler查询表中所有:
由于记录太多,换行输出。
关键代码:
public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); String sql="select * from goods"; List<Goods> list=r.query(sql, new BeanListHandler<Goods>(Goods.class)); for(Goods g:list) { System.out.println(g); } } }
使用ColumnListHandler进行查询
ColumnListHandler可以查询到表格到某一列所有的内容,例如查询goods表中的所有的商品的名字:
核心代码:
public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); String sql="select * from goods"; List<Object> list=r.query(sql, new ColumnListHandler<Object>("name")); for(Object g:list) { System.out.println(g); } }
ScalarHandler:只有一个结果集时使用的,可进行统计,例如对goods表中的数据进行统计:
统计结果:
核心代码:
public static void main(String[] args) throws SQLException { DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 QueryRunner r=new QueryRunner(ds); String sql="select count(*) from goods"; long count=r.query(sql, new ScalarHandler<Long>()); System.out.println(count); }
MapHandler只能得到结果集中的第一条记录:
MapListHandler
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package learning_something; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 import java.util.Map; 6 7 import javax.sql.DataSource; 8 9 import org.apache.commons.dbutils.QueryRunner; 10 import org.apache.commons.dbutils.handlers.ArrayHandler; 11 import org.apache.commons.dbutils.handlers.ArrayListHandler; 12 import org.apache.commons.dbutils.handlers.BeanHandler; 13 import org.apache.commons.dbutils.handlers.BeanListHandler; 14 import org.apache.commons.dbutils.handlers.ColumnListHandler; 15 import org.apache.commons.dbutils.handlers.MapHandler; 16 import org.apache.commons.dbutils.handlers.MapListHandler; 17 import org.apache.commons.dbutils.handlers.ScalarHandler; 18 19 import com.mchange.v2.c3p0.ComboPooledDataSource; 20 21 public class TestQueryRunner { 22 23 public static void main(String[] args) throws SQLException { 24 DataSource ds=new ComboPooledDataSource();//使用c3p0的数据源 25 26 27 QueryRunner r=new QueryRunner(ds); 28 // String sql="select* from goods where id=?"; 29 // Map<String,Object> map =r.query(sql, new MapHandler(),3);//查出id=3的商品信息 30 // for(String key:map.keySet()) { 31 // 32 // System.out.println(key+":"+map.get(key)); 33 // } 34 35 // String sql="delete from type where id in(?,?)"; 36 // int count=r.update(sql,11,12); 37 // System.out.println(count); 38 39 40 // String sql="update type set name='生日系列' where id=?"; 41 // int count=r.update(sql,8); 42 // System.out.println(count); 43 // 44 45 46 // String sql="insert into type(name) values(?)"; 47 // int count=r.update(sql,"节日系列"); 48 // System.out.println(count); 49 // 50 // String sql="select * from goods"; 51 // Object[] res=r.query(sql, new ArrayHandler()); 52 // for(Object o:res) { 53 // System.out.println(o); 54 // } 55 56 57 // 58 59 60 // String sql="select * from goods"; 61 // Goods goods=r.query(sql, new BeanHandler<Goods>(Goods.class)); 62 // System.out.println(goods); 63 64 65 66 // String sql="select * from goods"; 67 // List<Goods> list=r.query(sql, new BeanListHandler<Goods>(Goods.class)); 68 // for(Goods g:list) { 69 // System.out.println(g); 70 // } 71 // 72 73 // String sql="select * from goods"; 74 // List<Object> list=r.query(sql, new ColumnListHandler<Object>("name")); 75 // for(Object g:list) { 76 // System.out.println(g); 77 // } 78 // 79 80 81 82 // String sql="select count(*) from goods"; 83 // long count=r.query(sql, new ScalarHandler<Long>()); 84 // System.out.println(count); 85 86 87 // 88 // String sql="select* from goods "; 89 // Map<String,Object> map =r.query(sql, new MapHandler()); 90 // for(String key:map.keySet()) { 91 // 92 // System.out.println(key+":"+map.get(key)); 93 // } 94 95 String sql="select* from goods "; 96 List<Map<String,Object>> list =r.query(sql, new MapListHandler()); 97 for(Map<String,Object> map:list) 98 for(String key:map.keySet()) { 99 100 System.out.println(key+" "+map.get(key)+""); 101 } 102 System.out.println(); 103 104 105 106 107 } 108 109 }