JDBCPreparedStatement数据库访问与查询 

  1 package com.ding;
  2 
  3 import java.sql.*;
  4 import java.util.Scanner;
  5 
  6 /** 
  7  * <p>Title: JDBCPreparedStatementDemo</p>  
  8  * <p>Description: </p>  
  9  * @author: 丁帅帅
 10  * @date: 2021-4-4 16:21:56
 11  * @version v1.0 
 12  */
 13 public class JDBCPreparedStatementDemo {
 14   
 15      private static final String URL="jdbc:oracle:thin:@localhost:1521:ORCL";
 16      private static final String USERNAME="scott";
 17      private static final String PWD="tiger";
 18      private static final String className="oracle.jdbc.OracleDriver";
 19      public static void update() {
 20          Connection con=null;
 21          PreparedStatement pstmt=null;
 22          try {
 23             Class.forName(className);
 24             
 25             con=DriverManager.getConnection(URL, USERNAME, PWD);
 26             
 27             /*Statement         
 28             stmt=connection.createStatement();
 29             String sql="insert into student values(9,'大傻',18,'男','东京',66,76)";
 30             //String sql="delete from student where id=9";
 31             //String sql="update student set name='小傻' where id=9";
 32             //执行sql
 33             int count=stmt.executeUpdate(sql);// 返回值表示 增删改 几条数据
 34             */
 35             
 36             //PreparedStatement
 37             String sql="insert into student values(?,?,?,?,?,?,?)";
 38             
 39             pstmt=con.prepareStatement(sql);//预编译
 40             
 41             pstmt.setInt(1, 10);
 42             pstmt.setString(2, "大傻");
 43             pstmt.setInt(3, 20);
 44             pstmt.setString(4, "男");
 45             pstmt.setString(5, "泰安");
 46             pstmt.setInt(6, 60);
 47             pstmt.setInt(7, 60);
 48             
 49             int count=pstmt.executeUpdate();
 50             if(count>0) {
 51                 System.out.println(count);
 52                 System.out.println("操作成功");
 53             }
 54         } catch (ClassNotFoundException e) {
 55             // TODO Auto-generated catch block
 56             e.printStackTrace();
 57         } catch (SQLException e) {
 58             // TODO Auto-generated catch block
 59             e.printStackTrace();
 60         }
 61          finally {
 62             if(pstmt!=null) {
 63                 try {
 64                     pstmt.close();
 65                     if(con!=null) {
 66                         con.close();
 67                     }
 68                 } catch (SQLException e) {
 69                     // TODO Auto-generated catch block
 70                     e.printStackTrace();
 71                 }
 72             }
 73         }
 74          
 75      }
 76      
 77      public static void query() {
 78          Connection con=null;
 79          PreparedStatement pstmt=null;
 80          ResultSet rs=null;
 81          
 82          try {
 83             Class.forName("oracle.jdbc.OracleDriver");
 84             con=DriverManager.getConnection(URL, USERNAME, PWD);
 85             
 86             Scanner input= new Scanner(System.in);
 87             System.out.println("请输入用户名：");
 88             String name = input.nextLine() ;
 89             System.out.println("请输入号码：");
 90             int id = input.nextInt() ;
 91             
 92             /*//Statement//不安全有sql注入风险 ' or 1=1 --
 93              String sql = "select count(*) from login where uname='"+name+"' and upwd ='"+pwd+"' " ;
 94            //String sql = "select * from student where uname like '%"+name+"%'";
 95             // 执行SQL(增删改executeUpdate()，查询executeQuery())
 96             rs = stmt.executeQuery(sql); // 返回值表示 增删改 几条数据
 97             */
 98             //PreparedStatement
 99             String sql ="select id from student where id=? and name =?"; 
100             pstmt = con.prepareStatement(sql) ;
101             pstmt.setInt(1, id);
102             pstmt.setString(2, name);
103             rs = pstmt.executeQuery();
104             
105             // d.处理结果
106             int count = -1;
107             if(rs.next()) {
108                 count = rs.getInt("id");
109             }
110             if(count>0) {
111                 System.out.println("登陆成功！");
112             }else {
113                 System.out.println("登陆失败！");
114                 System.out.println(count);
115             }
116 
117         } catch (ClassNotFoundException e) {
118             // TODO Auto-generated catch block
119             e.printStackTrace();
120         } catch (SQLException e) {
121             // TODO Auto-generated catch block
122             e.printStackTrace();
123         }finally {
124             try {
125                  if(rs!=null) rs.close(); 
126                  if(pstmt!=null) pstmt.close();
127                  if(con!=null)con.close();
128             }catch(SQLException e) {
129                 e.printStackTrace();
130             }catch(Exception e) {
131                 e.printStackTrace();
132             }
133         }
134      }
135     public static void main(String[] args) {
136         // TODO Auto-generated method stub
137         //update();
138         query();
139     }
140 
141 }

 

posted @ 2021-04-04 23:40  丁帅帅dss  阅读(181)  评论(0)    收藏  举报