statement、prepareStatement、callablestatement－JoshS的部落格

statement:

select:

conn = DriverManager.getConnection(jdbcUrl, username, password);
Statement statement = conn.createStatement();
result = statement.executeQuery("SELECT * FROM test_message");

while (result.next()) {
Message message = new Message();
message.setName(result.getString(1));
message.setEmail(result.getString(2));
messages.add(message);
}

insert、update:

conn = DriverManager.getConnection(jdbcUrl, username, password);
statement = conn.createStatement();
statement.executeUpdate(
"INSERT INTO test_message(name, email) VALUES ('"
+ message.getName() + "', '"
+ message.getEmail() +"')");

prepareStatement:

select:

String selectSQL = "SELECT  name ,email FROM test_message WHERE seqno = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 2);   // 2就是 value值
ResultSet rs = preparedStatement.executeQuery(selectSQL );
while (rs.next()) {
	String userid = rs.getString("name");
	String username = rs.getString("email");	
}

insert、update:

conn = DriverManager.getConnection(jdbcUrl, username, password);
statement = conn.prepareStatement("insert into test_message(SEQNO,NAME,EMAIL,MSG) values( test_message.nextval ,?,?) ");
statement.setString(1,message.getName());
statement.setString(2, message.getEmail());
statement.executeUpdate();

callablestatement

如果有呼叫到Stored Procedure的話則可以用callablestatement

下面範例直接貼網路上參考

http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

tring getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);
 
// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();
 
String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);

CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
	   p_userid IN DBUSER.USER_ID%TYPE,
	   o_username OUT DBUSER.USERNAME%TYPE,
	   o_createdby OUT  DBUSER.CREATED_BY%TYPE,
	   o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  DBUSER WHERE USER_ID = p_userid;
 
END;