Web开发 用于操作数据库

package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class TestDAO {
private Connection conObj = null;

public TestDAO() {
if(conObj==null){
try {
conObj=TestDBAccess.getInstance().getConnection();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}

public ArrayList select(String keyStr) {
String sql = "";
PreparedStatement pstmt = null;
ResultSet rset = null;
ArrayList dataList = new ArrayList();

// SQL作成
sql = " SELECT * FROM dbo.TableName ";

// エリアコード
if (keyStr.equals("") || keyStr == null) {
sql += " WHERE COL1 LIKE ? ";
keyStr = "%";
} else {
sql += " WHERE COL1 = ? ";
}

try {

pstmt = this.conObj.prepareStatement(sql);
pstmt.setString(1, keyStr);

rset = pstmt.executeQuery();

while (rset.next()) {
ArrayList rowList = new ArrayList();

// データ格納
rowList.add(this.checkString(rset.getString("COL1")));
rowList.add(this.checkString(rset.getString("COL2")));
rowList.add(this.checkString(rset.getString("COL3")));
rowList.add(this.checkString(rset.getString("COL4")));
rowList.add(this.checkString(rset.getString("COL5")));

dataList.add(rowList);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (rset != null)
rset.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return dataList;
}

public boolean delete(String keyStr) {
boolean ret = true;
PreparedStatement pstmt = null;
String sql = "";

// SQL作成
sql = " DELETE FROM dbo.TableName ";
sql += " WHERE ";
sql += " ID = ? ";

try {
// データセット
pstmt = this.conObj.prepareStatement(sql);
// エリアコード
pstmt.setString(1, keyStr);

// SQL実行
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
ret = false;
} finally {
try {
if (pstmt != null)
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return ret;
}

public boolean insert(ArrayList dataList) {
boolean ret = true;
String sql = "";
PreparedStatement pstmt = null;

sql = " INSERT INTO dbo.TableName ( ";
sql += " COL1, ";
sql += " COL2, ";
sql += " COL3, ";
sql += " COL4, ";
sql += " COL5 ";
sql += " ) values ( ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "? ";
sql += ")";

try {
pstmt = this.conObj.prepareStatement(sql);

pstmt.setString(1, this.checkString(dataList.get(0)));// COL1
pstmt.setString(2, this.checkString(dataList.get(1)));// COL2
pstmt.setString(3, this.checkString(dataList.get(2)));// COL3
pstmt.setString(4, this.checkString(dataList.get(3)));// COL4
pstmt.setString(5, this.checkString(dataList.get(4)));// COL5

// SQL実行
pstmt.execute();

} catch (Exception e) {
e.printStackTrace();
ret = false;
} finally {
try {
if (pstmt != null)
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return ret;
}

public boolean update(ArrayList dataList) {
boolean ret = true;
String sql = "";
PreparedStatement pstmt = null;

// SQL作成
sql = " UPDATE dbo.TableName SET ";
sql += " COL1 = ?, ";
sql += " COL2 = ?, ";
sql += " COL3 = ?, ";
sql += " COL4 = ?, ";
sql += " COL5 = ? ";
sql += " WHERE ";
sql += " ID = ? ";

try {
pstmt = this.conObj.prepareStatement(sql);

pstmt.setString(1, this.checkString(dataList.get(0)));// COL1
pstmt.setString(2, this.checkString(dataList.get(1)));// COL2
pstmt.setString(3, this.checkString(dataList.get(2)));// COL3
pstmt.setString(4, this.checkString(dataList.get(3)));// COL4
pstmt.setString(5, this.checkString(dataList.get(4)));// COL5

// SQL実行
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
ret = false;
} finally {
try {
if (pstmt != null)
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return ret;
}

private String checkString(Object obj) {
return obj == null ? "" : obj.toString().trim();
}
}