Oracle database creates a table and adds some data first
1. Create a student table in the Oracle database first:
create table student( id number(11) not null primary key, stu_name varchar(16) not null, gender number(11) default null, age number(11) default null, address varchar(128) default null);
2. Add some data to the table
insert into student values('1','王小军','1','17','北京市和平里七区30号楼7门102')
Write java code in MyEclipse
1. Import ojdbc6.jar into the project
First create a project, then right-click on the project with the mouse -->new-->folder;folder name:lib; This creates a folder lib in the project; and then import the ojdbc6.jar package into the folder
The download address of this package is: http://wd.VeVB.COM:81//201612/yuanma/ojdbc6_jb51.rar
Move the mouse to the package; right-click --> 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 connection// 192.168.0.X is the native address (to change it to your own IP address), 1521 port number, XE is the default database name of the simplified version of Oracle private static String USERNAMR = "orcl"; private static String PASSWORD = "orcl"; private static Strategy DRVIER = "oracle.jdbc.OracleDriver"; private static String URL = "jdbc:oracle:thin:@192.168.0.X:1521:xe"; // Create a database connection Connection connection = null; // Create a precompiled statement object, generally use this instead of Statement PreparedStatement pstm = null; // Create a result set object ResultSet rs = null; /** * Add data to the database* First get 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 means male, 2 means 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','Gate 7, Building 30, Hepingli District 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 database student table pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); 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); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * Delete data to the database* @param stuName: Delete data according to name*/ public void DeleteData(String stuName) { connection = getConnection(); String sqlStr = "delete from student where stu_name=?"; System.out.println(stuName); try { // Perform the data deletion operation pstm = connection.prepareStatement(sqlStr); pstm.setString(1, stuName); pstm.executeUpdate(); } 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 address) { 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 database student table pstm = connection.prepareStatement(sql); pstm.setString(1, stuName); rs = pstm.executeQuery(); while (rs.next()) { count = rs.getInt(1); System.out.println(rs.getInt(1)); } // Perform 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); pstm.executeUpdate(); } 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 = pstm.executeQuery(); 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 employees where 1 = 1"; int count = 0; try { pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); 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* * @return */ 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) { /** * Addition, deletion, modification and search are completed, but there are certain limitations* 1. There is no big problem with addition* 2. Deletion requires giving a value to delete (the value may not exist-->There is no processing mechanism, how to deal with the value that is not unique?) * 3. The problem of changing and deleting the same * 4. The problem of checking is not big */ //Create OperateOracle object OperateOracle oo=new OperateOracle(); //Test the data operation//oo.AddData("Sun Yat-sen", 1, 25, "No. 111, Hongqi Road, Haidian District, Beijing"); //Test the data operation//oo.DeleteData("Sun Yat-sen"); //Test update data operation oo.UpdateData("Sun Yat-sen", 1, 30,"No. 11 Yueshan Road, Dongcheng District, Beijing"); //Test query data operation//oo.SelectData(); //Test ResultSetMetaData class//oo.SelectData2(); }}As noted in the test class, you can only connect to the Oracle database in the correct way, and operate the addition, deletion, modification and search operations, but the processing mechanism for some wrong operations is not perfect enough.
The above is all the content of this article. I hope that the content of this article will be of some help to everyone’s study or work. I also hope to support Wulin.com more!