jsp+servlet+mysql开发web(2)

JDBC步骤
1.建立连接（数据库服务器ip，数据库port，用户名，密码）
2.准备需要执行的SQL指令
3.通过数据库连接加载SQL指令
4.执行SQL指令，获取执行结果
5.处理结果
6.关闭连接
1.创建Java项目“jdbc_0100”
2.在项目的src中创建名为“com.qianfeng.jdbc.dto”的package
3.在“com.qianfeng.jdbc.dto”包中创建名为“Student”类
4.在项目的src中创建名为“com.qianfeng.jdbc.dao”的package
5.在“com.qianfeng.jdbc.dao”包中创建名为“StudentDAO”类，对学生表的所有数据库操作都在此类中完成。

public class userDAO {
//添加用户信息
public boolean insertuser(user u) {boolean b=false;
try {
Connection conn=DBManger.getconn();
String sql="insert into tb_user values(?,?,?,?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, u.getName());
ps.setString(2, u.getSex());
ps.setString(3, u.getSnumber());
ps.setString(4, u.getTel());
ps.setInt(5, u.getAge());
ps.setString(6, u.getSite());
ps.setString(7, u.getPassword());
int i=ps.executeUpdate();
b=i>0?true:false;
conn.close();} catch (Exception e) {
e.printStackTrace();
}
return b;
}


//根据身份证号删除用户信息
public boolean deleteuser1(String s) {
boolean b=false;
try {
Connection conn=DBManger.getconn();
String sql="delete from tb_user where snumber=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, s);
int i=ps.executeUpdate();
b=i>0?true:false;
conn.close();} catch (Exception e) {
e.printStackTrace();
}
return b;
}

//根据身份证号修改用户信息
public boolean updateuser(user u) {
boolean b=false;
try {
Connection conn=DBManger.getconn();
String sql="update tb_user set name=?,sex=?,tel=?,age=?,site=?,password=? where snumber=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, u.getName());
ps.setString(2, u.getSex());
ps.setString(3, u.getTel());ps.setInt(4, u.getAge());
ps.setString(5, u.getSite());
ps.setString(6, u.getPassword());
ps.setString(7, u.getSnumber());
int i=ps.executeUpdate();
b=i>0?true:false;
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return b;
}
//根据身份证号查询用户信息
public user selectuser1(String s){
user u=null;
try {
Connection conn=DBManger.getconn();
String sql="select * from tb_user where snumber=?";
PreparedStatement ps=conn.prepareStatement(sql);ps.setString(1, s);
ResultSet rs=ps.executeQuery();
if(rs.next()) {
String name=rs.getString("name");
String sex=rs.getString("sex");
String tel=rs.getString("tel");
int age=rs.getInt("age");
String site=rs.getString("site");
String password=rs.getString("password");
u=new user(name, sex, s, tel, age, site,password);
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return u;
}

数据库连接
创建“com.qianfeng.jdbc.utils”包然后创建DBManager，用于管理数据库连接
例如：

 public class DBM

anger {
private static final String DRIVER="com.mysql.jdbc.Driver";
private static final String URL="jdbc:mysql://localhost:3306/db_train";
private static final String USERNAME="root";
private static final String PASSWORD="521zys";
static{
try {
Class.forName(DRIVER);
}catch(Exception e) {
e.printStackTrace();
}
}public static Connection getconn() {
Connection conn=null;
try {
conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}

JDBC预编译机制解决SQL注入问题SQL注入在SQL语句采用字符串拼接传递参数的情况，会出现因为参数值得改变导致SQL语义发生改变的情况。
预编译
1.SQL指令中需要参数一律用?表示
2.如果语句中有?，则使用” PreparedStatement ps = conn.prepareStatement(sql);”加载SQL指令，不再使用“Statement stmt = conn.createStatement();”

创建实体类

public class ter {
private int origin_num;
private String origin;
private String optime;
private int bourn_num;
private String bourn;
private String ovtime;
private String number;
private int rest;
public ter(int origin_num, String origin, String optime, int bourn_num, String bourn, String ovtime, String number,int rest) {
super();
this.origin_num = origin_num;this.origin = origin;
this.optime = optime;
this.bourn_num = bourn_num;
this.bourn = bourn;
this.ovtime = ovtime;
this.number = number;
this.rest=rest;
}
public int getOrigin_num() {
return origin_num;
}
public void setOrigin_num(int origin_num) {
this.origin_num = origin_num;
}
public String getOrigin() {return origin;
}
public void setOrigin(String origin) {
this.origin = origin;
}
public String getOptime() {
return optime;
}
public void setOptime(String optime) {
this.optime = optime;
}
public int getBourn_num() {
return bourn_num;
}
public void setBourn_num(int bourn_num) {
this.bourn_num = bourn_num;
}
public String getBourn() {return bourn;
}
public void setBourn(String bourn) {
this.bourn = bourn;
}
public String getOvtime() {
return ovtime;
}
public void setOvtime(String ovtime) {
this.ovtime = ovtime;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public int getRest() {return rest;
}
public void setRest(int rest) {
this.rest = rest;
}
@Override
public String toString() {
return "ter [origin_num=" + origin_num + ", origin=" + origin + ", optime=" + optime + ", bourn_num="
+ bourn_num + ", bourn=" + bourn + ", ovtime=" + ovtime + ", number=" + number + ", rest=" + rest + "]";
}

}

书写servlet类中具体的请求：

 protected void doGet(HttpServletRequest request
, HttpServletResponse response)    throws ServletException, IOException {   doPost(request, response);  } protected void doPost(HttpServletRequest request, HttpServletResponse response) 
   throws ServletException, IOException {
  request.setCharacterEncoding("UTF-8");
  String op=request.getParameter("op");          //获取名为op的参数
  String ov=request.getParameter("ov");
  String number=request.getParameter("number");
  
  terDAO td=new terDAO();
    List<ter> trs1=td.selectter(number);            //根据省份证号查询
     List<ter> trs2=td.selectter(op, ov);
     if(trs1.size()!=0||trs2.size()!=0) {
      request.setAttribute("trs1",trs1);         //将trs1赋值给trs1
   request.setAttribute("trs2",trs2);  
   request.setAttribute("tips", " ");
     }else {
  request.setAttribute("tips", "没有找到相关车次信息!");
     }
     request.getRequestDispatcher("gou.jsp").forward(request, response);//带参数页面跳转
     }

在页面中使用${name}即可获取数据库值
页面中遍历，加头文件

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

页面中采用：

<c:forEach items="${trs2}" var="a">
<tr>
   <td>${a.origin_num}</td>
   <td>${a.origin}</td>
 </tr>
</c:forEach>