Shulou(Shulou.com)06/01 Report--

Use java to connect to Oracle database for simple query

Log in to sql plus with sys

Create a user

Create user user identified by password

Authorization

Grant connect,resource to user

Grant selet any dictionary to user

Grant OEM_MONITOR to user

Grant create any view to grant create any view to jk028

With jk... Log in to 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_ student score

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

Add data:

Student

Insert into jk028.Student values ('1060315014001' Wang 1fei 'male', 20' '610203199701192911'' female 'Communist Youth League')

Insert into jk028.Student values ('1060315014002' Wang 2Jing 'female', 20' '610203199701192912' Communist Youth League')

Insert into jk028.Student values ('1060315014003' Wang 3Jing 'male', 20''610203199701192913' 'Communist Youth League')

Insert into jk028.Student values ('1060315014004' female', 'Wang 4Jing', 'female', 20''610203199701192914' 'female' Communist Youth League')

Insert into jk028.Student values ('1060315014005' Wang 5zhuo 'male', 20' '610203199701192915'' female 'Communist Youth League')

Course

Insert into jk028.Course values ('0001' database', 11)

Insert into jk028.Course values ('0002' Marx's principle, 12)

Insert into jk028.Course values ('0003 data Mining', 13)

Insert into jk028.Course values ('0004' software engineering, 14)

Insert into jk028.Course values ('0005' operating system, 15)

SelectCourse

Insert into jk028.SelectCourse values ('1060315014001)

Insert into jk028.SelectCourse values ('1060315014002)

Insert into jk028.SelectCourse values ('1060315014003)

Insert into jk028.SelectCourse values ('1060315014004)

Insert into jk028.SelectCourse values ('1060315014005)

First introduce the external jar into the project file and the ojdbc6 file in the Oracle/product/11.2.0//db_home/jdbc/lib

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 {

Private static Connection con

Private static String user = "jk028"; / / Database account

Private static String password = "123456"; / / Database password

Private static String className = "oracle.jdbc.driver.OracleDriver"

Private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:Oraclewk"; / / "jdbc:oracle:thin:@127.0.0.1:1521:Oraclewk" br/ > / / "jdbc:oracle:thin:@127.0.0.1:1521:Oraclewk"

/ / hostname: XL2LY877TNOBUUF

Public static void main (String [] args) {

/ / TODO Auto-generated method stub

ConnOracle c = new ConnOracle ()

C.ConnectOracle ()

C.getCon ()

C.closed ()

}

Public static ResultSet select (String sql) {

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 ("database driver loaded successfully!" );} catch (ClassNotFoundException e) {System. Out.println ("failed to load database driver!" ); e.printStackTrace ();}

}

/ * * create a database connection * / public static Connection getCon () {try {con = DriverManager. GetConnection (url, user, password); System. Out.println ("database connection created successfully!" );} catch (SQLException e) {System. Out.print (con); System. Out.println ("failed to create database connection!" ); con = null; e.printStackTrace ();} return con

}

Public static void closed () {try {if (con! = null) {con.close (); System.out.println ("database connection closed successfully!") ;} catch (SQLException e) {System. Out.println ("failed to close con object!" ); 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_ student grades"

Ssc.sqlselect (sql1)

Ssc.sqlselect (sql2)

Ssc.sqlselect (sql3)

}

Public void sqlselect (String sql) throws SQLException {

ResultSet rs=ConnOracle.select (sql); / / Student form try {while (rs.next ()) {int item1; while (rs.getMetaData (). GetColumnCount () > = I) {/ / number of columns System.out.print (rs.getNString (iTunes +) + "\ t");} System.out.println ();}} catch (SQLException e) {e.printStackTrace ();} rs.close () ConnOracle.closed ();}

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.