JDBC之數據連接_ZenDei技術網路在線

1:資料庫語句： create database LandDB; use LandDB; create table T_BL_CANTON_CODE ( CTN_CODE int primary key, CTN_NAME varchar(60) not null, GOV_NAME varchar ...

1:資料庫語句：
create database LandDB;
use LandDB;
create table T_BL_CANTON_CODE
(
    CTN_CODE int primary key,
    CTN_NAME varchar(60) not null,
    GOV_NAME varchar(60) not null,
    LAND_DP_NAME varchar(60) not null,
    PARENT_CODE int not null
);INSERT INTO `landdb`.`t_bl_canton_code`
(`CTN_CODE`,
`CTN_NAME`,
`GOV_NAME`,
`LAND_DP_NAME`,
`PARENT_CODE`)
VALUES(12000,'長沙市','長沙市人民政府','長沙市國土資源廳','HN');INSERT INTO `landdb`.`t_bl_canton_code`
(`CTN_CODE`,
`CTN_NAME`,
`GOV_NAME`,
`LAND_DP_NAME`,
`PARENT_CODE`)
VALUES(11000,'北京市','北京市人民政府','北京市國土資源廳','BN');

2：將提供的Images,css文件夾拷貝至建立的網站根目錄下，也就是下項目的webroot目錄中。

３：改寫素材文件夾下所有ｈｔｍｌ　文件為ｊｓｐ文件到項目的webroot目錄下。替換校正原來的鏈接，因為素材文件原來鏈接指向的是ＨＴＭＬ文件，現在更名為了ＪＳＰ文件，需要把原來指向的ＨＴＭＬ改為ＪＳＰ。

４：按照工程開發的需要，建立命名空間，一般應該有以下命名空間：
放置實體類的空間：entity或者model
放置過濾器的空間:filter
放置數據訪問層的空間：dao
放置servlet的空間servlet

5:建立實體類：Canton，放置到entity命名空間下。
代碼如下：
package com.laozhu.entity;

public class Canton {
    private int ctn_code;    //行政區代碼
    private String ctn_name;    //行政區名稱
    private String gov_name;    //政府名稱
    private String land_dp_name;    //國土部門名稱
    private int parent_code;    //上級行政區代碼
    
    public int getCtn_code() {
        return ctn_code;
    }
    public void setCtn_code(int ctn_code) {
        this.ctn_code = ctn_code;
    }
    public String getCtn_name() {
        return ctn_name;
    }
    public void setCtn_name(String ctn_name) {
        this.ctn_name = ctn_name;
    }
    public String getGov_name() {
        return gov_name;
    }
    public void setGov_name(String gov_name) {
        this.gov_name = gov_name;
    }
    public String getLand_dp_name() {
        return land_dp_name;
    }
    public void setLand_dp_name(String land_dp_name) {
        this.land_dp_name = land_dp_name;
    }
    public int getParent_code() {
        return parent_code;
    }
    public void setParent_code(int parent_code) {
        this.parent_code = parent_code;
    }
}
6：建立資料庫連接類，過程如下：
A:將ＭＹＳＱＬ驅動如mysql-connector-java-5.1.18.jar複製到項目中，找到該文件後右鍵--build Path --add build path,從而將該驅動包引入到工程中。
B:確保資料庫伺服器Mysql已經啟動而且存在資料庫landb並且存在表t_bl_canton_code,該表結果正確。
C:建立資料庫連接類，如BuildConn類，代碼中的方法openconn()返回一個資料庫的連接Connection.


7:創建字元集過濾器以便過濾字元集。
創建命名空間Filter，在該空間下新建類CharsetFilter,在新建時在介面選項Interfaces中添加Javax.Servlet.Filter介面，這樣系統會自動引用相應的介面並且形成相應的方法。
自動形成的代碼中有以下代碼：
public void doFilter(ServletRequest arg0, ServletResponse arg1,
            FilterChain arg2) throws IOException, ServletException {
        // TODO Auto-generated method stub

    }
這就是形成的過濾器的方法，改寫該方法;代碼如下：

 arg0.setCharacterEncoding(encoding);
         arg1.setContentType("text/html;charset="+encoding);
         arg2.doFilter(arg0, arg1);
配置過濾器：代碼寫好以後打開web.xml，在</web-app>前添加類似以下代碼：
 <filter>
  <filter-name>CharSetfilter</filter-name>  //過濾器名
  <filter-class>com.laozhu.filter.Charfilter</filter-class>  //過濾器類
    <init-param>
  <param-name>encoding</param-name>  //定義參數encoding
  <param-value>UTF-8</param-value>
  </init-param>
  </filter>
  <filter-mapping>   //定義映射。
  <filter-name>CharSetfilter</filter-name> 
  <url-pattern>/*</url-pattern>  //映射到目錄中的所有文件。也就是針對所有的文件進行過濾。
  </filter-mapping>

7：新建資料庫訪問類：CantonDaoImp，該類實現能對錶的增刪改查操作，完成對資料庫的操作。代碼參考例子。

8：新建一個servlet CantonAddServlet，用於執行資料庫的插入。也就是新增數據。代碼見參考代碼。請註意web.xml中針對此servlet的映射路徑。

9：打開addCanton.jsp，首先修改執行的頁面，也就是代碼：<form action="" 改成：<form action="CantonAddServlet" ，註意 CantonAddServlet是你映射的路徑。
然後在文件中將參數名與表單中的欄位名一一對應。如在CantonAddServlet中的：
int ctn_code = Integer.parseInt(request.getParameter("ctn_code"));
這是要獲取ctn_code，就將addCanton.jsp中的城市代碼表單的文本框欄位名設置為：ctn_code，也就是將原來的代碼修改為：<td width="132" height="9"><span class="style2"> 行政區代碼</span></td>
      <td width="239"><input name="ctn_code" type="text" id="ctn_code" size="20">
其他的表單輸入框一樣處理。

10：可以調試增加記錄了。

11：在servlet命名空間增加servlet ，名字為CantonListServlet,代碼參考代碼包。

12：修改listCanton.jsp。代碼參考項目代碼。

13：修改index_tree1.jsp，將原來的鏈接修改為CantonListServlet<A href="CantonListServlet" target="main1">行政區劃</A>

14：測試。

步驟

佈局圖：

MySQL：

package com.caiduping.Dao;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class BuildConn {

public Connection openconn() 
{Connection conn=null;

    try {
        //載入Mysql驅動
        Class.forName("com.mysql.jdbc.Driver");  
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    String URL="jdbc:mysql://localhost:3306/landdb";
    String username="user2";
    String userpassword="123456";
    try {
        //建立連接
        conn=DriverManager.getConnection(URL, username, userpassword);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

return conn;
}
}

BuildConn類

package com.caiduping.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.caiduping.Dao.BuildConn;
import com.caiduping.entity.Canton;
public class CantonDaoImp {
    
    //addCanton方法實現插入數據
    public boolean addCanton(Canton canton) throws SQLException {
        //設置標識
        boolean flag = false;
        //執行的sql語句，？為參數
        String sql = "insert into T_BL_CANTON_CODE values(?,?,?,?,?)";
         //定義連接對象
        Connection con = null; 
        //定義命令對象
        PreparedStatement prst = null;  
        //實例化BuildConn類
        BuildConn bd=new BuildConn();  
        //調用BuildConn的openconn方法返回資料庫的鏈接
        con = bd.openconn();  
        try {
             //準備執行sql命令
            prst = con.prepareStatement(sql); 
            //分別對應設置sql語句中的參數，應該在數據類型與參數一一對應
            prst.setInt(1, canton.getCtn_code());  
            prst.setString(2, canton.getCtn_name());
            prst.setString(3, canton.getGov_name());
            prst.setString(4, canton.getLand_dp_name());
            prst.setString(5, canton.getParent_code());
            if(prst.executeUpdate()>0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            
        }con.close();
        return flag;
    }

    public boolean deleteCanton(int id) {
        // TODO Auto-generated method stub
        return false;
    }

    public Canton findCanton(int id) {
        // TODO Auto-generated method stub
        return null;
    }
    //以下方法返回Canton列表
    public List<Canton> listCanton() {
        List<Canton> list = new ArrayList<Canton>();  
        String sql = "select * from T_BL_CANTON_CODE";
        Connection con = null;
        PreparedStatement prst = null;
        ResultSet rest = null;
        //實例化BuildConn類
        BuildConn bd=new BuildConn();  
         //調用BuildConn的openconn方法返回資料庫的鏈接
        con = bd.openconn(); 
        try {
            prst = con.prepareStatement(sql);
            //結果集對象rest
            rest = prst.executeQuery();  
            while(rest.next()) {
                //定義一個canton對象
                Canton canton = new Canton();  
                canton.setCtn_code(rest.getInt("ctn_code"));
                canton.setCtn_name(rest.getString("ctn_name"));
                canton.setGov_name(rest.getString("gov_name"));
                canton.setLand_dp_name(rest.getString("land_dp_name"));
                canton.setParent_code(rest.getString("parent_code"));
                //讀出的該對象加入到list列表中
                list.add(canton);  
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //返回列表    
        return list; 
    }

    public boolean updateCanton(Canton canton) {
        // TODO Auto-generated method stub
        return false;
    }
    public int delCanton(long cantonID) throws SQLException
    {int a=0;
    Connection con = null;
    PreparedStatement prst = null;
    //實例化BuildConn類
    BuildConn bd=new BuildConn();  
    con = bd.openconn();
    String sql="delete from t_bl_canton_code where CTN_CODE =" + cantonID;
    Statement st=con.createStatement();
    a=st.executeUpdate(sql);
    st.close();
    con.close();
    return a;
    }
    public int delCanton(Canton canton) throws SQLException
    {int a=0;
    Connection con = null;
    PreparedStatement prst = null;
    //實例化BuildConn類
    BuildConn bd=new BuildConn();  
    con = bd.openconn();
    String sql="delete from t_bl_canton_code where CTN_CODE =" + canton.getCtn_code();
    Statement st=con.createStatement();
    a=st.executeUpdate(sql);
    st.close();
    con.close();
    return a;
    }
}

CantonDaoImp類

package com.caiduping.entity;

public class Canton {
    //行政區代碼
    private int ctn_code;    
    //行政區名稱
    private String ctn_name;    
    //政府名稱
    private String gov_name;    
    //國土部門名稱
    private String land_dp_name;    
    //上級行政區代碼
    private String parent_code;    
    
    public int getCtn_code() {
        return ctn_code;
    }
    public void setCtn_code(int ctn_code) {
        this.ctn_code = ctn_code;
    }
    public String getCtn_name() {
        return ctn_name;
    }
    public void setCtn_name(String ctn_name) {
        this.ctn_name = ctn_name;
    }
    public String getGov_name() {
        return gov_name;
    }
    public void setGov_name(String gov_name) {
        this.gov_name = gov_name;
    }
    public String getLand_dp_name() {
        return land_dp_name;
    }
    public void setLand_dp_name(String land_dp_name) {
        this.land_dp_name = land_dp_name;
    }
    public String getParent_code() {
        return parent_code;
    }
    public void setParent_code(String parent_code) {
        this.parent_code = parent_code;
    }
    
}

Canton類

CharsetFilter繼承Filter(javax.servlet.filter方法)：

package com.caiduping.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class CharsetFilter implements Filter {
    //定義編碼方式
    String encoding=null; 
    public void destroy() {
        // TODO Auto-generated method stub

    }

    public void doFilter(ServletRequest arg0, ServletResponse arg1,
            FilterChain arg2) throws IOException, ServletException {
        // TODO Auto-generated method stub
         //設置Request的字元集編碼
        arg0.setCharacterEncoding(encoding); 
        //發送response的編碼
        arg1.setContentType("text/html;charset="+encoding); 
         //傳遞給下一過濾器
        arg2.doFilter(arg0, arg1); 
    }
    //下麵的方法是在過濾器載入時調用，用於初始化過濾器。
    public void init(FilterConfig arg0) throws ServletException {
        // TODO Auto-generated method stub
        //讀取web.xml中的過濾器參數"encoding"
        encoding=arg0.getInitParameter("encoding");  
    }

}

CharsetFilter類

package com.caiduping.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.caiduping.Dao.CantonDaoImp;
import com.caiduping.entity.Canton;
public class CantonAddServlet extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    /**
     * Constructor of the object.
     */
    public CantonAddServlet() {
        super();
    }

    /**
     * Destruction of the servlet. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to get.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
        out.println("<HTML>");
        out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
        out.println("  <BODY>");
        out.print("    This is ");
        out.print(this.getClass());
        out.println(", using the GET method");
        out.println("  </BODY>");
        out.println("</HTML>");
        out.flush();
        out.close();
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to post.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        int ctn_code = Integer.parseInt(request.getParameter("ctn_code"));
        String ctn_name = request.getParameter("ctn_name");
        String gov_name = request.getParameter("gov_name");
        String land_dp_name = request.getParameter("land_dp_name");
        String parent_code = request.getParameter("parent_code");
        Canton canton = new Canton();
        canton.setCtn_code(ctn_code);
        canton.setCtn_name(ctn_name);
        canton.setGov_name(gov_name);
        canton.setLand_dp_name(land_dp_name);
        canton.setParent_code(parent_code);
        CantonDaoImp cantonadd = new CantonDaoImp();
        try {
            if(cantonadd.addCanton(canton)) {
                request.getRequestDispatcher("CantonListServlet").forward(request, response);
            } else {
                System.out.println("fail");
                request.getRequestDispatcher("../index.jsp");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            out.print(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * Initialization of the servlet. <br>
     *
     * @throws ServletException if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}

CantonAddServlet類

package com.caiduping.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.caiduping.Dao.CantonDaoImp;
public class CantonDelServlet extends HttpServlet {

    /**
     * Constructor of the object.
     */
    public CantonDelServlet() {
        super();
    }

    /**
     * Destruction of the servlet. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to get.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        int ctn_code = Integer.parseInt(request.getParameter("ctn_code"));
        CantonDaoImp cdi=new CantonDaoImp();
        try {
            cdi.delCanton(ctn_code);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        request.getRequestDispatcher("CantonListServlet").forward(request, response);
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to post.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        doGet(request,response);
    }

    /**
     * Initialization of the servlet. <br>
     *
     * @throws ServletException if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}

CantonDelServlet類

package com.caiduping.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.caiduping.Dao.CantonDaoImp;
import com.caiduping.entity.Canton;

import java.util.List;
public class CantonListServlet extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    /**
     * 
     */
    

    /**
     * Constructor of the object.
     */
    public CantonListServlet() {
        super();
    }

    /**
     * Destruction of the servlet. <br>
     */
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    /**
     * The doGet method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to get.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

         doPost(request, response);//以便不管提交數據是什麼方式均專一到dopost
    }

    /**
     * The doPost method of the servlet. <br>
     *
     * This method is called when a form has its tag value method equals to post.
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //實例化CantonDaoImp，工程中可以寫一個介面
        CantonDaoImp cantonService = new CantonDaoImp(); 
        //建立一個Canton列表　，調用CantonDaoImp方法返回這個列表
        List<Canton> list = cantonService.listCanton();  
        //放置這個列表到request對象中
        request.setAttribute("list", list);
        //轉向到listCanton.jsp
        request.getRequestDispatcher("listCanton.jsp").forward(request, response);
    }

    /**
     * Initialization of the servlet. <br>
     *
     * @throws ServletException if an error occurs
     */
    public void init() throws ServletException {
        // Put your code here
    }

}

CantonListServlet類

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>
</title>
<style type="text/css">
<!--
.style1 {color: #FF0000}
.style2 {font-size: 14px}
-->
</style>
</head>

<body>
<script language="javascript">
function check(){
if (form1.title.value==""){
  alert("請輸入新聞標題")
  form1.title.focus();
  return false;
 }
if (form1.content.value==""){
  alert("請輸入內容新聞內容");
  form1.content.focus();
  return false;
 }
}
</script>


<table width="397" border="0">
<tr><td width="391" align="left"><form action="CantonAddServlet" method="post" name="form1">
  <table width="378" height="162" border="1" align="left" style="border-collapse:collapse">
    <tr align="left">
      <td height="19" colspan="2"><span class="titletxt">行政區劃信息錄入（以下帶<span class="style1">*</span>為必填項）</span></td>
    </tr>
    <tr>
      <td width="132" height="9"><span class="style2"> 行政區代碼</span></td>
      <td width="239"><input name=