day17 11.JdbcUtils工具抽取
连接数据库的四个必要条件:driverclass、url、username、password。
package cn.itcast.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ResourceBundle; //使用配置文件 public class JdbcUtils { private static final String DRIVERCLASS; private static final String URL; private static final String USERNAME; private static final String PASSWORD; static{ DRIVERCLASS=ResourceBundle.getBundle("jdbc").getString("driverClass"); URL=ResourceBundle.getBundle("jdbc").getString("url"); USERNAME=ResourceBundle.getBundle("jdbc").getString("username"); PASSWORD=ResourceBundle.getBundle("jdbc").getString("password"); } static{//静态块只执行一次驱动就加载了 try { //将加载驱动操作,放置在静态代码块中,这样就保证了只加载一次。 Class.forName(DRIVERCLASS); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnectin() throws SQLException{//连接就抛SQLException最靠谱 //Class.forName("com.mysql.jdbc.Driver");//在开发中用哪个Statement人家有选择的权利,你不能给它抽取 //2.获取连接 //Connection con = DriverManager.getConnection("jdbc:mysql:///day17", "root", ""); Connection con = DriverManager.getConnection(URL,USERNAME, PASSWORD); return con; } //如果再完善一点,可以写关闭操作 public static void closeConnection(Connection con) throws SQLException{ if(con!=null){ con.close(); } } public static void closeStatement(Statement st) throws SQLException{ if(st!=null){ st.close(); } } public static void closeResultSet(ResultSet rs) throws SQLException{ if(rs!=null){ rs.close(); } } }
driverClass=com.mysql.jdbc.Driver url=jdbc:mysql:///day17 username=root password= #driverClass=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@localhost:1521:MFC #username=scott #password=scott
package cn.itcast.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; import cn.itcast.utils.JdbcUtils; import cn.itcast.utils.JdbcUtils1; //jdbc的crud操作 public class JdbcDemo6 { @Test public void findByIdTest(){ //1.定义sql String sql = "select * from user where id= 1"; Connection con = null; Statement st = null; ResultSet rs = null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 try { con = DriverManager.getConnection("jdbc:mysql:///day17", "root", ""); //3.获取操作sql语句对象Statement st = con.createStatement(); //4.执行sql rs = st.executeQuery(sql); //5.遍历结果集 while(rs.next()){ int id = rs.getInt("id"); //String id = rs.getString("id");//虽然用getString()行,但是用getInt()比较合适 String username = rs.getString("username"); String password = rs.getString("password"); String email = rs.getString("email"); System.out.println(id+" "+username+" "+password+" "+email); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //6.释放资源 try { if(rs !=null ){ rs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(st!=null){ st.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(con!=null){ con.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } //添加操作 @Test public void addTest(){ //定义sql String sql = "insert into user values(null,'张三','123','zs@163.com')"; Connection con = null; Statement st = null; ResultSet rs = null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 try { con = DriverManager.getConnection("jdbc:mysql:///day17", "root", ""); //3.获取操作sql语句对象Statement st = con.createStatement(); //4.执行sql int row = st.executeUpdate(sql); System.out.println(row); if(row!=0){ System.out.println("添加成功"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //6.释放资源 try { if(rs !=null ){ rs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(st!=null){ st.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(con!=null){ con.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } //update操作 @Test public void updateTest(){ //将id=3的人的password修改为456 String password = "456"; String sql = "update user set password='"+password+"' where id=3"; //1.得到Connection Connection con = null; Statement st = null; try { con = JdbcUtils1.getConnectin(); //3.获取操作sql语句对象Statement st = con.createStatement(); //4.执行sql int row = st.executeUpdate(sql); System.out.println(row); if(row!=0){ System.out.println("添加成功"); } }catch(ClassNotFoundException e){ e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //关闭资源 try { if(st!=null){ st.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(con!=null){ con.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } //delete测试 @Test public void deleteTest(){ //将id=3的人删除 //String sql = "delete from user where id=3"; //将id=2的人删除 String sql = "delete from user where id=2"; //1.得到Connection Connection con = null; Statement st = null; try { con = JdbcUtils.getConnectin(); //3.获取操作sql语句对象Statement st = con.createStatement(); //4.执行sql int row = st.executeUpdate(sql); System.out.println(row); if(row!=0){ System.out.println("删除成功"); } }/*catch(ClassNotFoundException e){ e.printStackTrace(); }*/ catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //关闭资源 /* try { if(st!=null){ st.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }*/ /* try { if(con!=null){ con.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } */ try { JdbcUtils.closeStatement(st); JdbcUtils.closeConnection(con); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }