java 与oracle数据库,java 连接Oracle数据库

用java连接Oracle数据库进行简单的查询

用sys登录进sql plus

创建用户

create user 用户 identified by 密码;

授权

grant connect,resource to 用户；

grant selet any dictionary to 用户;

grant OEM_MONITOR to 用户;

grant create any view to grant create any view to jk028;;

以jk...登录到sql plus上

conn jk...;

create table jk028.Student

(

Sno nvarchar2(16) not null,

Name nvarchar2(6),

Sex nchar(1),

Age smallint,

IdCard nvarchar2(20),

PoliticalStatus nvarchar2(6),

constraint pk_Sno primary key(Sno)

);

create table jk028.Course

(

Cno nvarchar2(16) not null,

CourseName nvarchar2(18),

Credit smallint,

constraint pk_Cno primary key(Cno)

);

create table jk028.SelectCourse

(

Sno nvarchar2(16) not null,

Cno nvarchar2(16) not null,

Score int,

constraint fk_Sno foreign key(Sno) references jk028.Student(Sno),

constraint fk_Cno foreign key(Cno) references jk028.Course(Cno)

);

create view jk028.V_学生成绩

as

select s.Sno,s.Name,s.Sex,s.IdCard,c.CourseName,c.Credit,sc.Score

from jk028.SelectCourse sc join jk028.Student s on sc.Sno=s.Sno join jk028.Course c on sc.Cno=c.Cno;

添加数据：

Student

insert into jk028.Student values('1060315014001','王1','男',20,'610203199701192911','共青团员');

insert into jk028.Student values('1060315014002','王2','女',20,'610203199701192912','共青团员');

insert into jk028.Student values('1060315014003','王3','男',20,'610203199701192913','共青团员');

insert into jk028.Student values('1060315014004','王4','女',20,'610203199701192914','共青团员');

insert into jk028.Student values('1060315014005','王5','男',20,'610203199701192915','共青团员');

Course

insert into jk028.Course values('0001','数据库',11);

insert into jk028.Course values('0002','马克思原理',12);

insert into jk028.Course values('0003','数据挖掘',13);

insert into jk028.Course values('0004','软件工程',14);

insert into jk028.Course values('0005','操作系统',15);

SelectCourse

insert into jk028.SelectCourse values('1060315014001','0001',91);

insert into jk028.SelectCourse values('1060315014002','0001',91);

insert into jk028.SelectCourse values('1060315014003','0001',91);

insert into jk028.SelectCourse values('1060315014004','0001',91);

insert into jk028.SelectCourse values('1060315014005','0001',91);

先将外部jar引入到项目文件中 将Oracle/product/11.2.0//db_home/jdbc/lib中的ojdbc6文件引入

package oraclework;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class ConnOracle {

ConnectOracle();

Connection conn=getCon();

ResultSet rs=null;

PreparedStatement pre=null;

try {

pre=conn.prepareStatement(sql);;

rs=pre.executeQuery(sql);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return rs;

}

public static void ConnectOracle() {

try {

Class. forName(className);

System. out.println("加载数据库驱动成功！" );

} catch (ClassNotFoundException e ) {

System. out.println("加载数据库驱动失败！" );

e.printStackTrace();

}

}

/** 创建数据库连接 */

public static Connection getCon() {

try {

con = DriverManager. getConnection(url, user, password);

System. out.println("创建数据库连接成功！" );

} catch (SQLException e ) {

System. out.print(con );

System. out.println("创建数据库连接失败！" );

con = null;

e.printStackTrace();

}

return con ;

}

public static void closed() {

try {

if (con != null) {

con.close();

System.out.println("关闭数据库连接成功！");

}

} catch (SQLException e ) {

System. out.println("关闭con对象失败！" );

e.printStackTrace();

}

}

}

package oraclework;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Iterator;

public class StudentSelectCourse {

public static void main(String[] args) throws SQLException {

StudentSelectCourse ssc=new StudentSelectCourse();

String sql1="select from jk028.Student";

String sql2="select from jk028.Course";

String sql3="select * from jk028.V_学生成绩";

ssc.sqlselect(sql1);

ssc.sqlselect(sql2);

ssc.sqlselect(sql3);

}

public void sqlselect(String sql) throws SQLException{

ResultSet rs=ConnOracle.select(sql);//学生表

try {

while(rs.next()){

int i=1;

while(rs.getMetaData().getColumnCount()>=i){//列数

System.out.print(rs.getNString(i++)+"\t");

}

System.out.println();

}

} catch (SQLException e) {

e.printStackTrace();

}

rs.close();

ConnOracle.closed();

}

}