DB接続方法ーJDBC

１．ドライバクラスのロード
２．getConnection
３．


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MConnection {
	public Connection getConnection() {

		// ユーザ名
		String user = "●●";
		// パスワード
		String pass = "●●";
		// サーバ名
		String servername = "●●";

		Connection conn = null;

		try {
			// JBBCドライバクラスのロード
			Class.forName("com.mysql.jdbc.Driver");

			// Connectionの作成
			conn = DriverManager.getConnection("jdbc:mysql://localhost:8080/" + servername , user, pass);
			return conn;

		} catch (ClassNotFoundException e) {
			return null;
		} catch (SQLException e) {
			return null;
		} catch (Throwable e) {
			return null;
		}
	}
}





import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.kaishamei.data.DShainData;

public class MShainInfo extends MConnection{
	/**
	 * @param in
	 * @return
	 */
	public DShainData getShainInfo(DShainData in) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rset = null;
		PreparedStatement pstmt = null;
		DShainData info = null;

		try {
			// JBBCドライバクラスのロード
			Class.forName("com.mysql.jdbc.Driver");

			// Connectionの作成
			conn = getConnection();
			// Statementの作成
			stmt = conn.createStatement();
			// SQL
			String sql = "select   ";
			sql += "    SHAIN_ID   ";
			sql += "   ,SHAIN_NM   ";
			sql += "   ,AGE        ";
			sql += "   ,POST_NO    ";
			sql += "   ,ADDRESS_CD ";
			sql += "   ,ADDRESS2   ";
			sql += "   ,ADDRESS3   ";
			sql += "   ,PASSWORD   ";
			sql += "   ,DEL_KBN    ";
			sql += "   ,ENTRY_ID   ";
			sql += "   ,ENTRY_DAY  ";
			sql += "   ,UPDATE_ID  ";
			sql += "   ,UPDATE_DAY ";
			sql += " from SHAIN_INFO  ";
			sql += " where         ";
			sql += "    SHAIN_ID = ? ";

			// Resultsetの作成
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,in.getShainId());
			rset = pstmt.executeQuery();

			// 取得したデータを出力する
			while (rset.next()) {
				info = new DShainData();

				info.setShainId(rset.getString("SHAIN_ID"));
				info.setShainNm(rset.getString("SHAIN_NM"));
				BigDecimal age = rset.getBigDecimal("AGE");
				int iAge = 0;
				if (age != null) {
					iAge = Integer.valueOf(age.toString());
				}
				info.setAge(iAge);
				info.setPostNo(rset.getString("POST_NO"));
				info.setAddressCd(rset.getString("ADDRESS_CD"));
				info.setAddress2(rset.getString("ADDRESS2"));
				info.setAddress3(rset.getString("ADDRESS3"));
				info.setPassword(rset.getString("PASSWORD"));
				info.setDelKbn(rset.getString("DEL_KBN"));
				info.setEntryId(rset.getString("ENTRY_ID"));
				info.setEntryDay(rset.getString("ENTRY_DAY"));
				info.setUpdateId(rset.getString("UPDATE_ID"));
				info.setUpdateDay(rset.getString("UPDATE_DAY"));
				break;
			}

		} catch (SQLException e) {
			String msg = e.toString();
			System.out.println(msg);
		} catch (Throwable e) {
			String msg = e.toString();
			System.out.println(msg);
		} finally {
			try {
				// クローズ処理
				if (rset != null) {
					rset.close();
					rset = null;
				}

				if (stmt != null) {
					stmt.close();
					stmt = null;
				}

				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Throwable e) {
				// nop
				String msg = e.toString();
				System.out.println(msg);
			}
		}
		return info;
	}

	public void upDateSInfo(DShainData upData) {

		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;

		try {
			// JBBCドライバクラスのロード
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// Connectionの作成
			conn = getConnection();
			conn.setAutoCommit(false);
			// Statementの作成
			stmt = conn.createStatement();
			// SQL
			String sql = " UPDATE ";
			sql += "  shain_info ";
			sql += "  set ";
			sql += "    SHAIN_ID = ? ";       // 社員ID
			sql += "   ,SHAIN_NM = ? ";      // 社員名
			sql += "   ,AGE = ? ";      // 年齢
			sql += "   ,POST_NO = ? ";          // 郵便番号
			sql += "   ,ADDRESS_CD = ? ";    // 住所コード
			sql += "   ,ADDRESS2 = ? ";   // 住所２
			sql += "   ,ADDRESS3 = ? ";        // 住所３
			sql += "   ,UPDATE_ID = 'system' ";      // 更新者ID
			sql += "   ,UPDATE_DAY = NOW() ";     // 更新年月日
			sql += "  where ";     // 更新年月日
			sql += "    SHAIN_ID = ? ";       // 社員ID


			// Resultsetの作成
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,upData.getShainId());
			pstmt.setString(2,upData.getShainNm());
			String sAge = Integer.toString(upData.getAge());
			pstmt.setString(3,sAge);
			pstmt.setString(4,upData.getPostNo());
			pstmt.setString(5,upData.getAddressCd());
			pstmt.setString(6,upData.getAddress2());
			pstmt.setString(7,upData.getAddress3());
			pstmt.setString(8,upData.getShainId());

			// INSERT文実行
			pstmt.execute();
			// COMMIT
			conn.commit();

		} catch (ClassNotFoundException e) {
			String msg = e.toString();
			System.out.println(msg);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}
			String msg = e.toString();
			System.out.println(msg);
		} catch (Throwable e) {
			String msg = e.toString();
			System.out.println(msg);
			throw e;
		} finally {
			try {
				// クローズ処理
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}

				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Throwable e) {
				// nop
				String msg = e.toString();
				System.out.println(msg);
			}
		}

	}

}