jdbc 增删改查 、事务 、过滤器的使用

jdbc 查询操作

1.加载驱动
2.加载用户信息
3.连接成功， 数据库对象，Connection 代表数据库
4. 执行sql语句对象
5.可能的返回结果
6. 释放连接

• 不多废话，代码就是最好的表达

package xyz.DemoWhite;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&useSSL=false";
        String name = "root";
        String password = "2020mysql";
        // 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 加载数据库
        Connection connection = DriverManager.getConnection(url, name, password);
        // 向数据库发送对象SQL statement
        Statement statement = connection.createStatement();
        //  编写对象
        String sql = "select * from jsbc1";
        //执行sql
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            System.out.println("id=" + rs.getObject("id"));
            System.out.println("name=" + rs.getObject("name"));
            System.out.println("password=" + rs.getObject("password"));
            System.out.println("email=" + rs.getObject("email"));
            System.out.println("birthday=" + rs.getObject("birthday"));
        }

        //依次close
        rs.close();
        statement.close();
        connection.close();

    }
}

jdbc 事务管理

package xyz.DemoWhite;

import org.junit.Test;

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

public class shiwu {
    @Test //模块化单元测试
    public void trad() throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&useSSL=false"; 
         //此处可能有些要加上时区设置
        String name = "root";
        String password = "2020mysql";
        Connection connection = null;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 加载数据库
            connection = DriverManager.getConnection(url, name, password);

            //开启事务
            connection.setAutoCommit(false);
            String sql1 = "update account set money = money - 100 where name = '张丹'";
            connection.prepareStatement(sql1).executeQuery();
            String Sql2 = "update account set money = money - 100 where name = '阿文'";
            connection.prepareStatement(Sql2).executeQuery();
            // 提交表单
            connection.commit();
            System.out.println("chenggong");
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException E1) {
                E1.printStackTrace();
            }
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }
}

模块化增删改查

package xyz.DemoWhite.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class BaseDao {
    private static String driver;
    private static String url;
    private static String name;
    private static String password;

    // 静态代码块 ，类加载是初始化
    static {
        Properties properties = new Properties();
        // 通过类加载器获取资源
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接

    public static Connection getConnect() {
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, name, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    // 公共查询

    public static ResultSet execute(Connection connection, String sql, Object[] parmas, ResultSet resultSet, PreparedStatement preparedStatement) throws SQLException {
        // 预编译 SQL ，直接执行
        preparedStatement = getConnect().prepareStatement(sql);
        for (int i = 0; i < parmas.length; i++) {
            preparedStatement.setObject(i + 1, parmas[i]);
        }
        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    // 增删改

    public static int execute(Connection connection, String sql, Object[] parmas, PreparedStatement preparedStatement) throws SQLException {
        preparedStatement = getConnect().prepareStatement(sql);
        for (int i = 0; i < parmas.length; i++) {
            preparedStatement.setObject(i + 1, parmas[i]);
        }
        int updateRows = preparedStatement.executeUpdate();
        return updateRows;
    }


    // 释放连接

    public static boolean release(Connection connection, ResultSet resultSet, PreparedStatement preparedStatement) {
        boolean flag = true;
        if (resultSet != null) {
            try {
                resultSet.close();
                // 资源回收
                resultSet = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                // 资源回收
                preparedStatement = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        if (connection != null) {
            try {
                connection.close();
                // 资源回收
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        return flag;
    }


}

过滤器的使用

package xyz.DemoWhite.filter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class Character extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.getWriter().write("我是中文处理乱码！");

//        req.setCharacterEncoding("TUF-8");
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}




=======================2==========================
package xyz.DemoWhite.filter;

import javax.servlet.*;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
//监听器

public class filter implements Filter {
    // 初始化变量
    public void init(FilterConfig filterConfig) throws ServletException {
        System.out.println("初始化变量！");

    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain Chain) throws IOException, ServletException {
        req.setCharacterEncoding("utf-8");
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html,charset=utf-8");

        HttpServletRequest servlet = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) resp;
        if (servlet.getSession().getAttribute("constant.changliang") == null) {
            response.sendRedirect("/error.jsp");
        }
        System.out.println("执行前。。。");
        Chain.doFilter(req, resp);
        System.out.println("执行后。。。");
    }

    // 自动销毁
    public void destroy() {
        System.out.println("销毁完成！");
    }
}