1.apache-dbutilsSQL语句是固定,不能通过参数传入;对于select操作,如果有返回值,返回类型不能固定,需要使用泛型。DAO:data access object 数据访问对象。BasicDao是专门和数据库交互的,即完成对数据库表的crud操作。
BasicDAO应用示例需创建存放工具类(dao_.utils)的包;把javabean都存放到dao_.damain包下;XxxDAO和BasicDAO存放到dao_.dao包下;测试类都存放到dao_.test包下。
工具类:
private static DataSource ds; static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src\\druid.properties")); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //编写getConnection方法 public static Connection getConnection() throws SQLException { return ds.getConnection(); } //关闭连接 public static void close(ResultSet resultSet, Statement statement, Connection connection) { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { throw new RuntimeException(e); } }
创建存放的Actor对象存放到dao_.damain包下:
private Integer id; private String name; private String sex; private Date borndate; private String phone; public Actor() { } public Actor(Integer id, String name, String sex, Date borndate, String phone) { this.id = id; this.name = name; this.sex = sex; this.borndate = borndate; this.phone = phone; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBorndate() { return borndate; } public void setBorndate(Date borndate) { this.borndate = borndate; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "\nActor{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", borndate=" + borndate + ", phone='" + phone + '\'' + '}'; }
创建BasicDAO(是XxxDAO类的父类)存放到dao_.dao包下:
public class BasicDAO<T> { private QueryRunner qr = new QueryRunner(); //dml方法 public int update(String sql, Object... parameters) { Connection connection = null; try { connection = JDBCUtilsByDruid.getConnection(); int update = qr.update(connection, sql, parameters); return update; } catch (SQLException e) { throw new RuntimeException(e); } finally { JDBCUtilsByDruid.close(null, null, connection); } } //查询的结果是多行 public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) { Connection connection = null; try { connection = JDBCUtilsByDruid.getConnection(); return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters); } catch (SQLException e) { throw new RuntimeException(e); } finally { JDBCUtilsByDruid.close(null, null, connection); } } //查询单行结果的方法 public T querySingle(String sql, Class<T> clazz, Object... parameters) { Connection connection = null; try { connection = JDBCUtilsByDruid.getConnection(); return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters); } catch (SQLException e) { throw new RuntimeException(e); } finally { JDBCUtilsByDruid.close(null, null, connection); } } //查询单行单列的方法 public Object queryScalar(String sql, Object... parameters) { Connection connection = null; try { connection = JDBCUtilsByDruid.getConnection(); return qr.query(connection, sql, new ScalarHandler(), parameters); } catch (SQLException e) { throw new RuntimeException(e); } finally { JDBCUtilsByDruid.close(null, null, connection); } } }
ActorDAO需继承自BasicDAO<Actor>(Actor是具体类型,ActorDAO是对Actor表的操作)存放到dao_.dao包下:
public class ActorDAO extends BasicDAO<Actor> { }
创建测试类存放到dao_.test包下:
public static void main(String[] args) { TestDAO testDAO = new TestDAO(); testDAO.testActorDAO(); } public void testActorDAO() { ActorDAO actorDAO = new ActorDAO(); //查询全部 List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1); System.out.print("===查询结果==="); for (Actor actor : actors) { System.out.println(actor); } //查询单行 Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 5); System.out.println("===查询单行结果==="); System.out.print(actor); //查询单行单列 Object o = actorDAO.queryScalar("select name from actor where id = ?", 5); System.out.println("===查询单行单列值==="); System.out.println(o); //4.dml操作 增加 // int insert = actorDAO.update("insert into actor values(null,?,?,?,?)", "小芳", "女", "2005-5-5", "99999"); // System.out.println(insert > 0 ? "执行成功" : "执行没有影响表"); //dml操作 修改 // int update = actorDAO.update("update actor set name = ? where id = ?", "小明", 4); // System.out.println(update > 0 ? "执行成功" : "执行没有影响表"); //dml操作 删除 int delete = actorDAO.update("delete from actor where id = ?", 7); System.out.println(delete > 0 ? "执行成功" : "执行没有影响表"); }