咖啡+牛奶=卡布其诺
JavaDeveloper
BlogJava
首页
新随笔
新文章
联系
聚合
管理
posts - 39, comments - 35, trackbacks - 0
一个简单的访问ORACLE数据库的例子
Manager
1
package
com.myStudentManager;
2
3
import
java.io.
*
;
4
import
java.sql.SQLException;
5
6
import
javax.swing.
*
;
7
import
javax.xml.parsers.DocumentBuilder;
8
import
javax.xml.parsers.DocumentBuilderFactory;
9
import
javax.xml.parsers.ParserConfigurationException;
10
11
import
org.w3c.dom.Document;
12
import
org.w3c.dom.Element;
13
import
org.w3c.dom.Node;
14
import
org.w3c.dom.NodeList;
15
import
org.xml.sax.SAXException;
16
17
public
class
Manager
{
18
19
private
String username;
20
private
String password;
21
private
String port;
22
private
String host;
23
private
String sid;
24
private
String xmlName;
25
26
private
static
DatebaseOperator dba;
//
获得对DatebaseOperator的引用
27
//
并且为静态变量
28
29
Manager()
throws
SQLException, ParserConfigurationException, SAXException, IOException
{
30
31
32
33
//
username = JOptionPane.showInputDialog("请输入用户名");
34
//
password = JOptionPane.showInputDialog("请输入密码");
35
host
=
JOptionPane.showInputDialog(
"
请输入主机名
"
);
36
port
=
JOptionPane.showInputDialog(
"
请输入端口号
"
);
37
sid
=
JOptionPane.showInputDialog(
"
请输入SID号
"
);
38
39
40
//
从XML文档读入用户名和密码
41
42
InputStreamReader isr
=
new
InputStreamReader(System.in);
43
BufferedReader br
=
new
BufferedReader(isr);
44
45
DocumentBuilderFactory factory
=
DocumentBuilderFactory
46
.newInstance();
47
DocumentBuilder builder
=
factory.newDocumentBuilder();
48
49
System.out.println(
"
请输入xml文件名,以读入用户名和密码
"
);
50
xmlName
=
br.readLine();
51
52
Document doc
=
builder.parse(xmlName
+
"
.xml
"
);
53
54
Element element
=
doc.getDocumentElement();
55
56
57
NodeList list
=
element.getChildNodes();
58
59
for
(
int
i
=
0
;i
<
list.getLength();i
++
)
{
60
61
Node d1
=
list.item(i);
62
63
if
(d1
instanceof
Element)
{
64
65
66
Element temp
=
(Element)d1;
67
//
打印出节点名字
68
//
System.out.println("<"+temp.getTagName()+">");
69
//
打印出节点的信息
70
//
System.out.println(temp.getFirstChild().getNodeValue());
71
if
(temp.getTagName().equals(
"
name
"
))
{
72
username
=
temp.getFirstChild().getNodeValue();
73
}
else
{
74
password
=
temp.getFirstChild().getNodeValue();
75
}
76
77
}
78
}
79
80
dba
=
new
DatebaseOperator(username,password,host,port,sid);
81
}
82
83
//
显示数据库中所有信息
84
void
showTableInfo()
{
85
dba.showAllInfo();
86
}
87
88
//
向数据库中插入数据
89
void
createTableAndInsertData()
throws
SQLException
{
90
dba.createTableAndInsertDate();
91
}
92
93
void
searchbyStuno()
throws
IOException
{
94
dba.searchbyStuno();
95
}
96
97
public
static
void
main(String[] args)
throws
SQLException, ParserConfigurationException, SAXException, IOException
{
98
99
Manager manager
=
new
Manager();
100
101
//
如果还没有建立表则调用这个函数
102
//
manager.createTableAndInsertData();
103
104
//
显示表中所有的数据
105
manager.showTableInfo();
106
107
manager.searchbyStuno();
108
109
110
111
}
112
113
}
114
Database
1
package
com.myStudentManager;
2
import
java.text.SimpleDateFormat;
3
import
java.util.
*
;
4
import
java.io.BufferedReader;
5
import
java.io.IOException;
6
import
java.io.InputStreamReader;
7
import
java.sql.
*
;
8
9
import
javax.swing.
*
;
10
11
public
class
DatebaseOperator
{
12
13
private
String username;
14
private
String password;
15
private
String port;
//
端口
16
private
String sid;
//
sid号
17
private
String host;
//
主机
18
private
String url;
19
20
private
static
Connection con;
//
获得对JDBC的连接
21
private
static
Statement st;
//
用于执行静态的SQL语句，并返回生成的记过对象
22
private
static
ResultSet rs;
23
private
static
ResultSetMetaData rsmd;
24
private
static
PreparedStatement ps;
25
26
static
InputStreamReader isr;
27
static
BufferedReader br;
28
29
DatebaseOperator()
{
30
31
}
32
33
//
初始化参数，并连接相关数据库
34
DatebaseOperator(String username,String password,String host,String port,String sid)
throws
SQLException
{
35
this
.username
=
username;
36
this
.password
=
password;
37
this
.port
=
port;
38
this
.host
=
host;
39
this
.sid
=
sid;
40
41
isr
=
new
InputStreamReader(System.in);
42
br
=
new
BufferedReader(isr);
43
44
try
{
45
Class.forName(
"
oracle.jdbc.driver.OracleDriver
"
);
46
JOptionPane.showMessageDialog(
null
,
"
注册数据库驱动成功！恭喜恭喜
"
);
47
}
catch
(ClassNotFoundException e)
{
48
JOptionPane.showMessageDialog(
null
,
"
注册数据库驱动不成功
"
);
49
e.printStackTrace();
50
}
51
52
url
=
"
jdbc:oracle:thin:@
"
+
host
+
"
:
"
+
port
+
"
:
"
+
sid;
53
54
try
{
55
con
=
DriverManager.getConnection(url,username,password);
56
JOptionPane.showMessageDialog(
null
,
"
连接数据库成功，恭喜恭喜
"
);
57
}
catch
(SQLException e)
{
58
JOptionPane.showMessageDialog(
null
,
"
连接数据库失败！
"
);
59
e.printStackTrace();
60
}
61
//
创建并获得一个statement对象
62
st
=
con.createStatement();
63
rs
=
st.executeQuery(
"
select * from studentinfo
"
);
64
}
65
66
//
显示表格中所有信息
67
void
showAllInfo()
{
68
try
{
69
rsmd
=
rs.getMetaData();
70
71
int
count
=
rsmd.getColumnCount();
//
获取字段的个数
72
int
i
=
1
;
73
while
(i
<=
count)
{
74
System.out.print(
"
\t
"
+
rsmd.getColumnName(i));
75
i
++
;
76
}
77
78
//
rs.beforeFirst();
//
将指针移动到给定ResultSet对象的开头
79
80
while
(rs.next())
{
81
System.out.println();
82
i
=
1
;
//
重新回到字段的第一列
83
while
(i
<=
count)
{
84
if
(i
!=
count)
{
85
System.out.print(
"
\t
"
+
rs.getString(i));
86
87
}
else
{
88
System.out.print(
"
\t
"
+
rs.getString(i).substring(
0
,
10
));
89
}
90
91
i
++
;
92
}
93
}
94
System.out.println();
95
96
}
catch
(SQLException e)
{
97
e.printStackTrace();
98
}
99
}
100
101
//
在数据库当中建表，同时插入数据
102
void
createTableAndInsertDate()
throws
SQLException
{
103
//
创建表
104
st.execute(
"
CREATE TABLE studentinfo(stuno VARCHAR(10),
"
+
105
"
name VARCHAR(10),
"
+
106
"
sex CHAR(2),
"
+
107
"
grade NUMBER,
"
+
108
"
birthday DATE)
"
);
109
//
插入数据
110
st.execute(
"
INSERT INTO studentinfo VALUES ('td1','张三',
"
+
111
"
'男',6,to_date('1982-10-27','yyyy-mm-dd'))
"
);
112
st.execute(
"
INSERT INTO studentinfo VALUES ('td2','李四',
"
+
113
"
'男',6,to_date('1982-10-27','yyyy-mm-dd'))
"
);
114
115
st.execute(
"
INSERT INTO studentinfo VALUES ('td3','王五',
"
+
116
"
'男',6,to_date('1982-10-27','yyyy-mm-dd'))
"
);
117
118
st.execute(
"
INSERT INTO studentinfo VALUES ('td4','飞机',
"
+
119
"
'男',6,to_date('1982-10-27','yyyy-mm-dd'))
"
);
120
121
st.execute(
"
INSERT INTO studentinfo VALUES ('td5','大炮',
"
+
122
"
'男',6,to_date('1982-10-27','yyyy-mm-dd'))
"
);
123
}
124
125
void
searchbyStuno()
throws
IOException
{
126
System.out.println(
"
请输入要查找的学号
"
);
127
String stuno
=
br.readLine();
128
129
try
{
130
131
ps
=
con.prepareStatement(
"
select * from studentinfo where stuno = ?
"
);
132
ps.setString(
1
, stuno);
133
134
135
if
(
0
!=
ps.executeUpdate())
{
136
rs
=
ps.getResultSet();
137
showAllInfo();
138
}
else
{
139
System.out.println(
"
您要查找的纪录不存在！
"
);
140
}
141
142
}
catch
(SQLException e)
{
143
144
e.printStackTrace();
145
}
146
147
System.out.println(
"
请输入要查找的姓名
"
);
148
String name
=
br.readLine();
149