dbUtil的简单使用

1. 配置maven依赖：

<dependency>  
   <groupId>commons-dbutils</groupId>  
   <artifactId>commons-dbutils</artifactId>  
   <version>1.5</version>  
</dependency>

2. 获取数据库连接：

public static QueryRunner getQueryRunner(){
        if(DBUtil.dataSource==null){
            BasicDataSource dbcpDataSource = new BasicDataSource();
            dbcpDataSource.setUrl("jdbc:mysql://localhost:3306/vcooline_test?useUnicode=true&characterEncoding=UTF-8");
            dbcpDataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dbcpDataSource.setUsername("root");
            dbcpDataSource.setPassword("1234");
            dbcpDataSource.setDefaultAutoCommit(true);
            dbcpDataSource.setMaxActive(100);
            dbcpDataSource.setMaxIdle(30);
            dbcpDataSource.setMaxWait(500);
            DBUtil.dataSource = (DataSource)dbcpDataSource;
        }
        return new QueryRunner(DBUtil.dataSource);
    }

3. 相关的增删改查：

public static int insert(String sql){
QueryRunner queryRunner = DBUtil.getQueryRunner();
int _ret = 0;
try {
_ret = queryRunner.update(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return _ret;
};

public static int update(String name,String address,Integer age,String id){
String sql = "UPDATE dbuser_test SET name=?,address=?,age=?,updated_at=NOW() WHERE id=?";
QueryRunner queryRunner = DBUtil.getQueryRunner();
int _ret = 0;
try {
_ret = queryRunner.update(sql, name, address, age, id);
} catch (SQLException e) {
e.printStackTrace();
}
return _ret;
};

public static DbUserTest query(String id){
String sql = "SELECT * FROM dbuser_test WHERE id=?";
QueryRunner queryRunner = DBUtil.getQueryRunner();
DbUserTest dbUserTest = null;
try {
dbUserTest = queryRunner.query(sql, new BeanHandler<DbUserTest>(DbUserTest.class), id);
} catch (SQLException e) {
e.printStackTrace();
}
return dbUserTest;
};

public static int[] insertParams(String sql, Object[][] params){
QueryRunner queryRunner = DBUtil.getQueryRunner();
int[] _ret = {};
try {
_ret = queryRunner.batch(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return _ret;
};


public static void main(String[] args) {
// int _ret = insert("INSERT INTO dbuser_test VALUES('9','liu8','上海',28,NOW(),NOW(),0)");

// Object[][] params = new Object[][]{{"12","liu12","上海",30},{"13","liu13","上海13",32}};
// int[] _ret = insertParams("INSERT INTO dbuser_test VALUES(?,?,?,?,NOW(),NOW(),0)", params);
// for (int i = 0; i < _ret.length; i++) {
// System.out.println(i + ":" + _ret[i]);
// }

// int _ret = update("aa", "aa", 33, "1");
// System.out.println(_ret);

DbUserTest dbUserTest = query("2");
System.out.println(dbUserTest.getName());
}

4. 关联实体:

public class DbUserTest implements Serializable {
private static final long serialVersionUID = 306532429926930513L;
private String id; // id
private String name; // 名字
private String address; // 地址
private Integer age; // 年龄
private Date created_at; // 创建时间
private Date updated_at; // 修改时间
private Integer status; // 状态

       //setter,getter方法

}