JDBC03-PreparedStatement实现表数据的添加操作

JDBC03-PreparedStatement实现表数据的增删改操作

一、通用方法封装成一个util类

获取数据库的连接

public static Connection getConnection() throws Exception {
   // 1.读取配置文件中的4个基本信息
   InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

   Properties pros = new Properties();
   pros.load(is);

   String user = pros.getProperty("user");
   String password = pros.getProperty("password");
   String url = pros.getProperty("url");
   String driverClass = pros.getProperty("driverClass");

   // 2.加载驱动
   Class.forName(driverClass);

   // 3.获取连接
   Connection conn = DriverManager.getConnection(url, user, password);
   return conn;
}

关闭连接和Statement的操作

public static void closeResource(Connection conn,Statement ps){
   try {
      if(ps != null)
         ps.close();
   } catch (SQLException e) {
      e.printStackTrace();
   }
   try {
      if(conn != null)
         conn.close();
   } catch (SQLException e) {
      e.printStackTrace();
   }
}

关闭资源的操作

public static void closeResource(Connection conn,Statement ps,ResultSet rs){
   try {
      if(ps != null)
         ps.close();
   } catch (SQLException e) {
      e.printStackTrace();
   }
   try {
      if(conn != null)
         conn.close();
   } catch (SQLException e) {
      e.printStackTrace();
   }
   try {
      if(rs != null)
         rs.close();
   } catch (SQLException e) {
      e.printStackTrace();
   }
}

二、向customers表中添加一条记录

@Test
   public void testInsert() {
      Connection conn = null;
      PreparedStatement ps = null;
      try {
         // 1.读取配置文件中的4个基本信息
         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

         Properties pros = new Properties();
         pros.load(is);

         String user = pros.getProperty("user");
         String password = pros.getProperty("password");
         String url = pros.getProperty("url");
         String driverClass = pros.getProperty("driverClass");

         // 2.加载驱动
         Class.forName(driverClass);

         // 3.获取连接
         conn = DriverManager.getConnection(url, user, password);
         
//    System.out.println(conn);
         
         //4.预编译sql语句，返回PreparedStatement的实例
         String sql = "insert into customers(name,email,birth)values(?,?,?)";//?:占位符
         ps = conn.prepareStatement(sql);
         //5.填充占位符
         ps.setString(1, "哪吒");
         ps.setString(2, "nezha@gmail.com");
         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
         java.util.Date date = sdf.parse("1000-01-01");
         ps.setDate(3, new Date(date.getTime()));
         
         //6.执行操作
         ps.execute();
      } catch (Exception e) {
         e.printStackTrace();
      }finally{
         //7.资源的关闭
         try {
            if(ps != null)
               ps.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
         try {
            if(conn != null)
               conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
         
      }
      
   }

三、修改customers表的一条记录

//
@Test
public void testUpdate(){
   Connection conn = null;
   PreparedStatement ps = null;
   try {
      //1.获取数据库的连接
      conn = JDBCUtils.getConnection();
      //2.预编译sql语句，返回PreparedStatement的实例
      String sql = "update customers set name = ? where id = ?";
      ps = conn.prepareStatement(sql);
      //3.填充占位符
      ps.setObject(1,"莫扎特");
      ps.setObject(2, 18);
      //4.执行
      ps.execute();
   } catch (Exception e) {
      e.printStackTrace();
   }finally{
      //5.资源的关闭
      JDBCUtils.closeResource(conn, ps);
      
   }
}

二、通用的增删改操作

public void update(String sql,Object ...args){//sql中占位符的个数与可变形参的长度相同！
   Connection conn = null;
   PreparedStatement ps = null;
   try {
      //1.获取数据库的连接
      conn = JDBCUtils.getConnection();
      //2.预编译sql语句，返回PreparedStatement的实例
      ps = conn.prepareStatement(sql);
      //3.填充占位符
      for(int i = 0;i < args.length;i++){
         ps.setObject(i + 1, args[i]);//小心参数声明错误！！
      }
      //4.执行
      ps.execute();
   } catch (Exception e) {
      e.printStackTrace();
   }finally{
      //5.资源的关闭
      JDBCUtils.closeResource(conn, ps);
      
   }
   
   
}