java 连接数据库实例

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GSYDao {
//定义对象
private PreparedStatement pstmt;
private Connection conn;
private ResultSet rs;

//加载驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

//方法卸载构造方法内，开始就会调用。
public GSYDao(String sql){
try {
//获取数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost/test",
"root", "admin");
//存入pstmt对象，以供使用
pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
} catch (Exception e) {
e.printStackTrace();
}
}

public void executeUpdate(String sql) throws SQLException {
System.out.println(sql);
pstmt.executeUpdate(sql);
}

public ResultSet executeQuery(String sql) throws SQLException {
System.out.println(sql);
rs = pstmt.executeQuery(sql);
return rs;
}

public void close() {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}

//main方法
public static void main(String[] args) {

}

}
连接池的写法

package com.database.config;

public class DB
{
public final static String driver="sun.jdbc.odbc.JdbcOdbcDriver";

//public final static String driver="com.mysql.jdbc.Driver";

public final static String dbName="stu.mdb";

public final static String url="jdbc:odbc:driver= {Microsoft Access Driver (*.mdb)};DBQ=D:/javaWeb/Stu/WEB-INF/classes/stu.mdb";

//public final static String url="jdbc:mysql://localhost/test";

public final static String port="";

public final static String username="";

public final static String pwd="";

public final static int max =50;

public final static int min=5;

/*
* 经测试以下参数为最佳合理配置，请勿更改!
* 根据不同的电脑配置,配置不同的参数
* */
public final static int inc=5;

public final static int timeout=20; //等待timeout时间后获取连接

public final static int timer=1000; //多少时间关掉多余的连接

}


//数据库连接包装类
ConnState.java

/*
* 创建日期 Jan 2, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;

public class ConnState
{
private boolean isOpen=false;

private Connection conn;

public Connection getConn()

{
return conn;
}

public void setConn(Connection conn)
{
this.conn = conn;
}

public boolean isOpen()
{
return isOpen;
}

public void setOpen(boolean isOpen)
{
this.isOpen = isOpen;
}


}

DatabasePool.java
/*
* 创建日期 Jan 2, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;

import com.database.config.DB;



/*数据库连接池
* 程序会将多余的连接关掉和在连接池里删除掉
*
* */

public class DatabasePool
{
private static List<ConnState> pool;

private static DatabasePool instance;

public TimerTask task=new TimerTask()
{
public void run()
{
System.out.println("run()");
System.out.println("DatabasePool 类使用中的连接总数:"+ getConnCount());
// if(pool.size()>DB.min)
{
Iterator<ConnState> it =pool.iterator();

while(it.hasNext())
{
ConnState st=it.next();
if(pool.size()<=DB.min)
{
if(st.isOpen()==true && null!=st.getConn())
{
try
{
st.getConn().commit();
st.setOpen(false);
}catch(SQLException e)
{
System.out.println("强行执行sql语句失败");
}
}
}
else
{
if(st.isOpen()==true && null!=st.getConn())
{
try
{
st.getConn().commit();
st.getConn().close();
//it.remove();
}catch(SQLException e)
{
try
{
Thread.sleep(200);
st.getConn().commit();
st.getConn().close();
}catch(InterruptedException ex)
{
ex.printStackTrace();
}catch(SQLException se)
{
se.printStackTrace();
}
}finally
{
try
{
st.setConn(null);
st=null;
System.out.println("最后的关闭");
it.remove();
System.gc();
}catch(Exception e)
{
e.printStackTrace();
}
}
}
}
}
}
}
};
public DatabasePool() throws Exception
{
/*
* 连接池使用ArrayList对象进行封装ConnState，这里使用了泛型
* */
pool=new ArrayList<ConnState>();
Init();
StartTimer();
}
/* 创建数据库连接
* 返回ConnState对象
* ConnState 对象封装Connection对象
* */
private static ConnState createConnection() throws ClassNotFoundException,SQLException
{
Connection conn;

Class.forName(DB.driver);

conn=DriverManager.getConnection(DB.url);

conn.setAutoCommit(false);

ConnState state =new ConnState();

state.setConn(conn);

return state;
}

/*数据库连接池的初始化过程*/
private static void Init() throws Exception
{
if(null==pool)
{
getInstance();
}
else
{

/*
* 创建预定义大小的连接
* */
for(int i=0;i<DB.min;i++)
{
pool.add(createConnection());
}
}
}

private static void inc() throws Exception
{
if(null==pool)
{
getInstance();
Init();
}
else if(pool.size()<DB.max)
{
int c=(pool.size()+DB.inc)>DB.max?DB.max-pool.size():DB.inc;

for(int i=0;i<c;i++)
{
pool.add(createConnection());
}
}
}
private static DatabasePool getInstance() throws Exception
{
instance =new DatabasePool();
return instance;
}
/*
* 返回数据库连接
* */
public synchronized static Connection getConnection() throws Exception
{
if(null ==instance )
{
getInstance();
}
Connection conn=findConnection();
int count=1;
/*
* 如果没有找到可用的连接或三次
* 将会抛出异常
* */
while(null==conn && count<=3)
{
count++;
Thread.sleep(DB.timeout);
conn=findConnection();
if(null!=conn)
{
break;
}
else
{
inc(); // 增加指定个数的连接
conn=findConnection();
}

}

if(null==conn) throw new Exception("当前连接己达到最连接池,没有可以使用的数据库连接了");
return conn;
}

private static Connection findConnection() throws Exception
{
Iterator<ConnState> it =pool.iterator();
Connection conn=null;

while(it.hasNext())
{
ConnState cs=it.next();
if(cs.isOpen()==false)
{
conn=cs.getConn();
cs.setOpen(true);
break;
}
}
return conn;
}

public void StartTimer()
{
Timer time=new Timer();
time.schedule(task, DB.timer,2000);
}

public static int getConnCount()
{
return pool.size();
}
}
SqlAware.java

/*
* 创建日期 Jan 3, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.util.List;
import java.util.Map;

public interface SqlAware
{
public List getData(String sql) throws Exception;

public Map getOneData(String sql) throws Exception;

public int execute(String sql) throws Exception;

public int executeBatch(String sql) throws Exception;
}


SqlDao.java
/*
* 创建日期 Jan 3, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.database.exception.SqlException;

public class SqlDao implements SqlAware
{
public void close(ResultSet rs) throws SQLException
{
if(null!=rs)
{
rs.close();
}
}

public void close(ResultSet rs,Statement stmt) throws SQLException
{
if(null!=rs) rs.close();
if(null!=stmt) stmt.close();
}

public void close(PreparedStatement pstmt) throws SQLException
{
if(null!=pstmt) pstmt.close();
}

public void close(ResultSet rs,PreparedStatement pstmt) throws SQLException
{
if(null!=rs) rs.close();
if(null!=pstmt) pstmt.close();
}

public int executeBatch(ArrayList<Map<String,String>> list, String sql) throws Exception
{
// TODO 自动生成方法存根
return 0;
}

public int executeBatch(String sql) throws Exception
{
// TODO 自动生成方法存根
return 0;
}

public int execute(String sql) throws Exception
{
int n=0;

Connection conn=DatabasePool.getConnection();

PreparedStatement pstmt=conn.prepareStatement(sql);

n=pstmt.executeUpdate();

close(pstmt);

return n;
}

public List<Map<String,String>> getData(String sql) throws Exception
{
ArrayList<Map<String,String>> list=new ArrayList<Map<String,String>>();

Connection conn=DatabasePool.getConnection();

PreparedStatement pstmt=conn.prepareStatement(sql);

ResultSet rs=pstmt.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

int c=rsmd.getColumnCount();

while(rs.next())
{

Map<String,String> hp=new HashMap<String,String>();

for(int i=1;i<=c;i++)
{
String label=rsmd.getColumnName(i);

System.out.println(label);

String value=rs.getString(i);

hp.put(label, value);

if(null==hp) throw new SqlException();

}

list.add(hp);

}

close(rs,pstmt);

return list;
}

public Map<String,String> getOneData(String sql) throws Exception
{
HashMap<String,String> hp=null;

Connection conn=DatabasePool.getConnection();

PreparedStatement pstmt=conn.prepareStatement(sql);

ResultSet rs=pstmt.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

int c=rsmd.getColumnCount();

if(rs.next())
{
hp=new HashMap<String,String>(1);

for(int i=1;i<c;i++)
{
String label=rsmd.getColumnName(i);

hp.put(label, rs.getString(i));
}
}

close(rs,pstmt);

if(null==hp) throw new SqlException();
return hp;

}


}


一般写一个业务操作类继承SqlDao类就可以了。