目录
五、新建一个DogDaoImpl2实现类，继承基类BaseDao2，实现DogDao接口的两个查询功能
1.public void testGetDogById2()
2.public void testGetAllDog2()
(三)新建DogDaoImpl3实现类，继承BaseDao2，实现DagDao2接口的功能
1.public void testGetDogById3()
上回书我们说到了 jdbcUtils工具类的优化升级——通过配置文件连接mysql8.0，并对mysql8.0中的表进行[增删改]操作
接下来继续讲查询的封装，因为增删改都返回影响的行数，所以可以统一封装在executeUpdate方法中，但是查询返回的可能是一条或多条结果，与executeUpdate封装的方法有所差别。
我们还是对dog表进行操作
一、在DogDao中新增查询的功能
package com.atguigu.kb21.dao;
import com.atguigu.kb21.pojo.Dog;
import java.util.List;
public interface DogDao {
// 查询操作
// 根据狗狗id查询对应的主人与狗狗信息
Dog getDogById(Integer id);
// 查询所有狗狗的信息
List<Dog> getAllDog();
}
二、在DogDaoImpl类中继承基类，并实现接口的功能
package com.atguigu.kb21.dao;
import com.atguigu.kb21.pojo.Dog;
import com.atguigu.kb21.pojo.Master;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DogDaoImpl extends BaseDao implements DogDao{
@Override
public List<Dog> getAllDog() {
String sql = "select id,name,health,love,strain,lytime 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.setLytime(resultSet.getDate("lytime"));
dogs.add(dog);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
super.close(preparedStatement,connection,resultSet);
}
return dogs;
}
@Override
public Dog getDogById(Integer id){
String sql = "select " +
"d.id,d.name,d.health,d.love,d.strain,d.lytime, " +
"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.setLytime(resultSet.getDate("lytime"));
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"));
master.setDid(resultSet.getInt("did"));
master.setDog(dog);
masters.add(master);
}
dog.setMaster(masters);
} catch (SQLException e) {
e.printStackTrace();
}finally {
super.close(preparedStatement,connection,resultSet);
}
return dog;
}
}
三、创建工具接口RowMapper<T>
从上面的代码可以看出，查询的操作还是有冗余的代码，下面我们创建一个工具接口，来实现resultSet的操作
package com.atguigu.kb21.utils;
import java.sql.ResultSet;
public interface RowMapper<T> {
T mapper(ResultSet resultSet);
}
文章开头处我提到过，sql的查询语句会返回各种形式的结果，所以这里我们将接口定义为泛型。
四、重新定义一个基类BaseDao2
接下来我们重新定义一个基类BaseDao2，将查询的操作进行封装，注意这里的BaseDao2也是泛型。
package com.atguigu.kb21.dao2;
import com.atguigu.kb21.dao.BaseDao;
import com.atguigu.kb21.utils.RowMapper;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class BaseDao2<T> {
private static String driver;
private static String url;
private static String user;
private static String pwd;
static {
Properties properties = new Properties();
InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
try {
properties.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
driver = properties.getProperty("mysqldriver");
url = properties.getProperty("mysqlurl");
user = properties.getProperty("mysqluser");
pwd = properties.getProperty("mysqlpwd");
System.out.println(driver);
System.out.println(url);
System.out.println(user);
System.out.println(pwd);
}
public Connection getConnection(){
Connection connection = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public void close(PreparedStatement preparedStatement, Connection connection, ResultSet resultSet){
try {
if(null != preparedStatement){
preparedStatement.close();
}
if(null != connection){
connection.close();
}
if(null != resultSet){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void close(PreparedStatement preparedStatement, Connection connection){
try {
if(null != preparedStatement){
preparedStatement.close();
}
if(null != connection){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void close(Connection connection){
try {
if(null != connection){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 第一个查询需求是根据狗狗id返回狗狗信息
* 由于每条狗只有一个id，所以可以确定的是：第一个查询需求返回的是一条数据
* 由于我们将dog表中所有字段映射定义为Dog类的属性，那么返回的这条狗狗信息，就是Dog类的实例化对象
*/
public T getObjectByParams(RowMapper<T> row,String strSql, Object...params) {
Connection connection = this.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(strSql);
if (null != params) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
// RowMapper接口是做组装的工作，得出的结果是一个实例化的对象
return row.mapper(resultSet);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.close(preparedStatement,connection,resultSet);
}
return null;
}
/**
* 同样的，一条狗狗可能对应多个主人，也会返回多个主人对象，这时候需要将这些主人对象存入集合中
* @param row
* @param sqlStr
* @param params
* @return
*/
public List<T> getObjectListByParam(RowMapper<T> row,String sqlStr,Object...params){
Connection connection = this.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
ArrayList<T> ts = new ArrayList<>();
try {
preparedStatement = connection.prepareStatement(sqlStr);
if(null != params){
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i+1,params[i]);
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
ts.add(row.mapper(resultSet));
}
return ts;
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.close(preparedStatement,connection,resultSet);
}
return null;
}
}
五、新建一个DogDaoImpl2实现类，继承基类BaseDao2，实现DogDao接口的两个查询功能
package com.atguigu.kb21.dao2;
import com.atguigu.kb21.dao.DogDao;
import com.atguigu.kb21.pojo.Dog;
import com.atguigu.kb21.utils.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class DogDaoImpl2 extends BaseDao2<Dog> implements DogDao {
@Override
public Dog getDogById(Integer id) {
String sqlStr = "select d.id,d.name,d.health,d.love,d.strain,d.lytime from dog d where d.id= ?";
Dog dog = super.getObjectByParams(row, sqlStr, id);
return dog;
}
@Override
public List<Dog> getAllDog() {
String sql = "select id,name,health,love,strain,lytime from dog";
List<Dog> dogs = super.getObjectListByParam(row, sql);
return dogs;
}
// 这里我们要对RowMapper接口进行实例化，用来实现resultSet的操作
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.setLytime(resultSet.getDate("lytime"));
} catch (SQLException e) {
e.printStackTrace();
}
return dog;
}
};
}
六、在DogTest测试类中进行两个查询需求的测试
package com.atguigu.kb21;
import com.atguigu.kb21.dao.DogDaoImpl;
import com.atguigu.kb21.dao2.DogDaoImpl2;
import com.atguigu.kb21.pojo.Dog;
import com.atguigu.kb21.pojo.Master;
import org.junit.Test;
import java.util.List;
public class DogTest {
// 测试查询方法——resultSet优化前
@Test
public void testGetDogById(){
DogDaoImpl dogDao = new DogDaoImpl();
Dog dog = dogDao.getDogById(7);
System.out.println(dog.toString());
System.out.println("狗狗与主人信息");
List<Master> masters = dog.getMaster();
for (Master master :
masters) {
System.out.println(master.toString());
}
}
@Test
public void testGetAllDog(){
DogDaoImpl dogDao = new DogDaoImpl();
List<Dog> dogs = dogDao.getAllDog();
// 遍历集合
for (Dog dog:
dogs) {
System.out.println(dog);
}
}
// 测试查询方法——resultSet优化后
@Test
public void testGetDogById2(){
DogDaoImpl2 dogDao = new DogDaoImpl2();
Dog dog = dogDao.getDogById(7);
System.out.println(dog);
}
//
@Test
public void testGetAllDog2(){
DogDaoImpl dogDao = new DogDaoImpl();
List<Dog> dogs = dogDao.getAllDog();
// 遍历集合
for (Dog dog:
dogs) {
System.out.println(dog);
}
}
}七、单表查询优化后返回的结果
1.public void testGetDogById2()
2.public void testGetAllDog2()
八、多表联查的实现
我们还是通过狗狗Id查询信息，但是要关联Master表，同时显示狗狗id对应的狗狗信息和主人信息
(一)新建DogDao2接口
package com.atguigu.kb21.dao2;
import com.atguigu.kb21.pojo.Dog;
public interface DogDao2 {
// 根据id查询狗狗信息与对应的主人信息
Dog getDogById(Integer integer);
}
(二)修改Dog类，添加主人集合
package nj.zb.kb21.pojo;
import java.util.Date;
import java.util.List;
public class Dog {
// 数据库中的每一个字段映射为java类
// name,health,love,
private Integer id;
private String name;
private Integer health;
private Integer love;
private String strain;
private Date lytime;
private List<Master> master;// 当前狗狗对象有过的主人信息 一对多
public List<Master> getMaster() {
return master;
}
public void setMaster(List<Master> master) {
this.master = master;
}
public Dog() {
}
public Dog(Integer id, String name, Integer health, Integer love, String strain, Date lytime) {
this.id = id;
this.name = name;
this.health = health;
this.love = love;
this.strain = strain;
this.lytime = lytime;
}
public Dog(String name, Integer health, Integer love, String strain) {
this.name = name;
this.health = health;
this.love = love;
this.strain = strain;
}
@Override
public String toString() {
return "Dog{" +
"id=" + id +
", name='" + name + '\'' +
", health=" + health +
", love=" + love +
", strain='" + strain + '\'' +
", lytime=" + lytime +
'}';
}
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 Integer getHealth() {
return health;
}
public void setHealth(Integer health) {
this.health = health;
}
public Integer getLove() {
return love;
}
public void setLove(Integer love) {
this.love = love;
}
public String getStrain() {
return strain;
}
public void setStrain(String strain) {
this.strain = strain;
}
public Date getLytime() {
return lytime;
}
public void setLytime(Date lytime) {
this.lytime = lytime;
}
}
(三)新建DogDaoImpl3实现类，继承BaseDao2<T>，实现DagDao2接口的功能
这里我们的实现思路是：通过传入id，找到这条狗的信息，因为主master表中的did对应dog表中的id，所以可以通过获取到的那一条狗的信息，找到对应的主人信息，返回的结果有多个Master对象，需要封装在List集合中，然后才能进行遍历获取主人信息。
package com.atguigu.kb21.dao2;
import com.atguigu.kb21.pojo.Dog;
import com.atguigu.kb21.pojo.Master;
import com.atguigu.kb21.utils.RowMapper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DogDaoImpl3<T> extends BaseDao2<T> implements DogDao2 {
@Override
public Dog getDogById(Integer id) {
String sql1 = "select id,name,health,love,strain,lytime from dog where id = ?";
String sql2 = "select pid,name,age,gender,yearnum,did from master where did = ?";
Dog dog =(Dog) super.getObjectByParams((RowMapper<T>) dogRow, sql1, id);
List<Master> masters = this.masterList(masterRow, sql2, id);
dog.setMaster(masters);
return dog;
}
RowMapper<Dog> dogRow = 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.setLytime(resultSet.getDate("lytime"));
} catch (SQLException e) {
e.printStackTrace();
}
return dog;
}
};
RowMapper<Master> masterRow = new RowMapper<Master>() {
@Override
public Master mapper(ResultSet resultSet) {
Master master = new Master();
try {
master.setPid(resultSet.getInt("pid"));
master.setName(resultSet.getString("name"));
master.setAge(resultSet.getInt("age"));
master.setGender(resultSet.getString("gender"));
master.setYearnum(resultSet.getInt("yearnum"));
master.setDid(resultSet.getInt("did"));
} catch (SQLException e) {
e.printStackTrace();
}
return master;
}
};
// 定义masterList的连接操作
// 之所以不能在BaseDao2中写，是因为BaseDao2中只能返回狗狗类型，会有冲突
public List<Master> masterList(RowMapper<Master> rowMapper,String sql,Object...params){
Connection connection = super.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
ArrayList<Master> ts = new ArrayList<>();
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()){
ts.add(rowMapper.mapper(resultSet));
}
return ts;
} catch (SQLException e) {
e.printStackTrace();
}finally {
super.close(preparedStatement,connection,resultSet);
}
return null;
}
}
(四)DogTest测试类中进行测试
@Test
public void testGetDogById3(){
DogDaoImpl3 dogDaoImpl3 = new DogDaoImpl3();
Dog dog = dogDaoImpl3.getDogById(7);
System.out.println(dog.toString());
System.out.println("狗狗对应的主人信息");
List<Master> masters = dog.getMaster();
for (Master master :
masters) {
System.out.println(master.toString());
}
}(五)返回结果
1.public void testGetDogById3()
1562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
