====1.
package common;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import java.sql.*;
public class PropertiesConnection extends Properties{
private static PropertiesConnection instance=null;
private PropertiesConnection() throws IOException{
InputStream is=getClass().getResourceAsStream("/db.properties");
try {
this.load(is);
} catch (IOException e) {
throw e;
}
}
private static PropertiesConnection getInstance() throws IOException{
if(instance==null){
makeInstance();
return instance;
}
return instance;
}
private static synchronized void makeInstance() throws IOException{
if(instance==null){
instance=new PropertiesConnection();
}
}
public static synchronized Connection getCon() throws IOException, ClassNotFoundException, SQLException {
String Driver=PropertiesConnection.getInstance().getProperty("DRIVER");
String Url=PropertiesConnection.getInstance().getProperty("URL");
String User=PropertiesConnection.getInstance().getProperty("USER");
String Pwd=PropertiesConnection.getInstance().getProperty("PWD");
Class.forName(Driver);
return DriverManager.getConnection(Url,User,Pwd);
}
}
====2.
package common;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.*;
public class PoolConnection {
public static Connection getConnection() throws NamingException,SQLException{
//context接口提供了查找JNDI Resource的接口
Context ct=new InitialContext();
//datasource对象由tomcat提供,所以不能在程序中实例化获得,需查找获取
DataSource ds=(DataSource)ct.lookup("java:comp/env/addressBook");
//获取datasource对象后,由对象的getConnection方法获取一个连接对象
return ds.getConnection();
}
public static void close(Connection con,PreparedStatement ps,ResultSet rs) {
try {
if (rs != null) {
rs.close();
rs = null;
}
if(ps!=null){
ps.close();
ps=null;
}
if(con!=null){
con.close();
con=null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
====3.
package common;
import java.sql.*;
public class DBConnection {
private static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL="jdbc:sqlserver://localhost:1433;DataBaseName=Book";
private static final String USER="sa";
private static final String PWD="sa";
private static DBConnection instance=null;
private DBConnection(){}
public static DBConnection getInstance(){
if(instance==null){
makeInstance();
return instance;
}
return instance;
}
private static synchronized void makeInstance() {
if(instance==null){
instance=new DBConnection();
}
}
public Connection getCon() throws ClassNotFoundException,SQLException{
Connection con=null;
try {
Class.forName(DRIVER);
con=DriverManager.getConnection(URL,USER,PWD);
} catch (ClassNotFoundException e) {
throw e;
}catch (SQLException e) {
throw e;
}
return con;
}
public void close(ResultSet rs,PreparedStatement ps,Connection con) throws SQLException{
try {
if(rs!=null){
rs.close();
rs=null;
}
if(ps!=null){
ps.close();
ps=null;
}
if(con!=null){
con.close();
con=null;
}
} catch (SQLException e) {
throw e;
}
}
}
====4.
package common;
import java.util.List;
import java.sql.*;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
public class CommonDaoConnection {
private String sqlValue;
private List values;
private Connection con;
/*
* 设定con
*/
public void setConnection(Connection _con){
this.con=_con;
}
/*
* 设定sqlValue
*/
public void setSqlValue(String _sqlValue){
this.sqlValue=_sqlValue;
}
/*
* 设定list(sql语句参数)
*/
public void setList(List _values){
this.values=_values;
}
/*
* 执行Query
* return Result
*/
public Result executeQuery() throws SQLException{
Result result=null;
PreparedStatement ps=null;
Statement st=null;
ResultSet rs=null;
try{
//带参数的是执行的PreparedStatement
if(this.values!=null||this.values.size()>0){
con.prepareStatement(this.sqlValue);
this.setValues(ps, values);
rs=ps.executeQuery();
}else{
con.createStatement();
rs=st.executeQuery(sqlValue);
}
result=ResultSupport.toResult(rs);
}finally{
if(rs!=null){
try{rs.close();}catch(SQLException e){}
}
if(st!=null){
try{st.close();}catch(SQLException e){}
}
if(ps!=null){
try{ps.close();}catch(SQLException e){}
}
}
return result;
}
/*
* 执行Update
*/
public int executeUpdate() throws SQLException{
PreparedStatement ps=null;
Statement st=null;
int num=0;
try{
if(this.values!=null||this.values.size()>0){
this.con.prepareStatement(sqlValue);
this.setValues(ps, values);
num=ps.executeUpdate();
}else{
this.con.createStatement();
num=st.executeUpdate(sqlValue);
}
}finally{
if(st!=null){
try{st.close();}catch(SQLException e){}
}
if(ps!=null){
try{ps.close();}catch(SQLException e){}
}
}
return num;
}
/*
* 设置参数
*/
public void setValues(PreparedStatement ps,List values) throws SQLException{
for(int i=0;i<values.size();i++){
ps.setObject(i+1, values.get(i));
}
}
}