JAVA使用JDBC连接ORACLE数据库封装查询语句

import java.sql.*;
import java.util.*;
import java.util.Date;

public class DatabaseManupulate {
    public static void main(String[] args) throws ClassNotFoundException
    {
        databaseConnection();
    }

    public static void forEachIterator(List<Emp> list)
    {
        Iterator<Emp> i = list.iterator();
        while (i.hasNext())
        {
            Emp e = i.next();
        System.out.println("Empno   "+"Ename    "+"Job   "+"Mgr   "+"HireDate   "+"Sal   "+"Comm   "+"Deptno");
        System.out.println(e.getEmpno()+"   "+e.getEname()+"   "+e.getJob()+"   "+e.getMgr()+"   "+e.getHireDate()+"   "+e.getSal()+"   "+e.getComm()+"   "+e.getDeptno());
        }
    }

    public static void forEachAdvFor(List<Emp> list)
    {
        for(Emp e : list)
        {
            System.out.println(e.toString());
        }
    }

    public static void databaseConnection() throws ClassNotFoundException {
        
        //成员变量声明在try catch外面为了在finally中释放资源
        Connection conn = null;
        Statement s = null;
        ResultSet result = null;
        String sqlDeptAll = null;
        List<Emp> list = null;


        try
        {
            //1.注册驱动
            Class.forName("oracle.jdbc.OracleDriver");
            //2.获取驱动对象
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.150:1521:orcl11g","scott","scott");
            System.out.println(conn);
            //获取进行的sql
            s = conn.createStatement();
            //定义进行sql的内容
            sqlDeptAll = "select * from scott.emp";
            //获得结果集
            result = s.executeQuery(sqlDeptAll);

            ResultSetMetaData rsmd = result.getMetaData();

            //遍历结果集，封装对象
            list = new ArrayList<Emp>();
            while (result.next())
            {
                int empno = result.getInt(1);
                String ename = result.getString(2);
                String job = result.getString(3);
                int mgr = result.getInt(4);
                Date hireDate = result.getDate(5);
                double sal = result.getDouble(6);
                double comm = result.getDouble(7);
                int deptno = result.getInt(8);
                Emp e = new Emp();
                e.setEmpno(empno);
                e.setEname(ename);
                e.setJob(job);
                e.setMgr(mgr);
                e.setHireDate(hireDate);
                e.setSal(sal);
                e.setComm(comm);
                e.setDeptno(deptno);
                list.add(e);
            }
            forEachAdvFor(list);
        }catch (ClassNotFoundException e)
        {
            e.getStackTrace();
        }catch (SQLException ex)
        {
            ex.getStackTrace();
        }finally
        {
            if(conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(s != null)
            {
                try
                {
                    s.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(result != null)
            {
                try
                {
                    result.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
    }
}

class Emp
{
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hireDate;
    private double sal;
    private double comm;
    private int deptno;

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hireDate=" + hireDate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }

    public double getSal() {
        return sal;
    }

    public void setSal(double sal) {
        this.sal = sal;
    }

    public double getComm() {
        return comm;
    }

    public void setComm(double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

}

遍历表输出：


Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hireDate=1980-12-17, sal=800.0, comm=0.0, deptno=20}
Emp{empno=7499, ename='ALLEN', job='SALESMAN', mgr=7698, hireDate=1981-02-20, sal=1600.0, comm=300.0, deptno=30}
Emp{empno=7521, ename='WARD', job='SALESMAN', mgr=7698, hireDate=1981-02-22, sal=1250.0, comm=500.0, deptno=30}
Emp{empno=7566, ename='JONES', job='MANAGER', mgr=7839, hireDate=1981-04-02, sal=2975.0, comm=0.0, deptno=20}
Emp{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hireDate=1981-09-28, sal=1250.0, comm=1400.0, deptno=30}
Emp{empno=7698, ename='BLAKE', job='MANAGER', mgr=7839, hireDate=1981-05-01, sal=2850.0, comm=0.0, deptno=30}
Emp{empno=7782, ename='CLARK', job='MANAGER', mgr=7839, hireDate=1981-06-09, sal=2450.0, comm=0.0, deptno=10}
Emp{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hireDate=1987-04-19, sal=3000.0, comm=0.0, deptno=20}
Emp{empno=7839, ename='KING', job='PRESIDENT', mgr=0, hireDate=1981-11-17, sal=5000.0, comm=0.0, deptno=10}
Emp{empno=7844, ename='TURNER', job='SALESMAN', mgr=7698, hireDate=1981-09-08, sal=1500.0, comm=0.0, deptno=30}
Emp{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hireDate=1987-05-23, sal=1100.0, comm=0.0, deptno=20}
Emp{empno=7900, ename='JAMES', job='CLERK', mgr=7698, hireDate=1981-12-03, sal=950.0, comm=0.0, deptno=30}
Emp{empno=7902, ename='FORD', job='ANALYST', mgr=7566, hireDate=1981-12-03, sal=3000.0, comm=0.0, deptno=20}
Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hireDate=1982-01-23, sal=1300.0, comm=0.0, deptno=10}

Process finished with exit code 0

封装两个方法可以对表中的列和行数进行统计。

查询表行数：

    public static int getTableRowCount(String sql) throws SQLException,ClassNotFoundException
    {
        int i = 0;
        //获得表行数,调用一次result.next()方法指针自动移动向表数据的下一行
        Connection conn = null;
        Statement s = null;
        ResultSet rs = null;

        try {
            conn = databaseConnection();
            s = conn.createStatement();
            rs = s.executeQuery(sql);
            while (rs.next())
            {
                i++;
            }
            System.out.println("table rowcount:"+i);
            return i;
        }catch (SQLException e)
        {
            e.getStackTrace();
        }finally
        {
            if(conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(s != null)
            {
                try
                {
                    s.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
        return i;
    }

查询表列数：

    public static int getTableColumnCount(String sql) throws SQLException,ClassNotFoundException
    {
        //获得表列数
        Connection conn = null;
        Statement s = null;
        ResultSet rs = null;

        try {
        conn = databaseConnection();
        s = conn.createStatement();
        rs = s.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        System.out.println("column count:"+rsmd.getColumnCount());
        return rsmd.getColumnCount();
        }catch (SQLException e)
        {
            e.getStackTrace();
        }finally
        {
            if(conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(s != null)
            {
                try
                {
                    s.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if(rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }

输出：

table rowcount:14
column count:8

Process finished with exit code 0

原文链接：https://blog.csdn.net/xielong8866/article/details/128500862

你可能也喜欢