Java操作Oracle

  我们一直在plsql中操作oracle，那么如何在java 程序中操作数据库呢？ 下面我们举例说明，写一个java，实现对emp表的增删查改。

 

java代码

User

/**
 *
 * @版权 : Copyright (c) 2017-2018 *********公司技术开发部
 * @author: gaozhenchao
 * @E-mail: 1226046769@qq.com
 * @版本: 1.0
 * @创建日期: 2019年1月10日 下午3:57:21
 * @ClassName User
 * @类描述-Description: TODO(这里用一句话描述这个方法的作用)
 * @修改记录:
 * @版本: 1.0
 */

public class User {

	private int id;
	private String name;
	private int deptId;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getDeptId() {
		return deptId;
	}

	public void setDeptId(int deptId) {
		this.deptId = deptId;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", deptId=" + deptId + "]";
	}

}

 

OjdbcUtil(提取的连接数据库工具类）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @版权 : Copyright (c) 2017-2018 *********公司技术开发部
 * @author: gaozhenchao
 * @E-mail: 1226046769@qq.com
 * @版本: 1.0
 * @创建日期: 2019年1月10日 下午3:57:06
 * @ClassName OjdbcUtil
 * @类描述-Description: TODO(这里用一句话描述这个方法的作用)
 * @修改记录:
 * @版本: 1.0
 */

public class OjdbcUtil {

	static {
		try {
			// 注册驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// 第二步： 获取与oracle的连接 Connection

	static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	static String user = "u_test";
	static String password = "1234";

	public static Connection connect() throws SQLException {

		return DriverManager.getConnection(url, user, password);

	}

	public static void close(ResultSet resultSet, PreparedStatement stmt, Connection conn) {
		System.out.println("关闭资源");
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException sqlex) {
				// ignore, as we can't do anything about it here
			}
			resultSet = null;
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException sqlex) {
				// ignore, as we can't do anything about it here
			}

			stmt = null;
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException sqlex) {
				// ignore, as we can't do anything about it here
			}

			conn = null;
		}
	}
}

 

OjdbcTest（测试类）

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

/**
 *
 * @版权 : Copyright (c) 2017-2018 *********公司技术开发部
 * @author: gaozhenchao
 * @E-mail: 1226046769@qq.com
 * @版本: 1.0
 * @创建日期: 2019年1月10日 下午3:41:50
 * @ClassName OjdbcTest
 * @类描述-Description: TODO(这里用一句话描述这个方法的作用)
 * @修改记录:
 * @版本: 1.0
 */

public class OjdbcTest {
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet resultSet = null;

	@Test
	public void testSelect() {

		try {
			conn = OjdbcUtil.connect();
			String sql = "select * from T_USER_INFO";
			stmt = conn.prepareStatement(sql);
			resultSet = stmt.executeQuery();
			while (resultSet.next()) {
				String id = resultSet.getString("USER_ID");
				String name = resultSet.getString("USER_NAME");
				String deptId = resultSet.getString("DEPT_ID");
				System.out.println("id:" + id + ",name:" + name + ",deptId:" + deptId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			OjdbcUtil.close(resultSet, stmt, conn);
		}
	}

	@Test
	public void testInsert() {
		// Connection conn = null;
		// PreparedStatement stmt = null;

		try {
			User user = new User();
			user.setId(7);
			user.setName("大仙");
			user.setDeptId(4);

			conn = OjdbcUtil.connect();
			String sql = "insert into T_USER_INFO(USER_ID,USER_NAME,DEPT_ID) values(?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setObject(1, user.getId());
			stmt.setObject(2, user.getName());
			stmt.setObject(3, user.getDeptId());
			int executeUpdate = stmt.executeUpdate();
			System.out.println("影响行数:" + executeUpdate + "行");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			OjdbcUtil.close(null, stmt, conn);
		}

	}

	@Test
	public void testUpdate() {
		// Connection conn = null;
		// PreparedStatement stmt = null;

		try {

			User user = new User();
			user.setId(3);
			user.setName("三大仙");
			conn = OjdbcUtil.connect();
			String sql = "update T_USER_INFO set USER_NAME=? where USER_ID = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setObject(1, user.getName());
			stmt.setObject(2, user.getId());
			int executeUpdate = stmt.executeUpdate();
			System.out.println("影响行数:" + executeUpdate + "行");
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			OjdbcUtil.close(null, stmt, conn);
		}
	}

	@Test
	public void testDelete() {
		// Connection conn = null;
		// PreparedStatement stmt = null;

		try {

			User user = new User();
			user.setId(7);

			conn = OjdbcUtil.connect();
			String sql = "delete from T_USER_INFO where USER_ID = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setObject(1, user.getId());
			int executeUpdate = stmt.executeUpdate();
			System.out.println("影响行数:" + executeUpdate + "行");
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			OjdbcUtil.close(null, stmt, conn);
		}
	}
}