JDBC操作数据库

一、数据库操作

需求：从数据库读取数据显示到JSP页面

 

建立数据库表并操作：

create database NEWS;

use NEWS;

createtable news(

    newsIdint(50) primarykey,

    titlevarchar(100) ,

    messagevarchar(100),

    isActivebit(1)

);

二、代码编辑

1．创建实体类

package com.cjw.entity;

import java.io.Serializable;

publicclass News implements Serializable{

    privateintnewsID;

    private String title;

    private String message;

    privatebooleanisActive;

    publicint getNewsID() {

       returnnewsID;

    }

    publicvoid setNewsID(int newsID) {

       this.newsID = newsID;

    }

    public String getTitle() {

       returntitle;

    }

    publicvoid setTitle(String title) {

       this.title = title;

    }

    public String getMessage() {

       returnmessage;

    }

    publicvoid setMessage(String message) {

       this.message = message;

    }

    publicboolean isActive() {

       returnisActive;

    }

    publicvoid setActive(boolean isActive) {

       this.isActive = isActive;

    }

   

    public News() {

       super();

    }

    public News(int newsID, String title, String message) {

       super();

       this.newsID = newsID;

       this.title = title;

       this.message = message;

    }

    public News(int newsID, String title, String message, boolean isActive) {

       super();

       this.newsID = newsID;

       this.title = title;

       this.message = message;

       this.isActive = isActive;

    }

}

2.创建工具类

2.1 MyConfig.java

package com.cjw.utils;

publicinterface MyConfig {

    String URL="jdbc:mysql://127.0.0.1/news?useUnicode=true&characterEncoding=utf8";

    String URLNAME="root";

    String URLPASS="root";

}

2.2 MyHelp.java

package com.cjw.utils;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

publicclass MyHelper {

    static{

       try {

            Class.forName("com.mysql.jdbc.Driver");

       } catch (Exception e) {

           // TODO: handleexception

           e.printStackTrace();

       }

    }

    publicstatic Connection getCon() throws SQLException{

       Connection con=DriverManager.getConnection(MyConfig.URL,MyConfig.URLNAME,MyConfig.URLPASS);

       return con;

      

    }

    //释放

    publicstaticvoid releaseCon(Connection con) throws SQLException{

       con.close();

      

    }

    publicstaticvoid releaseCon(PreparedStatement pre,Connection con) throws SQLException{

       try {

           pre.close();

       } catch (SQLException e) {

           // TODO Auto-generatedcatch block

           e.printStackTrace();

       }finally{

           releaseCon(con);

       }

    }

    publicstaticvoid releaseCon(ResultSet re,PreparedStatement pre,Connectioncon) throws SQLException{

       try {

           re.close();

       } catch (SQLException e) {

           // TODO Auto-generatedcatch block

           releaseCon(pre, con);

       }

    }

}

3.创建Dao

3.1 BaseDao.java

package com.cjw.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import com.cjw.utils.MyHelper;

publicabstractclass BaseDao<T> {

   

    private Connection  queryCon;

    private PreparedStatement queryPre;

    private ResultSet querySet;

   

    publicabstractint insert(T obj);

    publicabstractint delete(int id);

    publicabstractint update(T obj);

    publicabstract T findById(int id) throws SQLException;

    publicabstract List<T> findAll() throws SQLException;

   

    public Connection getCon() throws SQLException{

       return MyHelper.getCon();

      

    }

    publicint update(String sql,Object[]objs) throws SQLException{

       Connection con=getCon();

       PreparedStatement pre=con.prepareStatement(sql);

       if(objs!=null){

           for (int i = 1; i < objs.length; i++) {

              pre.setObject(i, objs[i-1]);

             

           }

       }

       int result=pre.executeUpdate();

       MyHelper.releaseCon(pre, con);

       return result;

      

    }

    publicvoid realse() throws SQLException{

       if(querySet!=null){

            MyHelper.releaseCon(querySet, queryPre, queryCon);

       }else{

            MyHelper.releaseCon(querySet, queryPre, queryCon);

       }

      

    }

    public ResultSet executeQuery(String sql) throws SQLException{

       queryCon=getCon();

       PreparedStatement pre=queryCon.prepareStatement(sql);

       querySet=pre.executeQuery();

      

       returnquerySet;

    }

}

3.2 NewsDao.java

package com.cjw.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.cjw.entity.News;

import com.cjw.utils.MyHelper;

 

publicclass NewsDao extends BaseDao<News> {

 

    @Override

    publicint delete(int id) {

       return 0;

    }

 

    @Override

    public List<News> findAll() throws SQLException {

       String sql="select *from news";

       ResultSet re= executeQuery(sql);

       List<News> list=new ArrayList<News>();

       while(re.next()){

           News news=new News(re.getInt(1),re.getString(2),re.getString(3));

           list.add(news);

      

       }

       realse();

       return list;

    }

 

    @Override

    public News findById(int id) throws SQLException {

       String sql="select *from news where id=?";

       Connection con=getCon();

       PreparedStatement pre=con.prepareStatement(sql);

       pre.setInt(1, id);

       ResultSet re=pre.executeQuery();

       News news=null;

       if(re.next()){

           news=new News(re.getInt(1),re.getString(2), re.getString(3));

          

       }

       MyHelper.releaseCon(re, pre, con);

       return news;

    }

 

    @Override

    publicint insert(News obj) {

       return 0;

    }

 

    @Override

    publicint update(News obj) {

       return 0;

    }

}

4.创建业务逻辑类service

package com.cjw.service;

import java.sql.SQLException;

import java.util.List;

import com.cjw.dao.NewsDao;

import com.cjw.entity.News;

publicclass NewsService {

    public News findById(int id) throws SQLException{

       returnnew NewsDao().findById(id);    

    }

    public List<News> findAll() throws SQLException{

       returnnew NewsDao().findAll();

    }

}

5.创建jsp视图页面

<!DOCTYPE html>

<%@page import="java.util.List"%>

<%@ page language="java"contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<%@page import="com.cjw.entity.News"%>

<%@page import="com.cjw.service.NewsService"%>

<%@ taglib prefix="s"uri="/struts-tags"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core"prefix="c"%>

<html>

    <head>

       <title>新闻</title>

    </head>

    <body>

       <%  List<News> list=newNewsService().findAll();

        request.setAttribute("list", list);

        %>

       <table border="1"align="center" width="660px"bordercolor="#0000CC" cellspacing="0">

           <c:forEach items="${list }"var="str" varStatus="abc">

              <c:if test="${abc.index%2==0}">

                  <tr height="35px">

               </c:if>

              <td>

                  ${str.title }

              </td>

 

              <c:if test="${abc.index%1==0 }">

              </c:if>

           </c:forEach>

       </table>

    </body>

</html>