import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import com.pb.news.util.ConfigManager;
public class NewsDao {
// 查询新闻信息
public void getNewsList(){
Connection connection=null;
Statement stmt=null;
ResultSet rs=null;
String driver=ConfigManager.getInstance().getString("jdbc.driver_class");
String url=ConfigManager.getInstance().getString("jdbc.connection.url");
String username=ConfigManager.getInstance().getString("jdbc.connection.username");
String password=ConfigManager.getInstance().getString("jdbc.connection.password");
try {
//(1)Class.forName()加载驱动
Class.forName(driver);
//(2)DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
connection=DriverManager.getConnection(url,username,password);
//(3)获得Statement对象,执行SQL语句
String sql="select * from news_detail";
stmt=connection.createStatement();
rs=stmt.executeQuery(sql);
//(4)处理执行结果(ResultSet),
while(rs.next()){
int id=rs.getInt("id");
String title=rs.getString("title");
String summary=rs.getString("summary");
String content=rs.getString("content");
String author=rs.getString("author");
Timestamp time=rs.getTimestamp("createdate");
System.out.println(id + "\t" + title + "\t" + summary + "\t"+ content + "\t" + author + "\t" + time);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//释放资源
try {
rs.close();
stmt.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 增加新闻信息
public void add(int id, int categoryId, String title, String summary,
String content, Date createdate) {
Connection connection=null;
PreparedStatement pstmt=null;
String driver=ConfigManager.getInstance().getString("jdbc.driver_class");
String url=ConfigManager.getInstance().getString("jdbc.connection.url");
String username=ConfigManager.getInstance().getString("jdbc.connection.username");
String password=ConfigManager.getInstance().getString("jdbc.connection.password");
try {
//(1)Class.forName()加载驱动
Class.forName(driver);
//(2)DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
connection=DriverManager.getConnection(url,username,password);
//(3)获得Statement对象,执行SQL语句
/*String sql="insert into news_detail(id,categoryId,title,summary,content,createdate) values("+id+","+categoryId+","+title+","+")";
stmt=connection.createStatement();
int i=stmt.executeUpdate(sql);*/
String sql="insert into news_detail(id,categoryId,title,summary,content,createdate) values(?,?,?,?,?,?)";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setInt(2, categoryId);
pstmt.setString(3, title);
pstmt.setString(4, summary);
pstmt.setString(5, content);
pstmt.setTimestamp(6, new java.sql.Timestamp(createdate.getTime()));
int i=pstmt.executeUpdate();
//(4)处理执行结果
if(i>0){
System.out.println("插入新闻成功!");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//释放资源
try {
pstmt.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//删除
public void delete(int id) {
Connection connection=null;
PreparedStatement pstmt=null;
String driver=ConfigManager.getInstance().getString("jdbc.driver_class");
String url=ConfigManager.getInstance().getString("jdbc.connection.url");
String username=ConfigManager.getInstance().getString("jdbc.connection.username");
String password=ConfigManager.getInstance().getString("jdbc.connection.password");
try {
//(1)Class.forName()加载驱动
Class.forName(driver);
//(2)DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
connection=DriverManager.getConnection(url,username,password);
//(3)获得Statement对象,执行SQL语句
/*String sql="insert into news_detail(id,categoryId,title,summary,content,createdate) values("+id+","+categoryId+","+title+","+")";
stmt=connection.createStatement();
int i=stmt.executeUpdate(sql);*/
String sql="delete from news_detail where id=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, id);
int i=pstmt.executeUpdate();
//(4)处理执行结果
if(i>0){
System.out.println("删除新闻成功!");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//释放资源
try {
pstmt.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//修改新闻信息
public void update(int id, int categoryId, String title, String summary,
String content, Date createdate,int oldid) {
Connection connection=null;
PreparedStatement pstmt=null;
String driver=ConfigManager.getInstance().getString("jdbc.driver_class");
String url=ConfigManager.getInstance().getString("jdbc.connection.url");
String username=ConfigManager.getInstance().getString("jdbc.connection.username");
String password=ConfigManager.getInstance().getString("jdbc.connection.password");
try {
//(1)Class.forName()加载驱动
Class.forName(driver);
//(2)DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
connection=DriverManager.getConnection(url,username,password);
//(3)获得Statement对象,执行SQL语句
/*String sql="insert into news_detail(id,categoryId,title,summary,content,createdate) values("+id+","+categoryId+","+title+","+")";
stmt=connection.createStatement();
int i=stmt.executeUpdate(sql);*/
String sql="update news_detail set id=?,categoryId=?,title=?,summary=?,content=?,createDate=? where id=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setInt(2, categoryId);
pstmt.setString(3, title);
pstmt.setString(4, summary);
pstmt.setString(5, content);
pstmt.setTimestamp(6, new java.sql.Timestamp(createdate.getTime()));
pstmt.setInt(7, oldid);
int i=pstmt.executeUpdate();
//(4)处理执行结果
if(i>0){
System.out.println("修改新闻成功!");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//释放资源
try {
pstmt.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//测试
public static void main(String[] args) {
NewsDao newsDao=new NewsDao();
newsDao.add(3, 1, "快女选秀快讯", "快女比赛正进入白热化", "她、她、她,谁是你心中的快女冠军?", new Date());
// newsDao.delete(3);
newsDao.update(2, 1, "快女选秀快讯", "快女比赛正进入白热化", "她、她、她,谁是你心中的快女冠军?",new Date(),3);
newsDao.getNewsList();
}
}
使用jdbc实现增删改查示例
最新推荐文章于 2024-07-21 23:33:30 发布