每日归档： 2013年7月29日

//[SHELL]-->表示在Linux终端执行的命令
//[MySQL]-->表示在MySQL中执行的命令

//[SHELL] vim shop.sql
//复制下面的内容,粘贴到里面
DROP DATABASE DEMO;
CREATE DATABASE DEMO;
USE DEMO;
create table SHOP1063 (
       id int not null auto_increment,
      name varchar(20) not null ,
       age int not null ,
      primary key(id)
      );
//[SHELL]:wq!+ENTER
[SHELL]mysql -u root -p
[SHELL]password:[键入你的密码,之后回车]
[MySQL]:source shop.sql
//如下提示表示创建成功.
//Query OK, 1 row affected (0.03 sec)

//Query OK, 1 row affected (0.00 sec)

//Database changed
//Query OK, 0 rows affected (0.15 sec)

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *  insert Data
 *
 * @author puruidong
 * @version 2013.7.29
 *
 */
public class InsertData {
   
   
    /*
     * !!!CREATE DATABASE AND CREATE TABLE.
     *  MYSQL.
     *
     
         DROP DATABASE DEMO;
            CREATE DATABASE DEMO;
            USE DEMO;
                create table SHOP1063 (
                    id int not null auto_increment,
                  name varchar(20) not null ,
                   age int not null ,
                  primary key(id)
                  );
     */
   
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/DEMO";
    private static String USER = "root";
    private static String PASSWORD = "dcxy5201314";
    private static Statement st = null ;
   
    private InsertData(){
        getStat();
    }
   
   
    /**
     * GET Statement Object.
     * @return Statement Object.
     */
   
    private Statement getStat(){
        try{
            Class.forName(DRIVER);
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             st = conn.createStatement();
           
        }catch(SQLException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return st;
    }
   
    /**
     * inser Data.
     * @return
     * @throws SQLException
     */
    private int InsertDatas(int i)throws SQLException{
        int result = 0 ;
        StringBuffer sql = new StringBuffer();
        sql.append(" INSERT INTO SHOP1063  ");
        sql.append(" (name,age) ");
        sql.append(" VALUES ");
        sql.append(" ( 'test"+i+"',");
        sql.append(i+");");
        result = st.executeUpdate(sql.toString());
        return  result;
    }
   
    public static void main (String [] args){
        try{
            InsertData id = new InsertData();
            int result=0;
            for (int i=0;i<30;i++){
                result=id.InsertDatas(i);
            }
            System.out.println(result);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
}

package bean;

public class ShopTest {
   
    private int id ;
    private String name;
    private int age ;
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

}

CONN_DB_URL=jdbc:mysql://localhost:3306/DEMO
CONN_DB_USER=root
CONN_DB_PASSWORD=password

package bean;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

public class CONNDB {
   
    private final static String DRIVER = "com.mysql.jdbc.Driver";
    private static String URL = null ;
    private static String USER = null;
    private static String PASSWORD = null ;
    private static Statement st = null ;
    private static Properties prop = new Properties(); 
    private static String PropFiles = "ConnDB.Properties";  //read Properties
   
    public CONNDB(){
        try{
        InputStream is = getClass().getResourceAsStream(PropFiles);
        prop.load(is);
        URL=prop.getProperty("CONN_DB_URL", URL);
        USER=prop.getProperty("CONN_DB_USER", USER);
        PASSWORD = prop.getProperty("CONN_DB_PASSWORD", PASSWORD);
        getStat();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
    public Statement getStat(){
        try{
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        st = conn.createStatement();
        }catch(Exception e){
            e.printStackTrace();
        }
        return st ;
    }

}

//已经省略部分代码

        /**
     * 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 {
        String query = request.getParameter("action");
        if (query.equals("query")){
            this.query(request,response);
        }

    }

        /**
     * List.
     * @param request
     * @param response
     * @throws ServletException
     * @throws IOException
     */
    public void query(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
                List <Object> list = new ArrayList<Object>();
                CONNDB co = new CONNDB();
                ResultSet rs = null ;  
                Statement sts = co.getStat();
                try{
                rs = sts.executeQuery(" SELECT * FROM SHOP1063 ");
                while (rs.next()){
                    ShopTest st = new ShopTest();
                    st.setId(rs.getInt("id"));
                    st.setName(rs.getString("name"));
                    st.setAge(rs.getInt("age"));
                    list.add(st);
                }
                }catch(SQLException e){
                    e.printStackTrace();
                }
                request.setAttribute("list", list);
                request.getRequestDispatcher("query.jsp").forward(request, response);
    }

<servlet>
    <servlet-name>ShopServlet</servlet-name>
    <servlet-class>bean.ShopServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>ShopServlet</servlet-name>
    <url-pattern>/servlet/ShopServlet</url-pattern>
  </servlet-mapping>

<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>首页</title>
  </head>
  <body>
    <c:redirect url="./ShopServlet" >
    <c:param name="action" value="query" />
    </c:redirect>
  </body>
</html>

<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>所得数据</title>
</head>
<body>
    <table border="1">
        <tr>
            <td>ID</td>
            <td>姓名</td>
            <td>年龄</td>
        </tr>
        <c:forEach items="${requestScope.list}" var="goods">
            <tr>
                <td>${goods.id}</td>
                <td>${goods.name}</td>
                <td>${goods.age}</td>
                </tr>
        </c:forEach>
    </table>
</body>
</html>