Apache的DBUtils

9404803

package com.test.dbutil;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class UserDaoImpl implements UserDao  {
public DataSource getDataSource(){
String url = "jdbc:mysql://127.0.0.1:3306/testdbutils";
MysqlDataSource ds = new MysqlDataSource();
ds.setServerName("127.0.0.1");
//ds.setDatabaseName("testdbutils");
ds.setURL(url);
ds.setUser("root");
ds.setPassword("123456");
ds.setCharacterEncoding("utf8");
return ds;
}
public BeanListHandler<User> getBeanListHandler(){
return new BeanListHandler<User>(User.class);
}
public BeanHandler<User> getBeanHandler(){
return new BeanHandler<User>(User.class);
}
public void delete(int id) {
QueryRunner runner = new QueryRunner(getDataSource());
try {
int affectedRows = runner.update("delete from user where id = ?",id);
System.out.println("删除成功，影响的行数："+affectedRows);
} catch (SQLException e) {
System.out.println("删除id为"+id+"的记录失败。错误为："+e.getMessage());
}
}
public void delete(User user) {
delete(user.getId());
}
public List<User> getAllUsers() {
QueryRunner runner = new QueryRunner(getDataSource());
try {
return runner.query("select * from user", getBeanListHandler());
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public User getById(int id) {
QueryRunner runner = new QueryRunner(getDataSource());
User user = null;
try {
user = runner.query("select * from user where id = ?", getBeanHandler(),id);
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public void save(User user) {
QueryRunner runner = new QueryRunner(getDataSource());
try {
runner.update("insert into user values(?,?,?,?,?)",new Object[]{
user.getId(),
user.getName(),
user.getAge(),
user.getSex(),
user.getBirth()
});
} catch (SQLException e) {
System.out.println("插入失败，失败原因:"+e.getMessage());
}
}
public long getCount() {
QueryRunner runner = new QueryRunner(getDataSource());
try {

Long count = runner.query("select count(*) as count from user", new ResultSetHandler<Long>(){
public Long handle(ResultSet rs) throws SQLException {
if(rs.next()){
return rs.getLong(1); //或者rs.getLong("count");
}
return 0L;
}
});
//或者用ScalarHandler, 这里的new ScalarHandler() 可以加上“count”参数
//Long count = (Long)runner.query("select count(*) as count from user",new  ScalarHandler(){
//
//@Override
//public Object handle(ResultSet rs) throws SQLException {
//return super.handle(rs);
//}
//
//});
return (Long)count;
} catch (SQLException e) {
e.printStackTrace();
return 0L;
}
}

}

　　如果什么框架都不想用，那就最基础的jdbc吧，简单封装下。