Maison >base de données >tutoriel mysql >JDBC优化
一.什么是dao 二.dao模式实现 三.dao优化 定义包的模式如下: TecDao package dao;import java.sql.SQLException;import java.util.List;import pojo.TecPojo;public interface TecDao {public int insert(TecPojo tec)throws SQLException;public int delet
一.什么是dao
二.dao模式实现
三.dao优化
定义包的模式如下:
TecDao
package dao; import java.sql.SQLException; import java.util.List; import pojo.TecPojo; public interface TecDao { public int insert(TecPojo tec)throws SQLException; public int delete(int id)throws SQLException; public int update(TecPojo tec)throws SQLException; public List<TecPojo> query(TecPojo tec)throws SQLException; }
package dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import dao.TecDao; import pojo.TecPojo; import util.SQLUtil; public class TecDaoImpl implements TecDao{ public int insert(TecPojo tec) throws SQLException { Connection conn = SQLUtil.getConnection(); String sql = "insert into tec (name,age,gender,job,createDate)values(?,?,?,?,?)"; PreparedStatement state = conn.prepareStatement(sql); state.setString(1, tec.getName()); state.setInt(2, tec.getAge()); state.setString(3, tec.getGender()); state.setString(4, tec.getJob()); state.setString(5, tec.getCreateDate()); int result = state.executeUpdate(); if(result>0){ System.out.println("Yes"); } return result; } public int update(TecPojo tec) throws SQLException { Connection conn = SQLUtil.getConnection(); String sql = "update tec set name = ?,age = ? where id = 11"; PreparedStatement state = conn.prepareStatement(sql); state.setString(1, tec.getName()); state.setInt(2, tec.getAge()); int result = state.executeUpdate(); if(result>0){ System.out.println("Yes"); } return result; } public List<TecPojo> query(TecPojo tec) throws SQLException { Connection conn = SQLUtil.getConnection(); String sql = "select * from tec"; PreparedStatement state = conn.prepareStatement(sql); ResultSet resultset = state.executeQuery(); ArrayList list = new ArrayList(); TecPojo tp = null; while(resultset.next()){ tp = new TecPojo(); tp.setId(resultset.getInt("id")); tp.setName(resultset.getString("name")); tp.setAge(resultset.getInt("age")); tp.setGender(resultset.getString("gender")); tp.setJob(resultset.getString("job")); tp.setCreateDate(resultset.getString("createDate")); list.add(tp); } for(int i = 0;i<list.size();i++){ System.out.println(list.get(i)); } return list; } public int delete(int id) throws SQLException { Connection conn = SQLUtil.getConnection(); String sql = "delete from tec where id = "+id; PreparedStatement state = conn.prepareStatement(sql); int result = state.executeUpdate(); if(result>0){ System.out.println("Yes"); } return result; } }
package pojo; public class TecPojo { private int id; private String name; private int age; private String gender; private String job; private String createDate; public TecPojo(String name, int age, String gender, String job, String createDate) { super(); this.name = name; this.age = age; this.gender = gender; this.job = job; this.createDate = createDate; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String toString() { return id+" "+name+" "+age+" "+gender+" "+job+" "+createDate; } public TecPojo() { super(); } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } }
package util; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class SQLUtil { private static String url; private static String user; private static String password; private static String driver; static{ try { Properties pro = new Properties(); InputStream ins = SQLUtil.class.getResourceAsStream("/sqlconfig.properties"); pro.load(ins); url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection(){ Connection conn = null; try { conn = DriverManager.getConnection(url,user,password); } catch (SQLException e) { e.printStackTrace(); } return conn; } }
package util; import java.sql.SQLException; import dao.impl.TecDaoImpl; import pojo.TecPojo; public class Test { public static void main(String[] args) { SQLUtil util = new SQLUtil(); TecPojo tp = new TecPojo("bb",22,"man","teacher","2016-4-6"); TecDaoImpl tdi = new TecDaoImpl(); try { // tdi.insert(tp); // tdi.update(tp); tdi.query(tp); // tdi.delete(12); } catch (SQLException e) { e.printStackTrace(); } } }
此时发现,TecDaoImpl的方法有多次重复,于是对重复的方法再次进行封装
SQLTemplete
package util; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class SQLTemplete { public static int update(String sql,Object...object)throws SQLException{ Connection conn = SQLUtil.getConnection(); PreparedStatement state = conn.prepareStatement(sql); for(int i=0;i<object.length;i++){ state.setObject(i+1, object[i]); } int result = state.executeUpdate(); return result; } public static ResultSet query(String sql,Object...object)throws SQLException{ Connection conn = SQLUtil.getConnection(); PreparedStatement state = conn.prepareStatement(sql); for(int i=0;i<object.length;i++){ state.setObject(i+1, object[i]); } ResultSet resultset = state.executeQuery(); return resultset; } }
TecDaoImpl
package dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import dao.TecDao; import pojo.TecPojo; import util.SQLTemplete; import util.SQLUtil; public class TecDaoImpl implements TecDao{ public int insert(TecPojo tec) throws SQLException { String sql = "insert into tec (name,age,gender,job,createDate)values(?,?,?,?,?)"; int result = SQLTemplete.update(sql, tec.getName(),tec.getAge(),tec.getGender(),tec.getJob(),tec.getCreateDate()); if(result>0){ System.out.println("Yes"); } return result; } public int update(TecPojo tec) throws SQLException { String sql = "update tec set name = ?,age = ? where id = 11"; int result = SQLTemplete.update(sql, tec.getName(),tec.getAge()); if(result>0){ System.out.println("Yes"); } return result; } public List<TecPojo> query(TecPojo tec) throws SQLException { String sql = "select * from tec"; ResultSet resultset = SQLTemplete.query(sql); ArrayList<TecPojo> list = new ArrayList<TecPojo>(); TecPojo tp = null; while(resultset.next()){ tp = new TecPojo(); tp.setId(resultset.getInt("id")); tp.setName(resultset.getString("name")); tp.setAge(resultset.getInt("age")); tp.setGender(resultset.getString("gender")); tp.setJob(resultset.getString("job")); tp.setCreateDate(resultset.getString("createDate")); list.add(tp); } for(int i = 0;i<list.size();i++){ System.out.println(list.get(i)); } return list; } public int delete(int id) throws SQLException { String sql = "delete from tec where id = "+id; int result = SQLTemplete.update(sql); if(result>0){ System.out.println("Yes"); } return result; } }