#jdbc筆記
- 複習抽取JdbcUtil工具
- 抽取JdbcTemplate模板 ##基本DAO的curd 下面以Employee的增刪改查為例: #####定義DAO的介面
public interface IEmployeeDao {
void save(Employee emp);
void delete(Employee emp);
void update(Employee emp);
Employee find(Employee emp);
List<Employee> findAll();
}
複製程式碼#####DAO的實現 導包:mysql-connector-java-5.1.26-bin.jar
public class EmployeeDaoImpl implements IEmployeeDao{
//員工的新增操作
public void save(Employee emp){
Connection conn=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
ps = conn.prepareStatement("insert into employee values(null,?,?,?)");
ps.setString(1, emp.getName());
ps.setInt(2, emp.getAge());
ps.setBigDecimal(3, emp.getSalary());
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
//員工的刪除操作
public void delete(Employee emp){
Connection conn=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
ps = conn.prepareStatement("delete from employee where id=?");
ps.setLong(1, emp.getId());
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
//員工的修該操作
public void update(Employee emp){
Connection conn=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
ps = conn.prepareStatement("update employee set name=? ,age=? ,salary=? where id=? ");
ps.setString(1, emp.getName());
ps.setInt(2, emp.getAge());
ps.setBigDecimal(3, emp.getSalary());
ps.setLong(4, emp.getId());
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
//查詢某一個員工
public Employee find(Employee emp){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
ps = conn.prepareStatement("select * from employee where id=? ");
ps.setLong(1,emp.getId());
rs = ps.executeQuery();
if(rs.next()){
emp.setName(rs.getString("name"));
emp.setAge(rs.getInt("age"));
emp.setSalary(rs.getBigDecimal("salary"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return emp;
}
//查詢所有員工
@Override
public List<Employee> findAll() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<Employee> list=new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
ps = conn.prepareStatement("select * from employee");
rs = ps.executeQuery();
if(rs.next()){
Employee emp=new Employee();
emp.setName(rs.getString("name"));
emp.setAge(rs.getInt("age"));
emp.setSalary(rs.getBigDecimal("salary"));
list.add(emp);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return list;
}
}
複製程式碼這樣的操作程式碼重複量很高,下面先進行一些基本的抽取
-
首先將
driverClassName,username,password,url抽取出來,放入到db.properties檔案中. -
db.properties
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdemo -
然後然後新建一個JdbcUtil的類,在靜態程式碼塊中載入db.properties檔案,
-
同時建立一個靜態方法
Connection getConnection(),用來返回Connection物件. -
然後在JdbcUtil類中提供一個關閉
conn,ps,rs的方法.public class JdbcUtil {
private static Properties prop = new Properties(); static { InputStream in = Thread.currentThread().getContextClassLoader() .getResourceAsStream("product.properties"); try { prop.load(in); } catch (IOException e1) { e1.printStackTrace(); } try { Class.forName(prop.getProperty("driverClassName")); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() { try { return DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("username"), prop.getProperty("password")); } catch (SQLException e) { e.printStackTrace(); } return null; } public static void close(Connection conn, ResultSet rs, PreparedStatement ps) { if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } } } } 複製程式碼}
這樣,就可以稍微簡化一下DAO的操作了
//員工的新增操作
public void save(Employee emp){
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("insert into employee values(null,?,?,?)");
ps.setString(1, emp.getName());
ps.setInt(2, emp.getAge());
ps.setBigDecimal(3, emp.getSalary());
ps.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//沒有結果集,就用null
JdbcUtil.close(conn,ps,null);
}
}
複製程式碼####接下來就是進行DAO的程式碼重構了
-
dao的增刪改操作基本上類似,可以取出相同的,不同的則由使用者自己新增.
-
查詢的操作也基本上類似,也可以進行重構.
public class MyJdbcTemplate {
// 操作,增刪改,傳入sql語句和引數,引數和sql之間要一一對應 public static boolean execute(String sql, Object... obj){ Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; conn = JdbcUtil.getConnection(); try{ ps = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { ps.setObject(i + 1, obj[i]); } if (ps.execute()) { JdbcUtil.close(conn, rs, ps); return true; } }catch(Exception e){ e.printStackTrace(); } JdbcUtil.close(conn, rs, ps); return false; } // 查詢返回的是一個list集合,也可以用來查詢單個,取list.get(0); public static <T> List<T> query(String sql,Class<T> clz, Object... params){ Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; //用來儲存物件 List<T> list =new ArrayList<>(); conn = JdbcUtil.getConnection(); try{ ps = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } rs = ps.executeQuery(); //內省機制，獲取javabean中的屬性方法 BeanInfo beanInfo=Introspector.getBeanInfo(clz,Object.class); PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors(); while(rs.next()){ //例項化一個物件，用來儲存獲取到的值， T obj =clz.newInstance(); for (PropertyDescriptor property : descriptors) { //獲取屬性set方法 Method writeMethod = property.getWriteMethod(); //獲取屬性名稱 String name=property.getName(); writeMethod.invoke(obj, rs.getObject(name)); } list.add(obj); } }catch(Exception e){ e.printStackTrace(); } JdbcUtil.close(conn, rs, ps); return list; } 複製程式碼}
得到MyJdbcTemplate之後,curd的操作就變得十分簡潔了.
public class EmployeeDaoImpl2 implements IEmployeeDao{
public void save(Employee emp){
String sql="insert into employee values(null,?,?,?)";
try {
MyJdbcTemplate.execute(sql, new Object[]{emp.getName(),emp.getAge(),emp.getSalary()});
} catch (Exception e1) {
e1.printStackTrace();
}
}
public void delete(Employee emp){
String sql="delete from employee where id=?";
try {
MyJdbcTemplate.execute(sql, new Object[]{emp.getId()});
} catch (Exception e1) {
e1.printStackTrace();
}
}
public void update(Employee emp){
String sql="update employee set name=? ,age=? ,salary=? where id=? ";
try {
MyJdbcTemplate.execute(sql, new Object[]{emp.getName(),emp.getAge(),emp.getSalary(),emp.getId()});
} catch (Exception e1) {
e1.printStackTrace();
}
}
public Employee find(Employee emp){
String sql="select * from employee where id=?";
try {
return MyJdbcTemplate.query(sql, emp.getClass(), new Object[]{emp.getId()}).get(0);
} catch (Exception e1) {
e1.printStackTrace();
}
return null;
}
@Override
public List<Employee> findAll() {
String sql="select * from employee";
try {
return MyJdbcTemplate.query(sql, Employee.class);
} catch (Exception e1) {
e1.printStackTrace();
}
return null;
}
}
複製程式碼