小明的学习圈子
Series
Appearance

❤️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

maven的oracle依赖

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

DBtool封装

package xin.dbtool;

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

public class DBtool {
	static String driver = "oracle.jdbc.driver.OracleDriver";
	static String url = "jdbc:oracle:thin:localhost:1521:ORCL";
	static String name = "MyTest";
	static String pass = "mytest";

	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, name, pass);
		return conn;
	}

	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}

	}

	public static void close(Connection conn, PreparedStatement ps) throws SQLException {

		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}

	}

}

DeptTable属性类

package xin.model;

public class DeptTable {
	private int deptNo;
	private String dName;
	private String city;
	public DeptTable(){}
	public DeptTable(int deptNo,String dName,String city){
		this.deptNo = deptNo;
		this.dName = dName;
		this.city = city;
	}
	public DeptTable(String dName,String city){
		this.dName = dName;
		this.city = city;
	}
	
	/**
	 * @return the deptNo
	 */
	public int getDeptNo() {
		return deptNo;
	}
	/**
	 * @param deptNo the deptNo to set
	 */
	public void setDeptNo(int deptNo) {
		this.deptNo = deptNo;
	}
	/**
	 * @return the dName
	 */
	public String getdName() {
		return dName;
	}
	/**
	 * @param dName the dName to set
	 */
	public void setdName(String dName) {
		this.dName = dName;
	}
	/**
	 * @return the city
	 */
	public String getCity() {
		return city;
	}
	/**
	 * @param city the city to set
	 */
	public void setCity(String city) {
		this.city = city;
	}
	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return "部门号是："+deptNo+"	部门名是："+dName+"	部门所在地是："+city;
	}
	

}

DeptTableServices业务层

package xin.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import xin.dbtool.DBtool;
import xin.model.DeptTable;

public class DeptTableServices {
	 String driver = "oracle.jdbc.driver.OracleDriver";
	 String url = "jdbc:oracle:thin:localhost:1521:ORCL";
	 String name = "MyTest";
	 String pass = "mytest";
	 List<DeptTable> list = new ArrayList<DeptTable>();
	 Connection conn = null;
	 PreparedStatement ps = null;
	 ResultSet rs = null;
	 //查询所有部门信息，没用model里的类
	 public void showAllDept(){
		 try {
			 //1加载驱动
			Class.forName(driver);
			//2数据库链接
			conn = DriverManager.getConnection(url,name,pass);
			//3(statement定义语句对象，sql语句)
//			Statement st = conn.createStatement();
			//定义sql语句
			String sql ="select * from dept";
			//prepareStatement定义语句对象
			ps = conn.prepareStatement(sql);
//			rs = st.executeQuery(sql);
			//4执行sql语句
			rs = ps.executeQuery();
			//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();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
	 }
	 //查询所有部门信息，有到类
	 public List<DeptTable> showAllDept1(){
		 try {
			 //1加载驱动
			Class.forName(driver);
			//2数据库链接
			conn = DriverManager.getConnection(url,name,pass);
			//3(statement定义语句对象，sql语句)
//			Statement st = conn.createStatement();
			//定义sql语句
			String sql ="select * from dept";
			//prepareStatement定义语句对象
			ps = conn.prepareStatement(sql);
//			rs = st.executeQuery(sql);
			//4执行sql语句
			rs = ps.executeQuery();
			//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);
				//通过set赋值
//				DeptTable dept = new DeptTable();
//				dept.setDeptNo(rs.getInt(1));
//				dept.setdName(rs.getString(2));
//				dept.setCity(rs.getString(3));
				//简写形式构造器
				DeptTable dept = new DeptTable(rs.getInt(1),rs.getString(2),rs.getString(3));
				list.add(dept);
			}
			//6关闭资源
			rs.close();
			ps.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return list;
	 }
	 public boolean addDept(String...val){
		 try {
			conn = DBtool.getConnection();
			String sql = "insert into dept values(DEPT_ID_SEQ.NEXTVAL,?,?)";
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<val.length;i++){
				ps.setString(i+1,val[i]);
			}
			int rs = ps.executeUpdate();
			while(rs>0){
				return true;
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			finally{
				try {
					DBtool.close(conn, ps);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		 return false;
	 }
	 public boolean addDept(DeptTable dept){
		 try {
			conn = DBtool.getConnection();
			String sql = "insert into dept values(DEPT_ID_SEQ.NEXTVAL,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1,dept.getdName());
			ps.setString(2,dept.getCity());
			int rs = ps.executeUpdate();
			while(rs>0){
				return true;
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			finally{
				try {
					DBtool.close(conn, ps);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		 return false;
	 }

	 public boolean deleteDept(int id){
		 try {
			conn = DBtool.getConnection();
			String sql = "delete from dept where deptno = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,id);
			int rs = ps.executeUpdate();
			if(rs>0){
				return true;
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally{
			 try {
				DBtool.close(conn, ps);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 }
		 return false;
	 }
	 
	 public boolean updateDept(DeptTable dept){
		 try {
			conn = DBtool.getConnection();
			String sql = "update dept set deptno = DEPT_ID_SEQ.NEXTVAL,dname = ?,city = ? "
					+ "where deptno = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1,dept.getdName());
			ps.setString(2,dept.getCity());
			ps.setInt(3, dept.getDeptNo());
			int rs = ps.executeUpdate();
			if(rs>0){
				return true;
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally{
			 try {
				DBtool.close(conn, ps);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 }
		 return false;
	 }
}

DeptTest测试

package xin.test;

import java.util.List;

import xin.model.DeptTable;
import xin.services.DeptTableServices;

public class DeptTest {

	public static void main(String[] args) {
		DeptTableServices dept = new DeptTableServices();
		//查询方法
////		dept.showAllDept();
//		List<DeptTable>list = dept.showAllDept1();
//		for(DeptTable d : list){
//			System.out.println(d);
//		}
////		for(DeptTable d : list){
////			System.out.println(d.getDeptNo()+d.getdName()+d.getCity());
////		}

//       //添加方法
//		boolean falg = dept.addDept(new DeptTable("开发部","大连"));
//		if (falg){
//			System.out.println(" ADD OK");
//		}
//		else{
//			System.out.println(" ADD FALSE");
//		}
//	}

	//根据id删除方法
//	boolean dfalg = dept.deleteDept(1);
//	if(dfalg){
//		System.out.println("DELETE OK");
//	}
//	else{
//		System.out.println("DELETE FALSE");
//	}
		//修改方法
//		boolean falg = dept.updateDept(new DeptTable(10,"客户","沈阳"));
//		if(falg){
//			System.out.println("update ok");
//		}else{
//			System.out.println("update fasle");
//		}
		
		//动态
		String[] val={"hahaha","xixixi"};
		boolean f = dept.addDept(val);
		if (f){
		System.out.println(" ADD OK");
	}
	else{
		System.out.println(" ADD FALSE");
	}
	}	
}
Last Updated 2024/10/26 13:32:02
❤️目录❤️