package dao.impl;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;import dao.photoDao;
import entity.Photo;public class photoDaoImpl extends BaseDao implements photoDao {
private Connection conn = null; //用于保存数据库连接
private PreparedStatement pstmt = null; //用于执行SQL语句
private ResultSet rs = null; //用户保存查询结果集
private String sql = null; //查询语句
private String time = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()); //得到当前日期
/**
* 增加照片
*/
public int AddPhoto(Photo photo) {
sql = "insert into Photo(albumId,photoName,description,fileAdress,publishTime) values (" +
photo.getAlbumId() + ",?,?,?,?)";
String[] pram = {photo.getPhotoName(),photo.getDescription(),photo.getFileAdress(),photo.getPublishTime()};
return this.executeSQL(sql, pram);
} /**
* 根据照片Id删除照片
*/
public int DeletePhoto(int photoId) {
sql = "delete from Photo where photoId = " + photoId;
return this.executeSQL(sql, null);
} /**
* 查找照片
* @return 返回照片列表集
*/
public List PhotoList(int albumId) {
List list = new ArrayList();//用来保存照片列表
sql = "select top 12 * from Photo where albumId = "+
albumId+ " order by publishTime desc";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
/*保存主题List中*/
while(rs.next()){
Photo photo = new Photo();
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
list.add(photo);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return list; } /**
* 查找照片信息
* @return 返回照片信息
*/
public Photo SearchPhoto(int photoId) {
Photo photo = new Photo();
sql = "select * from Photo where photoId =" + photoId;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if (rs.next()) {
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeAll(conn, pstmt, rs);
}
return photo; }
/**
* 更改照片信息
*/
public int UpdatePhoto(Photo photo) {
sql = "update Photo set albumId = " + photo.getAlbumId() + ",photoName = ?,description = ?,fileAdress = ?,publishTime = ? where photoId = "
+ photo.getPhotoId();
String[] pram = {photo.getPhotoName(),photo.getDescription(),photo.getFileAdress(),time};
return this.executeSQL(sql, pram);
} /**
* 根据相册查询照片数量
*/
public int findCountPhoto(int albumId) {
int num = 0;
sql = "select count(*) from Photo where albumId = " + albumId;
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
if(rs.next()){
num = rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return num;
} public List findTopPhoto() {
List list = new ArrayList();//用来保存照片列表
sql = "select top 10 * from Photo order by publishTime desc";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
/*保存主题List中*/
while(rs.next()){
Photo photo = new Photo();
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
list.add(photo);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return list; }
/**
* 根据图片的地址查询是否存在图片
*/
public int searchPhotoByFile(String fileAdress) {
int num = 0;
sql = "select count(*) from Photo where FileAdress = '" + fileAdress+"'";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
if(rs.next()){
num = rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return num;
} public int DeletePhotoByAlbumId(int albumId) {
sql = "delete from photo where albumId="+ albumId;
return this.executeSQL(sql, null);
}
}
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;import dao.photoDao;
import entity.Photo;public class photoDaoImpl extends BaseDao implements photoDao {
private Connection conn = null; //用于保存数据库连接
private PreparedStatement pstmt = null; //用于执行SQL语句
private ResultSet rs = null; //用户保存查询结果集
private String sql = null; //查询语句
private String time = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()); //得到当前日期
/**
* 增加照片
*/
public int AddPhoto(Photo photo) {
sql = "insert into Photo(albumId,photoName,description,fileAdress,publishTime) values (" +
photo.getAlbumId() + ",?,?,?,?)";
String[] pram = {photo.getPhotoName(),photo.getDescription(),photo.getFileAdress(),photo.getPublishTime()};
return this.executeSQL(sql, pram);
} /**
* 根据照片Id删除照片
*/
public int DeletePhoto(int photoId) {
sql = "delete from Photo where photoId = " + photoId;
return this.executeSQL(sql, null);
} /**
* 查找照片
* @return 返回照片列表集
*/
public List PhotoList(int albumId) {
List list = new ArrayList();//用来保存照片列表
sql = "select top 12 * from Photo where albumId = "+
albumId+ " order by publishTime desc";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
/*保存主题List中*/
while(rs.next()){
Photo photo = new Photo();
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
list.add(photo);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return list; } /**
* 查找照片信息
* @return 返回照片信息
*/
public Photo SearchPhoto(int photoId) {
Photo photo = new Photo();
sql = "select * from Photo where photoId =" + photoId;
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if (rs.next()) {
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeAll(conn, pstmt, rs);
}
return photo; }
/**
* 更改照片信息
*/
public int UpdatePhoto(Photo photo) {
sql = "update Photo set albumId = " + photo.getAlbumId() + ",photoName = ?,description = ?,fileAdress = ?,publishTime = ? where photoId = "
+ photo.getPhotoId();
String[] pram = {photo.getPhotoName(),photo.getDescription(),photo.getFileAdress(),time};
return this.executeSQL(sql, pram);
} /**
* 根据相册查询照片数量
*/
public int findCountPhoto(int albumId) {
int num = 0;
sql = "select count(*) from Photo where albumId = " + albumId;
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
if(rs.next()){
num = rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return num;
} public List findTopPhoto() {
List list = new ArrayList();//用来保存照片列表
sql = "select top 10 * from Photo order by publishTime desc";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
/*保存主题List中*/
while(rs.next()){
Photo photo = new Photo();
photo.setPhotoId(rs.getInt("photoId"));
photo.setAlbumId(rs.getInt("albumId"));
photo.setPhotoName(rs.getString("photoName"));
photo.setDescription(rs.getString("description"));
photo.setFileAdress(rs.getString("fileAdress"));
photo.setPublishTime(rs.getString("publishTime"));
list.add(photo);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return list; }
/**
* 根据图片的地址查询是否存在图片
*/
public int searchPhotoByFile(String fileAdress) {
int num = 0;
sql = "select count(*) from Photo where FileAdress = '" + fileAdress+"'";
try{
conn = this.getConn(); //获得数据库连接
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(); //执行SQL,得到结果集
if(rs.next()){
num = rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace(); //处理异常
}finally{
this.closeAll(conn, pstmt, rs); //释放资源
}
return num;
} public int DeletePhotoByAlbumId(int albumId) {
sql = "delete from photo where albumId="+ albumId;
return this.executeSQL(sql, null);
}
}
conn = this.getConn(); //获得数据库连接
this.closeAll(conn, pstmt, rs); //释放资源看来,你这个类中应该还有两个方法为获得连接跟释放连接资源,建议这两个方法用个jdbc工具类实现 是否想问的是这个 ?