java mysql dbutils_Java 使用 Dbutils 工具类库 操作mysql

1 packageJDBCDemo;2

3 importjava.sql.SQLException;4 importjava.util.ArrayList;5 importjava.util.HashMap;6 importjava.util.List;7 importjava.util.Map;8

9 importjavax.xml.bind.annotation.W3CDomHandler;10

11 importorg.apache.commons.dbutils.DbUtils;12 importorg.apache.commons.dbutils.QueryRunner;13 importorg.apache.commons.dbutils.handlers.ArrayHandler;14 importorg.apache.commons.dbutils.handlers.ArrayListHandler;15 importorg.apache.commons.dbutils.handlers.BeanHandler;16 importorg.apache.commons.dbutils.handlers.BeanListHandler;17 importorg.apache.commons.dbutils.handlers.ColumnListHandler;18 importorg.apache.commons.dbutils.handlers.MapHandler;19 importorg.apache.commons.dbutils.handlers.MapListHandler;20 importorg.apache.commons.dbutils.handlers.ScalarHandler;21

22 importcom.mysql.jdbc.Connection;23

24 public classdbutilsdemo1 {25 private static Connection connection =JDBCUtils.getConnection();26 private static QueryRunner qRunner = newQueryRunner();27 public static voidmain(String[] args) {28 try{29 //insert();30 //delete();31 //ArraryHandlerdemo();32 //beanListHandlerDemo();33 //ColumnListHandlerDemo();34 //mapHandlerDemo();

35 mapListHandlerDemo();36 } catch(Exception e) {37 //TODO: handle exception

38 System.out.println(e);39 }40

41 }42 public static void insert() throwsSQLException {43 //insert delete update

44 String sql = "insert into zhangwu (name,money) values (?,?);";45 Object[] object = {"打麻将支出",100};46 int row =qRunner.update(connection,sql,object);47 DbUtils.closeQuietly(connection);48 if (row>0) {49 System.out.println("插入数据成功");50 }else{51 System.out.println("插入数据失败");52 }53

54 }55

56 public static void update() throwsSQLException {57 String sql = "update zhangwu set money=? where id=?;";58 Object[] object = {500,4};59 int row =qRunner.update(connection,sql,object);60 DbUtils.closeQuietly(connection);61 if (row>0) {62 System.out.println("修改数据成功");63 }else{64 System.out.println("修改数据失败");65 }66 }67

68 public static void delete() throwsSQLException {69 String sql = "delete from zhangwu where id=?;";70 Object[] object = {100};71 int row =qRunner.update(connection,sql,object);72 DbUtils.closeQuietly(connection);73 if (row>0) {74 System.out.println("删除数据成功");75 }else{76 System.out.println("删除数据失败");77 }78 }79

80 public static void ArraryHandlerdemo () throwsSQLException {81 //返回1条数据集82 //ArraryHandler

83 String sql = "select * from zhangwu;";84 Object[] res = qRunner.query(connection, sql, newArrayHandler());85 for(Object object : res) {86 System.out.print(object+"\t");87 }88

89 }90 public static void ArraryListHandlerDemo() throwsSQLException {91 //返回整个数据集

92 String sql = "select * from zhangwu;";93 List res = qRunner.query(connection, sql, newArrayListHandler());94 for(Object[] objs : res) {95 for(Object oo : objs) {96 System.out .print(oo + "\t");97 }98 System.out.println("");99 }100 }101 public static void beanHandler() throwsSQLException {102 //返回1条数据集

103 String sql = "select * from zhangwu;";104 Zhangwu zw = qRunner.query(connection,sql,new BeanHandler(Zhangwu.class));105 System.out.println(zw);106 }107 public static void beanListHandlerDemo() throwsSQLException{108 //返回整个数据集

109 String sql = "select * from zhangwu;";110 List zwlist= qRunner.query(connection, sql, new BeanListHandler(Zhangwu.class));111 for(Zhangwu zwchild : zwlist) {112 System.out.println(zwchild.toString());113 }114 }115 public static void ColumnListHandlerDemo() throwsSQLException {116 //单列结果查询

117 String sql = "select distinct name from zhangwu";118 List zwlist= qRunner.query(connection, sql, new ColumnListHandler("name"));119 for(Object zwchild : zwlist) {120 System.out.println(zwchild.toString());121 }122 }123 public static void scalarHandlerDemo() throwsSQLException {124 //单结果集查询

125 String sql = "select count(*) from zhangwu;";126 Long num = qRunner.query(connection, sql, new ScalarHandler());127 System.out.println(num);128 }129 public static void mapHandlerDemo() throwsSQLException {130 //结果集第一行数据 封装到map集合中

131 String sql = "select * from zhangwu;";132 Map< String,Object > map= qRunner.query(connection,sql,newMapHandler());133 for(String key : map.keySet()) {134 System.out.println(key + "..." +map.get(key));135 }136 }137 public static void mapListHandlerDemo() throwsSQLException {138 //封装到map集合中 适合少量数据

139 String sql = "select * from zhangwu;";140 List> listmap = qRunner.query(connection, sql, newMapListHandler());141 for(Mapmapchild : listmap) {142 for(String key : mapchild.keySet()) {143 System.out.print(key + " = " + mapchild.get(key) + " ");144 }145 System.out.println("");146 }147 }148 }