JAVA使用JDBC连接ORACLE数据库基础版

---

title: JAVA使用JDBC连接ORACLE数据库基础版
date: 2023-08-17
categories:

• 数据库
  tags:
• 数据库
• 总结
• Oracle
• jdbc

---

JAVA使用JDBC连接ORACLE数据库基础版

JAVA使用JDBC连接ORACLE数据库基础版

JAVA使用JDBC连接ORACLE数据库改进优化版

构建工厂优化JAVA使用JDBC连接ORACLE数据库

数据库jdbc连接一共分一下几步

maven的oracle依赖

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>

1、加载驱动

Class.forName(driver);

2、数据库链接

//连接数据库的地址，用户名和密码
String url = "jdbc:oracle:thin:@localhost:1521:orcl1";
String name = "hr";
String pass = "orcl";
conn = DriverManager.getConnection(url,name,pass);

3、(statement定义语句对象，sql语句)

Statement st = con.createStatement();
//定义一个sql语句
String sql = "select department_id,department_name,location_id from departments";

4、执行sql语句

ResultSet rs = st.executeQuery(sql);

5、调用结果集的方法实现遍历

while(rs.next()){
	//获得结果集的数据,下标从1开始，1表示一行第一列，2表示第二列

    int id = rs.getInt(1);
    String name = rs.getString(2);
    String pass = rs.getString(3);
    System.out.println("部门号是："+id+"	部门名是："+name+"	部门所在地是："+pass);

}

6、关闭资源

rs.close();
ps.close();
conn.close();

案例

Department属性类

package vo;

public class Department {
	private int departmentId;
	private String departmentName;
	private int manager_id;
	private int location_id;
	
	public int getDepartmentId() {
		return departmentId;
	}
	public void setDepartmentId(int departmentId) {
		this.departmentId = departmentId;
	}
	public String getDepartmentName() {
		return departmentName;
	}
	public void setDepartmentName(String departmentName) {
		this.departmentName = departmentName;
	}
	public int getManager_id() {
		return manager_id;
	}
	public void setManager_id(int manager_id) {
		this.manager_id = manager_id;
	}
	public int getLocation_id() {
		return location_id;
	}
	public void setLocation_id(int location_id) {
		this.location_id = location_id;
	}
	public String toString(){
		return "id:"+departmentId+"  name:"+departmentName;
	}
}

DepartmentsDao定义jdbc并测试

package dao;

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

import vo.Department;

public class DepartmentsDao {

	//实现连接数据库查询所有的部门信息
	public void getAllDepartments(){		
		try {
			//连接数据库的地址，用户名和密码
			String url = "jdbc:oracle:thin:@localhost:1521:orcl1";
			String name = "hr";
			String pass = "orcl";
			//1,加载数据库驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2,获得连接
			Connection con = DriverManager.getConnection(url, name, pass);
			//3定义语句对象
			Statement st = con.createStatement();
			//定义一个sql语句
			String sql = "select department_id,department_name,location_id from departments";
			//4，执行sql语句，查询
			ResultSet rs = st.executeQuery(sql);
			//调用结果集的next方法实现遍历
			while(rs.next()){
				//获得结果集的数据
				int id = rs.getInt("department_id");
				String dname = rs.getString("department_name");
				int lid = rs.getInt("location_id");
				System.out.println(id+" "+dname+" "+lid);
			}
			//5,关闭资源
			rs.close();
			st.close();
			con.close();
		}
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//根据id和名字查询信息
	public Department getDepartmentById(int id,String departmentName){
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			//连接数据库的地址，用户名和密码
			String url = "jdbc:oracle:thin:@localhost:1521:orcl1";
			String name = "hr";
			String pass = "orcl";
			//1,加载数据库驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2,获得连接
			 con = DriverManager.getConnection(url, name, pass);
			//3定义语句对象
			st = con.createStatement();
			//定义一个sql语句
			String sql = "select * from departments where department_id="+id+" and department_name='"+departmentName+"'";
			//4，执行sql语句，查询
			rs = st.executeQuery(sql);
			//调用结果集的next方法实现遍历
			if(rs.next()){
				Department d = new Department();
				d.setDepartmentId(rs.getInt(1));
				d.setDepartmentName(rs.getString(2));
				d.setManager_id(rs.getInt(3));
				d.setLocation_id(rs.getInt(4));
				return d;
			}
			
		}
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				st.close();
				con.close();
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		DepartmentsDao dao = new DepartmentsDao();
		//dao.getAllDepartments();
		Department d = dao.getDepartmentById(60, "IT");
		System.out.println(d);
	}

}