尚硅谷之JDBC

/*

 * BeanHandler: 把结果集的第一条记录转为创建 BeanHandler 对象时传入的 Class参数对应的对象.

 * 当JavaBean的属性名与字段名不一致时，可以通过指定别名告知属性名

 */

public static void main(String[] args) throws SQLException {

//1、连接池

DataSource ds = new ComboPooledDataSource("mypool");

//2、直接使用QueryRunner

QueryRunner qr = new QueryRunner(ds);

String sql = "select pid as id,pname,price,description from t_goods where pid =?";

Goods goods = qr.query(sql, new BeanHandler<Goods>(Goods.class), 1);

System.out.println(goods);

}

/*

 * BeanListHandler: 把结果集转为一个 List, 该 List 不为 null, 但可能为空集合(size() 方法返回 0) 若

 * SQL 语句的确能够查询到记录, List 中存放创建 BeanListHandler 传入的 Class对象对应的对象.

 */

public static void main(String[] args) throws SQLException {

//1、连接池

DataSource ds = new ComboPooledDataSource("mypool");

//2、直接使用QueryRunner

QueryRunner qr = new QueryRunner(ds);

String sql = "select pid as id,pname,price,description from t_goods";

List<Goods> query = qr.query(sql, new BeanListHandler<Goods>(Goods.class));

for (Goods goods : query) {

System.out.println(goods);

}

}

public static void main(String[] args) throws SQLException {

// 1、连接池

DataSource ds = new ComboPooledDataSource("mypool");

// 2、直接使用QueryRunner

QueryRunner qr = new QueryRunner(ds);

String sql = "select did,count(*) from employee where did = 1";

Map<String, Object> map = qr.query(sql, new MapHandler());

Set<Entry<String, Object>> entrySet = map.entrySet();

for (Entry<String, Object> entry : entrySet) {

// System.out.println(entry.getKey() +"-->" + entry.getValue());

if ("did".equals(entry.getKey())) {

System.out.println("部门编号：" + entry.getValue());

} else {

System.out.println("人数：" + entry.getValue());

}

}

}

public static void main(String[] args) throws SQLException {

//1、连接池

DataSource ds = new ComboPooledDataSource("mypool");

//2、直接使用QueryRunner

QueryRunner qr = new QueryRunner(ds);

String sql = "select did,count(*) from employee group by did";

/*

 * did count(*)

 *  1   7

 *  2   3

 *  3   1

 *  

 *  List:

 *  map:

 *    key(did) value(1)

 *    key(count(*) value(7)

 * 第二行

 *  map:

 *    key(did) value(2)

 *    key(count(*) value(3)

 * 第三行

 * map:

 *    key(did) value(3)

 *    key(count(*) value(1)

 *

 */

List<Map<String, Object>> query = qr.query(sql, new MapListHandler());

for (Map<String, Object> map : query) {

Set<Entry<String, Object>> entrySet = map.entrySet();

for (Entry<String, Object> entry : entrySet) {

//System.out.println(entry.getKey() +"-->" + entry.getValue());

if("did".equals(entry.getKey())){

System.out.println("部门编号：" + entry.getValue());

}else{

System.out.println("人数：" + entry.getValue());

}

}

}

}

/*

 * ScalarHandler: 把结果集转为一个数值(可以是任意基本数据类型和字符串， Date 等)返回

 * ScalarHandler（）只取第一行第一列

 * ScalarHandler(int columnIndex)：取第一行的第columnIndex列

 * ScalarHandler(String columnName)：取第一行的列名为columnName列的值

 */

@Test

public static void main(String[] args) throws SQLException {

//1、连接池

DataSource ds = new ComboPooledDataSource("mypool");

//2、直接使用QueryRunner

QueryRunner qr = new QueryRunner(ds);

// String sql = "select count(*) from t_goods";

String sql = "select max(price) from t_goods";

Object query = qr.query(sql, new ScalarHandler());

System.out.println(query);

}