AJAX&JDBC&C3P0&DBUtils

发表于 阅读次数：
本文字数： 15k 阅读时长 ≈ 14 分钟

皮卡丘

javaWeb

Ajax

异步加载、客户端和服务器数据交互更新在局部页面的技术、局部刷新

  • Ajax优点

局部刷新、效率高

用户体验好

  • Ajax语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<script type="text/javascript" src="lib/jquery-3.2.1.min.js"></script>
<html>
  <head>
    <title>首页</title>
      <script type="text/javascript">
          $(function () {
              var btn = $("#btn");
              btn.click(function () {
                $.ajax({
                    url: "/test",
                    type: "post",
                    data: "id=1",
                    dataType: "json",
                    success: function (data) {
                        var id = $("#id");
                        var name = $("#name");
                        var score = $("#score");
                        id.val(data.id);
                        name.val(data.name)
                        score.val(data.score)
                    }
                });
              });
          });
      </script>
  </head>
  <body>
  <h1 style="color: aqua">${String }</h1>
  编号<input id="id" type="text" /><br/>
  姓名<input id="name" type="text" /><br/>
  分数<input id="score" type="text" /><br/>
  <input id="btn" type="button" value="点击添加">
  </body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
public class User {

    private Integer id;
    private String name;
    private double score;

    public User(Integer id, String name, double score) {
        this.id = id;
        this.name = name;
        this.score = score;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@WebServlet("/test")
public class testServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        try {
            TimeUnit.SECONDS.sleep(3);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }finally{
            User user = new User(1,"小博",99.99);
            JSONObject jsonObject = JSONObject.fromObject(user);
            resp.getWriter().write(jsonObject.toString());
        }
    }
}
  • $.ajax({属性})

url:请求的后端服务地址

type:请求方式、默认get

data:请求参数

dataType:服务器返回的数据类型、text/json/html/xml、一般为text/json

success:请求成功的回调函数

error:请求失败的回调函数

complete:请求完成的回调函数、无论成功或者失败都会调用

  • Ajax大致原理
    AJAX流程

  • 传统Web数据交互和Ajax数据交互的区别

客户端请求方式

传统Web发送同步请求
Ajax发送异步请求

服务器响应方式

传统Web响应整个JSP页面
Ajax响应需要的数据

客户端处理方式

传统Web需要等待服务器完成响应并重新加载整个页面后、用户才能继续后续操纵
Ajax动态更新页面中局部内容、不影响用户其他操作

JSON

一种轻量级数据交互格式、完成js与后端开发语言对象之间的转换

  • JSON格式
1
2
3
4
5
var user = {
    id: 1,
    name: "小博",
    score: 99.99
}

省市区分级联动模拟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<script type="text/javascript" src="js/jquery-3.2.1.min.js"></script>
<html>
<head>
    <title>地区</title>
    <script type="text/javascript">
        $(function () {
            $("#shi").change(function () {
                var id = $(this).val();
                $.ajax({
                    url: "/servlet",
                    type: "post",
                    data: "id=" + id + "&type=shi",
                    dataType: "json",
                    success: function (data) {
                        var common = "";
                        for (var i = 0; i < data.length; i++) {
                            common += "<option>" + data[i] + "</option>";
                        }
                        $("#qu").html(common);
                    }
                })
            });
            $("#sheng").change(function () {
                var id = $(this).val();
                $.ajax({
                    url: "/servlet",
                    type: "post",
                    data: "id=" + id + "&type=sheng",
                    dataType: "json",
                    success: function (data) {
                        var common = "";
                        var shi = data.shi;
                        for (var i = 0; i < shi.length; i++) {
                            common += "<option>" + shi[i] + "</option>";
                        }
                        $("#shi").html(common);

                        var  common = "";
                        var qu = data.qu;
                        for (var i = 0; i < qu.length; i++) {
                            common += "<option>" + qu[i] + "</option>";
                        }
                        $("#qu").html(common);
                    }
                });
            });
        });
    </script>
</head>
<body>
省<select id="sheng">
    <option value="请选择">请选择</option>
    <option value="辽宁省">辽宁省</option>
    <option value="吉林省">吉林省</option>
    <option value="黑龙江省">黑龙江省</option>
</select>
市<select id="shi">
    <option value="请选择">请选择</option>
</select>
区<select id="qu">
    <option>请选择</option>
</select>
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
@WebServlet("/servlet")
public class AjaxServlet extends HttpServlet {

    private static Map<String, List<String>> map = null;

    private static Map<String, List<String>> map1 = null;
    static{
        map = new HashMap<>();
        List<String> list = new ArrayList<>();
        list.add("沈北新区");
        list.add("皇姑区");
        list.add("和平区");
        map.put("沈阳市",list);
        list = new ArrayList<>();
        list.add("昌图县");
        list.add("西丰县");
        list.add("铁岭县");
        map.put("铁岭市",list);
        list = new ArrayList<>();
        list.add("西岗区");
        list.add("甘井子区");
        list.add("高薪园区");
        map.put("大连市",list);
        list = new ArrayList<>();
        list.add("吉林一区");
        list.add("吉林二区");
        map.put("吉林市",list);
        list = new ArrayList<>();
        list.add("长春一区");
        list.add("长春二区");
        map.put("长春市",list);
        list = new ArrayList<>();
        list.add("四平一区");
        list.add("四平二区");
        map.put("四平市",list);

        map1 = new HashMap<>();
        list = new ArrayList<>();
        list.add("沈阳市");
        list.add("大连市");
        list.add("铁岭市");
        map1.put("辽宁省",list);

        list = new ArrayList<>();
        list.add("吉林市");
        list.add("四平市");
        list.add("长春市");
        map1.put("吉林省",list);

        list = new ArrayList<>();
        list.add("哈尔滨市");
        list.add("齐齐哈尔市");
        list.add("鹤岗市");
        map1.put("黑龙江省",list);

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        String id = req.getParameter("id");
        String type = req.getParameter("type");
        if(type.equals("shi")){
            List<String> list = map.get(id);

            JSONArray jsonArray = JSONArray.fromObject(list);
            resp.getWriter().write(jsonArray.toString());
        }else if(type.equals("sheng")){
            List<String> shi = map1.get(id);
            String s = shi.get(0);
            List<String> qu = map.get(s);
            Region region = new Region();
            region.setShi(shi);
            region.setQu(qu);

            JSONObject jsonObject = JSONObject.fromObject(region);

            resp.getWriter().write(jsonObject.toString());
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class Region {

    private List<String> shi;

    private List<String> qu;

    public List<String> getShi() {
        return shi;
    }

    public void setShi(List<String> shi) {
        this.shi = shi;
    }

    public List<String> getQu() {
        return qu;
    }

    public void setQu(List<String> qu) {
        this.qu = qu;
    }
}

JDBC

  • java.sql 和 javax.sq

DriverManager类

Connection接口

Statement接口

ResultSet接口

  • JDBC使用

加载数据驱动
获取Connection、java于数据库一次连接
创建Statement对象、由Connection产生执行SQL
创建ResultSet对象、保存Statement执行之后所查询到的结果 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
public static void main(String[] args) {
        try {

            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接
            String url = "jdbc:mysql://localhost:3306/test_jdbc?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

            //将地址、用户名、密码传进去
            Connection connection = DriverManager.getConnection(url, "root", "123");

//            表更新的操作
//            String sql = "INSERT INTO student(name,score) VALUES('xiaobo',66.66)";
//            String sql = "UPDATE student SET name = '小王' WHERE id = 3";
//            String sql = "DELETE FROM student WHERE id = 3";
//            Statement statement = connection.createStatement();
//            int i = statement.executeUpdate(sql);
//            if(i > 0){
//                System.out.println("成功执行Sql");
//            }

            String sql = "SELECT * FROM student";

            //createStatement对象的创建
            Statement statement = connection.createStatement();
            //执行SQL
            ResultSet resultSet = statement.executeQuery(sql);

            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double score = resultSet.getDouble("score");

                System.out.println(id + "-" + name + "-" + score);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
  • Statement存在的问题

需要频繁拼接String字符串、出错率高

存在SQL注入风险

  • SQL注入
    利用系统没有对用户输入的信息进行充分检测、在用户输入的数据中注入非法的SQL语句、来进行恶意的行为

  • PreparedStatement

Statement的子类、提供了SQL占位符的功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public static void main(String[] args) {
        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接
            String url = "jdbc:mysql://localhost:3306/test_jdbc?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            Connection connection = DriverManager.getConnection(url, "root", "123");

            String userName = "xiaobo";
            String passWord = "xiaobo";
            String sql = "SELECT * FROM user WHERE name = ? AND password = ?";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,"xiaobo");
            preparedStatement.setString(2,"xiaobo");

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                if(userName.equals(resultSet.getString("password")) && passWord.equals(resultSet.getString(2))){
                    System.out.println("登录成功");
                }else{
                    System.out.println("登录失败");
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

数据库连接池

  • 数据库连接池思想

为数据库建立一个缓冲区、预先向缓冲区放入一定数量的连接对象、当需要获取数据库连接时、只需要从缓冲池取出对象、用完之后在放回缓冲池中、供下一次请求使用、使资源重复利用
当数据库连接池中没有空闲的连接时、新的请求就会进入等待队列、等待其他线程释放资源

  • C3P0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public static void main(String[] args) {
        //创建C3P0
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test_jdbc?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC");
            dataSource.setUser("root");
            dataSource.setPassword("123");

            //初始化连接池数量
            dataSource.setInitialPoolSize(20);
            //当访问大于连接池数量时、连接池向DB申请的数量
            dataSource.setAcquireIncrement(5);
            //连接池空闲的最小数量
            dataSource.setMinPoolSize(2);
            //连接池最大数量
            dataSource.setMaxPoolSize(50);

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
  • C3P0配置文件c3p0-config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

	<named-config name="testc3p0">
		
		<!-- 指定连接数据源的基本属性 -->
		<property name="user">root</property>
		<property name="password">123</property>
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/test_jdbc?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC</property>
		
		<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
		<property name="acquireIncrement">5</property>
		<!-- 初始化数据库连接池时连接的数量 -->
		<property name="initialPoolSize">5</property>
		<!-- 数据库连接池中的最小的数据库连接数 -->
		<property name="minPoolSize">5</property>
		<!-- 数据库连接池中的最大的数据库连接数 -->
		<property name="maxPoolSize">10</property>
	
	</named-config>
		
</c3p0-config>
1
ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");

初始化ComboPooledDataSource传入named-config标签name属性值即可

DBUtils

  • ResultHandler结果集处理

BeanHandler:将结果映射成java对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public class Student {

    private Integer id;
    private String name;
    private Double score;

    public Student() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getScore() {
        return score;
    }

    public void setScore(Double score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", score=" + score +
                '}';
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");

        Connection connection = null;
        Student student = null;

        try {
            connection = dataSource.getConnection();
            String sql = "SELECT * FROM student WHERE id = 1";
            QueryRunner queryRunner = new QueryRunner();
            student = queryRunner.query(connection, sql, new BeanHandler<>(Student.class));
            System.out.println(student);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                connection.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Student类当中必须有无参构造、因为通过反射获取到的Stduent、Student中属性需要和数据库字段相同形成映射关系

BeanListHandler:将结果映射成List集合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");

        Connection connection = null;

        try {
            connection = dataSource.getConnection();
            String sql = "SELECT * FROM student";
            QueryRunner queryRunner = new QueryRunner();
            List<Object> list = queryRunner.query(connection, sql, new BeanListHandler<>(Student.class));
            list.add(queryRunner.query(connection, sql, new BeanListHandler<>(User.class)));

            System.out.println(list);

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                connection.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

MapHandler:将结果映射成Map对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");

        Connection connection = null;

        try {
            connection = dataSource.getConnection();
            String sql = "SELECT * FROM student WHERE id = 1";
            QueryRunner queryRunner = new QueryRunner();
            Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler());
            System.out.println(map.get("id"));
            System.out.println(map.get("name"));
            System.out.println(map.get("score"));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                connection.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

MapListHandler:将结果映射成MapList集合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");

        Connection connection = null;

        try {
            connection = dataSource.getConnection();
            String sql = "SELECT * FROM student";
            QueryRunner queryRunner = new QueryRunner();

            List<Map<String,Object>> list = queryRunner.query(connection,sql,new MapListHandler());
            for(Map<String,Object> map : list){
                System.out.println(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
-------------本文结束 感谢您的阅读-------------

本文标题:AJAX&JDBC&C3P0&DBUtils

文章作者:小博

发布时间:2021年06月22日 - 11:44

最后更新:2021年06月22日 - 11:46

原始链接:https://codexiaobo.github.io/posts/851211976/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。