DBUtils
-
apache提供的开源类库:工具类 对jdbc简单的封装
-
commons-dbutils-1.7.jar 核心jar包
-
执行对象:QueryRunner
-
ResultSetHandler:处理程序 接口
-
需要将查询某条记录封装成对象 :BeanHandler<?>
-
需要将查询的多条记录(查询所有)封装List集合对象 :BeanListHandler<?>
-
查询的单行单列的数据:查询总记录数ScalarHandler<>
上代码
需求 数据库有一张user表 使用Druid连接数据库 使用DBUtils对user表进行增删改查 查询结果封装到User实体内
Druid封装成的工具类
public class DriudUtils {
private static DataSource ds;
private DriudUtils() {}
static {
InputStream in = DriudUtils.class.getClassLoader().getResourceAsStream("druid.properties");
Properties prop = new Properties();
try {
prop.load(in);
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return ds;
}
public static Connection getConnection() {
Connection conn;
try {
conn = ds.getConnection();
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Connection conn,PreparedStatement ps) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection conn,PreparedStatement ps) {
close(null, conn, ps);
}
}
User实体
public class User {
private int id;
private String username;
private String password;
private String email;
private Date brithday;
private String address;
public User(int id,String username, String password, String email, Date brithday, String address) {
super();
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.brithday = brithday;
this.address = address;
}
public User() {
super();
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBrithday() {
return brithday;
}
public void setBrithday(Date brithday) {
this.brithday = brithday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
+ ", brithday=" + brithday + ", address=" + address + "]";
}
}
UserDao
public interface UserDao {
//添加
public void insertUser(User u);
//根据id删除
public void deleteUserById(int id);
//根据id查找
public User selectUserById(int id);
//查找所有
public List<User> selectAllUsers();
//查找总记录
public long selectCount();
}
UserDaoImpl
public class UserDaoImpl implements UserDao{
private static QueryRunner qr = new QueryRunner(DriudUtils.getDataSource());
@Override
public void insertUser(User u) {
String sql = "insert into user (username,password,email,birthday,address)value(?,?,?,?,?)";
try {
int count = qr.update(
sql,
u.getUsername(),
u.getPassword(),
u.getEmail(),
u.getBrithday(),
u.getAddress());
if(count>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void deleteUserById(int id) {
String sql = "delete from user where id = ?";
try {
int count = qr.update(sql, id);
if(count>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public User selectUserById(int id) {
String sql = "select * from user where id = ?";
try {
User u = qr.query(sql, new BeanHandler<User>(User.class), id);
return u;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<User> selectAllUsers() {
String sql = "select * from user;";
try {
List<User> lists = qr.query(sql, new BeanListHandler<User>(User.class));
return lists;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public long selectCount() {
String sql = "select count(*) from user;";
try {
long count = qr.query(sql, new ScalarHandler<Long>());
return count;
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
}