在编写Java连接数据库的时候,常用到数据源;
在配置时我们通常这样改写Server.xml的Context(以Eclipse为例)
<Context docBase="D:/JaryProject/workspace/TTT/.deployables/TTT" path="/TTT" reloadable="true" source="org.eclipse.jst.j2ee.server.web:TTT">
<Resource name="jdbc/meeting" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/meeting">
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>password</name>
<value>1234</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/wdsa?useUnicode=true&characterEncoding=utf8</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>username</name>
<value>root</value>
</parameter>
</ResourceParams>
</Context>
然后写一个Java类连接此连接池(ConnPool.Java):
import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;
public class ConnPool
{
private static ConnPool instace;
//从连接池中取的连接对象
public synchronized Connection getConnection()
{
Connection con = null;
Context ct = null;
try
{
ct = new InitialContext();
DataSource ds = (DataSource)ct.lookup("java:comp/env/jdbc/meeting");
con = (Connection)ds.getConnection();
}catch(Exception ex)
{
System.out.print("Get Connection Error");
}
return con;
}
//释放连接
public synchronized void freeConnection(Connection con)
{
try
{
con.close();
}catch(Exception e)
{
System.out.println("Close Connection Error");
}
}
}
那么在程序中可以这样连接:
ConnPool connpool = new ConnPool();
Connection con = connpool.getConnection();
try
{
// con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareCall("use BaseDatabase;exec P_Insert_Company ?,?,?,?,?,?,?,?,?,?,?,?,?,?");
pstmt.setString(1,comp_code);
.......................
pstmt.executeUpdate();
//Get All Table Name;
CallableStatement cstmt = con.prepareCall("use BaseDatabase;exec P_getAllTable");
ResultSet rs = cstmt.executeQuery();
while(rs.next())
{
String tablename = rs.getString("name");
String sqlstate = "use BaseDatabase;select * into "+comp_code+".."+tablename+" from "+tablename;
PreparedStatement ps = con.prepareStatement(sqlstate);
ps.executeUpdate();
}
PreparedStatement ps2 = con.prepareStatement(insertusermsg);
ps2.executeUpdate();
// con.commit();
return true;
}catch(Exception e)
{
try
{
// con.rollback();
e.printStackTrace();
return false;
}catch(Exception ex)
{
ex.printStackTrace();
return false;
}
}finally
{
connpool.freeConnection(con);
}
}
在配置时我们通常这样改写Server.xml的Context(以Eclipse为例)
<Context docBase="D:/JaryProject/workspace/TTT/.deployables/TTT" path="/TTT" reloadable="true" source="org.eclipse.jst.j2ee.server.web:TTT">
<Resource name="jdbc/meeting" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/meeting">
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>password</name>
<value>1234</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/wdsa?useUnicode=true&characterEncoding=utf8</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>username</name>
<value>root</value>
</parameter>
</ResourceParams>
</Context>
然后写一个Java类连接此连接池(ConnPool.Java):
import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;
public class ConnPool
{
private static ConnPool instace;
//从连接池中取的连接对象
public synchronized Connection getConnection()
{
Connection con = null;
Context ct = null;
try
{
ct = new InitialContext();
DataSource ds = (DataSource)ct.lookup("java:comp/env/jdbc/meeting");
con = (Connection)ds.getConnection();
}catch(Exception ex)
{
System.out.print("Get Connection Error");
}
return con;
}
//释放连接
public synchronized void freeConnection(Connection con)
{
try
{
con.close();
}catch(Exception e)
{
System.out.println("Close Connection Error");
}
}
}
那么在程序中可以这样连接:
ConnPool connpool = new ConnPool();
Connection con = connpool.getConnection();
try
{
// con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareCall("use BaseDatabase;exec P_Insert_Company ?,?,?,?,?,?,?,?,?,?,?,?,?,?");
pstmt.setString(1,comp_code);
.......................
pstmt.executeUpdate();
//Get All Table Name;
CallableStatement cstmt = con.prepareCall("use BaseDatabase;exec P_getAllTable");
ResultSet rs = cstmt.executeQuery();
while(rs.next())
{
String tablename = rs.getString("name");
String sqlstate = "use BaseDatabase;select * into "+comp_code+".."+tablename+" from "+tablename;
PreparedStatement ps = con.prepareStatement(sqlstate);
ps.executeUpdate();
}
PreparedStatement ps2 = con.prepareStatement(insertusermsg);
ps2.executeUpdate();
// con.commit();
return true;
}catch(Exception e)
{
try
{
// con.rollback();
e.printStackTrace();
return false;
}catch(Exception ex)
{
ex.printStackTrace();
return false;
}
}finally
{
connpool.freeConnection(con);
}
}