欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-05 23:29:39
预处理语句对象PreparedStatement,使用PreparedStatement进行添加数据,更新数据,删除数据和查询数据   添加数据   0...
预处理语句对象PreparedStatement,使用PreparedStatement进行添加数据,更新数据,删除数据和查询数据
 
添加数据
 
01
<%@page language="java" contentType="text/html;charset=gb2312"%>
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=gb2312"%>
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=gb2312"%>
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=gb2312"%>
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>


摘自 张大鹏的博客