对jsp的一个小结（3）使用JDBC操作数据库

6使用JDBC操作数据库

-----新闻分类表， 有外键存在，因此先删除子表drop table NEWS_COMMENT;drop table NEWS_DETAIL;drop table NEWS_CATEGORY;create table NEWS_CATEGORY(id              NUMBER(10,0)     NOT NULL PRIMARY KEY,name            varchar2(50)     NOT NULL,createdate      Date             NOT　NULL ---创建时间);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(1,'国内',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(2,'国际',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(3,'娱乐',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(4,'军事',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(5,'财经',sysdate); INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(6,'天气',sysdate); -----新闻明细表create table NEWS_DETAIL(id               number(10,0)    NOT NULL  PRIMARY KEY,    --idcategoryId       number(10,0)    NOT NULL,                 --新闻类别idtitle      varchar2(100)   NOT NULL,--新闻标题summary          varchar2(255)   NULL,                   --新闻摘要content          CLOB   NULL,                           --新闻内容picpath          varchar2(255)   NULL,                  --新闻图片路径author      varchar2(50)    NULL,--发表者createdate       date            NULL,                  --创建时间modifydate       date            NULL,                  --修改时间Foreign key(categoryId) references NEWS_CATEGORY(id));INSERT INTO NEWS_DETAIL VALUES(1,1,'尼日利亚一架客机坠毁','尼日利亚一架客机坠毁，伤亡惨重','尼日利亚一架客机坠毁，伤亡惨重,10人重伤','','admin',sysdate,sysdate);-----新闻评论表create table NEWS_COMMENT(id               number(10,0)    PRIMARY KEY,            --idnewsId           number(10,0)    NOT NULL,               --评论新闻idcontent          varchar2(2000),                         --评论内容author           varchar2(50),                          --评论者ip               varchar2(15),                          --评论ipcreatedate       date,                                  --发表时间Foreign key(newsId) references NEWS_DETAIL(id));SELECT * FROM news_detail;DELETE FROM news_detail WHERE ID=2

1.解决问题

2.使用JDBC查询新闻信息

package com.pb.news.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Timestamp;public class NewsDao {// 查询新闻信息public void getNewsList(){Connection connection=null;Statement stmt=null;ResultSet rs=null;try {//（1）Class.forName()加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");//（2）DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:news","scott","tiger");//（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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{//释放资源try {rs.close();stmt.close();connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}//测试public static void main(String[] args) {NewsDao newsDao=new NewsDao();newsDao.getNewsList();}}

3使用配置文件保存数据库信息

database.properties配置文件

jdbc.driver_class=oracle.jdbc.driver.OracleDriverjdbc.connection.url=jdbc:oracle:thin:@localhost:1521:newsjdbc.connection.username=scottjdbc.connection.password=tiger

工具类

package com.pb.news.util;import java.io.IOException;import java.io.InputStream;import java.util.Properties;//读取配置文件（属性文件）的工具类public class ConfigManager {private static ConfigManager configManager;//properties.load(InputStream);读取属性文件private static Properties properties; private ConfigManager(){String configFile="database.properties";properties=new Properties();InputStream in=ConfigManager.class.getClassLoader().getResourceAsStream(configFile);try {properties.load(in);in.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static ConfigManager getInstance(){if(configManager==null){configManager=new ConfigManager();}return configManager;}public String getString(String key){return properties.getProperty(key);}}

newsdao

package com.pb.news.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Timestamp;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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{//释放资源try {rs.close();stmt.close();connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}//测试public static void main(String[] args) {NewsDao newsDao=new NewsDao();newsDao.getNewsList();}}

4使用JDBC增、删、改新闻信息

// 增加新闻信息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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{//释放资源try {pstmt.close();connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}//测试public static void main(String[] args) {NewsDao newsDao=new NewsDao();newsDao.add(3, 1, "快女选秀快讯", "快女比赛正进入白热化", "她、她、她，谁是你心中的快女冠军？",new Date());newsDao.getNewsList();}

5编写通用DAO数据访问对象(Data Access Object)

①创建DAO数据访问对象接口

package com.pb.news.dao;import java.util.Date;public interface NewsDao {// 查询新闻信息public void getNewsList();// 增加新闻信息public void add(int id, int categoryId, String title, String summary,String content, Date createdate) ;// 删除新闻信息public void delete(int id) ;// 修改新闻标题信息public void update(int id, String title) ;}

②创建基类：数据库操作通用类

package com.pb.news.dao;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 com.pb.news.util.ConfigManager;//基类：数据库操作通用类public class BaseDao {protected Connection conn;protected PreparedStatement ps;protected Statement stmt;protected ResultSet rs;// 获取数据库连接public boolean getConnection() {// 读出配置信息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");// 加载JDBC驱动try {Class.forName(driver);// 与数据库建立连接conn = DriverManager.getConnection(url, username, password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}return true;}// 增删改 delete from news_detail where id=? and title=?public int executeUpdate(String sql, Object[] params) {int updateRows = 0;getConnection();try {ps=conn.prepareStatement(sql);//填充占位符for(int i=0;i<params.length;i++){ps.setObject(i+1, params[i]);}updateRows=ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();}return updateRows;}// 查询public ResultSet executeSQL(String sql,Object[] params) {getConnection();try {ps=conn.prepareStatement(sql);//填充占位符for(int i=0;i<params.length;i++){ps.setObject(i+1, params[i]);}rs=ps.executeQuery();} catch (SQLException e) {e.printStackTrace();}return rs;}// 关闭资源public boolean closeResource() {if(rs!=null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}}if(ps!=null){try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}}if(stmt!=null){try {stmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}}return true;}}

③创建实现类 继承基类：数据库操作通用类实现DAO数据访问对象接口

package com.pb.news.dao.impl;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.dao.BaseDao;import com.pb.news.dao.NewsDao;import com.pb.news.util.ConfigManager;public class NewsDaoImpl extends BaseDao implements NewsDao {// 查询新闻信息public void getNewsList(){try {//（3）获得Statement对象，执行SQL语句String sql="select * from news_detail";Object[] params={};ResultSet rs=this.executeSQL(sql, params);//（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 (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{//释放资源this.closeResource();}}// 增加新闻信息public void add(int id, int categoryId, String title, String summary,String content, Date createdate) {try {String sql="insert into news_detail(id,categoryId,title,summary,content,createdate) values(?,?,?,?,?,?)";Object[] params={id,categoryId,title,summary,content, new java.sql.Timestamp(createdate.getTime())};int i=this.executeUpdate(sql, params);//（4）处理执行结果if(i>0){System.out.println("插入新闻成功！");}}finally{//释放资源this.closeResource();}}// 删除新闻信息public void delete(int id) {try {String sql = "delete from  news_Detail where id=?";Object[] params={id};int i=this.executeUpdate(sql, params);//（4）处理执行结果if(i>0){System.out.println("删除新闻成功！");}}  finally {//释放资源this.closeResource();}}// 修改新闻标题信息public void update(int id, String title) {try {String sql = "update  news_Detail set title=? where id=?";Object[] params={title,id};int i=this.executeUpdate(sql, params);//（4）处理执行结果if(i>0){System.out.println("修改新闻成功！");}}  finally {//释放资源this.closeResource();}}//测试public static void main(String[] args) {NewsDaoImpl newsDao=new NewsDaoImpl();/*newsDao.add(3, 1, "快女选秀快讯", "快女比赛正进入白热化", "她、她、她，谁是你心中的快女冠军？",new Date());*///newsDao.update(3, "快女选秀快讯速递");//newsDao.delete(3);newsDao.getNewsList();}}

6Tomcat配置数据源

1、Tomcat中加入数据库驱动文件2、配置Tomcat的conf/context.xml<Resource name="jdbc/news"               auth="Container"  type="javax.sql.DataSource"  maxActive="100"               maxIdle="30" maxWait="10000" username="scott"  password="tiger"               driverClassName="oracle.jdbc.OracleDriver"               <span style="color:#ff0000;">url="jdbc:oracle:thin:@localhost:1521:news"</span>/>3、配置应用程序的web.xml文件（可选）<resource-ref><res-ref-name>jdbc/news</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>4、使用JNDI获取连接对象

// 获取数据库连接public Connection getConnection2() {try {//初始化上下文<span style="color:#ff6666;">Context cxt=new InitialContext();</span>//获取与逻辑名相关联的数据源对象<span style="color:#ff0000;">DataSource ds=(DataSource)cxt.lookup("java:comp/env/jdbc/news");</span>conn=ds.getConnection();} catch (NamingException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}