新浪SAE ———— JavaWeb项目部署教程<数据库连接2> (源码)

• user_info—–>JavaBean

   package DB;

public class user_info {
  private int  id;
  private String sname;
  private String school_num;
  private String account;
  private String password;
  private String email;
public int getID() {
    return id;
}
public void setID(int id) {
    this.id = id;
}
public String getSname() {
    return sname;
}
public void setSname(String sname) {
    this.sname = sname;
}
public String getSchool_num() {
    return school_num;
}
public void setSchool_num(String school_num) {
    this.school_num = school_num;
}
public String getAccount() {
    return account;
}
public void setAccount(String account) {
    this.account = account;
}
public String getPassword() {
    return password;
}
public void setPassword(String password) {
    this.password = password;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}
public user_info(int id, String sname, String school_num, String account,
        String password, String email) {    
    this.id = id;
    this.sname = sname;
    this.school_num = school_num;
    this.account = account;
    this.password = password;
    this.email = email;
}
  public user_info(){     
  }
}

• DB类

package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.sina.sae.util.SaeUserInfo;//导入新浪提供的jar包，里面有一系列方法
public class DB {
    String sql = "select * from user_info";
  public static Connection getCon(){
        Connection con=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://w.rdc.sae.sina.com.cn:3307/app_percygo";
            String user=SaeUserInfo.getAccessKey();    //获取AccessKey,不需要自己填写
            String password=SaeUserInfo.getSecretKey(); //获取SecretKey
            con=DriverManager.getConnection(url,user,password);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return con;
    }   
 public static Statement createStat(Connection con){
    Statement stat=null;
    try{
        stat=con.createStatement();
    }catch(SQLException e){
        e.printStackTrace();
    }
    return stat;
  } 
  public static ResultSet executeQuery(Statement stat,String sql){
    ResultSet rs=null;
    try{
        rs=stat.executeQuery(sql);
    }catch(SQLException e){
        e.printStackTrace();
    }
    return rs;
  }
    public static int executeUpdate(Connection con, String sql) {
        int ret = 0;
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            ret = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(stmt);
        }
        return ret;
    }
  public static PreparedStatement preparedStat(Connection con,String sql){
    PreparedStatement pStat=null;
    try{
        pStat=con.prepareStatement(sql);
    }catch(SQLException e){
        e.printStackTrace();
    }
    return pStat;   
  }
  public static PreparedStatement preparedStat(Connection con,String sql,int autoGeneratedKeys){
    PreparedStatement pStat=null;
    try{
        pStat=con.prepareStatement(sql,autoGeneratedKeys);  
    }catch(SQLException e){
        e.printStackTrace();
    }
    return pStat;
  }
  public static void close(Connection con){
    try{
        if(con!=null){
             con.close();
             con=null;
        }
    }catch(SQLException e){
        e.printStackTrace();
    }
  }

  public static void close(Statement stat){
    try{
        if(stat!=null){
            stat.close();
            stat=null;
        }
    }catch(SQLException e){
        e.printStackTrace();
    }
  }

  public static void close(ResultSet rs){
    try{
        if(rs!=null){
            rs.close();
            rs=null;
        }
    }catch(SQLException e){
        e.printStackTrace();
    }
  }
}

• Servlet

   package DB;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import DB.DB;
public class connection_test extends HttpServlet {
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
    doPost(request, response);

    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
           Connection con=null;
            Statement stmt=null;
            ResultSet  rs=null;
            String sql="select * from user_info";
            con=DB.getCon();                                                       //获取数据库连接
            stmt=DB.createStat(con);
             try {
                rs=stmt.executeQuery(sql);
                if(rs.next()){  
                    user_info user=new user_info();
                    List<user_info> list=new ArrayList<user_info>();
                    user.setID(rs.getInt("ID"));
                    user.setSname(rs.getString("sname"));
                    user.setSchool_num(rs.getString("school_num"));
                    user.setAccount(rs.getString("account"));
                    user.setPassword(rs.getString("password"));
                    user.setEmail(rs.getString("email"));       
                    list.add(user);
                    request.setAttribute("list", list);
                    request.getRequestDispatcher("./page/success.jsp").forward(request, response);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }    
}
}

• index.jsp

<%@ 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">
<html>
  <head>
    <base href="<%=basePath%>">
    <title>title_publish_test</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">

    <style type="text/css">
       .center {
    margin: 100 auto;
    width: 900px;
    height: 600px;
    border: 2px solid #555555;
    padding:20px;
    padding-left:60px;
}
    </style>    
  </head>
<div class="container">
    <div class="row">
        <div class="col-md-4"></div>
        <div class="col-md-4">
            <h1>SAE数据库测试</h1>
        </div>
        <div class="col-md-4"></div>
    </div>
    <br>
    <br>
    <br>
    <div class="row">
        <div class="col-md-5"></div>
        <div class="col-md-2">
            <form action="connection_test" method="post">
                <button class="btn btn-danger " type="submit">数据读取测试</button>
            </form>
        </div>
        <div class="col-md-5"></div>
    </div>
    <div class="row"><</div>
</div>
<body>  
  </body>
</html>