1. In Oracle's SQL Plus tool, log in to the database as a user with DBA authority.
The to_128 after @ is the connection string name of the database, which needs to be changed according to the specific situation. If the database is local, you can omit the @ and connection string.
2. Create the jbuser user and specify the password as abc.
SQL> create user jbuser identified by abc;
3. Assign connect and resource role permissions to the jbuser user.
SQL> grant connect ,resource to jbuser;
4. Use jbuser to log in to the database
SQL> connect jbuser/abc@to_128 ;
5. Create user tables and sequences and run the following sql code in the SQL> command.
Code Listing 1 Code to create tables and sequences
1. --Create user table
2. create table T_USER (
3. USER_ID CHAR(6) not null,
4. USER_NAME VARCHAR2(60),
5. PASSWORD VARCHAR2(20),
6. constraint PK_T_USER primary key (USER_ID)
7. );
8. --Create login log table
9. create table T_LOGIN_LOG (
10. ID CHAR(12) not null,
11. USER_ID CHAR(6) not null,
12. DT_LOGIN CHAR(14) not null,
13. DT_LONOUT CHAR(14),
14. constraint PK_T_LOGIN_LOG primary key (ID)
15. );
16.
17. --Create an index to generate the primary key of the T_LOGIN_LOG table
18. create sequence SEQ_LOGIN_LOG_ID
19. increment by 1
20. maxvalue 999999999999
21. minvalue 100000000000;
6. Insert 3 historical figures into the T_USER user table as initial users, and run the following sql code in the SQL> command.
Code Listing 2 Insert 3 records into the T_USER table
1. insert into T_USER(USER_ID,USER_NAME,PASSWORD) values('100000','Jiang Ziya','123456');
2. insert into T_USER(USER_ID,USER_NAME,PASSWORD) values('100001','Bao Shuya','123456');
3. Insert into T_USER(USER_ID,USER_NAME,PASSWORD) values('100002','试gna','123456');
4. commit;
create the project and Web module.
After creating the database, open JBuilder and create the project and Web module.
1. File->New Project...Create a project named bookstore.
2. File->New...->Web->Double-click the Web Module (WAR) icon to create a Web module named webModule. Tomcat 5.0 is selected as the Web application server.
When writing a class to obtain a database connection
, you must access the database through a data connection. Database connections need to be obtained in multiple places in the module, so we write a class to obtain a data connection to enhance code reuse.
When writing a class to obtain a data connection, you must first add Oracle's JDBC driver class package classes12.jar to the project extension class library (classes12.jar is located in the directory of <oracle installation directory>/jdbc/lib). We will add classes12.jar. The jar is placed under <project directory>/oraJdbcLib. Use the following steps to introduce classes12.jar into the project extension class library:
Project->Properties...->Paths settings page->Switch to Required Libraries->Click Add...->Switch to the Archives tab in the pop-up Add to Project Classpath dialog box, and select <Project in the project directory Directory>/oraJdbcLib/classes12.jar.
After introducing Oracle's JDBC driver class package classes12.jar into the project extension class library, create the DBConnection class in the project, and its code is as follows:
Code Listing 3 DBConnection.java
1. package bookstore;
2.
3. import java.sql.*;
4. import java.util.Properties;
5.
6. public class DBConnection {
7. //Get the database connection class
8. public static Connection getConnection() throws SQLException {
9. try {
10. Class.forName("oracle.jdbc.driver.OracleDriver");
11. } catch (ClassNotFoundException ex) {
12. ex.printStackTrace();
13. return null;
14. }
15. Properties sysProps = new Properties();
16. sysProps.put("user", "jbuser");
17. sysProps.put("password", "abc");
18. return DriverManager.getConnection(
19. "jdbc:oracle:thin:@192.168.0.128:1521:ora9i", sysProps);
20. }
21. }
This class only provides a static method getConnection(), use jbuser/abc to obtain the data connection located at 192.168.0.128, SID is ora9i.
There are two key points in obtaining a database connection:
1. Specify the database driver class
as shown in line 10 of the code. Oracle's JDBC driver class name is: oracle.jdbc.driver.OracleDriver. Different databases have their own JDBC database drivers. If you If you use other databases, please check the relevant information yourself.
2. Specify the URL connection string of the database.
In line 19, we specify a database URL connection string. The format of the URL connection string of different databases is also different. For Oracle database, the database URL connection string contains 4 parts:
· jdbc:oracle:thin: Specify the type of JDBC driver. The thin client driver is specified here. There is no need to install other components on the connecting client. It is the most commonly used.
·@192.168.0.128 : The IP of the machine where the database is located, or the machine name.
·1521: The port where the database listener is located. Generally, Oracle defaults to port 1521.
·ora9i: Database SID name.