网上书店___使用Servlet通过JDBC操作MySQL数据库

/*

 *用于创建存储图书信息的数据库和表

 */

package cn.apache.pl;

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

public class CreateDBServlet extends HttpServlet

{

    private String url;

    private String user;

    private String password;

    public void init() throws ServletException

    {

       /*

        *调用getInitParameter()方法得到JDBC驱动程序的类名,

        *连接URL,用户名,密码,我们将这些信息放在web.xml文件中配置,

        *如果需要更好数据库或连接信息时,只需要修改web.xml文件即可

        */

       String driverClass = getInitParameter("driverClass");

       url      = getInitParameter("url");

       user     = getInitParameter("user");

       password = getInitParameter("password");

       /*

        *调用Class类的静态方法forName()加载并注册JDBC驱动程序,

        *如果没有找到驱动程序类,抛出UnavailableException异常,

        *通知Servlet容器,该Servlet永久不可用

        */

       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;

       try

       {

           //调用DriverManager类的静态方法getConnection()得到数据库的连接对象

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

           //调用连接对象的createStatement()方法创建一个Statement对象

           stmt = conn.createStatement();

           //利用Statement对象创建bookstore数据库

           stmt.executeUpdate("create database bookstore");

           //user bookstore数据库

           stmt.executeUpdate("use bookstore");

           /*

            *创建bookinfo数据表,用来存储图书信息,新建以下字段:

            *id          图书编号

            *title           书名

            *author      作者

            *bookconcern     出版社

            *publish_date    出版日期

            *price           价格

            *amount      数量

            *remark      备注

            */

            //该条语句最后"ENGINE = InnoDB",用于指定表的存储引擎使用InnoDB

           stmt.execute("create table bookinfo(id INT not null primary key, title VARCHAR(50) not null, author VARCHAR(50) not null, bookconcern VARCHAR(100) not null, publish_date DATE not null, price FLOAT(4,2) not null, amount SMALLINT,remark VARCHAR(200)) ENGINE = InnoDB");

           //调用Statement对象的addBatch()方法将插入数据的SQL语句组成一个命令列表

           stmt.addBatch("insert into bookinfo values(1,'JAVA从入门到精通','孙卫琴','电子工业出版社','2004-6-1',34.00,35,null)");

           stmt.addBatch("insert into bookinfo values(2,'JSP应用开发详解','刘小华','清华大学出版社','2005-10-3',56.00,20,null)");

           stmt.addBatch("insert into bookinfo values(3,'MySQL数据库编程','孙鑫','人民邮电出版社','2006-6-29',78.00,10,null)");

           //调用executeBatch()方法批量执行这些SQL语句

           //大量SQL语句批量执行可以显著提高性能,减少服务器负载

           stmt.executeBatch();

           //如果创建成功,则输出success!

           PrintWriter out = resp.getWriter();

           out.println("success!");

           out.close();

       }catch(SQLException se)

       {

           se.printStackTrace();

       /*在finally语句中依次关闭Statement对象和Connection对象,

        *之所以在finally语句中处理,可以保证不管是正常执行还是执行

        *过程中发生了异常,都会关闭它们.

        */

       }finally

       {

           if(stmt != null)

           {

              try

              {

                  stmt.close();

              }catch(SQLException se)

              {

                  se.printStackTrace();

              }

              stmt = null;

           }

           if(conn != null)

           {

              try

              {

                  conn.close();

              }catch(SQLException se)

              {

                  se.printStackTrace();

              }

              conn = null;

           }

       }

    }

}

字     段

            描                述

id

bookinfo表的主键,整型,唯一标示一本图书

title

字符串类型,书名

author

字符串类型,作者

bookconcern

字符串类型,出版社

public_date

日期类型,出版日期

price

单精度浮点型,价格

amount

整型,库存量

remark

字符串类型,备注

<servlet>

    <servlet-name>CreateDBServlet</servlet-name>

    <servlet-class>cn.apache.pl.CreateDBServlet</servlet-class>

    <init-param>

       <param-name>driverClass</param-name>

       <param-value>com.mysql.jdbc.Driver</param-value>

    </init-param>

    <init-param>

       <param-name>url</param-name>

       <param-value>jdbc:mysql://localhost:3306/mysql</param-value>

    </init-param>

    <init-param>

       <param-name>user</param-name>

       <param-value>root</param-value>

    </init-param>

    <init-param>

       <param-name>password</param-name>

       <param-value>123</param-value>

    </init-param>

</servlet>

<servlet-mapping>

    <servlet-name>CreateDBServlet</servlet-name>

    <url-pattern>/createdb</url-pattern>

</servlet-mapping>