How to connect to and operate Oracle databases using JDBC

How to connect to and operate Oracle databases using JDBC

Learn before. NET, ODBC was used to connect to the database, while JDBC is usually used in Java to connect to the database. Here we take the oracle database as an example to briefly summarize how to connect to and operate the database using JDBC.

1. Connection

 

Public class DbUtil {public static Connection getConnection () {Connection conn = null; try {Class. forName ("oracle. jdbc. driver. oracleDriver "); // find the oracle drive class String url =" jdbc: oracle: thin: @ localhost: 1521: bjpowernode "; // URL String username = "drp"; String password = "drp"; conn = DriverManager. getConnection (url, username, password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return conn ;}

public static void close(PreparedStatement pstmt){ if(pstmt !=null){ try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs){ if(rs !=null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

}

 

When executing Class. forName, you need to find the address of OracleDriver. The path is:

 

1. First find

D: \ oracle \ product \ 10.2.0 \ db_1 \ jdbc \ lib find ojdbc14.jar

2. Find the driver file under ojdbc14.jar \ oracle \ jdbc \ driver.

2. operate databases -- add

 

Public void addUser (User user) {String SQL = "insert into t_user (user_id, user_name, PASSWORD, CONTACT_TEL, EMAIL, CREATE_DATE) values (?,?,?,?,?,?) ";//? For the parameter placeholder Connection conn = null; PreparedStatement pstmt = null; // you can use PreparedStatement to optimize the performance. try {conn = DbUtil. getConnection (); pstmt = conn. prepareStatement (SQL); pstmt. setString (1, user. getUserId (); pstmt. setString (2, user. getUserName (); pstmt. setString (3, user. getPassword (); pstmt. setString (4, user. getContactTel (); pstmt. setString (5, user. getEmail (); // pstmt. setTimestamp (6, new Timestamp (System. currentTimeMillis (); pstmt. setTimestamp (6, new Timestamp (new Date (). getTime (); // obtain the current system time pstmt.exe cuteUpdate (); // execute the add, delete, modify, and delete operation} catch (SQLException e) {e. printStackTrace ();} finally {DbUtil. close (conn); DbUtil. close (pstmt );}}

 

3. Database Operations-Query

 

Public User findUserById (String userId) {String SQL = "select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id =? "; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // defines the User defined cuteQuery () that stores the query results (); // execute the query operation if (rs. next () {user = new User (); user. setUserId (rs. getString ("user_Id"); user. setUserName (rs. getString ("user_name"); user. setPassword (rs. getString ("password"); user. setContactTel (rs. getString ("contact_Tel"); user. setEmail (rs. getString ("email"); user. setCreateDate (rs. getTimestamp ("create_date") ;}} catch (SQLException e) {e. printStackTrace ();} finally {// disable DbUtil in sequence. close (rs); DbUtil. close (pstmt); DbUtil. close (conn);} return user ;}

 

Iv. Summary 1. Simple differences between PreparedStatement and Statement generate an execution plan for an SQL Statement. If the parameter value is different, different SQL statements are generated and the number of times corresponding parameter values are executed. If only one SQL Statement is executed, use Statement. PreparedStatement uses the Bind Variable to reuse the execution plan. Only one SQL statement is generated for the query statement corresponding to different parameter values, which greatly improves the execution efficiency. Is pre-compiled. When a large number of statements are operated, the efficiency is improved '? 'To represent parameters, which can prevent SQL injection and improve security. 2. Contact with the previous knowledge. Currently, the Connection object in JDBC has Connection, which is the same as the Connection of ODBC and is the database Connection object, the PraparedStatement and Statement are similar to the command object in ODBC and are used to execute SQL statements. The ResultSet used in the query method is similar to the DataSet or able function used previously. In this way, it seems that the connection and usage process have become much simpler!