jdbc 2.0

1.Statement接口不能接受參數

2.

PreparedStatement

接口在運作時接受輸入參數

3.

CallableStatement

接口也可以接受運作時輸入參數，當想要通路資料庫存儲過程時使用

4.示例一：

1 package com.rong.web;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.SQLException;
 7 
 8 public class Test2 {
 9 
10     /**
11      * @author 容傑龍
12      */
13     public static void main(String[] args) {
14         Connection conn = getConnection();
15         PreparedStatement ps = null;
16         String sql = "insert into student(name,age) values(?,?)";
17         if (conn != null) {
18             try {
19                 // 預處理語句對象 可用占位符？解決sql注入漏洞，占位符位置從1開始
20                 ps = conn.prepareStatement(sql);
21                 ps.setString(1, "rjl");
22                 ps.setInt(2, 18);
23                 // 是否傳回結果集，此時是false
24                 boolean flag = ps.execute();
25                 System.out.println(flag);
26                 // 傳回執行結果的影響行數，此時傳回值為1
27                 int effects = ps.executeUpdate();
28                 System.out.println(effects);
29                 /////////批處理/////////
30                 String sql1 = "insert into student(name,age) values('one',28)";
31                 String sql2 = "insert into student(name,age) values('two',27)";
32                 // PreparedStatement構造方法必須有字元串參數
33                 ps = conn.prepareStatement("");
34                 ps.addBatch(sql1);
35                 ps.addBatch(sql2);
36                 int[] ints = ps.executeBatch();
37                 for (int i : ints) {
38                     System.out.println(i);
39                 }
40             } catch (SQLException e) {
41                 e.printStackTrace();
42             } finally {
43                 // 關閉資源
44                 try {
45                     if (ps != null) {
46                         ps.close();
47                     }
48                 } catch (SQLException e) {
49                     e.printStackTrace();
50                 }
51                 try {
52                     if (conn != null) {
53                         conn.close();
54                     }
55                 } catch (SQLException e) {
56                     e.printStackTrace();
57                 }
58             }
59 
60         }
61 
62     }
63     public static Connection getConnection() {
64         Connection connection=null;
65         try {
66             Class.forName("com.mysql.jdbc.Driver");
67             String url="jdbc:mysql://127.0.0.1:3306/rjl";
68             String user="root";
69             String password="123123";
70             connection = DriverManager.getConnection(url, user, password);
71             
72         } catch (Exception e) {
73             e.printStackTrace();
74         }
75         return connection;
76     }
77 
78 }      

View Code

 5.示例二：

MySQL存儲過程

1 DELIMITER $$
2 CREATE PROCEDURE getPrice(INOUT myName VARCHAR(20),OUT age INT(20))
3 BEGIN
4 SET myName="rjl";
5 SET age=22;
6 END $$      

java操作

1 package com.rong.web;
 2 
 3 import java.sql.CallableStatement;
 4 import java.sql.Connection;
 5 import java.sql.SQLException;
 6 
 7 public class Test3 {
 8     public static void main(String[] args) {
 9         Connection connection = Test2.getConnection();
10         if (connection != null) {
11             String sql = "call getPrice(?,?)";
12             try {
13                 //建立存儲過程的語句操作對象
14                 CallableStatement cs = connection.prepareCall(sql);
15                 //IN類型參數直接設定set即可
16                 //第一個參數為INOUT類型，需要設定set參數，并注冊參數
17                 cs.setString(1, "kobe");
18                 cs.registerOutParameter(1, java.sql.Types.VARCHAR);
19                 //第二個參數為OUT類型，也需要注冊
20                 cs.registerOutParameter(2, java.sql.Types.INTEGER);
21                 boolean flag = cs.execute();
22                 System.out.println(flag);
23                 //擷取執行存儲過程後的OUT結果
24                 String name = cs.getString(1);
25                 int age = cs.getInt(2);
26                 System.out.println(name + ":" + age);
27             } catch (SQLException e) {
28                 e.printStackTrace();
29             }
30         }
31     }
32 }