PreparedStatement接口学习笔记

 

     PreparedStatement接口继承Statemet接口,通过调用Connection对象的prepareStatement()方法来得到PreparedStatement对象,使用预编译的SQL语句来创建表及向表中中插入数据...

 

1, 编写CreateAccountServlet.java

 

 

 

package cn.apache.pl;

 

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

 

public class CreateAccountServlet extends HttpServlet

{

    private String url;

    private String user;

    private String password;

   

    public void init() throws ServletException

    {

       ServletContext sc = getServletContext();

       String driverClass = sc.getInitParameter("driverClass");

       url = sc.getInitParameter("url");

       user = sc.getInitParameter("user");

       password = sc.getInitParameter("password");

      

       try

       {

           Class.forName(driverClass);

       }catch(ClassNotFoundException ce)

       {

           throw new UnavailableException("加载数据库驱动失败");

       }

    }

   

    public void doGet(HttpServletRequest req, HttpServletResponse resp)

       throws ServletException, IOException

    {

       Connection conn = null;

       Statement stmt = null;

       PreparedStatement pstmt = null;

      

       try

       {

           conn = DriverManager.getConnection(url, user, password);

           stmt = conn.createStatement();

          

           /*

            *调用Statement对象的executeUpdate()方法新建一个帐户表account,

            *表中有两个字段:userid,可变字符串型,balance,单精度浮点型,

            */

           stmt.executeUpdate("create table account(userid VARCHAR(10) not null primary key, balance FLOAT(6,2)) ENGINE = InnoDB");

          

           //调用Connection对象的prepareStatement()方法创建一个PreparedStatement对象,

           pstmt = conn.prepareStatement("insert account values(?,?)");

          

           /*

            *向表中插入两行数据

            *第1个参数是索引,第2个是值

            */

           pstmt.setString(1,"彭磊");

           pstmt.setFloat(2,500.00f);

           pstmt.executeUpdate();

          

           pstmt.setString(1,"徐璐");

           pstmt.setFloat(2,1000.50f);

           pstmt.executeUpdate();

          

           //插入数据成功则输出"Success"

           PrintWriter out = resp.getWriter();

           out.println("Success");

           out.close();

       }catch(SQLException se)

       {

           se.printStackTrace();

       }finally

       {

           if(stmt != null)

           {

              try

              {

                  stmt.close();

              }catch(SQLException se)

              {

                  se.printStackTrace();

              }

              stmt = null;

           }

          

           if(pstmt != null)

           {

              try

              {

                  pstmt.close();

              }catch(SQLException se)

              {

                  se.printStackTrace();

              }

              pstmt = null;

           }

          

           if(conn != null)

           {

              try

              {

                  conn.close();

              }catch(SQLException se)

              {

                  se.printStackTrace();

              }

              conn = null;

           }

       }

    }     

}

 

 

 

2,部署Servlet:

 

 

 

    CreateAccountServlet

    cn.apache.pl.CreateAccountServlet

 

    CreateAccountServlet

    /account

 

 

 

3,运行CreateAccountServlet

启动Tomact服务器,在浏览器中打开:

页面输出:Success,表明account表创建成功!

 

mysql> select * from account;

+--------+---------+

| userid | balance |

+--------+---------+

| 彭磊   |  500.00 |

| 徐璐   | 1000.50 |

+--------+---------+

2 rows in set

 

                                                         仙尘于08年3月15日