寫一支簡單的 java程式 來取得資料庫連線(getConnection)

package com.test.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
*
* @author jimmy
*/
public class simpleConnection {


Connection conn = null;

simpleConnection(){}


//get connection
public Connection getConnection(){
try {

Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ICPS","root", "");
/*
利用properties
Properties props = new Properties ();
props.put("user", "root");
props.put("root", "");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ICPS",props);
*/
System.out.println("=================== getconnection Success ======================");
}catch(Exception e){
e.printStackTrace();
System.out.println("=================== getconnection error ======================");
}
return conn;
}


//close connection
public void closeConnection(){
if(conn!=null){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

//execute query
public void sqlexecuteQuery(Connection conn){

String sql = "select * from icpsuser";
try {
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

while(rs.next()){
System.out.println(rs.getString("userid")+"/"+ rs.getString("username"));
}

} catch (SQLException ex) {
ex.printStackTrace();
}

}


public void run(){
Connection conn = this.getConnection();
this.sqlexecuteQuery(conn);
this.closeConnection();
}

public static void main(String[] arg){

simpleConnection sc = new simpleConnection();
sc.run();
}

}

利用 seam-gen 快速生產程式

1. seam setup
建立設定檔(build.properties)

#Tue Oct 30 20:24:11 CST 2007
hibernate.connection.password=1234
workspace.home=c\:/icps
model.package=com.icps.entity
hibernate.default_catalog=icps
driver.jar=c\:\\icps\\lib\\sqljdbc.jar
action.package=com.icps.session
test.package=com.icps.test
database.type=mssql
hibernate.default_catalog.null=
hibernate.default_schema=dbo
database.drop=n
hibernate.default_schema.null=
project.name=icps
hibernate.connection.username=icps
hibernate.connection.driver_class=com.microsoft.sqlserver.jdbc.SQLServerDriver
hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider
project.type=war
icefaces.home=
database.exists=y
jboss.home=C\:/icps//jboss-4.2.1.GA
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.connection.url=jdbc\:sqlserver\://59.127.137.13\:1433

2. seam new-project
建立專案檔案

3. seam generate-entities
產生資料庫所有的 action bean 及 form bean

Ant 環境變數設定

SET ANT_HOME=C:\JAVA\ant
set JAVA_HOME=C:\Program Files\Java\jdk1.5.0_12
set PATH=%PATH%;%ANT_HOME%\bin

StringUtils 使用subStringBetween

package com.test;
import org.apache.commons.lang.StringUtils;

/**
*
* @author macbook
*/
public class splitString {


public String substringBetween(String var,String open ,String close,String d4 ){
return StringUtils.isBlank(StringUtils.substringBetween(var, open, close))?d4:StringUtils.substringBetween(var, open, close);
}

public String splitLeft(String var){
return StringUtils.substringBetween(var, "(", ",");
}


public String splitLeft(String var ,String d4){
return StringUtils.isBlank(this.splitLeft(var))?d4:this.splitLeft(var);
}

public static void main(String[] arg){
splitString s = new splitString();
System.out.println(s.splitLeft("(,)", "--"));
System.out.print(s.substringBetween("(20070401,)", ",", ")", "--"));

}
}

使用 dbcp 做 connection pool(下)

實做connectionManager

/**
* 2007-08-20 create by jimmy
*
*/
package com.infinitiessoft.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.*;
import javax.servlet.ServletException;
import javax.sql.*;
import org.apache.commons.dbcp.BasicDataSource;


/**
*
* @author jimmy
*/
public class ConnectionManager {

public static final String JDBC_MYSQL = "jdbc/mysql";
public static final String JDBC_ORCALE = "jdbc/orcale";
public String JDBC_TYPE;
public static DataSource dataSource = null;
public String connectionStatus = "";

public ConnectionManager(){
this.init();
}
public ConnectionManager(String type) {

if(type.equalsIgnoreCase(this.JDBC_ORCALE)){
this.JDBC_TYPE = this.JDBC_ORCALE;
}else{
this.init();
}
}

public void init(){
this.JDBC_TYPE = this.JDBC_MYSQL;
}

public Connection getConnection(){

Connection conn = null;
DataSource ds = null;
BasicDataSource bds = null;

try{
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
ds = (DataSource)envContext.lookup(JDBC_TYPE);

BasicDataSource dbs =(BasicDataSource)ds;
this.connectionStatus = "jdbc type:"+this.JDBC_TYPE+"MaxActive:"+dbs.getMaxActive()+",NumActive: " + dbs.getNumActive() + ",NumIdle: " + dbs.getNumIdle();
//System.out.println(this.connectionStatus);

if( dbs.getNumActive() >= dbs.getMaxActive()){
// System.out.println("`" + JDBC_TYPE + "' is an unknown DataSource");
// System.out.println("'Connection is Busy!!");
this.connectionStatus = "Connection is Busy!!";
}else{
conn = ds.getConnection();
}

}catch(Exception ex){
ex.printStackTrace();
}finally{
System.out.println(this.connectionStatus);
}
return conn;
}



public int getMaxActive(BasicDataSource bds){
return bds.getMaxActive();
}


public int getMaxIdle(BasicDataSource bds){
return bds.getMaxIdle();
}


public String printStatus(){
return this.connectionStatus;
}
public void freeConnection(Connection conn){

if(conn!=null){
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}



}

使用 dbcp 做 connection pool(上)

1. context 設定



auth="Container"
driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
loginTimeout="10"
maxWait="5000"
maxActive="100"
maxIdle="10"
name="jdbc/mysql"
testOnBorrow="true"
type="javax.sql.DataSource"
url="jdbc:mysql://localhost:3306/TICP"
username="root"
password=""
validationQuery="select 1"/>



2. web.xml

....

JNDI JDBC DataSource
jdbc/GUESTBOOK
javax.sql.DataSource
Container

.....

取得localhost ip及轉換DNS TO IP

package com.webtest;

import java.net.InetAddress;
import java.net.UnknownHostException;

/**
* @author jimmy
*/
public class ip {

public String getip(){ //local ip
String localip = "";
try{
InetAddress address=InetAddress.getLocalHost();
localip=address.getHostAddress();
} catch(UnknownHostException uhe){
localip = "Unable to find: ";
}
return localip;
}

public String DNS2ip(String hostname){ // server ip
String strvar="";
try{
InetAddress address=InetAddress.getByName(hostname);
strvar=address.getHostAddress();
} catch(UnknownHostException uhe){
strvar="Unable to find: "+hostname;
}
return strvar;
}

static public void main(String args[]){
ip a=new ip();
//System.out.println(aa.getip(args[0]));
System.out.println(a.getip());
System.out.println(a.DNS2ip("www.yahoo.com.tw"));
}

}