import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBConnection { public static Connection getConnection()throws SQLException,IllegalAccessException{ Connection conn= null; try{ Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); String url ="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test"; String user = "sa"; String password = ""; conn = DriverManager.getConnection(url, user, password); } catch( ClassNotFoundException e){System.out.println(e.getMessage());} catch (SQLException ex){ System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); } return conn; }}
//
public void insert(part newpart) throws Exception{ Connection conn = null; PreparedStatement ps = null; String sql = "insert into part(pno,name)" + " values(?,?)"; try{ conn = DBConnection.getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1,newpart.getPno()); ps.setString(2,newpart.getPname()); ps.executeUpdate(); }catch(SQLException sqle){ throw new Exception("insert data exception:" + sqle.getMessage()); }finally{ try{ if(ps != null){ ps.close(); } }catch (Exception e){ throw new Exception("ps close exception:" + e.getMessage()); } } try{ if(conn != null){ conn.close(); } }catch (Exception e){ throw new Exception("connection close exception:" + e.getMessage()); } }
public ArrayList<part> select(String sql) throws Exception{ ArrayList result = new ArrayList<part>(); Connection conn = null; Statement stmt = null; ResultSet rs = null; System.out.println("enter into select"); try{ conn = DBConnection.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ part newpart = new part(); newpart.setPno(rs.getInt("pno")); newpart.setPname(rs.getString("name")); result.add(newpart); System.out.println("added once"); } } catch(SQLException sqle){ throw new SQLException("select data exception:"+sqle.getMessage()); }finally{ try{ if(rs != null){ rs.close(); } }catch(Exception e){ throw new Exception("statement close exception"+e.getMessage()); } try{ if(stmt != null){ stmt.close(); } }catch(Exception e){ throw new Exception("statement close exception"+e.getMessage()); } try{ if(conn != null){ conn.close(); } }catch(Exception e){ throw new Exception("statement close exception"+e.getMessage()); } } return result; }
