2 实例操作
1.1 建立数据库
2.1.1准备
1、 启动Oracle三个服务,OracleDBConsoleorcl、OracleOraDb10g_home1TNSListener、OracleServiceORCL;
2.1.2建库及表
建立一个名为"JAVA_EXAMPLE"的数据库和名为DUSER的表。
首先通过PowerDesigner设计数据库,如下图所示;
通过Oracle Enterprise Manager Console创建表空间:JAVA_EXAMPLE,创建用户MYJAVA,其默认表空间为JAVA_EXAMPLE,并赋予该用户一定的操作权限。
打开Oracle Net Manager,新建一个orcl_local服务,如下图所示:
运行PL/SQL Developer,用Username=”myjava”、Password=”iloveu”、Database=” orcl_local”、Connect as=“Normal”登陆,打开一个SQL Window,运行脚本如下:
create table DUSER (
USER_ID INT not null,
USER_NAME VARCHAR2(20) not null,
USER_PSD VARCHAR2(50) not null,
USER_DETAIL VARCHAR2(100),
constraint PK_DUSER primary key (USER_ID)
);
添加一行记录,运行脚本:
insert into duser (USER_ID, USER_NAME, USER_PSD, USER_DETAIL)
values (1, 'Robin', '123', 'I am Robin');
1.1 Java编程实例
2.2.1设计
1、我们的目标是建立几个简单的网页来显示、添加、修改、删除用户;
2、建立以下几个网页:
页面show.jsp显示数据库中的所有用户
页面add. jsp添加用户
页面edit. jsp修改用户信息
页面del. jsp删除指定用户
2.2.2准备
1、新建一个Dynamic Web Project,Project Name 为Test,其它为默认设置;
2、添加Oracle的JDBC。从Oracle的安装目录下“D:/Program/oracle/product/10.2.0/db_1/jdbc/lib”中将“classes12.jar”拷贝到“Test”项目的“D:/Program/Java/JavaSpace/Test/WebContent/WEB-INF/lib”目录下即可。
选择“Test”项目点击右键,在右键菜单中选择“Build Path”,点击下一级菜单“Configure Build Path”,
从弹出的窗体中,选择选择“Libraries”选项卡,打开“Web App Libraries”导航树结点,可以看到已经添加了“classes12.jar”。(注:若没有看到,可刷新“Test”项目,重新查看。)
3、选择“Test”项目点击右键,在右键菜单中选择“New”,点击下一级菜单“JSP”,分别新建“show.jsp”、“add.jsp”、“edit.jsp”、“del.jsp”四个文件。
4、选择“Java Resources: src”项目点击右键,在右键菜单中选择“New”,点击下一级菜单“Package”,分别新建“robin.model”、“robin.util”、“robin.web”三个包。
5、在“robin.model”包下新建userunit类,为用户实体类,内容如下:
package robin.model;
public class userunit {
private int UserId;
private String UserName;
private String UserPsd;
private String UserDetail;
public userunit() {
UserId = 0;
UserName = "";
UserPsd = "";
UserDetail = "";
}
public userunit(int newId, String newname, String newpsd, String newdetail) {
UserId = newId;
UserName = newname;
UserPsd = newpsd;
UserDetail = newdetail;
}
public int getUserId() {
return UserId;
}
public void setUserId (int newId) {
this.UserId = newId;
}
public String getUserName() {
return UserName;
}
public void setUserName(String newname) {
this.UserName = newname;
}
public String getUserPsd() {
return UserPsd;
}
public void setUserPsd(String newpsd) {
this.UserPsd = newpsd;
}
public String getUserDetail() {
return UserDetail;
}
public void setUserDetail(String newdetail) {
this.UserDetail = newdetail;
}
}
6、在“robin.util”包下新建DBConnMgr类,为管理数据库连接类,内容如下:
package robin.util;
import java.sql.*;
public class DBConnMgr {
private String driverName = "oracle.jdbc.driver.OracleDriver";
private String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private String user = "myjava";
private String password = "iloveu";
public void setDriverName(String newDriverName) {
driverName = newDriverName;
}
public String getDriverName() {
return driverName;
}
public void setUrl(String newUrl) {
url = newUrl;
}
public String getUrl() {
return url;
}
public void setUser(String newUser) {
user = newUser;
}
public String getUser() {
return user;
}
public void setPassword(String newPassword) {
password = newPassword;
}
public String getPassword() {
return password;
}
public Connection getConnection() {
try {
Class.forName(driverName);
return DriverManager.getConnection(url, user, password);
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void main(String[] args) {
try{
DBConnMgr dcm = new DBConnMgr();
Connection conn = dcm.getConnection();
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("select * from duser");
while(rs.next()){
System.out.print("username: "+rs.getString(1));
System.out.print("/tuserpsd: "+rs.getString(2));
System.out.println();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
7、在“robin.web”包下新建DataBase类,为数据库访问基类,内容如下:
package robin.web;
import java.sql.*;
import robin.util.*;
public class DataBase {
protected Connection conn = null; //Connection接口
protected Statement stmt = null; //Statement接口
protected ResultSet rs = null; //记录结果集
protected PreparedStatement prepstmt = null; //PreparedStatement接口
protected String sqlStr; //sql String
protected boolean isConnect=true; //与数据库连接标识
protected int rscount = 0;
public DataBase() {
try {
sqlStr = "";
DBConnMgr dcm = new DBConnMgr();
conn = dcm.getConnection();
stmt = conn.createStatement();
}
catch (Exception e) {
System.out.println(e);
isConnect=false;
}
}
public Statement getStatement() {
return stmt;
}
public Connection getConnection() {
return conn;
}
public PreparedStatement getPreparedStatement() {
return prepstmt;
}
public ResultSet getResultSet() {
return rs;
}
public String getSql() {
return sqlStr;
}
public boolean execute() throws Exception {
return false;
}
public boolean insert() throws Exception {
return false;
}
public boolean update() throws Exception {
return false;
}
public boolean delete() throws Exception {
return false;
}
public boolean query() throws Exception {
return false;
}
public void close() throws SQLException {
if ( stmt != null ) {
stmt.close();
stmt = null;
}
conn.close();
conn = null;
}
};
8、在“robin.web”包下新建userdao类,为操作数据库DUser表类,内容如下:
package robin.web;
import java.sql.*;
import java.util.Vector;
import robin.model.userunit;
public class userdao extends DataBase {
private userunit auser = new userunit(); //新的用户
private javax.servlet.http.HttpServletRequest request; //建立页面请求
private boolean sqlflag = true ; //接收到的数据是否正确
private Vector user_list; //显示用户列表向量数组
public userdao() throws Exception{
super();
}
public Vector getUserList() {
return user_list;
}
public boolean getSqlflag() {
return sqlflag;
}
public String getGbk( String str) {
try {
return new String(str.getBytes("ISO8859-1"));
}
catch (Exception e) {
return str;
}
}
//将页面表单传来的资料分解
public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
boolean flag = false;
try {
request = newrequest;
String user_id = request.getParameter("txt_UserId");
int userId = 0;
try {
userId = Integer.parseInt(user_id);
}
catch (Exception e) {
}
auser.setUserId(userId);
String username = request.getParameter("txt_UserName");
if (username==null || username.equals("")) {
username = "";
sqlflag = false;
}
auser.setUserName(getGbk(username));
String userpsd = request.getParameter("txt_UserPsd");
if (userpsd==null || userpsd.equals("")) {
userpsd = "";
sqlflag = false;
}
auser.setUserPsd(getGbk(userpsd));
String userdetail = request.getParameter("txt_UserDetail");;
if (userdetail==null) {
userdetail = "";
}
auser.setUserDetail(getGbk(userdetail));
if (sqlflag) {
flag = true;
}
return flag;
}
catch (Exception e) {
return flag;
}
}
public String getSql() {
sqlStr = "select * from duser ";
return sqlStr;
}
public boolean execute() throws Exception {
sqlStr = "select * from duser ";
try {
rs = stmt.executeQuery(sqlStr);
rscount = stmt.getMaxRows();
user_list = new Vector(rscount);
while (rs.next()) {
userunit user = new userunit();
user.setUserId(rs.getInt("USER_ID"));
user.setUserName(rs.getString("USER_NAME"));
user.setUserPsd(rs.getString("USER_PSD"));
user.setUserDetail(rs.getString("USER_DETAIL"));
user_list.addElement(user);
}
rs.close();
return true;
}
catch (SQLException e) {
System.out.println("错误:"+e);
return false;
}
}
public boolean insert() throws Exception {
sqlStr = "insert into DUser (USER_ID,USER_NAME,USER_PSD,USER_DETAIL) values (";
sqlStr = sqlStr + auser.getUserId() + ",'";
sqlStr = sqlStr + auser.getUserName() + "','";
sqlStr = sqlStr + auser.getUserPsd() + "','";
sqlStr = sqlStr + auser.getUserDetail() + "')";
try {
stmt.execute(sqlStr);
return true;
}
catch (SQLException e) {
System.out.println("错误:"+e+sqlStr);
return false;
}
}
public boolean update() throws Exception {
sqlStr = "update DUser set ";
sqlStr = sqlStr + "USER_NAME = '" + auser.getUserName() + "',";
sqlStr = sqlStr + "USER_PSD = '" + auser.getUserPsd() + "',";
sqlStr = sqlStr + "USER_DETAIL = '" + auser.getUserDetail() + "' ";
sqlStr = sqlStr + "where USER_ID = " + auser.getUserId() ;
try {
stmt.execute(sqlStr);
return true;
}
catch (SQLException e) {
System.out.println("错误:"+e);
return false;
}
}
public boolean delete( int aid ) throws Exception {
sqlStr = "delete from DUser where USER_ID = " + aid ;
try {
stmt.execute(sqlStr);
return true;
}
catch (SQLException e) {
System.out.println("错误:"+e);
return false;
}
}
public boolean getOneUser(int newid ) throws Exception {
try {
sqlStr="select USER_ID,USER_NAME,USER_PSD,USER_DETAIL from DUser where USER_ID = " + newid ;
rs = stmt.executeQuery(sqlStr);
if (rs.next()) {
userunit user = new userunit();
user.setUserId(rs.getInt("USER_ID"));
user.setUserName(rs.getString("USER_NAME"));
user.setUserPsd(rs.getString("USER_PSD"));
user.setUserDetail(rs.getString("USER_DETAIL"));
user_list = new Vector(1);
user_list.addElement(user);
} else {
rs.close();
return false;
}
rs.close();
return true;
}
catch (SQLException e) {
System.out.println("错误:"+e);
return false;
}
}
};