jdbc查询

jdbc查询

在接口类中

List<Dog> getAllDog();

实现类中

@Override
    public List<Dog> getAllDog() {
        String sql = "select id, name, health, love, strain, lytm from dog";
        Connection connection = super.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Dog> dogs = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Dog dog = new Dog();
                dog.setId(resultSet.getInt("id"));
                dog.setName(resultSet.getString("name"));
                dog.setHealth(resultSet.getInt("health"));
                dog.setLove(resultSet.getInt("love"));
                dog.setStrain(resultSet.getString("strain"));
                dog.setLytm(resultSet.getDate("lytm"));
                dogs.add(dog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(preparedStatement,connection,resultSet);
        }
        return dogs;
    }

根据id查找领养主人（主人可以有多个）

public Dog getDogById(Integer id) {
        String sql = "select d.id,d.name,d.health,d.love,d.strain,d.lytm," +
                "m.pid,m.name pname,m.age,m.gender,m.yearnum,m.did" +
                " from dog d " +
                "left join master m on d.id = m.did " +
                "where d.id = ?";
        Connection connection = super.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Dog dog = new Dog();
        List<Master> masters = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                dog.setId(resultSet.getInt("id"));
                dog.setName(resultSet.getString("name"));
                dog.setHealth(resultSet.getInt("health"));
                dog.setLove(resultSet.getInt("love"));
                dog.setStrain(resultSet.getString("strain"));
                dog.setLytm(resultSet.getDate("lytm"));
                Master master = new Master();
                master.setPid(resultSet.getInt("pid"));
                master.setName(resultSet.getString("pname"));
                master.setAge(resultSet.getInt("age"));
                master.setGender(resultSet.getString("gender"));
                master.setYearnum(resultSet.getInt("yearnum"));

                masters.add(master);
            }
            dog.setMasters(masters);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(preparedStatement,connection,resultSet);
        }
        return dog;
    }

封装写法

BaseDao2

public T getObjectByParams(RowMapper<T> row, String sql, Object... params){
    Connection connection = this.getConnection();
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
    	 preparedStatement = connection.prepareStatement(sql);
         if (null != params){
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
        }
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            return row.mapper(resultSet);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        this.close(preparedStatement,connection,resultSet);
    }
        return null;

}

接口类RowMapper

public interface RowMapper<T> {
    T mapper(ResultSet resultSet);

}

实现类DogDaoImpl2

@Override
public Dog getDogById(Integer id) {
	String sql = " select d.id,d.name,d.health,d.love,d.strain,d.lytm " +
                " from dog d where d.id = ?";
	Dog dog = super.getObjectByParams(row, sql, id);
	return dog;
}

RowMapper<Dog> row = new RowMapper<Dog>() {
	@Override
	public Dog mapper(ResultSet resultSet) {
		Dog dog = new Dog();
		try {
                dog.setId(resultSet.getInt("id"));
                dog.setName(resultSet.getString("name"));
                dog.setHealth(resultSet.getInt("health"));
                dog.setLove(resultSet.getInt("love"));
                dog.setStrain(resultSet.getString("strain"));
                dog.setLytm(resultSet.getDate("lytm"));
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return dog;
        }
    };

测试类

@Test
public void testGetDogById2(){
	Dogdao dogDao = new DogDaoImpl2();
	Dog dog= dogDao.getDogById(6);
	System.out.println(dog.toString());
}