DBUtils实现增、删、改、查

DBUtils实现增、删、改、查

插入

public void insert() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

String sql = "insert into

user(id,name,password,email,birthday) values(null,?,?,?,?)";

    Object[] param={"by","sss","by@wer.com","1980-03-04"};

    runner.update(sql,param);

}

删除

public void delete() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "delete fromuser where name=?";

    Object[] param ={"by"};

    runner.update(sql,param);

}

查询

public User find() throws SQLException{

QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user where id=?";

          

    User u = (User)runner.query(sql, newBeanHandler(User.class),1);

   

    return u;

}

public void getAll() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    List<User> list = (List<User>) runner.query(sql,

newBeanListHandler(User.class));

    System.out.println(list.size());

}

批处理

public void bath() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "insert intouser(id,name,password) values(?,?,?)";

    Object[][] params =

{{5,"Jake","aaa"},{6,"John","bbb"},{7,"Jansen","ccc"}};

    runner.batch(sql,params);

}

ResultSetHandler 接口的实现类

ArrayHandler

public void testArrayHandler() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    Object[] result = runner.query(sql, new ArrayHandler());

    System.out.println(Arrays.asList(result));

}

ArrayListHandler

public void testArrayListHandler() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    List<Object[]> result =

(List<Object[]>) runner.query(sql, newArrayListHandler());

    for(int i=0; i<result.size(); i++){

       System.out.println(Arrays.asList(result.get(i)));

    }

}

ColumnListHandler

public void testColumnListHandler() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    List<Object> result =

(List<Object>) runner.query(sql, new ColumnListHandler("name"));

    System.out.println(result);    

}

KeyedHandler

public void testKeyedHandler() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    Map<Integer,Map<String,Object>> map =

(Map) runner.query(sql, newKeyedHandler("id"));

    for(Map.Entry<Integer,Map<String,Object>> me :map.entrySet()){

       int id = me.getKey();

       Map<String,Object> innermap = me.getValue();

       for(Map.Entry<String, Object> innerme:innermap.entrySet()){

           String name = innerme.getKey();

           Object value = innerme.getValue();

           System.out.println(name + "=" + value);

             

       }

    }

}

MapListHandler

public void testMapListHandler() throws SQLException{

    QueryRunner runner =

newQueryRunner(DBManager_c3p0.getDataSource());

    String sql = "select *from user";

    List<Map<String,Object>> result =

(List<Map<String,Object>>)

runner.query(sql, new MapListHandler());

    for(int i=0; i<result.size(); i++){

       Map<String, Object> map = result.get(i);

      

       System.out.println("---------------------");

      

       for(Map.Entry<String, Object> me :map.entrySet() ){

           String colName = me.getKey();

            Object value = me.getValue();

          

           System.out.println(colName + " = " + value);

       }

   

    }

}