用户登录
用户注册

分享至

jsp---预处理语句对象PreparedStatement

  • 作者: 自由四季风
  • 来源: 51数据库
  • 2021-09-04
预处理语句对象PreparedStatement,使用PreparedStatement进行添加数据,更新数据,删除数据和查询数据
 
添加数据
 
01
<%@page language="java" contentType="text/html;charset=utf-8"%>
02
<%@page import="java.sql.*" %>
03
<!DOCTYPE html>
04
<html>
05
    <head>
06
        <title>获得第二条记录开始的三条记录</title>
07
    </head>
08
    <body>
09
        <%
10
            String url = "jdbc:mysql://localhost:3306/javaweb";//连接数据库的url地址
11
            String user = "root";//登录数据库的用户名
12
            String password = "zhangda890126;;";//登录数据库的用户名的密码
13
            Connection conn = null;//链接对象
14
            PreparedStatement pstmt = null;//语句对象
15
            //ResultSet rs = null;//结果集对象
16
            try{
17
                Class.forName("com.mysql.jdbc.Driver");//加载JDBC驱动程序
18
                conn = DriverManager.getConnection(url,user,password);//链接数据库
19
            }catch(ClassNotFoundException e){
20
                out.println("找不到驱动类");//抛出异常时,提示信息
21
            }catch(SQLException e){
22
                out.println("链接MySQL数据库失败");//处理SQLException异常
23
            }
24
            try{
25
                String adduser = "INSERT INTO user (userid,username,password) VALUES(null,?,?)";//添加一条用户信息
26
                pstmt = conn.<span style="color:#e53333;"><b>prepareStatement</b></span>(adduser);//创建预处理语句对象PreparedStatement
27
                
28
                //设置参数
29
                pstmt.setString(1,"YAO");
30
                pstmt.setString(2,"yao");
31
                
32
                //执行语句
33
                pstmt.executeUpdate();
34
                
35
            }catch(SQLException e){
36
                out.println("添加用户信息失败");
37
            }
38
            
39
            try{
40
                if(pstmt != null){
41
                    pstmt.close();
42
                    conn = null;
43
                }
44
                if(conn != null){
45
                    conn.close();
46
                    conn = null;
47
                }
48
            }catch(Exception e){
49
            
50
                out.println("数据库关闭失败");
51
            }
52
        %>
53
    </body>
54
</html>
提示一下一定不要用错大小写,红色标记就是因为我前面的字母大写,花费了很长时间
更新数据
 
01
<%@page language="java" contentType="text/html;charset=utf-8"%>
02
<%@page import="java.sql.*" %>
03
<!DOCTYPE html>
04
<html>
05
    <head>
06
        <title>获得第二条记录开始的三条记录</title>
07
    </head>
08
    <body>
09
        <%
10
            String url = "jdbc:mysql://localhost:3306/javaweb";//连接数据库的url地址
11
            String user = "root";//登录数据库的用户名
12
            String password = "zhangda890126;;";//登录数据库的用户名的密码
13
            Connection conn = null;//链接对象
14
            PreparedStatement pstmt = null;//语句对象
15
            //ResultSet rs = null;//结果集对象
16
            try{
17
                Class.forName("com.mysql.jdbc.Driver");//加载JDBC驱动程序
18
                conn = DriverManager.getConnection(url,user,password);//链接数据库
19
            }catch(ClassNotFoundException e){
20
                out.println("找不到驱动类");//抛出异常时,提示信息
21
            }catch(SQLException e){
22
                out.println("链接MySQL数据库失败");//处理SQLException异常
23
            }
24
            try{
25
                String updateuser = "UPDATE user SET password = ? WHERE userid = ?";//添加一条用户信息
26
                pstmt = conn.prepareStatement(updateuser);//创建预处理语句对象PreparedStatement
27
                
28
                //设置参数
29
                pstmt.setString(1,"hello world");
30
                pstmt.setInt(2,1);
31
                
32
                //执行语句
33
                pstmt.executeUpdate();
34
                
35
            }catch(SQLException e){
36
                out.println("添加用户信息失败");
37
            }
38
            
39
            try{
40
                if(pstmt != null){
41
                    pstmt.close();
42
                    conn = null;
43
                }
44
                if(conn != null){
45
                    conn.close();
46
                    conn = null;
47
                }
48
            }catch(Exception e){
49
            
50
                out.println("数据库关闭失败");
51
            }
52
        %>
53
    </body>
54
</html>
删除数据
 
01
<%@page language="java" contentType="text/html;charset=utf-8"%>
02
<%@page import="java.sql.*" %>
03
<!DOCTYPE html>
04
<html>
05
    <head>
06
        <title>获得第二条记录开始的三条记录</title>
07
    </head>
08
    <body>
09
        <%
10
            String url = "jdbc:mysql://localhost:3306/javaweb";//连接数据库的url地址
11
            String user = "root";//登录数据库的用户名
12
            String password = "zhangda890126;;";//登录数据库的用户名的密码
13
            Connection conn = null;//链接对象
14
            PreparedStatement pstmt = null;//语句对象
15
            //ResultSet rs = null;//结果集对象
16
            try{
17
                Class.forName("com.mysql.jdbc.Driver");//加载JDBC驱动程序
18
                conn = DriverManager.getConnection(url,user,password);//链接数据库
19
            }catch(ClassNotFoundException e){
20
                out.println("找不到驱动类");//抛出异常时,提示信息
21
            }catch(SQLException e){
22
                out.println("链接MySQL数据库失败");//处理SQLException异常
23
            }
24
            try{
25
                String deleteuser = "DELETE FROM user WHERE userid = ?";//添加一条用户信息
26
                pstmt = conn.prepareStatement(deleteuser);//创建预处理语句对象PreparedStatement
27
                
28
                //设置参数
29
                pstmt.setInt(1,2);
30
                
31
                //执行语句
32
                pstmt.executeUpdate();
33
                
34
            }catch(SQLException e){
35
                out.println("添加用户信息失败");
36
            }
37
            
38
            try{
39
                if(pstmt != null){
40
                    pstmt.close();
41
                    conn = null;
42
                }
43
                if(conn != null){
44
                    conn.close();
45
                    conn = null;
46
                }
47
            }catch(Exception e){
48
            
49
                out.println("数据库关闭失败");
50
            }
51
        %>
52
    </body>
53
</html>
查询数据
 
01
<%@page language="java" contentType="text/html;charset=utf-8"%>
02
<%@page import="java.sql.*" %>
03
<!DOCTYPE html>
04
<html>
05
    <head>
06
        <title>获得第二条记录开始的三条记录</title>
07
    </head>
08
    <body>
09
        <%
10
            String url = "jdbc:mysql://localhost:3306/javaweb";//连接数据库的url地址
11
            String user = "root";//登录数据库的用户名
12
            String password = "zhangda890126;;";//登录数据库的用户名的密码
13
            Connection conn = null;//链接对象
14
            PreparedStatement pstmt = null;//语句对象
15
            ResultSet rs = null;//结果集对象
16
            try{
17
                Class.forName("com.mysql.jdbc.Driver");//加载JDBC驱动程序
18
                conn = DriverManager.getConnection(url,user,password);//链接数据库
19
            }catch(ClassNotFoundException e){
20
                out.println("找不到驱动类");//抛出异常时,提示信息
21
            }catch(SQLException e){
22
                out.println("链接MySQL数据库失败");//处理SQLException异常
23
            }
24
            try{
25
                String queryAll = "SELECT * FROM user LIMIT ?,?;";//添加一条用户信息
26
                pstmt = conn.prepareStatement(queryAll);//创建预处理语句对象PreparedStatement
27
                
28
                //设置参数
29
                pstmt.setInt(1,2);
30
                pstmt.setInt(2,5);
31
                
32
                //执行语句
33
                rs = pstmt.executeQuery();
34
                
35
                while(rs.next()){
36
                    int userid = rs.getInt(1);
37
                    String username = rs.getString(2);
38
                    String userpassword = rs.getString(3);
39
                    out.println("用户的ID:"+userid+"用户名:"+username+"用户的密码:"+userpassword+"<br />");
40
                }
41
                
42
            }catch(SQLException e){
43
                out.println("添加用户信息失败");
44
            }
45
            
46
            try{
47
                if(pstmt != null){
48
                    pstmt.close();
49
                    conn = null;
50
                }
51
                if(conn != null){
52
                    conn.close();
53
                    conn = null;
54
                }
55
            }catch(Exception e){
56
            
57
                out.println("数据库关闭失败");
58
            }
59
        %>
60
    </body>
61
</html>


摘自 张大鹏的博客
软件
前端设计
程序设计
Java相关