Skip to content

Commit bb208b6

Browse files
TimSongCoderTimSongCoder
committed
Play with CallableStatement APIs for the first time, not supported by sqlite.
1 parent 49978a9 commit bb208b6

2 files changed

Lines changed: 137 additions & 2 deletions

File tree

‎Chapter14/CallableStatementDemo.java‎

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,135 @@
1+
import java.io.IOException;
2+
import java.io.FileNotFoundException;
3+
4+
import java.sql.Connection;
5+
import java.sql.DriverManager;
6+
import java.sql.SQLException;
7+
import java.sql.Statement;
8+
import java.sql.ResultSet;
9+
import java.sql.PreparedStatement;
10+
import java.sql.CallableStatement;
11+
12+
public class CallableStatementDemo{
13+
14+
private static final String URL_JAVADB = "jdbc:derby:employee4;create=true";
15+
16+
public static void main(String[] args){
17+
Connection con = null;
18+
try{
19+
con = DriverManager.getConnection(URL_JAVADB);
20+
Statement stmt = null;
21+
try{
22+
stmt = con.createStatement();
23+
String sql = "CREATE PROCEDURE FIRE(IN ID INTEGER)" +
24+
" PARAMETER STYLE JAVA" +
25+
" LANGUAGE JAVA" +
26+
" DYNAMIC RESULT SETS 0" +
27+
" EXTERNAL NAME 'CallableStatementDemo.fire'";
28+
stmt.executeUpdate(sql); // store the procedure, not supported by sqlite.
29+
sql = "CREATE TABLE EMPLOYEES(ID INTEGER, NAME VARCHAR(30), FIRED BOOLEAN)"; // STATIC SQL STATEMENT
30+
stmt.executeUpdate(sql);
31+
PreparedStatement pstmt = null;
32+
CallableStatement cstmt = null;
33+
try{
34+
pstmt = con.prepareStatement("INSERT INTO EMPLOYEES VALUES(?, ?, false)");
35+
String[] names = {"Jhon Doe", "Sally Smith"};
36+
for(int i=0; i<names.length;i++){
37+
pstmt.setInt(1, i+1);
38+
pstmt.setString(2, names[i]);
39+
pstmt.executeUpdate();
40+
}
41+
// output the tabular content
42+
sql = "SELECT * FROM EMPLOYEES";
43+
ResultSet resultSet = stmt.executeQuery(sql);
44+
while(resultSet.next()){
45+
System.out.println(resultSet.getInt("ID") + ", " + resultSet.getString("NAME") + ", " + resultSet.getBoolean("FIRED"));
46+
}
47+
// FIRE ONE EMPLOYEE
48+
cstmt = con.prepareCall("{call FIRE(?)}"); // CALL THE STORED PROCEDURE FIRE, WILL INVOKE THE SPECIFIED PUBLIC STATIC METHOD
49+
cstmt.setInt(1, 2); // set the parameter value to fire No.2 employee4
50+
cstmt.execute();
51+
//output the tabular content again.
52+
sql = "SELECT * FROM EMPLOYEES";
53+
resultSet = stmt.executeQuery(sql);
54+
while(resultSet.next()){
55+
System.out.println(resultSet.getInt("ID") + ", " + resultSet.getString("NAME") + ", " + resultSet.getBoolean("FIRED"));
56+
}
57+
// drop the table
58+
sql = "DROP TABLE EMPLOYEES";
59+
stmt.executeUpdate(sql);
60+
// drop procedure
61+
sql = "DROP PROCEDURE FIRE";
62+
stmt.executeUpdate(sql);
63+
}catch(SQLException sqle){
64+
while(sqle!=null){
65+
System.err.println("SQL error : " + sqle.getMessage());
66+
System.err.println("SQL state : " + sqle.getSQLState());
67+
System.err.println("Error code : " + sqle.getErrorCode());
68+
System.err.println("Cause : " + sqle.getCause());
69+
sqle = sqle.getNextException();
70+
}
71+
}finally{
72+
if(pstmt!=null){
73+
try{
74+
pstmt.close();
75+
}catch(SQLException sqle){
76+
sqle.printStackTrace();
77+
}
78+
}
79+
}
80+
}catch(SQLException sqle){
81+
while(sqle!=null){
82+
System.err.println("SQL error : " + sqle.getMessage());
83+
System.err.println("SQL state : " + sqle.getSQLState());
84+
System.err.println("Error code : " + sqle.getErrorCode());
85+
System.err.println("Cause : " + sqle.getCause());
86+
sqle = sqle.getNextException();
87+
}
88+
}finally{
89+
if(stmt != null){
90+
try{
91+
stmt.close();
92+
}catch(SQLException sqle){
93+
sqle.printStackTrace();
94+
}
95+
}
96+
}
97+
}catch(SQLException sqle){
98+
while(sqle!=null){
99+
System.err.println("SQL error : " + sqle.getMessage());
100+
System.err.println("SQL state : " + sqle.getSQLState());
101+
System.err.println("Error code : " + sqle.getErrorCode());
102+
System.err.println("Cause : " + sqle.getCause());
103+
sqle = sqle.getNextException();
104+
}
105+
}finally{
106+
if(con != null){
107+
try{
108+
con.close();
109+
}catch(SQLException sqle){
110+
sqle.printStackTrace();
111+
}
112+
}
113+
}
114+
}
115+
116+
public static void fire(int id) throws SQLException{
117+
Connection con = DriverManager.getConnection("jdbc:default:connection");
118+
String sql = "UPDATE EMPLOYEES SET FIRED=TRUE WHERE ID=" + id;
119+
Statement stmt = null;
120+
try{
121+
stmt = con.createStatement();
122+
stmt.executeUpdate(sql);
123+
}catch(SQLException sqle){
124+
sqle.printStackTrace();
125+
}finally{
126+
if(stmt != null){
127+
try{
128+
stmt.close();
129+
}catch(SQLException sqle){
130+
sqle.printStackTrace();
131+
}
132+
}
133+
}
134+
}
135+
}

‎Chapter14/derby.log‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
----------------------------------------------------------------
2-
Thu Oct 27 09:22:35 CST 2016:
2+
Thu Oct 27 11:03:37 CST 2016:
33
����ʹ������س��� sun.misc.Launcher$AppClassLoader@5e481248 �������ݿ�
4-
Ŀ¼ E:\LearnJavaForAndroid\Exercise\Chapter14\employee3 �е� Derby �汾 The Apache Software Foundation - Apache Derby - 10.11.1.2 - (1629631) ʵ�� a816c00e-0158-03bb-4903-00000e9d9f80
4+
Ŀ¼ E:\LearnJavaForAndroid\Exercise\Chapter14\employee4 �е� Derby �汾 The Apache Software Foundation - Apache Derby - 10.11.1.2 - (1629631) ʵ�� a816c00e-0158-0417-c87d-00000e9d9f68
55
�Ѵ� file:/C:/Program%20Files/Java/jdk1.8.0_45/db/lib/derby.jar ����
66
java.vendor=Oracle Corporation
77
java.runtime.version=1.8.0_45-b15

0 commit comments

Comments
 (0)