测试
package com.itheima.dao;
import com.itheima.pojo.LinkMan;
import com.itheima.pojo.PageBean;
import com.itheima.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.List;
/**
* @author qiuji
* @version 1.0
* @date 2021-07-26 16:50
*/
public class LinkManDao {
private QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
public void delete(String id) throws SQLException {
String sql = "delete from linkman where id = ?";
queryRunner.update(sql, id);
}
/**
* 查询所有联系人
*
* @return
* @throws SQLException
*/
public List<LinkMan> findAll() throws SQLException {
String sql = "select * from linkman";
List<LinkMan> query = queryRunner.query(sql, new BeanListHandler<>(LinkMan.class));
return query;
}
/**
* 添加联系人
*
* @param linkMan
* @throws SQLException
*/
public void add(LinkMan linkMan) throws SQLException {
String sql = "insert into linkman values(null,?,?,?,?,?,?)";
queryRunner.update(sql,
linkMan.getName(),
linkMan.getSex(),
linkMan.getAge(),
linkMan.getAddress(),
linkMan.getQq(),
linkMan.getEmail()
);
}
/**
* 查找一条记录
*
* @param id
* @return
* @throws SQLException
*/
public List<LinkMan> findOne(Integer id) throws SQLException {
String sql = "select * from linkman where id = ?";
List<LinkMan> list = queryRunner.query(sql, new BeanListHandler<>(LinkMan.class), id);
return list;
}
public void update(LinkMan linkMan1) throws SQLException {
//update里面占位符?不能加单引号 否则会识别不到 ?就成了单引号了
String sql1 = "update linkman set name=?,sex=?,age=?,address=?,qq=?,email=? where id = ?";
System.out.println( "update linkman set name='"+linkMan1.getName()
+"',sex='"+linkMan1.getSex()+"',age="+linkMan1.getAge()
+",address='"+linkMan1.getAddress()+"',qq='"+linkMan1.getQq()
+"',email='"+linkMan1.getEmail()+"' where id = "+linkMan1.getId());
queryRunner.update(sql1,
linkMan1.getName(),
linkMan1.getSex(),
linkMan1.getAge(),
linkMan1.getAddress(),
linkMan1.getQq(),
linkMan1.getEmail(),
linkMan1.getId()
);
}
/**
* 查询总数据条数
* @return
*/
public Long findTotalSize() throws SQLException {
String sql = "select count(*) from linkman";
Long totalSize = (Long) queryRunner.query(sql, new ScalarHandler());
return totalSize;
}
/**
* 查询获取到的当前页数据
* @param l
* @param pageSize
* @return
* @throws SQLException
*/
public List<LinkMan> findCurrentPageList(long l, Integer pageSize) throws SQLException {
String sql = "select * from linkman limit ?,?";
List<LinkMan> query = queryRunner.query(sql, new BeanListHandler<>(LinkMan.class), (l-1)*pageSize, pageSize);
System.out.println(query);
return query;
}
}