JDBC使用

在工作中碰到要向另一个数据库进行操作的需求，例如数据源为mysql的工程某个方法内需要向oracle数据库进行某些查询操作

接口类

package com.y.erp.pur.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.y.erp.pur.entity.PqhFile;
import com.y.erp.sal.entity.PhxpFile;
import com.y.erp.yBase.entity.ItemFile;


public class OraclePqpUtil {
    public static SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
    public static Connection getConnection(){
        Connection connection=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@10.0.20.21:1521:TOPDB";
            String username="t41";
            String password="t41";
            connection=DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //插入
    public static String insertPmr(PqhFile pqhFile,String bnun,String id) throws SQLException{
        //拼接sql
        String pqhsql=pqhSql(pqhFile,id);
        Connection con = getConnection();
        //注明手动提交事务
        con.setAutoCommit(false);
        Statement stmt = con.createStatement();
        StringBuffer sql=null;
        sql=new StringBuffer();
        sql.append("insert into ");
        sql.append(bnun+".PMR_FILE ");
        sql.append(pqhsql);
        try {
            stmt.executeUpdate(sql.toString());
        } catch (Exception e) {
            con.rollback();
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
            return null;
        }
        con.commit();
        if (stmt != null) {
            stmt.close();
        }
        if (con != null) {
            con.close();
        }
        return id;
    }
        
        //主表插入sql
        public static String pqhSql(PqhFile pqhFile,String id) {
            StringBuffer values=new StringBuffer();
            StringBuffer rowSql=new StringBuffer();
            StringBuffer allSql=new StringBuffer();
            allSql.append("(");
            if(null!=pqhFile.getPqh01()) {
                rowSql.append("pmr01,");
                values.append("'"+id+"',");
            }
            if(null!=pqhFile.getPqh02()) {
                rowSql.append("pmr02,");
                values.append("'"+pqhFile.getPqh02()+"',");
            }
            if(null!=pqhFile.getPqh03()) {
                rowSql.append("pmr03,");
                values.append("'"+pqhFile.getPqh03()+"',");
            }
            if(null!=pqhFile.getPqh04()) {
                rowSql.append("pmr04,");
                values.append("'"+pqhFile.getPqh04()+"',");
            }
            if(null!=pqhFile.getPqh05()) {
                rowSql.append("pmr05,");
                values.append("'"+pqhFile.getPqh05()+"',");
            }
            if(null!=pqhFile.getPqh05t()) {
                rowSql.append("pmr05t,");
                values.append("'"+pqhFile.getPqh05t()+"',");
            }
            allSql.append(rowSql.toString().substring(0,rowSql.toString().length()-1));
            allSql.append(") VALUES (");
            allSql.append(values.toString().substring(0,values.toString().length()-1));
            allSql.append(")");
            return allSql.toString();
        }
        //查询
        public static ItemFile getItemTt(PhxpFile phxp,String bnun) throws SQLException, ParseException {
            String querySql="SELECT * FROM "+bnun+".IMA_FILE where IMA01 = '" +phxp.getPhxp14()+"'";
            Connection con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(querySql);
            ItemFile item =new ItemFile();
            while (rs.next()) {
                item.setItem01(rs.getString("IMA01"));
                item.setItem02(rs.getString("IMA02"));
                item.setItem03(rs.getString("IMA03"));
                item.setItem04(rs.getString("IMA04"));
                item.setItem05(rs.getString("IMA05"));
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
            return item;
        }
        
}

pom.xml

<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.1.0.7.0</version>
        </dependency>