图书商城mysql数据库设计_JavaWeb网上图书商城完整项目-数据库操作工具类

packagecom.weiyuan.test;importjava.sql.SQLException;importjava.util.List;importjava.util.Map;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.apache.commons.dbutils.handlers.MapHandler;importorg.apache.commons.dbutils.handlers.MapListHandler;importorg.apache.commons.dbutils.handlers.ScalarHandler;importorg.junit.Test;importorg.junit.runner.RunWith;importcn.itcast.jdbc.JdbcUtils;importcn.itcast.jdbc.TxQueryRunner;/*** 对数据库操作类的测试

**/

public classTxRunnerTest {

@Testpublic void testUpdate() throwsSQLException{

QueryRunner run= newTxQueryRunner();

String sql= "insert into person(pid,pname,age,sex)values(?,?,?,?)";

Object[] params= {"2","张三",20,"男"};

run.update(sql,params);

System.out.println("ok");

}/** 测试批处理的方法

**/@Testpublic void testUpdate2() throwsSQLException{try{//开启事物

JdbcUtils.beginTransaction();

QueryRunner run= newTxQueryRunner();

String sql= "insert into person(pid,pname,age,sex)values(?,?,?,?)";

Object[] params= {"2","张三",20,"男"};

run.update(sql,params);

Object[] params2= {"20","张三",20,"男"};

run.update(sql,params2);//提交事物

JdbcUtils.commitTransaction();

}catch(Exception e){//如果存在异常回滚事物

JdbcUtils.rollbackTransaction();

}

}/**测试查询方法

*@throwsSQLException

* 注意事项：1 person类中的字段必须和数据库中表的字段名称一模一样

* 2 person存在public Person(){

}的构造函数

**/@Testpublic void testQuery() throwsSQLException{

QueryRunner run= newTxQueryRunner();

String sql= "select * from person where pid=?";

Person p= run.query(sql, new BeanHandler(Person.class),"20");

System.out.println(p);

}

@Testpublic void testQuery2() throwsSQLException{

QueryRunner run= newTxQueryRunner();

String sql= "select * from person";

List lists = run.query(sql, new BeanListHandler(Person.class));

System.out.println(lists);

}

@Testpublic void testQuery3() throwsSQLException{/** {sex=男, age=20, pname=张三, pid=20}

**/QueryRunner run= newTxQueryRunner();

String sql= "select * from person where pid=?";

Map map=run.query(sql,new MapHandler(),"20");

System.out.println(map);

}

@Testpublic void testQuery4() throwsSQLException{/** [{sex=男, age=1, pname=l;l;sl;, pid=1}, {sex=男, age=20, pname=张三, pid=2},

* {sex=男, age=20, pname=张三, pid=2}, {sex=男, age=20, pname=张三, pid=2},

* {sex=男, age=20, pname=张三, pid=2}, {sex=男, age=20, pname=张三, pid=2},

* {sex=男, age=20, pname=张三, pid=20}, {sex=男, age=20, pname=张三, pid=2}, {sex=男, age=20, pname=张三, pid=20},

* {sex=男, age=20, pname=张三, pid=2}]

**/QueryRunner run= newTxQueryRunner();

String sql= "select * from person";

List> maps=run.query(sql,newMapListHandler());

System.out.println(maps);

}

@Testpublic void testQuery5() throwsSQLException{/** 统计表的总共有多少条记录

* ScalarHandler将单行单列的结果封装成Object对象

* 直接如果直接将obj转成Int对象不同的驱动可能存在不一样的问题，

* 所以先转换成Number对象，在转化成int类型

**/QueryRunner run= newTxQueryRunner();

String sql= "select count(*) from person";

Object obj= run.query(sql, newScalarHandler());

Number number=(Number) obj;int count =number.intValue();

System.out.println(count);

}

}