JSP新闻系统之二DAO框架
【摘要】
package com.news.util; import java.sql.*;import java.util.ArrayList;import java.util.List; //Dao工厂类public class DaoFactory { private static String driver = "com.microsof...
-
package com.news.util;
-
-
import java.sql.*;
-
import java.util.ArrayList;
-
import java.util.List;
-
-
//Dao工厂类
-
public class DaoFactory {
-
private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
-
private static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=News";
-
private static String user = "sa";
-
private static String pwd = "123@qwe";
-
-
// private static String driver="com.mysql.jdbc.Driver";
-
// private static String url="jdbc:mysql://localhost:3306/news";
-
// private static String user = "root" ;
-
// private static String pwd = "admin" ;
-
-
// 1.公共方法是获得数据库链接对象
-
public static Connection getConnection() {
-
Connection con = null;
-
try {
-
Class.forName(driver);// 加,连
-
con = DriverManager.getConnection(url, user, pwd);
-
} catch (ClassNotFoundException e) {
-
e.printStackTrace();
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}
-
return con;// 非void都需要return
-
}
-
// 2.关闭所有方法;有3个参数!,省代码了!!!
-
public static void closeAll(ResultSet rs, Statement stmt, Connection con) {
-
try {
-
if (rs != null) {
-
rs.close();
-
}
-
if (stmt != null) {
-
stmt.close();
-
}
-
if (con != null) {
-
con.close();
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}
-
}
-
-
// 3.setParams,用来设置预编译语句对象的?占位符的值;
-
public static void setParams(PreparedStatement pstmt, Object[] params) {
-
if (params == null) {
-
return;
-
}// return:直接返回，啥也不做;
-
try {
-
for (int i = 0; i < params.length; i++) {
-
pstmt.setObject(i + 1, params[i]);
-
}
-
} catch (SQLException e) {// 有异常,加上去
-
e.printStackTrace();
-
}
-
}
-
-
// 4.做公共的更新方法，可以更新所有的基本sql语句;
-
public int executeUpdate(String sql, Object[] params) {
-
// 1.声明对象;是将来工作当中省内存;
-
Connection con = null;
-
PreparedStatement pstmt = null;
-
int count = 0; // 增删改受影响的行数;
-
-
try {
-
con = this.getConnection();// 调用本类的方法；
-
pstmt = con.prepareStatement(sql);// 建对象:预编译对象,?
-
setParams(pstmt, params);// 调用设置?的方法，已经写过了!!!
-
count = pstmt.executeUpdate();// 3.执行;
-
} catch (SQLException e) {
-
e.printStackTrace();
-
} finally {
-
this.closeAll(null, pstmt, con);
-
}
-
return count;
-
}
-
-
// 5.执行查询方法;
-
public static List executeQuery(String sql, Object[] params) {
-
Connection con = null;
-
PreparedStatement pstmt = null;
-
ResultSet rs = null;
-
int colCount = 0;
-
ArrayList tableList=new ArrayList();//表集合
-
-
try {
-
con = getConnection();
-
pstmt = con.prepareStatement(sql);
-
setParams(pstmt, params);
-
rs = pstmt.executeQuery();// 执行查询，结果给rs
-
ResultSetMetaData rd = rs.getMetaData();// 获得元数据
-
colCount = rd.getColumnCount();
-
while (rs.next()) {
-
ArrayList rowList = new ArrayList();//行集合
-
for (int i = 1; i <= colCount; i++) {
-
rowList.add(rs.getString(i));
-
}
-
tableList.add(rowList);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
closeAll(rs,pstmt,con);
-
}
-
return tableList;
-
}
-
}
-
package com.news.dao;
-
-
import java.util.List;
-
-
import com.news.entity.User;
-
-
/**
-
* 用户访问接口
-
* @author Administrator
-
*
-
*/
-
public interface UserDao {
-
public List<User> getAllUser();//查询所有用户
-
public boolean saveUser(String username,String pwd,String email,String address,String bobby);
-
public User queryUserByNameAndPwd(String username,String userpwd);//根据用户名和密码来查询用户是否存在;
-
//通过查询获取数据表中数据总条数;
-
public int getCount();
-
//根据当前页码和页数，来分页的方法;返回User集合;
-
public List<User>queryUserByPage(int currentPage,int pageSize);
-
}
-
package com.news.dao;
-
-
import java.util.List;
-
-
import com.news.entity.Topic;
-
-
public interface TopicDao {
-
public List<Topic> getAllTopic();//获得所有栏目
-
public int addTopic(String tname); //实现类和接口方法务必一致;!
-
public boolean deleteTopicById(int id);//根据ID删除主题栏目;
-
-
//更新主题，先根据ID找到该主题；
-
//根据ID查询一条主题记录
-
Topic queryTopicById(int id);
-
//根据ID修改一条记录
-
public int updateTopicById(int id,String tname);
-
}
-
package com.news.dao;
-
-
import java.util.List;
-
-
import com.news.entity.News;
-
-
/**
-
* News接口
-
* @author Administrator
-
*
-
*/
-
public interface NewsDao {
-
List<News>queryNews();
-
public int addNews(News news);//使用实体类进行传输数据;
-
//根据新闻主题查询新闻
-
public List<News>queryNewsByTopicId(int topicId);
-
public News queryNewsById(int id);
-
}
//实体类User,News,Topic等暂时省略
-
package com.news.dao.impl;
-
-
import java.util.ArrayList;
-
import java.util.List;
-
import java.sql.*;
-
-
import com.news.dao.UserDao;
-
import com.news.entity.User;
-
import com.news.util.DaoFactory;
-
-
public class UserDaoImpl implements UserDao{
-
@Override
-
public List<User> getAllUser() {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
List<User>users=null;//利用泛型来创建users集合
-
String sql="select * from t_user";
-
//获得数据库的代码
-
try {
-
con=DaoFactory.getConnection();//贾琏
-
st=con.createStatement();//语句对象；
-
rs=st.executeQuery(sql);//执行结果;
-
//遍历结果集
-
users=new ArrayList<User>();
-
-
while(rs.next()){
-
//从数据库取出数据，然后再把数据库放入实体对象中;
-
User user=new User();
-
user.setId(rs.getInt("id"));
-
user.setUsername(rs.getString("username"));
-
user.setPwd(rs.getString("pwd"));
-
user.setEmail(rs.getString("email"));
-
user.setHobby(rs.getString("hobby"));
-
user.setAddress(rs.getString("address"));
-
-
users.add(user);
-
}
-
//users=DaoFactory.executeQuery(sql, null);
-
-
} catch (Exception e) {
-
e.printStackTrace();
-
}finally{
-
//操作结束
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return users;//返回user的集合
-
}
-
-
@Override
-
public boolean saveUser(String username, String pwd, String email,
-
String address, String bobby) {
-
Connection con=null;
-
PreparedStatement pstmt=null;
-
boolean flag=false;
-
//String sql="insert into t_user values(null,?,?,?,?,?)";//mysql格式;
-
String sql="insert t_user values(?,?,?,?,?)";
-
-
try {
-
con=DaoFactory.getConnection();
-
pstmt=con.prepareStatement(sql);
-
Object[]params={username,pwd,email,address,bobby};
-
DaoFactory.setParams(pstmt, params);
-
pstmt.executeUpdate();
-
flag=true;//成功则为true;
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(null, pstmt, con);
-
}
-
return flag;
-
}
-
-
@Override
-
public User queryUserByNameAndPwd(String username, String userpwd) {
-
Connection con=null;
-
PreparedStatement pstmt=null;
-
ResultSet rs=null;
-
User user=null;
-
String sql="select * from t_user where username=? and pwd=?";
-
-
try {
-
con=DaoFactory.getConnection();
-
pstmt=con.prepareStatement(sql);
-
Object[]params={username,userpwd};
-
DaoFactory.setParams(pstmt, params);
-
rs=pstmt.executeQuery();
-
while(rs.next()){
-
user=new User();
-
user.setId(rs.getInt("id"));
-
user.setUsername(rs.getString("username"));
-
user.setPwd(rs.getString("pwd"));
-
user.setEmail(rs.getString("email"));
-
user.setHobby(rs.getString("hobby"));
-
user.setAddress(rs.getString("address"));
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, pstmt, con);
-
}
-
return user;
-
}
-
-
/* 获得user的总个数,即要分页的总记录数
-
* @see com.news.dao.UserDao#getCount()
-
*/
-
@Override
-
public int getCount() {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
int count=0;
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
String sql="select count(*) from t_user";
-
rs=st.executeQuery(sql);
-
if(rs.next()){
-
count=rs.getInt(1);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return count;
-
}
-
-
@Override
-
public List<User> queryUserByPage(int currentPage, int pageSize) {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
List<User>users=null;
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
users=new ArrayList<User>();
-
String sql="select top "+pageSize+" * from t_user where id not in (select top "+(currentPage-1)*pageSize+" id from t_user order by id)order by id";
-
rs=st.executeQuery(sql);
-
//遍历结果集
-
while(rs.next()){
-
//从数据库中取出一条记录后，放到实体对象中;
-
User user=new User();
-
user.setId(rs.getInt("id"));
-
user.setUsername(rs.getString("username"));
-
user.setEmail(rs.getString("email"));
-
user.setAddress(rs.getString("address"));
-
user.setHobby(rs.getString("hobby"));
-
//将实体对象放入集合中;
-
users.add(user);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return users;
-
}
-
-
}
-
package com.news.dao.impl;
-
-
import java.sql.*;
-
import java.util.*;
-
-
import com.news.dao.TopicDao;
-
import com.news.entity.Topic;
-
import com.news.util.DaoFactory;
-
-
public class TopicDaoImpl implements TopicDao {
-
-
@Override
-
public List<Topic> getAllTopic(){
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
String sql="select * from t_topic";
-
List<Topic>topices=new ArrayList<Topic>();
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
rs=st.executeQuery(sql);
-
while(rs.next()){
-
Topic topic=new Topic();
-
topic.setId(rs.getInt("id"));
-
topic.setTname(rs.getString("tname"));
-
topic.setCreatetime(rs.getDate("createtime"));
-
-
topices.add(topic);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return topices;
-
}
-
-
@Override
-
public int addTopic(String tname) {
-
Connection con=null;
-
PreparedStatement pstmt=null;
-
String sql="insert t_topic values(?,getDate())";
-
int result=0;
-
-
try {
-
con=DaoFactory.getConnection();
-
pstmt=con.prepareStatement(sql);
-
Object[]params={tname};
-
DaoFactory.setParams(pstmt, params);
-
result=pstmt.executeUpdate();
-
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(null, pstmt, con);
-
}
-
return result;
-
}
-
-
/* 需要注意，存在关联删除的情况，如何报错！本处没有做处理!
-
* @see com.news.dao.TopicDao#deleteTopicById(int)
-
*/
-
@Override
-
public boolean deleteTopicById(int id) {
-
Connection con=null;
-
Statement st=null;
-
boolean flag=false;
-
String sql="delete from t_topic where id="+id;
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
st.executeUpdate(sql);
-
flag=true;
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(null, st, con);
-
}
-
return flag;
-
}
-
-
@Override
-
public Topic queryTopicById(int id) {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
Topic topic=null;
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
String sql="select * FROM t_topic where id="+id;
-
rs=st.executeQuery(sql);
-
while(rs.next()){
-
topic=new Topic();
-
topic.setId(rs.getInt("id"));
-
topic.setTname(rs.getString("tname"));
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return topic;
-
}
-
-
@Override
-
public int updateTopicById(int id, String tname) {
-
Connection con=null;
-
PreparedStatement pstmt=null;
-
int result=0;
-
String sql="update t_topic set tname=? where id=?";
-
-
try {
-
con=DaoFactory.getConnection();
-
pstmt=con.prepareStatement(sql);
-
Object[]params={tname,id};
-
DaoFactory.setParams(pstmt, params);
-
result=pstmt.executeUpdate();
-
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(null, pstmt, con);
-
}
-
return result;
-
}
-
}
-
package com.news.dao.impl;
-
-
import java.util.*;
-
import java.util.Date;
-
import java.sql.*;
-
-
import com.news.dao.NewsDao;
-
import com.news.entity.News;
-
import com.news.util.DaoFactory;
-
-
/**
-
* NewsDao的实现类，可以对新闻实现查询;
-
* @author Administrator
-
*
-
*/
-
-
public class NewsDaoImpl implements NewsDao {
-
-
@Override
-
public List<News> queryNews() {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
List<News>newses=new ArrayList<News>();
-
String sql="select * from t_news";
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
rs=st.executeQuery(sql);
-
while(rs.next()){
-
News news=new News();
-
news.setId(rs.getInt("id"));
-
news.setTitle(rs.getString("title"));
-
news.setAuthor(rs.getString("author"));
-
news.setCreatetime(rs.getDate("createtime"));
-
news.setModifyTime(rs.getDate("modifytime"));
-
news.setNcontent(rs.getString("ncontent"));
-
news.setSummary(rs.getString("summary"));
-
news.setTid(rs.getInt("tid"));
-
-
newses.add(news);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return newses;
-
}
-
-
@Override
-
public int addNews(News news) {
-
Connection con=null;
-
PreparedStatement pstmt=null;
-
int result=0;
-
String sql="insert t_news values(?,?,?,getdate(),?,getdate(),?,null)";
-
-
try {
-
con=DaoFactory.getConnection();
-
pstmt=con.prepareStatement(sql);
-
Object[]params={news.getTid(),news.getTitle(),news.getAuthor(),news.getNcontent(),news.getSummary()};
-
DaoFactory.setParams(pstmt, params);
-
result=pstmt.executeUpdate();
-
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(null, pstmt, con);
-
}
-
return result;
-
}
-
-
@Override
-
public List<News> queryNewsByTopicId(int topicId) {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
List<News>newes=null;
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
newes=new ArrayList<News>();
-
//根据主题id进行查询;
-
String sql="select top 5 * from t_news where tid="+topicId+" order by createtime desc";
-
rs=st.executeQuery(sql);
-
while(rs.next()){
-
//从数据库取出一条记录后,然后把数据放入实体对象中;
-
News news=new News();
-
news.setId(rs.getInt("id"));
-
news.setTitle(rs.getString("title"));
-
news.setAuthor(rs.getString("author"));
-
//放入集合中;
-
newes.add(news);
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return newes;
-
}
-
-
@Override
-
public News queryNewsById(int id) {
-
Connection con=null;
-
Statement st=null;
-
ResultSet rs=null;
-
News news=null;
-
-
try {
-
con=DaoFactory.getConnection();
-
st=con.createStatement();
-
String sql="select * from t_news where id="+id;
-
rs=st.executeQuery(sql);
-
while(rs.next()){
-
news=new News();
-
news.setId(rs.getInt("id"));
-
news.setTitle(rs.getString("title"));
-
news.setNcontent(rs.getString("ncontent"));
-
news.setAuthor(rs.getString("author"));
-
news.setCreatetime(rs.getDate("createtime"));
-
}
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}finally{
-
DaoFactory.closeAll(rs, st, con);
-
}
-
return news;
-
}
-
}
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/53044405
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）