数据源连接SQL server

liyao20060101

　　
　　model:

package model;

public class UserInfo {
private int id;
private String userName;
private String address;


public UserInfo(String userName, String address) {
super();
this.userName = userName;
this.address = address;
}

public UserInfo(int id, String userName, String address) {
super();
this.id = id;
this.userName = userName;
this.address = address;
}

public UserInfo() {
super();
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getUserName() {
return userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

}

　　
　　
　　dao:

package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import commons.DbManager;
import model.UserInfo;
public class UserDao {
public int create(UserInfo userInfo) {
Connection conn = null;
PreparedStatement pst = null;
int state = 0;
try {
conn = DbManager.getConnection();
conn.setAutoCommit(false);
pst = conn
.prepareStatement("insert into jdbc values(?,?,)");
pst.setString(1, userInfo.getUserName());
pst.setString(2, userInfo.getAddress());
state = pst.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DbManager.clossConnection(conn, pst, null);
}
return state;
}
public List<UserInfo> findByAccount(UserInfo userInfo) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
List<UserInfo> list = new ArrayList<UserInfo>();
try {
conn = DbManager.getConnection();
conn.setAutoCommit(false);
pst = conn
.prepareStatement("select * from jdbc where address=?");
pst.setString(1, userInfo.getAddress());
rs = pst.executeQuery();
while (rs.next()) {
list.add(new UserInfo(rs.getInt(1), rs.getString(2), rs
.getString(3)));
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DbManager.clossConnection(conn, pst, rs);
}
return list;
}
}

　　
　　
　　commons:

package commons;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DbManager {
public static Connection getConnection() {
Connection conn = null;
try {
Context context = new InitialContext();
DataSource ds = (DataSource) context
.lookup("java:comp/env/jdbc/myinfo");
conn = ds.getConnection();
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void clossConnection(Connection conn, PreparedStatement pst,
ResultSet rs) {
try {
if (rs != null){
rs.close();
}
if (pst !=null){
pst.close();
}
if (conn!= null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

　　
　　
　　数据源的xml配置文件：

<Context>
<Resource name="jdbc/myinfo" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="wdpc"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=test;user=sa;password=wdpc" />
</Context>

　　
　　
　　context.xml放在