小明的学习圈子
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>

定义db属性文件

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:localhost:1521:ORCL
user=MyTest
password=MyTest

定义工厂

package com.portsoft.iot.web;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionFactory {
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	static{
        //设置db文件的属性值
		driver= "oracle.jdbc.driver.OracleDriver";
		url="jjdbc:oracle:thin:localhost:1521:ORCL";
		user="xxx";
		password="xxxxx";
        //获取db文件的属性值
        //Properties p=new Properties();
        //p.load(ConnectionFactory.class.getResourceAsStream("db.properties"));
        //driver=p.getProperty("driver");
        //url=p.getProperty("url");
        //user=p.getProperty("user");
        // password=p.getProperty("password");
 
	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			// 1.注册驱动
			Class.forName(driver);
			// 2.建立连接
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public static void close(Connection conn, Statement stmt) {
		close(null, conn, stmt);
	}

	public static void close(ResultSet rs, Connection conn, Statement stmt) {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		
	}
}

JDBC工具类

package com.portsoft.iot.web;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCUtil {

	public static void update(String sql) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = ConnectionFactory.getConnection();
			stmt = conn.createStatement();
			stmt.execute(sql);
			ConnectionFactory.close(conn, stmt);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	public static void query(String sql,IWorkAdapter work) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = ConnectionFactory.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			//处理结果集
			work.processRs(rs);
			ConnectionFactory.close(rs,conn, stmt);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void execute_DML_ps(String sql, IWorkAdapter work) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = ConnectionFactory.getConnection();
			ps=conn.prepareStatement(sql);
			work.setValues(ps);
			ps.execute();
			//ps.executeBatch();
			ConnectionFactory.close(conn, ps);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	public static void execute_select_ps(String sql, IWorkAdapter work) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		try {
			conn = ConnectionFactory.getConnection();
			ps=conn.prepareStatement(sql);
			work.setValues(ps);
			rs = ps.executeQuery();
			work.processRs(rs);
			ConnectionFactory.close(rs,conn, ps);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
}

两个接口，一个用于处理结果集，一个用于设置参数

IWork

package com.portsoft.iot.web;
 
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public interface IWork {

	public void processRs(ResultSet rs);

	public void setValues(PreparedStatement ps);

	
		
}

IWorkAdapter

package com.portsoft.iot.web;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

public abstract class IWorkAdapter implements IWork{

	@Override
	public void processRs(ResultSet rs) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setValues(PreparedStatement ps) {
		// TODO Auto-generated method stub
		
	}
 
}

测试

	
public static void test(){
        String sqlS = "select * from RLZY.dd_rlzy_ykt WHERE type  =  ? ";
//        stmt = connection.prepareStatement(sql);
//         stmt.setString(1, "离职人员");
		JDBCUtil.execute_select_ps(sqlS, new IWorkAdapter() {
		 
			@Override
			public void setValues(PreparedStatement ps) {
				try {
					ps.setString(1,  "离职人员");
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
			@Override
			public void processRs(ResultSet rs) {
				try {
					//这里我定义的json，可以根据实际业务自由定义，但是定义json需要到jar包
					List<JSONObject> list = new ArrayList<>();
			        while (rs.next()) {
			        	JSONObject result = new JSONObject();
			        	
			         	result.put("name", rs.getString("name"));//t."姓名",
			        	result.put("phone", rs.getString("phone"));//t."手机电话",
       	
			        	list.add(result);
			        }
			        System.out.println(list);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		});
	}
Last Updated 2024/10/26 13:32:02
❤️目录❤️