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

小明的学习圈子2023-08-17数据库总结 Oracle jdbc

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

JAVA使用JDBC连接ORACLE数据库基础版open in new window

JAVA使用JDBC连接ORACLE数据库改进优化版open in new window

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

数据库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);
	}

}
Last Updated 2024/4/6 10:47:15