Connect java to the Oracle database and the javaoracle Database

Connect java to the Oracle database and the javaoracle Database

Create a table and add some data to the Oracle database first.

1. Create a student table in the Oracle database:

1 create table student2 (3        id number(11) not null primary key,4        stu_name varchar(16) not null,5        gender number(11) default null,6        age number(11) default null,7        address varchar(128) default null8 );

2. Add some data to the table

Insert into student values ('1', 'wang Xiaojun ', '1', '17', 'door 7, building 30, Heping District 7, Beijing ')

Compile java code in MyEclipse

1. Import ojdbc6.jar to the project.

Create a project, right-click the project, and choose --> new --> folder; folder name: lib; To create a folder lib in the project; import the ojdbc6.jar package to this folder.

Share this package URL: Link: http://pan.baidu.com/s/1eRJbTMq password: eofs

Move the mouse over the package, right-click, and choose build path> add to build path;

2. Create a class and start Encoding

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; public class OperateOracle {// define the string required for the connection // 192.168.0.X is the local address (to change to your own IP address), port number 1521, XE is the default database name of lite version Oracle, private static String USERNAMR = "orcl"; private static String PASSWORD = "orcl"; private static String DRVIER = "orac Le. jdbc. oracleDriver "; private static String URL =" jdbc: oracle: thin: @ 192.168.0.X: 1521: xe "; // create a database Connection connection = null; // create a pre-compiled Statement object, which is generally used instead of Statement PreparedStatement pstm = null; // create a result set object ResultSet rs = null; /*** add data to the database * first obtain the total number of data in the table. The total number + 1 is the id value of the newly added data * @ param stuName: Student name * @ param gender: student gender, 1 indicates male, 2 indicates female * @ param age: Student age * @ param address: Student address */public void AddData (String StuName, int gender, int age, String address) {connection = getConnection (); // String SQL = // "insert into student values ('1', 'wang Xiaojun ', '1', '17', 'door 7, building 30, Heping Li 7, Beijing') "; String SQL =" select count (*) from student where 1 = 1 "; string sqlStr = "insert into student values (?,?,?,?,?) "; Int count = 0; try {// calculate the total number of data in the student table of the database pstm = connection. prepareStatement (SQL); rs = p0000.exe cuteQuery (); while (rs. next () {count = rs. getInt (1) + 1; System. out. println (rs. getInt (1);} // execute the insert data operation pstm = connection. prepareStatement (sqlStr); pstm. setInt (1, count); pstm. setString (2, stuName); pstm. setInt (3, gender); pstm. setInt (4, age); pstm. setString (5, address); p0000.exe cuteUpdate ();} ca Tch (SQLException e) {e. printStackTrace () ;}finally {ReleaseResource () ;}/ *** delete data from the database * @ param stuName: delete data by name */public void DeleteData (String stuName) {connection = getConnection (); String sqlStr = "delete from student where stu_name =? "; System. out. println (stuName); try {// execute the delete data operation pstm = connection. prepareStatement (sqlStr); pstm. setString (1, stuName); pcmd.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace () ;}finally {ReleaseResource () ;}/ *** modify data to the database * @ param stuName: Student name, query the value of a row to be modified based on this value * @ param gender * @ param age * @ param address */public void UpdateData (String stuName, int gender, int age, String addres S) {connection = getConnection (); String SQL = "select id from student where 1 = 1 and stu_name =? "; String sqlStr =" update student set stu_name = ?, Gender = ?, Age = ?, Address =? Where id =? "; Int count = 0; try {// calculate the total number of data in the student table of the database pstm = connection. prepareStatement (SQL); pstm. setString (1, stuName); rs = p0000.exe cuteQuery (); while (rs. next () {count = rs. getInt (1); System. out. println (rs. getInt (1);} // execute the insert data operation pstm = connection. prepareStatement (sqlStr); pstm. setString (1, stuName); pstm. setInt (2, gender); pstm. setInt (3, age); pstm. setString (4, address); pstm. setInt (5, count); ps Tm.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace () ;} finally {ReleaseResource () ;}/ *** query data in the database */public void SelectData () {connection = getConnection (); string SQL = "select * from student where 1 = 1"; try {pstm = connection. prepareStatement (SQL); rs = p0000.exe cuteQuery (); while (rs. next () {String id = rs. getString ("id"); String name = rs. getString ("stu_name"); String Gender = rs. getString ("gender"); String age = rs. getString ("age"); String address = rs. getString ("address"); System. out. println (id + "\ t" + name + "\ t" + gender + "\ t" + age + "\ t" + address) ;}} catch (SQLException e) {e. printStackTrace () ;} finally {ReleaseResource () ;}/ *** use ResultSetMetaData to calculate the number of columns */public void SelectData2 () {connection = getConnection (); string SQL = "select * from em Ployees where 1 = 1 "; int count = 0; try {pstm = connection. prepareStatement (SQL); rs = p0000.exe cuteQuery (); while (rs. next () {count ++;} ResultSetMetaData rsmd = rs. getMetaData (); int cols_len = rsmd. getColumnCount (); System. out. println ("count =" + count + "\ tcols_len =" + cols_len);} catch (SQLException e) {e. printStackTrace ();} finally {ReleaseResource () ;}/ *** get Connection object ** @ retu Rn */public Connection getConnection () {try {Class. forName (DRVIER); connection = DriverManager. getConnection (URL, USERNAMR, PASSWORD); System. out. println ("successfully connected to the database");} catch (ClassNotFoundException e) {throw new RuntimeException ("class not find! ", E);} catch (SQLException e) {throw new RuntimeException (" get connection error! ", E);} return connection;}/*** release resource */public void ReleaseResource () {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (pstm! = Null) {try {pstm. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (connection! = Null) {try {connection. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

3. Create a test class

Public class Test {public static void main (String [] args) {/*** added, deleted, modified, and queried, but some limitations exist. * 1. not much increased * 2. to delete a value, you must delete it (the value may not exist --> there is no processing mechanism and the value is not unique. How can this problem be solved ?) * 3. change the same deletion issue * 4. no problem found * // create OperateOracle object OperateOracle oo = new OperateOracle (); // Add data operations to the test // oo. addData ("Sun yat-sen", 111, "No. Hongqi Road, Haidian District, Beijing"); // test the data deletion operation // oo. deleteData ("Sun yat-sen"); // test the data update operation oo. updateData ("Sun yat-sen", "No. 11 yueshan Road, Dongcheng District, Beijing"); // test the Data Query operation // oo. selectData (); // test the ResultSetMetaData class // oo. selectData2 ();}}

As noted in the test class, you can only connect to the Oracle database in the correct way to add, delete, modify, and query operations. However, the processing mechanism for some incorrect operations is not complete.