Originally posted by David Fahy:
Hi,
Is it possible to make a direct database connection from inside a CM bean? I have a bean that registers customersand stores their details in a database (Oracle 9i). The bean handles all the database access. I now want to enhance this bean and encrypt the credit card numbers using 3des and a key stored in the database. Can I open a connection to the database from inside the bean to access the key which is stored in a different table or is this not possible because of the container managed persistence? If so does anyone have any suggestions on how I could achieve this, I'm new to EJBs and have run out of ideas. Any help is greatly appreciated. Thanks.
You have brought up a topic which at the top of my personal "Best Practices with Database Programming" list.
Never Access Tables Directly from a Program In order to truly separate your database from your business logic, any database activity should be through Stored Procedures. This allows your DBA to optimize tables and queries without requiring the programmers to change a single line of code in the software.
Lets use your case for an example. As part of the database requirements, you tell your DBA that you need to store and retrieve customer information within the database, you give him the information you need to persist
Customer Name
Account number
Address
Phone
Credit Card Number
The dba creates a set of stored procedure that allow you to store the customer's information, update the information, and retrieve it based on various data (like customer name or account number). He creates a table structure that best represents the customer's information but all access to the data will be through the stored procedure.
When you later determine that the CC Number should be stored in an encrypted format, you inform the DBA and he modifies the stored procedure to support the new business rule. This can be done without any changes within your
Java code whatsoever.
The added benefit is that it requires one less roundtrip to the database (retrieving the key).
And that ends my lesson for today. Remember, ALWAYS USED STORED PROCEDURES FOR DATABASE ACCESS.
Hope that helps,
Michael
PS see my post
here to learn why you shouldn't be writing CMP Entity Beans in the first place.
[ February 17, 2004: Message edited by: Michael D. Brown ]