JDBC操作oracle

首先需要oracle的驱动jar包。剩下的步骤和mysql相仿。例：

package com.store.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DbUtil {private String uri;private String name;private String pwd;private Connection conn;public DbUtil(String uri, String name, String pwd) {super();this.uri = uri;this.name = name;this.pwd = pwd;}static {try {Class.forName("oracle.jdbc.driver.OracleDriver");} catch (Exception e) {}}public Connection getConn() {try {conn=DriverManager.getConnection(uri, name, pwd);} catch (SQLException e) {e.printStackTrace();}return conn;}//增、删、改public int updateExeuce(String sql,Object...paras){getConn();int a=-1;try{PreparedStatement ps=conn.prepareStatement(sql);putParas(ps,paras); a=ps.executeUpdate();}catch (Exception e) {// TODO: handle exception}return a;}//查public ResultSet query(String sql,Object...paras){getConn();ResultSet set=null;try{PreparedStatement ps=conn.prepareStatement(sql);putParas(ps, paras); set=ps.executeQuery();}catch (Exception e) {// TODO: handle exception}return set;}private void putParas(PreparedStatement ps, Object[] paras) {for(int i=0;i<paras.length;i++){try {ps.setObject(i+1, paras[i]);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

测试商品信息：

package com.store.main;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;import com.store.util.DbUtil;public class StoreTest {public static void main(String[] args) throws SQLException {String uri="jdbc:oracle:thin:@172.16.50.12:1521:orcl";String username="wtyy";String pwd="bb";DbUtil dbUtil=new DbUtil(uri, username, pwd);Connection conn=dbUtil.getConn();System.out.println("请选择操作:1.查看商品;2.查看订单;3.查询在当月的商品分类，销售的百分比；4.退出请输入bye");Scanner scanner=new Scanner(System.in);String str=scanner.nextLine();while(!str.equals("bye")){if(str.equals("1")){String sql="select * from goods order by price desc,goodsname";ResultSet set=dbUtil.query(sql);if(set!=null){while(set.next()){System.out.println("商品编号: "+set.getString("goodsno")+"  商品名称:  "+set.getString("goodsname")+"     商品价格: "+set.getString("price")+"  商品分类: "+set.getString("classfiy"));}}str=scanner.nextLine();}if(str.equals("2")){System.out.println("输入日期查看订单，如输入2017-08-05");String time=scanner.nextLine();String sql="select orderno ,goodsname, ordertime,price,sumprice from orders inner join goods"+ " on orders.goodsno=goods.goodsno and ordertime=to_date(?,'YYYY-mm-dd')";ResultSet set=dbUtil.query(sql,time);if(set!=null){while(set.next()){System.out.println("订单编号: "+set.getString("orderno")+"  商品名称:  "+set.getString("goodsname")+"     订单时间: "+set.getString("ordertime")+"  商品价格: "+set.getString("price")+"    订单价格："+set.getString("sumprice"));}}str=scanner.nextLine();}if(str.equals("3")){String sql="select classfiy,sum(sumprice) as allcount,sumprice/(select sum(sumprice)  from orders) as bfb from orders inner "+"join  goods on goods.goodsno=orders.goodsno group by classfiy,sumprice";ResultSet set=dbUtil.query(sql);if(set!=null){while(set.next()){System.out.println("商品分类: "+set.getString("classfiy")+"  销售总额:  "+set.getString("allcount")+" 占百分比: "+set.getString("bfb"));}}str=scanner.nextLine();}}}}

测试系统当前时间：

package com.store.main;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import com.store.util.DbUtil;public class T {public static void main(String[] args) throws SQLException {String uri="jdbc:oracle:thin:@172.16.50.12:1521:orcl";String username="wtyy";String pwd="bb";DbUtil dbUtil=new DbUtil(uri, username, pwd);Connection conn=dbUtil.getConn();String sql="select to_char(sysdate,'YYYY-MM-DD hh24:mm:ss') as sj from dual";ResultSet set=dbUtil.query(sql);while(set.next()){System.out.println(set.getString("sj"));}}}