1、src 下建立luckydb.properties
#---Database---
DB.DRIVER=oracle.jdbc.driver.OracleDriver
DB.URL=jdbc:oracle:thin:@tekt2:1521:bak
DB.USER=readonly
DB.PASSWORD=readonly
DB.MAX_CONNECTIONS=30
2、建立 DBConnectionManage
/*
* Created on 2004-8-10
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.tek.all.database;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Vector;
import java.sql.DriverManager;
import com.tek.all.database.Configuration;
/**
* @author qianzh
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class DBConnectionManage {
private final static DBConnectionManage instance=new DBConnectionManage();
private DBConnectionPool pool;
public static DBConnectionManage getInstance(){
return instance;
}
public void freeConnection(Connection con) throws SQLException{
pool.freeConnection(con);
}
public Connection getConnection() throws SQLException{
return pool.getConnection();
}
private DBConnectionManage(){
init();
}
private void init(){
Configuration cfg=Configuration.getInstance();
String db_driver=cfg.getValue("DB.DRIVER");
String db_url=cfg.getValue("DB.URL");
String db_user=cfg.getValue("DB.USER");
String db_password=cfg.getValue("DB.PASSWORD");
int db_maxConn=Integer.parseInt(cfg.getValue("DB.MAX_CONNECTIONS"));
try{
Class.forName(db_driver);
}catch(ClassNotFoundException ex){
System.out.println(ex);
}
pool = new DBConnectionPool(db_url,db_user,db_password,db_maxConn);
}
class DBConnectionPool{
private Vector freeConnections=new Vector();
private int maxConn;
private int connNumb;
private String URL;
private String password;
private String user;
public DBConnectionPool(String URL,String user,String password,int maxConn){
this.URL=URL;
this.user=user;
this.password=password;
this.maxConn=maxConn;
//System.out.print("这里是OK的!!!"+URL+"___"+user+password);
}
public synchronized void freeConnection(Connection con){
freeConnections.addElement(con);
connNumb--;
notifyAll();
}
public synchronized Connection getConnection() throws SQLException{
Connection con=null;
if(freeConnections.size()>0){
con=(Connection)freeConnections.firstElement();
freeConnections.removeElementAt(0);
try{
if(con.isClosed()){
con=getConnection();
//System.out.print("3这里是OK的!!!");
}
}catch(SQLException e){
con=getConnection();
}
}else if(maxConn==0||connNumb<maxConn){
con=newConnection();
}if (con != null) {
connNumb++;
}
return con;
}
private Connection newConnection() throws SQLException{
Connection con =DriverManager.getConnection(URL,user, password);
return con;
}
}
}
3、建立Configuration
/*
* Created on 2004-8-10
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.tek.all.database;
import java.util.Properties;
import java.io.InputStream;
import java.io.IOException;
/**
* @author qianzh
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class Configuration {
private Properties properties;
private final static Configuration cfg=new Configuration();
private Configuration(){
properties=new Properties();
InputStream is=null;
try{
is=getClass().getResourceAsStream("/luckydb.properties");
properties.load(is);
//System.out.print("1这里是OK的!!!");
}catch(Exception e){
System.out.print("can't read the file");
}finally{
try{
if(is!=null){
is.close();
}
}catch(IOException ex){ }
}
}
/
public static Configuration getInstance(){
return cfg;
}
public String getValue(String key){
return properties.getProperty(key);
}
}
4、建立DBAccess
/*
* Created on 2004-8-11
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.tek.all.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
/**
* @author qianzh
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class DBAccess {
protected Statement statement;
protected ResultSet resutlset;
protected Connection connection;
public DBAccess(Connection connection){
this.connection=connection;
}
public void closeSelect() throws SQLException{
if(statement!=null){
statement.close();
}
}
public ResultSet openSelect(String sql) throws SQLException{
statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
return rs;
}
public int runSql(String sql) throws SQLException{
statement=connection.createStatement();
int result=statement.executeUpdate(sql);
statement.close();
return result;
}
}
5、下面建立HelpBean选取数据库数据。
package com.tek.all.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.tek.all.filter.ChangeISO;
import com.tek.all.database.DBConnectionManage;
import com.tek.all.struts.form.*;
import java.sql.*;
import java.util.*;
import com.tek.all.struts.form.ManageUserForm;
import com.tek.all.database.DBAccess;
/**
* @author qianzh
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class HelpBean {
private DBConnectionManage dbm=DBConnectionManage.getInstance();
Connection conn=null;
ChangeISO iso=new ChangeISO();
public void savaRegForm(RegForm regform,int i) throws Exception{
int t=i+1;
conn=dbm.getConnection();
String sql="insert into tek_user "+
"(u_id,user_name, "+
"password, "+
"department, "+
"work_id, "+
"full_name, "+
"contact_mobile, "+
"contact_phone, "+
"contact_email_in, "+
"contact_email_out, "+
"flag, "+
"reg_date) "+
"values('"+t+"',?,?,?,?,?,?,?,?,?,1,sysdate)";
PreparedStatement pStmt=conn.prepareStatement(sql);
pStmt.setString(1,regform.getUser_name());
pStmt.setString(2,regform.getPassword());
pStmt.setString(3,iso.trans(regform.getDepartment()));
pStmt.setString(4,regform.getWork_id());
pStmt.setString(5,iso.trans(regform.getFull_name()));
pStmt.setString(6,regform.getContact_mobile());
pStmt.setString(7,regform.getContact_phone());
pStmt.setString(8,regform.getContact_Email_in());
pStmt.setString(9,regform.getContact_Email_out());
pStmt.executeUpdate();
pStmt.close();
dbm.freeConnection(conn);
}