public class DBHelp<T> {
private static String driver;
private static String url;
private static String username;
private static String password;
private static BasicDataSource dataSource;
static{
Properties properties = new Properties();
try {
//讀取到src目錄中存放的db.properties配置文件
properties.load(DBHelp.class.getClassLoader().getResourceAsStream("db.properties"));
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
dataSource = new BasicDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setInitialSize(5);
dataSource.setMaxWait(5000);
dataSource.setMaxActive(20);
dataSource.setMinIdle(10);
}
public Connection getConnection(){
try {
Connection conn = dataSource.getConnection();
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List<T> executeForList(RowMapper<T> rowMapper,String sql) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
List<T> list = new ArrayList<T>();
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
rs = stat.executeQuery();
while(rs.next()) {
list.add(rowMapper.mapperRow(rs));
}
System.out.println("SQL"+sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, stat, conn);
}
return list;
}
public List<T> executeForList(String sql,RowMapper<T> rowMapper,Object... args) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
List<T> list = new ArrayList<T>();
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1,args[i]);
}
rs = stat.executeQuery();
while(rs.next()) {
list.add(rowMapper.mapperRow(rs));
}
System.out.println("SQL"+sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, stat, conn);
}
return list;
}
public T executeForObject(String sql,RowMapper<T> rowMapper,Object... args) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
T obj = null;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rs = stat.executeQuery();
if(rs.next()) {
obj = rowMapper.mapperRow(rs);
}
System.out.println("SQL"+sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, stat, conn);
}
return obj;
}
public int executeForCount(String sql,Object... args){
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
int count = 0;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
rs = stat.executeQuery();
for(int i = 0;i < args.length;i++){
stat.setObject(i+1, args[i]);
}
if(rs.next()) {
count = rs.getInt(1);
}
System.out.println("SQL"+sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, stat, conn);
}
return count;
}
public void executeUpdate(String sql,Object... args) {
Connection conn = null;
PreparedStatement stat = null;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
stat.setObject(i+1, args[i]);
}
stat.executeUpdate();
System.out.println("SQL"+sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(stat,conn);
}
}
public void close(PreparedStatement stat,Connection conn) {
close(null,stat,conn);
}
public void close(ResultSet rs,PreparedStatement stat,Connection conn) {
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
public interface RowMapper<T> {
public T mapperRow(ResultSet rs) throws SQLException;
}
public class TourDao {
private DBHelp<Tour> db = new DBHelp<Tour>();
public Tour findByName(String name){
String sql = "SELECT id,tourname FROM t_tour WHERE tourname=?";
Tour t = db.executeForObject(sql, new TourRowMapper(), name);
return t;
}
public void insertSale(String name){
String sql = "INSERT INTO t_tour (tourname) VALUE(?)";
db.executeUpdate(sql, name);
}
public class TourRowMapper implements RowMapper<Tour>{
public Tour mapperRow(ResultSet rs) throws SQLException {
Tour t = new Tour();
t.setId(rs.getInt("id"));
t.setTourname(rs.getString("tourname"));
return t;
}
}
}