Java学习之路0811(预编译、事务、web项目)

PreparedStatement预编译

在学习SQL语句时，为了防止在进行查询时SQL注入，需要将SQL语句参数化，就需要用到connection类的preparestatement方法进行预编译,(创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库)

String select="SELECT * FROM USER WHERE name='maqi ' and password='123'";
String select="SELECT * FROM USER WHERE name='maqi ' and password='123'or '1=1'";

但表中实际存在的是

分别执行后，结果为:

执行成功
maqi 123
true 1
maqi登录成功！ 密码为123 num=1
maqi 123’or ‘1=1
true 13
maqi登录成功！ 密码为123’or ‘1=1 num=13

虽然两条SQL语句查找的结果不同，但证明了要查找的结果“存在”，用在登录查找是会是得不存在的用户也能登陆成功，虽然不是我们想要查找的结果，但是在SQL语句编译时改变了对该语句的理解，变成了对SQL语句的理解，不是我们原来的理解.这就是SQL注入。解决办法为使SQL参数化，用preparedStatement进行预编译。具体代码实现如下：

Connection coon=SQLManager.newInstabce().getConn();
                String name=textAreaName.getText();
                String password=textAreaPassword.getText();
                try {
                    PreparedStatement preparedStatement = coon.prepareStatement("select *  from user where name=? and password=?");
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, password);
                    ResultSet set=preparedStatement.executeQuery();
                    set.last();
                    int num=set.getRow();
                    System.out.println("查询的结果"+num);
                    if (num>0) {
                        System.out.println(name+"登录成功！ 密码为"+password+"   num="+num);
                    }else{
                        System.out.println("该用户不存在，请注册！  num="+num);
                    }
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }

分别输入 maqi–123和maqi–1234‘or 1=’1
运行结果：

执行成功
查询的结果1
maqi登录成功！ 密码为123 num=1
查询的结果0
该用户不存在，请注册！ num=0

事务

事务（一组不可拆分的操作）

                String name=textAreaNmae.getText();
                String password=textAreaPassword.getText();
                Connection coon=SQLManager.newInstabce().getConn();
                        String sql1="insert into user(name,password)values('星星','123')";
                        String sql2="insert into user(name,password)values('猴子','124')";
                        String sql3="insert into user(name,password)values('猫','125')";
                        String sql4="insert into user(name,password)values('鱼','5555')";
                        String sql5="insert into user(name,password)values('狗','8888')";
                        try {
                            Statement state=coon.createStatement();
                            //数据库连接默认为每一条语句都是一个事务，会单独执行
                            //首先设置connection不会自动提交
                            coon.setAutoCommit(false);
                            state.execute(sql1);
                            state.execute(sql2);
                            state.execute(sql3);
                            state.execute(sql4);
                            state.execute(sql5);
//                          state.addBatch(sql1);
//                          state.addBatch(sql2);
//                          state.addBatch(sql3);
//                          state.addBatch(sql4);
//                          state.addBatch(sql5);
//                          state.executeBatch();
                            coon.commit();
                        } catch (SQLException e1) {
                            // TODO Auto-generated catch block
                            e1.printStackTrace();
                        }

Web项目

利用eclipse中的插件tomcat编写网页
将编写服务器连接到数据库

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.tomcat.util.http.fileupload.ParameterParser;

/**
 * Servlet implementation class MyTestServerlet
 */
@WebServlet("/MyTestServerlet")
public class MyTestServerlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public MyTestServerlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        String userName = request.getParameter("userName");
        String password = request.getParameter("password");
try {
         Thread.sleep(5000);
         } catch (InterruptedException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         }
         if (userName==null&&password==null) {
         System.out.println("userName=null password=null");
         }else{
         userName=Encoding.doEncoding(userName);
         System.out.println("提交了用户和密码，用户名："+userName+" 密码："+password);
         String s="提交了用户信息，用户名称："+userName+" 密码："+password;
//       让浏览器以utf-8编码格式解析
        response.setHeader("Content-type", "text/html;charset=UTF-8");
        response.getWriter().append(s);
         }

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

SQLManager类

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

public class SQLManager {

    public Connection conn;

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public Statement state;

    public Statement getState() {
        return state;
    }

    public void setState(Statement state) {
        this.state = state;
    }

    // 单例设计模式
    private static SQLManager manager;

    public static synchronized SQLManager newInstabce() {
        if (manager == null) {
            manager = new SQLManager();
        }
        return manager;
    }

    private SQLManager() {

        // 链接数据库驱动
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名
        String url = "jdbc:mysql://localhost:3306/calzz";
        // MySQL配置使得用户名
        String user = "root";
        // Java连接MySQL时配置的密码
        String password = "2012163";
        try {
            Class.forName(driver);// 加载驱动
            // 与数据库建立连接
            conn = DriverManager.getConnection(url, user, password);
            if (!conn.isClosed()) {
                // 数据库操作类
                state = conn.createStatement();
                String creatTable = "CREATE TABLE if not exists user (id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(20) BINARY NOT NULL,password int(6) NOT NULL )";
                state.execute(creatTable);
                // String sql="insert into
                // student(name,sex,age)values('张三',1,18)";
                 state.execute(sql);
                // String update="update student set name='王五' where id=7";
                // state.execute(update);
                // String delete="delete from student where id=8";
                // state.execute(delete);
                // String select="select * from student";
                 state.executeQuery(select);
                // ResultSet set=state.executeQuery(select);
                // set.first();
                // while (!set.isLast()) {
                // System.out.println(set.getString("name"));
                // set.next();
                // }
                //
                System.out.println("执行成功");
            } else {
                System.out.println("请打开数据库");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

encoding类
import java.io.UnsupportedEncodingException;

public class Encoding {
    public static String doEncoding(String string) {

        try {
            byte[] array = string.getBytes("ISO-8859-1");
            string = new String(array, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return string;
    }
}