package Jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLIntegrityConstraintViolationException; import java.sql.SQLSyntaxErrorException; import java.sql.Statement; import java.util.ArrayList; public class Jdbc { private String sqlInsert; private ResultSet selectRs; private boolean flg = true; public Connection JdbcSelect(String user) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@192.168.0.195:1521:fbjb01"; String password = "111"; // if(user.equals("testxjj5")) // { // password = "111"; // }else // { // password = "111"; // } return DriverManager.getConnection(url, user, password); } public Connection JdbcInsert() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@192.168.0.195:1521:fbjb01"; String user = "testxjj5"; String password = "111"; return DriverManager.getConnection(url, user, password); } // 列名取得 @SuppressWarnings("unchecked") public ArrayList select(String sql, String user) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Connection conn = JdbcSelect(user); Statement st = conn.createStatement(); System.out.println(sql); selectRs = st.executeQuery(sql); int row = 0; ArrayList colList = new ArrayList(); while (selectRs.next()) { colList.add(selectRs.getObject("COLUMN_NAME")); row++; } st.close(); conn.close(); return colList; } // テーブルレコードを取得 @SuppressWarnings("unchecked") public ArrayList select(String sql, String user, ArrayList colList) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Connection conn = JdbcSelect(user); Statement st = conn.createStatement(); System.out.println(sql); selectRs = st.executeQuery(sql); int row = 0; ArrayList valueList = new ArrayList<ArrayList>(); while (selectRs.next()) { ArrayList list = new ArrayList(); for (int i = 0; i < colList.size(); i++) { // System.out.println(selectRs.getObject(colList.get(i).toString())); // list.add(selectRs.getObject(colList.get(i).toString().replace("A.", "").replace("B.", ""))); list.add(selectRs.getString(i+1)); } valueList.add(row, list); row++; } st.close(); conn.close(); return valueList; } @SuppressWarnings("unchecked") public void insert(String table, ArrayList colList, ArrayList valueList) { int i = 0; try { Connection conn = JdbcInsert(); conn.setAutoCommit(flg); Statement st = conn.createStatement(); SqlCreate sqlCreate = new SqlCreate(); for (i = 0; i < valueList.size(); i++) { sqlInsert = sqlCreate.insertCreate(table, colList, (ArrayList) valueList.get(i)); try { System.out.println(sqlInsert); st.executeUpdate(sqlInsert); } catch (SQLIntegrityConstraintViolationException e) { System.out.println("/******************************************************************************************/"); System.out.println(sqlInsert); e.printStackTrace(); System.out.println(i); System.out.println("/******************************************************************************************/"); continue; }catch (SQLSyntaxErrorException e) { System.out.println("/******************************************************************************************/"); System.out.println(sqlInsert); e.printStackTrace(); System.out.println(i); System.out.println("/******************************************************************************************/"); continue; } } st.close(); conn.close(); } catch (Exception e) { System.out.println(i); e.printStackTrace(); } } public void insert(String table, String sql) { try { Connection conn = JdbcInsert(); conn.setAutoCommit(flg); Statement st = conn.createStatement(); try { System.out.println(sql); st.executeUpdate(sql); } catch (SQLIntegrityConstraintViolationException e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); }catch (SQLSyntaxErrorException e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); } } catch (Exception e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); } } @SuppressWarnings("unchecked") public void update(String table, ArrayList colList, ArrayList valueList) { int i = 0; try { Connection conn = JdbcInsert(); conn.setAutoCommit(flg); Statement st = conn.createStatement(); SqlCreate sqlCreate = new SqlCreate(); for (i = 0; i < valueList.size(); i++) { String updateInsert = sqlCreate.updateCreate(table, colList, (ArrayList) valueList.get(i)); try { int returnValue = st.executeUpdate(updateInsert); System.out.println(returnValue); } catch (SQLIntegrityConstraintViolationException e) { System.out.println("/******************************************************************************************/"); System.out.println(updateInsert); e.printStackTrace(); System.out.println(i); System.out.println("/******************************************************************************************/"); continue; }catch (SQLSyntaxErrorException e) { System.out.println("/******************************************************************************************/"); System.out.println(updateInsert); e.printStackTrace(); System.out.println(i); System.out.println("/******************************************************************************************/"); continue; } } st.close(); conn.close(); } catch (Exception e) { System.out.println(i); e.printStackTrace(); } } public void update(String table, String sql) { try { Connection conn = JdbcInsert(); conn.setAutoCommit(flg); Statement st = conn.createStatement(); try { int cnt = st.executeUpdate(sql); System.out.println(sql + "/n" + cnt); } catch (SQLIntegrityConstraintViolationException e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); }catch (SQLSyntaxErrorException e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); } } catch (Exception e) { System.out.println("/******************************************************************************************/"); System.out.println(sql); e.printStackTrace(); System.out.println("/******************************************************************************************/"); } } }