Java之DBUtils(实例)

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;
	}

}