续2:DBConnectionPool.javapackage base.db;import java.sql.*; import java.util.*; import org.apache.log4j.*; import base.Config; import base.db.connection.*;public class DBConnectionPool { private static Logger logger = Logger.getLogger(DBConnectionPool.class .getName()); private static DBConnectionPool poolInstance = null; private Connection[] pool; private Object[] catcher; int totalConnection; public static DBConnectionPool getPoolInstance() { if (poolInstance == null) poolInstance = new DBConnectionPool(); return poolInstance; } public Connection getConnectin(Object b) { synchronized (catcher) { for (int i = 0; i < totalConnection; i++) { if (catcher[i] == null) { catcher[i] = b; logger.info(toString()+b.toString()+" catch a connection."); return pool[i]; } } } return null; } public void freeConnection(Object b) { logger.info(toString()+b.toString() + " freeConnection"); synchronized (catcher) { for (int i = 0; i < totalConnection; i++) { if (catcher[i] == b) { catcher[i] = null; break; } } } } public String toString() { return new String("[DBConnectionPool]"); }
public String showPool() { Collection out = new ArrayList(); out.add(new String("[DBConnectionPool]")); for (int i = 0; i < totalConnection; i++) { out.add(new String("[" + i + "," + catcher[i] + "]")); } return out.toString(); }
http://msdn2.microsoft.com/zh-cn/library/ms378749.aspx
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class Dao {
//属性
private String IP;
private String dbString;
private String dbMing;
private String yongHuMing;
private String yongHuMima;
private String url;
private Connection conn ;
//功能
public Dao()
{
IP="localhost";
dbString="com.microsoft.jdbc.sqlserver.SQLServerDriver";
dbMing="saolei";
yongHuMing="saolei";
yongHuMima="saolei";
url= "jdbc:microsoft:sqlserver://"+IP+":1433;DatabaseName="+dbMing+"";
jiaZaiQuDong();
jianLiLianJie();
}
private void jiaZaiQuDong()
{
try
{
Class.forName(dbString);
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
private void jianLiLianJie()
{
try
{
conn = DriverManager.getConnection(url,yongHuMing,yongHuMima);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public Statement shengChengStmt()
{
Statement stmt=null;
try
{
stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE ,ResultSet.CONCUR_READ_ONLY );
}
catch (SQLException e)
{
e.printStackTrace();
}
return stmt;
}
//-------------表数据---------------------------------------------------
public ResultSet query(String sql)
{
//System.out.println(sql);
Statement stmt=this.shengChengStmt();
ResultSet rs=null;
try
{
rs = stmt.executeQuery(sql);
}
catch (SQLException e)
{
e.printStackTrace();
}
return rs;
}
public int update(String sql)
{
Statement stmt=this.shengChengStmt();
int tiao=0;
try
{
tiao = stmt.executeUpdate(sql);
}
catch (SQLException e)
{
e.printStackTrace();
}
return tiao;
}
}
Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException e) {
System.out.println("找不到驱动程序");
}Step3:提供JDBC URLjdbc:mysql://主机名:端口号/数据库名?user=***&password=***&useUnicode=true&characterEncoding=UTF8端口号:MySQL的默认值是3306useUnicode,characterEncoding:如果要存取中文,则必须使用,表明是否使用Unicode,并指定编码方式.Step4:从DriverManager取得Connection 可以直接将JDBC URL传入DriverManager.getConnection()得到Connection对象,如:try {
String url = "jdbc:mysql://localhost:3306/GUESTBOOK?user=caterpillar&password=123456";
Connection conn = DriverManager.getConnection(url);
if(!conn.isClosed())
System.out.println("数据库连接成功!"); conn.close();
}
catch(SQLException e) {
....
}也可以将userName和password传入DriverManager.getConnection()得到Connection对象,如:String url = "jdbc:mysql://localhost:3306/AddressBook";
String user = "ZhuJun";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
一个完整的例子:import java.sql.*; public class DBTest {
public static void main(String[] args) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/AddressBook";
String user = "ZhuJun";
String password = "123456";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password); if(!conn.isClosed())
System.out.println("数据库连接成功!");
conn.close();
}
catch(ClassNotFoundException e) {
System.out.println("找不到驱动程序");
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}
}
}JDBC连接MySQL数据库- -
* 用JDBC驱动建立与数据库的连接
*/
public static void getConnection() throws Exception { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
.newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Newspaper";//数据库名称
String user = "sa";
String password = "";
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// 每个Connection只能有一个有效的ResultSet
} public static void freeConnection() {// 断开程序与DB连接
try {
if (rs != null)
rs.close();
stmt.close();
conn.close();
} catch (SQLException exc) {
}
}
}
msbase.jar
mssqlserver.jar
msutil.jar
//程序代码如下
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;public class Dbcon {
Connection con;
public Connection getCon()
{
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databaseName=login","sa","");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//测试连接是否成功
public static void main(String[] args)
{
Dbcon conn=new Dbcon();
conn.getCon();
System.out.println("ok");
}}
<%@ page import="java.sql.*"%>
<%@ page import="oracle.jdbc.driver.*"%>
<%@ page import="java.net.*"%>
<%@ page import="java.lang.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<html>
<body>
<%
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@23.226.3.113:1521:ora90","wrq","wrq");
Statement stmt=conn.createStatement();
String sql="select * from TEST";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
%>
您的第一个字段内容为:<%=rs.getString(1)%>
您的第二个字段内容为:<%=rs.getString(2)%>
<%
}
out.print("数据库操作成功,恭喜你");
}
catch(java.lang.ClassNotFoundException e)
{
System.out.println("myconn():"+e.getMessage());
}
catch(java.sql.SQLException e)
{
System.out.println("conn():"+e.getMessage());
}
catch(Exception e)
{
System.out.println("Driver error!");
System.err.println(e.getMessage());
}
%>
</body>
</html>
用 JAVA 与 Microsoft Access 连接的例子。1、利用Access建立数据库database,建立用户表,如User表。
2、添加数据源驱动(控制面板->管理工具->数据源),选择Microsoft Access driver,然后选择刚建立的数据库。
3、代码如下:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
public class DatabaseOperate { String dbDriver = null; String url = null; Connection conn = null; Statement stmt = null; String sqlCommond = null; // ResultSet rs = null; public DatabaseOperate() {
try {
jbInit();
} catch (Exception exception) {
exception.printStackTrace();
}
} private void jbInit() { dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
url = "jdbc:odbc:database"; //数据库名为:database
try {
Class.forName(dbDriver);
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
} public void closeDatabase() throws SQLException {
// rs.close();
stmt.close();
conn.close();
} public boolean isUser(String sName, String sPassword) { ResultSet rs = null; sqlCommond = "SELECT * FROM User WHERE " + "UserName = '" + sName
+ "'AND Password = '" + sPassword + "'";
try {
rs = stmt.executeQuery(sqlCommond);
while (rs.next()) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public class Conn {
public static Connection getConnection(){
Connection con=null;
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db","sa","");
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException ex){
ex.printStackTrace();
}
return con;
}
}
{
static Connection connect=null;
public static void main(String args[]) throws SQLException
{
try
{
// Class.forName("com.mysql.jdbc.Driver"); // 加载MYSQL JDBC驱动程序!
// 作用同下(为什么?)
Class.forName("org.gjt.mm.mysql.Driver");
System.out.println("成功连接MYSQL驱动!");// Success loading Mysql Driver! }
catch (Exception e)
{
System.out.print("Error loading Mysql Driver!");
e.printStackTrace();
} try
{ connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/javaweb", "root", "123");
// 连接URL为 jdbc:mysql//服务器地址/数据库名
// 后面的2个参数分别是登陆用户名和密码
System.out.println("成功连接MYSQL服务器");// Success connect Mysql server!
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from notice");// help_topic
while (rs.next())
{
System.out.println(rs.getString("title"));
}
}
catch (Exception e)
{
System.out.print("get data error!");
e.printStackTrace();
}
connect.close();// 关闭连接 System.out.println("关闭连接MySQL驱动!"); }
}
自己好好看看把
Config:解决配置的问题,比如获取ip,userid,pwd什么的,还有一些别的配置参数,没有往外摘,所有配置信息写在一个java属性文件里。
DBConnectionPool:一个自己做的连接池,主要是限制DB连接数,其它的不会作了。
DBConnection:就用这个真正链接数据库了。有个方法用于返回一个DB连接。
MySQLConnection和SQLServerConnection:看名字就知道了。都是DBConnection的子类。代码:
Config.java
package base;import java.io.*;
import java.util.*;
import org.apache.log4j.*;public class Config
{
private static Logger logger = Logger.getLogger(Config.class.getName()); private static boolean alredayCreateInstance = false; private static Config instance = null; private String dbserver_id; private String dbserver_pwd; private String dbserver_port; private String dbserver_ip; private String dbserver_dbname; private String dbserver_dbtype; private int dbserver_totalConnection; private int dbserver_reConnecTime; private String higherupserver_ip; private int higherupserver_port; private int listener_port_to_center; private int listener_port_to_bar; private int create_server_socket_retry; private int sleep_MSEL; private String local_ip; private String locale_country; private String locale_language; private Config(String fileName)
{
FileInputStream f = null;
// open config file
Properties property = new Properties();
try
{
f = new FileInputStream(new File(fileName));
property.load(f);
// dbserver
dbserver_id = property.getProperty("dbserver_id");
dbserver_pwd = property.getProperty("dbserver_pwd");
dbserver_ip = property.getProperty("dbserver_ip");
dbserver_port = property.getProperty("dbserver_port");
dbserver_dbname = property.getProperty("dbserver_dbname");
dbserver_dbtype = property.getProperty("dbserver_dbtype");
dbserver_totalConnection = (new Integer(property
.getProperty("dbserver_totalConnection"))).intValue();
dbserver_reConnecTime = (new Integer(property
.getProperty("dbserver_reConnecTime"))).intValue();
// higherup server
higherupserver_ip = property.getProperty("higherupserver_ip");
higherupserver_port = new Integer(property
.getProperty("higherupserver_port")).intValue();
// litener port
listener_port_to_center = new Integer(property
.getProperty("listener_port_to_center")).intValue();
listener_port_to_bar = new Integer(property
.getProperty("listener_port_to_bar")).intValue();
// liten port
listener_port_to_center = new Integer(property
.getProperty("listener_port_to_center")).intValue();
listener_port_to_bar = new Integer(property
.getProperty("listener_port_to_bar")).intValue();
create_server_socket_retry = new Integer(property
.getProperty("create_server_socket_retry")).intValue();
local_ip = property.getProperty("local_ip");
// local
locale_country = property.getProperty("locale_country");
locale_language = property.getProperty("locale_language");
// time
sleep_MSEL = new Integer(property.getProperty("sleep_MSEL"))
.intValue();
// ftp server
// web server
alredayCreateInstance = true;
logger.info("dbserver_id: " + dbserver_id);
logger.info("dbserver_pwd: " + dbserver_pwd);
logger.info("dbserver_ip: " + dbserver_ip);
logger.info("dbserver_port: " + dbserver_port);
logger.info("dbserver_dbname: " + dbserver_dbname);
logger.info("dbserver_dbtype: " + dbserver_dbtype);
logger.info("dbserver_totalConnection: "
+ dbserver_totalConnection);
logger.info("dbserver_reConnecTime: " + dbserver_reConnecTime);
logger.info("higherupserver_ip: " + higherupserver_ip);
logger.info("higherupserver_port: " + higherupserver_port);
logger.info("listener_port_to_center: " + listener_port_to_center);
logger.info("listener_port_to_bar: " + listener_port_to_bar);
logger.info("locale_ip: " + local_ip);
logger.info("sleep_MSEL: " + sleep_MSEL);
}
catch (FileNotFoundException e)
{
// TODO 自动生成 catch 块
// e.printStackTrace();
logger.error(e.toString());
System.exit(0);
}
catch (IOException e)
{
// TODO 自动生成 catch 块
// e.printStackTrace();
logger.error(e.toString());
System.exit(0);
}
finally
{
try
{
if (f != null)
f.close();
}
catch (IOException e)
{
// e.printStackTrace();
logger.error(e.toString());
System.exit(0);
}
}
} public static Config getInstance()
{
if (!alredayCreateInstance)
{
instance = new Config("config.properties");
}
return instance;
} public String getDbserver_dbname()
{
return dbserver_dbname;
} public String getDbserver_dbtype()
{
return dbserver_dbtype;
} public String getDbserver_id()
{
return dbserver_id;
} public String getDbserver_ip()
{
return dbserver_ip;
} public String getDbserver_port()
{
return dbserver_port;
} public String getDbserver_pwd()
{
return dbserver_pwd;
} public int getDbserver_reConnecTime()
{
return dbserver_reConnecTime;
} public int getDbserver_totalConnection()
{
return dbserver_totalConnection;
} public String getHigherupserver_ip()
{
return higherupserver_ip;
} public int getHigherupserver_port()
{
return higherupserver_port;
} public int getListener_port_to_bar()
{
return listener_port_to_bar;
} public int getListener_port_to_center()
{
return listener_port_to_center;
} public int getCreate_server_socket_retry()
{
return create_server_socket_retry;
} public String getLocal_ip()
{
return local_ip;
} public Locale getLocale()
{
return new Locale(locale_language, locale_country);
} public int getSleep_MSEL()
{
return sleep_MSEL;
} public static void main(String[] args)
{
Config.getInstance();
}}
if (Request["typenameid"] != null)
{
if (this.TreeView1.Nodes[0].Selected == true)
{
this.TreeView1.SelectedNode.Expand();
}
}
}
import java.util.*;public class StudentDB { private Connection conn = null; //创建连接对象
private Statement stat = null; //得到Statement对象 private String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
private String url;
private String userName = "sa"; //定义变量做登陆的用户名
private String pwd = ""; //密码 private String getURL() {
url = "jdbc:microsoft:sqlserver://localhost:1433;databaseName=Student";
return url;
} public Connection ConnectionDB() { //连接数据库
try {
Class.forName(driver); //加载驱动
conn = DriverManager.getConnection(getURL(), userName, pwd);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} catch (SQLException ex) {
ex.printStackTrace();
}
return conn;
}
config.property
##locale
locale_country=CN
locale_language=zh
dbserver_id=sojtest
dbserver_pwd=ilovethiswork
dbserver_ip=1.1.1.1
dbserver_port=1433
dbserver_dbname=CenterDB
dbserver_dbtype=SQLServer
dbserver_totalConnection=10
dbserver_reConnecTime=5
##listener port
listener_port_to_center=10000
listener_port_to_bar=10000
##higherup server
higherupserver_port=10000
higherupserver_ip=1.1.1.1
#####################
##create ServerSocket retry times
create_server_socket_retry=5
#####################
local_ip=192.168.1.181
#####################
sleep_MSEL=200
import java.util.*;
import org.apache.log4j.*;
import base.Config;
import base.db.connection.*;public class DBConnectionPool
{
private static Logger logger = Logger.getLogger(DBConnectionPool.class
.getName()); private static DBConnectionPool poolInstance = null; private Connection[] pool; private Object[] catcher; int totalConnection; public static DBConnectionPool getPoolInstance()
{
if (poolInstance == null)
poolInstance = new DBConnectionPool();
return poolInstance;
} public Connection getConnectin(Object b)
{
synchronized (catcher)
{
for (int i = 0; i < totalConnection; i++)
{
if (catcher[i] == null)
{
catcher[i] = b;
logger.info(toString()+b.toString()+" catch a connection.");
return pool[i];
}
}
}
return null;
} public void freeConnection(Object b)
{
logger.info(toString()+b.toString() + " freeConnection");
synchronized (catcher)
{
for (int i = 0; i < totalConnection; i++)
{
if (catcher[i] == b)
{
catcher[i] = null;
break;
}
}
}
} public String toString()
{
return new String("[DBConnectionPool]");
}
public String showPool()
{
Collection out = new ArrayList();
out.add(new String("[DBConnectionPool]"));
for (int i = 0; i < totalConnection; i++)
{
out.add(new String("[" + i + "," + catcher[i] + "]"));
}
return out.toString();
}
public void closeAllConnection()
{
for(int i=0;i<pool.length;i++)
{
if(pool[i]!=null)
{
try
{
pool[i].close();
}
catch (SQLException e)
{
// TODO 自动生成 catch 块
e.printStackTrace();
continue;
}
}
}
for(int i=0;i<pool.length;i++)
{
if(pool[i]!=null)
{
try
{
pool[i].close();
}
catch (SQLException e)
{
// TODO 自动生成 catch 块
e.printStackTrace();
continue;
}
}
}
} private DBConnectionPool()
{
logger.info(toString()+" constr ");
// dbconfig file dbconfig.properties
// create DBConfgi obj
Config dbconf;
try
{
dbconf = Config.getInstance();
String dbtype = dbconf.getDbserver_dbtype();
totalConnection = dbconf.getDbserver_totalConnection();
DBConnection dbcon = (DBConnection) Class.forName(
"base.db.connection." + dbtype + "Connection")
.newInstance();
dbcon.setDBConfig(dbconf);
pool = new Connection[totalConnection];
catcher = new Object[totalConnection];
int reConnecTime = dbconf.getDbserver_reConnecTime();
// get dbconnection
for (int i = 0; i < totalConnection; i++)
{
for (int j = 0; j < reConnecTime; j++)
{
pool[i] = dbcon.getConnection();
if (pool[i] == null)
continue;
else
break;
}
catcher[i] = null;
}
}
catch (InstantiationException e)
{
// TODO 自动生成 catch 块
// e.printStackTrace();
logger.error(e.toString());
}
catch (IllegalAccessException e)
{
// TODO 自动生成 catch 块
// e.printStackTrace();
logger.error(e.toString());
}
catch (ClassNotFoundException e)
{
// TODO 自动生成 catch 块
// e.printStackTrace();
logger.error(toString()+" class can't be finded");
} }
public void finalize()
{
for(int i=0;i<pool.length;i++)
{
if(pool[i]!=null)
try
{
pool[i].close();
}
catch (SQLException e)
{
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
} public static void main(String[] args)
{
int i = 0;
Logger logger = Logger.getLogger(DBConnectionPool.class.getName());
PropertyConfigurator.configure("log4j.properties");
do
{
String s = new String("tester "+i);
DBConnectionPool dbp = DBConnectionPool.getPoolInstance();
logger.info(dbp.getConnectin(s));
// dbp.freeConnection(s);
} while (++i < 6);
logger.info("end");
}
}
import java.sql.*;import base.Config;public abstract class DBConnection
{
protected Config dbconf;
protected String id;
protected String pwd;
protected String ip;
protected String port;
protected String dbname;
protected String connectionURL;
public void setDBConfig(Config d)
{
this.dbconf=d;
}
abstract public Connection getConnection();
}
import org.apache.log4j.*;public class SQLServerConnection extends DBConnection
{
private static Logger logger = Logger.getLogger(SQLServerConnection.class
.getName()); public Connection getConnection()
{
id = dbconf.getDbserver_id();
pwd = dbconf.getDbserver_pwd();
ip = dbconf.getDbserver_ip();
port = dbconf.getDbserver_port();
dbname = dbconf.getDbserver_dbname();
connectionURL = "jdbc:sqlserver://" + ip + ":" + port
+ ";databaseName=" + dbname + ";user=" + id + ";password="
+ pwd;
Connection c = null;
try
{
// System.out.print(this+" urlis::"+connectionURL+"\n");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
c = DriverManager.getConnection(connectionURL);
}
catch (ClassNotFoundException e)
{
// TODO: handle exception
logger.error(e.toString());
}
catch (SQLException e)
{
logger.error(e.toString());
}
return c;
}
}