文章目录
- 储存过程
- 结合使用JDBC和GUI API
储存过程
储存过程是一组形成逻辑单元并执行特定任务的SQL语句，用于封装一组要在数据库服务器上执行的操作或查询。大多数DBMS支持储存过程，但是语法和功能存在很大差异
- Java DB中创建储存过程
- 注意：不要Statement在存储过程的Java方法中关闭任何对象；如果这样做，则在调用存储过程ResultSet时发出该CALL语句时该对象将不存在。
- 在Java类中创建一个公共的静态方法：该方法执行储存过程的必须过程
public static void showSuppliers(ResultSet[] rs)
throws SQLException {
//JavaDB的约定，用于指示储存过程将使用当前存在的Connection对象
Connection con = DriverManager.getConnection("jdbc:default:connection");
Statement stmt = null;
String query =
"select SUPPLIERS.SUP_NAME, " +
"COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"order by SUP_NAME";
stmt = con.createStatement();
rs[0] = stmt.executeQuery(query);
}- 将生成的结果分配给数组
public static void getSupplierOfCoffee(String coffeeName, String[] supplierName)
throws SQLException {
Connection con = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement pstmt = null;
ResultSet rs = null;
String query =
"select SUPPLIERS.SUP_NAME " +
"from SUPPLIERS, COFFEES " +
"where " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"and ? = COFFEES.COF_NAME";
pstmt = con.prepareStatement(query);
pstmt.setString(1, coffeeName);
rs = pstmt.executeQuery();
if (rs.next()) {
supplierName[0] = rs.getString(1);
} else {
supplierName[0] = null;
}
}- 签名方法
public static void raisePrice(
String coffeeName, double maximumPercentage,
BigDecimal[] newPrice) throws SQLException- 使用SQL脚本或JDBC API在JavaDB中创建储存过程
public void createProcedures(Connection con)
throws SQLException {
Statement stmtCreateShowSuppliers = null;
// ...
String queryShowSuppliers =
"CREATE PROCEDURE SHOW_SUPPLIERS() " +
"PARAMETER STYLE JAVA " +
"LANGUAGE JAVA " +
"DYNAMIC RESULT SETS 1 " +
"EXTERNAL NAME " +
"'com.oracle.tutorial.jdbc." +
"StoredProcedureJavaDBSample." +
"showSuppliers'";
//PARAMETER STYLE：标识用于将参数传递给存储过程的约定。
// JAVA：指定存储过程使用符合Java语言和SQL例程规范的参数传递约定。
// DERBY：指定存储过程支持vararg作为参数列表中的最后一个参数。
//LANGUAGE JAVA：指定存储过程的编程语言（当前JAVA是唯一的选项）。
//DYNAMIC RESULT SETS 1：指定检索到的最大结果集数；在这种情况下，它是1。
//EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'指定此存储过程调用的标准Java方法。
// ...
try {
System.out.println("Calling CREATE PROCEDURE");
stmtCreateShowSuppliers = con.createStatement();
// ...
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtCreateShowSuppliers != null) {
stmtCreateShowSuppliers.close();
}
// ...
}
}- 在JAR文件中打包Java类
- Ant构建脚本build.xml包含将代码编译和打包为JAR文件的目标，使用ant jar编译并打包
- 直接将JAR文件添加到数据库
CALL sqlj.install_jar(
'<JDBC tutorial directory>/
lib/JDBCTutorial.jar',
'APP.JDBCTutorial', 0)
CALL sqlj.replace_jar(
'<JDBC tutorial directory>/
lib/JDBCTutorial.jar',
'APP.JDBCTutorial')";
CALL syscs_util.syscs_set_database_property(
'derby.database.classpath',
'APP.JDBCTutorial')";- 在MySql中创建储存过程
- 使用SQL脚本或JDBC API在MySql中c创建储存过程
- MySqlzai在其储存过程中s使用基于SQL的语法
SELECT 'Dropping procedure SHOW_SUPPLIERS' AS ' '|
drop procedure if exists SHOW_SUPPLIERS|
# ...
SELECT 'Creating procedure SHOW_SUPPLIERS' AS ' '|
create procedure SHOW_SUPPLIERS()
begin
select SUPPLIERS.SUP_NAME,
COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME;
end|- 使用DELIMITER指定分隔符
<target name="mysql-create-procedure">
<sql driver="${DB.DRIVER}"
url="${DB.URL}" userid="${DB.USER}"
password="${DB.PASSWORD}"
classpathref="CLASSPATH"
print="true"
delimiter="|"
autocommit="false"
onerror="abort">
<transaction
src="./sql/${DB.VENDOR}/
create-procedures.sql">
</transaction>
</sql>
</target>- 使用JDBC APIy语句创建储存过程
public void
createProcedureShowSuppliers()
throws SQLException {
String createProcedure = null;
String queryDrop =
"DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";
createProcedure =
"create procedure SHOW_SUPPLIERS() " +
"begin " +
"select SUPPLIERS.SUP_NAME, " +
"COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"order by SUP_NAME; " +
"end";
Statement stmt = null;
Statement stmtDrop = null;
try {
System.out.println("Calling DROP PROCEDURE");
stmtDrop = con.createStatement();
stmtDrop.execute(queryDrop);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtDrop != null)
{
stmtDrop.close();
}
}
try {
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}- 创建SQL查询的储存过程
public void createProcedureGetSupplierOfCoffee()
throws SQLException {
String createProcedure = null;
// ...
createProcedure =
"create procedure GET_SUPPLIER_OF_COFFEE(" +
"IN coffeeName varchar(32), " +
"OUT supplierName varchar(40)) " +
"begin " +
"select SUPPLIERS.SUP_NAME into " +
"supplierName " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"and coffeeName = COFFEES.COF_NAME; " +
"select supplierName; " +
"end";
// ...
}- 在JavaDB和MySqlz中调用储存过程
- JavaDB调用储存过程并打印生成的结果集
cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
String supplier = rs.getString("SUP_NAME");
String coffee = rs.getString("COF_NAME");
System.out.println(supplier + ": " + coffee);
}- MySql使用JavaApi创建储存过程
cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
cs.setString(1, coffeeNameArg);
cs.registerOutParameter(2, Types.VARCHAR);
cs.executeQuery();
String supplierName = cs.getString(2);结合使用JDBC和GUI API
- 实现TableModel
- 管理JTable对象中的数据
- 在实现TableModel接口之前，构造函数初始化成员变量
public CoffeesTableModel(CachedRowSet rowSetArg)
throws SQLException {
//储存表的内容
this.coffeesRowSet = rowSetArg;
// 检查表中的lli列数以及每列的名称
this.metadata = this.coffeesRowSet.getMetaData();
//表冲储存行数和列数
numcols = metadata.getColumnCount();
// Retrieve the number of rows.
this.coffeesRowSet.beforeFirst();
this.numrows = 0;
while (this.coffeesRowSet.next()) {
this.numrows++;
}
this.coffeesRowSet.beforeFirst();
}- 实现方法
- 实现ggegetColumnCount和getRowCount，返回成员变量numcols和numrows值
public int getColumnCount() {
return numcols;
}
public int getRowCount() {
return numrows;
}- 实现getColumnClass，返回指定列的sshu数据类型
public Class getColumnClass(int column) {
return String.class;
}- 实现getColumnName，返回指定列的名称
public String getColumnName(int column) {
try {
return this.metadata.getColumnLabel(column + 1);
} catch (SQLException e) {
return e.toString();
}
}- 实现getColumnAt，返回指定行和列的值
public Object getValueAt(int rowIndex, int columnIndex) {
try {
this.coffeesRowSet.absolute(rowIndex + 1);
Object o = this.coffeesRowSet.getObject(columnIndex + 1);
if (o == null)
return null;
else
return o.toString();
} catch (SQLException e) {
return e.toString();
}
}- 实现isCellEditaable，返回是否允许用户编辑表的内容
Because this sample does not allow users to directly edit the contents of the table (rows are added by another window control), this method returns false regardless of the values of rowIndex and columnIndex:
public boolean isCellEditable(int rowIndex, int columnIndex) {
return false;
}- 实现RowSetLister
- RowSetLister接口中只有一个RowChanged方法
public void rowChanged(RowSetEvent event) {
CachedRowSet currentRowSet =
this.myCoffeesTableModel.coffeesRowSet;
try {
currentRowSet.moveToCurrentRow();
myCoffeesTableModel = new CoffeesTableModel(
myCoffeesTableModel.getCoffeesRowSet());
table.setModel(myCoffeesTableModel);
} catch (SQLException ex) {
JDBCTutorialUtilities.printSQLException(ex);
// Display the error in a dialog box.
JOptionPane.showMessageDialog(
CoffeesFrame.this,
new String[] {
// Display a 2-line message
ex.getClass().getName() + ": ",
ex.getMessage()
}
);
}
}- 布置SWing组件
- 初始化JTableSwing组件
CachedRowSet myCachedRowSet = getContentsOfCoffeesTable();
myCoffeesTableModel = new CoffeesTableModel(myCachedRowSet);
myCoffeesTableModel.addEventHandlersToRowSet(this);
// Displays the table
table = new JTable();
table.setModel(myCoffeesTableModel);- 使用GridBagLayout布局Swing组件
Container contentPane = getContentPane();
contentPane.setComponentOrientation(
ComponentOrientation.LEFT_TO_RIGHT);
contentPane.setLayout(new GridBagLayout());
GridBagConstraints c = new GridBagConstraints();- 为按钮添加监听器
button_ADD_ROW.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
//创建消息提示框，显示要添加到表中的行
JOptionPane.showMessageDialog(
CoffeesFrame.this, new String[] {
"Adding the following row:",
"Coffee name: [" +
textField_COF_NAME.getText() +
"]",
"Supplier ID: [" +
textField_SUP_ID.getText() + "]",
"Price: [" +
textField_PRICE.getText() + "]",
"Sales: [" +
textField_SALES.getText() + "]",
"Total: [" +
textField_TOTAL.getText() + "]"
}
);
try {
//将行添加到cch成员变量
myCoffeesTableModel.insertRow(
textField_COF_NAME.getText(),
Integer.parseInt(textField_SUP_ID.getText().trim()),
Float.parseFloat(textField_PRICE.getText().trim()),
Integer.parseInt(textField_SALES.getText().trim()),
Integer.parseInt(textField_TOTAL.getText().trim())
);
} catch (SQLException sqle) {
displaySQLExceptionDialog(sqle);
}
}
});- 更新数据库功能
button_UPDATE_DATABASE.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
myCoffeesTableModel.coffeesRowSet.acceptChanges();
msgline.setText("Updated database");
} catch (SQLException sqle) {
displaySQLExceptionDialog(sqle);
// Now revert back changes
try {
createNewTableModel();
msgline.setText("Discarded changes");
} catch (SQLException sqle2) {
displaySQLExceptionDialog(sqle2);
}
}
}
}
);- 放弃更改功能
button_DISCARD_CHANGES.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
createNewTableModel();
} catch (SQLException sqle) {
displaySQLExceptionDialog(sqle);
}
}
});
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
